REGR: fix error caused by deprecation warning in pd.merge when joining two Series
State: closed

- [x] [Tests added and passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#writing-tests) if fixing a bug or adding a new feature
- [x] All [code checks passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#pre-commit).

There is a small bug/typo in the warning of message of `pd.merge` when merging two `Series` of different levels which results in an exception.  

Thrown error message:
`AttributeError: 'Series' object has no attribute 'columns'`

Labels:
name: Reshaping
description: Concat, Merge/Join, Stack/Unstack, Explode
name: Regression
description: Functionality that used to work in a prior pandas version
name: Warnings
description: Warnings that appear or should be added to pandas

Comments:
@phofl There's no warning. An exception occurs while processing the warning, and I've included the message in my original post.

The reason for the exception is that `left` and `right` might be a Series and they don't have the `columns` attribute. While `_left` and `_right` are the converted DataFrames.
Sorry, did not understand you then.

could you please add a test? 
And a whatsnew in 1.4.4, this is a regression
@phofl No worries. I'll add one in a few hours.
@nsarang would you have time to add a test ?
@jorisvandenbossche Forgot about this one!
I've added the test now. The explanation for the test is that using `to_frame` on a Series with a multi-level name will result in a multi-level column DataFrame (to_frame is used in pd.merge._validate_operand). So it'll create the right condition where the levels of `left` and `right` in pd.merge are not equal.
Sure
@phofl  I can't reference the PR in the release notes since its path is not added to `sphinx.ext.extlinks`. Do you want me to add it?
It is sufficient if you add something like ``Fixed regression in :func:`merge` when...``

to https://github.com/pandas-dev/pandas/blob/main/doc/source/whatsnew/v1.4.4.rst

You don't have to do anything else. Maybe you did not merge main in a while?
Fine by me. Just wanted to make sure it's okay that the release note is not having a reference.

> Maybe you did not merge main in a while?

I don't follow. Can you explain?
I pushed a small patch to ensure we catch the warning in the test (ideally, our tests don't cause warnings from pandas, if a warning is expected, it should be either asserted or explicitly ignored).

However, doing that also made we wonder why we are actually raising a deprecation warning here. The case from the test:

```
In [3]: merge(a, b, on=["outer", "inner"])
<ipython-input-3-4caf266e26f3>:1: FutureWarning: merging between different levels is deprecated and will be removed in a future version. (1 levels on the left, 2 on the right)
  merge(a, b, on=["outer", "inner"])
Out[3]: 
             A  (B, C)
outer inner           
a     1      2       1
b     1      4       3

In [4]: a
Out[4]: 
             A
outer inner   
a     0      1
      1      2
b     0      3
      1      4

In [5]: b
Out[5]: 
outer  inner
a      1        1
       2        2
b      1        3
       2        4
Name: (B, C), dtype: int64
```

So the original `a` and `b` don't have different number of levels in the columns. It's only because the Series is converted to a DataFrame that pandas itself introduces that. It's of course quite a corner case ..
@jorisvandenbossche 

We wanted to get rid of value dependent behaviour. Your op looks "correct", but if "a" also has a midx, then the tuple name is converted into a MultiIndex itself:

```
a = DataFrame(
    {("A", "B"): 1},
    index=MultiIndex.from_product([["a"], [0, 1]], names=["o", "i"]),
)
b = Series(
    1,
    index=MultiIndex.from_product([["a"], [1, 2]], names=["o", "i"]),
    name=("B", "C"),
)
pd.merge(b, a, on=["o", "i"])
```

And the more complicated case:

```
a = DataFrame(
    {("A", "B"): 1},
    index=MultiIndex.from_product([["a"], [0, 1]], names=["o", "i"]),
)
b = Series(
    1,
    index=MultiIndex.from_product([["a"], [1, 2]], names=["o", "i"]),
    name=("B", "C", "D"),
)
pd.merge(a, b, on=["o", "i"])
```

raises: ``ValueError: Length of names must match number of levels in MultiIndex.``

If the DataFrame has more levels than our tuple name has elements, we flatten the DataFrame MultiIndex columns to a tuple too:

```
a = DataFrame(
    {("A", "B", "C"): 1},
    index=MultiIndex.from_product([["a"], [0, 1]], names=["o", "i"]),
)
b = Series(
    1,
    index=MultiIndex.from_product([["a"], [1, 2]], names=["o", "i"]),
    name=("B", "C"),
)
pd.merge(a, b, on=["o", "i"])
```

```
     (A, B, C)  (B, C)
o i                   
a 1          1       1
```
thx @nsarang 
Owee, I'm MrMeeseeks, Look at me.

There seem to be a conflict, please backport manually. Here are approximate instructions:

1. Checkout backport branch and update it.

```
git checkout 1.4.x
git pull
```

2. Cherry pick the first parent branch of the this PR on top of the older branch:
```
git cherry-pick -x -m1 236cedfe874016a42bc3fe226a65316c514a0e41
```

3. You will likely have some merge/cherry-pick conflict here, fix them and commit:

```
git commit -am 'Backport PR #47946: REGR: fix error caused by deprecation warning in pd.merge when joining two Series'
```

4. Push to a named branch:

```
git push YOURFORK 1.4.x:auto-backport-of-pr-47946-on-1.4.x
```

5. Create a PR against branch 1.4.x, I would have named this PR:

> "Backport PR #47946 on branch 1.4.x (REGR: fix error caused by deprecation warning in pd.merge when joining two Series)"

And apply the correct labels and milestones.

Congratulations — you did some good work! Hopefully your backport PR will be tested by the continuous integration and merged soon!

Remember to remove the `Still Needs Manual Backport` label once the PR gets merged.

If these instructions are inaccurate, feel free to [suggest an improvement](https://github.com/MeeseeksBox/MeeseeksDev).
                
> And a whatsnew in 1.4.4, this is a regression

and to be clear, this is a regression from 1.0.5?

I've added the deprecation in the 1.3 series, which causes the error
the code sample (test) was raising an exception before that

```python-traceback
~/miniconda3/envs/pandas-1.1.5/lib/python3.9/site-packages/pandas/core/reshape/merge.py in __init__(self, left, right, how, on, left_on, right_on, axis, left_index, right_index, sort, suffixes, copy, indicator, validate)
    639                 "merging between different levels can give an unintended "
    640                 f"result ({left.columns.nlevels} levels on the left,"
--> 641                 f"{right.columns.nlevels} on the right)"
    642             )
    643             warnings.warn(msg, UserWarning)

~/miniconda3/envs/pandas-1.1.5/lib/python3.9/site-packages/pandas/core/generic.py in __getattr__(self, name)
   5139             if self._info_axis._can_hold_identifiers_and_holds_name(name):
   5140                 return self[name]
-> 5141             return object.__getattribute__(self, name)
   5142 
   5143     def __setattr__(self, name: str, value) -> None:

AttributeError: 'Series' object has no attribute 'columns'
```
Ok this is weird. I remember testing this and I thought I installed 1.2.5 for testing purposes. But can not reproduce now. Should we avoid backporting then?
the fix seems low risk so (imo) ok with the backport. 

however, if there is an outstanding issue about whether we should be raising a warning here https://github.com/pandas-dev/pandas/pull/47946#issuecomment-1220404403, then should open an issue about that to ensure it is not forgotten. Whether any further changes get backported would be part of that discussion.
Ok, got it.

I think the warning is oldish, the current behaviour is definitely buggy (https://github.com/pandas-dev/pandas/pull/47946#issuecomment-1220607705), so in principal I am ok with he warning, but we could also avoid treating tuples as MultiIndexes. Disallowing this would avoid any potential value dependent bugs
Yes, indeed with the current behaviour in other cases, the warning seems correct. 
It's maybe worth a more general question whether we want to continue interpreting a tuple label as MultiIndex (but if you select a Series from a DataFrame with MI columns, you also get such tuple label, so it makes some sense to do that)