如何无需预处理合并汽车DataFrame？求精准匹配方案及代码

我有两个DataFrame：

df1 #

model_detail                                        series_detail
Nissan Sentra 430 Sedan -1123                            Nissan Sentra 
Acura ILX150 Base Sedan -17652                          Acura ILX Sedan
Acura TLX300 Base FWD -89789                            Acura TLX Sedan
Acura MDX450 Advanced Electric Hybrid AWD -55647        Acura MDX Sedan

df_2 #

Name           model                      Series      Amount         
  Acura       MDX 450 Hybrid                 MDX        45900
  Acura       ILX 150 Sedan                  ILX        156700
  Nissan      Sentra 430 Electric           Sentra      88897
  Nissan      Sunny 150                     Sunny       12333
  Acura       MDX 450 Electric               MDX        90000

需要将df1与df2合并，把Amount列加入df1中。涉及40个汽车品牌且格式各异，统一转换格式工作量极大。目前的处理方法是手动提取并校验，效率低且易出错：

def model_extractfunc(row):
    blocks = row['model_detail'].split()
    mid_index = 1
    if ('Hybrid' in blocks) or ('Electric' in blocks) or('Connect' in blocks):
        mid_index = 3
    return ' '.join(blocks[1:mid_index+1])

df1['extract_model'] = df1.apply(model_extractfunc, axis=1)

想知道有没有其他合并方式？模糊匹配是否可行？或者替代方案？要求保证Amount列匹配的准确性，同时提供代码示例。

1. 优化规则提取，减少手动干预 #

针对车型编号、关键词的规律，用正则表达式提取核心信息，匹配df2的字段：

代码示例 #

import pandas as pd
import re

# 处理df1：提取品牌、系列、车型编号、动力类型
def parse_df1(row):
    # 提取品牌（第一个单词）
    brand = row['model_detail'].split()[0]
    # 提取系列（匹配常见系列关键词，可扩展）
    series_match = re.search(r'(Sentra|ILX|TLX|MDX)', row['model_detail'])
    series = series_match.group() if series_match else None
    # 提取车型编号（3位数字）
    num_match = re.search(r'(\d{3})', row['model_detail'])
    model_num = num_match.group() if num_match else None
    # 提取动力类型
    power_type = None
    if 'Hybrid' in row['model_detail']:
        power_type = 'Hybrid'
    elif 'Electric' in row['model_detail']:
        power_type = 'Electric'
    return pd.Series([brand, series, model_num, power_type], 
                     index=['brand', 'series', 'model_num', 'power_type'])

df1_parsed = df1.join(df1.apply(parse_df1, axis=1))

# 处理df2：拆分model字段提取编号和动力类型
def parse_df2(row):
    # 提取车型编号
    num_match = re.search(r'(\d{3})', row['model'])
    model_num = num_match.group() if num_match else None
    # 提取动力类型
    power_type = None
    if 'Hybrid' in row['model']:
        power_type = 'Hybrid'
    elif 'Electric' in row['model']:
        power_type = 'Electric'
    return pd.Series([model_num, power_type], 
                     index=['model_num', 'power_type'])

df2_parsed = df2.join(df2.apply(parse_df2, axis=1))

# 优先按品牌+系列+编号+动力类型合并
merged = pd.merge(df1_parsed, df2_parsed, 
                  left_on=['brand', 'series', 'model_num', 'power_type'],
                  right_on=['Name', 'Series', 'model_num', 'power_type'],
                  how='left')

# 处理动力类型不匹配但核心字段匹配的情况
merged_backup = pd.merge(df1_parsed, df2_parsed,
                         left_on=['brand', 'series', 'model_num'],
                         right_on=['Name', 'Series', 'model_num'],
                         how='left', suffixes=('', '_backup'))

# 填充未匹配到的Amount
merged['Amount'] = merged['Amount'].fillna(merged_backup['Amount_backup'])
merged = merged.drop(columns=['Name', 'Series', 'model_num_backup', 'power_type_backup', 'model_backup'])

2. 模糊匹配+人工校验（保证准确性） #

用fuzzywuzzy库计算字符串相似度，设定阈值筛选匹配结果，仅对少量存疑项人工复核：

代码示例 #

from fuzzywuzzy import process, fuzz
import pandas as pd

# 为df2创建组合匹配键
df2['match_key'] = df2['Name'] + ' ' + df2['model'] + ' ' + df2['Series']

# 为df1创建组合匹配键
df1['match_key'] = df1['model_detail'] + ' ' + df1['series_detail']

# 定义模糊匹配函数，设定相似度阈值（比如80）
def fuzzy_match(row):
    match, score = process.extractOne(row['match_key'], df2['match_key'], scorer=fuzz.token_set_ratio)
    if score >= 80:
        return df2[df2['match_key'] == match]['Amount'].values[0]
    else:
        return None

df1['Amount'] = df1.apply(fuzzy_match, axis=1)

# 导出未匹配的行进行人工复核
unmatched = df1[df1['Amount'].isna()]
unmatched.to_csv('unmatched_cars.csv', index=False)

注意：使用前需安装依赖：pip install fuzzywuzzy python-Levenshtein

3. 建立品牌-系列映射字典 #

针对40个品牌，预先整理系列映射关系，再结合规则过滤车型细节：

代码示例 #

import pandas as pd
import re

# 预先整理品牌-系列映射（可根据实际品牌扩展）
brand_series_map = {
    'Nissan': ['Sentra', 'Sunny'],
    'Acura': ['ILX', 'TLX', 'MDX']
}

# 提取df1的品牌和系列
df1['brand'] = df1['model_detail'].str.split().str[0]
df1['series'] = df1['series_detail'].str.split().str[1]

# 提取df2的品牌和系列
df2['brand'] = df2['Name']
df2['series'] = df2['Series']

# 先按品牌+系列合并
merged = pd.merge(df1, df2, on=['brand', 'series'], how='left')

# 针对同系列多车型，过滤匹配编号和动力类型
def filter_amount(row):
    if pd.isna(row['Amount']):
        return None
    # 提取编号
    df1_num = re.search(r'(\d{3})', row['model_detail']).group() if re.search(r'(\d{3})', row['model_detail']) else ''
    df2_num = re.search(r'(\d{3})', row['model']).group() if re.search(r'(\d{3})', row['model']) else ''
    # 提取动力类型
    df1_power = 'Hybrid' if 'Hybrid' in row['model_detail'] else 'Electric' if 'Electric' in row['model_detail'] else ''
    df2_power = 'Hybrid' if 'Hybrid' in row['model'] else 'Electric' if 'Electric' in row['model'] else ''
    # 校验匹配
    if df1_num == df2_num and (df1_power == df2_power or df1_power == ''):
        return row['Amount']
    else:
        return None

merged['Amount'] = merged.apply(filter_amount, axis=1)
# 合并重复行，保留有效Amount
merged = merged.groupby(df1.columns.tolist())['Amount'].first().reset_index()

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