Month wise total and cummilative sum - Pandas

Question

I want to have a month wise distribution of total revenue and month on month cumulative revenue sorted according to the months of the year. A sample of the source is table given below

Bill_Date     Sale_Net_Value
2021-01-01    220
2021-01-28    300
2021-02-03    250

Expected Output:

Month  Total_Revnue cumilitive
Jan    520          520
Feb    250          770

Does this answer your question? [pandas dataframe groupby datetime month](https://stackoverflow.com/questions/24082784/pandas-dataframe-groupby-datetime-month) — Sembei Norimaki, Feb 11 '22 at 17:24
how do you get the expected output? where did 420 or 440 come from? — , Feb 11 '22 at 17:29
Yes these are just random numbers that I have put in.. But u get the idea i have more than 1 million rows in the actual store transaction dataset — Harish reddy, Feb 11 '22 at 17:33
No it contains only transactions of 2021 and for each day there is at least 1 transaction — Harish reddy, Feb 11 '22 at 17:38

score 1 · Answer 1 · answered Feb 11 '22 at 18:14

Using your sample table as the df variable below, here is my attempt:

df.index = pd.to_datetime(df['Bill_Date'],format='%Y-%m-%d')
df = df.groupby(pd.Grouper(freq='M')).sum().reset_index()

df['Bill_Date'] = pd.to_datetime(df['Bill_Date'],format='%Y-%m-%d').dt.strftime('%b')

df['Cumulative'] = df['Sale_Net_Value'].cumsum()

df=df.rename(columns={"Bill_Date":"Month", "Sale_Net_Value":"Total_Revenue"})

print(df)

Output:

  Month  Total_Revenue  Cumulative
0   Jan            520         520
1   Feb            250         770

score 1 · Accepted Answer · answered Feb 11 '22 at 18:15

1

You could group on your months and sum your 'Sale_Net_Value' column, and then create a new column on that using assign and cumsum() :

df['Bill_Date'] = pd.to_datetime(df.Bill_Date)

df.groupby(df.Bill_Date.dt.month_name().str[:3]).agg(
    Total_revenue=('Sale_Net_Value','sum')
    ).assign(cumulative= lambda col: col['Total_revenue'].cumsum())

prints:

           Total_revenue  cumulative
Bill_Date                           
Jan                  520         520
Mar                  250         770

answered Feb 11 '22 at 18:15

sophocles

11,440
3
12
27

Hi the logic is correct but the cumulative values are calculated according to the alphabetical order of the month names which starts from april , aug ..Can this be recalculated according to Jan , feb , March – Harish reddy Feb 12 '22 at 10:24

Month wise total and cummilative sum - Pandas

2 Answers2