基于两列匹配值合并Pandas DataFrame的技术问题求助

Looks like the main issue here is a column name mismatch in your merge code, plus a small adjustment to how you populate the target column. Let's break this down and fix it step by step.

What Went Wrong #

Your sample data uses the column name Store, but your merge code references All Stores for both left and right DataFrames. Pandas couldn't find matching columns to join on, which is why you ended up with empty columns. Additionally, we need to explicitly map the Effective values from mini_rank to the Effective From Rank column in mini_buyer instead of just merging and leaving extra columns.

Step-by-Step Solution #

1. Correct the Merge with Matching Columns

First, perform a left join (to preserve all rows from mini_buyer) using the actual column names (Division and Store) present in both DataFrames. We'll only pull the Effective column from mini_rank to avoid cluttering the result:

# Perform left join on the matching columns, only include needed columns from mini_rank
merged_df = mini_buyer.merge(
    mini_rank[['Division', 'Store', 'Effective']],
    how='left',
    on=['Division', 'Store']
)

2. Populate the Target Column

Next, replace the placeholder ? values in Effective From Rank with the matched Effective values from mini_rank. For rows where no match exists, we'll keep the original ?:

# Fill the target column with valid matches, retain ? where no match found
merged_df['Effective From Rank'] = merged_df['Effective'].fillna(merged_df['Effective From Rank'])

# Clean up the temporary merged column
merged_df = merged_df.drop(columns=['Effective'])

3. Verify the Result

If you print merged_df, you'll see all rows from mini_buyer are preserved, and Effective From Rank is correctly populated where Division and Store match:

Division  Store Other Data Effective From Rank
0           TTH    100        aaa                 Low
1    LRG Animal    100        aaa                High
2      Companion    105        aaa                 Low
3             CG    102        aaa                 Med
4           TTH    102        aaa                High
5             CG    103        aaa              X-High
6           TTH    104        aaa              X-High
7      Companion    107        aaa                High

Alternative Concise Method #

You can also use indexed DataFrames and combine_first for a more streamlined approach:

# Set indexes to the matching columns for both DataFrames
buyer_indexed = mini_buyer.set_index(['Division', 'Store'])
rank_indexed = mini_rank.set_index(['Division', 'Store'])

# Fill the target column directly using combine_first
buyer_indexed['Effective From Rank'] = rank_indexed['Effective'].combine_first(buyer_indexed['Effective From Rank'])

# Reset index to restore original column structure
merged_df = buyer_indexed.reset_index()

This will produce the exact same result as the step-by-step method.

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