Pandas match with a dict between two columns and create a new column

Question

I have a df1/dic like below:

Station_to    Station_from      Distance_km
A                AA               1.1
A                BB               2.2
A                CC               3.3
B                AA               4.4
...

a df2 like below:

Origin    Destination      
A          CC
A          DD
B          AA
...

And I want to creat a new column ['Distance'] in df2, and match origin and destination with df1/dic.

The ideal outcome would like below:

Origin    Destination      Distance
A          CC               3.3
A          DD               Nan
B          AA               4.4
...

Can anyone help me? Thank you.

`df2.merge(df1, left_on=['Origin', 'Destination'], right_on=['Station_to', 'Station_from'], how='left')`? — Quang Hoang, Jul 15 '19 at 19:34
`df2['Distance'] = pd.merge(df2,df1, left_on=['Origin', 'Destination'], right_on=['Station_to', 'Station_from'], how='left')['Distance_km']` as @QuangHoang suggested — help-ukraine-now, Jul 15 '19 at 19:49

score 0 · Answer 1 · edited Jul 15 '19 at 19:55

0

One way to do this would be to use pd.merge operation but it would create a new dataframe so you might want to delete the previous one. If df1 and df2 are two dataframes then:

df3 = df2.merge(df1, how='right',left_on=['Stationto','Stationfrom'],right_on=['origin','destination'])

Later on you can drop unrequired columns from df3

edited Jul 15 '19 at 19:55

Ayoub ZAROU

2,387
6
20

answered Jul 15 '19 at 19:44

Parijat Bhatt

664
4
6

Pandas match with a dict between two columns and create a new column

1 Answers1