I would like to make a column of which week of the month. like the 2022-10-04 will be the first week of October. How should I do it ?
| Date |
|---|
| 2022-10-04 |
| 2022-10-04 |
| 2022-10-06 |
| 2022-10-12 |
| 2022-10-19 |
| 2022-10-25 |
| 2022-10-31 |
| 2022-11-02 |
| 2022-11-03 |
I would like to look like this
| Date | Week |
|---|---|
| 2022-10-04 | Week1_Oct |
| 2022-10-04 | Week1_Oct |
| 2022-10-06 | Week1_Oct |
| 2022-10-12 | Week1_Oct |
| 2022-10-19 | Week2_Oct |
| 2022-10-25 | Week3_Oct |
| 2022-10-31 | Week4_Oct |
| 2022-11-02 | Week1_Nov |
| 2022-11-03 | Week1_Nov |
I worked out some code, but it seems that your sample data has a different result than mine.
import pandas as pd
from math import ceil
import calendar
#from this post https://stackoverflow.com/questions/3806473/week-number-of-the-month
def week_of_month(dt):
first_day = dt.replace(day=1)
dom = dt.day
adjusted_dom = dom + first_day.weekday()
week_num = int(ceil(adjusted_dom/7.0))
month_name = calendar.month_abbr[dt.month]
return f'Week{week_num}_{month_name}'
#Creating a sample DataFrame
df = pd.DataFrame({'date': ['2022-10-04', '2022-10-04', '2022-10-06', '2022-10-19', '2022-11-02']})
df['date'] = pd.to_datetime(df['date'])
df['week'] = df['date'].apply(week_of_month)
print(df)
Result:
| date | week |
|---|---|
| 2022-10-04 | Week2_Oct |
| 2022-10-04 | Week2_Oct |
| 2022-10-06 | Week2_Oct |
| 2022-10-19 | Week4_Oct |
| 2022-11-02 | Week1_Nov |
