Python divide multiple columns by values in another dataframe

Question

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.

jezrael · Accepted Answer · 2020-03-31 12:03:06Z

1

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

edited Mar 31, 2020 at 12:03

answered Mar 31, 2020 at 6:41

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python divide multiple columns by values in another dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related