API: label-based slicing with not-included labels

[jorisvandenbossche]

I didn't directly find an issue about it, or an explanation in the docs, but I stumbled today on the following, which did surprise me a bit:

Considering the following dataframe:

In [18]: df = pd.DataFrame(np.random.randn(5,2), index=pd.date_range('2012-01-01', periods=5))

In [19]: df
Out[19]:
                   0         1
2012-01-01  2.511337 -0.776326
2012-01-02  0.133589  0.441911
2012-01-03  0.348167  1.285188
2012-01-04  1.075843  1.282131
2012-01-05  0.683006  0.558459

Slicing with a label that is not included in the index works with .ix, but not with .loc:

In [20]: df.ix['2012-01-03':'2012-01-31']
Out[20]:
                   0         1
2012-01-03  0.348167  1.285188
2012-01-04  1.075843  1.282131
2012-01-05  0.683006  0.558459

In [21]: df.loc['2012-01-03':'2012-01-31']
...
KeyError: 'stop bound [2012-01-31] is not in the [index]'

Context: I was updating some older code, and I wanted to replace .ix with .loc (as this is what we recommend if it is purely label based to prevent confusion).

Some things:

• If this is intended, I don't find this stated somewhere in the docs. So the docs are at least lacking at this point.
• the inconsistency between [], .ix[] and .loc[] is a bit surprising here
• it is also inconsistent with iloc -> that behaviour was changed in 0.14 to allow out of bound slicing (http://pandas.pydata.org/pandas-docs/stable/whatsnew.html#whatsnew-0140-api)
• Specifically for datetime-line indexing, it is also inconsistent with the feature of partial string indexing: df.loc['2012-01-03':'2012-01'] will work and do the expected while df.loc['2012-01-03':'2012-01-31'] fails

[jorisvandenbossche]

Ah, seems there was a (closed) issue related to this: #5223

[jorisvandenbossche]

Also, and this was the actual use case, how would be the following be best done?

In [32]: df = pd.DataFrame(np.random.randn(60,3), index=pd.date_range('2012-01-01', periods=60), columns=['a', 'b', 'c'])
In [33]: df.iloc[25:35,1] = np.nan

In [34]: df = df.dropna()

So you have a dataframe, and for some reason there are some NaNs, and these data have to be removed. With the resulting dataframe, I now want to select all data up to the end of January for columns a and d. So my code did:

In [36]: df.ix[:'2012-01-31', ['a', 'b']]
Out[36]:
                   a         b
2012-01-01  2.988938 -0.818541
2012-01-02  0.068606  0.203595
2012-01-03  0.748376  0.397277
2012-01-04  1.577034 -1.061293
...
2012-01-22  0.079241  0.330411
2012-01-23 -0.799368 -1.815083
2012-01-24 -0.177154  0.329965
2012-01-25  0.391620  1.613779

as I don't know beforehand which indexes will be missing. I wanted to update this to use loc, which didn't work. But it seems strange that you have to use ix for a case like this.

[immerrr]

IMO this should work with loc given the corresponding axis is monotonic.

[jorisvandenbossche]

@immerrr Well, that is indeed the 'logic' for ix: this slicing with non-included labels only works for monotic indices (and this was explicitly introduced, see c3708f2).
I don't really see a reason why this would be different for loc, and I am wondering if this was an oversight or on-purpose when .loc was introduced (and it could easily be an oversight, as this behaviour of ix is also not really documented, apart from the whatsnew of 0.7, and a single title (without further text) in the gotcha section: http://pandas.pydata.org/pandas-docs/stable/gotchas.html#non-monotonic-indexes-require-exact-matches)

[jreback]

I think this is a bug. .ix does much less validation, so prob just slipping thru (I also didn't touch it much), was trying to preserve its exact functionaility.

That said, we should document the missing values handling (for scalar/slice) a bit more

[jorisvandenbossche]

@jreback What do you mean with a 'bug'? That loc should also have the same behaviour as ix in this case? (so not 'intended' as you said in #5223)

[jreback]

I shouldn't say bug, rather unintended non-compliance with .ix behavior (that we didn't mean to change)

[shoyer]

In #8740, I noted that this is already inconsistent between float and int indexes (float indexes don't check bounds).

In addition to consistency considerations, there may also be an efficiency argument. For float and interval indexes, you can't check whether a number is within the index bounds without doing binary search. This essentially doubles the amount of work necessary for doing slice lookups.