基于多时间维度人口数据，求各郡县相邻郡县平均人口的技术问询

我有两个DataFrame：

• 第一个包含郡县标识（county_ID）及对应郡县的人口数据，实际数据涵盖多年来每月的人口值（示例伪数据为固定时间点）；
• 第二个DataFrame记录每个郡县的相邻郡县，部分郡县有多个邻居，部分无邻居。

我的目标是计算每个郡县在对应年月的相邻郡县平均人口，需要输出neighbor_county_agg（聚合的相邻郡县列表）和mean_neighbor_county（相邻郡县平均人口）两列。之前尝试按date和county_ID分组后用summarize，但结果不随年月变化，不确定是否需要用循环处理多邻居的情况。

伪数据 #

# df1（含期望输出列示例）
structure(list(county_ID = c("A", "B", "C", "D", "E", "F"), 
               population = c(100, 350, 200, 100, 50, 80), 
               neighbor_county_agg = c("D, B", "A, F", "NA", "A, F", "NA", "D, B, G"), 
               mean_neighbor_county = c("100 + 350 / 2", "100+80 / 2", "NA", "100 + 80 / 2", "NA", "100 + 350 + 50 /3")), 
          row.names = c(NA, -6L), class = c("tbl_df", "tbl", "data.frame"))

# df2（邻居关系表）
structure(list(county_ID = c("A", "A", "B", "B", "C", "D"), 
               neighbor_county = c("D", "B", "A", "F", "NA", "A")), 
          row.names = c(NA, -6L), class = c("tbl_df", "tbl", "data.frame"))

尝试过的代码（未成功） #

df1 <- df1 %>% mutate(neighbor_population = df1$population[df1$neighbor_county_agg, df2$neighbor_county)]) 

# 注：示例伪数据不含date字段，实际代码会分组
group_by(date, county_ID) %>% 
  summarise(mean_population=mean(neighbor_population),
            .groups = 'drop')

核心思路是通过关联邻居关系表和人口表，按年月+郡县分组后聚合信息，无需循环，用dplyr即可实现：

步骤1：清理邻居关系表 #

先过滤掉neighbor_county为无效"NA"的记录：

library(dplyr)
library(stringr)

df2_clean <- df2 %>%
  filter(neighbor_county != "NA")

步骤2：关联带时间维度的人口数据 #

假设你的实际df1包含date字段（如2020-01格式的年月），将邻居表与人口表按邻居郡县+年月匹配，关联对应人口：

# 实际df1结构应为：county_ID, date, population
df_merged <- df2_clean %>%
  left_join(df1, by = c("neighbor_county" = "county_ID", "date" = "date")) %>%
  rename(neighbor_population = population) %>%
  left_join(df1, by = c("county_ID", "date"))

步骤3：分组聚合计算结果 #

按date和county_ID分组，聚合邻居列表并计算平均人口：

result <- df_merged %>%
  group_by(date, county_ID, population) %>%
  summarise(
    neighbor_county_agg = ifelse(n() == 0, NA, str_c(neighbor_county, collapse = ", ")),
    mean_neighbor_county = ifelse(n() == 0, NA, mean(neighbor_population, na.rm = TRUE)),
    .groups = "drop"
  )

适配无date伪数据的演示 #

如果用你提供的无时间维度伪数据，代码可简化为：

# 关联邻居与人口数据
df_merged <- df2_clean %>%
  left_join(df1 %>% select(county_ID, population), by = c("neighbor_county" = "county_ID")) %>%
  rename(neighbor_pop = population) %>%
  left_join(df1 %>% select(county_ID, population), by = "county_ID")

# 聚合计算
result <- df_merged %>%
  group_by(county_ID, population) %>%
  summarise(
    neighbor_county_agg = str_c(neighbor_county, collapse = ", "),
    mean_neighbor_county = mean(neighbor_pop, na.rm = TRUE),
    .groups = "drop"
  )

# 补充无邻居的郡县记录
result <- df1 %>%
  select(county_ID, population) %>%
  left_join(result, by = c("county_ID", "population")) %>%
  mutate(
    neighbor_county_agg = ifelse(is.na(neighbor_county_agg), "NA", neighbor_county_agg),
    mean_neighbor_county = ifelse(is.na(mean_neighbor_county), "NA", mean_neighbor_county)
  )

关键说明 #

• 结果会自动匹配对应年月的人口数据，确保随时间变化；
• 无邻居的郡县，neighbor_county_agg和mean_neighbor_county会显示为NA；
• 用str_c聚合邻居列表，若未安装stringr包，可替换为paste(neighbor_county, collapse = ", ")。

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