Inconsistent behavior of partial string indexing vs straight indexing for timeseries data

[washougal]

I recently ran into an issue where pandas is not handling the "<=" logical operator when comparing dates in an interesting way.

import pandas as pd
rng = pd.date_range('1/1/2011', periods=72, freq='H')
df = pd.DataFrame(rng)
df.columns=['date']

crit = (df['date'] >= '2011-01-01') & (df['date'] <= '2011-01-02')
df.loc[crit, 'New_Col'] = 'First two days?'

Anything after 2011-01-02 00:00:00 is not included in the less than or equal to 2011-01-02. One would expect that less than or equal to mean that entire day. Is this by design, or is this a bug?

[jreback]

So the typical way to thing about how slices are interpreted is [49], where slices are partial string indexing, see here.

This basically expands a partial date to be inclusive of times for that date, e.g. '2011-01-02' is implicity '2011-01-02 11:59:59.999999999'

However for straight indexing, these values are simply converted as is, e.g. '2011-01-02' -> '2011-01-02 00:00:00.000000000'.

I think these should be consistent, so we'll call this a bug. Though this may be a bit tricky to fix, see core/ops._comp_method_SERIES. And prob should only use partial indexing for ge,gt,le,lt.

In [46]: df = pd.DataFrame({'date' : rng, 'value' : range(len(rng))})

In [47]: df2 = df.set_index('date')

In [48]: df2     
Out[48]: 
                     value
date                      
2011-01-01 00:00:00      0
2011-01-01 01:00:00      1
2011-01-01 02:00:00      2
2011-01-01 03:00:00      3
2011-01-01 04:00:00      4
...                    ...
2011-01-03 19:00:00     67
2011-01-03 20:00:00     68
2011-01-03 21:00:00     69
2011-01-03 22:00:00     70
2011-01-03 23:00:00     71

[72 rows x 1 columns]

In [49]: df2['2011-01-01':'2011-01-02']
Out[49]: 
                     value
date                      
2011-01-01 00:00:00      0
2011-01-01 01:00:00      1
2011-01-01 02:00:00      2
2011-01-01 03:00:00      3
2011-01-01 04:00:00      4
...                    ...
2011-01-02 19:00:00     43
2011-01-02 20:00:00     44
2011-01-02 21:00:00     45
2011-01-02 22:00:00     46
2011-01-02 23:00:00     47

[48 rows x 1 columns]

In [50]: df2[(df2.index >= '2011-01-01') & (df2.index <= '2011-01-02')]
Out[50]: 
                     value
date                      
2011-01-01 00:00:00      0
2011-01-01 01:00:00      1
2011-01-01 02:00:00      2
2011-01-01 03:00:00      3
2011-01-01 04:00:00      4
...                    ...
2011-01-01 20:00:00     20
2011-01-01 21:00:00     21
2011-01-01 22:00:00     22
2011-01-01 23:00:00     23
2011-01-02 00:00:00     24

[25 rows x 1 columns]

[shoyer]

I'm actually a little surprised this works -- you can't compare either Timestamp or np.datetime64 arrays to strings.

But, I agree with @jreback that this is a nice feature and it would be good to fix it.

[mroeschke]

Seems like there hasn't been much interest in this feature over the years so closing