I'm trying to get week on a month, some months might have four weeks some might have five. For each date i would like to know to which week does it belongs to. I'm mostly interested in the last week of the month.
data = pd.DataFrame(pd.date_range(' 1/ 1/ 2000', periods = 100, freq ='D'))
0 2000-01-01
1 2000-01-02
2 2000-01-03
3 2000-01-04
4 2000-01-05
5 2000-01-06
6 2000-01-07
See this answer and decide which week of month you want.
There's nothing built-in, so you'll need to calculate it with apply. For example, for an easy 'how many 7 day periods have passed' measure.
data['wom'] = data[0].apply(lambda d: (d.day-1) // 7 + 1)
For a more complicated (based on the calender), using the function from that answer.
import datetime
import calendar
def week_of_month(tgtdate):
tgtdate = tgtdate.to_datetime()
days_this_month = calendar.mdays[tgtdate.month]
for i in range(1, days_this_month):
d = datetime.datetime(tgtdate.year, tgtdate.month, i)
if d.day - d.weekday() > 0:
startdate = d
break
# now we canuse the modulo 7 appraoch
return (tgtdate - startdate).days //7 + 1
data['calendar_wom'] = data[0].apply(week_of_month)
I've used the code below when dealing with dataframes that have a datetime index.
import pandas as pd
import math
def add_week_of_month(df):
df['week_in_month'] = pd.to_numeric(df.index.day/7)
df['week_in_month'] = df['week_in_month'].apply(lambda x: math.ceil(x))
return df
If you run this example:
df = test = pd.DataFrame({'count':['a','b','c','d','e']},
index = ['2018-01-01', '2018-01-08','2018-01-31','2018-02-01','2018-02-28'])
df.index = pd.to_datetime(df.index)
you should get the following dataframe
count week_in_month
2018-01-01 a 1
2018-01-08 b 2
2018-01-31 c 5
2018-02-01 d 1
2018-02-28 e 4
import pandas as pd
def weekinmonth(dates):
"""Get week number in a month.
Parameters:
dates (pd.Series): Series of dates.
Returns:
pd.Series: Week number in a month.
"""
firstday_in_month = dates - pd.to_timedelta(dates.dt.day - 1, unit='d')
return (dates.dt.day-1 + firstday_in_month.dt.weekday) // 7 + 1
df = pd.DataFrame(pd.date_range(' 1/ 1/ 2000', periods = 100, freq ='D'), columns=['Date'])
weekinmonth(df['Date'])
0 1
1 1
2 2
3 2
4 2
..
95 2
96 2
97 2
98 2
99 2
Name: Date, Length: 100, dtype: int64
At first, calculate first day in month (from this answer: How floor a date to the first date of that month?):
df = pd.DataFrame(pd.date_range(' 1/ 1/ 2000', periods = 100, freq ='D'), columns=['Date'])
df['MonthFirstDay'] = df['Date'] - pd.to_timedelta(df['Date'].dt.day - 1, unit='d')
df
Date MonthFirstDay
0 2000-01-01 2000-01-01
1 2000-01-02 2000-01-01
2 2000-01-03 2000-01-01
3 2000-01-04 2000-01-01
4 2000-01-05 2000-01-01
.. ... ...
95 2000-04-05 2000-04-01
96 2000-04-06 2000-04-01
97 2000-04-07 2000-04-01
98 2000-04-08 2000-04-01
99 2000-04-09 2000-04-01
[100 rows x 2 columns]
Obtain weekday from first day:
df['FirstWeekday'] = df['MonthFirstDay'].dt.weekday
df
Date MonthFirstDay FirstWeekday
0 2000-01-01 2000-01-01 5
1 2000-01-02 2000-01-01 5
2 2000-01-03 2000-01-01 5
3 2000-01-04 2000-01-01 5
4 2000-01-05 2000-01-01 5
.. ... ... ...
95 2000-04-05 2000-04-01 5
96 2000-04-06 2000-04-01 5
97 2000-04-07 2000-04-01 5
98 2000-04-08 2000-04-01 5
99 2000-04-09 2000-04-01 5
[100 rows x 3 columns]
Now I can calculate with modulo of weekdays to obtain the week number in a month:
df['Date'].dt.day and make sure that begins with 0 due to modulo calculation df['Date'].dt.day-1.+ df['FirstWeekday']// 7 + 1.Whole modulo calculation:
df['WeekInMonth'] = (df['Date'].dt.day-1 + df['FirstWeekday']) // 7 + 1
df
Date MonthFirstDay FirstWeekday WeekInMonth
0 2000-01-01 2000-01-01 5 1
1 2000-01-02 2000-01-01 5 1
2 2000-01-03 2000-01-01 5 2
3 2000-01-04 2000-01-01 5 2
4 2000-01-05 2000-01-01 5 2
.. ... ... ... ...
95 2000-04-05 2000-04-01 5 2
96 2000-04-06 2000-04-01 5 2
97 2000-04-07 2000-04-01 5 2
98 2000-04-08 2000-04-01 5 2
99 2000-04-09 2000-04-01 5 2
[100 rows x 4 columns]
This seems to do the trick for me
df_dates = pd.DataFrame({'date':pd.bdate_range(df['date'].min(),df['date'].max())})
df_dates_tues = df_dates[df_dates['date'].dt.weekday==2].copy()
df_dates_tues['week']=np.mod(df_dates_tues['date'].dt.strftime('%W').astype(int),4)
You can get it subtracting the current week and the week of the first day of the month, but extra logic is needed to handle first and last week of the year:
def get_week(s):
prev_week = (s - pd.to_timedelta(7, unit='d')).dt.week
return (
s.dt.week
.where((s.dt.month != 1) | (s.dt.week < 50), 0)
.where((s.dt.month != 12) | (s.dt.week > 1), prev_week + 1)
)
def get_week_of_month(s):
first_day_of_month = s - pd.to_timedelta(s.dt.day - 1, unit='d')
first_week_of_month = get_week(first_day_of_month)
current_week = get_week(s)
return current_week - first_week_of_month
My logic to get the week of the month depends on the week of the year.
Hope this solves the problem for multiple logics used above which have limitations, the below function does the same. Temp here is the data frame for which week of the year is calculated using dt.weekofyear
def weekofmonth(dt1):
if dt1.month == 1:
return (dt1.weekofyear)
else:
pmth = dt1.month - 1
year = dt1.year
pmmaxweek = temp[(temp['timestamp_utc'].dt.month == pmth) & (temp['timestamp_utc'].dt.year == year)]['timestamp_utc'].dt.weekofyear.max()
if dt1.weekofyear == pmmaxweek:
return (dt1.weekofyear - pmmaxweek + 1)
else:
return (dt1.weekofyear - pmmaxweek)
import pandas as pd
import math
def week_of_month(dt:pd.Timestamp):
return math.ceil((x-1)//7)+1
dt["what_you_need"] = df["dt_col_name"].apply(week_of_month)
This gives you week from 1-5, if days>28, then it will count as 5th week.
