how to join two Pandas dataframes on one column and one index [duplicate]

Question

Suppose I have two DataFrame df1 and df2, the join key in df1 is a column but the key in df2 is the index.

df1
Out[88]: 
   A  B   C
0  1  A  10
1  2  B  20
2  3  C  30
3  4  D  40
4  5  E  50

df2

Out[89]: 
    D  E
A  22  2
B  33  3
C  44  4
D  55  5
E  66  6

I want to do something like,

pd.merge(df1,df2, how= 'outer',left_on="B" , right_on= df2.index )

I know this is sure to fail.I can workaround by reset the index on df2, but in the application I will have to index it back.

df2=df2.reset_index()

I am wondering whether it is possible to just join one column and one index together easily ?

Answer 1

You can specify right_index=True to merge on the index for the rhs:

In [193]:
pd.merge(df1,df2, how= 'outer',left_on="B" , right_index= True )

Out[193]:
   A  B   C   D  E
0  1  A  10  22  2
1  2  B  20  33  3
2  3  C  30  44  4
3  4  D  40  55  5
4  5  E  50  66  6