Multiple based on different data frame

user_15492238 0
Viewed 53

I have two dataframes: df1:

  Name   Segment Axis    1   2    3    4    5       
  Amazon 1       slope  NaN  2.5  2.5  2.5  2.5
  Amazon 1       x      0.0  1.0  2.0  3.0  4.0
  Amazon 1       y      0.0  0.4  0.8  1.2  1.6
  Amazon 2       slope  NaN  2.0  2.0  2.0  2.0
  Amazon 2       x      0.0  2.0  4.0  6.0  8.0
  Amazon 2       y      0.0  1.0  2.0  3.0  4.0

df2:

 Name  Segment Cost
Amazon   1      100
Amazon   2      112
Netflix  1      110 
Netflix  2      210

I want to multiple all the values on that fall on the "Slope" in columns 1-5 by the corresponding cost in the second dataframe.

Expected output:

     Name   Segment Axis    1   2    3    4    5       
    Amazon 1       slope  NaN  250  250  250  250
    Amazon 1       x      0.0  1.0  2.0  3.0  4.0
    Amazon 1       y      0.0  0.4  0.8  1.2  1.6
    Amazon 2       slope  NaN  224  224  224  224
    Amazon 2       x      0.0  2.0  4.0  6.0  8.0
    Amazon 2       y      0.0  1.0  2.0  3.0  4.0
pandas numpy python
2 Answers

Try this:

#merge df2 to align to df1
u = df1.merge(df2,on=['Name','Segment'],how='left')
#find columns to multiply the cost
cols = df1.columns ^ ['Name','Segment','Axis']
#multiply and assign back
df1[cols] = u[cols].mul(u['Cost'],axis=0).where(df1['Axis'].eq('slope'),df1[cols])

print(df1)

     Name  Segment   Axis    1      2      3      4      5
0  Amazon        1  slope  NaN  250.0  250.0  250.0  250.0
1  Amazon        1      x  0.0    1.0    2.0    3.0    4.0
2  Amazon        1      y  0.0    0.4    0.8    1.2    1.6
3  Amazon        2  slope  NaN  224.0  224.0  224.0  224.0
4  Amazon        2      x  0.0    2.0    4.0    6.0    8.0
5  Amazon        2      y  0.0    1.0    2.0    3.0    4.0
user_98406370

You can make use of the Index to have pandas do all the heavy lifting with alignment. Unfortunately DataFrame.mul(Series) doesn't yet support a fill_value so we need to .fillna.

df1 = df1.set_index(['Name', 'Segment', 'Axis'])

# Give df2 a 'slope' level so we know what to align 
df2 = df2.assign(Axis='slope').set_index(['Name', 'Segment', 'Axis'])
# So we don't add rows from df2 not in df1
df2 = df2[df2.index.isin(df1.index)]

df1 = df1.mul(df2['Cost'], axis=0).fillna(df1)

print(df1)
                        1      2      3      4      5
Name   Segment Axis                                  
Amazon 1       slope  NaN  250.0  250.0  250.0  250.0
               x      0.0    1.0    2.0    3.0    4.0
               y      0.0    0.4    0.8    1.2    1.6
       2       slope  NaN  224.0  224.0  224.0  224.0
               x      0.0    2.0    4.0    6.0    8.0
               y      0.0    1.0    2.0    3.0    4.0
user_43333590
Related