How to merge two dataframes using multiple columns?

Question

I have two dataframes:

df_1 = pd.DataFrame(columns=["pointid","lat","lon"],data=[[1,41.792145,3.046884],[2,41.799834,3.051082],[3,41.813694,3.063463], [4,41.817673,3.067025]])

df_2 = pd.DataFrame(columns=["id","point_from","point_to"],
                              data=[[1,1,2],[2,1,3],[3,2,3]])

I want to merge them in order to get a new dataframe df_3 with the following columns (example of 1 row):

id    point_from    point_to   lat_from   lon_from   lat_to     lon_to
1     1             2          41.792145  3.046884   41.799834  3.051082

How can I do this?

akuiper · Accepted Answer · 2017-07-17 19:27:01Z

1

One option is to merge df_1 with df_2 twice on point_from and point_to columns respectively:

df_1 = df_1.set_index("pointid")

(df_2.merge(df_1.add_suffix("_from"), left_on="point_from", right_index=True)
     .merge(df_1.add_suffix("_to"), left_on="point_to", right_index=True))

#   id  point_from  point_to   lat_from  lon_from     lat_to    lon_to
#0   1           1         2  41.792145  3.046884  41.799834  3.051082
#1   2           1         3  41.792145  3.046884  41.813694  3.063463
#2   3           2         3  41.799834  3.051082  41.813694  3.063463

answered Jul 17, 2017 at 19:27

akuiper

216k33 gold badges362 silver badges379 bronze badges

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

Collectives™ on Stack Overflow

How to merge two dataframes using multiple columns?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related