如何使用tidyr的pivot_wider将堆叠式人口数据转为宽格式？

先模拟你的原始数据结构（方便对应实际数据）：

library(tidyr)
library(dplyr)

# 模拟堆叠格式的原始数据
raw_data <- tibble(
  mun_code = c("A001", "A001", "A001", "A002", "A002", "A002"),
  population_type = c("市镇名", "urban", "rural", "市镇名", "urban", "rural"),
  population = c(50000, 35000, 15000, 80000, 60000, 20000)
)

核心解决步骤 #

你用pivot_wider()没得到预期结果，大概率是没正确配置分组键或列映射参数，按以下步骤操作即可：

1. 统一人口类型命名：把"市镇名"替换为"total"，让新变量名更规范
2. 调用pivot_wider()：指定分组标识、列名来源、值来源三个核心参数
3. 重命名变量：改成你需要的total_pop/urban_pop/rural_pop

完整代码：

wide_data <- raw_data %>%
  # 替换人口类型名称，避免"市镇名"作为变量名不规范
  mutate(population_type = ifelse(population_type == "市镇名", "total", population_type)) %>%
  pivot_wider(
    id_cols = mun_code,          # 按市镇编码分组，确保每个市镇一行
    names_from = population_type,# 从该列取值生成新列名
    values_from = population     # 从该列提取对应新列的值
  ) %>%
  # 重命名为你需要的变量名
  rename(
    total_pop = total,
    urban_pop = urban,
    rural_pop = rural
  )

# 查看结果
print(wide_data)

包含市镇名称的情况 #

如果你的原始数据还有mun_name（市镇名称）列，只需把它加入id_cols即可：

raw_data_with_name <- tibble(
  mun_code = c("A001", "A001", "A001", "A002", "A002", "A002"),
  mun_name = c("甲市镇", "甲市镇", "甲市镇", "乙市镇", "乙市镇", "乙市镇"),
  population_type = c("市镇名", "urban", "rural", "市镇名", "urban", "rural"),
  population = c(50000, 35000, 15000, 80000, 60000, 20000)
)

wide_data_with_name <- raw_data_with_name %>%
  mutate(population_type = ifelse(population_type == "市镇名", "total", population_type)) %>%
  pivot_wider(
    id_cols = c(mun_code, mun_name),
    names_from = population_type,
    values_from = population
  ) %>%
  rename(total_pop = total, urban_pop = urban, rural_pop = rural)

print(wide_data_with_name)

常见问题排查 #

• 如果转换后出现重复行：检查id_cols是否包含所有唯一标识列，确保每个分组（市镇）的population_type没有重复值
• 如果出现NA值：确认原始数据中每个mun_code对应的三种人口类型都存在，没有缺失行

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