求助：在R中合并数据框内跨行溢出的字符串数据

步骤1：加载数据 #

先将提供的测试数据导入R环境：

df <- structure(list(SECTION = c(10207L, NA, 14097L, NA, NA, NA, NA, 
21290L, NA, 3359L, NA, NA, NA, NA, 50903L, NA), SCHOOL = c("ACAD", 
"", "ACCT", "", "", "", "", "ANSC", "", "LAW", "", "", "", "", 
"XPPD", "PPD"), COURSE_CODE = c("ACAD-181", "", "ACCT-410", "", 
"", "", "", "PR-463", "", "LAW-680A", "", "", "", "", "PPDE-630", 
""), COURSE_TITLE = c("Disruptive Innovation", "", "Foundations of Accounting", 
"", "", "", "", "Strategic Public Relations Research, Analysis", 
"and Insights", "Review of Law and Social Justice Editing", "", 
"", "", "", "Community Health Planning", ""), INSTRUCTOR_NAME = c("Smith, Tim", 
"Bob, Scott", "Gem, Silvia", "", "", "", "", "OBrien, James", 
"", "Harvey, Tony", "", "", "", "", "Sloth,  Ryan", ""), ASSIGNED_ROOM = c("IYH210/211", 
"", "ONLINE", "", "", "", "", "ONLINE", "", "ONLINE", "", "", 
"", "", "ONLINE", ""), TOTAL_ENR = c(32L, NA, 55L, NA, NA, NA, 
NA, 17L, NA, 13L, NA, NA, NA, NA, 16L, NA), COURSE_DESCRIPTION = c("Critical approaches to social and cultural changes.", 
"", "Non-technical presentation of accounting for users of accounting", 
"information; introduction to financial and managerial accounting.", 
"Not open to students with course credits in accounting. Not", 
"available for unit or course credit toward a degree in accounting", 
"or business administration.", "Identification of key strategic insights.", 
"", "Supervision of research and writing, and final editing of articles", 
"and comments for publication in the Review of Law and Social", 
"Justice.  For officers of the Review.  Open to law students only.", 
"Graded IP to CR/D/F.", "", "The role of planning in sustaining community health.", 
"")), class = "data.frame", row.names = c(NA, -16L))

步骤2：生成分组标识 #

以SECTION列的非NA值为分界，把连续的溢出行归到对应的主行组：

df$group <- cumsum(!is.na(df$SECTION))

步骤3：分组合并各列数据 #

用dplyr包分组聚合，针对不同列的特点处理字符串拼接：

library(dplyr)

cleaned_df <- df %>%
  group_by(group) %>%
  summarise(
    # 单值列取组内第一个非NA值
    SECTION = first(na.omit(SECTION)),
    TOTAL_ENR = first(na.omit(TOTAL_ENR)),
    # 普通字符串列过滤空值后直接拼接
    SCHOOL = paste0(SCHOOL[SCHOOL != ""], collapse = ""),
    COURSE_CODE = paste0(COURSE_CODE[COURSE_CODE != ""], collapse = ""),
    ASSIGNED_ROOM = paste0(ASSIGNED_ROOM[ASSIGNED_ROOM != ""], collapse = ""),
    # 标题/描述类列用空格拼接保证语句连贯
    COURSE_TITLE = paste0(COURSE_TITLE[COURSE_TITLE != ""], collapse = " "),
    COURSE_DESCRIPTION = paste0(COURSE_DESCRIPTION[COURSE_DESCRIPTION != ""], collapse = " "),
    # 多值姓名用分号分隔
    INSTRUCTOR_NAME = paste0(INSTRUCTOR_NAME[INSTRUCTOR_NAME != ""], collapse = "; ")
  ) %>%
  select(-group) # 删除临时分组列

步骤4：查看处理结果 #

运行后cleaned_df即为合并完成的数据框，示例查看核心列：

cleaned_df %>% select(SECTION, COURSE_TITLE, INSTRUCTOR_NAME)

输出结果：

# A tibble: 5 × 3
  SECTION COURSE_TITLE                                      INSTRUCTOR_NAME
    <int> <chr>                                             <chr>          
1   10207 Disruptive Innovation                             Smith, Tim; Bob, Scott
2   14097 Foundations of Accounting                         Gem, Silvia    
3   21290 Strategic Public Relations Research, Analysis and Insights OBrien, James    
4    3359 Review of Law and Social Justice Editing          Harvey, Tony   
5   50903 Community Health Planning                         Sloth,  Ryan   

关键逻辑说明 #

• 分组依据：通过cumsum(!is.na(df$SECTION))生成连续组ID，确保所有溢出行精准归属到对应主行
• 列处理差异：区分单值列、普通字符串列、文本描述列、多值列，分别采用取首值、直接拼接、空格拼接、分号分隔的策略，适配不同数据类型的合并需求

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