CODEWITHSUNDEEP
pythonpandasdataframe
Marlin
Marlin

Reputation: 111

Python divide multiple columns by values in another dataframe

I've done some google, but couldn't find what I want. Most of the questions are updating values of a column from another columns. My situation is that I need to update the columns of a data frame with values from another data frame.

>>> print(df1)
  country road  score1  score2  score3
0      us   m1      10      20      30
1      us   m2      20      30      40
2  canada   m3      30      40      50
3  canada   m4      40      50      60
4  canada   m5      50      60      70

>>> print(df2)
  country road  divider
0      us   m1       10
1      us   m2       20
2  canada   m3       30
3  canada   m4       40
4  canada   m5       50

The expected result:

  country road  score1    score2    score3
0      us   m1     1.0  2.000000  3.000000
1      us   m2     1.0  1.500000  2.000000
2  canada   m3     1.0  1.333333  1.666667
3  canada   m4     1.0  1.250000  1.500000
4  canada   m5     1.0  1.200000  1.400000

Each value in the result data frame is obtained by dividing the values in df1 by the corresponding values in df2.

Any help is much appreciated.

Upvotes: 0

Views: 111

Answers (1)

jezrael
jezrael

Reputation: 862591

Create MultiIndex in both DataFrames by DataFrame.set_index, so possible divide by DataFrame.div:

df = (df1.set_index(['country','road'])
         .div(df2.set_index(['country','road'])['divider'], axis=0)
         .reset_index())
print (df)
  country road  score1    score2    score3
0      us   m1     1.0  2.000000  3.000000
1      us   m2     1.0  1.500000  2.000000
2  canada   m3     1.0  1.333333  1.666667
3  canada   m4     1.0  1.250000  1.500000
4  canada   m5     1.0  1.200000  1.400000

Upvotes: 1

Related Questions