如何在Pandas多级索引DataFrame中合并分组明细与外层聚合值？

Absolutely! You can merge your grouped detail rows with the total aggregates for each metric1 group into a single DataFrame. Here's a step-by-step solution that matches the format you're looking for:

Step 1: Set up your sample data #

First, let's recreate your original flat DataFrame:

import pandas as pd

data = {
    'metric1': ['A', 'A', 'A', 'B', 'B', 'B'],
    'metric2': [1, 2, 3, 1, 2, 3],
    'percentage': [20, 10, 5, 40, 10, 15]
}
df = pd.DataFrame(data)

Step 2: Compute grouped details and totals #

Calculate the grouped sums for each (metric1, metric2) pair, then compute the total for each metric1 group:

# Get grouped details
grouped_details = df.groupby(['metric1', 'metric2']).sum()

# Calculate totals per metric1 group and format into a matching structure
group_totals = grouped_details.sum(level='metric1').reset_index()
group_totals['metric2'] = 'Total'  # Add a label for the total row
group_totals = group_totals.set_index(['metric1', 'metric2'])

Step 3: Combine and reshape the data #

Concatenate the details and totals, then reshape to separate the occurrence percentages and total values into distinct columns:

# Combine details and totals
combined_df = pd.concat([grouped_details, group_totals]).sort_index(level=['metric1', 'metric2'])

# Split into % occurrence and total columns
combined_df['% occurrence'] = combined_df['percentage'].where(combined_df.index.get_level_values('metric2') != 'Total', None)
combined_df['total'] = combined_df['percentage'].where(combined_df.index.get_level_values('metric2') == 'Total', None)
combined_df = combined_df.drop('percentage', axis=1)

Step 4: Display the formatted result #

To make it look clean (like your desired output), you can use Pandas Styler to hide NaN values and add group separators:

styled = combined_df.style \
    .format(na_rep='') \
    .set_table_styles([
        {'selector': 'tr:nth-child(4), tr:nth-child(8)', 'props': [('border-top', '2px solid black')]}
    ])
display(styled)

This will produce a table that looks exactly like your desired format:

If you prefer to keep the total in the same column as the percentages (without separate columns), you can skip the reshaping step and just use the concatenated combined_df directly.

内容的提问来源于stack exchange，提问作者A.Wan