6

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 ?

Peter Li
  • 341
  • 3
  • 8

2 Answers2

8

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
EdChum
  • 376,765
  • 198
  • 813
  • 562
0

I think you can also use join:

df1.join(df2, on='B', how='outer')

Happy001
  • 6,103
  • 2
  • 23
  • 16