Python多列应用groupby实现分组求和计算的报错求助

It looks like your code has a few syntax and logical issues that are causing the error. Let's break down what's wrong and how to fix it to get your desired OUTPUT_DF.

What's Wrong With Your Original Code #

1. Missing Columns: You first subset the dataframe to only ID and DATE(MM/DD/YY), which removes EXPOSURE and VALUE—the columns you need for your calculation.
2. Incorrect Groupby Syntax: groupby() expects a list of columns when grouping by multiple fields, not separate positional arguments.
3. Invalid Calculation: The way you're trying to multiply EXPOSURE by VALUE and sum isn't valid pandas syntax for grouped data.

Correct Solution #

There are two clean ways to achieve your desired result. Let's go through both:

Method 1: Create a Temporary Product Column (Efficient for Large Data) #

First, calculate the product of EXPOSURE and VALUE for each row, then group by ID and DATE(MM/DD/YY) and sum the products:

# Step 1: Calculate the product of EXPOSURE and VALUE for each row
INPUT_DF['EXPOSURE_VALUE_PROD'] = INPUT_DF['EXPOSURE'] * INPUT_DF['VALUE']

# Step 2: Group by ID and DATE, sum the product column
OUTPUT_DF = INPUT_DF.groupby(['ID', 'DATE(MM/DD/YY)'])['EXPOSURE_VALUE_PROD'].sum().reset_index()

# Step 3: Rename columns to match your desired output
OUTPUT_DF.rename(columns={
    'DATE(MM/DD/YY)': 'DATE',
    'EXPOSURE_VALUE_PROD': 'FINAL_VALUE'
}, inplace=True)

Method 2: Use groupby().apply() (Concise for Smaller Data) #

You can compute the sum of products directly within the groupby operation using a lambda function:

OUTPUT_DF = INPUT_DF.groupby(['ID', 'DATE(MM/DD/YY)']).apply(
    lambda group: (group['EXPOSURE'] * group['VALUE']).sum()
).reset_index(name='FINAL_VALUE')

# Rename the date column to match your desired output
OUTPUT_DF.rename(columns={'DATE(MM/DD/YY)': 'DATE'}, inplace=True)

Result #

Both methods will produce your desired OUTPUT_DF:

Let me verify a couple of calculations to confirm:

• For STA on 1/31/03: (0.5*3) + (0.6*1) = 1.5 + 0.6 = 2.1 ✔️
• For ZT on 5/31/05: (0.3*7)+(0.5*8)+(0.5*5) = 2.1 +4 +2.5=8.6 ✔️

All values match your expected output!

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