使用dplyr::left_join合并数据时出现NA值及列重复问题排查

问题描述 #

用dplyr的left_join()合并两个dataframe时，新生成的列全是NA值；偶尔能得到有实际值的结果，但合并列会重复，只有用不指定type参数的base R merge()函数才不会出现重复。已经确认匹配ID变量REPORTER和PARTNER都是相同字符类且全大写，还是找不到问题原因。

代码与数据示例 #

# 加载数据
country_dyad <- readRDS(url("https://www.dropbox.com/scl/fi/q0ivr9d3lgjpdlxq88a4u/country_dyad.rds?rlkey=vc4i6j7bgbd3xaftkdk4yqhny&dl=1"))
trade <- readRDS(url("https://www.dropbox.com/scl/fi/wxism5ytikswdhyy27rle/trade.rds?rlkey=aonsyj1q2uied1t8y9necsuk0&dl=1"))

# 使用left_join合并
merge <- left_join(country_dyad, trade, by = c("REPORTER", "PARTNER"))

# 输出结果
merge
#    REPORTER PARTNER Chapter_ID Chapter_sum
# 1       afg     afg         NA          NA
# 2       alb     afg         NA          NA
# 3       ago     afg         NA          NA
# 4       atg     afg         NA          NA
# 5       arg     afg         NA          NA
# 6       arm     afg         NA          NA
# 7       aus     afg         NA          NA
# 8       aut     afg         NA          NA
# 9       bhr     afg         NA          NA
# 10      bgd     afg         NA          NA

# 注：有时执行left_join会出现合并列重复的情况

排查与解决方法 #

1. 检查匹配键的隐形差异 #

虽然你确认了大小写和变量类型，但字符串可能存在隐形空格、制表符或编码差异，导致表面相同的字符实际不匹配。用以下代码验证：

# 查看两个数据框中匹配键的交集数量
length(intersect(country_dyad$REPORTER, trade$REPORTER))
length(intersect(country_dyad$PARTNER, trade$PARTNER))

# 随机抽取值验证是否真的匹配
sample_cd <- sample(country_dyad$REPORTER, 5)
sapply(sample_cd, function(x) x %in% trade$REPORTER)

如果交集为空或部分值不匹配，先清理字符串：

library(stringr)
# 清理所有隐形空格和特殊字符
country_dyad <- country_dyad %>%
  mutate(across(c(REPORTER, PARTNER), ~ str_trim(str_remove_all(., "\\s+"))))

trade <- trade %>%
  mutate(across(c(REPORTER, PARTNER), ~ str_trim(str_remove_all(., "\\s+"))))

2. 检查匹配键的重复组合 #

如果其中一个数据框存在重复的REPORTER-PARTNER组合，left_join会生成重复行，看起来像是列重复。检查重复情况：

# 检查country_dyad中的重复组合
country_dyad %>% count(REPORTER, PARTNER) %>% filter(n > 1)
# 检查trade中的重复组合
trade %>% count(REPORTER, PARTNER) %>% filter(n > 1)

如果有重复，先去重再合并：

# 对trade去重，保留每个组合的第一条记录
trade_unique <- trade %>% distinct(REPORTER, PARTNER, .keep_all = TRUE)
merge_clean <- left_join(country_dyad, trade_unique, by = c("REPORTER", "PARTNER"))

3. 对比merge和left_join的匹配逻辑差异 #

base R的merge()默认会自动匹配所有同名列，而left_join只匹配你指定的键。如果之前用merge()没指定by参数，可能是两个数据框还有其他同名列被用作匹配键，导致结果不同。验证这一点：

# 查看两个数据框的所有列名
colnames(country_dyad)
colnames(trade)

# 用merge默认参数合并，查看结果列名（后缀.x/.y的列是额外匹配的同名列）
merge_base <- merge(country_dyad, trade, all.x = TRUE)
colnames(merge_base)

如果发现额外的匹配列，需要在left_join中明确指定所有需要匹配的键，或者删除不需要的同名列后再合并。

4. 重置数据框索引 #

极少数情况下，行索引或顺序会导致匹配异常，重置索引后再尝试：

country_dyad <- country_dyad %>% mutate(row_id = row_number()) %>% select(row_id, everything())
trade <- trade %>% mutate(row_id = row_number()) %>% select(row_id, everything())

merge_reset <- left_join(country_dyad, trade, by = c("REPORTER", "PARTNER"))

内容的提问来源于stack exchange，提问作者Chris T.