New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Partial string matching for timestamps with multiindex #10331

Closed
thrasibule opened this Issue Jun 11, 2015 · 10 comments

Comments

Projects
None yet
3 participants
@thrasibule
Contributor

thrasibule commented Jun 11, 2015

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 comment has been minimized.

Show comment
Hide comment
@jreback

jreback Jun 11, 2015

Contributor

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

Contributor

jreback commented Jun 11, 2015

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

This comment has been minimized.

Show comment
Hide comment
@markroth8

markroth8 Feb 27, 2016

Contributor

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.

Contributor

markroth8 commented Feb 27, 2016

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

This comment has been minimized.

Show comment
Hide comment
@markroth8

markroth8 Feb 27, 2016

Contributor

@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

Contributor

markroth8 commented Feb 27, 2016

@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

This comment has been minimized.

Show comment
Hide comment
@markroth8

markroth8 Feb 27, 2016

Contributor

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.

Contributor

markroth8 commented Feb 27, 2016

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 jreback modified the milestones: 0.18.1, Next Major Release Feb 29, 2016

@jreback

This comment has been minimized.

Show comment
Hide comment
@jreback

jreback Feb 29, 2016

Contributor

thanks @markroth8 I will be visting this next week.

Contributor

jreback commented Feb 29, 2016

thanks @markroth8 I will be visting this next week.

@markroth8

This comment has been minimized.

Show comment
Hide comment
@markroth8

markroth8 Feb 29, 2016

Contributor

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.

Contributor

markroth8 commented Feb 29, 2016

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

This comment has been minimized.

Show comment
Hide comment
@markroth8

markroth8 Feb 29, 2016

Contributor

Confirmed the same behavior exists in master branch with:

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

Is this a bug we should file separately?

Contributor

markroth8 commented Feb 29, 2016

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

This comment has been minimized.

Show comment
Hide comment
@markroth8

markroth8 Mar 21, 2016

Contributor

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

Contributor

markroth8 commented Mar 21, 2016

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

@jreback

This comment has been minimized.

Show comment
Hide comment
@jreback

jreback Mar 21, 2016

Contributor

@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).

Contributor

jreback commented Mar 21, 2016

@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

This comment has been minimized.

Show comment
Hide comment
@markroth8

markroth8 Mar 22, 2016

Contributor

@jreback: I opened #12685

Contributor

markroth8 commented Mar 22, 2016

@jreback: I opened #12685

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment