如何无需硬编码县名合并行数不等的县域迁移流入流出数据集？

核心思路 #

先分别将迁入、迁出数据集转换为目标结构所需的字段格式，再通过**全连接（full join）**合并所有县-年份组合，最后将缺失的迁入/迁出值填充为0（代表该年份该县无对应流动记录）。

步骤及代码实现 #

我们用dplyr包完成数据整理与合并（若未安装需先执行install.packages("dplyr")）：

1. 整理迁出数据集
   提取目标字段，确保每个县-年份的迁出人口汇总（若原数据有重复记录需分组求和）：

   library(dplyr)
   
   # 处理迁出数据：提取目标字段，按县、州、年份分组汇总
   cleaned_outflow <- Outflow_df %>%
     mutate(County = Dest_place,
            State = Dest_StateName,
            Outflow = Individuals) %>%
     group_by(County, State, Year) %>%
     summarise(Outflow = sum(Outflow), .groups = "drop")
   

2. 整理迁入数据集
   同理处理迁入数据，匹配目标字段结构：

   # 处理迁入数据：提取目标字段，按县、州、年份分组汇总
   cleaned_inflow <- Inflow_df %>%
     mutate(County = Origin_Place,
            State = Origin_StateName,
            Inflow = Individuals) %>%
     group_by(County, State, Year) %>%
     summarise(Inflow = sum(Inflow), .groups = "drop")
   

3. 合并数据集并填充缺失值
   使用全连接保留所有县-年份组合，将缺失的迁入/迁出值设为0：

   # 全连接合并，填充缺失值为0
   final_df <- cleaned_outflow %>%
     full_join(cleaned_inflow, by = c("County", "State", "Year")) %>%
     mutate(Inflow = ifelse(is.na(Inflow), 0, Inflow),
            Outflow = ifelse(is.na(Outflow), 0, Outflow)) %>%
     select(County, State, Inflow, Outflow, Year) # 调整列顺序匹配目标结构
   

结果验证 #

运行上述代码后，final_df会生成符合要求的结构：

County State Inflow Outflow Year
B      BB    0      223     2019
C      CC    0      224     2019
D      DD    111    2333    2019
E      EE    8888   4444    2019
F      FF    9999   5555    2020
G      GG    0      6666    2020
H      HH    0      7777    2020

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