pythonpandaspandas-groupby

cumsum() on multi-index pandas dataframe


I have a multi-index dataframe that shows the sum of transactions on a monthly frequency.

I am trying to get a cumsum() on yearly basis that respects my 'mapid' and 'service' multi-index. However I don't know how to derive that data

combined_df = combined_df.groupby([pd.Grouper(freq='M'), 'provider', 'mapid', 'service']).sum()


                                                 cost
datetime   provider mapid   service                  
2017-08-31 Amazon   10147.0 Monitor              0.41
                            Storage             90.51
                            Virtual Machine  11646.32
2017-09-30 Amazon   10147.0 Monitor              0.89
                            Storage            226.06
                            Virtual Machine  32624.91
2017-10-31 Amazon   10147.0 Monitor              0.17
                            Storage            261.72
                            Virtual Machine  36934.93
2017-11-30 Amazon   10147.0 Monitor              0.35
                            Storage            269.06
                            Virtual Machine  30790.70

I would like to derive the follow results

enter image description here


Solution

  • Group on the last level of your MultiIndex and call DataFrameGroupBy.cumsum:

    combined_df['cumsum'] = combined_df.groupby(level=-1)['cost'].cumsum()