Get end of week based on date column

Question

in the dataframe below, the 'end_of_week' column does not yet exist. I'm trying to make so that if 'date' is lesser or equal to Thursday of that week, 'end_of_week' is Thursday. How do I do this?

What I'm trying to do:

df['end_of_week'] = Thursday of same week if df['date'] <= Thursday

Example:

0   date    end_of_week
1   2015-08-31  2015-09-03 #if <= Thursday of that week
2   2015-09-01  2015-09-03
3   2015-09-07  2015-09-10
4   2015-09-09  2015-09-10
5   2015-09-16  2015-09-17
6   2015-09-17  2015-09-17

Thanks.

score 1 · Accepted Answer · edited May 23 '17 at 11:58

How about:

df['end_of_week'] = df['date'].map(lambda x: next_weekday(x, 3) if  x.weekday() < 3 else x)

where, from @phihag's answer at Find the date for the first Monday after a given a date:

def next_weekday(d, weekday):
    days_ahead = weekday - d.weekday()
    if days_ahead <= 0:
        days_ahead += 7
    return d + datetime.timedelta(days_ahead)

Doing a test:

import pandas as pd
import datetime
xl3 = pd.ExcelFile('test2.xlsx')
df3 = xl3.parse("Sheet1")
df3
Out[71]: 
   x          y       date 
0  1        fum 2015-06-01  
1  2         fo 2015-06-02 
2  3         fi 2015-06-03 
3  4        fee 2015-06-04 
4  5  dumbledum 2015-06-05 
5  6  dumbledee 2015-06-06 

df3['end_of_week'] = df3['date'].map(lambda x: next_weekday(x, 3) if x.weekday() < 3 else x)

df3
   x          y       date end_of_week 
0  1        fum 2015-06-01  2015-06-04
1  2         fo 2015-06-02  2015-06-04
2  3         fi 2015-06-03  2015-06-04
3  4        fee 2015-06-04  2015-06-04
4  5  dumbledum 2015-06-05  2015-06-05
5  6  dumbledee 2015-06-06  2015-06-06

Thank you!! this totally worked. You answer like all of the questions I ask! — Andy Do, Sep 15 '15 at 04:51

score 1 · Answer 2 · answered Sep 15 '15 at 04:47

You can use arrow to find the nearest Thursday. Arrow considers Monday the zeroth day of the week.

import arrow

THURSDAY = 3
arw = arrow.get("2015-08-31")
arw = arw.replace(days= +(THURSDAY - arw.weekday())).format('YYYY-MM-DD')

#=> arw = '2015-09-03'

To address your issue directly:

import arrow

THURSDAY = 3
dates = [
    "2015-08-31",
    "2015-09-01",
    "2015-09-07",
    "2015-09-09",
    "2015-09-16",
    "2015-09-17",
]

end_of_week_dates = []

for date in dates:
    arw = arrow.get(date)
    if arw.weekday() <= THURSDAY:
        end_of_week = arw.replace(days= +(THURSDAY - arw.weekday())).format('YYYY-MM-DD')
        end_of_week_dates.append(end_of_week)
    else:
        # handle the case where the date is after the Thursday of that week

# end_of_week_dates = [
#   '2015-09-03',
#   '2015-09-03',
#   '2015-09-10',
#   '2015-09-10',
#   '2015-09-17',
#   '2015-09-17'
# ]

thank you for your answer! I went to Tris' answer above – Andy Do Sep 15 '15 at 04:52 — Andy Do, Sep 15 '15 at 04:52

Get end of week based on date column

2 Answers2