Get Week of month column from date-CodePudding

I want to extract week of month column from the date.

Dummy Data:

data = pd.DataFrame(pd.date_range(' 1/ 1/ 2000', periods = 100, freq ='D'))

Code I tried:

def add_week_of_month(df):
    df['monthweek'] = pd.to_numeric(df.index.day/7)
    df['monthweek'] = df['WeekOfMonth'].apply(lambda x: math.ceil(x))
    return df

But this code does count 7 day periods within a month. The first 7 days of a month the column would be 1, from day 8 to day 14 it would be 2 etc

But I want to have is calendar weeks per month, so on the first day of the month the feature would be 1, from the first Monday after that it would be 2 etc.

Can anyone help me with this?

CodePudding user response：

You can convert to weekly period and subtract to the first week of the month 1 if a Monday.

If you want weeks starting on Sundays, use 'W-SAT' as period and start.dt.dayofweek.eq(6).

# get first day of month
start = data[0] pd.offsets.MonthBegin() pd.offsets.MonthBegin(-1)
# or
# start = data[0].dt.to_period('M').dt.to_timestamp()

data['monthweek'] = ((data[0].dt.to_period('W')-start.dt.to_period('W'))
                     .apply(lambda x: x.n)
                     .add(start.dt.dayofweek.eq(0))
                    )

NB. in your input, column 0 is the date.

output:

            0  monthweek
0  2000-01-01          0
1  2000-01-02          0
2  2000-01-03          1  # Monday
3  2000-01-04          1
4  2000-01-05          1
5  2000-01-06          1
6  2000-01-07          1
7  2000-01-08          1
8  2000-01-09          1
9  2000-01-10          2  # Monday
10 2000-01-11          2
..        ...        ...
95 2000-04-05          1
96 2000-04-06          1
97 2000-04-07          1
98 2000-04-08          1
99 2000-04-09          1

[100 rows x 2 columns]

Example for 2001 (starts on a Monday):

            0  monthweek
0  2001-01-01          1 # Monday
1  2001-01-02          1
2  2001-01-03          1
3  2001-01-04          1
4  2001-01-05          1
5  2001-01-06          1
6  2001-01-07          1
7  2001-01-08          2 # Monday
8  2001-01-09          2
9  2001-01-10          2
10 2001-01-11          2
11 2001-01-12          2
12 2001-01-13          2
13 2001-01-14          2
14 2001-01-15          3