Inconsistent behavior of DatetimeIndex Partial String Indexing on Series and DataFrames

[ischurov]

This bugreport is related to this SO question and the discussion there.

Summary

I believe that current DatetimeIndex Partial String Indexing behavior is either inconsistent or underdocumented as the result depends nontrivially on whether we are working with Series or DataFrame and whether DateTimeIndex is periodic or not.

Series vs. DataFrame

series = pd.Series([1, 2, 3], pd.DatetimeIndex(['2016-12-07 09:00:00',
                                                '2016-12-08 09:00:00',
                                                '2016-12-09 09:00:00']))
print(type(series["2016-12-07 09:00:00"]))
# <class 'numpy.int64'>

df = pd.DataFrame(series)
df["2016-12-07 09:00:00"]

KeyError: '2016-12-07 09:00:00'

Here we see that the behaviour depends on what we are indexing: Series returns scalar while DataFrame raises an exception. This exception is consistent with the documentation notice:

Warning The following selection will raise a KeyError; otherwise this selection methodology would be inconsistent with other selection methods in pandas (as this is not a slice, nor does it resolve to one)

Why we do not get the same exception for Series object?

Periodic vs. Non-periodic

series = pd.Series([1, 2, 3], pd.DatetimeIndex(['2016-12-07 09:00:00',
                                                '2016-12-08 09:00:00',
                                                '2016-12-09 09:00:01']))
# now it is not periodic due to 1 second in the last timestamp

print(type(series["2016-12-07 09:00:00"]))
# <class 'pandas.core.series.Series'>

In contrast with the previous example, we get an instance of Series here, so the same timestamp is considered as a slice, not index. Why it depends in such a way on periodicity of the index?

df = pd.DataFrame(series)
print(type(df["2016-12-07 09:00:00"]))
# <class 'pandas.core.frame.DataFrame'>

No exceptions here, in contrast with periodic case.

Is it intended behavior? If yes, I believe that this should be clearly documented and rationale provided.

Output of pd.show_versions()

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

pandas: 0.19.0+157.g2466ecb
nose: 1.3.7
pip: 9.0.1
setuptools: 27.2.0
Cython: 0.25.1
numpy: 1.11.2
scipy: None
statsmodels: None
xarray: None
IPython: 5.1.0
sphinx: None
patsy: None
dateutil: 2.6.0
pytz: 2016.10
blosc: None
bottleneck: None
tables: None
numexpr: None
matplotlib: None
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: None
httplib2: None
apiclient: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: None
boto: None
pandas_datareader: None

[jorisvandenbossche]

The first "series vs dataframe" issue is as expected / follows from the second issue. __getitem__/[] indexing does not work exactly the same for series vs dataframe (http://pandas.pydata.org/pandas-docs/stable/indexing.html#basics). When provided a single key, Series will get a single value, while dataframe will try to get a column. And there is no column named "2016-12-07 09:00:00", hence the KeyError.
So this behaviour follows from the fact that in your first example, "2016-12-07 09:00:00" is not interpreted as a slice, but as a single key. Given that, the behaviour is as expected.

But, you are correct there might be an inconsistency in determining whether the string is a single key or a slice between the regular and irregular datetimeindex. The problem is that it is very difficult for pandas to guess/determine this when the index has no frequency.

[ischurov]

For the first part, I completely agree. I forgot that [] for dataframe select columns if argument is not a slice (shame on me), this explains the behavior nicely. (And now I understand the rationale behind the Warning in the docs I quoted.)

For the second part, is it true that for irregular indexes any string is considered to be slice, and for regular ones only those strings that provide date-time specification with precision less then frequency is considered to be slice?

Why it is not possible to consider string a key (not a slice) if it is casted to date-time that is exactly the same as one of keys in the index for irregular indexes?

[jorisvandenbossche]

And now I understand the rationale behind the Warning in the docs I quoted.

If you have ideas to rephrase this to make it clearer, very welcome!

Why it is not possible to consider string a key (not a slice) if it is casted to date-time that is exactly the same as one of keys in the index for irregular indexes?

I am not exactly sure how it is implemented in the code, but imagine the following case: you have a timeseries with index ["2016-01-01 00:00", "2016-01-01 12:00", "2016-01-01 23:00", "2016-01-02 05:00", "2016-01-02 18:00"] (some irregular hours over two days).
If you would index this with the key "2016-01-01", this can be interpreted as "give me all the data of the 1st of January", so in this case: a slice (which is what pandas does). But if you would also want to check if the provided key exactly matches with one of the labels of the index, pandas parses this key -> pd.Timestamp("2016-01-01"), which gives you "2016-01-01 00:00:00" (because pandas does not have different resolution in its datetime data type) and this would match with one of the elements (so: not a slice). So which one of the two would the user want?
(to put it in other words, pandas cannot make a distinction between s["2016-01-01"] (logically a slice) and s["2016-01-01 00:00"] (logically a single element))

[ischurov]

Yes, I'm going to improve the docs according to our discussion after I understand all the details.

Your explanation sounds reasonable, but I cannot get why all these arguments do not apply to the case of regular index? In fact, pandas can detect the resolution of a string-represented timestamp, this is done in function pandas.tseries.tools.parse_time_string() and it allows pandas to distinct s["2016-01-01"] and s["2016-01-01 00:00"] in case s is indexed with regular index:

series = pd.Series([1, 2, 3], pd.DatetimeIndex(['2016-12-07 00:00:00',
                                                '2016-12-07 01:00:00',
                                                '2016-12-07 02:00:00']))
print(series["2016-12-07 00:00:00"])
# 1
print(series["2016-12-07 00:00"])
# 1
print(series["2016-12-07"])
# 2016-12-07 00:00:00    1
# 2016-12-07 01:00:00    2
# 2016-12-07 02:00:00    3
# dtype: int64

[ischurov]

Finally, it seems that I got it. The code I'm interested in is the following:

    def _partial_date_slice(self, reso, parsed, use_lhs=True, use_rhs=True):
        is_monotonic = self.is_monotonic
        if ((reso in ['day', 'hour', 'minute'] and
             not (self._resolution < Resolution.get_reso(reso) or
                  not is_monotonic)) or
            (reso == 'second' and
             not (self._resolution <= Resolution.RESO_SEC or
                  not is_monotonic))):
            # These resolution/monotonicity validations came from GH3931,
            # GH3452 and GH2369.
            raise KeyError

raising KeyError here means that the timestamp cannot be coerced to a slice. The condition basically says that if the resolution of the timestamp (that we remember from the string) is less precise than the resolution of the index, it is slice, otherwise it is not. That sounds very reasonable.

I'm not sure yet, why second resolution uses different condition (non-strict inequality instead of strict one), but basically I understand what's going on here.

I'll try to improve the docs soon and prepare PR that will refer to this issue.

[ischurov]

Btw, could anybody tell, why second resolution is treated in such a different way here? I tried to figure it out myself (looking at PRs mentioned near the code), but didn't succeed.

[ischurov]

I finally discovered that this was PR #3931

@jreback, could you please comment on this? Why do we introduce the inconsistence like this:

series = pd.Series([1, 2, 3, 4], pd.DatetimeIndex(['2016-12-06 23:59:00',
                                                   '2016-12-07 01:00:00',
                                                   '2016-12-07 01:01:00',
                                                   '2016-12-07 01:02:01']))

print(type(series["2016-12-07 01:01:00"]))
# <class 'pandas.core.series.Series'>

series = pd.Series([1, 2, 3, 4], pd.DatetimeIndex(['2016-12-07',
                                                   '2016-12-08',
                                                   '2016-12-09',
                                                   '2016-12-10']))
print(type(series["2016-12-07"]))

# <class 'numpy.int64'>

Why second frequency is treated in different way?

[jorisvandenbossche]

@ischurov Thanks for digging in! So indeed, it had in the end nothing to do with the irregular/regular index (only the resolution of the index is different due to the irregularity, and this impact how the slice is determined), but the different treatment of second resolution or higher resolutions.

I am not sure why this was added differently as the other resolution, and this seems rather inconsistent to me.

By the way, apart from clarification in the docs, some comprehensive tests looping over some combinations of different resolutions is also welcome

[jreback]

if you look at the PR, DataFrames need to have a slice here (and not a single indexer). So I think this could fix the inconsistent case you enumerate above (e.g. seconds is an exact match in which case you raise KeyError; this may be counter intuitive in how to do it, but it goes to another path that converts to a timestamp and just looks for an exact match, if its there it returns the value, otherwise raises).

So obviously this is not tested on series.

But I think you'd have to introduce some logic to actually return a slice when selecting for a DataFrame, which in this case IS a slice.

The other resolutions, day, hour, minute, cannot by definition ever have an exact match directly (because they always have a seconds component attached which you don't know). However seconds is special in that it could fully represented and actually be an exact match.