Passing level to reindex on a multi-index doesn't reindex the desired level

[iaindillingham]

Code Sample

import numpy as np
import pandas as pd

category_idx = pd.Index(['A', 'B'])
date_idx = pd.date_range('2018-01', '2018-02', freq='MS')
idx = pd.MultiIndex.from_product([category_idx, date_idx], names=['category', 'date'])

series = pd.Series(np.random.randn(len(category_idx) * len(date_idx)), index=idx)
series
# category  date      
# A         2018-01-01   -0.060216
#           2018-02-01    0.757948
# B         2018-01-01    0.411784
#           2018-02-01   -0.062216
# dtype: float64

new_date_idx = date_idx.union([date_idx[-1] + date_idx.freq])
new_date_idx
# DatetimeIndex(['2018-01-01', '2018-02-01', '2018-03-01'], dtype='datetime64[ns]', freq='MS')

series.reindex(index=new_date_idx, level='date')
# Where are the NaNs?
# category  date      
# A         2018-01-01   -0.060216
#           2018-02-01    0.757948
# B         2018-01-01    0.411784
#           2018-02-01   -0.062216
# dtype: float64

series.loc['A'].reindex(index=new_date_idx)
# 2018-01-01   -0.060216
# 2018-02-01    0.757948
# 2018-03-01         NaN
# Freq: MS, dtype: float64

Problem description

Passing level to reindex on a multi-index doesn't reindex the desired level.

Expected Output

In the above example, I'd expect to see two new rows, each containing NaN. The expected output is analogous to calling reindex on a single-level index.

I originally posted this issue as a question to StackOverflow, where a user kindly posted a workaround: https://stackoverflow.com/questions/54904974/extending-a-datetime-index-within-a-multi-index.

Output of pd.show_versions()

INSTALLED VERSIONS ------------------ commit: None python: 3.6.6.final.0 python-bits: 64 OS: Linux OS-release: 4.15.0-45-generic machine: x86_64 processor: x86_64 byteorder: little LC_ALL: None LANG: en_GB.UTF-8 LOCALE: en_GB.UTF-8

pandas: 0.24.1
pytest: None
pip: 19.0.3
setuptools: 40.4.3
Cython: None
numpy: 1.15.3
scipy: 1.2.0
pyarrow: 0.12.1
xarray: None
IPython: 7.3.0
sphinx: None
patsy: None
dateutil: 2.7.3
pytz: 2018.6
blosc: None
bottleneck: 1.2.1
tables: None
numexpr: 2.6.8
feather: None
matplotlib: 3.0.0
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml.etree: 4.2.5
bs4: 4.7.1
html5lib: None
sqlalchemy: 1.2.12
pymysql: 0.9.2
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: 0.7.0
gcsfs: None

[gfyoung]

cc @toobaz

[dmvianna]

Present in 0.23.4

It works for an Index (new row created)

pd.DataFrame(dict(a=[1,2],b=[2,3]), index=[1,2]).reindex([1,2,3])

But not for a MultiIndex (new row is not created)

a = pd.DataFrame(dict(a=[1,2],b=[2,3]), index=[[1,2],[3,4]])
a.index.names = ['a','b']
a.reindex([1,2,3], level='a')

This affects joins too. Right joins of a MultiIndex to an Index will have the same effect as an inner join. Could be related.

[ybagdasa]

@simonjayhawkins Has this issue been resolved? I'm just encountering it in pandas 1.4.2.

[jreback]

it's an open issue - you or anyone in the community can submit a patch

[ybagdasa]

I'm spent a few hours looking into the issue, but I am not familiar enough with index joins to know what the problem is. I think it might be happening in the function Index._join_level found in pandas/core/indexes/base.py.

Within the function new_level, left_lev_indexer, right_lev_indexer = old_level.join( right, how=how, return_indexers=True )
is called. The variable how is a right join. Not sure whether the outputs are as expected here. new_level seems to be correct.

Finally near the end of the function
join_index = MultiIndex( levels=new_levels, codes=new_codes, names=left.names, verify_integrity=False, )
is called. The new_codes appear to be the same as the old multiindex codes. I wonder if this is the issue.

Hope that helps someone willing to fix it.

[simonjayhawkins]

Thanks @ybagdasa for the investigation. Given this issue is a duplicate of #21147, which was itself a duplicate and closed. so closing this as duplicate of #12319 for the same reasons #21147 (comment) to help keep all the discussion in one place. (as there is also overlap with #7895)

feel free to add your comments to those issues.