CODEWITHSUNDEEP
python-3.xpandaspandas-groupby
Angel Gálvez
Angel Gálvez

Reputation: 43

How to apply double cum sum grouped on pandas?

This is my pandas dataframe.

import pandas as pd

df = pd.DataFrame([
    ['2017-01-01 19:00:00','2017-01-01 19:00:00','2017-01-02 17:00:00','2017-01-01 17:00:00',
     '2017-01-02 19:00:00','2017-01-02 19:00:00'],
                   ['RUT','RUT','RUT','NDX','NDX','NDX'],[1.0,1.0,1.0,1.0,2.0,2.0],[2.0,2.0,1.0,1.0,3.0,3.0]]).T

df.columns=[['Fecha_Hora','Ticker_Suby','Rtdo_Bruto_x_Estrat','Rtdo_Neto_x_Estrat']]
df = df.sort_values(by=['Ticker_Suby','Fecha_Hora',], ascending=True)
df

Well, I need to sum 'Rtdo_Bruto_x_Estrat'and 'Rtdo_Neto_x_Estrat' grouped by 'Fecha_Hora' and 'Ticker_Suby'.

I have used:

df.groupby(by=['Fecha_Hora','Ticker_Suby']).sum().groupby(level[0]).cumsum()

And I get successfully:

enter image description here

Well, my problem is that I need to apply an accumulative sum of the values of fields 'Rtdo_Bruto_x_Estrat' and 'Rtdo_Neto_x_Estrat'on this new df grouped by 'Ticker Suby'. My target is:

enter image description here

I have used:

df.groupby(by=['Fecha_Hora','Ticker_Suby']).sum().groupby(level=[1]).cumsum()

And I get:

enter image description here

So, really my problem is how to make both solutions can be on a same dataframe.

Thanks very much.

Upvotes: 2

Views: 297

Answers (1)

jezrael
jezrael

Reputation: 862671

Use concat of DataFrames created by cumsum, difference is first Dataframe is created by groupby by level=0 and second by level=1:

a = df.groupby(by=['Fecha_Hora','Ticker_Suby']).sum()
df = pd.concat([a.groupby(level=[0]).cumsum(), 
                a.groupby(level=[1]).cumsum().add_suffix('_cum')], 1)
print (df)
                                 Rtdo_Bruto_x_Estrat  Rtdo_Neto_x_Estrat  \
Fecha_Hora          Ticker_Suby                                            
2017-01-01 17:00:00 NDX                          1.0                 1.0   
2017-01-01 19:00:00 RUT                          2.0                 4.0   
2017-01-02 17:00:00 RUT                          1.0                 1.0   
2017-01-02 19:00:00 NDX                          4.0                 6.0   

                                 Rtdo_Bruto_x_Estrat_cum  \
Fecha_Hora          Ticker_Suby                            
2017-01-01 17:00:00 NDX                              1.0   
2017-01-01 19:00:00 RUT                              2.0   
2017-01-02 17:00:00 RUT                              3.0   
2017-01-02 19:00:00 NDX                              5.0   

                                 Rtdo_Neto_x_Estrat_cum  
Fecha_Hora          Ticker_Suby                          
2017-01-01 17:00:00 NDX                             1.0  
2017-01-01 19:00:00 RUT                             4.0  
2017-01-02 17:00:00 RUT                             5.0  
2017-01-02 19:00:00 NDX                             7.0

Upvotes: 2

Related Questions