pandas groupby cummulative

Question

I would like to add a cumulative sum of the Count_in column to my dataframe grouped by the Location,Date and Entry Hour.

Current dataframe:

Result I want:

I tried the following:

df.groupby(['Location','Date','Entry_Hour']).sum()['Count_in'].groupby(level=1).cumsum().reset_index().tail()

But the results are wrong:

Try `df['Cumsum'] = df.groupby(['Location', 'Date'])['Count_in'].cumsum()` — cs95, Oct 15 '20 at 15:02
Does this answer your question? [Pandas groupby cumulative sum](https://stackoverflow.com/questions/22650833/pandas-groupby-cumulative-sum) — Shradha, Oct 15 '20 at 15:05

score 0 · Answer 1 · answered Oct 15 '20 at 17:16

Asuming you want Multi index frame

df = pd.read_csv("cumulative_groupby.csv")
df["Date"] = pd.to_datetime(df["Date"], format="%Y-%m-%d")
df.set_index(["Location", "Date", "Entry_Hour"], inplace=True)
df["cumsum"] = df.groupby(["Location", "Date"]).Count_in.cumsum()
print(df)

outputs:

                                Count_out  Count_in  cumsum
Location Date       Entry_Hour                             
YEMEN    2018-10-29 16                300       500     500
                    17                200       600    1100
                    18                 10        20    1120
         2018-10-30 16                400        20      20
                    17                500        20      40
                    18                700        20      60
USA      2018-10-29 2                 300       500     500
                    3                 200       600    1100
                    4                  10       456    1556
         2018-10-30 2                 400       123     123
                    3                 500         6     129
                    4                 700       788     917

cumulative_groupby.csv

Date,Entry_Hour,Count_out,Location,Count_in
2018-10-29,16,300,YEMEN,500
2018-10-29,17,200,YEMEN,600
2018-10-29,18,10,YEMEN,20
2018-10-30,16,400,YEMEN,20
2018-10-30,17,500,YEMEN,20
2018-10-30,18,700,YEMEN,20
2018-10-29,2,300,USA,500
2018-10-29,3,200,USA,600
2018-10-29,4,10,USA,456
2018-10-30,2,400,USA,123
2018-10-30,3,500,USA,6
2018-10-30,4,700,USA,788

pandas groupby cummulative

1 Answers1