如何在Pandas groupby/Grouper中保留起止日期并补全缺失月份？

示例DataFrame #

import pandas as pd
df = pd.DataFrame({
    'date':["2022-01-01", "2022-02-01", "2022-03-01",
             "2022-04-01", "2022-05-01", "2022-06-01"],
    'amount': [2,4,3,5,6,4],
    'category': ['credit', 'credit', 'debit', 'credit', 'debit', 'credit']})

df.date = pd.to_datetime(df.date)

print(df)
# 输出：
#        date  amount category
# 0 2022-01-01       2   credit
# 1 2022-02-01       4   credit
# 2 2022-03-01       3    debit
# 3 2022-04-01       5   credit
# 4 2022-05-01       6    debit
# 5 2022-05-01       4    debit
# 6 2022-06-01       4   credit

需求与问题 #

需要统计每月debit类型的amount总和，且保留原始DataFrame中的所有月份，无debit数据的月份填充为0。

当前使用以下代码得到的结果缺失了1月、2月、6月：

print(df[df['category']=='debit'].groupby(pd.Grouper(key="date", freq="MS")).sum())

# 输出：
#             amount
# date              
# 2022-03-01       3
# 2022-04-01       0
# 2022-05-01       10

期望输出：

amount
date  
2022-01-01       0
2022-02-01       0     
2022-03-01       3
2022-04-01       0
2022-05-01       10
2022-06-01       0

解决方案 #

问题出在先筛选debit数据再分组，导致原始数据中无debit的月份直接丢失。以下两种方法可以解决：

方法一：先按月份分组，再筛选求和 #

先保留所有月份的分组框架，对每个分组内的debit数据单独求和，无数据则返回0：

result = df.groupby(pd.Grouper(key="date", freq="MS")).apply(
    lambda x: x[x['category'] == 'debit']['amount'].sum()
).rename('amount').reset_index()

print(result)

方法二：构建完整日期索引，重新对齐结果 #

1. 从原始数据中提取所有月份的起始日期，构建完整的日期索引；
2. 筛选debit数据并分组求和；
3. 用完整索引重新对齐结果，缺失值填充为0：

# 构建完整的月份起始日期索引
full_month_dates = pd.date_range(
    start=df['date'].min(), 
    end=df['date'].max(), 
    freq='MS'
)

# 计算debit每月总和
debit_month_sum = df[df['category'] == 'debit'].groupby(
    pd.Grouper(key="date", freq="MS")
)['amount'].sum()

# 重新索引并填充0
result = debit_month_sum.reindex(full_month_dates, fill_value=0).rename('amount').reset_index()

print(result)

两种方法都能得到符合预期的结果，包含所有原始月份，无debit数据的月份显示0。

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