3

I have this two data frames:

left = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3'],
                     'B': ['B0', 'B1', 'B2', 'B3'],
                     'key': ['K0', 'K1', 'K0', 'K1']}, 
                    index = [9,10,11,12])

right = pd.DataFrame({'C': ['C0', 'C1'],
                      'key': ['K0', 'K1']})

When I run pd.merge(left, right, on='key') I get a new data frame with new index:

    A   B key   C
0  A0  B0  K0  C0
1  A2  B2  K0  C0
2  A1  B1  K1  C1
3  A3  B3  K1  C1

What I want to get is this

     A   B key   C
9   A0  B0  K0  C0
11  A2  B2  K0  C0
10  A1  B1  K1  C1
12  A3  B3  K1  C1

I can get it by doing pd.merge(left, right, on='key', right_index = True) but I have the feeling that it's more of a coincidence then feature.

Edit

So the solution goes like this:

left.merge(right.set_index("key"), left_on="key", right_index = True)

mjaskowski
  • 1,479
  • 1
  • 12
  • 16

1 Answers1

6

You can also do this:

left.reset_index().merge(right, on='key').set_index('index')

See here: How to keep index when using pandas merge

I must confess I'm not entirely sure when exactly pandas merge preserves the index and when it does not. I would love to be more enlightened about this!

Community
  • 1
  • 1
Matthew
  • 10,361
  • 5
  • 42
  • 54