[Python] Pandas - Smarter way to divide some columns of a dataframe by another dataframe

I have a dataframe df1 that has a year column and numeric columns. The values of year in df1 are not unique. I would like to normalize the numeric columns of df1 at a yearly level using a specific function. For this purpose, I have another dataframe df2 that has a year column, and with the same numeric columns as in df1. To do the renormalization, I want to divide all numeric columns in a given year with df2 for that year.

Here's sample code that achieves what I want but I'm sure there's a "smart" way of doing the same procedure.

import pandas as pd

df1 = pd.DataFrame({
'year': [1, 1, 0, 0],
'B': [4, 2, 1, 5],
'C': [5, 3, 2, 6],
'D': ["Good 1", "Good 2", "Good 1", "Good 2"]
})
df1


year B C D
0 1 4 5 Good 1
1 1 2 3 Good 2
2 0 1 2 Good 1
3 0 5 6 Good 2


df2 = pd.DataFrame({'year': [1, 0], 'B': [3, 5], 'C': [4, 7]})

vars_to_replace = ['B', 'C']


This works:

for year in df1.year.unique():
df1.loc[df1.year == year, vars_to_replace] /= df2.loc[year]

df1


year B C D
0 1 0.800000 0.714286 Good 1
1 1 0.400000 0.428571 Good 2
2 0 0.333333 0.500000 Good 1
3 0 1.666667 1.500000 Good 2


The code achieves what I want but I'm not too happy with having to use for-loops. I've tried other alternatives but can't make it work. Any ideas? I've tried the following approach but it doesn't work because df1 isn't unique in year.

df1.set_index("year", inplace=True)
df2.set_index("year", inplace=True)
df1.loc[:, vars_to_replace] /= df2


The last commands returns a "ValueError: cannot reindex on an axis with duplicate labels"

NOTE: after doing research on the questions and answers concerning this topic on SO, I was unable to find a case that covers a multicolumn operation where the indexes (year in df1 in my example) are not unique.

Continue reading...