基于另一DataFrame分组列批量填充Pandas DataFrame的NaN值

我有一个DataFrame df1，其中多列Adj_Prod存在NaN值，需要按plant_name分组，同时匹配month，用另一个DataFrame df2中对应维度的n列（示例中n=5）数据填充这些NaN值，且方案要适配动态变化的列数n。

df1数据示例 #

Index   Adj_Prod    Adj_Prod    Adj_Prod    Adj_Prod    Adj_Prod    month   plant_name  year
3455    63285.13821 63285.13821 63285.13821 63285.13821 63285.13821 12  PENASCAL II     2021
3464    52758.13661 52758.13661 52758.13661 52758.13661 52758.13661 1   PENASCAL II     2022
3473    55998.67419 55998.67419 55998.67419 55998.67419 55998.67419 2   PENASCAL II     2022
3482    68582.45954 68582.45954 68582.45954 68582.45954 68582.45954 3   PENASCAL II     2022
3491    97313.92303 97313.92303 97313.92303 97313.92303 97313.92303 4   PENASCAL II     2022
3500    106054.0829 106054.0829 106054.0829 106054.0829 106054.0829 5   PENASCAL II     2022
3509    70424.47176 70424.47176 70424.47176 70424.47176 70424.47176 6   PENASCAL II     2022
3518    nan         nan         nan         nan         nan         7   PENASCAL II     2022
3527    nan         nan         nan         nan         nan         8   PENASCAL II     2022
3536    nan         nan         nan         nan         nan         9   PENASCAL II     2022
3545    nan         nan         nan         nan         nan        10   PENASCAL II     2022
3554    nan         nan         nan         nan         nan        11   PENASCAL II     2022
3563    nan         nan         nan         nan         nan        12   PENASCAL II     2022

df2数据示例 #

Index   month   plant_name  0       1           2           3           4
46      11  PENASCAL I  57024.37    85799.06    56423.82    44967.31    62426.29
47      12  PENASCAL I  72072.84    61719.23    74177.79    53048.06    61513.94
48       7  PENASCAL II 56188.81    64556.23    74918.13    72951.01    57474.33
49       8  PENASCAL II 31309.33    38571.34    61658.58    38578.86    52948.55
50       9  PENASCAL II 29783.46    39220.07    38641.02    35055.39    33024.38
51      10  PENASCAL II 65961.29    38898.14    55066.84    30100.4     65961.29
52      11  PENASCAL II 55134.4     49616.31    50353.2     48451.29    51903.16
53      12  PENASCAL II 62738.47    61756.62    60691.09    54747.75    48753.57

期望结果 #

Adj_Prod    Adj_Prod    Adj_Prod    Adj_Prod    Adj_Prod    month   plant_name  year
3455    63285.13821 63285.13821 63285.13821 63285.13821 63285.13821 12      PENASCAL II 2021
3464    52758.13661 52758.13661 52758.13661 52758.13661 52758.13661 1       PENASCAL II 2022
3473    55998.67419 55998.67419 55998.67419 55998.67419 55998.67419 2       PENASCAL II 2022
3482    68582.45954 68582.45954 68582.45954 68582.45954 68582.45954 3       PENASCAL II 2022
3491    97313.92303 97313.92303 97313.92303 97313.92303 97313.92303 4       PENASCAL II 2022
3500    106054.0829 106054.0829 106054.0829 106054.0829 106054.0829 5       PENASCAL II 2022
3509    70424.47176 70424.47176 70424.47176 70424.47176 70424.47176 6       PENASCAL II 2022
3518    56188.81    64556.23    74918.13    72951.01    57474.33    7       PENASCAL II 2022
3527    31309.33    38571.34    61658.58    38578.86    52948.55    8       PENASCAL II 2022
3536    29783.46    39220.07    38641.02    35055.39    33024.38    9       PENASCAL II 2022
3545    65961.29    38898.14    55066.84    30100.4     65961.29    10      PENASCAL II 2022
3554    55134.4     49616.31    50353.2     48451.29    51903.16    11      PENASCAL II 2022
3563    62738.47    61756.62    60691.09    54747.75    48753.57    12      PENASCAL II 2022

尝试过的代码及错误 #

代码1 #

df1.fillna(df2.groupby(['plant_name'])['0','1','2','3','4']. 

错误信息：

KeyError: "Columns not found: '2', '3', '1', '0', '4'"

代码2 #

df1.fillna(df2.groupby(['plant_name'])[list(range(5))]))

代码3 #

df1.groupby(['plant_name'])['Adj_Prod'].fillna(df2.iloc[:,2:6])

解决方案 #

核心思路是按plant_name+month双维度匹配，同时动态识别填充列，适配任意n值：

步骤1：动态识别填充列和目标列 #

# 动态获取df2中用于填充的数字列（适配任意n）
fill_cols = [col for col in df2.columns if str(col).isdigit()]
# 或者如果填充列是固定位置（比如从第3列开始），用：
# fill_cols = df2.columns[2:].tolist()

# 获取df1中需要填充的Adj_Prod列
adj_cols = [col for col in df1.columns if 'Adj_Prod' in col]

步骤2：合并两个DataFrame，匹配分组和月份 #

# 提取df2中用于匹配和填充的列
df2_fill = df2[['plant_name', 'month'] + fill_cols]
# 按plant_name和month合并到df1，保留所有df1数据
merged_df = df1.merge(df2_fill, on=['plant_name', 'month'], how='left')

步骤3：一一对应填充NaN值 #

# 遍历每一组对应列，填充NaN
for adj_col, fill_col in zip(adj_cols, fill_cols):
    df1[adj_col] = df1[adj_col].fillna(merged_df[fill_col])

错误原因说明 #

• 之前的代码仅按plant_name分组，忽略了month维度，导致无法准确匹配填充数据；
• 使用list(range(5))时，传入的是整数，但df2的列名是字符串类型的'0'、'1'等，因此触发KeyError；
• 直接用fillna传入分组对象无法完成维度匹配，必须先合并数据再填充。

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