如何基于唯一Key合并行数不等的人口迁移流入流出数据集？

我拥有两个人口迁移数据集：一个是其他县向A县的人口流入数据，另一个是A县向其他县的人口流出数据。需要将二者合并为以下格式，要求无需硬编码县名、州名、FIPS编码和年份，缺失值填充为NA。

两个数据集的观测行数不同（流出数据集有517条，流入数据集有441条），且不同县可能同属一个州。计划通过拼接FIPS（县唯一编码）与Year生成唯一Key，将各县对应的关联字段合并到同一行。

目标合并格式 #

Key         County          State   FIPS    Inflow  Outflow FiscalYear  Year
510012012   Accomack County VA      51001   NA      27      2011 - 2012 2012
160012012   Ada County      ID      16001   16      16      2011 - 2012 2012
80012012    Adams County    CO      8001    30      39      2011 - 2012 2012
80012011    Adams County    CO      8001    42      31      2010 - 2011 2011
450032012   Aiken County    SC      45003   NA      21      2011 - 2012 2012
120012012   Alachua County  FL      12001   433     NA      2011 - 2012 2012
120012011   Alachua County  FL      12001   381     NA      2010 - 2011 2011
160012011   Ada County      ID      16001   21      NA      2010 - 2011 2011

示例数据集 #

# 从A县迁往其他县的人口流出数据
inflow_df <- structure(list(Origin_FIPS = c(12001L, 8001L, 16001L, 12001L, 8001L, 16001L), 
                            Origin_StateName = c("FL", "CO", "ID", "FL", "CO", "ID"), 
                            Origin_Place = c("Alachua County", "Adams County", "Ada County", "Alachua County", "Adams County", "Ada County"), 
                            InIndividuals = c(433L, 30L, 16L, 381L, 42L, 21L), 
                            FiscalYear = c("2011 - 2012", "2011 - 2012", "2011 - 2012", "2010 - 2011", "2010 - 2011", "2010 - 2011"), 
                            Year = c(2012L, 2012L, 2012L, 2011L, 2011L, 2011L), 
                            Key = c(120012012L, 80012012L, 160012012L, 120012011L, 80012011L, 160012011L)), 
                       class = "data.frame", row.names = c(NA, -6L))

# 从其他县迁往A县的人口流入数据
outflow_df <- structure(list(Dest_FIPS = c(51001L, 16001L, 8001L, 8001L, 45003L), 
                             Dest_StateName = c("VA", "ID", "CO", "CO", "SC"), 
                             Dest_Place = c("Accomack County", "Ada County", "Adams County", "Adams County", "Aiken County"), 
                             OutIndividuals = c(27L, 16L, 39L, 31L, 21L), 
                             FiscalYear = c("2011 - 2012", "2011 - 2012", "2011 - 2012", "2010 - 2011", "2011 - 2012"), 
                             Year = c(2012L, 2012L, 2012L, 2011L, 2012L), 
                             Key = c(510012012L, 160012012L, 80012012L, 80012011L, 450032012L)), 
                        class = "data.frame", row.names = c(NA, -5L))

解决方案（R语言） #

用dplyr包的全连接功能可以快速实现需求，步骤如下：

1. 加载依赖包 #

library(dplyr)

2. 统一两个数据集的字段名 #

将流入、流出数据中代表「目标县/来源县」的字段重命名为统一名称，避免合并后出现重复字段：

# 处理流出数据，重命名字段
inflow_clean <- inflow_df %>%
  rename(
    FIPS = Origin_FIPS,
    State = Origin_StateName,
    County = Origin_Place,
    Inflow = InIndividuals
  ) %>%
  select(Key, FIPS, State, County, Inflow, FiscalYear, Year)

# 处理流入数据，重命名字段
outflow_clean <- outflow_df %>%
  rename(
    FIPS = Dest_FIPS,
    State = Dest_StateName,
    County = Dest_Place,
    Outflow = OutIndividuals
  ) %>%
  select(Key, FIPS, State, County, Outflow, FiscalYear, Year)

3. 全连接合并数据集 #

通过full_join基于Key及关联字段合并，自动填充缺失值为NA：

merged_df <- inflow_clean %>%
  full_join(outflow_clean, by = c("Key", "FIPS", "State", "County", "FiscalYear", "Year")) %>%
  # 调整字段顺序为目标格式
  select(Key, County, State, FIPS, Inflow, Outflow, FiscalYear, Year) %>%
  # 可选：按Key排序，和目标格式一致
  arrange(Key)

合并结果说明 #

• 全连接会保留两个数据集中所有的Key，无论该Key只出现在流入还是流出数据中；
• 未匹配到的Inflow或Outflow字段会自动填充为NA；
• 无需手动编码任何县、州或年份信息，完全基于数据中的FIPS和Year生成的Key完成匹配。

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