CODEWITHSUNDEEP
pythonpython-2.7pandasdataframe
SpanishBoy
SpanishBoy

Reputation: 2205

GroupBy in pandas divided by date range

I need to perform a complex groupBy in pandas with dividing by date range.

                                PROFIT   TOTAL  MATCH_HOME  PROFIT_2
LEAGUE  MATCH_DATE  MATCH_HOME              
   1    2007-06-05  (1.2, 1.6]  -20.00    1.00      1.55     21.00
   2    2007-08-10  (2.4, 2.8]   19.50    0.50      2.50    -19.00
   5    2012-04-05    (2, 2.4]   18.20    0.20      2.40    -18.00
        2014-09-05    (2, 2.4]  -14.00    1.40      2.20     15.40

I got this dataframe by next code:

bins = np.linspace(0, 10, 25, endpoint=False)
g1 = dfml.groupby([dfml.LEAGUE, dfml.MATCH_DATE, 
                   pd.cut(dfml.MATCH_HOME, bins)]).agg({'MATCH_HOME' : 'min',
                                                        'PROFIT': sum,
                                                        'PROFIT_2': sum,
                                                        'TOTAL': sum
                                                       })
g1[g1['TOTAL'] > 0]

How can I group by Month instead of Day?

Upvotes: 2

Views: 357

Answers (1)

jezrael
jezrael

Reputation: 862471

Maybe you can use dt.month:

bins = np.linspace(0, 10, 25, endpoint=False)
g1 = dfml.groupby([dfml.LEAGUE, dfml.MATCH_DATE.dt.month, 
                   pd.cut(dfml.MATCH_HOME, bins)]).agg({'MATCH_HOME' : 'min',
                                                        'PROFIT': sum,
                                                        'PROFIT_2': sum,
                                                        'TOTAL': sum
                                                       })
g1[g1['TOTAL'] > 0]

Or if you need years and month together, convert datetime to period:

df['MATCH_DATE'] = df['MATCH_DATE'].dt.to_period('M')

Upvotes: 2

Related Questions