pandas compare two column from different dataframes and create new column

Question

I have two df's:

df1:

       date                         point_id  
0      2010-10-11               121    
1      2010-10-11               433         
2      2010-10-11               6618

df2:

          id       point_id
0         11       433
1         22       121
2         97       6618

Now I want the df to be like:

       date                     point_id     cal_id 
0      2010-10-11               121          22
1      2010-10-11               433          11
2      2010-10-11               6618         97

What i try:

df1['cal_id'] = df1.where(df1['point_id']==df2['point_id']).notna()

with error:

ValueError: Can only compare identically-labeled Series objects

score 0 · Accepted Answer · answered Dec 09 '20 at 09:19

0

Use merge

df_merged = df1.merge(df2, how="inner", on="point_id")

See here for more.

answered Dec 09 '20 at 09:19

Philipp

652
2
10
28

Please accept my answer if it helped you :) – Philipp Dec 09 '20 at 09:20
how will this create the new column `cal_id`? – GTr Tin Dec 09 '20 at 09:21
the column "id" in `df2` will also appear in the `df_merged`. You can then rename the column, if you want with `df_merged.rename({"id": "cal_id"})`. – Philipp Dec 09 '20 at 09:24
I understand that, "id" and "cal_id" share the same values. Isn't that so? – Philipp Dec 09 '20 at 09:25
what if i only want to merge with the df1 values, would it be left join? – GTr Tin Dec 09 '20 at 10:41

pandas compare two column from different dataframes and create new column

1 Answers1