MultiIndexなDataFrameの第一インデックス毎に累積和を求める

例えば、日時+αのMultiIndexなDataFrameで、日時毎の累積和を求める。

環境

  • pandas 1.5.2

例

import io
import pandas as pd

csv = """date,AM/PM,no
2022-01-01,AM,1
2022-01-01,PM,2
2022-01-02,AM,3
2022-01-02,PM,4
2022-02-01,AM,11
2022-02-01,PM,12
"""

df = pd.read_csv(io.StringIO(csv), index_col=[0, 1], parse_dates=[0])

df = (
    df
        .groupby(
            by=[
                pd.Grouper(level='date', freq='M'),
                'AM/PM'
            ]
        )
        .sum()
)

df['cumsum'] = (
    df
        .sort_values(by=['date', 'AM/PM'], ascending=[True, True])
        .groupby(level=0)
        .cumsum()
)

df
>>>
              no cumsum
date    AM/PM       
2022-01-31 AM 4 4
           PM 6 10
2022-02-28 AM 11 11
           PM 12 23

参考