BUG: .loc indexing into MultiIndex with tuple with non-found keys gives wrong error

[tikael1011]

Code Sample, a copy-pastable example if possible

# Your code here

idx = ['str1', 'str2', 'str3']
df_full.set_index(idx, drop=False, inplace=True)
for i, rc in df.iterrows():
    df.at[i, 'prev_state'] = df_full.loc[tuple(rc[e] for e in idx)]['state']

## df is a dataframe with columns in idx and prev_state

Problem description

The above piece of code works in 0.23.4, yet after I upgraded to 0.24.1, it throws

  File "/usr/local/lib/python3.6/dist-packages/pandas/core/indexing.py", line 1494, in __getitem__
    return self._getitem_tuple(key)
  File "/usr/local/lib/python3.6/dist-packages/pandas/core/indexing.py", line 873, in _getitem_tuple
    self._has_valid_tuple(tup)
  File "/usr/local/lib/python3.6/dist-packages/pandas/core/indexing.py", line 221, in _has_valid_tuple
    raise IndexingError('Too many indexers')
pandas.core.indexing.IndexingError: Too many indexers

I did compare that part of source code in both 0.23.4 and 0.24.1, and they look like the same.(maybe I am wrong) Please help.

Output of pd.show_versions() for 0.23.4

INSTALLED VERSIONS

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

pandas: 0.23.4
pytest: None
pip: 9.0.1
setuptools: 40.6.2
Cython: None
numpy: 1.15.4
scipy: 1.1.0
pyarrow: None
xarray: None
IPython: None
sphinx: None
patsy: None
dateutil: 2.7.4
pytz: 2018.6
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: None
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: None
sqlalchemy: 1.2.16
pymysql: None
psycopg2: 2.7.6.1 (dt dec pq3 ext lo64)
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None
None

Output of pd.show_versions() for 0.24.1

INSTALLED VERSIONS

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

pandas: 0.24.1
pytest: None
pip: 9.0.1
setuptools: 40.6.2
Cython: None
numpy: 1.15.4
scipy: 1.1.0
pyarrow: None
xarray: None
IPython: None
sphinx: None
patsy: None
dateutil: 2.7.4
pytz: 2018.6
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: None
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml.etree: None
bs4: None
html5lib: None
sqlalchemy: 1.2.16
pymysql: None
psycopg2: 2.7.6.1 (dt dec pq3 ext lo64)
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None
gcsfs: None
None

[jorisvandenbossche]

@tikael1011 Can you please provide a reproducible example? So some code that creates the df and df_full dataframes. Otherwise it is impossible to know what is going on.
(see eg http://matthewrocklin.com/blog/work/2018/02/28/minimal-bug-reports for more explanation)

[tikael1011]

cdb = importlib.import_module('xxx.xxxx.models')
def get_full():

    full_obj = getattr(cdb, 'table_name')
    full_query = Session.query(full_obj).filter(
        full_obj.accountId == obj.accountId)
    if not full_query.first():
        return pd.DataFrame(), None
    latest_date = Session.query(full_obj).filter(
        full_obj.accountId == obj.accountId
    ).order_by(full_obj.date.desc()).first().date

    df = pd_read_sql(full_query.statement, con=Session.bind)
    idx = ['str1', 'str2', 'str3']
    df = df.sort_values('date').groupby(idx).tail(1)
    df = df.where(pd.notnull(df), None)
    df.set_index(idx, drop=False, inplace=True)
    return df, latest_date

## this following part of code works both 0.23.4 and 0.24.1
def get_df(df_shadow, df_diff, idx):
    for i, rc in df_diff.iterrows():
        df = df.append(df_shadow.loc[tuple(rc[e] for e in idx)])
    return df

'''
example of df_diff

str1, str2, str3, str4
123, 456, 789, 8
234, 345, 567, 7
345, 567, 890, 6

example of df_shadow:

str1, str2, str3, str4
123, 456, 789, 10
122, 467, 234, 8
234, 345, 567, 5
344, 890, 345, 1
345, 567, 890, 2

example of expected df
str1, str2, str3, prev_str4, str4
123, 456, 789, 10, 8
234, 345, 567, 5, 7
345, 567, 890, 2, 6
'''

Hopefully these snippet is adequate.

[jorisvandenbossche]

Please provide runnable code. Something we can copy paste in a python session and execute. The above makes use of unknown variables.
If the example dataframes you show are sufficient to reproduce the issue, you could adapt the code to create and use those.

[tikael1011]

it is 4 am here...I will provide runnable code with some real data asap

[jorisvandenbossche]

No hurry!
And to be clear: the reason we ask this is because we get a lot of bug reports, and it is very helpful if bug reports provide a clear, minimal reproducible example. Otherwise the chance is high it will not get much attention.

[tikael1011]

import pandas as pd

df1 = pd.DataFrame([{'id': 6.0, 'b': 102556776032464.0, 'value': 0.79, 'c': 46127581169045.0, 'a': 1070770444536.0, 'prev_value': None}])

df2 = pd.DataFrame([{'id': 6, 'c': 117747917820594, 'b': 166082626904243, 'a': 161128393449045, 'value': 0.65}])

idx = ['a', 'b', 'c']

df2.set_index(idx, drop=False, inplace=True)

for i, rc in df1.iterrows():
	df1.at[i, 'prev_value'] = df2.loc[tuple(rc[e] for e in idx)]['value']
	

the result of the above varies using different pandas version. For 0.23.4 I got KeyError [label not in index] yet for 0.24.1 I got pandas.core.indexing.IndexingError: Too many indexers

[jorisvandenbossche]

@tikael1011 Thanks for the reproducible example.

Simplified it a little bit further:

In [21]: df = pd.DataFrame([[1, 2, 3, 4]], columns=['a', 'b', 'c', 'd']) 

In [22]: df = df.set_index(['a', 'b', 'c'])

In [23]: df
Out[23]: 
       d
a b c   
1 2 3  4

In [24]: df.loc[(1, 2, 3)]
Out[24]: 
d    4
Name: (1, 2, 3), dtype: int64

In [27]: df.loc[(2., 2., 3.)] 
...
IndexingError: Too many indexers

In 0.23.4 this gave correctly a KeyError.

Above, you said that it was working in pandas 0.23.4, but failing on 0.24. But it's only the error that changed?

[tikael1011]

yes, in 0.23.4, it throws 'KeyError', which is correct/expected since not all entries can be found in df, yet in 0.24.1 it throws too many indexers error. An easy go-around would be to catch new exception as well (my code already deal with KeyError) yet the real problem is why the error path changed?

[jorisvandenbossche]

OK, just wanted to make sure I fully understand the issue.

the real problem is why the error path changed?

That was certainly not on purpose I think, that is a bug.