'Add new column based on Filtering Pandas Dates
I am trying to add a new column in my data frame based on dates in the date column. As part of some analysis work, I want to map dates that fall in the last 7 days as "Period 2" and the previous 14 days as "Period 1" in my data frame
p2_start_date = df["Date"] > (max(df['Date']) - timedelta(days=7))
p2_end_date = df["Date"] <= max(df['Date'])
Period_2 = p2_start_date & p2_end_date
p1_start_date = df["Date"] > (max(df['Date']) - timedelta(days=14))
p1_end_date = df["Date"] < (max(df['Date']) - timedelta(days=7))
Period_1 = p1_start_date & p1_end_date
date_filters = [Period_1, Period_2]
date_filters_values = ['Period 1', 'Period 2']
Then to add my column
df['Period'] = np.select(date_filters, date_filters_values, "-")
So I get the desired output
date Period
01/01/2001 Period 1
02/01/2001 Period 1
03/01/2001 Period 1
04/01/2001 Period 1
05/01/2001 Period 1
06/01/2001 Period 1
07/01/2001 Period 1
08/01/2001 Period 2
09/01/2001 Period 2
10/01/2001 Period 2
11/01/2001 Period 2
12/01/2001 Period 2
13/01/2001 Period 2
14/01/2001 Period 2
This is fine as long as there are no missing dates. But the issue is I need the same count of dates that fall into both periods. The above won't work if there are missing dates. For example, if the data frame is missing the 3rd, 4th, 5th, and 6th, will give the below. And that's because the filters are relative to the dates I've set for.
date Period
01/01/2001 Period 1
02/01/2001 Period 1
06/01/2001 Period 1
07/01/2001 Period 1
08/01/2001 Period 2
09/01/2001 Period 2
10/01/2001 Period 2
11/01/2001 Period 2
12/01/2001 Period 2
13/01/2001 Period 2
14/01/2001 Period 2
But I'm not sure how to select the dates correctly, I'm not too familiar with Pandas datetime module so apologies for my ignorance but any help is appreciated!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
