如何在R语言中合并跨行拆分的文本内容

问题场景 #

使用R语言的tabulizer包抓取PDF表格时，常会遇到多行文本被拆分为独立行的情况，示例混乱数据如下：

item quantity price
1   PURCHASE ORDER       NA    NA
2    ITEM CODE ABC       NA    NA
3  1 Computer soft        3 10.99
4             ware       NA    NA
5         2 Mathem        2  8.50
6 atics curriculum       NA    NA
7  and calculators       NA    NA

期望合并后的目标效果：

item                                    quantity price
1 PURCHASE ORDER ITEM CODE ABC                NA    NA  
2 1 Computer software                           3 10.99
3 2 Mathematics curriculum and calculators      2  8.5 

用于重建数据框的代码：

# 混乱数据框
item <- c("PURCHASE ORDER", "ITEM CODE ABC", "1 Computer soft", "ware", "2 Mathem", "atics curriculum", "and calculators")
quantity <- c(NA, NA, 3, NA, 2, NA, NA)
price <- c(NA, NA, 10.99, NA, 8.50, NA, NA)
df <- data.frame(item, quantity, price)
df

# 目标数据框
item <- c("PURCHASE ORDER ITEM CODE ABC", "1 Computer software", "2 Mathematics curriculum and calculators")
quantity <- c(NA, 3, 2)
price <- c(NA, 10.99, 8.50)
df <- data.frame(item, quantity, price)
df

解决方法 #

核心思路是根据quantity和price的非NA值划分分组，将同一组内的item文本合并，以下提供两种实现方式：

方式1：使用dplyr包处理 #

library(dplyr)

df_clean <- df %>%
  # 创建分组标识：非NA值的行作为组起始点，向下填充分组ID
  mutate(group_id = cumsum(!is.na(quantity) | !is.na(price))) %>%
  # 单独处理首行全NA的分组
  mutate(group_id = ifelse(row_number() == 1 & is.na(quantity) & is.na(price), 0, group_id)) %>%
  # 按分组合并文本，提取非NA的数量和价格
  group_by(group_id) %>%
  summarise(
    item = paste(item, collapse = " "),
    quantity = first(na.omit(quantity)),
    price = first(na.omit(price))
  ) %>%
  ungroup() %>%
  select(-group_id)

df_clean

方式2：基础R原生实现 #

# 生成分组标识向量
group_id <- cumsum(!is.na(df$quantity) | !is.na(df$price))
# 调整首段连续NA的分组
if (is.na(df$quantity[1]) & is.na(df$price[1])) {
  group_id[1:2] <- 0
}

# 按分组合并数据
df_clean <- do.call(rbind, lapply(split(df, group_id), function(x) {
  data.frame(
    item = paste(x$item, collapse = " "),
    quantity = if (all(is.na(x$quantity))) NA else x$quantity[!is.na(x$quantity)][1],
    price = if (all(is.na(x$price))) NA else x$price[!is.na(x$price)][1]
  )
}))

rownames(df_clean) <- NULL
df_clean

运行上述代码后，即可得到符合预期的干净数据框。

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