5

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!

Community
  • 1
  • 1
Kartik
  • 621
  • 2
  • 5
  • 11
  • Add 7 to each day and get the first Friday: `f = lambda x: pd.date_range(x, periods = 7)[pd.date_range(x, periods=7).weekday_name == 'Friday'][0]; df.DATE.apply(f)`. – Abdou Jan 20 '17 at 16:04

2 Answers2

12

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

EdChum
  • 376,765
  • 198
  • 813
  • 562
2

There is a fairly standard mathematical algorithm you can use.

1) Subtract the weekday (0-7) of the day you want the week to end on from the weekday of the dates you want adjusting. In this case (and for Python), Friday=4, so you will be subtracting 4 from the weekday values of your individual dates.

2) Take the result from step 1) and apply the modulus 7 if you want the value to go back to preceding Friday, or modulus -7 if you want it to go up to the next Friday.

3) Use pd.to_timedelta on the result from 2) and subtract from your date.

This becomes a fairly simple formula (assuming you imported pandas as pd):

    df['week_ending'] = df['date'] - pd.to_timedelta((df['date'].dt.weekday-4)%-7,unit='d')

If you want to see the math in action, just run the pd.to_timedelta((df['date'].dt.weekday-4)%-7,unit='d') code on its own. If I haven't made any typos, you should see Fridays=0 days, Saturdays=-6 days, Sundays=-5 days, etc. You can play around with moving the dates back to the previous Friday by doing %7 instead of %-7. Fridays still equal 0 days, Saturdays=1 days, Sundays=2 days, etc. Neat trick, eh?

This is great to keep in your back pocket because it's generally applicable anywhere with some minor tweaks for however the software numbers its weekdays.