如何在Python中对DataFrame使用groupby按类别统计月度平均价格

• 第一步：解析日期列，指定月/日/年的格式避免解析错误，这是多数人统计结果出错的核心原因
• 第二步：提取年月维度作为分组的时间依据
• 第三步：按分类、子分类、年月三个维度分组，对两个价格字段求平均值

完整实现代码如下：

import pandas as pd

# 若你已读取数据到df，可直接从下方日期转换步骤开始
df = pd.DataFrame(
    data=[
        ["1/1/2021", "Cloth", "women", "shirt style A", 5, 4],
        ["1/1/2021", "Cloth", "men", "skirt style A", 7, 6.5],
        ["1/1/2021", "Accessories", "ear", "sky wing", 2, 1],
        ["2/1/2021", "Automotive", "wheel", "small", 21, 18],
        ["2/1/2021", "Automotive", "wheel", "big", 34, 30],
        ["1/14/2021", "Accessories", "ring", "queen couple", 3, 3],
        ["1/17/2021", "Cloth", "women", "shirt style B", 7, 7],
        ["1/17/2021", "Cloth", "men", "skirt style A", 7, 6.5],
        ["4/2/2021", "Cloth", "men", "skirt style A", 10, 9],
        ["5/2/2021", "Accessories", "ring", "queen couple", 3, 2.5],
        ["7/2/2021", "Cloth", "women", "shirt style B", 16, 12],
        ["7/2/2021", "Automotive", "wheel", "big", 40, 35],
        ["2/26/2021", "Accessories", "ring", "queen couple", 4, 4],
        ["2/26/2021", "Cloth", "women", "shirt style B", 9, 5],
        ["2/26/2021", "Cloth", "men", "skirt style A", 7, 9],
        ["2/28/2021", "Accessories", "ear", "sky wing", 2, 1],
        ["1/3/2021", "Automotive", "wheel", "big", 38, 35],
        ["1/3/2021", "Accessories", "ring", "queen couple", 4, 4],
        ["7/3/2021", "Automotive", "wheel", "big", 39, 37],
        ["3/31/2021", "Accessories", "ring", "queen couple", 4, 4],
    ],
    columns=["buy_date", "category", "subcategory", "product", "actual_price", "sell_price"]
)

# 转换日期格式，明确指定月/日/年规则，避免解析错误
df["buy_date"] = pd.to_datetime(df["buy_date"], format="%m/%d/%Y")
# 提取年月作为分组维度
df["buy_month"] = df["buy_date"].dt.to_period("M")

# 分组统计平均值，as_index=False让分组键转为普通列返回
stat_result = df.groupby(
    ["category", "subcategory", "buy_month"],
    as_index=False
)[["actual_price", "sell_price"]].mean()

运行后得到的stat_result即为所需的统计结果。

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