CODEWITHSUNDEEP
pythonpandas
Shubham R
Shubham R

Reputation: 7644

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

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?

Upvotes: 2

Views: 165

Answers (1)

jezrael
jezrael

Reputation: 862431

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

Upvotes: 2

Related Questions