pd.merge: MultiIndex column label mistakenly classified as "not unique"

[Mofef]

Code Sample, a copy-pastable example if possible

from numpy import nan
a = np.random.rand(3,4)
a[:,-1] = range(3)
b = np.random.rand(3,4)
b[:,-1] = range(3)
dfa = pd.DataFrame(a,columns=pd.MultiIndex.from_tuples([("n","a"),("n","b"),("n","c"),("x",)]))
dfb = pd.DataFrame(a,columns=pd.MultiIndex.from_tuples([("m","a"),("m","b"),("m","c"),("x",)]))
pd.merge(dfa, dfb, how="outer", on="x")

yields: "ValueError: The column label 'x' is not unique"

Problem description

It would be cumbersome and degrade readability to write on=("x", np.nan) instead of on="x". especially if the real word example would be more like on=("x", np.nan, np.nan, np.nan, np.nan, np.nan)

I think an easy solution would be to add here https://github.com/pandas-dev/pandas/blob/master/pandas/core/generic.py#L1384
sth. like if values.ndim == 2 and values.shape[0] == 1: return values[0]

But actually I was wondering for a long tome, why MultiIndex

• automatically fills np.nan for unknown values BUT
• for selecting treats '' (empty string) as "skippable" levels

(My wording is probably not very clear, but Expected Output should clarify my point)

Expected Output

the same as with

dfa = pd.DataFrame(a,columns=pd.MultiIndex.from_tuples([("n","a"),("n","b"),("n","c"),("x","")]))
dfb = pd.DataFrame(a,columns=pd.MultiIndex.from_tuples([("m","a"),("m","b"),("m","c"),("x","")]))
pd.merge(dfa, dfb, how="outer", on="x")

Output of pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 2.7.12.final.0
python-bits: 64
OS: Linux
OS-release: 4.4.0-119-generic
machine: x86_64
processor: x86_64
byteorder: little
LC_ALL: en_US.utf8
LANG: en_US.UTF-8
LOCALE: None.None

pandas: 0.23.0.dev0+38.g6552718
pytest: 2.8.7
pip: 9.0.1
setuptools: 20.7.0
Cython: 0.23.4
numpy: 1.14.2
scipy: 1.0.0
pyarrow: None
xarray: None
IPython: 5.5.0
sphinx: 1.3.6
patsy: 0.4.1
dateutil: 2.7.2
pytz: 2018.3
blosc: None
bottleneck: None
tables: 3.2.2
numexpr: 2.6.4
feather: None
matplotlib: 2.1.2
openpyxl: 2.3.0
xlrd: 0.9.4
xlwt: 0.7.5
xlsxwriter: 0.7.3
lxml: 3.5.0
bs4: 4.4.1
html5lib: 0.9999999
sqlalchemy: 1.0.11
pymysql: None
psycopg2: 2.6.1 (dt dec mx pq3 ext lo64)
jinja2: 2.8
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

[Mofef]

No comment on this?
In the meanwhile i think my suggested hotfix is not sufficient.

But I get the expected behavior by using the following function to preprocess DataFrames with MultiIndex columns.

def replace_nan_in_column_names(df):
    filtered_tuples = []
    for tup in df.columns.tolist():
        tup = tuple((s if not pd.isna(s) else "") for s in tup)
        filtered_tuples.append(tup)
    df.columns = pd.MultiIndex.from_tuples(filtered_tuples)

[lkugler]

I have a similar issue in python 3.7.1, pandas 0.24.1

import numpy as np
import datetime as dt
import pandas as pd
print(pd.show_versions())

# create two sample DataFrames
t0 = dt.datetime(2019,1,1,0)
t = [t0 + dt.timedelta(hours=i) for i in range(24)]
obs = pd.DataFrame(index=t, data={'T': np.arange(len(t))})
obs['valid'] = obs.index
obs = pd.concat([obs], axis=1, keys=['obs'])

idx = range(48)
t1 = dt.datetime(2019,1,1,12)
t_fcst = [t0 + dt.timedelta(hours=i) for i in range(24)] \
        + [t0 + dt.timedelta(hours=i) for i in range(24)]
fcst = pd.DataFrame(index=idx, data={('ds1', 'T-mod'): np.arange(len(idx)),
                                     ('metadata', 'valid'): t_fcst})

# try to merge
right_on=('obs', 'valid')
left_on = ('metadata', 'valid')

out = pd.merge(fcst, obs, how='left', left_on=left_on, right_on=right_on)

gives me ValueError: The column label 'obs' is not unique. For a multi-index, the label must be a tuple with elements corresponding to each level.

Though 'obs' is clearly unique in the keys, which are:

MultiIndex(levels=[['ds1', 'metadata'], ['T-mod', 'valid']],
           codes=[[0, 1], [0, 1]]) 
MultiIndex(levels=[['obs'], ['T', 'valid']],
           codes=[[0, 0], [0, 1]])

pd.show_versions()

commit: None python: 3.7.1.final.0 python-bits: 64 OS: Darwin OS-release: 18.2.0 machine: x86_64 processor: i386 byteorder: little LC_ALL: de_AT.UTF-8 LANG: de_AT.UTF-8 LOCALE: de_AT.UTF-8

pandas: 0.24.1
pytest: None
pip: 18.1
setuptools: 40.6.3
Cython: None
numpy: 1.16.2
scipy: 1.2.1
pyarrow: None
xarray: None
IPython: 7.3.0
sphinx: 1.8.5
patsy: 0.5.1
dateutil: 2.8.0
pytz: 2018.9
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: 3.0.3
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml.etree: None
bs4: None
html5lib: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None
gcsfs: None
None

[jreback]

``NaN` on an index level is not very well supported, and in general should be avoided. I suppose we would take a patch for this.

[carlosxh]

I got the same problem for py3.7... with pandas 0.24... , after reverted back to py 2.7... and pandas 0.20.2, everything is OK, no such error message with exactly same code and source data.

[roupenminassian]

Is there a patch to fix this?