pandas groupby with function as key

user_12945785 0
Viewed 95

I would like to calculate the mean with with a timespan of 3 years. My data are like that :

import pandas as pd 
import numpy as np

N=120
data = {'p1': np.random.randint(50,100,N),
        'p2': np.random.randint(0,100,N),
        'p3': np.random.randint(10,70,N)
        }
df = (pd.DataFrame(data, index=pd.bdate_range(start='20100101', periods=N, freq='BM'))
      .stack()
      .reset_index()
      .rename(columns={'level_0': 'date', 'level_1': 'type', 0: 'price'})
      .sort_values('date')
      )

I tried :

(df.sort_values('date')
       .groupby(['type', 
                 ''.join([(df.date.dt.year-3), '-', (df.date.dt.year)]) #3 years time span
                ]
               )
       ['price']
       .apply(lambda x: x.mean())
       )

but get an error message :

TypeError: sequence item 0: expected str instance, Series found

I would like to calculate the mean (and others stat) on price with group by type/time period of 2010-2013, 2011-2014, 2012-2015...

The label is important because I can use :

(df.sort_values('date')
       .groupby(['type', df.date.dt.year//3]) #3 years time span
       ['price']
       .apply(lambda x: x.mean())
)

any idea ?

pandas dataframe group-by
1 Answers

I think I found the answer to my own question with (someone else could be interested) :

(df.sort_values('date')
       .groupby(['type', (df.date.dt.year-3).astype(str).str.cat((df.date.dt.year).astype(str), sep='-')
                ]
               )
       ['price']
       .apply(lambda x: x.mean())
)
user_129457850
Related