如何用Pandas实现按年月分组、商品为列且带小计的数据透视表？

样本数据 #

import pandas as pd

df = pd.DataFrame(columns=["date", "item", "qty"], data=[
    ['2022-10-11','apple',2],['2022-10-12','orange',4],
    ['2021-11-01','apple',5],['2021-11-02','orange',8],
    ['2021-12-01','apple',9],['2021-12-02','orange',3],
    ['2022-01-01','banana',2],['2022-01-02','apple',1],
    ['2022-01-03','orange',6],['2022-02-02','apple',7],
    ['2022-02-03','orange',4]
])
df['date'] = pd.to_datetime(df['date'], format='%Y-%m-%d')

需求 #

将数据按年、月进行行分组，item作为列维度，同时包含行和列的小计（类似Excel数据透视表效果）。

已尝试的方法及问题 #

• pivot_table方法：
  • 使用pd.pivot_table(df, values='qty', index='date', columns='item', aggfunc='sum', fill_value='', margins=True)能得到带小计的结果，但无法按年月分组；
  • 将index替换为[pd.Grouper(key='date', freq='M')]并保留margins=True时，报错KeyError: "[TimeGrouper(key='date', freq=<MonthEnd>, axis=0, sort=True, closed='right', label='right', how='mean', convention='e', origin='start_day')] not in index"；
  • 移除margins=True后可得到按年月分组的透视表，但缺少小计。
• groupby方法：
  • 使用df.groupby([df.date.dt.year, df.date.dt.month, 'item']).agg({'qty':'sum'})时，item显示在行而非列上，且不知道如何添加行和列小计。

以下提供两种高效可行的实现方式，均能满足需求：

方案一：提取年月列后结合pivot_table+手动拼接小计 #

步骤1：新增年月分组列 #

先给原数据生成year_month列，统一格式为YYYY-MM，方便后续分组：

df['year_month'] = df['date'].dt.strftime('%Y-%m')

步骤2：生成基础透视表 #

按年月作为行索引、item作为列维度，聚合求和：

pivot_df = pd.pivot_table(
    df,
    values='qty',
    index='year_month',
    columns='item',
    aggfunc='sum',
    fill_value=0
)

步骤3：添加行小计 #

计算每行的总和，新增行小计列：

pivot_df['行小计'] = pivot_df.sum(axis=1)

步骤4：添加列小计 #

计算各列的总和，生成单独的小计行，拼接到透视表底部：

col_totals = pivot_df.sum(axis=0).to_frame().T
col_totals.index = ['列小计']
final_df = pd.concat([pivot_df, col_totals])

最终输出示例 #

apple  banana  orange  行小计
year_month                           
2021-11       5       0       8    13
2021-12       9       0       3    12
2022-01       1       2       6     9
2022-02       7       0       4    11
2022-10       2       0       4     6
列小计        24       2      25    51

方案二：用pd.Grouper分组后拼接小计 #

无需手动提取年月列，直接通过时间分组聚合后处理：

步骤1：按月份和item分组聚合 #

grouped = df.groupby([pd.Grouper(key='date', freq='M'), 'item'])['qty'].sum().unstack(fill_value=0)

步骤2：格式化索引并添加行小计 #

将月份索引转为YYYY-MM格式，同时新增行小计列：

grouped['行小计'] = grouped.sum(axis=1)
grouped.index = grouped.index.strftime('%Y-%m')

步骤3：添加列小计 #

col_totals = grouped.sum(axis=0).to_frame().T
col_totals.index = ['列小计']
final_df = pd.concat([grouped, col_totals])

此方案与方案一输出结果完全一致，适合不想手动处理年月列的场景。

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