分组DataFrame多列字符串汇总结果异常的解决求助

数据结构 #

df <- structure(list(TurnID = c(1L, 1L, 1L, 2L, 2L, 2L), 
                     grp = c(1L, 2L, 3L, 1L, 2L, 3L), 
                     File = c("F01", "F01", "F01", "F01", "F01", "F01"), 
                     N_p = c(3L, 3L, 3L, 3L, 3L, 3L), Line = c(6L, 6L, 6L, 7L, 7L, 7L), 
                     Speaker = c("ID01.A", "ID01.A", "ID01.A", "ID01.C", "ID01.C", "ID01.C"), 
                     Utterance = c("=yeah (...) yeah yeah", "=yeah (...) yeah yeah", "=yeah (...) yeah yeah", "[(...)]", "[(...)]", "[(...)]"), 
                     A_aoi = c("C", "*", "C", "C", NA, NA), 
                     B_aoi = c("A", NA, NA, "A", NA, NA), 
                     C_aoi = c("A", "*", NA, "A", "*", "A"), 
                     A_aoi_dur = c(310L, 499L, 1201L, 2051L, NA, NA), 
                     B_aoi_dur = c(2010L, NA, NA, 2051L, NA, NA), 
                     C_aoi_dur = c(945L, 1065L, NA, 88L, 1660L, 303L)), 
                class = c("grouped_df", "tbl_df", "tbl", "data.frame"), 
                row.names = c(NA, -6L), 
                groups = structure(list(TurnID = 1:2, .rows = structure(list(1:3, 4:6), ptype = integer(0), class = c("vctrs_list_of",  "vctrs_vctr", "list"))), class = c("tbl_df", "tbl", "data.frame"), 
                                   row.names = c(NA, -2L), .drop = TRUE))

当前错误代码及结果 #

执行以下汇总代码：

df %>%
  #group_by(TurnID) %>%
  summarise(across(c(File, N_p, Line, Speaker, Utterance), first),
    A_aoi = str_c(A_aoi, collapse = ""),
    B_aoi = str_c(B_aoi, collapse = ""),
    C_aoi = str_c(C_aoi, collapse = ""),
    A_aoi_dur = str_c(A_aoi_dur, collapse = ","),
    B_aoi_dur = str_c(B_aoi_dur, collapse = ","),
    C_aoi_dur = str_c(C_aoi_dur, collapse = ",")
  )

得到错误结果：

# A tibble: 2 × 12
  TurnID File    N_p  Line Speaker Utterance             A_aoi B_aoi C_aoi A_aoi_dur    B_aoi_dur C_aoi_dur  
   <int> <chr> <int> <int> <chr>   <chr>                 <chr> <chr> <chr> <chr>        <chr>     <chr>      
1      1 F01       3     6 ID01.A  =yeah (...) yeah yeah C*C   NA    NA    310,499,1201 NA        NA         
2      2 F01       3     7 ID01.C  [(...)]               NA    NA    A*A   NA           NA        88,1660,303

预期正确结果 #

TurnID File    N_p  Line Speaker Utterance             A_aoi B_aoi C_aoi   A_aoi_dur      B_aoi_dur C_aoi_dur  
   <int> <chr> <int> <int> <chr>   <chr>                 <chr> <chr> <chr>   <chr>          <chr>     <chr>      
1      1 F01       3     6 ID01.A  =yeah (...) yeah yeah C*C    A    A*      310,499,1201   2010      945,1201         
2      2 F01       3     7 ID01.C  [(...)]               C      A    A*A     2051           2051      88,1660,303

问题原因及解决方案 #

问题根源 #

str_c()函数在遇到NA时会直接返回NA，导致包含NA的AOI列拼接结果全部变为NA；duration列的NA会被保留为字符串"NA"，不符合预期格式。

修正代码 #

library(dplyr)
library(stringr)
library(tidyr)

df %>%
  summarise(
    across(c(File, N_p, Line, Speaker, Utterance), first),
    # 处理AOI列：将NA替换为空字符串后拼接
    across(c(A_aoi, B_aoi, C_aoi), ~str_c(replace_na(., ""), collapse = "")),
    # 处理duration列：过滤NA后用逗号拼接有效值
    across(ends_with("_dur"), ~str_c(na.omit(.), collapse = ","))
  )

代码说明 #

1. AOI列处理：用replace_na(., "")把NA转为空字符串，再执行str_c()拼接，避免NA导致整列结果失效。
2. duration列处理：用na.omit(.)直接过滤掉NA值，仅拼接有效时长数据，得到无冗余内容的字符串。

执行上述代码后，即可得到符合预期的汇总结果。

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