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 — user2261062
– user2261062, Commented Feb 11, 2022 at 17:24
how do you get the expected output? where did 420 or 440 come from? — user7864386
– user7864386, Commented Feb 11, 2022 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
– Harish reddy, Commented Feb 11, 2022 at 17:33
No it contains only transactions of 2021 and for each day there is at least 1 transaction — Harish reddy
– Harish reddy, Commented Feb 11, 2022 at 17:38

sophocles · Accepted Answer · 2022-02-11 18:15:17Z

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

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

Richard K Yu · Accepted Answer · 2022-02-11 18:14:49Z

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

Collectives™ on Stack Overflow

Month wise total and cummilative sum - Pandas

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related