Add a column in dataframe conditionally from values in other dataframe python

Question

i have a table in pandas df

id   product_1 count
1        100     10
2        200     20
3        100     30
4        400     40
5        500     50
6        200     60
7        100     70

also i have another table in dataframe df2

product    score
100         5
200         10
300         15
400         20
500         25
600         30
700         35

i have to create a new column score in my first df, taking values of score from df2 with respect to product_1.

my final output should be. df =

id   product_1 count  score
1        100     10     5
2        200     20     10
3        100     30     5
4        400     40     20
5        500     50     25
6        200     60     10
7        100     70     5

Any ideas how to achieve it?

jezrael · Accepted Answer · 2016-12-09 09:12:37Z

2

Use map:

df['score'] = df['product_1'].map(df2.set_index('product')['score'].to_dict())
print (df)
   id  product_1  count  score
0   1        100     10      5
1   2        200     20     10
2   3        100     30      5
3   4        400     40     20
4   5        500     50     25
5   6        200     60     10
6   7        100     70      5

Or merge:

df = pd.merge(df,df2, left_on='product_1', right_on='product', how='left')
print (df)
   id  product_1  count  product  score
0   1        100     10      100      5
1   2        200     20      200     10
2   3        100     30      100      5
3   4        400     40      400     20
4   5        500     50      500     25
5   6        200     60      200     10
6   7        100     70      100      5

EDIT by comment:

df['score'] = df['product_1'].map(df2.set_index('product')['score'].to_dict())
df['final_score'] = (df['count'].mul(0.6).div(df.id)).add(df.score.mul(0.4))
print (df)
   id  product_1  count  score  final_score
0   1        100     10      5          8.0
1   2        200     20     10         10.0
2   3        100     30      5          8.0
3   4        400     40     20         14.0
4   5        500     50     25         16.0
5   6        200     60     10         10.0
6   7        100     70      5          8.0

edited Dec 9, 2016 at 9:12

answered Dec 9, 2016 at 8:36

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

9 Comments

Shubham R Over a year ago

working. If handling large dataset, which one of the method map or merge would take less time?

Shubham R Over a year ago

also if i want to create one more column 'final_score' i.e (0.6*count/id + 0.4* score ) how do i do that

jezrael Over a year ago

try df['final_score'] = 0.6*df['count'].div(df.id).add(0.4.mul(df.score))

Shubham R Over a year ago

id = 1 , count = 10, score = 5, it should be (0.6*10/1 + 0.4*5) = 8 but in ur final score it is 7.2

jezrael Over a year ago

Thank you for accepting! And for verifying solution.

|

Collectives™ on Stack Overflow

Add a column in dataframe conditionally from values in other dataframe python

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related