CODEWITHSUNDEEP
pythonpandas
Yog
Yog

Reputation: 825

How to make new columns with reference to other data frame in python pandas

Input:

df1=pd.DataFrame({
    "BusId":['abc1','abc2','abc3'],
    "Fair Increase":[2,3,5]
})
df2=pd.DataFrame({
    'BusId':['abc1','abc2','abc3','abc4','abc5'],
    "Fair":[5,6,7,8,9]
})

Need to compute only for BusId present in df1 on df2.

To calculate Increased Fair Fair in df2 + Fair Increase in df1

Expected Output:

BusId   Fair    Increased Fair
abc1    5           7
abc2    6           9
abc3    7           12

Upvotes: 1

Views: 44

Answers (2)

piRSquared
piRSquared

Reputation: 294498

You can use map with a dictionary lookup

m = dict(df2.values)
df1.assign(**{'Increased Fair': df1.BusId.map(m) + df1['Fair Increase']})

  BusId  Fair Increase  Increased Fair
0  abc1              2               7
1  abc2              3               9
2  abc3              5              12

Upvotes: 3

Sunitha
Sunitha

Reputation: 12015

You can use df.merge to merge df2 and df2, create a new column Increased Fair and delete the old column Fair Increase

>>> df3 = df2.merge(df1).set_index('BusId')
>>> df3['Increased Fair'] = df3['Fair'] + df3['Fair Increase']
>>> del df3['Fair Increase']
>>> df3
       Fair  Increased Fair
BusId                      
abc1      5               7
abc2      6               9
abc3      7              12

Upvotes: 1

Related Questions