ENH: Support merging DataFrames on a combination of columns and index levels

[jonmmease]

• .groupby ENH/API: clarify groupby by to handle columns/index names #5677
• .sort_values ENH: Support sorting DataFrames by a combination of columns and index levels #14353, though this is directly in combat with .sort_index and non-explict
• .merge (this issue)

Overview

@jorisvandenbossche
As a part of the Pandas 1.0 goal to "Make the index/column distinction less painful (#5677, #8162)" I propose that the df.merge method support merging DataFrames on a combination of columns and index levels.

This could be accomplished in the API by allowing the on, left_on, and right_on keywords to accept a combination of column names and index level names. Any index levels that are joined on would be preserved as index levels in the resulting merged DataFrame, while all other index levels would be removed.

This proposal is in the spirit of #5677 for df.groupby and #14353 for df.sort_values.

[jorisvandenbossche]

+1 on this proposal. As I said in the related issue (#14353 (comment)), would be nice to have a general solution for this, but for now enabling this behaviour for specific functions/keywords is fine for me.

@jreback @wesm @TomAugspurger @shoyer @sinhrks @chris-b1 any concerns of feedback regarding this proposal, before work is done to implement it? (@jmmease you plan to tackle this if OK?)

[jonmmease]

@jorisvandenbossche Yes, if the direction is agreeable I plan to begin tackling this set of issues during the next month or two.
Thanks for the feedback!

[TomAugspurger]

I think I'm in favor of all the changes. Thanks for taking them on @jmmease!

[shoyer]

Yes, seems like a good idea to me.

[shoyer]

We do need to clarify how we will handle conflicting index/column names in a uniform way. For backwards compatibility, I think we need to always check column names before falling back to use index names.

[jonmmease]

@shoyer Agreed regarding conflict resolution. Thanks for the feedback!