0

I want to create a new variable that tells what week(nth week) of the month a certain date belongs to. For example, December 2020 has 5 weeks and Dec 8, 2020 belongs to the 2nd week of December. I already have date and month columns in my table and want to created a new column called "NthWeek". For example I want something like this as my output.

  Date            Month     NthWeek
2020-12-08          12        2nd
2020-12-15          12        3rd

My current code is as follows and it works well for month like Dec 2020 but returns in accurate amount for a month like Oct 2020.

df['NthWeek'] = df['Date'].apply(lambda d: (d.day-1) // 7 + 1)

For example, October 5, 2020 is actually in the 2nd week of October, but my current calculation will just give me 1 (first week). Is there any way I can use a built-in calendar to get correct Nth weeks for all instances? How do I edit my code to make it work?

I'd appreciate any help on this.

Karina1117
  • 69
  • 1
  • 4
  • 3
    Does this answer your question? [python - Week number of the month](https://stackoverflow.com/questions/3806473/python-week-number-of-the-month) – thshea Dec 08 '20 at 02:55

1 Answers1

0

You can use the weekday function to get the weekday of the first of the month, and use that to determine the week you are in. It returns the day as an int, with 0 = Monday.

from calendar import weekday
wd = weekday(year, month, day)

E.g., if you determine the first of the month starts on day wd, you know the first week will end 6 - wd days later. You can use this with your calculation by taking day / 7 and comparing it to what day the month started.

The implementation I have in mind is the following:

from calendar import weekday
from math import floor
yr, mon, day = 2020, 10, 5
# check weekday on the first of the month in question.
wd = weekday(year=yr, month=mon, day=1)
# make it start at sunday=0 (assuming week is sun-sat).
wd = (wd+1)%7
# The month started on the day of the week wd (Sunday = 0).
# Thus our first week is only (7 - wd) days long.
wk = floor((day - wd) / 7.0) + 2
print(str(wk))

The final computation uses the fact that day / 7 + 1 will give the week if we round down, assuming the week always starts on Sunday. Since it starts on day wd, we subtract this from day; now it is possible to find week -1, which means we're in the first (shorter than 7 days) week. This is why we add 2 rather than the 1 as before.

This computation should work in all instances. I hope this is what you're after.

kevin-robb
  • 104
  • 8