使用R语言通过部分匹配合并数据框的方法

Hey there! Let's figure out how to merge your two DataFrames using partial matches on the Country column. I'll walk you through a few practical methods, depending on the types of mismatches you're dealing with (like extra parentheses, suffixes, or different name variations).

First, let's recreate your two DataFrames to work with:

import pandas as pd

# Build df1
df1 = pd.DataFrame({
    'Continent': ['Europe', 'Asia', 'africa', 'africa', 'africa'],
    'Country': ['Russia', 'Myanmar (Burma)', 'Benin', 'Botswana', 'Burkina']
})

# Build df2
df2 = pd.DataFrame({
    'Continent': ['Europe', 'Asia', 'africa', 'africa', 'africa'],
    'Country': ['Russian Federation', 'Myanmar', 'Benin,new', 'Botswana', 'Burkina']
})

Method 1: String Cleaning + Exact Match #

This works great for regularized mismatches (like countries with extra parenthetical notes, commas, or suffixes). We'll clean up the Country columns first to remove redundant info, then do an exact merge.

Step 1: Write a cleaning function #

Let's make a function to strip out extra bits we don't need:

import re

def clean_country(country_name):
    # Remove parentheses and everything inside (e.g., "Myanmar (Burma)" → "Myanmar")
    cleaned = re.sub(r'\(.*?\)', '', country_name).strip()
    # Remove commas and everything after (e.g., "Benin,new" → "Benin")
    cleaned = re.sub(r',.*', '', cleaned).strip()
    # Convert to lowercase to avoid case sensitivity issues
    return cleaned.lower()

Step 2: Add cleaned columns and merge #

Now apply the function to both DataFrames and merge on the cleaned column:

# Add cleaned country columns
df1['clean_country'] = df1['Country'].apply(clean_country)
df2['clean_country'] = df2['Country'].apply(clean_country)

# Merge using the cleaned column, keep original columns with suffixes
merged_df = pd.merge(df1, df2, on='clean_country', suffixes=('_df1', '_df2'))

# Optional: Drop the temporary cleaned column if you don't need it
merged_df = merged_df.drop('clean_country', axis=1)

This handles matches like Myanmar (Burma) ↔ Myanmar and Benin ↔ Benin,new perfectly. But it won't catch Russia ↔ Russian Federation since their cleaned names are still different. For those cases, we need fuzzy matching.

Method 2: Fuzzy Matching with RapidFuzz #

For irregular name variations (like "Russia" vs "Russian Federation"), we can use fuzzy matching to calculate string similarity and match based on a threshold. rapidfuzz is recommended here because it's faster than the older fuzzywuzzy library.

Step 1: Install dependencies #

# Install rapidfuzz (faster alternative to fuzzywuzzy)
pip install rapidfuzz

Step 2: Match countries using fuzzy similarity #

We'll use process.extractOne to find the closest match in df2 for each country in df1:

from rapidfuzz import process, fuzz

def find_best_match(row, target_countries):
    # Use token_set_ratio to ignore word order/extra words (great for country names)
    match, similarity_score, _ = process.extractOne(
        row['Country'], 
        target_countries, 
        scorer=fuzz.token_set_ratio
    )
    # Only return the match if similarity is above a threshold (adjust as needed)
    if similarity_score >= 80:
        return match
    return None

# Add a column with the best match from df2 to df1
df1['matched_country'] = df1.apply(lambda x: find_best_match(x, df2['Country']), axis=1)

# Merge using the matched country column
merged_df = pd.merge(df1, df2, left_on='matched_country', right_on='Country', suffixes=('_df1', '_df2'))

The token_set_ratio scorer is perfect here because it compares the set of words in each string—so "Russia" and "Russian Federation" will get a high score since they share the core term. Adjust the threshold (80 in this example) based on how strict you want matches to be.

Method 3: Cross Join + Similarity Filter (Small Datasets) #

If your dataset is small, you can do a cross join (all possible pairs of rows), calculate similarity for each pair, then filter for good matches. This lets you see all potential matches before deciding which to keep.

# Create a cross join of the two DataFrames
cross_join_df = df1.assign(temp_key=1).merge(df2.assign(temp_key=1), on='temp_key').drop('temp_key', axis=1)

# Calculate similarity for each country pair
cross_join_df['similarity'] = cross_join_df.apply(
    lambda x: fuzz.token_set_ratio(x['Country_x'], x['Country_y']), 
    axis=1
)

# Filter for matches with similarity >=80, then keep only the best match per df1 country
merged_df = cross_join_df[cross_join_df['similarity'] >= 80] \
    .sort_values('similarity', ascending=False) \
    .drop_duplicates('Country_x')

Pro Tips #

• Speed up large datasets: If you have a lot of rows, first filter by Continent to only compare countries in the same continent—this cuts down on the number of similarity calculations:

  merged_list = []
  for continent in df1['Continent'].unique():
      # Only work with rows from the same continent
      df1_sub = df1[df1['Continent'] == continent]
      df2_sub = df2[df2['Continent'] == continent]
      # Find matches within the continent
      df1_sub['matched_country'] = df1_sub.apply(lambda x: find_best_match(x, df2_sub['Country']), axis=1)
      # Merge and add to the list
      merged_list.append(pd.merge(df1_sub, df2_sub, left_on='matched_country', right_on='Country', suffixes=('_df1', '_df2')))
  # Combine all continent merges
  merged_df = pd.concat(merged_list)
  

• Tune the threshold: If you're getting too many false matches, raise the threshold (e.g., to 90). If you're missing valid matches, lower it (e.g., to 70).

内容的提问来源于stack exchange，提问作者Hanish Kiran Sanghrajka