CODEWITHSUNDEEP
pythonpandas
jonas
jonas

Reputation: 380

multiply columns based on values in other column

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

Upvotes: 2

Views: 1858

Answers (5)

timgeb
timgeb

Reputation: 78650

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

Upvotes: 0

Pygirl
Pygirl

Reputation: 13349

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

Upvotes: 1

Balaji Ambresh
Balaji Ambresh

Reputation: 5037

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

Upvotes: 1

NYC Coder
NYC Coder

Reputation: 7594

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

Upvotes: 1

DYZ
DYZ

Reputation: 57033

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]

Upvotes: 1

Related Questions