Add new column based on groupby map in pandas

Question

I have a df as shown below

product     bought_date      number_of_sales 
A           2016             15
A           2017             10
A           2018             15
B           2016             20
B           2017             30
B           2018             20
C           2016             20
C           2017             30
C           2018             20

From the above I would like to add one column called cost_per_unit as shown below.

cost of product A is 100, B is 500 and C is 200

d1 = {'A':100, 'B':500, 'C':'200'}

Expected Output:

product     bought_date      number_of_sales   cost_per_unit
A           2016             15                100
A           2017             10                100
A           2018             15                100
B           2016             20                500
B           2017             30                500
B           2018             20                500
C           2016             20                200
C           2017             30                200
C           2018             20                200

Use `df['cost_per_unit'] = df['product'].map(d1)` – Space Impact Jun 21 '20 at 17:28 — Space Impact, Jun 21 '20 at 17:28

Valdi_Bo · Accepted Answer · 2020-06-21T17:38:05.620

1

No need for any lambda function. Run just:

df['cost_per_unit'] = df['product'].map(d1)

Additional remark: product is a name of a Pandas function. You should avoid column names "covering" existing functions or attributes. It is a good habit, that they should differ, at least in char case.

edited Jun 21 '20 at 17:38

answered Jun 21 '20 at 17:33

Valdi_Bo

30,023
4
23
41

which column name is an existing function? product? – Danish Jun 21 '20 at 17:56
The name of function is *product*. Run e.g. *df.product()*. This is why you can not write e.g. *df.product.map(d1)* – Valdi_Bo Jun 21 '20 at 19:04

score 0 · Answer 2 · answered Jun 21 '20 at 17:31

You can try this:

df['cost_per_unit'] = df.apply(lambda x: d1[x['product']], axis=1)
print(df)

  product  bought_date  number_of_sales cost_per_unit
0       A         2016               15           100
1       A         2017               10           100
2       A         2018               15           100
3       B         2016               20           500
4       B         2017               30           500
5       B         2018               20           500
6       C         2016               20           200
7       C         2017               30           200
8       C         2018               20           200

Add new column based on groupby map in pandas

2 Answers2