Create months since current month column in pandas

Question

My DF looks like below:

IndexData     Week_number
2022-12-28    53
2022-12-29    53
2022-12-30    53
2022-12-31    53
2023-01-01    1
2023-01-02    1
2023-01-03    1
2023-01-04    1
.........
2023-02-27    9
2023-02-28    9
2023-03-01    9
2023-03-02    9
........
2023-03-29   13
2023-03-30   13
2023-03-31   13

I need to create another column that will looks like below:

IndexData     Week_number  new_column
2022-12-28    53           -9
2022-12-29    53           -9
2022-12-30    53           -9
2022-12-31    53           -9
........
2023-01-03    1            -8
2023-01-04    1            -8
.........
2023-02-27    9            -1
2023-02-28    9            -1
2023-03-01    9            Current_month
2023-03-02    9            Current_month
........
2023-03-29   13            Current_month
2023-03-30   13            Current_month
2023-03-31   13            Current_month

The logic for new column is:

• it should take the last month in data set and label it as 'Current_month' and then based on index date starts to count week numbers from the most recent to the oldest date. Do you have idea how I could solve this?

Regards

Answer 1

If this isn't it, then please show me how you get to the desired output (I'm just throwing out random ideas at this point

df['weeks from max date'] = (df['IndexData'] - df['IndexData'].max()).dt.days // 7

    IndexData  Week_number  weeks from max date
0  2022-12-28           53                  -14
1  2022-12-29           53                  -14
2  2022-12-30           53                  -13
3  2022-12-31           53                  -13
4  2023-01-01            1                  -13
5  2023-01-02            1                  -13
6  2023-01-03            1                  -13
7  2023-01-04            1                  -13
8  2023-02-27            9                   -5
9  2023-02-28            9                   -5
10 2023-03-01            9                   -5
11 2023-03-02            9                   -5
12 2023-03-29           13                   -1
13 2023-03-30           13                   -1
14 2023-03-31           13                    0