如何筛选聚合后的数据并补充小额交易汇总项

我有一个存储财务数据的DataFrame，示例如下：

{'Transaction Date': {0: Timestamp('2022-05-04 00:00:00'),
  1: Timestamp('2022-05-04 00:00:00'),
  2: Timestamp('2022-04-04 00:00:00'),
  3: Timestamp('2022-04-04 00:00:00'),
  4: Timestamp('2022-04-04 00:00:00'),
  5: Timestamp('2022-04-04 00:00:00'),
  6: Timestamp('2022-04-04 00:00:00'),
  7: Timestamp('2022-04-04 00:00:00'),
  8: Timestamp('2022-04-04 00:00:00'),
  9: Timestamp('2022-01-04 00:00:00')},
 'Transaction Description': {0: 'School',
  1: 'Cleaner',
  2: 'Taxi',
  3: 'shop',
  4: 'MOBILE',
  5: 'Restaurant',
  6: 'Restaurant',
  7: 'shop',
  8: 'Taxi',
  9: 'shop'},
 'Debit Amount': {0: 15.0,
  1: 26.0,
  2: 48.48,
  3: 9.18,
  4: 7.0,
  5: 10.05,
  6: 9.1,
  7: 2.14,
  8: 16.0,
  9: 11.68}
}

我用以下代码生成每月交易汇总：

reportseries = df.assign(ym=pd.to_datetime(df['Transaction Date']).dt.strftime('%Y-%m')).groupby(['ym','Transaction Description' ] )['Debit Amount'].sum() 
print(reportseries)

运行结果：

ym       Transaction Description
2022-01  shop                       11.68
2022-04  MOBILE                      7.00
         Restaurant                 19.15
         Taxi                       64.48
         shop                       11.32
2022-05  Cleaner                    26.00
         School                     15.00
Name: Debit Amount, dtype: float64

我需要实现两个需求：

1. 每月仅显示Debit Amount总和超过20的交易类别；
2. 为每个月添加名为Small items的交易项，值为该月总支出与显示项总和的差值（即未显示的小额交易总支出）。

期望输出：

2022-01  Small items                11.68 
2022-04  Taxi                       64.48
         Small items                37.47
2022-05  Cleaner                    26.00
         Small items                15.00

可以通过以下代码实现需求：

import pandas as pd

# 生成月份分组列
df['ym'] = pd.to_datetime(df['Transaction Date']).dt.strftime('%Y-%m')

# 按月份+交易类别汇总金额
grouped = df.groupby(['ym', 'Transaction Description'])['Debit Amount'].sum().reset_index()

# 计算每个月的总支出
monthly_total = df.groupby('ym')['Debit Amount'].sum().reset_index(name='Total')

# 筛选出单类别金额超过20的记录
filtered = grouped[grouped['Debit Amount'] > 20]

# 合并数据，计算每个月Small items的金额
merged = pd.merge(filtered, monthly_total, on='ym')
merged['small_total'] = merged['Total'] - merged.groupby('ym')['Debit Amount'].transform('sum')

# 生成Small items对应的记录行
small_items = merged.groupby('ym').agg(
    **{'Transaction Description': lambda x: 'Small items',
       'Debit Amount': lambda x: x['small_total'].iloc[0]}
).reset_index()

# 合并筛选记录和Small items记录，调整排序
final_data = pd.concat([filtered, small_items]).sort_values(
    ['ym', 'Transaction Description'], 
    ascending=[True, False]
)

# 转换为多级索引格式并输出保留两位小数的结果
final_series = final_data.set_index(['ym', 'Transaction Description'])['Debit Amount'].round(2)
print(final_series)

代码说明 #

1. 先通过ym列实现按月份分组的基础；
2. monthly_total统计每个月的总支出，是计算小额交易总和的基础；
3. filtered直接筛选出金额超20的交易类别；
4. 通过合并分组计算，得到每个月Small items的金额；
5. 最后合并两类数据并调整排序，转为和期望一致的多级索引Series格式。

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