Python中如何合并求和不同索引的groupby聚合结果？

Hey there! Let's break down how to solve this problem efficiently, with built-in pandas tools that keep things scalable for future changes.

Step 1: Convert GroupBy Objects to DataFrames #

First, since GroupBy objects are designed for aggregation rather than merging, we'll convert each one to a DataFrame while preserving the Year and ID index (or reset them to columns if you prefer—either works, but keeping as a multi-index makes merging cleaner).

Assuming your GroupBy objects were created with something like groupby_obj = df.groupby(['Year', 'ID'])['£'].sum(), convert them to named DataFrames to avoid confusion:

# Convert each GroupBy result to a DataFrame with a unique column name
df_set1 = groupby_obj1.to_frame(name='£_set1')
df_set2 = groupby_obj2.to_frame(name='£_set2')

Step 2: Merge with Outer Join to Preserve All Indices #

Use an outer join to retain every (Year, ID) pair that exists in either dataset—this handles the "missing index" requirement perfectly:

# Merge the two DataFrames on their multi-index (Year + ID)
merged_df = df_set1.join(df_set2, how='outer')

Alternatively, if you reset the index earlier, use pd.merge with how='outer' and on=['Year', 'ID']—same end result.

Step 3: Sum the £ Columns (Handle Missing Values Automatically) #

Pandas' sum() method with skipna=True (default behavior) will treat missing values (from indices that only exist in one dataset) as 0, so you'll get the exact sum you need:

# Calculate total £ across both datasets
merged_df['Total_£'] = merged_df[['£_set1', '£_set2']].sum(axis=1)

Step 4: Make It Scalable for More Datasets #

If you ever need to add more GroupBy objects later, just wrap the logic in a loop or use pd.concat to handle any number of datasets:

# Example with 3+ GroupBy objects
groupby_objs = [groupby_obj1, groupby_obj2, groupby_obj3]

# Convert all to DataFrames with unique names
df_list = [obj.to_frame(name=f'£_set{i+1}') for i, obj in enumerate(groupby_objs)]

# Concatenate all DataFrames with outer join
merged_df = pd.concat(df_list, axis=1, join='outer')

# Sum all £ columns (filter by column name pattern to avoid hardcoding)
merged_df['Total_£'] = merged_df.filter(like='£_set').sum(axis=1)

Quick Example of the Output #

Suppose your two GroupBy results look like this:

Your final merged DataFrame will be:

This keeps all your required indices and sums values where they overlap—exactly what you need!

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