import pandas as pd
left = pd.DataFrame({'A': ['A1', 'A1', 'A2', 'A3'],
'B': ['B0', 'B1', 'B2', 'B3'],
'C': ['K0', 'K1', 'K0', 'K1']})
right = pd.DataFrame({'AA': ['A1', 'A3'],
'BB': ['B0', 'B3'],
'CC': ['K0', 'K1'],
'DD': ['D0', 'D1']})
I want to join these two data frames by adding column DD
to left
. The values of DD
should be selected based on comparing A
and AA
, B
and BB
, C
and CC
.
The simple joining case would be as as shown below, but in my case I need to compare columns with different names, and then I want only add DD
to right
.
result = left.join(right, on='DD')
The result should be:
result = pd.DataFrame({'A': ['A1', 'A1', 'A2', 'A3'],
'B': ['B0', 'B1', 'B2', 'B3'],
'C': ['K0', 'K1', 'K0', 'K1'],
'DD': ['D0', NaN, NaN, 'D1']})