如何在Pandas中按月份和交易描述汇总借方金额

核心思路 #

要实现按月份拆分、每月内按支出金额降序排列的汇总效果，需先从Transaction Date中提取年月信息作为分组维度之一，结合Transaction Description分组求和后，再按年月升序、月内金额降序的规则排序，最后调整输出格式匹配需求。

具体实现步骤 #

1. 提取年月字段 #

将Transaction Date转换为YYYY-MM格式的年月字符串，新增为YearMonth列：

df['YearMonth'] = df['Transaction Date'].dt.to_period('M').astype(str)

2. 分组求和并排序 #

按YearMonth和Transaction Description分组，对Debit Amount求和，再按指定规则排序：

# 分组计算每月各项目的总支出
summary = df.groupby(['YearMonth', 'Transaction Description'], as_index=False)['Debit Amount'].sum()
# 先按年月升序，再按当月支出金额降序排序
summary = summary.sort_values(by=['YearMonth', 'Debit Amount'], ascending=[True, False])

3. 生成缩进格式的输出 #

通过循环遍历排序后的结果，打印出符合要求的缩进样式：

current_month = None
for idx, row in summary.iterrows():
    if row['YearMonth'] != current_month:
        current_month = row['YearMonth']
        print(f"{current_month} {row['Transaction Description']} {row['Debit Amount']:.2f}")
    else:
        print(f"    {row['Transaction Description']} {row['Debit Amount']:.2f}")

完整代码示例 #

结合你的示例数据，完整可运行代码如下：

import pandas as pd

# 示例数据
data = {'Transaction Date': {0: pd.Timestamp('2022-05-04 00:00:00'),
  1: pd.Timestamp('2022-05-04 00:00:00'),
  2: pd.Timestamp('2022-04-04 00:00:00'),
  3: pd.Timestamp('2022-04-04 00:00:00'),
  4: pd.Timestamp('2022-04-04 00:00:00'),
  5: pd.Timestamp('2022-04-04 00:00:00'),
  6: pd.Timestamp('2022-04-04 00:00:00'),
  7: pd.Timestamp('2022-04-04 00:00:00'),
  8: pd.Timestamp('2022-04-04 00:00:00'),
  9: pd.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}
}

df = pd.DataFrame(data)

# 提取年月字段
df['YearMonth'] = df['Transaction Date'].dt.to_period('M').astype(str)

# 分组求和并排序
summary = df.groupby(['YearMonth', 'Transaction Description'], as_index=False)['Debit Amount'].sum()
summary = summary.sort_values(by=['YearMonth', 'Debit Amount'], ascending=[True, False])

# 打印格式化输出
current_month = None
for idx, row in summary.iterrows():
    if row['YearMonth'] != current_month:
        current_month = row['YearMonth']
        print(f"{current_month} {row['Transaction Description']} {row['Debit Amount']:.2f}")
    else:
        print(f"    {row['Transaction Description']} {row['Debit Amount']:.2f}")

输出结果 #

运行后将得到准确的汇总输出（注：原期望中2022-04的shop金额计算有误，实际应为9.18+2.14=11.32）：

2022-01 shop 11.68
2022-04 Taxi 64.48
    Restaurant 19.15
    shop 11.32
    MOBILE 7.00
2022-05 Cleaner 26.00
    School 15.00

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