根据r中的值重构数据框

Question

我有一个这样结构的df:

q1_1 q1_2 q1_3 q2_1 q2_2 q2_3
1    2    3    1    3    4
1    3         1    2    3  
1    4         2  

并且想要将数据转换为这个结构

Brand_1  Brand_2 Brand_3 Brand_4
q1  3        1      2        1
q2  2        2      2        1 

所以基本上我正在重塑数据，但是根据值的计数创建列，所以在这个例子中，我需要一个名为Brand_1的列，它计数q1和q2中出现1的次数。我已经能够重塑数据，但不知道如何处理基于值的列名。

Answer 1

可以吗?

library(dplyr)
library(tidyr)
library(stringr)
df %>% 
mutate(across(everything(), ~ recode(., `1` = 'Brand_1', `2` = 'Brand_2', `3` = 'Brand_3', `4` = 'Brand_4'))) %>% 
pivot_longer(cols = everything()) %>% mutate(name = str_extract(name, 'q\d')) %>% count(name,value) %>% na.omit() %>% 
pivot_wider(id_cols = name, names_from = value, values_from = n)
# A tibble: 2 x 5
name  Brand_1 Brand_2 Brand_3 Brand_4
<chr>   <int>   <int>   <int>   <int>
1 q1          3       1       2       1
2 q2          2       2       2       1

Answer 2

adata.table解

library( data.table )
DT <- fread("q1_1 q1_2 q1_3 q2_1 q2_2 q2_3
1    2    3    1    3    4
1    3    NA     1    2    3  
1    4    NA     2  NA NA")
#melt to long format
ans <- melt( DT, measure.vars = names(DT), na.rm = TRUE )
#summarise while casting to wide
dcast( ans, gsub("(^q[0-9]).*", "\1", variable) ~ 
paste0("Brand_", value), fun.aggregate = length )
#    variable Brand_1 Brand_2 Brand_3 Brand_4
# 1:       q1       3       1       2       1
# 2:       q2       2       2       2       1

Answer 3

这样做

library(tidyverse)
df %>% pivot_longer(everything(), names_to = "Brand", values_to = "Brand_no") %>%
filter(!is.na(Brand_no)) %>%
mutate(Brand = str_extract(Brand, ".*(?=_)"),
Brand_no = paste("Brand", Brand_no, sep = "_")) %>%
count(Brand, Brand_no) %>%
pivot_wider(id_cols = Brand, names_from = Brand_no, values_from = n, values_fill = 0)
# A tibble: 2 x 5
Brand Brand_1 Brand_2 Brand_3 Brand_4
<chr>   <int>   <int>   <int>   <int>
1 q1          3       1       2       1
2 q2          2       2       2       1

dput使用

df <- structure(list(q1_1 = c(1L, 1L, 1L), q1_2 = 2:4, q1_3 = c(3L, 
NA, NA), q2_1 = c(1L, 1L, 2L), q2_2 = c(3L, 2L, NA), q2_3 = c(4L, 
3L, NA)), class = "data.frame", row.names = c(NA, -3L))
> df
q1_1 q1_2 q1_3 q2_1 q2_2 q2_3
1    1    2    3    1    3    4
2    1    3   NA    1    2    3
3    1    4   NA    2   NA   NA