如何合并重复行缺失值生成完整数据并移除重复项（R语言）

原始数据 #

df1 = structure(list(item = c("HY04SB", "HY04SB", "GSP8Y1", "GSP8Y1", 
"9PLM0A", "9PLM0A"), weird = c("w'`tc7>", NA, NA, "($^-yk1%", 
"jd(,*'7_", NA), simple = c(NA, 14661746, 58489634, NA, NA, NA
), code = c("WX&}Awx:65Dgn9A3", "WX&}Awx:65Dgn9A3", "$8@g44;T7w'{8e^=", 
NA, NA, "(X<f..\\Js9da]CE")), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -6L))

# A tibble: 6 × 4
  item   weird      simple code              
  <chr>  <chr>       <dbl> <chr>             
1 HY04SB w'`tc7>        NA "WX&}Awx:65Dgn9A3"
2 HY04SB NA       14661746 "WX&}Awx:65Dgn9A3"
3 GSP8Y1 NA       58489634 "$8@g44;T7w'{8e^="
4 GSP8Y1 ($^-yk1%       NA  NA               
5 9PLM0A jd(,*'7_       NA  NA               
6 9PLM0A NA             NA "(X<f..\\Js9da]CE"

期望结果 #

structure(list(item = c("HY04SB", "GSP8Y1", "9PLM0A"), weird = c("w'`tc7>", 
"($^-yk1%", "jd(,*'7_"), simple = c(14661746, 58489634, NA), 
    code = c("WX&}Awx:65Dgn9A3", "$8@g44;T7w'{8e^=", "(X<f..\\Js9da]CE"
    )), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-3L))

# A tibble: 3 × 4
  item   weird      simple code              
  <chr>  <chr>       <dbl> <chr>             
1 HY04SB w'`tc7>  14661746 "WX&}Awx:65Dgn9A3"
2 GSP8Y1 ($^-yk1% 58489634 "$8@g44;T7w'{8e^="
3 9PLM0A jd(,*'7_     NA   "(X<f..\\Js9da]CE"

尝试过的代码（未成功） #

new_df <- df1 %>%
  group_by(item) %>%
  mutate(valnew = str_c(weird, simple, code, sep=":")) %>% 
  select(-weird, -simple, -code) %>%
  complete(ID, valnew = unique(valnew[!is.na(valnew)])) %>% 
  filter(!is.na(valnew)) %>% 
  separate(valnew, into = c('weird', 'simple', 'code'), sep=":") %>% 
  mutate(rn = row_number()) %>%
  left_join(df1 %>% 
              select(ID)) %>%
  filter(!duplicated(rn)) %>%
  select(names(df1))

解决方案 #

可以通过dplyr的分组汇总功能，提取每个分组内各列的非NA值，实现合并行并填充缺失值：

library(dplyr)

new_df <- df1 %>%
  group_by(item) %>%
  summarize(
    # 对每个列提取非NA值，若全为NA则保留NA
    across(everything(), ~ if(all(is.na(.))) NA else first(na.omit(.))),
    .groups = "drop"  # 取消分组
  )

代码说明 #

1. group_by(item)：按item列分组，将相同item的行归为一组；
2. summarize(across(...))：遍历所有列，对每一列执行逻辑：如果该列全为NA则返回NA，否则提取第一个非NA值（由于每个分组内每个列仅存在一个非NA值，因此first(na.omit(.))可以准确获取目标值）；
3. .groups = "drop"：汇总后取消分组结构，得到标准的dataframe格式。

执行上述代码后，即可得到你期望的3行4列结果。

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