使用Pandas合并多个DataFrame并填充缺失值的技术求助

Hey Matt, let's break down how to get your desired merged DataFrame—this is actually a common scenario once you know the right tools for combining DataFrames with overlapping columns and indexes!

Full Solution Code #

import pandas as pd
import numpy as np

# Your original data setup
data1 = {'SKU' : ['C1', 'D1'], 'Description' : ['c2', 'd'], 'Unit Cost' : [0.2, 1.5], 'Qty1' : [18, 10]}
idx1 = ['RM0001', 'RM0004']
data2 = {'SKU' : ['C1', np.nan], 'Description' : ['c', 'e'], 'Qty2' : [15, 8]}
idx2 = ['RM0001', 'RM0010']
data3 = {'SKU' : ['D1', 'E1'], 'Description' : ['d', 'e'], 'Qty3' : [7, 9]}
idx3 = ['RM0004', 'RM0010']
df1 = pd.DataFrame(data1, index=idx1)
df2 = pd.DataFrame(data2, index=idx2)
df3 = pd.DataFrame(data3, index=idx3)

# Step 1: Concatenate all DataFrames to align indexes and keep all columns
combined = pd.concat([df1, df2, df3], axis=1)

# Step 2: Merge duplicate columns by taking the last non-null value (matches your desired output)
final_df = combined.groupby(combined.columns, axis=1).last()

# Step 3: Reorder columns to match your expected output
final_df = final_df[['SKU', 'Description', 'Unit Cost', 'Qty1', 'Qty2', 'Qty3']]

print(final_df)

Step-by-Step Explanation #

1. Concatenate DataFrames by Index #

The pd.concat([df1, df2, df3], axis=1) command stacks all three DataFrames side-by-side, automatically aligning rows using their index values (like RM0001, RM0004). This creates a single DataFrame with duplicate columns for SKU and Description (one from each source DataFrame), plus your unique quantity columns and Unit Cost.

2. Resolve Duplicate Columns #

Using groupby(combined.columns, axis=1).last() groups columns by their name, then selects the last non-null value from each group for every row. This perfectly matches your desired output:

• For RM0001, it picks the Description value c from df2 (instead of c2 from df1)
• For RM0010, it grabs SKU: E1 from df3 (since df2's value is NaN)
• For rows where all values in a column group are null, it leaves NaN (like Unit Cost for RM0010)

If you wanted to prioritize earlier values instead of later ones, you could use .first() instead of .last()—just adjust based on your data's priority.

3. Reorder Columns #

The final step is a simple reordering to match the column sequence you showed in your expected output.

Why merge Was Trickier #

You mentioned trying pd.merge and functools.reduce—those tools are great for combining DataFrames on shared regular columns, but since your key is the index and you have overlapping columns with conflicting values, concat + groupby is far more straightforward. Using merge would require explicitly merging on the index (left_index=True, right_index=True) and manually resolving duplicate columns, which adds unnecessary complexity here.

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