Pandas DataFrame按条件匹配填充pred_id、pred_image空值的实现问题

需求 #

对pred_id为NaN的行，检查对应image_name的文件名前缀（去掉.jpg后缀）是否存在于pred_image列的路径取值中，若存在则将匹配行的pred_id、pred_image值填充到当前行的对应空列，不存在则留空。

完整实现代码 #

import pandas as pd
import numpy as np

# 构造示例数据
data = {'pred_id': [2051,2052,2053,2054,2055,np.nan,np.nan,np.nan],
        'pred_image':['app_images/prediction/162000_p_r.jpg', 'app_images/prediction/162100_p_r.jpg','app_images/prediction/162200_p_r.jpg','app_images/prediction/162300_p_r.jpg','app_images/prediction/162400_p_r.jpg',np.nan,np.nan,np.nan],
        'image_name':['162000.jpg','162100.jpg','162200.jpg','162300.jpg','162400.jpg','162100.jpg','162200.jpg','162700.jpg'],
        'pred_label':[1,2,4,1,2,1,1,2],
        'vol':[345,124,312,234,564,212,313,414]}
df = pd.DataFrame(data)

# 步骤1：构建前缀与对应pred_id、pred_image的映射字典
valid_rows = df[df['pred_id'].notna()].copy()
# 从pred_image路径中提取匹配前缀
valid_rows['prefix'] = valid_rows['pred_image'].str.split('/').str[-1].str.split('_').str[0]
prefix_map = valid_rows.set_index('prefix')[['pred_id', 'pred_image']].to_dict('index')

# 步骤2：对空值行做匹配填充
na_mask = df['pred_id'].isna()
# 提取待匹配行的image前缀
df.loc[na_mask, 'match_prefix'] = df.loc[na_mask, 'image_name'].str[:-4]
# 匹配填充对应列
df.loc[na_mask, 'pred_id'] = df.loc[na_mask, 'match_prefix'].map(lambda x: prefix_map.get(x, {}).get('pred_id', np.nan))
df.loc[na_mask, 'pred_image'] = df.loc[na_mask, 'match_prefix'].map(lambda x: prefix_map.get(x, {}).get('pred_image', np.nan))
# 删除临时辅助列
df = df.drop(columns=['match_prefix'])

# 输出结果
print(df)

处理结果 #

执行代码后输出的DataFrame如下：

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