How do I extract last day of week from yyyyww column in python

Question

I have a df_mixed column containing data in yyyyww format, eg: 201501, 201502…etc I have to extract the last date of the week and put it in ds column.

For eg: For 201501, last day of week 1 is 4-1-2015 For 201502, last day is 11-1-2015

I have to follow the ISO format. According to the ISO format the 1st week of 2015 starts from 29th December 2014 and ends on 4th January 2015

Any idea how to go about it using python, pandas and datetime library?

Do you require the last day of all weeks in the particular month or is there a criteria? — Vidya Ganesh, Aug 05 '21 at 17:47
duplicate of [Get first and last day of given week number in python](https://stackoverflow.com/a/51194746/10513549) — JWB, Aug 05 '21 at 17:48
Does this answer your question? [Get first and last day of given week number in python](https://stackoverflow.com/questions/51194745/get-first-and-last-day-of-given-week-number-in-python) — JWB, Aug 05 '21 at 17:48
I think the question is `Pandas` specific and thus not that duplicate — Prayson W. Daniel, Aug 05 '21 at 17:50
@VidyaGanesh Yes I need the last days for all the weeks given in the dataset i.e. from 201501 to 201805. — smrithi, Aug 05 '21 at 18:43
Please read the [description](https://stackoverflow.com/tags/ml/info) of the ML tag. — molbdnilo, Aug 05 '21 at 19:10

score 1 · Accepted Answer · answered Aug 05 '21 at 17:56

1

IIUC use pd.to_datetime to construct the datetime in format %Y%W%w. I added 0 as the weekday since you want Sundays which is first day of a week:

df = pd.DataFrame({"Date":[201501, 201502]})

df["Date"] = pd.to_datetime((df["Date"]-1).astype(str)+"0", format="%Y%W%w")

print (df)

        Date
0 2015-01-04
1 2015-01-11

answered Aug 05 '21 at 17:56

Henry Yik

22,275
4
18
40

I am getting an error of operands. Maybe it's because of my dataset – smrithi Aug 05 '21 at 19:07
Can I somehow share the dataset with you? The error just says - TypeError: unsupported operand type(s) for -: 'str' and 'int' – smrithi Aug 05 '21 at 19:10
It probably means your date column is type ‘str’. I did a minus 1 in order to get the correct result, so all you need to do is ‘astype(int)’ first before -1. – Henry Yik Aug 05 '21 at 19:12
Yes got it. Thanks! – smrithi Aug 06 '21 at 08:38

mozway · Answer 2 · 2021-08-05T18:05:04.393

0

Assuming this input:

df = pd.DataFrame({'date': ['201501', '201502']})

If you choose Sunday as the last day of week:

df['date2'] = pd.to_datetime(df['date']+'Sun', format='%Y%W%a')
df

output:

     date      date2
0  201501 2015-01-11
1  201502 2015-01-18

NB. if you want American week format, use %U in place of %W and Mon as the last day of week. See the doc for datetime for more precisions

edited Aug 05 '21 at 18:05

answered Aug 05 '21 at 17:44

mozway

194,879
13
39
75

By doing this, I am getting 11-01-2015 for 201501 instead of 4-01-2015. – smrithi Aug 05 '21 at 18:41
I think by adding 'Sun' it is counting additional 7 days – smrithi Aug 05 '21 at 18:41
According to the ISO format, the first week starts from 29th dec of last year to 4th Jan of the present – smrithi Aug 05 '21 at 18:47
I see that, this is unexpected. I honestly don't see the reason why. This seems like a bug to me – mozway Aug 05 '21 at 18:49
Have you tested 0/%w in place of Sun/%a like @HenryYik suggested? – mozway Aug 05 '21 at 18:51
Alternatively you could subtract 7 days to the result with pd.Timedelta if this difference is always the same – mozway Aug 05 '21 at 18:53
I got it. Thanks a lot for helping! – smrithi Aug 05 '21 at 19:15

How do I extract last day of week from yyyyww column in python

2 Answers2