0

I can see that what I'm trying to do is possible via concat: Merge dataframes on index

Why can I not do something equivalent using merge?

import pandas as pd

df = pd.DataFrame({'name':['joe strummer','johnny rotten'],'age':[73,80]})
df2 = pd.DataFrame({'name':['nancy','sid'],'age':[17,19]})

df.index.name = 'x'
df2.index.name = 'y'

pd.merge(df2,df, left_on='y',right_on='x')
Community
  • 1
  • 1
whytheq
  • 34,466
  • 65
  • 172
  • 267

1 Answers1

1

This is possible; just use left_index=True instead of left_on, and right_index=True instead of right_on:

>>> pd.merge(df, df2, left_index=True, right_index=True)
   age_x         name_x  age_y name_y
0     73   joe strummer     17  nancy
1     80  johnny rotten     19    sid

Using left_on doesn't work because indexes are separate objects to DataFrame columns. An index can have name, even an identical name to one of your columns, but left_on won't see it because it only looks at column names.

The documentation for merge gives the following guidance for these arguments:

left_index : boolean, default False

Use the index from the left DataFrame as the join key(s). If it is a MultiIndex, the number of keys in the other DataFrame (either the index or a number of columns) must match the number of levels

Alex Riley
  • 169,130
  • 45
  • 262
  • 238
  • thanks - I should have looked at the specification a little closer! coming from a pure database background I'm finding pandas fascinating but not too intuitive. – whytheq Mar 12 '16 at 14:54
  • No problem - I appreciate the fact that pandas' indexes are a little different to say, indexes in MySQL. It's a fantastic library when you get used to the differences though. – Alex Riley Mar 12 '16 at 15:02
  • totally off subject but could I please pick your brains - if our db at work is sql-server will it be easy for me to move data between pandas and that db? – whytheq Mar 12 '16 at 15:09
  • That's not an area I have very much experience with, unfortunately. pandas has various builtin methods for writing to and reading from SQL databases but I don't know if all versions/editions of sql-server are covered. – Alex Riley Mar 12 '16 at 15:21