BUG: incorrect fills when reindexing multi-indexed DataFrames · Issue #29896 · pandas-dev/pandas (original) (raw)

Assignment to multiple columns of a :class:`DataFrame` when some of the columns do not exist would previously assign the values to the last column. Now, new columns would be constructed with the right values. 

.. ipython:: python

   df = pd.DataFrame({'a': [0, 1, 2], 'b': [3, 4, 5]})
   df

*Previous behavior*:

.. code-block:: ipython

   In [3]: df[['a', 'c']] = 1
   In [4]: df
   Out[4]:
      a  b
   0  1  1
   1  1  1
   2  1  1

*New behavior*:

.. ipython:: python

   df[['a', 'c']] = 1
   df

Code Sample, a copy-pastable example if possible

df = pd.DataFrame({ ... 'a': [0, 0, 0, 0], ... 'b': [0, 2, 3, 4], ... 'c': ['A', 'B', 'C', 'D'] ... }).set_index(['a', 'b'])

df c a b
0 0 A 2 B 3 C 4 D df.index MultiIndex([(0, 0), (0, 2), (0, 3), (0, 4)], names=['a', 'b']) mi_2 = pd.MultiIndex.from_product([[0], [-1, 0, 1, 3, 4, 5]])

df.reindex(mi_2, method='backfill') c 0 -1 A 0 A 1 D 3 A 4 A 5 C

df.reindex(mi_2, method='pad') c 0 -1 NaN 0 NaN 1 D 3 NaN 4 A 5 C

Problem description

As far as I understand, the ordering logic for reindexing here should be tuple ordering with the constituent tuples of the MultiIndex -- i.e. df's index has tuples:

(0,  0),
(0,  2),
(0,  3),
(0,  4)

and mi_2's tuples are:

(0, -1),
(0,  0),
(0,  1),
(0,  3),
(0,  4),
(0,  5)

where tuple ordering properties are, for tuples x = (x_1, ..., x_n), y = (y_1, ..., y_n) that x > y iff there exists some i in 1, ..., n such that x_i > y_i and x_j >= y_j for all j in 1, ..., i-1. Moreover, x = y <=> x_i = y_i for all i.

As such, the reindexing of the DataFrame with backfilling should:

• "match" (0, -1) and (0, 0) from mi_2 both to (0, 0) in df.index
• match (0, 1) from mi_2 to (0, 2) in df.index
• match (0, 3) from mi_2 to (0, 3) in df.index
• match (0, 4) from mi_2 to (0, 4) in df.index
• not match (0, 5) from mi_2

Similarly, the reindexing of the DataFrame with forward-filling, aka padding, should:

• not match (0, -1) from mi_2
• match (0, 0) and (0, 1) from mi_2 to (0, 0) in df.index
• match (0, 3) from mi_2 to (0, 3) in df.index
• match (0, 4) and (0, 5) from mi_2 to (0, 5) in df.index

In summary, as far as I can tell, this is simply a bug which was introduced with the new implementation of the multi-indexing backend in 0.23, since I couldn't find anything in the docs about changing the semantics of reindexing. I have a diff locally (will prepare a PR shortly if contributors here are in agreement that this warrants fixing) which addresses these and does not break any existing tests (and which also adds tests which pass on 0.22 but fail on versions >= 0.23), which also suggests to me that this is a bug.

Expected Output

This is with python2.7, numpy 1.16.5, and pandas 0.22.0 -- as far as I can tell, the issue was introduced in 0.23.0.

df.reindex(mi_2, method='backfill') c 0 -1 A 0 A 1 B 3 C 4 D 5 NaN

df.reindex(mi_2, method='pad') c 0 -1 NaN 0 A 1 A 3 C 4 D 5 D

Output of pd.show_versions()

>>> pd.show_versions() >>> pd.show_versions()

INSTALLED VERSIONS

commit : None
python : 3.7.4.final.0
python-bits : 64
OS : Linux
OS-release : 3.10.0-1062.4.1.el7.x86_64
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 0.25.3
numpy : 1.17.0
pytz : 2019.3
dateutil : 2.8.1
pip : 19.3.1
setuptools : 42.0.1
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : None
pandas_datareader: None
bs4 : None
bottleneck : None
fastparquet : None
gcsfs : None
lxml.etree : None
matplotlib : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pytables : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
xarray : None
xlrd : None
xlwt : None
xlsxwriter : None