Partial string matching for timestamps with multiindex

[thrasibule]

I'm trying to get a slice from a multiindex. I find the behavior pretty inconsistent. Here is a simple dataframe:

test = pd.DataFrame({'l': ['A', 'B', 'C', 'C', 'B', 'A'], 'm': [pd.Timestamp('2014-06-11 14:26:27'), pd.Timestamp('2014-06-11 15:26:27'),  pd.Timestamp('2014-06-11 16:26:27'), pd.Timestamp('2014-06-12 14:26:27'), pd.Timestamp('2014-06-12 15:26:27'),  pd.Timestamp('2014-06-12 16:26:27')]})

With a single index, I can select all the data for a given day as follows:

test_single_index = test.set_index(['m'])
#partial string indexing works
test_single_index.loc['2014-06-11']

But it doesn't work for a multiindex:

test_multi_index = test.set_index(['m', 'l'])
#would expect this to work
test_multi_index.loc['2014-06-11']
#or this
test_multi_index.loc[('2014-06-11', 'A'),:]

#what works
idx = pd.IndexSlice
test_multi_index.loc[idx['2014-06-11':'2014-06-11',:],:]
test_multi_index.loc[idx['2014-06-11','A'],:]

So I can make it work if I specify the slice explicitely, but it would be nice if the behavior for the 1D index carried over to Multiindices.

[jreback]

this is almost a dupe of #9732
This is simply not implemented (though not that difficult). See that issue for an easy work-around.

[markroth8]

Added some unit tests that should pass once this feature works. @jreback did a quick review pass on the tests.

@jreback, you said you'd have a look at the code path and give some recommendations on how to fix this issue.

[markroth8]

@jreback, per our discussion, I have an implementation that seems to fix this issue (in the private branch above), but it does not fail the test for df.loc[('2016-01-01', 'a'), :] which you thought it should fail. Before I make a formal pull request, can you have a look at the branch?

These tests all pass:
$ nosetests -A "not slow" tests/frame tests/series tests/indexes tests/test_indexing.py

[markroth8]

Additionally, df_swap.loc[idx[:, '2016-01-02':], :] fails with an error, but I believe this can be considered a separate bug, as it did so before my proposed fix.

The cause is that the code in multi.py is trying to call .stop on an int instead of first checking to see if stop is a slice:

if isinstance(start, slice) or isinstance(stop, slice):
                # we have a slice for start and/or stop
                # a partial date slicer on a DatetimeIndex generates a slice
                # note that the stop ALREADY includes the stopped point (if
                # it was a string sliced)
                return convert_indexer(start.start, stop.stop, step)

Please let me know how you'd like me to proceed with the pull request.

[jreback]

thanks @markroth8 I will be visting this next week.

[markroth8]

While trying to update the docs, I think I found an issue with the implementation. I'm not sure if it's new:

dft2 = DataFrame(randn(200000,1),columns=['A'],index=MultiIndex.from_product([dft.index, ['a', 'b']]))

In [32]: dft2.loc['2013-03']
Out[32]: 
                              A
2013-01-01 00:00:00 a  0.145858
2013-01-01 00:01:00 a  0.007413
2013-01-01 00:02:00 a  0.286948
2013-01-01 00:03:00 a -0.695290
2013-01-01 00:04:00 a -0.948675
2013-01-01 00:05:00 a -2.454434
2013-01-01 00:06:00 a  0.920254
2013-01-01 00:07:00 a -0.614170
2013-01-01 00:08:00 a  2.039961
2013-01-01 00:09:00 a -1.326936
2013-01-01 00:10:00 a  0.500509
2013-01-01 00:11:00 a -0.433509
2013-01-01 00:12:00 a  0.466115
2013-01-01 00:13:00 a  1.245106
2013-01-01 00:14:00 a  1.077567
2013-01-01 00:15:00 a  0.603533
2013-01-01 00:16:00 a -1.153203
2013-01-01 00:17:00 a -0.932540
2013-01-01 00:18:00 a -0.942356
2013-01-01 00:19:00 a  0.078370
2013-01-01 00:20:00 a -0.421003
2013-01-01 00:21:00 a -0.700034
2013-01-01 00:22:00 a  0.956662
2013-01-01 00:23:00 a -1.009236
2013-01-01 00:24:00 a -1.325032
2013-01-01 00:25:00 a -1.633084
2013-01-01 00:26:00 a -0.281456
2013-01-01 00:27:00 a  0.394617
2013-01-01 00:28:00 a -1.875757
2013-01-01 00:29:00 a  1.056043
...                         ...
2013-03-11 10:25:00 a  0.212931
                    b  0.419044
2013-03-11 10:26:00 a  1.571333
                    b  0.059788
2013-03-11 10:27:00 a -0.271262
                    b -0.167976
2013-03-11 10:28:00 a -1.111855
                    b -0.525583
2013-03-11 10:29:00 a -0.801815
                    b  0.557208
2013-03-11 10:30:00 a -0.004837
                    b -0.452653
2013-03-11 10:31:00 a  1.270449
                    b -0.775152
2013-03-11 10:32:00 a -0.773299
                    b  0.258476
2013-03-11 10:33:00 a  0.376850
                    b  1.430959
2013-03-11 10:34:00 a  0.566823
                    b -0.464938
2013-03-11 10:35:00 a  0.889925
                    b  1.714770
2013-03-11 10:36:00 a -1.593098
                    b -0.938460
2013-03-11 10:37:00 a  0.046490
                    b  0.543553
2013-03-11 10:38:00 a  0.527221
                    b  0.117193
2013-03-11 10:39:00 a -0.459528
                    b -1.791615

[115040 rows x 1 columns]

Note how rows with 'b' are only present for '2013-03' rows but 'a' appears for all rows.

[markroth8]

Confirmed the same behavior exists in master branch with:

dft2.loc[IndexSlice['2013-03':'2013-03',:],:]

Is this a bug we should file separately?

[markroth8]

@jreback, see my above note on dft2.loc[IndexSlice['2013-03':'2013-03',:],:]. Should I file a bug on this?

[jreback]

@markroth8 yes that is a separate bug. Pls open a new issue (you can use the ex that you put in the whatsnew for a repro).

[markroth8]

@jreback: I opened #12685

[ianzhy]

Additionally, df_swap.loc[idx[:, '2016-01-02':], :] fails with an error, but I believe this can be considered a separate bug, as it did so before my proposed fix.

The cause is that the code in multi.py is trying to call .stop on an int instead of first checking to see if stop is a slice:

if isinstance(start, slice) or isinstance(stop, slice):
                # we have a slice for start and/or stop
                # a partial date slicer on a DatetimeIndex generates a slice
                # note that the stop ALREADY includes the stopped point (if
                # it was a string sliced)
                return convert_indexer(start.start, stop.stop, step)

Please let me know how you'd like me to proceed with the pull request.

This bug still exists in version 0.24.2.
see this SO question

[TomAugspurger]

If you have a reproducible example I would recommend opening a new issue.

…

On Wed, Jun 19, 2019 at 4:53 AM Ian Zhang ***@***.***> wrote: Additionally, df_swap.loc[idx[:, '2016-01-02':], :] fails with an error, but I believe this can be considered a separate bug, as it did so before my proposed fix. The cause is that the code in multi.py is trying to call .stop on an int instead of first checking to see if stop is a slice: if isinstance(start, slice) or isinstance(stop, slice): # we have a slice for start and/or stop # a partial date slicer on a DatetimeIndex generates a slice # note that the stop ALREADY includes the stopped point (if # it was a string sliced) return convert_indexer(start.start, stop.stop, step) Please let me know how you'd like me to proceed with the pull request. This bug still exists in version 0.24.2. see this SO question <https://stackoverflow.com/questions/56616824/indexslice-on-a-datetime-multindex-not-working-but-doesnt-seem-different-from/56664818#56664818> — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#10331?email_source=notifications&email_token=AAKAOIQIEU2A5MYNOFQK4M3P3H6ZZA5CNFSM4BH7XRJKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYBK2AQ#issuecomment-503491842>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAKAOIUIKRUJTIGI7AQPFDLP3H6ZZANCNFSM4BH7XRJA> .

[soilstack]

If you have a reproducible example I would recommend opening a new issue.
…
On Wed, Jun 19, 2019 at 4:53 AM Ian Zhang @.***> wrote: Additionally, df_swap.loc[idx[:, '2016-01-02':], :] fails with an error, but I believe this can be considered a separate bug, as it did so before my proposed fix. The cause is that the code in multi.py is trying to call .stop on an int instead of first checking to see if stop is a slice: if isinstance(start, slice) or isinstance(stop, slice): # we have a slice for start and/or stop # a partial date slicer on a DatetimeIndex generates a slice # note that the stop ALREADY includes the stopped point (if # it was a string sliced) return convert_indexer(start.start, stop.stop, step) Please let me know how you'd like me to proceed with the pull request. This bug still exists in version 0.24.2. see this SO question https://stackoverflow.com/questions/56616824/indexslice-on-a-datetime-multindex-not-working-but-doesnt-seem-different-from/56664818#56664818 — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#10331?email_source=notifications&email_token=AAKAOIQIEU2A5MYNOFQK4M3P3H6ZZA5CNFSM4BH7XRJKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYBK2AQ#issuecomment-503491842>, or mute the thread https://github.com/notifications/unsubscribe-auth/AAKAOIUIKRUJTIGI7AQPFDLP3H6ZZANCNFSM4BH7XRJA .

I can demonstrate the issue occuring/not-occuring. It depends on whether the index is Dates only, or DateTimes.

dt_index = pd.date_range(start="1jan2019 00:15:33",periods=100,freq="h")  #this will fail
#dt_index = pd.date_range(start="1jan2019",periods=100,freq="d")    #this won't fail

idx = pd.IndexSlice

zf = pd.DataFrame(index=dt_index)
zf['foo']=10
zf['bar']="squat"
zf['zaa']=range(len(dt_index))
zf.index.name="date"
zf = zf.reset_index().set_index(["date", "bar", "zaa"])


zf.loc[idx['2019-1-1':'2019-1-3',"squat",:],:]     # This works ok
#zf.loc[idx['2019-1-1':,"squat",:],:]     # This fails  AttributeError: 'int' object has no attribute 'stop'
#zf.loc[idx['2019-1-1':None,'squat',:,:],:]           # AttributeError: 'int' object has no attribute 'stop'