如何在Python Pandas的MultiIndex DataFrame中移除每组最大值？

Let's break this down into straightforward steps to get exactly what you need. First, let's start by recreating your sample DataFrame so we can work with it directly:

import pandas as pd

# Your original data
data = {
    'count': [10, 15, 15, 20],
    'day': ['Monday', 'Tuesday', 'Wednesday', 'Thursday'],
    'group': ['A', 'A', 'B', 'B'],
    'name': ['Anna', 'Beatrice', 'Beatrice', 'Cecilia']
}
df = pd.DataFrame(data)

Step 1: Identify Rows with Maximum Count per Group-Name Pair #

We need to find the index of the row where count is the highest for each combination of group and name. Pandas' groupby() combined with idxmax() does this perfectly—it returns the first occurrence of the maximum value in each group:

# Get indices of rows with max count for each group-name combination
max_row_indices = df.groupby(['group', 'name'])['count'].idxmax()

Step 2: Drop Those Rows from the Original DataFrame #

Now we just remove the rows corresponding to those indices using drop():

# Remove the max count rows
result_df = df.drop(max_row_indices)

If you print result_df, you'll get exactly the output you wanted:

count        day group     name
0     10     Monday     A     Anna
2     15  Wednesday     B  Beatrice

Handling Ties (Multiple Rows with the Same Max Count) #

If you ever have cases where multiple rows in a group-name pair have the same maximum count (e.g., two rows with count=15 for group A and name Beatrice), the above method only removes the first one. If you want to remove all rows that have the maximum count in their group, use transform() instead:

# Get the max count value for each group-name pair
max_counts_per_group = df.groupby(['group', 'name'])['count'].transform('max')
# Filter out any rows where count equals the group's max
result_df_all_ties = df[df['count'] != max_counts_per_group]

This will drop every row that matches the maximum count in its group-name combination.

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