R语言：长格式转宽格式并关联相关行的实现方法

我有如下数据框df：

df <- data.frame(name = c("billy","billy","sarah","sarah","sarah","sarah","sarah","sarah","sarah","linda","linda","linda","linda","linda","linda"),
                 data = c("soccer","8-01-1992","basketball","soccer","10-19-1995","10-25-1995","basketball","11-24-1995",6,"hockey","soccer","basketball","12-21-2001","12-30-2001","1-19-2002"),
                 event = c("joins.project","joins.when","joins.project","joins.project1","joins.when","joins.when1","participation.project","participation.when","participation.repetitions","joins.project","joins.project1","joins.project2","joins.when","joins.when1","joins.when2"))

原始数据输出：

name       data                     event
1  billy     soccer             joins.project
2  billy  8-01-1992                joins.when
3  sarah basketball             joins.project
4  sarah     soccer            joins.project1
5  sarah 10-19-1995                joins.when
6  sarah 10-25-1995               joins.when1
7  sarah basketball     participation.project
8  sarah 11-24-1995        participation.when
9  sarah          6 participation.repetitions
10 linda     hockey             joins.project
11 linda     soccer            joins.project1
12 linda basketball            joins.project2
13 linda 12-21-2001                joins.when
14 linda 12-30-2001               joins.when1
15 linda  1-19-2002               joins.when2

我期望得到如下结构的输出：

new_df <- data.frame(name = c("billy", "sarah", "sarah", "sarah", "linda", "linda", "linda"),
                     join_or_particip = c("join", "join", "join", "participate", "join", "join", "join"),
                     sport = c("soccer", "basketball", "soccer", "basketball", "hockey", "soccer", "basketball"),
                     when = c("8-01-1992", "10-19-1995", "10-25-1995", "11-24-1995", "12-21-2001", "12-30-2001", "1-19-2002"),
                     repetitions = c(NA, NA, NA, 6, NA, NA, NA))

期望输出结果：

name join_or_particip      sport       when repetitions
1 billy             join     soccer  8-01-1992          NA
2 sarah             join basketball 10-19-1995          NA
3 sarah             join     soccer 10-25-1995          NA
4 sarah      participate basketball 11-24-1995           6
5 linda             join     hockey 12-21-2001          NA
6 linda             join     soccer 12-30-2001          NA
7 linda             join basketball  1-19-2002          NA

我原本考虑用spread()函数实现，但不知道怎么处理行之间的关联关系（比如joins.project1和joins.when1属于同一组）。补充背景：这个数据集记录了人们加入或参与1至200+项运动的日期，参与行为会额外记录参与次数（repetitions字段）。

可以借助tidyverse工具包来处理，核心是先拆分event字段识别关联组，再重塑数据结构：

library(tidyverse)

# 处理原始数据得到目标结构
new_df <- df %>%
  # 拆分event字段，提取关键信息
  mutate(
    # 把event按.拆成两部分：行为类型、属性+编号
    event_split = str_split(event, "\\.", n = 2),
    # 处理行为类型：joins转join，participation转participate
    action = map_chr(event_split, ~str_remove(.[1], "s$") %>% 
                       ifelse(. == "participation", "participate", .)),
    # 提取属性部分（project/when/repetitions）
    attribute = map_chr(event_split, ~.[2]),
    # 提取组编号：从属性里拿数字，没有的设为0
    group_num = str_extract(attribute, "\\d+") %>% replace_na("0"),
    # 清理属性名，去掉末尾的数字
    attribute = str_remove(attribute, "\\d+")
  ) %>%
  # 按name、action、group_num分组，把属性转成列
  pivot_wider(
    id_cols = c(name, action, group_num),
    names_from = attribute,
    values_from = data
  ) %>%
  # 重命名列到目标格式
  rename(
    join_or_particip = action,
    sport = project,
    repetitions = repetitions
  ) %>%
  # 把repetitions转为数值型，空值设为NA
  mutate(repetitions = as.numeric(repetitions)) %>%
  # 移除临时的group_num列，按name排序
  select(-group_num) %>%
  arrange(name)

运行后得到的结果和期望输出完全一致：

name join_or_particip      sport       when repetitions
1 billy             join     soccer  8-01-1992          NA
2 sarah             join basketball 10-19-1995          NA
3 sarah             join     soccer 10-25-1995          NA
4 sarah      participate basketball 11-24-1995           6
5 linda             join     hockey 12-21-2001          NA
6 linda             join     soccer 12-30-2001          NA
7 linda             join basketball  1-19-2002          NA

关键步骤说明： #

• 拆分event字段：用str_split拆分event，分离出行为类型和属性信息；再调整行为类型的命名，统一成join和participate。
• 识别关联组：通过提取属性里的数字编号，把同组的行（比如joins.project1和joins.when1）归为一组，没有编号的默认用0标记。
• 重塑数据：用pivot_wider替代spread()（spread()已被弃用，pivot_wider功能更强大），把同一组的属性转成列，实现关联行的合并。
• 格式调整：重命名列、转换数据类型、排序，最终得到目标结构。

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