Groupby two columns one of them is datetime

user_11354177 0
Viewed 761

I have data frame that I want to groupby by two columns one of them is datetime type. How can I do this?

import pandas as pd 
import datetime as dt 

df = pd.DataFrame({ 


'a':np.random.randn(6),
'b':np.random.choice( [5,7,np.nan], 6),
'g':{1002,300,1002,300,1002,300}
'c':np.random.choice( ['panda','python','shark'], 6),

# some ways to create systematic groups for indexing or groupby
# this is similar to r's expand.grid(), see note 2 below
'd':np.repeat( range(3), 2 ),
'e':np.tile(   range(2), 3 ),

# a date range and set of random dates
'f':pd.date_range('1/1/2011', periods=6, freq='D'),
'g':np.random.choice( pd.date_range('1/1/2011', periods=365, 
                      freq='D'), 6, replace=False) 
})
pandas dataframe datetime
1 Answers

You can use pd.Grouper to specify groupby instructions. It can be used with pd.DatetimeIndex index to group data with specified frequency using the freq parameter.

Assumming that you have this dataframe:

df = pd.DataFrame(dict(
    a=dict(date=pd.Timestamp('2020-05-01'), category='a', value=1),
    b=dict(date=pd.Timestamp('2020-06-01'), category='a', value=2),
    c=dict(date=pd.Timestamp('2020-06-01'), category='b', value=6),
    d=dict(date=pd.Timestamp('2020-07-01'), category='a', value=1),
    e=dict(date=pd.Timestamp('2020-07-27'), category='a', value=3),
)).T

You can set index to date column and it would be converted to pd.DatetimeIndex. Then you can use pd.Grouper among with another columns. For the following example I use category column.

freq='M' parameter used to group index using month frequency. There are number of string data series aliases that can be used in pd.Grouper

df.set_index('date').groupby([pd.Grouper(freq='M'), 'category'])['value'].sum()

Result:

date        category
2020-05-31  a           1
2020-06-30  a           2
            b           6
2020-07-31  a           4
Name: value, dtype: int64

Another example with your mcve:

df.set_index('g').groupby([pd.Grouper(freq='M'), 'c']).d.sum()

Result:

g           c     
2011-01-31  panda     0
2011-04-30  shark     2
2011-06-30  panda     2
2011-07-31  panda     0
2011-09-30  panda     1
2011-12-31  python    1
Name: d, dtype: int32
user_71218840
Related