Pandas Dataframe datetime slicing with Index vs MultiIndex

Question

With single indexed dataframe I can do the following:

df2 = DataFrame(data={'data': [1,2,3]}, 
                index=Index([dt(2016,1,1),
                      dt(2016,1,2),
                      dt(2016,2,1)]))

>>> df2['2016-01 : '2016-01']
                data
    2016-01-01     1
    2016-01-02     2

>>> df2['2016-01-01' : '2016-01-01']
                data
    2016-01-01     1

Date time slicing works when you give it a complete day (i.e. 2016-01-01), and it also works when you give it a partial date, like just the year and month (2016-01). All this works great, but when you introduce a multiindex, it only works for complete dates. The partial date slicing doesn't seem to work anymore

df = DataFrame(data={'data': [1, 2, 3]},
               index=MultiIndex.from_tuples([(dt(2016, 1, 1), 2),
                                             (dt(2016, 1, 1), 3),
                                             (dt(2016, 1, 2), 2)],
                                             names=['date', 'val']))


 >>> df['2016-01-01 : '2016-01-02']
                            data
     date       val     
     2016-01-01 2           1
                3           2
     2016-01-02 2           3

ok, thats fine, but the partial date:

>>> df['2016-01' : '2016-01']
 File "pandas/index.pyx", line 134, in pandas.index.IndexEngine.get_loc      (pandas/index.c:3824)
 File "pandas/index.pyx", line 154, in pandas.index.IndexEngine.get_loc (pandas/index.c:3704)
 File "pandas/hashtable.pyx", line 686, in pandas.hashtable.PyObjectHashTable.get_item (pandas/hashtable.c:12280)
 File "pandas/hashtable.pyx", line 694, in pandas.hashtable.PyObjectHashTable.get_item (pandas/hashtable.c:12231)
  KeyError: '2016-01'

(I shortened the traceback).

Any idea if this is possible? Is this a bug? Is there any way to do what I want to do without having to resort to something like:

df.loc[(df.index.get_level_values('date') >= start_date) &
       (df.index.get_level_values('date') <= end_date)]

Any tips, comments, suggestions, etc are MOST appreciated! I've tried a lot of other things to no avail!

I think in version 0.18.0 it does not work, but in version 0.18.1 this can be implemented - see — jezrael
– jezrael, Commented Apr 14, 2016 at 11:42

IanS · Accepted Answer · 2016-04-14 11:57:56Z

15

Cross-section should work:

df.xs(slice('2016-01-01', '2016-01-01'), level='date')

Documentation: http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.xs.html

answered Apr 14, 2016 at 11:57

IanS

16.3k9 gold badges64 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bryant Over a year ago

that does seem to work, but it drops the multiindex. Any way to preserve the index?

safetyduck Over a year ago

This drops the index.

IanS Over a year ago

There is a drop_level argument (set to True by default).

hyperneutrino · Accepted Answer · 2021-07-07 20:43:02Z

13

Use the pandas IndexSlice for a more pandtastic syntax.

idx = pd.IndexSlice
df.loc[idx['2016-01-01':'2016-01-01', :], :]

Remember pandas slices are left and right inclusive.

edited Jul 7, 2021 at 20:43

hyperneutrino

5,4362 gold badges32 silver badges52 bronze badges

answered Jul 23, 2017 at 22:14

Little Bobby Tables

4,8146 gold badges34 silver badges51 bronze badges

Collectives™ on Stack Overflow

Pandas Dataframe datetime slicing with Index vs MultiIndex

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related