Pandas dataframe calculation based on condition

Question

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?

Is possible if no match? Then whats happen? – jezrael Aug 11 '20 at 11:15 — jezrael, Aug 11 '20 at 11:15

jezrael · Accepted Answer · 2020-08-11T11:19:26.307

3

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

edited Aug 11 '20 at 11:19

answered Aug 11 '20 at 11:14

jezrael

822,522
95
1,334
1,252

score 1 · Answer 2 · answered Aug 11 '20 at 12:20

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

score 0 · Answer 3 · answered Aug 11 '20 at 11:23

0

Merge first

m = df.merge(df_lb)

and then compute your new column

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

answered Aug 11 '20 at 11:23

dmontaner

2,076
1
14
17

Pandas dataframe calculation based on condition

3 Answers3