Pandas range in a group by date

Question

I have a table in which one of the columns is the date of occurrence (the data frame is not indexed by date)

I want to group a table by date, in which all the elements that occurred before a certain date are grouped in one bucket. This should be cumulative, so later buckets will include all data points from earlier ones.

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

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

Here is an example of several data points in a 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 is appreciated.

thanks

Answer 1

you can create a function that returns 1 if the date is in the correct date, and then use it 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))