在R中实现单列数值拆分至多列并适配多文件的技术需求

需求说明 #

需要从数据框第2行第2列开始，将Number of farms 2022列中的多空格分隔数值，拆分到后续的Number of farms 2023、Land in farms 2022等6列中。目标数据结构和期望输出样式如下：

目标数据结构 #

structure(list(State = c("State", "Alabama ", "Alaska ", "Arizona ", 
"Arkansas ", "California "), `Number of farms 2022` = c("number", 
"37,400    37,100    8,600     8,600      230       232", "1,200     1,200      870       870      725       725", 
"16,700    16,400   25,500    25,000    1,527     1,524", "37,800    37,400   13,700    13,700      362       366", 
"63,100    62,900   24,200    23,800      384       378"), `Number of farms 2023` = c("number", 
NA, NA, NA, NA, NA), `Land in farms 2022` = c("1,000 acres", 
NA, NA, NA, NA, NA), `Land in farms 2023` = c("1,000 acres", 
NA, NA, NA, NA, NA), `Average farm size 2022` = c("acres", NA, 
NA, NA, NA, NA), `Average farm size 2023` = c("acres", NA, NA, 
NA, NA, NA)), row.names = c(NA, 6L), class = "data.frame")

期望输出样式 #

State | Number of farms 2022 | Number of farms 2023 | Land in farms 2022 | Land in farms 2023 | Average farm size 2022 | Average farm size 2023
State | number | number | 1,000 acres | 1,000 acres | acres | acres
Alabama | 37,400  |  37,100  |  8,600   |  8,600   |   230   |    232

解决方案 #

核心思路 #

利用tidyr的separate_wider_delim函数，针对数据行（第2行及以后）拆分多空格分隔的数值，匹配到已存在的目标列；同时通过动态列名匹配，适配不同年份的文件。

完整代码示例 #

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

# 1. 加载并预处理原始文本数据
df <- read.delim('file.txt', header = F, stringsAsFactors = F)
df <- df[106:167,]
df <- data.frame(df)
df <- cSplit(df, "df",":")

# 2. 动态获取待拆分列和目标列（适配不同年份）
split_col <- colnames(df)[str_detect(colnames(df), "^Number of farms ")]
target_cols <- setdiff(colnames(df), c("State", split_col))

# 3. 拆分数据行，保留表头行原始值
df_processed <- df %>%
  # 标记表头/数据行，仅对数据行做拆分
  mutate(row_type = ifelse(row_number() == 1, "header", "data")) %>%
  separate_wider_delim(
    cols = all_of(split_col),
    delim = "\\s+", # 匹配多个连续空格作为分隔符
    names = target_cols,
    too_few = "align_start",
    too_many = "drop",
    rows = row_type == "data" # 仅处理数据行
  ) %>%
  # 恢复表头行的原始单位说明
  mutate(
    across(all_of(target_cols), ~ ifelse(row_type == "header", df[[cur_column()]][1], .))
  ) %>%
  select(-row_type) # 移除辅助标记列

# 查看处理结果
print(df_processed)

适配不同年份文件说明 #

代码通过str_detect自动匹配以Number of farms 开头的列，无需硬编码年份；target_cols自动提取所有待填充的后续列，只要待拆分列的数值数量和目标列数量一致，就能直接适配不同年份的USDA文件。

注意事项 #

• 确保待拆分列中每个数据行的数值数量（固定6个）和目标列数量一致
• 使用\\s+作为分隔符，兼容多个连续空格的情况
• 表头行（第一行）不参与拆分，保留原始的单位说明文本

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