面板数据地区匹配（District Concordance）：批量合并拆分地区的代码问询

问题概述 #

需要处理2010-2021年的面板数据集，针对期间发生拆分、更名的约100个地区进行数值合并：例如Agar Malwa于2014年从Shajapur拆分，需将2014年及之后Agar Malwa的数值合并至Shajapur，仅保留合并后的Shajapur条目，删除原两个地区的记录。

原始数据集示例 #

期望输出示例 #

核心思路 #

1. 先构建地区映射表，统一记录所有拆分/更名关系及生效年份，包含字段：
   • original_district：合并后保留的目标地区
   • split_district：需被合并的拆分/更名地区
   • effective_year：合并规则开始生效的年份
2. 基于映射表批量遍历处理每组地区关系，实现数值合并与条目替换

Python（Pandas）代码实现 #

1. 构建地区映射表 #

可根据实际100个地区的情况扩展此表：

import pandas as pd

district_mapping = pd.DataFrame({
    "original_district": ["Shajapur", "OldDist1", "OldDist2"],
    "split_district": ["Agar Malwa", "NewDist1", "NewDist2"],
    "effective_year": [2014, 2016, 2018]
})

2. 加载原始数据 #

替换为你的实际数据加载代码（如pd.read_csv()）：

# 示例原始数据
df = pd.DataFrame({
    "Year": [2012, 2013, 2014, 2014, 2015, 2015],
    "District": ["Shajapur", "Shajapur", "Shajapur", "Agar Malwa", "Shajapur", "Agar Malwa"],
    "State": ["x", "x", "x", "x", "x", "x"],
    "values": [23, 21, 16, 8, 17, 10]
})

3. 批量合并处理 #

processed_df = df.copy()

# 遍历每组地区拆分关系
for _, mapping in district_mapping.iterrows():
    original = mapping["original_district"]
    split = mapping["split_district"]
    eff_year = mapping["effective_year"]
    
    # 筛选需要合并的条目
    mask = (processed_df["Year"] >= eff_year) & (processed_df["District"].isin([original, split]))
    
    # 按年份、州合并数值
    merged = processed_df[mask].groupby(["Year", "State"], as_index=False)["values"].sum()
    merged["District"] = original
    
    # 删除原条目，添加合并后的条目
    processed_df = processed_df[~mask]
    processed_df = pd.concat([processed_df, merged], ignore_index=True)

# 按年份、地区排序，得到最终结果
processed_df = processed_df.sort_values(["Year", "District"]).reset_index(drop=True)
print(processed_df)

可选：R语言实现 #

library(dplyr)

# 构建地区映射表
district_mapping <- tibble(
  original_district = c("Shajapur", "OldDist1", "OldDist2"),
  split_district = c("Agar Malwa", "NewDist1", "NewDist2"),
  effective_year = c(2014, 2016, 2018)
)

# 示例原始数据
df <- tibble(
  Year = c(2012, 2013, 2014, 2014, 2015, 2015),
  District = c("Shajapur", "Shajapur", "Shajapur", "Agar Malwa", "Shajapur", "Agar Malwa"),
  State = c("x", "x", "x", "x", "x", "x"),
  values = c(23, 21, 16, 8, 17, 10)
)

# 批量处理
processed_df <- df

for (i in 1:nrow(district_mapping)) {
  original <- district_mapping$original_district[i]
  split <- district_mapping$split_district[i]
  eff_year <- district_mapping$effective_year[i]
  
  # 筛选并合并数值
  to_merge <- processed_df %>% filter(Year >= eff_year, District %in% c(original, split))
  merged <- to_merge %>% 
    group_by(Year, State) %>% 
    summarise(values = sum(values), .groups = "drop") %>% 
    mutate(District = original)
  
  # 更新数据集
  processed_df <- processed_df %>% 
    filter(!(Year >= eff_year & District %in% c(original, split))) %>% 
    bind_rows(merged)
}

# 排序输出
processed_df <- processed_df %>% arrange(Year, District)
print(processed_df)

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