Apply a weight to a dataframe by date

March 30, 2022

I have a DataFrame that is indexed by Date and has a couple of columns like this:

                 XLY        UA
Date
2017-04-01  0.023991  0.060656
2017-05-01  0.010993 -0.081401
2017-06-01 -0.015596  0.130679
2017-07-01  0.019302 -0.101686
2017-08-01 -0.018608 -0.166207
2017-09-01  0.004684 -0.005298
2017-10-01  0.021203 -0.232357
2017-11-01  0.050658  0.034692
2017-12-01  0.021107  0.116513
2018-01-01  0.092411 -0.035285
2018-02-01 -0.034691  0.171206
...
2022-03-01  0.079468  0.039667

I have a python dictionary of weights

weights = {2022: 6, 2021: 5, 2020: 4, 2019: 3, 2018: 2, 2017: 1}

Is there a way to apply these weights to each row of the Dataframe so that for example, the row 2022-03-01 would be 0.079468 * 6 and .039667 * 6 and so on for all the rows that are in the year 2022, when it gets to 2021, it would apply 5 *, etc.

I know I can loop and do this. I am looking for a functional concise version.

>Solution :

Use mul on axis=0:

weights = {2022: 6, 2021: 5, 2020: 4, 2019: 3, 2018: 2, 2017: 1}
cols = ['XLY', 'UA']
df[cols] = df[cols].mul(df.index.year.map(weights), axis=0)
print(df)

# Output
                 XLY        UA
Date                          
2017-04-01  0.023991  0.060656
2017-05-01  0.010993 -0.081401
2017-06-01 -0.015596  0.130679
2017-07-01  0.019302 -0.101686
2017-08-01 -0.018608 -0.166207
2017-09-01  0.004684 -0.005298
2017-10-01  0.021203 -0.232357
2017-11-01  0.050658  0.034692
2017-12-01  0.021107  0.116513
2018-01-01  0.184822 -0.070570
2018-02-01 -0.069382  0.342412
2022-03-01  0.476808  0.238002