FEC数据集右合并遇多对多关系及数据重复问题求助

问题根源 #

你遇到的大量重复行，核心是原始数据里存在重复的党派条目（比如2018数据里有两行民主党记录），以及非候选人的汇总行（比如"District Votes:"），破坏了STATE/DISTRICT/PARTY合并键的唯一性，导致right_join时出现多对多匹配。

第一步：先清洗两个原始数据集 #

先过滤无效行、去重，确保每个(STATE, DISTRICT, PARTY)组合唯一：

library(tidyverse)
library(readxl)

# 清洗2014年数据
Election_2014_clean <- read_excel("2014 US House Election by State.xlsx", na = c("", "NA")) %>%
  # 只保留民主党(D)和共和党(R)
  filter(PARTY %in% c("D", "R")) %>%
  # 移除"District Votes:"汇总行
  filter(!str_detect(`TOTAL VOTES`, "District Votes:")) %>%
  # 去掉无明确选区的行（比如Alabama NA）
  drop_na(DISTRICT) %>%
  # 确保每个选区的每个党派仅保留一行
  distinct(STATE, DISTRICT, PARTY, .keep_all = TRUE)

# 清洗2018年数据
Election_2018_clean <- read_excel("2018 US House Election by State.xlsx", na = c("", "NA")) %>%
  filter(PARTY %in% c("D", "R")) %>%
  filter(!str_detect(`TOTAL VOTES`, "District Votes:")) %>%
  drop_na(DISTRICT) %>%
  distinct(STATE, DISTRICT, PARTY, .keep_all = TRUE)

第二步：执行合并 #

清洗后再用right_join，此时合并键唯一，不会出现重复：

Election_14.18 <- Election_2014_clean %>%
  right_join(Election_2018_clean, by = c("STATE", "DISTRICT", "PARTY"))

额外需求：保留选区总票数 #

如果需要同时保留各选区的总投票数，可以单独提取汇总行再合并：

# 提取2014年选区总票数
total_votes_2014 <- Election_2014 %>%
  filter(str_detect(`TOTAL VOTES`, "District Votes:")) %>%
  select(STATE, DISTRICT, total_votes_2014 = `GENERAL VOTES`) %>%
  drop_na(DISTRICT)

# 提取2018年选区总票数
total_votes_2018 <- Election_2018 %>%
  filter(str_detect(`TOTAL VOTES`, "District Votes:")) %>%
  select(STATE, DISTRICT, total_votes_2018 = `GENERAL VOTES`) %>%
  drop_na(DISTRICT)

# 合并候选人数据与总票数
Election_14.18_with_total <- Election_14.18 %>%
  left_join(total_votes_2014, by = c("STATE", "DISTRICT")) %>%
  left_join(total_votes_2018, by = c("STATE", "DISTRICT"))

内容的提问来源于stack exchange，提问作者D. Robert