如何用Pandas实现分组时忽略指定行取最大值并全量求和？

Got it, let's break down how to solve this problem exactly as you described. We need to handle two separate rules for the same group: sum all rows for V1/V2, but only consider non-EX rows when finding the max V1. Here's a step-by-step approach with code examples:

Step 1: Prepare Test Data (for demonstration) #

First, let's create a sample DataFrame so you can follow along and test the code:

import pandas as pd

data = {
    'W2': ['A', 'A', 'A', 'B', 'B', 'C'],
    'V1': [10, 20, 30, 40, 50, 60],
    'V2': [5, 10, 15, 20, 25, 30],
    'N': ['XX', 'EX', 'YY', 'EX', 'ZZ', 'EX']  # Group C has only EX rows
}
df = pd.DataFrame(data)

Step 2: Calculate Grouped Sums (include all rows) #

We start by computing the total sum of V1 and V2 for each W2 group—this includes rows where N='EX':

grouped_sums = df.groupby('W2')[['V1', 'V2']].sum().rename(columns={'V1': 'V1_sum', 'V2': 'V2_sum'})

Step 3: Calculate Filtered V1 Max (exclude N='EX' rows) #

Next, we find the maximum V1 value per group, but only for rows where N is not 'EX'. Note that if a group has only N='EX' rows (like group C in our test data), this will return NaN:

filtered_v1_max = df[df['N'] != 'EX'].groupby('W2')['V1'].max().rename('V1_max')

Step 4: Merge Results into One DataFrame #

Now we combine the sums and max values using join() (since both are indexed by W2):

final_result = grouped_sums.join(filtered_v1_max).reset_index()

# Optional: Handle NaN values if needed (e.g., replace with 0 or a placeholder)
final_result['V1_max'] = final_result['V1_max'].fillna(0)

Alternative: One-Liner with agg() #

If you prefer a more concise approach, you can use Pandas' agg() method to do everything in one step. The lambda function here filters out N='EX' rows specifically for the max calculation:

final_result = df.groupby('W2').agg(
    V1_sum=('V1', 'sum'),
    V2_sum=('V2', 'sum'),
    V1_max=('V1', lambda x: x[df.loc[x.index, 'N'] != 'EX'].max())
).reset_index().fillna({'V1_max': 0})

Example Output #

Running either method on our test data will give you this result:

Key Notes: #

• The sums include all rows in the group, even those with N='EX'.
• The V1_max only considers rows where N is not 'EX'—we handled the edge case of all-EX groups by filling NaN with 0 (adjust this to your specific needs).

内容的提问来源于stack exchange，提问作者Сергей Гульдяшев