R语言中基于标准dataframe修正大型dataframe拼写错误并匹配字段

Got it, let's tackle this problem step by step! The core challenge here is fixing the spelling/case inconsistencies between DF_1's Val1 and DF_2's correct Val4 entries, then merging the right values into a target DataFrame. Below are two practical solutions depending on your specific use case.

方法1：模糊匹配（适合多样的拼写错误） #

If you have lots of varied spelling mistakes (not just case differences), fuzzy matching will automatically find the closest correct entry from DF_2. We'll use the fuzzywuzzy library for this—you'll need to install it first with pip install fuzzywuzzy python-Levenshtein.

First, define your original DataFrames:

import pandas as pd
from fuzzywuzzy import process

# Original DF_1 with typos
df1 = pd.DataFrame({
    'Val1': ['COPPAR', 'Bittar Gourd', 'Blackbery'],
    'Val2': ['Ert', 'vegetble', 'd']
})

# DF_2 with correct values
df2 = pd.DataFrame({
    'Val4': ['Copper', 'Bitter Gourd', 'Blackberry'],
    'Val5': ['Metal', 'Vegetable', 'Fruit'],
    'Type': ['A-I', 'B-II', 'C-III']
})

Next, create a helper function to match each entry in df1['Val1'] to the closest correct entry in df2['Val4'], then pull in the corresponding Val5 and Type:

def fetch_correct_data(input_val):
    # Find the closest match in df2['Val4'], return match, similarity score, and index
    match, score, idx = process.extractOne(input_val, df2['Val4'])
    # Only accept matches with a similarity score >=80 (adjust this threshold as needed)
    if score >= 80:
        return pd.Series([match, df2.loc[idx, 'Val5'], df2.loc[idx, 'Type']])
    else:
        # Return NaN if no good match is found
        return pd.Series([None, None, None])

# Apply the function to df1 and add the new columns
df1[['New_Val1', 'New_Val2', 'Type']] = df1['Val1'].apply(fetch_correct_data)

# View the final result
print(df1)

This will output:

Val1     Val2    New_Val1    New_Val2   Type
0        COPPAR      Ert      Copper       Metal   A-I
1  Bittar Gourd  vegetble  Bitter Gourd  Vegetable  B-II
2     Blackbery        d  Blackberry       Fruit  C-III

方法2：手动标准化+合并（适合少量固定拼写错误） #

If you only have a few predictable typos, you can clean the strings manually and use pandas' merge method (no extra libraries needed):

import pandas as pd

# Original DataFrames (same as before)
df1 = pd.DataFrame({
    'Val1': ['COPPAR', 'Bittar Gourd', 'Blackbery'],
    'Val2': ['Ert', 'vegetble', 'd']
})

df2 = pd.DataFrame({
    'Val4': ['Copper', 'Bitter Gourd', 'Blackberry'],
    'Val5': ['Metal', 'Vegetable', 'Fruit'],
    'Type': ['A-I', 'B-II', 'C-III']
})

# Clean strings in both DataFrames to match each other
df1['clean_val1'] = df1['Val1'].str.lower().replace({
    'coppar': 'copper',
    'bittar gourd': 'bitter gourd',
    'blackbery': 'blackberry'
})
df2['clean_val4'] = df2['Val4'].str.lower()

# Merge the DataFrames on the cleaned columns
merged_df = df1.merge(df2, left_on='clean_val1', right_on='clean_val4', how='left')

# Rename columns and keep only the target fields
target_df = merged_df.rename(columns={
    'Val4': 'New_Val1',
    'Val5': 'New_Val2'
})[['New_Val1', 'New_Val2', 'Type']]

print(target_df)

This will give you the same correct output as the fuzzy matching method.

Which method to choose? #

• Use fuzzy matching if you have many varied typos or don't want to manually list every possible error.
• Use manual cleaning + merge if your typos are fixed and limited—it's faster and doesn't require external libraries.

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