如何仅对指定行做宽表转换并添加对应年度总计列？

样本数据 #

indcode <- c(71,72,81,82,99,000000,71,72,81,82,99,000000)
year <- c(2020,2020,2020,2020,2020,2020,2021,2021,2021,2021,2021,2021)
employment <- c(3,5,7,9,2,26,4,6,8,10,3,31)

test <- data.frame(indcode, year, employment)

需求说明 #

给上述test数据框新增total列，total的值为对应年份中indcode等于000000的employment数值，同时删除所有indcode为000000的行。尝试用pivot_wider方法但无法让总计值重复到每行，期望结果如下：

year indcode employment total
1 2020      71          3    26
2 2020      72          5    26
3 2020      81          7    26
4 2020      82          9    26
5 2020      99          2    26
6 2021      71          4    31
...

解决方案 #

方法一：结合pivot_wider与pivot_longer实现 #

利用tidyverse包的函数，先将总计值转为单独列，再转回长格式并过滤：

library(tidyverse)

result <- test %>%
  # 按年份拆分，将不同indcode转为列，employment为对应值
  pivot_wider(
    id_cols = year,
    names_from = indcode,
    values_from = employment,
    names_prefix = "ind_",
    values_fill = 0
  ) %>%
  # 将原000000对应的列重命名为total（原数据中000000会被转成整数0）
  rename(total = ind_0) %>%
  # 转回长格式，恢复indcode和employment列
  pivot_longer(
    cols = starts_with("ind_"),
    names_to = "indcode",
    values_to = "employment",
    names_prefix = "ind_"
  ) %>%
  # 过滤掉indcode为0的行
  filter(indcode != "0") %>%
  # 将indcode转回整数类型
  mutate(indcode = as.integer(indcode)) %>%
  # 调整列顺序并按年份、行业代码排序
  select(year, indcode, employment, total) %>%
  arrange(year, indcode)

print(result)

方法二：提取总计数据后左连接（更简洁） #

先提取各年份的总计值，再通过左连接将总计值匹配到对应年份的每行数据：

library(dplyr)

# 提取各年份的总计值，存储为单独数据框
total_data <- test %>%
  filter(indcode == 0) %>% # 原数据中000000会被自动转为整数0
  select(year, total = employment)

# 过滤掉原数据中的总计行，再左连接总计数据
result <- test %>%
  filter(indcode != 0) %>%
  left_join(total_data, by = "year") %>%
  select(year, indcode, employment, total)

print(result)

注意事项 #

如果需要保留indcode的字符串格式（比如保留000000而非转为整数0），创建数据框时要指定indcode为字符型：

test <- data.frame(
  indcode = as.character(c(71,72,81,82,99,"000000",71,72,81,82,99,"000000")),
  year = c(2020,2020,2020,2020,2020,2020,2021,2021,2021,2021,2021,2021),
  employment = c(3,5,7,9,2,26,4,6,8,10,3,31)
)

此时对应的过滤和匹配要使用字符串"000000"：

total_data <- test %>%
  filter(indcode == "000000") %>%
  select(year, total = employment)

result <- test %>%
  filter(indcode != "000000") %>%
  left_join(total_data, by = "year") %>%
  select(year, indcode, employment, total)

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