长格式转宽格式时保留所有sn3匹配行的实现方法

要实现将长格式数据转换为宽格式且保留sn3=1和sn3=2的所有匹配行，核心是先为每个sn2+sn3的分组添加唯一行索引，避免转换时重复值被默认聚合（仅保留第一个），再执行宽格式转换。以下是具体实现方案：

步骤说明 #

1. 添加分组行索引：按sn2（家庭编号）和sn3（个人编号）分组，为每组内的行添加序号，确保每一行能被唯一识别。
2. 执行宽格式转换：以sn2和新增行索引作为分组依据，将sn3的取值作为列名后缀，把长格式数据展开为宽格式。
3. 清理临时列：移除新增的行索引列，得到目标格式。

代码实现 #

首先加载tidyverse工具包（包含dplyr和tidyr）：

library(tidyverse)

接着处理你的数据：

# 加载你提供的原始数据
df <- structure(list(sn2 = structure(c(227, 227, 227, 227, 249, 249, 
250, 250, 250, 250, 294, 294, 294, 294, 307, 307, 307, 307), label = "household number", format.stata = "%8.0g"), 
    sn3 = structure(c(1, 1, 2, 2, 1, 1, 1, 1, 2, 2, 1, 1, 2, 
    2, 1, 1, 2, 2), label = "person number", format.stata = "%8.0g"), 
    leisure = c(230, 130, 540, 170, 430, 480, 370, 380, 380, 
    190, 120, 200, 210, 310, 130, 480, 220, 270), hmonth = structure(c(6, 
    6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 7, 7, 7, 7), label = "month of household interview", format.stata = "%8.0g", labels = c(january = 1, 
    february = 2, march = 3, april = 4, may = 5, june = 6, july = 7, 
    august = 8, september = 9, october = 10, november = 11, december = 12
    ), class = c("haven_labelled", "vctrs_vctr", "double")), 
    hyear = structure(c(2000, 2000, 2000, 2000, 2000, 2000, 2000, 
    2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 
    2000), label = "year of household interview", format.stata = "%8.0g")), row.names = c(NA, 
-18L), class = c("tbl_df", "tbl", "data.frame"), na.action = structure(c(`3492` = 3492L, 
`3493` = 3493L, `3494` = 3494L, `3495` = 3495L, `3496` = 3496L, 
`3497` = 3497L, `3498` = 3498L, `3499` = 3499L, `3500` = 3500L, 
`3501` = 3501L, `3508` = 3508L, `3509` = 3509L, `3510` = 3510L, 
`3511` = 3511L, `3512` = 3512L, `3513` = 3513L, `3518` = 3518L, 
`3519` = 3519L, `3520` = 3520L, `3521` = 3521L, `3522` = 3522L, 
`3523` = 3523L, `3524` = 3524L, `3525` = 3525L), class = "omit"))

# 转换为目标宽格式
wide_df <- df %>%
  group_by(sn2, sn3) %>%
  mutate(row_id = row_number()) %>%
  ungroup() %>%
  pivot_wider(
    id_cols = c(sn2, row_id),
    names_from = sn3,
    values_from = c(leisure, hmonth, hyear),
    names_sep = "."
  ) %>%
  select(-row_id) %>%
  arrange(sn2)

# 查看结果
print(wide_df)

结果说明 #

运行代码后，输出结果与你提供的期望格式完全一致：每个sn2对应的sn3=1和sn3=2的所有匹配行都被保留，没有丢失任何数据。

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