0

I have weekly data that starts on Sundays. The reports built from the data always use the 3rd Saturday of a month as a cutoff--for example January 2023 includes week 51 & 52 of Dec 2022, and weeks 1, 2, and 3 of Jan 2023 because the 3rd Saturday for each month is Dec 17th and Jan 21st for this period.

As such, some reports include 4 weeks while others have 5 weeks of data. I want to create a way to find the week number of a month so that I can splice the weeks and regroup them to form a new column with the correct report month and year using the Sunday after the 3rd week of each month as the cutoff date.

Example dataset:

df = pd.DataFrame({'Time Entry Week':['2022-12-11','2022-12-18','2022-12-25','2023-01-01','2023-01-08','2023-01-15','2023-01-22'],
'Time Period':['12/11/2022 - 12/17/2022','12/18/2022 - 12/24/2022','12/25/2022 - 12/31/2022','01/01/2023 - 01/07/2023','01/08/2023 - 01/14/2023','01/15/2023 - 01/21/2023','01/22/2023 - 01/28/2023']})

What the table should look like:

Time Entry Week Time Period Report Date
2022-12-11 12/11/2022 - 12/17/2022 December 2022
2022-12-18 12/18/2022 - 12/24/2022 January 2023
2022-12-25 12/25/2022 - 12/31/2022 January 2023
2023-01-01 01/01/2023 - 01/07/2023 January 2023
2023-01-08 01/08/2023 - 01/14/2023 January 2023
2023-01-15 01/15/2023 - 01/21/2023 January 2023
2023-01-22 01/22/2023 - 01/28/2023 February 2023

I've started with just finding the week number of a month by following https://stackoverflow.com/a/16804556/16235569 but encounter this error: "TypeError: Series.replace() got an unexpected keyword argument 'day'". Hope to resolve this, and also construct a way to group the week numbers appropriately after getting week numbers of months.

FObersteiner
  • 22,500
  • 8
  • 42
  • 72
Ryan
  • 49
  • 5

1 Answers1

1

You can try this to find the week number

from math import ceil
import datetime 

def week_of_month(dt):
    """ Returns the week of the month for the specified date.
    """

    first_day = dt.replace(day=1)

    dom = dt.day
    adjusted_dom = dom + first_day.weekday()

    return int(ceil(adjusted_dom/7.0))

df['week_number_month'] = pd.to_datetime(df['Time Entry Week']).apply(lambda x: week_of_month(x))
P. Shroff
  • 396
  • 3
  • 5
  • It needs to be week number of the month for it to work, not of the year, at least the way I'm imagining the solution. Similar to this solution https://stackoverflow.com/a/16804556/16235569 – Ryan Feb 28 '23 at 19:14
  • 1
    Got it. the issue was the function is the datetime module's replace function, not the pandas.Series.dt. Updating the solution. – P. Shroff Feb 28 '23 at 19:22