Split the date into 1st, 2nd, 3rd, 4th week of the month

Question

I have a data frame of dates:

  id|date
  13|2017-01-31
  12|2016-12-07
  11|2013-03-19

I want to classify the dates into weather it is the 1st, 2nd, 3rd or 4th week of the month.

I am using this function:


calendar.setfirstweekday(calendar.SUNDAY)

def get_week_of_month(dt):
    year  = dt.year
    month = dt.month
    day   = dt.day
    x = np.array(calendar.monthcalendar(year, month))
    week_of_month = np.where(x==day)[0][0] + 1
    return(week_of_month)

df['week_month'] = df['date'].apply(get_week_of_month)

However I am getting 6 possible weeks instead of 4. Please help

look at may of this year. its days stretch over 6 weeks (4 full weeks and parts of the first and last weeks). — acushner, Jun 01 '20 at 17:51
Depending on your definition of week you might say `(dayofmonth - 1) / 7` — shawnt00, Jun 01 '20 at 17:54
@shawnt00 No, it will lead to show `0` week for the first 7 days of the month. It should be ideally `(dayofmonth - 1) / 7 + 1` — Mayank Porwal, Jun 01 '20 at 18:03
Yes, of course you add one if you don't want it to be zero-based. — shawnt00, Jun 01 '20 at 18:10

Mayank Porwal · Accepted Answer · 2020-06-01T18:47:52.293

1

Like this:

In [1036]: df['date']= pd.to_datetime(df['date'])

In [1044]: df['week_of_month'] = ((df['date'].dt.day-1)//7)+1

In [1045]: df 
Out[1045]: 
   id       date  week_of_month
0  13 2017-01-31              5
1  12 2016-12-07              1
2  11 2013-03-19              3

edited Jun 01 '20 at 18:47

answered Jun 01 '20 at 17:51

Mayank Porwal

33,470
8
37
58

Split the date into 1st, 2nd, 3rd, 4th week of the month

1 Answers1