基于Col1分组求和Val列并添加总计行的Pandas实现问题

Got it! Here's how you can extend your approach to handle all three Val columns at once, with a clean chained workflow:

import pandas as pd

tmp = [('A','a', 1,1,0), ('A','b', 2,1,0), ('A','c', 3,2,1), ('B','a', 1,2,3), ('B','b', 0,1,2)]
df = pd.DataFrame(tmp, columns=['Col1', 'Col2', 'Val1', 'Val2', 'Val3'])

result = (
    # First, group by Col1 & Col2 and sum all Val columns
    df.groupby(['Col1', 'Col2'])[['Val1', 'Val2', 'Val3']].sum()
    # Pipe in logic to add total rows per Col1 group
    .pipe(lambda grouped_data: pd.concat([
        grouped_data,
        # Calculate totals for each Col1 group
        grouped_data.groupby(level='Col1').sum()
        # Add 'Total' as the Col2 value
        .assign(Col2='Total')
        # Set Col2 as part of the MultiIndex
        .set_index('Col2', append=True)
    ]))
    # Sort to place 'Total' rows at the end of each Col1 group
    .sort_index(key=lambda idx: (idx.get_level_values('Col1'), idx.get_level_values('Col2') == 'Total'))
)

print(result)

Breakdown of the steps: #

1. Base Aggregation: We start by grouping on Col1 and Col2 and summing all three Val columns—this gives us the individual row sums you already had for Val1, but now for all columns.
2. Compute Totals: Using pipe, we calculate the sum of each Col1 group, add a Col2 label of 'Total', and integrate it into the MultiIndex to match the base data structure.
3. Combine & Sort: We concatenate the base data with the total rows, then use a custom sort key to ensure 'Total' always comes last in each Col1 group (since we sort by Col1 first, then a boolean where 'Total' is marked as True which sorts after False).

If you prefer a more readable approach using apply (great for clarity with smaller datasets):

def add_total_row(group):
    # Calculate the sum of the current Col1 group
    total_values = group.sum().to_frame().T
    # Set the index to (Col1 value, 'Total') to match the MultiIndex structure
    total_values.index = pd.MultiIndex.from_tuples([(group.name, 'Total')], names=['Col1', 'Col2'])
    # Combine the original group with the new total row
    return pd.concat([group, total_values])

result = (
    df.groupby(['Col1', 'Col2'])[['Val1', 'Val2', 'Val3']].sum()
    .groupby('Col1')
    .apply(add_total_row)
    # Drop the extra Col1 level added by the apply step
    .reset_index(level=0, drop=True)
)

print(result)

Both methods will output exactly what you're looking for:

Val1  Val2  Val3
Col1 Col2     
A    a     1.0   1.0   0.0
     b     2.0   1.0   0.0
     c     3.0   2.0   1.0
     Total 6.0   4.0   1.0
B    a     1.0   2.0   3.0
     b     0.0   1.0   2.0
     Total 1.0   3.0   5.0

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