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.
CodePudding user response:
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
CodePudding user response:
I would do something llike this:
col_weights = np.array([weights[dt.year] for dt in df.index.get_level_values(0)])
df.loc[:, "XLY"] = df["XLY"] * col_weights
df.loc[:, "UA"] = df["UA"] * col_weights
fist line creates a weights array mapping index.year to weitghs dict. next lines applies weight to each column.