基于子串匹配在DataFrame中填充缺失的serial_num字段

核心思路 #

先将df2中所有名称列（别名列）转换为长格式，建立每个别名与对应serial_num的映射关系，再通过子串匹配为df1中缺失serial_num的行找到对应值，最后回填到原DataFrame。这种方法避免了逐行迭代，效率更高。

分步实现代码 #

1. 导入依赖并定义示例数据

import pandas as pd
import numpy as np

# 示例df1
df1 = pd.DataFrame({
    'serial_num': ['39jr93j', pd.NA, '21pr12n'],
    'name': ['Phyl', 'Gil', 'Ann']
})

# 示例df2
df2 = pd.DataFrame({
    'serial_num': ['5c2v40l', '21pr12n', '39jr93j'],
    'name': ['Jill', 'Karen', 'Phyllis'],
    'alternate_nombre': ['Jyl.', 'Caren.', 'Fillus'],
    'different_name': ['Jill Smith.', 'Karen Ann.', 'Phil'],
    'nm_alternate': ['Gil Smyth', 'Caryn Anne', 'Phyllus']
})

2. 预处理df2，构建别名-序列号映射

将df2的多列别名转换为单列格式，每个别名对应一个serial_num：

# 提取所有名称列（排除serial_num）
name_columns = df2.columns.difference(['serial_num'])

# 转换为长格式，清理空值
df2_name_mapping = df2.melt(
    id_vars='serial_num',
    value_vars=name_columns,
    value_name='alias'
).dropna(subset=['alias'])

3. 矢量化匹配并填充缺失值

使用numpy矢量化操作替代逐行apply，提升大数据量下的效率：

# 筛选df1中需要填充的行及掩码
missing_mask = df1['serial_num'].isna()
missing_df = df1[missing_mask].copy()

# 统一转换为小写，避免大小写匹配问题
aliases = df2_name_mapping['alias'].str.lower().values
serials = df2_name_mapping['serial_num'].values
missing_names = missing_df['name'].str.lower().values

# 生成子串匹配布尔矩阵：每个name是否在每个alias中
matches = np.char.find(aliases, missing_names[:, None]) != -1

# 找到每个name对应的第一个匹配serial_num（若需多匹配处理可调整逻辑）
match_indices = np.argmax(matches, axis=1)
missing_df['serial_num'] = serials[match_indices]

# 回填到原df1
df1.loc[missing_mask, 'serial_num'] = missing_df['serial_num']

4. 查看结果

print(df1)

输出结果：

serial_num  name
0    39jr93j  Phyl
1    5c2v40l   Gil
2    21pr12n   Ann

关键说明 #

• 多匹配处理：如果一个name匹配到多个serial_num，上述代码取第一个匹配结果。若需业务规则筛选（如取出现次数最多的），可在df2_name_mapping中先按alias和serial_num分组计数，再取计数最高的。
• 无匹配处理：若某个name未找到匹配，可修改match_indices逻辑，比如：

  # 无匹配时保留NA
  has_match = matches.any(axis=1)
  missing_df.loc[has_match, 'serial_num'] = serials[np.argmax(matches[has_match], axis=1)]
  

• 效率优化：numpy矢量化操作比apply快10-100倍，适合处理十万级以上的数据量。

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