在R中重组数据表，如果重复，将其转换为行到列

Question

我有一个不同家庭的详细信息表。每一行都有一个家庭成员的名字。我想通过将所有家庭成员的详细信息放在一行来重组我的表。请参阅下面的示例表。

Family_no <- c(01,02,02,03,04,05,05,05,04)
name <- c('n1','n2','n3','n4','n5','n6','n7','n8','n9')
gender  <- c('m','f','m','m','f','f','f','f','f')
age <- c(35,35,32,2,4,6,7,11,9)
relation <- c(1,2,1,2,4,6,7,8,1)
df <- data.frame(Family_no,name,gender,age,relation)
Family_no name gender age relation
1         1   n1      m  35        1
2         2   n2      f  35        2
3         2   n3      m  32        1
4         3   n4      m   2        2
5         4   n5      f   4        4
6         5   n6      f   6        6
7         5   n7      f   7        7
8         5   n8      f  11        8
9         4   n9      f   9        1

我正在寻找类似于下表的结果。你能告诉我该怎么走吗?

与

您可以从tidyr中使用pivot_wider来获得您想要的结果。

df_new <- df %>%
group_by(Family_no) %>%
mutate(row = row_number())
df_new2 <- df_new %>% 
pivot_wider(
names_from = row, 
values_from = c(name, gender, age, relation)
)

结果如下:

> df_new2
# A tibble: 5 × 13
# Groups:   Family_no [5]
Family_no name_1 name_2 name_3 gender_1 gender_2 gender_3 age_1 age_2 age_3 relation_1 relation_2 relation_3
<dbl> <chr>  <chr>  <chr>  <chr>    <chr>    <chr>    <dbl> <dbl> <dbl>      <dbl>      <dbl>      <dbl>
1         1 n1     NA     NA     m        NA       NA          35    NA    NA          1         NA         NA
2         2 n2     n3     NA     f        m        NA          35    32    NA          2          1         NA
3         3 n4     NA     NA     m        NA       NA           2    NA    NA          2         NA         NA
4         4 n5     n9     NA     f        f        NA           4     9    NA          4          1         NA
5         5 n6     n7     n8     f        f        f            6     7    11          6          7          8

---

如果列的顺序很重要，我可以建议这样做(不确定是否可以直接使用pivot_wider更简单):

cols <- grep("^(name|gender|age|relation)_", names(df_new), value = TRUE)
n <- length(cols)
df_new3 <- df_new2 %>%
select(Family_no, 
matches(sprintf("^(name|gender|age|relation)_%d$", 1:n)))

与结果:

> df_new3
# A tibble: 5 × 13
# Groups:   Family_no [5]
Family_no name_1 gender_1 age_1 relation_1 name_2 gender_2 age_2 relation_2 name_3 gender_3 age_3 relation_3
<dbl> <chr>  <chr>    <dbl>      <dbl> <chr>  <chr>    <dbl>      <dbl> <chr>  <chr>    <dbl>      <dbl>
1         1 n1     m           35          1 NA     NA          NA         NA NA     NA          NA         NA
2         2 n2     f           35          2 n3     m           32          1 NA     NA          NA         NA
3         3 n4     m            2          2 NA     NA          NA         NA NA     NA          NA         NA
4         4 n5     f            4          4 n9     f            9          1 NA     NA          NA         NA
5         5 n6     f            6          6 n7     f            7          7 n8     f           11          8