在R中重构数据表：将重复行转换为列（家庭数据场景）

我有一张记录不同家庭信息的数据表，每行对应一位家庭成员的详细信息。需要重构数据表，将同一家庭的所有成员信息整合到单独一行中。

示例数据 #

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)

原数据表：

期望结果 #

解决方案 #

方法1：使用tidyverse包的pivot_wider #

先给每个家庭内的成员添加序号，再通过行转列实现整合：

library(tidyverse)

# 为每个家庭的成员生成序号
df_with_id <- df %>%
  group_by(Family_no) %>%
  mutate(member_id = row_number()) %>%
  ungroup()

# 行转列整合数据
result <- df_with_id %>%
  pivot_wider(
    id_cols = Family_no,
    names_from = member_id,
    values_from = c(name, gender, age, relation),
    names_sep = ""
  )

# 调整列顺序并修改列名，匹配期望格式
result <- result %>%
  select(
    Family_no,
    name1, gender1, age1, relation1,
    name2, gender2, age2, relation2,
    name3, gender3, age3, relation3
  )
colnames(result) <- c(
  "Family_no",
  rep(c("name", "gender", "age", "relation"), 3)
)

print(result)

方法2：使用data.table包的dcast #

适合大数据集的高效实现方式：

library(data.table)

setDT(df)
# 为每个家庭的成员生成序号
df[, member_id := .I, by = Family_no]

# 多变量行转列
result <- dcast(
  df,
  Family_no ~ member_id,
  value.var = c("name", "gender", "age", "relation")
)

# 调整列顺序和列名
setcolorder(result, c(
  "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"
))
colnames(result) <- c(
  "Family_no",
  rep(c("name", "gender", "age", "relation"), 3)
)

print(result)

内容的提问来源于stack exchange，提问作者Roshan