如何在面板数据中计算邻州variable的人口加权均值？

针对你的1600行面板数据需求，以下是两种主流工具的高效实现方案，逻辑清晰且处理速度快：

Python（Pandas）实现 #

import pandas as pd

# 假设你的数据集已加载到df中（示例数据如下）
df = pd.DataFrame({
    'state': ['AL', 'FL', 'GA', 'TN'],
    'year': [1990]*4,
    'population': [10,20,30,25],
    'variable': [0.023,0.5,0.01,0.3],
    'neighbors': ['FL,GA,TN', 'AL,GA', 'NC,SC,TN,FL,AL', 'AL,AK,GA,KT,MI,MO,VA']
})

# 1. 拆分邻州列，将每个邻州转为单独行
df_neighbors = df.assign(neighbor=df['neighbors'].str.split(',')).explode('neighbor')

# 2. 匹配邻州对应年份的人口与variable数据
df_merged = df_neighbors.merge(
    df[['state', 'year', 'population', 'variable']],
    left_on=['year', 'neighbor'],
    right_on=['year', 'state'],
    suffixes=('_original', '_neighbor')
)

# 3. 按原州-年份分组，计算人口加权均值
df_weighted = df_merged.groupby(['state_original', 'year']).apply(
    lambda x: pd.Series({
        'variable_weighted': (x['population_neighbor'] * x['variable_neighbor']).sum() / x['population_neighbor'].sum()
    })
).reset_index()

# 4. 合并回原数据集
df_final = df.merge(df_weighted, left_on=['state', 'year'], right_on=['state_original', 'year']).drop('state_original', axis=1)

R（Tidyverse）实现 #

library(tidyverse)

# 示例数据集
df <- tibble(
  state = c("AL", "FL", "GA", "TN"),
  year = rep(1990, 4),
  population = c(10, 20, 30, 25),
  variable = c(0.023, 0.5, 0.01, 0.3),
  neighbors = c("FL,GA,TN", "AL,GA", "NC,SC,TN,FL,AL", "AL,AK,GA,KT,MI,MO,VA")
)

# 一步完成拆分、匹配、计算、合并
df_final <- df %>%
  # 拆分邻州为多行
  mutate(neighbor = str_split(neighbors, ",")) %>%
  unnest(neighbor) %>%
  # 匹配邻州的年度数据
  left_join(df, by = c("year" = "year", "neighbor" = "state")) %>%
  # 按原州-年份分组计算加权均值
  group_by(state.x, year) %>%
  summarize(
    variable_weighted = sum(population.y * variable.y, na.rm = TRUE) / sum(population.y, na.rm = TRUE),
    .groups = "drop"
  ) %>%
  rename(state = state.x) %>%
  # 合并回原数据集
  left_join(df, by = c("state", "year"))

关键注意事项 #

• 若部分邻州在对应年份无数据（如示例中GA的邻州NC、SC），需用na.rm=TRUE跳过NA值，避免结果失效。
• 确保州编码（如AL）格式统一，大小写、缩写规则一致，避免匹配失败。
• 1600行数据量极小，上述方法无需额外性能优化，运行效率足够。

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