基于另一Pandas DataFrame的值替换目标Pandas DataFrame中的缺失值

Got it, let's solve this problem where we need to fill NaN values in df1 using corresponding labels from df2, even when df1 has dynamic column names beyond ID and Test. Here's a flexible, step-by-step approach that adapts to any number of additional columns in df1:

Step 1: Fix String 'NaN' Values (if present) #

First, notice in your sample df1, the "NaN" entries are actually strings, not proper pandas missing values. We need to convert them to real NaNs so pandas' fill methods work correctly:

import pandas as pd

# Your sample data
df1 = pd.DataFrame({
    'ID':['1111','2222','3333','4444','5555'],
    'Test':['T1','T1','T1','T2','T2'], 
    'Day1': ['P','P','P','P','P'], 
    'Day2': ['P','P','P','P', 'NaN'], 
    'Day3':['P','P','NaN','P','NaN'], 
    'Day4': ['P','P','NaN','P','NaN']
})
df2 = pd.DataFrame({
    'ID':['3333','5555'], 
    'Test':['T1','T2'], 
    'Label': ['OOT-P', 'OOT-T']
})

# Convert string 'NaN' entries to actual missing values
df1 = df1.replace('NaN', pd.NA)

Step 2: Merge df2 with df1 to Attach Matching Labels #

We'll merge the two DataFrames using ID and Test as keys, so every row in df1 gets its corresponding Label (if it exists in df2):

merged_df = df1.merge(df2, on=['ID', 'Test'], how='left')

Step 3: Auto-Detect Columns That Need Filling #

Since df1's non-ID/Test columns are dynamic (could be Day1-Day5, Week1-Week3, etc.), we'll automatically identify all columns to fill by excluding ID, Test, and the newly added Label column:

fill_columns = [col for col in merged_df.columns if col not in ['ID', 'Test', 'Label']]

Step 4: Fill NaNs with Corresponding Labels #

We'll use mask to replace NaN values in the target columns with the Label value for that row—this only applies if a Label exists (i.e., isn't NaN itself):

for col in fill_columns:
    merged_df[col] = merged_df[col].mask(pd.isna(merged_df[col]), merged_df['Label'])

If you prefer a more concise (slightly less efficient for large datasets) approach, you can use apply:

merged_df[fill_columns] = merged_df.apply(
    lambda row: row[fill_columns].fillna(row['Label']) if pd.notna(row['Label']) else row[fill_columns],
    axis=1
)

Step 5: Clean Up for the Final Result #

Drop the temporary Label column to get back your filled df1:

df1_filled = merged_df.drop('Label', axis=1)

Final Output #

Printing df1_filled gives exactly the result you need:

ID Test Day1  Day2  Day3  Day4
0  1111   T1    P     P     P     P
1  2222   T1    P     P     P     P
2  3333   T1    P     P  OOT-P OOT-P
3  4444   T2    P     P     P     P
4  5555   T2    P  OOT-T OOT-T OOT-T

This method works regardless of what additional columns df1 has—It will automatically detect and fill all non-ID/Test columns with NaNs using the matching Label from df2.

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