基于多条件用其他DataFrame数据填充Pandas中的NaN值
2026-8-23
问题：基于name和month匹配填充DataFrame中的NaN值
我有一个包含NaN值的DataFrame dfnan，需要根据name和month的匹配条件，使用另一个DataFrame dffill中的数据填充这些NaN值。
dfnan数据如下：
index result result result result result month name year 1 4 4 4 4 4 1 Bears 2022 2 20 20 20 20 20 2 Bears 2022 3 8 8 8 8 8 3 Bears 2022 4 5 5 5 5 5 4 Bears 2022 5 3 3 3 3 3 5 Bears 2022 6 19 19 19 19 19 6 Bears 2022 7 nan nan nan nan nan 7 Bears 2022 8 nan nan nan nan nan 8 Bears 2022 9 nan nan nan nan nan 9 Bears 2022 10 nan nan nan nan nan 10 Bears 2022 11 nan nan nan nan nan 11 Bears 2022 12 nan nan nan nan nan 12 Bears 2022 13 5 5 5 5 5 1 Eagles 2022 14 9 9 9 9 9 2 Eagles 2022 15 12 12 12 12 12 3 Eagles 2022 16 21 21 21 21 21 4 Eagles 2022 17 2 2 2 2 2 5 Eagles 2022 18 17 17 17 17 17 6 Eagles 2022 19 nan nan nan nan nan 7 Eagles 2022 20 nan nan nan nan nan 8 Eagles 2022 21 nan nan nan nan nan 9 Eagles 2022 22 nan nan nan nan nan 10 Eagles 2022 23 nan nan nan nan nan 11 Eagles 2022 24 nan nan nan nan nan 12 Eagles 2022
用于填充的dffill数据如下：
index month name 1 2 3 4 5 1 7 Bears 10 25 14 4 22 2 8 Bears 5 8 6 24 18 3 9 Bears 18 8 8 14 16 4 10 Bears 19 11 13 8 9 5 11 Bears 16 25 3 9 6 6 12 Bears 17 11 18 3 24 7 7 Eagles 15 24 11 2 25 8 8 Eagles 1 7 18 9 17 9 9 Eagles 11 11 8 18 20 10 10 Eagles 16 20 3 24 2 11 11 Eagles 10 24 6 4 19 12 12 Eagles 8 16 12 19 22
期望得到的最终结果：
index result result result result result month name year 1 4 4 4 4 4 1 Bears 2022 2 20 20 20 20 20 2 Bears 2022 3 8 8 8 8 8 3 Bears 2022 4 5 5 5 5 5 4 Bears 2022 5 3 3 3 3 3 5 Bears 2022 6 19 19 19 19 19 6 Bears 2022 7 10 25 14 4 22 7 Bears 2022 8 5 8 6 24 18 8 Bears 2022 9 18 8 8 14 16 9 Bears 2022 10 19 11 13 8 9 10 Bears 2022 11 16 25 3 9 6 11 Bears 2022 12 17 11 18 3 24 12 Bears 2022 13 5 5 5 5 5 1 Eagles 2022 14 9 9 9 9 9 2 Eagles 2022 15 12 12 12 12 12 3 Eagles 2022 16 21 21 21 21 21 4 Eagles 2022 17 2 2 2 2 2 5 Eagles 2022 18 17 17 17 17 17 6 Eagles 2022 19 15 24 11 2 25 7 Eagles 2022 20 1 7 18 9 17 8 Eagles 2022 21 11 11 8 18 20 9 Eagles 2022 22 16 20 3 24 2 10 Eagles 2022 23 10 24 6 4 19 11 Eagles 2022 24 8 16 12 19 22 12 Eagles 2022
解决方案
步骤说明
- 统一列名：将
dffill中的数字列名改为result开头的名称，和dfnan的列名对应； - 合并DataFrame：以
name和month为键，将dffill合并到dfnan中； - 填充NaN：用合并后得到的
dffill数据替换dfnan中的NaN值； - 恢复结构：保留原
dfnan的列顺序，清理临时列。
代码实现
import pandas as pd # 假设dfnan和dffill已经加载完成 # 1. 重命名dffill的列，匹配dfnan的result列 dffill_renamed = dffill.rename(columns={ '1': 'result1', '2': 'result2', '3': 'result3', '4': 'result4', '5': 'result5' }) # 给dfnan的重复result列加后缀区分 dfnan.columns = ['index'] + [f'result{i+1}' for i in range(5)] + ['month', 'name', 'year'] # 2. 合并两个DataFrame merged = pd.merge(dfnan, dffill_renamed, on=['name', 'month'], how='left') # 3. 填充NaN值：对每个result列，用dffill的对应列填充 for i in range(1, 6): merged[f'result{i}'] = merged[f'result{i}_x'].fillna(merged[f'result{i}_y']) # 4. 整理最终结果，保留原列顺序并恢复无后缀的result列名 final_df = merged[['index', 'result1', 'result2', 'result3', 'result4', 'result5', 'month', 'name', 'year']] final_df.columns = ['index', 'result', 'result', 'result', 'result', 'result', 'month', 'name', 'year'] print(final_df)
替代简化方案
可以用combine_first方法结合索引匹配实现更简洁的填充：
import pandas as pd # 将两个DataFrame的索引设置为匹配键 dfnan.set_index(['name', 'month'], inplace=True) dffill_renamed = dffill.set_index(['name', 'month']).rename(columns={str(i): f'result{i}' for i in range(1,6)}) # 统一dfnan的列名格式 dfnan.columns = [f'result{i+1}' for i in range(5)] + ['year', 'index'] # 调整dffill的列顺序与dfnan的result列对应 dffill_renamed = dffill_renamed[dfnan.columns[:5]] # 填充NaN值 final_df = dfnan.combine_first(dffill_renamed).reset_index() # 恢复原列顺序和列名 final_df = final_df[['index', 'result1', 'result2', 'result3', 'result4', 'result5', 'month', 'name', 'year']] final_df.columns = ['index', 'result', 'result', 'result', 'result', 'result', 'month', 'name', 'year']
内容的提问来源于stack exchange，提问作者user2100039
相关产品推荐
相关产品推荐
