Possible bug when parsing from string to datetime when slicing

[tomchor]

From this question in SO

Please consider the following example

data = pd.DataFrame(np.random.random((72000,3)), columns=list('uvw'), 
           index=pd.date_range('2013-11-08 10:00:00', periods=72000, freq='50L'))
data.loc['2013-11-08 10:15:00.000':'2013-11-08 10:17:00.000']

This outputs

                                u         v         w
2013-11-08 10:15:00.000  0.569030  0.850393  0.600106
2013-11-08 10:15:00.050  0.679713  0.933720  0.041018
2013-11-08 10:15:00.100  0.503491  0.142397  0.841705
2013-11-08 10:15:00.150  0.171248  0.545567  0.247094
2013-11-08 10:15:00.200  0.149745  0.149588  0.935516
2013-11-08 10:15:00.250  0.039780  0.097837  0.087254
...                           ...       ...       ...
2013-11-08 10:17:00.700  0.001165  0.020971  0.197322
2013-11-08 10:17:00.750  0.003923  0.722930  0.312988
2013-11-08 10:17:00.800  0.941241  0.600529  0.479640
2013-11-08 10:17:00.850  0.272536  0.738084  0.486551
2013-11-08 10:17:00.900  0.060388  0.606207  0.359640
2013-11-08 10:17:00.950  0.464268  0.965543  0.699740

[2420 rows x 3 columns]

This is weird for me because I expected the last row to be 2013-11-08 10:17:00.000, since that's the end-point I defined. Indeed when I define the endpoint as datetime(2013,11,8,10,17,0,0), which should be identical, it works as I would expect:

In [13]: data.loc['2013-11-08 10:15:00.000':datetime(2013,11,8,10,17,0,0)]
Out[13]: 
                                u         v         w
2013-11-08 10:15:00.000  0.569030  0.850393  0.600106
2013-11-08 10:15:00.050  0.679713  0.933720  0.041018
2013-11-08 10:15:00.100  0.503491  0.142397  0.841705
2013-11-08 10:15:00.150  0.171248  0.545567  0.247094
2013-11-08 10:15:00.200  0.149745  0.149588  0.935516
2013-11-08 10:15:00.250  0.039780  0.097837  0.087254
...                           ...       ...       ...
2013-11-08 10:16:59.750  0.652168  0.606795  0.901583
2013-11-08 10:16:59.800  0.868184  0.249873  0.517637
2013-11-08 10:16:59.850  0.917543  0.303403  0.980257
2013-11-08 10:16:59.900  0.118191  0.032437  0.580734
2013-11-08 10:16:59.950  0.093644  0.017865  0.080326
2013-11-08 10:17:00.000  0.770234  0.310025  0.065127

[2401 rows x 3 columns]

I'm submitting this as a suggestion from an SO user because this seems like a bug.

To complete:

INSTALLED VERSIONS
------------------
commit: None
python: 2.7.12.final.0
python-bits: 64
OS: Linux
OS-release: 4.4.0-21-generic
machine: x86_64
processor: x86_64
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8

pandas: 0.17.1
nose: 1.3.7
pip: 8.1.1
setuptools: 20.7.0
Cython: None
numpy: 1.11.0
scipy: 0.17.0
statsmodels: None
IPython: 2.4.1
sphinx: 1.4.5
patsy: None
dateutil: 2.4.2
pytz: 2014.10
blosc: None
bottleneck: None
tables: None
numexpr: 2.6.0
matplotlib: 1.5.1
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: 0.999
httplib2: 0.9.1
apiclient: None
sqlalchemy: None
pymysql: None
psycopg2: None
Jinja2: None

[jreback]

this is because we rely on dateutil for some of the resolution checking e.g.

So these are equal but of a different intended resolution

In [5]: pd.Timestamp('20160101 10:17:00').value
Out[5]: 1451643420000000000

In [6]: pd.Timestamp('20160101 10:17:00.000').value
Out[6]: 1451643420000000000

e.g. [3] should be 'millisecond'

In [1]: from pandas.tseries.tools import parse_time_string

In [2]: parse_time_string('20160101 10:17:00')
Out[2]: 
(datetime.datetime(2016, 1, 1, 10, 17),
 datetime.datetime(2016, 1, 1, 10, 17),
 'second')

In [3]: parse_time_string('20160101 10:17:00.000')
Out[3]: 
(datetime.datetime(2016, 1, 1, 10, 17),
 datetime.datetime(2016, 1, 1, 10, 17),
 'second')

In [4]: parse_time_string('20160101 10:17:00.001')
Out[4]: 
(datetime.datetime(2016, 1, 1, 10, 17, 0, 1000),
 datetime.datetime(2016, 1, 1, 10, 17, 0, 1000),
 'millisecond')

The reason this matters is on the rhs of the slice bound we take all values <= reso (IOW if its second, then we get everything thru 1 second, while if its millisecond, then its thru 1 millisecond).

So I think we could simply fix the resolution parsing.

[cpaulik]

Is this the same problem I'm seeing here?

import pandas as pd
p
In [2]: pd.__version__
Out[2]: u'0.19.2'

In [3]: df = pd.DataFrame(columns=['1'], index=pd.date_range('2016-10-01T00:00:00', '2016-10-02T00:00:00'))

In [4]: df
Out[4]: 
              1
2016-10-01  NaN
2016-10-02  NaN

In [5]: df.loc['2016-10-01T00:00:00':]
Out[5]: 
              1
2016-10-01  NaN
2016-10-02  NaN

In [6]: df = pd.DataFrame(columns=['1'], index=pd.date_range('2016-10-01T00:00:00', '2016-10-01T23:59:59'))

In [7]: df
Out[7]: 
              1
2016-10-01  NaN

In [8]: df.loc['2016-10-01T00:00:00':]
Out[8]: 
Empty DataFrame
Columns: [1]
Index: []

In [9]: df.loc[datetime(2016,10,1):]
Out[9]: 
              1
2016-10-01  NaN

[jreback]

@cpaulik looks similiar. These are both fencepost issues and could be addressed by a similar fix. pull-requests welcome if you can dig in.

[cpaulik]

After looking into it it seems that those are slightly different problems. I'll open an issue and a PR for my problem.

For this problem:

Dateutil returns the same object (with microsecond set to 0) regardless of the input string resolution.
So the code at https://github.com/pandas-dev/pandas/blob/master/pandas/_libs/tslib.pyx#L2135-L2137 sets the resolution to seconds.

We could use the freq information if it is not None to actually enforce the rule given in the comments here

Is there an easy way to convert a freq into a timedelta?

[Sup3rGeo]

I guess this is the same problem I am having, with this very simple example:

>>> import pandas
>>> values = [1,2,3,4,5,6,7,8]
>>> index = [1, 1.5, 2, 2.5, 3, 3.5, 4, 4.5]
>>> serie = pandas.Series(values, index=pandas.to_timedelta(index, unit='s'))

>>> # WORKS JUST FINE
>>> sliced = serie[pandas.Timedelta('1s'):pandas.Timedelta('2s')]
>>> print(sliced)
00:00:01           1
00:00:01.500000    2
00:00:02           3
dtype: int64
>>> assert sliced.index[-1] == pandas.Timedelta('2s')

>>> # NOT GOOD
>>> sliced2 = serie['1s':'2s']
>>> print(sliced2)
00:00:01           1
00:00:01.500000    2
00:00:02           3
00:00:02.500000    4
dtype: int64
>>> assert sliced2.index[-1] == pandas.Timedelta('2s')
Traceback (most recent call last):
  File "C:\Python36\lib\site-packages\IPython\core\interactiveshell.py", line 2910, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-11-0ade2aa6bd8d>", line 1, in <module>
    assert sliced2.index[-1] == pandas.Timedelta('2s')
AssertionError

[Sup3rGeo]

The reason this matters is on the rhs of the slice bound we take all values <= reso (IOW if its second, then we get everything thru 1 second, while if its millisecond, then its thru 1 millisecond).

@jreback would you mind explaining why is it the case for the rhs of the slice bound? In my example above it makes the slicing return more than what we expect.