Allow joins between MultiIndexes and normal Indexes to make use of common keys

[spool]

Hi not sure the best way to describe this but this stackoverflow question explains the problem nicely: http://stackoverflow.com/questions/14149156/merge-multi-indexed-with-single-indexed-data-frames-in-pandas

Would be nice to be able to do this without the awkward reindex. Thanks again for a great project.

Currently on version 0.10.1

[spool]

Here's another question that amounts to the same feature: http://stackoverflow.com/questions/12461470/how-to-broadcast-on-a-single-index-in-hierarchically-indexed-dataframe

[hayd]

I can see how this could be a useful feature. Perhaps merge should take left_levels and right_levels (similar to left_index, left_on) ?

FWIW you can nearly merge like this:

df1.merge(df2, left_on=df1.index.get_level_values('first'), right_on=df2.index.get_level_values('first'))

but it doesn't quite work:

  key_0  value1  value
0     a   0.123     10
1     a   0.234     10
2     a   0.451     10
3     b   0.453     20

where, as the example from SO:

df1 = pd.DataFrame([['a', 'x', 0.123], ['a','x', 0.234], ['a', 'y', 0.451], ['b', 'x', 0.453]], columns=['first', 'second', 'value1']).set_index(['first', 'second'])
df2 = pd.DataFrame([['a', 10],['b', 20]], columns=['first', 'value']).set_index(['first'])

(I'm assuming there's not already a way to do this...)

[spool]

I think the ideal thing would be for index columns with the same name to automatically join. So if I've taken the trouble to name my indices various things, that will automatically be taken advantage of. Perhaps this shouldn't be turned on by default for fear of mistakes, but it seems like a useful addition.

[jreback]

single vs multi-indexing enabled by #6363