Pandas groupby date range

Question

I have a table where one of the columns is the date of occurrence (the dataframe is not indexed by date)

I want to group the table by date wherein all items which occurred prior to a certain date are grouped into one bucket. This would need to be cumulative, so later buckets will include all datapoints from earlier ones.

Here's the daterange object I need to group by:

date_rng = date_range('28/02/2010','31/08/2014',freq='3M')

Here's an example of a few datapoints in the table:

df_raw.head()
     Ticker   FY Periodicity  Measure     Val                Date
0  BP9DL90  2009         ANN     CPX  1000.00 2008-03-31 00:00:00
1  BP9DL90  2010         ANN     CPX   600.00 2009-03-25 00:00:00
2  BP9DL90  2010         ANN     CPX   600.00 2009-09-16 00:00:00
3  BP9DL90  2011         ANN     CPX   570.00 2010-03-17 00:00:00
4  BP9DL90  2011         ANN     GRM    57.09 2010-09-06 00:00:00

[5 rows x 6 columns]

Any input would be much appreciated.

Thanks

Answer 1

you could create a function that returns 1 if the date is in the date range you want, and then use this to group by:

# convert date column do datetime type
df['Date']=pd.to_datetime(df['DATE']), format='%d-%m-%Y %H:%M:%S'

def is_in_range(x):
   if x['Date'] > '28-02-2010 00:00:00' and x['Date'] < '31-08-2014 00:00:00':
       return 1
   else:
       return 0

data.groupby(df['date'].map(is_in_range))