Python Pandas: Joining Dataframes

Question

I have table A and Table B. I want to join them to get Table C. I tried the following code. But it is not giving me the result that I want.

C = pd.merge(A, B, how = 'inner', left_on = ['ID1', 'ID2', 'ID3'], right_on = ['IDA', 'IDB', 'IDC'])

Table A

ID1	ID2	ID3	Color	Flag
A	1	1	White	Y
B	1	2	Black	Y
A	1	3	Green	N
E	2	3	Blue	Y
D	4	5	Blue	N
C	6	7	Red	N
F	9	7	Black	Y

Table B

IDA	IDB	IDC
A	1	1
F	9	7
A	1	3
D	4	5

Table C

ID1	ID2	ID3	Color	Flag
A	1	1	White	Y
A	1	3	Green	N
D	4	5	Blue	N
F	9	7	Black	Y

Answer 1

here is one way to do it

# do a left merge and rop the null rows
out=(pd.merge(df, df2, 
          how = 'left', 
          left_on = ['ID1', 'ID2', 'ID3'], 
          right_on = ['IDA', 'IDB', 'IDC'])
 .dropna()
 .drop(columns=['IDA', 'IDB','IDC']))

ID1     ID2     ID3     Color   Flag
0   A   1   1   White   Y
2   A   1   3   Green   N
4   D   4   5   Blue    N
6   F   9   7   Black   Y

alternately, if these are the only columns in your DF, you can convert these to string to make them of same type. that too is only for join and not affecting the DFs

(pd.merge(df.astype(str), df2.astype(str), 
          how = 'left', 
          left_on = ['ID1', 'ID2', 'ID3'], 
          right_on = ['IDA', 'IDB', 'IDC'])
 .dropna()
 .drop(columns=['IDA', 'IDB','IDC']))

    ID1     ID2     ID3     Color   Flag
0   A   1   1   White   Y
2   A   1   3   Green   N
4   D   4   5   Blue    N
6   F   9   7   Black   Y