DEPR: Merging on different number of levels

[dinya]

Two dataframes with MultiIndex in columns (shapes are different).

df1_columns = pd.MultiIndex.from_tuples([("A0", "B0", "C0"), ("A1", "B1", "C1")])
df1 = pd.DataFrame([[1, 2], [10, 20]], columns=df1_columns)

df2_columns = pd.MultiIndex.from_tuples([("X0", "Y0"), ("X1", "Y1")])
df2 = pd.DataFrame([[1, 200], [10, 200]], columns=df2_columns)

Case 1

pd.merge(df1, df2, left_on=[("A0", "B0","C0")], right_on=[("X0", "Y0")])

returns

   (A0, B0, C0)  (A1, B1, C1)  (X0, Y0)  (X1, Y1)
0             1             2         1       200
1            10            20        10       200

It's ok. Columns saved its structure from the both dataframes. They are tuples, but it can be fixed (for example with something like this).

Case 2

pd.merge(df2, df1, left_on=[("X0", "Y0")], right_on=[("A0", "B0","C0")])

returns

   X0   X1  A0  A1
   Y0   Y1  B0  B1
0   1  200   1   2
1  10  200  10  20

When the left DataFrame is a DataFrame with fewer column levels the merge result is questionable: lower levels are dropped.

Is this a bug or a feature (result levels shape must be the same as for the left DataFrame)?

Notes:

1. In both cases the warning is the same UserWarning: merging between different levels can give an unintended result ....
2. Checked with pandas 0.25.1 and 1.0.4.
3. See notebook.

---

• I have searched the [pandas] tag on StackOverflow for similar questions.

• I have asked my usage related question on StackOverflow (see 62490244).

[TomAugspurger]

Does the UesrWarning make it clear that you should be passing the same number of levels?

[dinya]

Does the UesrWarning make it clear that you should be passing the same number of levels?

Yes, the warning is clear. But why not to add auto-leveling of columns with different levels? If it can break something (I really don't know what it is) then the pd.merge behaviour may be optional.

The auto-leveling can be useful if the pandas is used in an automatic data-processing flow when the column levels shapes are not known ahead.

Or does this feature look like "rarely used" and therefore unnecessary?

[TomAugspurger]

I'm not sure. Perhaps investigate the original issue where the warning was added?

[dinya]

Ok, investigation is started...

The warning was added in bb9b9c5 (line 200) by @nbonnotte and @jreback after #9455 and #12219.

See discussion in #12219 starting with this comment (as result the warning was added?).

@jreback wrote in this comment

my point is that the default of this operation is not generally wanted (IOW a user almost certainly would be suprised that this DOES not merge on a particular level). So I would like to see this raise a helpful error message and force the user to be proactive (e.g. pass an option) to actually merge a combined multi-level with a single level.

The default is too flexible here. We could simply show a warning, but warnings are often ignored.

I don't have a concrete idea of how to do this ATM.

@jreback, did you mean "option == optional parameter for pd.merge function to auto-leveling"?

If so, is the optional parameter for auto-leveling of multi-index columns is an actual issue?

[jreback]

@dinya I think the warning is fine, meaning some people really want to do this, but I am open to deprecating this and simply prohibiting merging on different number of levels as the results are unexpected.

[shawnbissell]

@TomAugspurger @jreback was this level dropping deprecated/prohibited in the 1.2.0 release? After upgrading I'm seeing "ValueError: Length of names must match number of levels in MultiIndex." where as with 1.1.5 I previously saw the UserWarning.

[jreback]

@shawnbissell you would have to show a reproducible example