Pandas分组求最大动作数并保留对应日期列值的问题求助

Step-by-Step Approach #

1. Calculate Daily Action Counts: First, group the DataFrame by id, week, and day_of_week to count actions per day.
2. Handle Tie-Breaking Randomly: For each id-week group, identify days with the maximum action count, then randomly select one of those days.

Code Implementation #

Sample Data (for testing) #

import pandas as pd
import numpy as np

data = {
    'id': [1,1,1,1,1,2,2,2,2],
    'week': [1,1,1,2,2,1,1,1,2],
    'day_of_week': ['Mon','Mon','Tue','Wed','Wed','Mon','Tue','Tue','Thu'],
    'time_of_action': ['09:00','10:00','14:00','08:00','15:00','11:00','13:00','16:00','12:00']
}
df = pd.DataFrame(data)

Method 1: Using Custom apply Function #

This method is straightforward and easy to read, suitable for most dataset sizes:

# Compute daily action counts
daily_counts = df.groupby(['id', 'week', 'day_of_week'])['time_of_action'].count().reset_index(name='action_count')

# Define function to select random max day per group
def get_random_max_day(group):
    max_count = group['action_count'].max()
    # Filter days with max count
    max_day_candidates = group[group['action_count'] == max_count]
    # Randomly pick one candidate
    selected = max_day_candidates.sample(n=1)
    return pd.Series({
        'max_number_actions': max_count,
        'max_actions_day': selected['day_of_week'].iloc[0]
    })

# Apply function to each id-week group
result = daily_counts.groupby(['id', 'week']).apply(get_random_max_day).reset_index()

Method 2: Vectorized Approach (Faster for Large Data) #

This method uses sorting with a random tie-breaker, which is more efficient for big datasets:

# Compute daily action counts
daily_counts = df.groupby(['id', 'week', 'day_of_week'])['time_of_action'].count().reset_index(name='action_count')

# Add random column to break ties randomly
daily_counts['random_tiebreaker'] = np.random.rand(len(daily_counts))

# Sort by action count (descending) and random tiebreaker (ascending)
sorted_counts = daily_counts.sort_values(
    by=['id', 'week', 'action_count', 'random_tiebreaker'],
    ascending=[True, True, False, True]
)

# Take first row per id-week group (random max day if ties exist)
result = sorted_counts.groupby(['id', 'week']).first().reset_index()
# Rename columns to match desired output
result = result.rename(columns={
    'action_count': 'max_number_actions',
    'day_of_week': 'max_actions_day'
})[['id', 'week', 'max_number_actions', 'max_actions_day']]

Explanation of Your Previous Issues #

• Using agg('first'): This only selects the first occurrence of the max count, not a random one when multiple days have the same highest count, which violates your requirement.
• idxmax Error: The error 'other must be a MultiIndex or a list of tuples' typically occurs when you try to use idxmax on a grouped object without properly aligning the indices. Even if resolved, idxmax would still pick the first max occurrence instead of a random one, so it’s not suitable for your tie-breaking need.

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