Conditional update to a date column in pandas dataframe

user_4289641 0
Viewed 59

For a given table:

df = pd.DataFrame(  {
'datetime': ['2015-01-01', '2015-04-01', '2015-07-01', '2015-12-01', '2015-01-01', '2015-04-01', '2015-07-01', '2015-12-01'],
})
df['datetime'] = pd.to_datetime(df['datetime'])

I would like like to change all the dates that fall on weekends (so weekday==5 or weekday==6) to the Friday before, so something like this:

def adjust_exp_date(x):
    if x.weekday()==5:
        x.weekday() -= 1
    if x.weekday()==6:
        x.weekday() -= 2

df['datetime'].apply(adjust_exp_date)
pandas datetime python
2 Answers

I was able to use a combination of assigning a day to the date and using np.select() to timedelta 7 days based on the datetime column

df = pd.DataFrame(  {
'datetime': ['2015-01-01', '2015-01-02', '2015-04-01', '2015-07-01', '2015-12-01', '2015-01-01', '2015-04-01', '2015-07-01', '2015-12-01'],
})
df['datetime'] = pd.to_datetime(df['datetime'])
df['dayOfWeek'] = df['datetime'].dt.day_name()
condition_list = [df['dayOfWeek'] == 'Friday', df['dayOfWeek'] == 'Saturday']
choice_list = [df['datetime'] - datetime.timedelta(days=7), df['datetime'] - datetime.timedelta(days=8)]
df['datetime'] = np.select(condition_list, choice_list, df['datetime'])
df

Additionally I added a date ('2015-01-02') since your original example did not include any Friday dates

user_177504310

You can sub pd.offsets.Week to adjust to nearest Friday(weekday 4)

m = df['datetime'].dt.weekday.isin([5,6])

df['adjust'] = df['datetime'].mask(m, df['datetime'] - pd.offsets.Week(weekday=4))

     datetime       week  weekday     adjust  adjustweek
0  2015-01-01   Thursday        3 2015-01-01   Thursday
1  2015-01-02     Friday        4 2015-01-02     Friday
2  2015-01-03   Saturday        5 2015-01-02     Friday
3  2015-01-04     Sunday        6 2015-01-02     Friday
4  2015-01-05     Monday        0 2015-01-05     Monday
5  2015-01-06    Tuesday        1 2015-01-06    Tuesday
6  2015-01-07  Wednesday        2 2015-01-07  Wednesday
7  2015-01-08   Thursday        3 2015-01-08   Thursday
8  2015-01-09     Friday        4 2015-01-09     Friday
9  2015-01-10   Saturday        5 2015-01-09     Friday
10 2015-01-11     Sunday        6 2015-01-09     Friday
11 2015-01-12     Monday        0 2015-01-12     Monday
12 2015-01-13    Tuesday        1 2015-01-13    Tuesday
13 2015-01-14  Wednesday        2 2015-01-14  Wednesday
14 2015-01-15   Thursday        3 2015-01-15   Thursday
15 2015-01-16     Friday        4 2015-01-16     Friday
user_103151630
Related