Pandas按ID分组统计各年月每日计数极值的实现问题

需求 #

按ID进行分组，统计每个ID在各年份、月份对应的每日name计数的最小值和最大值。

原始数据 #

DATE        ID  name        
4/20/2023   AA  h88     
4/30/2023   AA  ha4     
4/30/2023   AA  hy66        
4/30/2023   AA  hc      
4/30/2023   AA  jk      
5/30/2023   AA  jk      
5/1/2023    AA  DD      
5/1/2023    AA  vb      
4/20/2023   BB  h88     
4/20/2023   BB  ha4     
4/20/2023   BB  hy66        
4/20/2023   BB  hc      
4/30/2023   BB  jk1     
4/30/2023   BB  jk2     
4/30/2023   BB  jk3     
5/1/2023    BB  DD      
5/2/2023    BB  vb      
5/2/2023    BB  Xx  

期望输出 #

ID  Month   Year    stat    count
AA  April   2023    min     1
AA  April   2023    max     4
AA  May     2023    min     1
AA  May     2023    max     2
BB  April   2023    min     3
BB  April   2023    max     4
BB  May     2023    min     1
BB  May     2023    max     2

当前实现代码 #

# Convert 'DATE' column to datetime format and extract month and year
df['DATE'] = pd.to_datetime(df['DATE'])
df['month'] = df['DATE'].dt.month
df['year'] = df['DATE'].dt.year

# Group by 'ID', 'month', and 'year' and calculate the count of names
result = df.groupby(['ID', 'year', 'month', 'DATE'])['name'].size().reset_index(name='count')

# Find the min and max counts for each ID and month combination
result_min = result.groupby(['ID', 'year', 'month'])['count'].min().reset_index(name='min_count')
result_max = result.groupby(['ID', 'year', 'month'])['count'].max().reset_index(name='max_count')

# Merge the min and max counts with the original result DataFrame
result = result.merge(result_min, on=['ID', 'year', 'month']).merge(result_max, on=['ID', 'year', 'month'])

# Create a 'stat' column based on the min and max counts
result['stat'] = np.where(result['count'] == result['min_count'], 'min', 'max')

# Drop unnecessary columns and reset index
result = result.drop(columns=['min_count', 'max_count']).reset_index(drop=True)

问题分析 #

当前代码保留了DATE维度的分组结果，最终输出会包含每日数据，无法直接得到按ID-年-月聚合的min/max统计结果，不符合期望格式。

改进方案 #

直接在每日统计的基础上，按ID-年-月聚合得到min和max，再将宽表转为长表匹配输出格式：

import pandas as pd

# 转换日期格式，提取年份和英文月份名称
df['DATE'] = pd.to_datetime(df['DATE'])
df['Year'] = df['DATE'].dt.year
df['Month'] = df['DATE'].dt.month_name()

# 第一步：统计每个ID-年-月-日的name计数
daily_counts = df.groupby(['ID', 'Year', 'Month', 'DATE'])['name'].size().reset_index(name='daily_count')

# 第二步：按ID-年-月聚合，计算每日计数的min和max
monthly_stats = daily_counts.groupby(['ID', 'Year', 'Month'])['daily_count'].agg(['min', 'max']).reset_index()

# 第三步：将宽表转为长表，生成stat列
result = monthly_stats.melt(
    id_vars=['ID', 'Year', 'Month'],
    value_vars=['min', 'max'],
    var_name='stat',
    value_name='count'
)

# 调整列顺序，匹配期望输出结构
result = result[['ID', 'Month', 'Year', 'stat', 'count']]

print(result)

改进说明 #

1. 用dt.month_name()直接生成英文月份名，无需额外转换
2. 先统计每日计数，再按ID-年-月聚合得到min/max宽表，避免保留多余的日期维度
3. 通过melt将宽表转为长表，直接生成包含stat（min/max）的行数据，完全匹配期望输出格式

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