R语言Data Frame合并/连接失败问题求助

问题描述 #

两个数据框frank_stops和ohio_data均包含字符型列CTYNAME，实际存在19个重叠的县名（例如"Butler County"），但合并时出现异常：

• 使用inner_join返回0行结果
• 使用full_join返回行数等于两个数据框行数之和，重叠县名未合并为一行，而是分别保留两行

当前合并代码：

frank_stops = 
  frank_stops %>%
  filter(State == "Ohio") %>%
  select(4) %>% 
  distinct() %>%
  rename(CTYNAME = 'County') %>% 
  mutate(CTYNAME = paste(CTYNAME, " County")) %>% 
  data.frame()
frank_stops$CTYNAME <- as.character(frank_stops$CTYNAME)

ohio_data = 
  ohio_data %>% 
  rename(CTYNAME = 'CTYNAME') %>%
  filter(AGEGRP == 0, YEAR == 12) %>% 
  data.frame()
ohio_data$CTYNAME <- as.character(ohio_data$CTYNAME)

together = full_join(frank_stops, ohio_data, by="CTYNAME")

控制台输出（已翻译）：

Rows: 20064 Columns: 80── 列规格说明 ─────────────────────────────────────────────────────────────────────────────────────────────────
分隔符: ","
字符型(4): SUMLEV, COUNTY, STNAME, CTYNAME
数值型(76): STATE, YEAR, AGEGRP, TOT_POP, TOT_MALE, TOT_FEMALE, WA_MALE, WA_FEMALE, BA_MALE, BA_FEMALE, IA_MALE, IA_FEM...
ℹ 使用spec()获取完整列规格。
ℹ 指定列类型或设置show_col_types = FALSE可关闭此提示。
Rows: 153 Columns: 4── 列规格说明 ─────────────────────────────────────────────────────────────────────────────────────────────────
分隔符: ","
字符型(4): Date of Visit, Town/City, State, County
ℹ 使用spec()获取完整列规格。
ℹ 指定列类型或设置show_col_types = FALSE可关闭此提示。
Joining, by = "CTYNAME"

问题根源 #

这种情况几乎都是字符串表面相同但实际存在不可见字符或格式差异导致的，常见差异包括：

• 前后空格（含全角/半角空格、制表符）
• 大小写不一致（比如"Butler county"和"Butler County"）
• 隐藏的换行符、特殊编码字符
• 多余的连续空格

解决步骤 #

1. 确认字符串实际差异 #

先提取两数据框的CTYNAME列，对比重叠项的真实内容：

# 提取县名向量
frank_counties <- frank_stops$CTYNAME
ohio_counties <- ohio_data$CTYNAME

# 查看理论上的重叠项是否被识别
common <- intersect(frank_counties, ohio_counties)
print(common)

# 以"Butler County"为例，查看字节级差异
frank_bc <- frank_counties[frank_counties == "Butler County"]
ohio_bc <- ohio_counties[ohio_counties == "Butler County"]

# 输出字节详情，对比差异
charToRaw(frank_bc)
charToRaw(ohio_bc)

2. 标准化字符串格式 #

对两个数据框的CTYNAME列做统一清洗：

library(stringr)

# 定义标准化函数
clean_county_name <- function(x) {
  x %>%
    # 去除前后所有空白字符
    str_trim() %>%
    # 统一为首字母大写格式
    str_to_title() %>%
    # 把连续空白替换为单个半角空格
    str_squish() %>%
    # 统一后缀为" County"
    str_replace("(?i)\\s*county$", " County")
}

# 应用清洗到两个数据框
frank_stops <- frank_stops %>% mutate(CTYNAME = clean_county_name(CTYNAME))
ohio_data <- ohio_data %>% mutate(CTYNAME = clean_county_name(CTYNAME))

3. 重新执行合并 #

清洗后再尝试合并：

# 内连接，应返回19行
together_inner <- inner_join(frank_stops, ohio_data, by = "CTYNAME")
nrow(together_inner)

# 全连接，行数应为：frank行数 + ohio行数 - 19
together_full <- full_join(frank_stops, ohio_data, by = "CTYNAME")
nrow(together_full)

额外排查点 #

如果清洗后仍有问题，检查：

• 是否存在拼写差异（比如"Butler County"和"Butler Cty"），可使用fuzzyjoin包的stringdist_join做模糊匹配
• 数据框中是否有重复的CTYNAME行，先执行distinct(CTYNAME)去重再合并

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