如何用mutate(across(case_when()))填充空白行实现两行数据聚合？

需求说明 #

需要将两行数据聚合成一行：相同字段值保留，不同值用分号拼接。尝试过用add_row()新增全NA空白行作为聚合目标，再用mutate(across(case_when()))计算，但代码覆盖了原有两行数据，未将聚合值填充到新增行中，且不想通过pivot转置实现。

示例数据 #

library(tidyverse)
test <- data.frame(
  ID = c("1","2"),
  Email = c("janedoe@email.com","jessicadoe@email.com"),
  Gender = c("Female","Female"),
  Location = c("Los Angeles","Los Angeles"),
  Ice_Cream_Flavor = c("Strawberry","Rocky Road")
)

原数据展示：

# A tibble: 2 × 5
  ID    Email                Gender Location    Ice_Cream_Flavor
  <chr> <chr>                <chr>  <chr>       <chr>
1 1     janedoe@email.com    Female Los Angeles Strawberry
2 2     jessicadoe@email.com Female Los Angeles Rocky Road

尝试的代码及问题 #

merge <- test %>%
  add_row() %>%
  mutate(across(.cols = -c(`ID`, `Email`),
                ~ case_when(!is.na(`ID`) & .[1]==.[2] ~ .[1],
                            !is.na(`ID`) & .[1]!=.[2] ~ paste(.[1],.[2], sep = "; "),
                            TRUE~NA)))

实际输出（原有行被错误覆盖）：

# A tibble: 3 × 5
  ID    Email                Gender Location    Ice_Cream_Flavor
  <chr> <chr>                <chr>  <chr>       <chr>
1 1     janedoe@email.com    Female Los Angeles Strawberry; Rocky Road
2 2     jessicadoe@email.com Female Los Angeles Strawberry; Rocky Road
3 NA    NA                   NA     NA          NA

期望输出 #

ID    Email                Gender Location    Ice_Cream_Flavor
 1     janedoe@email.com    Female Los Angeles Strawberry
 2     jessicadoe@email.com Female Los Angeles Rocky Road
 NA    NA                   Female Los Angeles Strawberry; Rocky Road

优雅解决方案 #

直接生成聚合行后与原数据合并，避免修改原有行：

# 生成聚合行
aggregate_row <- test %>%
  summarize(
    ID = NA_character_,
    Email = NA_character_,
    across(c(Gender, Location, Ice_Cream_Flavor), ~ {
      # 列值全部相同时取第一个，否则拼接所有值
      if (n_distinct(.) == 1) first(.) else paste(., collapse = "; ")
    })
  )

# 合并原数据与聚合行
result <- bind_rows(test, aggregate_row)

# 查看最终结果
print(result)

运行后得到的结果与期望输出一致：

ID                Email Gender    Location Ice_Cream_Flavor
1  1    janedoe@email.com Female Los Angeles       Strawberry
2  2 jessicadoe@email.com Female Los Angeles        Rocky Road
3 NA                  <NA> Female Los Angeles Strawberry; Rocky Road

代码解释 #

• summarize单独计算聚合行：对需要处理的列，用n_distinct(.)判断是否所有值相同，相同则取第一个值，不同则用paste(., collapse = "; ")拼接。
• bind_rows将原数据和聚合行合并，不会修改原有数据，直接新增目标聚合行。

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