Pandas Convert Week Date to Weekend Friday Date

Question

I have a pandas data frame with a column that has dates like so:

DATE
01/16/2017
01/17/2017
01/18/2017
01/19/2017
01/20/2017

I need to convert each of those dates to a weekend date that is the date of the Friday of that corresponding week. So add a new column resulting in a data frame that looks like this:

DATE       WEEK_ENDING
01/16/2017  01/20/2017
01/17/2017  01/20/2017
01/18/2017  01/20/2017
01/19/2017  01/20/2017
01/20/2017  01/20/2017

Essentially I am looking for a Pandas solution to this question for a date get the friday of the week ending

The format of the date itself is not that important. Is there a built in function that can do this or will I have to write one? Thanks!

Answer 1

You can use the built in DateOffsets to achieve this:

In [310]:
from pandas.tseries.offsets import *
df['WEEK ENDING'] = df['DATE'] + Week(weekday=4)
df

Out[310]:
        DATE WEEK ENDING
0 2017-01-16  2017-01-20
1 2017-01-17  2017-01-20
2 2017-01-18  2017-01-20
3 2017-01-19  2017-01-20
4 2017-01-20  2017-01-27

Note that technically because the last day rolls onto the following week, also your date strings need to be converted to datetime first using pd.to_datetime:

df['DATE'] = pd.to_datetime(df['DATE'])

You can fix the last row by testing if the calculated offset is the same as the original data by subtracting a week and using where:

In [316]:
from pandas.tseries.offsets import *
df['WEEK ENDING'] = df['DATE'].where( df['DATE'] == (( df['DATE'] + Week(weekday=4) ) - Week()), df['DATE'] + Week(weekday=4))
df

Out[316]:
        DATE WEEK ENDING
0 2017-01-16  2017-01-20
1 2017-01-17  2017-01-20
2 2017-01-18  2017-01-20
3 2017-01-19  2017-01-20
4 2017-01-20  2017-01-20

Here it leaves the last row untouched