如何在R中完成特定行运算：计算指定区域联邦就业数据

需求说明 #

• 现有数据集中，存在18行indcode=000000且ownership=10的记录（以area作为区分维度），同时对应18行indcode=4911且ownership=10的记录
• 数据包含02-Jan至23-Jun的月度数值列，需要生成indcode=910的新记录，计算规则为：同一area、同一月份下，indcode=000000的数值减去indcode=4911的数值
• 额外要求：将类似02-Jan的列名重命名为Jan（其他月份同理）

示例数据 #

indcode <- c("000000","000000","000000","000000", "55", "4911","4911","4911","4911")
ownership <- c("10","10","10","10","10","10","10","10","10")
area <- c("000000","031","029","017","029","000000","031","029","017")
`02-Jan` <- c(1000,600,300,100,50,100,50,40,10)
`02-Feb` <- c(1003,601,301,101,51,101,51,41,11)

first <- data.frame(indcode, ownership, area, `02-Jan`, `02-Feb`)

解决方案（基于tidyverse工具包） #

通过数据重塑+分组计算的方式可以高效实现需求，步骤如下：

1. 安装并加载tidyverse工具包（未安装则先执行安装）
2. 筛选出参与计算的目标行（仅保留indcode为000000/4911且ownership=10的记录）
3. 将宽表转为长表，便于按月份维度分组计算
4. 按area和月份分组，计算差值并生成indcode=910的记录
5. 将长表转回宽表，并重命名月份列去除前缀
6. （可选）将计算结果与原数据集合并

完整代码

# 安装并加载tidyverse
if (!require(tidyverse)) {
  install.packages("tidyverse")
  library(tidyverse)
}

# 处理数据生成目标记录
result <- first %>%
  # 筛选需要参与计算的行
  filter(ownership == "10", indcode %in% c("000000", "4911")) %>%
  # 宽表转长表，提取月份和对应数值
  pivot_longer(cols = starts_with("02-"), names_to = "month", values_to = "value") %>%
  # 按区域和月份分组，计算差值
  group_by(area, month) %>%
  summarise(
    value = value[indcode == "000000"] - value[indcode == "4911"],
    .groups = "drop"
  ) %>%
  # 添加固定列信息
  mutate(
    indcode = "910",
    ownership = "10"
  ) %>%
  # 调整列顺序后转回宽表
  select(indcode, ownership, area, month, value) %>%
  pivot_wider(names_from = "month", values_from = "value") %>%
  # 重命名月份列，去掉前缀"02-"
  rename_with(~ str_remove(., "^02-"), starts_with("02-"))

# 查看最终结果
print(result)

输出结果 #

indcode ownership area   Jan Feb
  <chr>   <chr>     <chr> <dbl> <dbl>
1 910     10        000000  900  902
2 910     10        017     90  90
3 910     10        029    260  260
4 910     10        031    550  550

如果需要保留1000-100这类文本格式（而非直接计算差值），只需修改summarise部分的代码：

summarise(
  value = paste(value[indcode == "000000"], value[indcode == "4911"], sep = "-"),
  .groups = "drop"
)

此时输出会变为：

indcode ownership area   Jan    Feb   
  <chr>   <chr>     <chr>  <chr>  <chr> 
1 910     10        000000 1000-100 1003-101
2 910     10        017     100-10  101-11  
3 910     10        029     300-40  301-41  
4 910     10        031     600-50  601-51  

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