multi index(time series) slicing error in pandas

Question

i have below dataframe. date/time is multi-indexed indexes. when i doing this code,

<code>
idx = pd.IndexSlice 
print(df_per_wday_temp.loc[idx[:,datetime.time(4, 0, 0): datetime.time(7, 0, 0)]])"

but i got error 'MultiIndex Slicing requires the index to be fully lexsorted tuple len (2), lexsort depth (1)'. this may be error in index slicing but i don't know why this happened. anybody can solve it ?

                      a          b
date       time                     
2018-01-26 19:00:00  25.08     -7.85
           19:15:00  24.86     -7.81
           19:30:00  24.67     -8.24
           19:45:00    NaN     -9.32
           20:00:00    NaN     -8.29
           20:15:00    NaN     -8.58
           20:30:00    NaN     -9.48
           20:45:00    NaN     -8.73
           21:00:00    NaN     -8.60
           21:15:00    NaN     -8.70
           21:30:00    NaN     -8.53
           21:45:00    NaN     -8.90
           22:00:00    NaN     -8.55
           22:15:00    NaN     -8.48
           22:30:00    NaN     -9.90
           22:45:00    NaN     -9.70
           23:00:00    NaN     -8.98
           23:15:00    NaN     -9.17
           23:30:00    NaN     -9.07
           23:45:00    NaN     -9.45
           00:00:00    NaN     -9.64
           00:15:00    NaN    -10.08
           00:30:00    NaN     -8.87
           00:45:00    NaN     -9.91
           01:00:00    NaN     -9.91
           01:15:00    NaN     -9.93
           01:30:00    NaN     -9.55
           01:45:00    NaN     -9.51
           02:00:00    NaN     -9.75
           02:15:00    NaN     -9.44
...                    ...       ...
           03:45:00    NaN     -9.28
           04:00:00    NaN     -9.96
           04:15:00    NaN    -10.19
           04:30:00    NaN    -10.20
           04:45:00    NaN     -9.85
           05:00:00    NaN    -10.33
           05:15:00    NaN    -10.18
           05:30:00    NaN    -10.81
           05:45:00    NaN    -10.51
           06:00:00    NaN    -10.41
           06:15:00    NaN    -10.49
           06:30:00    NaN    -10.13
           06:45:00    NaN    -10.36
           07:00:00    NaN    -10.71
           07:15:00    NaN    -12.11
           07:30:00    NaN    -10.76
           07:45:00    NaN    -10.76
           08:00:00    NaN    -11.63
           08:15:00    NaN    -11.18
           08:30:00    NaN    -10.49
           08:45:00    NaN    -11.18
           09:00:00    NaN    -10.67
           09:15:00    NaN    -10.60
           09:30:00    NaN    -10.36
           09:45:00    NaN     -9.39
           10:00:00    NaN     -9.77
           10:15:00    NaN     -9.54
           10:30:00    NaN     -8.99
           10:45:00    NaN     -9.01
           11:00:00    NaN    -10.01

thanks in advance

You need df = df.sort_index(), for correct working need always sorting MultiIndex. Check this — jezrael
– jezrael, Commented Mar 2, 2018 at 8:23
oh.. but i wanna keep time index order.. i have used "df = df.sort_index()" but time order that i arranged is changed.. don't have other way for keeping time index order & index slicing? — jerry han
– jerry han, Commented Mar 2, 2018 at 9:01
Unfortunately not with indexing. For MultiIndex need sorted levels, I think because performace. — jezrael
– jezrael, Commented Mar 2, 2018 at 9:05

jezrael · Accepted Answer · 2018-03-02 09:11:17Z

1

If is not possible sorting index, is necessary create boolean mask and filter by boolean indexing:

from datetime import time
mask = df1.index.get_level_values(1).to_series().between(time(4, 0, 0), time(7, 0, 0)).values
df = df1[mask]
print (df)
                      a      b
date       time               
2018-01-26 04:00:00 NaN  -9.96
           04:15:00 NaN -10.19
           04:30:00 NaN -10.20
           04:45:00 NaN  -9.85
           05:00:00 NaN -10.33
           05:15:00 NaN -10.18
           05:30:00 NaN -10.81

answered Mar 2, 2018 at 9:11

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

1 Comment

jezrael Over a year ago

@jerryhan - Glad can help!

Collectives™ on Stack Overflow

multi index(time series) slicing error in pandas

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related