Python Pandas中外连接后填充列空值的实现方法

Step 1: Create Sample DataFrames #

First, let's build two sample DataFrames with overlapping columns, unique columns, and differing text values for rows that share the same key identifiers.

import pandas as pd

# DataFrame from first source
data1 = {
    'Date': ['2023-10-01', '2023-10-02', '2023-10-03'],
    'Order ID': ['ORD001', 'ORD002', 'ORD003'],
    'Employee ID': ['EMP001', 'EMP002', 'EMP003'],
    'Name': ['Nick M.', 'Alice Smith', 'Bob Johnson'],
    'Amount': [150.0, 200.0, 300.0],
    'Region': ['North', 'South', 'East']
}
df1 = pd.DataFrame(data1)

# DataFrame from second source
data2 = {
    'Date': ['2023-10-01', '2023-10-02', '2023-10-04'],
    'Order ID': ['ORD001', 'ORD002', 'ORD004'],
    'Employee ID': ['EMP001', 'EMP002', 'EMP004'],
    'Name': ['Nick Mason', 'Alice S.', 'Charlie Brown'],
    'Product': ['Laptop', 'Phone', 'Tablet'],
    'Department': ['IT', 'Sales', 'Operations']
}
df2 = pd.DataFrame(data2)

Step 2: Perform Outer Join with Column Suffixes #

Use pd.merge to do an outer join on the shared key columns. Add suffixes to distinguish columns from each source—this avoids conflicts when same-named columns have different values.

# Outer join on the key columns, add suffixes to duplicate column names
outer_joined_df = pd.merge(
    df1, 
    df2, 
    on=['Date', 'Order ID', 'Employee ID'], 
    how='outer', 
    suffixes=('_df1', '_df2')
)

Step 3: Fill Nulls and Resolve Conflicting Columns #

Combine Overlapping Columns (e.g., Name) #

For columns present in both DataFrames but with differing values, define clear logic to combine them:

Option 1: Prioritize values from one source

Choose values from df2 if available, fall back to df1's values when df2 has nulls:

outer_joined_df['Name'] = outer_joined_df['Name_df2'].combine_first(outer_joined_df['Name_df1'])

Option 2: Concatenate values when both exist

Show both values if they differ, otherwise use the non-null entry:

outer_joined_df['Name'] = outer_joined_df.apply(
    lambda x: f"{x['Name_df1']} ({x['Name_df2']})" 
    if pd.notna(x['Name_df1']) and pd.notna(x['Name_df2']) 
    else x['Name_df1'] or x['Name_df2'], 
    axis=1
)

Fill Nulls for Unique Columns #

For columns that only exist in one DataFrame, fill nulls with appropriate default values:

# Fill nulls for columns exclusive to df1
outer_joined_df['Amount'] = outer_joined_df['Amount'].fillna(0.0)
outer_joined_df['Region'] = outer_joined_df['Region'].fillna('N/A')

# Fill nulls for columns exclusive to df2
outer_joined_df['Product'] = outer_joined_df['Product'].fillna('N/A')
outer_joined_df['Department'] = outer_joined_df['Department'].fillna('N/A')

Step 4: Clean Up the Result #

Remove the suffixed columns and reorder columns for readability:

# Drop the original suffixed columns
outer_joined_df = outer_joined_df.drop(['Name_df1', 'Name_df2'], axis=1)

# Reorder columns to group key identifiers first
final_df = outer_joined_df[['Date', 'Order ID', 'Employee ID', 'Name', 'Amount', 'Region', 'Product', 'Department']]

print(final_df)

Final Output #

The resulting DataFrame will include all rows from both sources, with nulls filled and conflicting text values handled per your chosen logic:

Date Order ID Employee ID          Name  Amount Region  Product   Department
0  2023-10-01    ORD001      EMP001    Nick Mason   150.0  North   Laptop           IT
1  2023-10-02    ORD002      EMP002      Alice S.   200.0  South    Phone         Sales
2  2023-10-03    ORD003      EMP003    Bob Johnson   300.0   East     N/A          N/A
3  2023-10-04    ORD004      EMP004  Charlie Brown     0.0    N/A   Tablet  Operations

Key Notes #

• Using suffixes in the merge lets you explicitly control how conflicting columns are handled, avoiding the confusion caused by combine_first on unsuffixed columns.
• Adjust fill logic (default values, prioritization) to match your specific business needs.

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