CODEWITHSUNDEEP
pythonpandasdataframejoin
Vyshnavi
Vyshnavi

Reputation: 149

Join Pandas dataframe columns based on condition

Dataframe A

cid country a_price
0 US 100
1 UK 200
2 CN 300
3 US 400
4 UK 500

Dataframe B

cid country price
4 US 600
5 UK 700
6 CN 800
7 US 900
8 UK 1000

Left Join Dataframe B with A. Join based on country names and cid where cid Condition: cid of B - 4 = cid of A

Result DF:

cid country price a_price
4 US 600 100
5 UK 700 200
6 CN 800 300
7 US 900 400
8 UK 1000 500

Thanks in advance.

Upvotes: 0

Views: 45

Answers (1)

Lara19
Lara19

Reputation: 699

Please try this:

df_all = pd.merge(df_b,df_a[['country','a_price']],on='country', how='left')

Edit: (I just found you said cid of b -4 = cid of A, so please try below instead)

df_b["cid"] = df_b["cid"].apply(lambda x: x - 4)
df_all=pd.merge(df_b,df_a,  how='left', left_on=['cid','country'], 
right_on = 'cid','country')

df_all = df_all[df_all['a_price'].notna()]
df_all["cid"] = df_all["cid"].apply(lambda x: x + 4)

Upvotes: 2

Related Questions