在Pandas中实现月度表格的日均订单量计算

需求说明 #

• 现有数据包含ID、order、date1、date2、user、category等字段
• 需要修改现有Pandas统计代码，将每月列的「订单数 | 月度平均值」格式替换为「订单数 | 日均订单量」，日均订单量计算方式为订单数除以当月天数
• 保持最终表格的输出结构与原格式一致

现有数据示例 #

ID      order   col1    col2    col3    date1       date2       user    category
ID_1    Order1  data1   data2   data100 2022-06-19  2021-10-10  User1   HR
ID_2    Order2  data1   data3   data101 2022-07-26  2022-01-13  User1   HR
ID_3    Order3  data1   data4   data102 2022-02-17  2022-02-17  User1   HR
ID_4    Order4  data1   data5   data103 2022-02-17  2022-02-20  User1   HR
ID_5    Order5  data1   data6   data104 2022-02-21  2022-02-21  User1   HR
ID_6    Order6  data1   data7   data105 2022-02-23  2022-02-23  User1   Purchasing
ID_7    Order7  data1   data8   data106 2022-02-23  2022-02-23  User1   Purchasing
ID_8    Order8  data1   data9   data107 2022-02-23  2022-02-23  User1   Purchasing
ID_9    Order9  data1   data10  data108 2022-02-23  2022-02-23  User1   Purchasing
ID_10   Order10 data1   data11  data109 2022-02-23  2022-02-23  User2   Purchasing
ID_11   Order11 data1   data12  data110 2022-02-23  2022-02-23  User2   Purchasing
ID_12   Order12 data1   data13  data111 2022-02-23  2022-02-23  User2   Admin
ID_13   Order13 data1   data14  data112 2022-02-23  2022-02-23  User3   Admin
ID_14   Order14 data1   data15  data113 2022-02-23  2022-02-23  User3   Admin
ID_15   Order15 data1   data16  data114 2022-02-21  2022-02-23  User3   Admin
ID_16   Order16 data1   data17  data115 2022-02-23  2022-02-23  User3   Admin
ID_17   Order17 data1   data18  data116 2022-03-06  2022-03-06  User4   Purchasing
ID_18   Order18 data1   data19  data117 2022-03-04  2022-03-06  User4   Purchasing
ID_19   Order19 data1   data20  data118 2022-03-06  2022-03-06  User4   Admin
ID_20   Order20 data1   data21  data119 2022-03-06  2022-03-06  User4   Admin
ID_21   Order21 data1   data22  data120 2022-03-06  2022-03-06  User4   Admin
ID_22   Order22 data1   data23  data121 2022-03-06  2022-03-06  User5   HR
ID_23   Order23 data1   data24  data122 2022-03-06  2022-03-06  User5   HR
ID_24   Order24 data1   data25  data123 2022-03-06  2022-03-06  User5   HR
ID_25   Order25 data1   data26  data124 2022-03-06  2022-03-06  User5   Admin
ID_26   Order26 data1   data27  data125 2022-03-06  2022-03-06  User5   Admin
ID_27   Order27 data1   data28  data126 2022-03-06  2022-03-06  User5   Purchasing
ID_28   Order28 data1   data29  data127 2022-03-06  2022-03-06  User5   Purchasing
ID_29   Order29 data1   data30  data128 2022-03-06  2022-03-06  User5   Purchasing

修改前输出示例 #

user    category    January February    March   April   May June    July    August
User1   HR          0       3 | 1.0     0       0       0   0       0       0
        Purchasing  0       4 | 0.0     0       0       0   0       0       0
User2   Admin       0       1 | 0.0     0       0       0   0       0       0
        Purchasing  0       2 | 0.0     0       0       0   0       0       0
User3   Admin       0       4 | 0.5     0       0       0   0       0       0
User4   Admin       0       0           3 | 0.0 0       0   0       0       0
        Purchasing  0       0           2 | 1.0 0       0   0       0       0
User5   Admin       0       0           2 | 0.0 0       0   0       0       0
        HR          0       0           3 | 0.0 0       0   0       0       0
        Purchasing  0       0           3 | 0.0 0       0   0       0       0

修改后的代码 #

import pandas as pd

df1 = pd.read_csv("test_data.csv")
# 定义2022年各月份的天数
month_days = {
    1:31, 2:28, 3:31, 4:30, 5:31, 6:30,
    7:31, 8:31, 9:30, 10:31, 11:30, 12:31
}

for i in range(1,13):
    m = str(i).zfill(2)
    start_date = '2022-'+m+'-01'
    end_date = '2022-'+m+'-31'

    mask = (df1['date1'] >= start_date) & (df1['date2'] <= end_date)
    df2 = df1.loc[mask].copy()  # 避免SettingWithCopyWarning
    df2['date1'] = pd.to_datetime(df2['date1'])
    df2['date2'] = pd.to_datetime(df2['date2'])
    # 过滤无效的时间范围数据
    df2 = df2[df2['date2'] >= df2['date1']]

    # 分组统计订单数，并计算日均订单量
    combinations = df2.groupby(['user','category']).agg(order_count=('order','count'))
    combinations['daily_avg'] = combinations['order_count'] / month_days[i]
    # 拼接成要求的格式
    combinations['agg'] = combinations.apply(lambda x: f"{x['order_count']} | {round(x['daily_avg'], 1)}", axis=1)
    combinations = combinations[['agg']]
    
    if i == 1:
        final_df = combinations
    else:
        final_df = final_df.merge(combinations, how='outer', on=['user','category'])

final_df = final_df.fillna(0)
final_df.columns = ['January','February','March','April','May','June','July','August','September','October','November','December']
display(final_df)

修改关键点说明 #

1. 移除冗余计算：删除原代码中对days字段的统计逻辑，聚焦订单数和日均计算
2. 添加月度天数映射：通过字典存储2022年各月天数，确保日均订单量计算准确
3. 简化数据过滤：用布尔索引替代drop操作，避免潜在的索引问题
4. 优化格式拼接：用apply方法直接生成「订单数 | 日均订单量」格式的字符串，保留1位小数提升可读性

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