Can't combine DatetimeIndex MultiIndex with numeric MultiIndex

[nackerley]

Code Sample, a copy-pastable example if possible

import numpy as np
import pandas as pd
from itertools import combinations

columns_list = [ 
    pd.MultiIndex.from_product([['a'], range(2)]),
    pd.MultiIndex.from_product([['a'], np.arange(2.)]),
    pd.MultiIndex.from_product([['b'], ['A', 'B']]),
    pd.MultiIndex.from_product([['c'], pd.date_range(start='2017', end='2018', periods=2)])]

dfs = [pd.DataFrame(np.zeros((1, len(columns))), columns=columns) for columns in columns_list]
for i, df in enumerate(dfs):
    print('dataframe:', i)
    print(df)
    print()
for i, j in combinations(range(len(dfs)), 2):
    print('combining:', i, j)
    try: 
        temp = pd.concat((dfs[i], dfs[j]), axis=1)
        print(temp)
    except TypeError as ex:
        print(repr(ex))
    print()

Output

dataframe: 0
     a     
     0    1
0  0.0  0.0

dataframe: 1
     a     
   0.0  1.0
0  0.0  0.0

dataframe: 2
     b     
     A    B
0  0.0  0.0

dataframe: 3
           c           
  2017-01-01 2018-01-01
0        0.0        0.0

combining: 0 1
     a               
   0.0  1.0  0.0  1.0
0  0.0  0.0  0.0  0.0

combining: 0 2
     a         b     
     0    1    A    B
0  0.0  0.0  0.0  0.0

combining: 0 3
TypeError("'values' is not ordered, please explicitly specify the categories order by passing in a categories argument.",)

combining: 1 2
     a         b     
   0.0  1.0    A    B
0  0.0  0.0  0.0  0.0

combining: 1 3
TypeError("'values' is not ordered, please explicitly specify the categories order by passing in a categories argument.",)

combining: 2 3
     b                        c                    
     A    B 2017-01-01 00:00:00 2018-01-01 00:00:00
0  0.0  0.0                 0.0                 0.0

Problem description

I expect that you should be able to combine any MultiIndex of any type, as long as the number of levels matches. It turns out you can combine DatetimeIndex with str, but if you try to combine it with with int or float a baffling TypeError is raised.

At the moment the only workaround I can see is to convert numeric indices to strings.

Expected Output

dataframe: 0
     a     
     0    1
0  0.0  0.0

dataframe: 1
     a     
   0.0  1.0
0  0.0  0.0

dataframe: 2
     b     
     A    B
0  0.0  0.0

dataframe: 3
           c           
  2017-01-01 2018-01-01
0        0.0        0.0

combining: 0 1
     a               
     0    1  0.0  1.0
0  0.0  0.0  0.0  0.0

combining: 0 2
     a         b     
     0    1    A    B
0  0.0  0.0  0.0  0.0

combining: 0 3
     a                        c                    
     0    1 2017-01-01 00:00:00 2018-01-01 00:00:00
0  0.0  0.0                 0.0                 0.0

combining: 1 2
     a         b     
   0.0  1.0    A    B
0  0.0  0.0  0.0  0.0

combining: 1 3
     a                        c                    
   0.0  1.0 2017-01-01 00:00:00 2018-01-01 00:00:00
0  0.0  0.0                 0.0                 0.0

combining: 2 3
     b                        c                    
     A    B 2017-01-01 00:00:00 2018-01-01 00:00:00
0  0.0  0.0                 0.0                 0.0

Output of pd.show_versions()

INSTALLED VERSIONS ------------------ commit: None python: 3.6.3.final.0 python-bits: 64 OS: Linux OS-release: 3.10.0-862.11.6.el7.x86_64 machine: x86_64 processor: x86_64 byteorder: little LC_ALL: C LANG: en_US.utf8 LOCALE: None.None

pandas: 0.23.4
pytest: 3.7.4
pip: 9.0.1
setuptools: 36.3.0
Cython: None
numpy: 1.14.2
scipy: 1.0.0
pyarrow: None
xarray: None
IPython: 6.1.0
sphinx: 1.6.3
patsy: 0.5.0
dateutil: 2.6.1
pytz: 2017.2
blosc: None
bottleneck: 1.2.1
tables: 3.4.4
numexpr: 2.6.4
feather: None
matplotlib: 2.2.2
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: 4.0.0
bs4: None
html5lib: 0.999999999
sqlalchemy: 1.1.13
pymysql: None
psycopg2: 2.7.5 (dt dec pq3 ext lo64)
jinja2: 2.9.6
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

[gfyoung]

@nackerley : Thanks reporting this! Admittedly, your use-cases seem a little unusual, but I am uncertain that we should be raising as you said. Let's get some more feedback before proceeding.

cc @toobaz @jreback

[toobaz]

Can't reproduce with master (although some different errors pop out under python 2). Might be related to #22072 (just a guess, can't check right now).

[mroeschke]

Looks to be fixed on master. Could use a test

In [114]: import numpy as np
     ...: import pandas as pd
     ...: from itertools import combinations
     ...:
     ...: columns_list = [
     ...:     pd.MultiIndex.from_product([['a'], range(2)]),
     ...:     pd.MultiIndex.from_product([['a'], np.arange(2.)]),
     ...:     pd.MultiIndex.from_product([['b'], ['A', 'B']]),
     ...:     pd.MultiIndex.from_product([['c'], pd.date_range(start='2017', end='2018', periods=2)])]
     ...:
     ...: dfs = [pd.DataFrame(np.zeros((1, len(columns))), columns=columns) for columns in columns_list]
     ...: for i, df in enumerate(dfs):
     ...:     print('dataframe:', i)
     ...:     print(df)
     ...:     print()
     ...: for i, j in combinations(range(len(dfs)), 2):
     ...:     print('combining:', i, j)
     ...:     try:
     ...:         temp = pd.concat((dfs[i], dfs[j]), axis=1)
     ...:         print(temp)
     ...:     except TypeError as ex:
     ...:         print(repr(ex))
     ...:     print()
     ...:
dataframe: 0
     a
     0    1
0  0.0  0.0

dataframe: 1
     a
   0.0  1.0
0  0.0  0.0

dataframe: 2
     b
     A    B
0  0.0  0.0

dataframe: 3
           c
  2017-01-01 2018-01-01
0        0.0        0.0

combining: 0 1
     a
   0.0  1.0  0.0  1.0
0  0.0  0.0  0.0  0.0

combining: 0 2
     a         b
     0    1    A    B
0  0.0  0.0  0.0  0.0

combining: 0 3
     a                        c
     0    1 2017-01-01 00:00:00 2018-01-01 00:00:00
0  0.0  0.0                 0.0                 0.0

combining: 1 2
     a         b
   0.0  1.0    A    B
0  0.0  0.0  0.0  0.0

combining: 1 3
     a                        c
   0.0  1.0 2017-01-01 00:00:00 2018-01-01 00:00:00
0  0.0  0.0                 0.0                 0.0

combining: 2 3
     b                        c
     A    B 2017-01-01 00:00:00 2018-01-01 00:00:00
0  0.0  0.0                 0.0                 0.0


In [115]: pd.__version__
Out[115]: '1.1.0.dev0+1068.g49bc8d8c9'

[VictoriaKfk]

Hello! @mroeschke Shall I take this one and write a test for it? I'm looking for a good first issue as it's my first time contributing to this project. Any tips are welcome to help me jumpstart

[mroeschke]

Yes go for it @VictoriaKfk. https://pandas.pydata.org/pandas-docs/stable/development/contributing.html#test-driven-development-code-writing will help with testing guidelines