如何在R中基于列的非精确匹配值合并两个DataFrame

Hey there! It sounds like you're stuck merging two DataFrames where the key columns (DF1$Districts and DF2$nom_comptage) don't have perfect matches, and grepl isn't working as expected. Let's break down some practical solutions for this.

First, let's recap the mismatch issues we can see in your data:

• CSC (Côte Sainte-Catherine) (DF1) vs CSC (DF2)
• Pont Jacques-Cartier (DF1) vs Pont Jacques-Ca (DF2)
• Rachel/Hôtel de Ville (DF1) vs Rachel/Hôtel de (DF2)
• Truncated or shortened names across both frames

Solution 1: Use the fuzzyjoin Package (Recommended) #

This package is built specifically for merging DataFrames with non-exact matches, and it's way more efficient than writing manual grepl loops.

Step 1: Install and load the package

install.packages("fuzzyjoin")
library(fuzzyjoin)

Option A: Regex-Based Matching

If you want to match using partial string matches (e.g., a shorter name in DF2 is part of the full name in DF1), use regex_left_join:

# Merge DF1 with DF2, matching where DF2$nom_comptage is a substring of DF1$Districts
merged_df <- regex_left_join(
  DF1, 
  DF2, 
  by = c("Districts" = "nom_comptage"),
  ignore_case = TRUE  # Optional: Ignore case differences
)

Option B: String Distance Matching

If the matches are off due to typos, truncations, or small character differences, use stringdist_left_join which calculates how similar two strings are (using edit distance):

# Merge with a maximum allowed edit distance of 5 (adjust this as needed)
merged_df <- stringdist_left_join(
  DF1, 
  DF2, 
  by = c("Districts" = "nom_comptage"),
  max_dist = 5,  # Higher = more lenient matching
  method = "lv"  # Levenshtein distance: counts insertions/deletions/substitutions
)

Solution 2: Clean Strings First for Better Matches #

Sometimes cleaning up the key columns before merging can make matches more accurate. Here's how to standardize both columns:

Clean DF1$Districts

library(dplyr)

DF1_clean <- DF1 %>%
  mutate(
    # Remove parentheses and their content (e.g., "CSC (Côte Sainte-Catherine)" → "CSC")
    Districts_clean = gsub("\\s\\(.*\\)", "", Districts),
    # Remove underscores and numbers (e.g., "Maisonneuve_2" → "Maisonneuve")
    Districts_clean = gsub("_\\d+", "", Districts_clean)
  )

Clean DF2$nom_comptage

DF2_clean <- DF2 %>%
  mutate(
    # Remove underscores and numbers
    nom_comptage_clean = gsub("_\\d+", "", nom_comtage),
    # Fix truncated names (example: "Pont Jacques-Ca" → "Pont Jacques-Cartier")
    nom_comptage_clean = ifelse(nom_comptage_clean == "Pont Jacques-Ca", "Pont Jacques-Cartier", nom_comptage_clean),
    # Fix "Rachel/Hôtel de" → "Rachel/Hôtel de Ville"
    nom_comptage_clean = ifelse(nom_comptage_clean == "Rachel/Hôtel de", "Rachel/Hôtel de Ville", nom_comptage_clean)
  )

Merge the cleaned DataFrames

merged_clean_df <- merge(DF1_clean, DF2_clean, by.x = "Districts_clean", by.y = "nom_comptage_clean", all.x = TRUE)

Solution 3: Manual grepl Loop (For Small Data Only) #

If you only have a small dataset and want full control, you can loop through each row to find matches. Note: This is not efficient for large datasets!

# Add a matching column to DF2
DF2$matched_district <- NA

for (i in 1:nrow(DF2)) {
  # Find rows in DF1 where Districts contains DF2$nom_comtage[i]
  match_positions <- which(grepl(DF2$nom_comtage[i], DF1$Districts, ignore.case = TRUE))
  
  if (length(match_positions) > 0) {
    DF2$matched_district[i] <- DF1$Districts[match_positions[1]]  # Take the first match
  }
}

# Merge the two DataFrames
merged_loop_df <- merge(DF1, DF2, by.x = "Districts", by.y = "matched_district", all.x = TRUE)

Final Notes #

• Start with fuzzyjoin for most cases—it's the most scalable and least error-prone.
• Adjust the max_dist in stringdist_left_join based on your data: smaller values mean stricter matches.
• For edge cases (like truncated names), manual string cleaning will give you the most precise results.

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