如何用Pandas将groupby求和值分配至原数据表最后一行

Hey Kevin, glad to help you out with this! Let's break down how to get those group sums into the right spot in your sum column. I'll cover two common scenarios based on what you might need:

场景1：把每个分组的price总和，填入该分组的最后一行sum列 #

If you want each group's total to show up in the last row of that group's sum column, here's how to do it with pandas:

First, let's set up sample data to mimic your table A:

import pandas as pd

data = {
    'group': ['A', 'A', 'B', 'B'],
    'price': [10, 20, 30, 40],
    'sum': [None, None, None, None]
}
df = pd.DataFrame(data)

Step 1: Calculate group totals with transform #

transform keeps the same length as your original DataFrame, which makes it easy to map values back to the right rows:

# Get the total price for each group, repeated for every row in the group
group_totals = df.groupby('group')['price'].transform('sum')

Step 2: Assign totals to the last row of each group #

We'll find the index of the last row in each group and assign the corresponding total to the sum column:

# Get indices of the last row in each group
last_rows = df.groupby('group').tail(1).index
# Assign the group totals to those rows' sum column
df.loc[last_rows, 'sum'] = group_totals[last_rows]

Your resulting DataFrame will look like this:

group  price   sum
0     A     10  None
1     A     20  30.0
2     B     30  None
3     B     40  70.0

场景2：把所有分组的总求和，填入整个表的最后一行sum列 #

If you want a single total (sum of all group sums) in the very last row of your table's sum column, try this:

Option 1: Update the existing last row #

If your table already has a final row where you want to place the total:

# Calculate the grand total (sum of all group sums)
grand_total = df.groupby('group')['price'].sum().sum()
# Assign to the last row's sum column
df.iloc[-1, df.columns.get_loc('sum')] = grand_total

Result:

group  price   sum
0     A     10  None
1     A     20  None
2     B     30  None
3     B     40  100.0

Option 2: Add a new row for the grand total #

If you want to append a brand new row with the total:

# Calculate group sums first
group_sums = df.groupby('group')['price'].sum().reset_index()
# Create a new row for the grand total
total_row = pd.DataFrame({
    'group': 'Grand Total',
    'price': group_sums['price'].sum(),
    'sum': group_sums['price'].sum()
}, index=[len(df)])
# Merge the new row with your original DataFrame
df = pd.concat([df, total_row])

Result:

group  price   sum
0            A     10  None
1            A     20  None
2            B     30  None
3            B     40  None
4  Grand Total    100  100.0

Let me know if either of these fits your exact use case, or if you need adjustments for your specific table structure!

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