CODEWITHSUNDEEP
pythonpandaspandas-groupby
Dominik Novotný
Dominik Novotný

Reputation: 346

Cumulative sum over days in python

I have the following dataframe:

        date      money
0     2018-01-01     20
1     2018-01-05     30
2     2018-02-15     7
3     2019-03-17     150
4     2018-01-05     15
...
2530  2019-03-17     350

And I need:

[(2018-01-01,20),(2018-01-05,65),(2018-02-15,72),...,(2019-03-17,572)]

So i need to do a cumulative sum of money over all days: So far I have tried many things and the closest Ithink I've got is:

graph_df.date = pd.to_datetime(graph_df.date)
temporary = graph_df.groupby('date').money.sum()
temporary = temporary.groupby(temporary.index.to_period('date')).cumsum().reset_index()

But this gives me ValueError: Invalid frequency: date

Could anyone help please?

Thanks

Upvotes: 1

Views: 2524

Answers (3)

nimrodz
nimrodz

Reputation: 1594

you can try using df.groupby('date').sum():

example data frame:

df
         date  money
0  01/01/2018     20
1  05/01/2018     30
2  15/02/2018      7
3  17/03/2019    150
4  05/01/2018     15
5  17/03/2019    550
6  15/02/2018     13



df['cumsum'] = df.money.cumsum()
list(zip(df.groupby('date').tail(1)['date'], df.groupby('date').tail(1)['cumsum']))

[('01/01/2018', 20),
 ('05/01/2018', 222),
 ('17/03/2019', 772),
 ('15/02/2018', 785)]

Upvotes: 0

kuzand
kuzand

Reputation: 9806

list(map(tuple, df.groupby('date', as_index=False)['money'].sum().values))

Edit:

df = pd.DataFrame({'date': ['2018-01-01', '2018-01-05', '2018-02-15', '2019-03-17', '2018-01-05'],
                   'money': [20, 30, 7, 150, 15]})

#df['date'] = pd.to_datetime(df['date'])
#df = df.sort_values(by='date')

temporary = df.groupby('date', as_index=False)['money'].sum()
temporary['money_cum'] = temporary['money'].cumsum()

Result: 

>>> list(map(tuple, temporary[['date', 'money_cum']].values))
[('2018-01-01', 20),
 ('2018-01-05', 65),
 ('2018-02-15', 72),
 ('2019-03-17', 222)]

Upvotes: 1

BramV
BramV

Reputation: 556

I don't think you need the second groupby. You can simply add a column with the cumulative sum.

This does the trick for me:

import pandas as pd
df = pd.DataFrame({'date': ['01-01-2019','04-06-2019', '07-06-2019'], 'money': [12,15,19]})
df['date'] = pd.to_datetime(df['date']) # this is not strictly needed

tmp = df.groupby('date')['money'].sum().reset_index()
tmp['money_sum'] = tmp['money'].cumsum()

Converting the date column to an actual date is not needed for this to work.

Upvotes: 3

Related Questions