multiply columns based on values in other column

Question

I have two Dataframes. My goal is to create a new column with the product of the multiplication from the column "number" and the column "multiplier" based on the name column.

First DataFrame:

id  name    number
1   aa      3
2   aa      6       
3   bb      2   
4   bb      8
5   cc      3

Second Data Frame:

id  name    multiplier
20  aa      2
21  bb      4
23  cc      6

Result:

id  name    number  product
1   aa      3       6
2   aa      6       12  
3   bb      2       8
4   bb      8       32
5   cc      3       18

why haven't you created any code yet? You should have the dataframes defined for us etc.. at the very least. — Mike Q
– Mike Q, Commented Jul 1, 2020 at 19:15
@MikeQ With pandas.read_clipboard(), there is really no more need for "defining the dataframes." — DYZ
– DYZ, Commented Jul 1, 2020 at 19:17
@DYZ interesting, though I think the poster should be obligated to show something they had tried to do etc.. — Mike Q
– Mike Q, Commented Jul 1, 2020 at 19:19

Balaji Ambresh · Accepted Answer · 2020-07-01 19:19:51Z

1

Here you go:

df3 = df1.merge(df2[['name', 'multiplier']], on='name')
df3['product'] = df3['number'] * df3['multiplier']
print(df3)

## -- End pasted text --
   id name  number  multiplier  product
0   1   aa       3           2        6
1   2   aa       6           2       12
2   3   bb       2           4        8
3   4   bb       8           4       32
4   5   cc       3           6       18

answered Jul 1, 2020 at 19:19

Balaji Ambresh

5,0022 gold badges7 silver badges17 bronze badges

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

2 Comments

NYC Coder Over a year ago

Same as my answer :)

Balaji Ambresh Over a year ago

@NYCCoder Close. In your case, the id column will be copied over from both left and right sides. Other than that, agreed.

DYZ · Accepted Answer · 2020-07-01 19:16:54Z

1

Temporarily set the index of each dataframe to "name" and simply do the multiplication. Then reset the index.

first['product'] = (first.set_index('name')['number'] * \
                    second.set_index('name')['multiplier'])\
                   .reset_index()[0]

answered Jul 1, 2020 at 19:16

DYZ

57.3k10 gold badges73 silver badges101 bronze badges

Comments

NYC Coder · Accepted Answer · 2020-07-01 19:17:51Z

1

You can do this using merge:

df = df1.merge(df2, on=['name'])
df['product'] = df['number'] * df['multiplier']
print(df[['name', 'number', 'product']])

  name  number  product
0   aa       3        6
1   aa       6       12
2   bb       2        8
3   bb       8       32
4   cc       3       18

answered Jul 1, 2020 at 19:17

NYC Coder

7,6443 gold badges14 silver badges25 bronze badges

1 Comment

jonas Over a year ago

I thought there is a nicer approach, but I'll take it. Thank you!

Pygirl · Accepted Answer · 2020-07-01 19:21:27Z

1

Use you can use df.multiply() after setting the index. Once multiply is done reset the index.

df1.set_index('name', inplace=True)
df2.set_index('name', inplace=True)
df1['product'] = df1['number'].multiply(df2['multiplier'])
df1

    id  number  product
name            
aa  1   3   6
aa  2   6   12
bb  3   2   8
bb  4   8   32
cc  5   3   18

df1.reset_index()

    name    id  number  product
0   aa      1   3       6
1   aa      2   6       12
2   bb      3   2       8
3   bb      4   8       32
4   cc      5   3       18

answered Jul 1, 2020 at 19:21

Pygirl

13.4k6 gold badges36 silver badges48 bronze badges

Comments

timgeb · Accepted Answer · 2020-07-01 19:25:06Z

0

Yet another approach. This one sets the index of df2 to the 'name' column and then indexes into it with the 'name' column of df1 to obtain the factors.

>>> factors = df2.set_index('name').loc[df1['name'], 'multiplier'].values
>>> df1['number'] *= factors
>>> df1
   id name  number
0   1   aa       6
1   2   aa      12
2   3   bb       8
3   4   bb      32
4   5   cc      18

answered Jul 1, 2020 at 19:25

timgeb

79.2k20 gold badges129 silver badges150 bronze badges

Collectives™ on Stack Overflow

multiply columns based on values in other column

5 Answers 5

2 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related