Pandas: Convert Groupby DateObject into PeriodObject

Question

I have the following DataFrame:

df = pd.DataFrame({
'Buyer': 'Carl Mark Carl Joe Joe Carl'.split(),
'Trades': [1,3,5,8,9,3],
'Date' : [
    DT.datetime(2013,1,1,13,0),
    DT.datetime(2013,1,1,13,5),
    DT.datetime(2013,1,1,20,0),
    DT.datetime(2013,1,2,10,0),
    DT.datetime(2013,1,2,12,0),                                      
    DT.datetime(2013,1,2,14,0),
    ]})

Now I would like to group by the day and calculate the mean number of trades for each trader. To do so, I use the groupby function:

gr = df.groupby([df.Date.map(lambda d: d.date()), 'Buyer']).mean()

Is there any possibility to convert the resulting 'gr' DataFrame into a DataFrame with a PeriodIndex so that I can use the resample function to calculate weekly and monthly averages?

Important: I need to resample the 'gr' Dataframe not the original 'df'

I would deeply appreciate any help.

Thank you

Andy

score 0 · Answer 1 · answered Jun 04 '13 at 09:17

First, convert the Index (with date object) to DatetimeIndex. And then convert the DatetimeIndex to PeriodIndex.

In [103]: gr.reset_index(level=1)
Out[103]: 
           Buyer  Trades
Date                        
2013-01-01  Carl     3.0 
2013-01-01  Mark     3.0 
2013-01-02  Carl     3.0 
2013-01-02   Joe     8.5 

In [104]: gr = gr.reset_index(level=1)

In [105]: gr.index = gr.index.to_datetime().to_period('M')

In [106]: gr
Out[106]: 
        Buyer  Trades
2013-01  Carl     3.0 
2013-01  Mark     3.0 
2013-01  Carl     3.0 
2013-01   Joe     8.5

Pandas: Convert Groupby DateObject into PeriodObject

1 Answers1