Pandas dataframe calculation based on condition

user_5037278 0
Viewed 776

I have two dataframe and i need to use 1 as a reference to calculate the other values.

For example, I have df like this:

Brand    LB_ID       Score
BMW      Class       98
BMW      Cost        99
VW       Class       85
VW       Cost        70

And the other df_lb like this

Brand     Total
BMW       56
VW        180

I need to use this formula to calculate another column: (Score(df) / Total(df_lb)) * 100

Normally I can use if-else conditions for such data but I have big data and it will take a lot time to write hundreds of if_else lines... I need an efficient way? Is there any?

calculation pandas function python
3 Answers

Use Series.map for new Series by Brand in second DataFrame used for divide by Score column and multiple by 100 for new column:

df['new'] = df['Score'].div(df['Brand'].map(df_lb.set_index('Brand')['Total'])).mul(100)
print (df)
  Brand  LB_ID  Score         new
0   BMW  Class     98  175.000000
1   BMW   Cost     99  176.785714
2    VW  Class     85   47.222222
3    VW   Cost     70   38.888889
user_29010020

Set Brand as the index for both dataframes and divide :

df["new"] = (df.set_index("Brand")
               .Score
               .div(df_lb.set_index("Brand").Total)
              .mul(100)
              .array)

df


   Brand    LB_ID   Score   new
0   BMW     Class   98  175.000000
1   BMW     Cost    99  176.785714
2   VW      Class   85  47.222222
3   VW      Cost    70  38.888889
user_71757130

Merge first

m = df.merge(df_lb)

and then compute your new column

m['new'] = 100 * m['Score'] / m['Total']
user_45124540
Related