Pandas DataFrame Time index using .loc function error

Question

I have created DataFrame with DateTime index, then I split the index into the Date index column and Time index column. Now, when I call for a row of a specific time by using pd.loc(), the system shows an error.

Here're an example of steps of how I made the DataFrame from beginning till reaching my consideration.

import pandas as pd
import numpy as np

df= pd.DataFrame({'A':[1, 2, 3, 4], 'B':[5, 6, 7, 8], 'C':[9, 10, 11, 12],
    'DateTime':pd.to_datetime(['2021-09-01 10:00:00', '2021-09-01 11:00:00', '2021-09-01 12:00:00', '2021-09-01 13:00:00'])})

df=df.set_index(df['DateTime'])
df.drop('DateTime', axis=1, inplace=True)
df

OUT >>

                    A   B   C
DateTime            
2021-09-01 10:00:00 1   5   9
2021-09-01 11:00:00 2   6   10
2021-09-01 12:00:00 3   7   11
2021-09-01 13:00:00 4   8   12

In this step, I'm gonna splitting DateTime index into multi-index Date & Time

df.index = pd.MultiIndex.from_arrays([df.index.date, df.index.time], names=['Date','Time'])
df

OUT >>

                        A   B   C
     Date   Time            
2021-09-01  10:00:00    1   5   9
            11:00:00    2   6   10
            12:00:00    3   7   11
            13:00:00    4   8   12

##Here is the issue##

when I call this statement, The system shows an error

df.loc["11:00:00"]

How to fix that?

So do you want to select every 11:00:00 from every Date?

Andrej Kesely
– Andrej Kesely

2021-08-31 20:42:43 +00:00
Commented Aug 31, 2021 at 20:42 — Andrej Kesely
– Andrej Kesely, Commented Aug 31, 2021 at 20:42

SeaBean · Accepted Answer · 2021-08-31 22:37:35Z

1

1. If you want to use `.loc`, you can just specify the time by:

import datetime

df.loc[(slice(None), datetime.time(11, 0)), :]

or use pd.IndexSlice similar to the solution by BENY, as follows:

import datetime

idx = pd.IndexSlice
df.loc[idx[:,datetime.time(11, 0)], :]

(defining a variable idx to use pd.IndexSlice gives us cleaner code and less typing if you are going to use pd.IndexSlice multiple times).

Result:

                     A  B   C
Date       Time              
2021-09-01 11:00:00  2  6  10

2. If you want to select just for one day, you can use:

import datetime

df.loc[(datetime.date(2021, 9, 1), datetime.time(11, 0))]

Result:

A     2
B     6
C    10
Name: (2021-09-01, 11:00:00), dtype: int64

3. You can also use `.xs` to access the MultiIndex row index, as follows:

import datetime

df.xs(datetime.time(11,0), axis=0, level='Time')

Result:

            A  B   C
Date                
2021-09-01  2  6  10

4. Alterative way if you haven't split DateTime index into multi-index Date & Time

Actually, if you haven't split the DatetimeIndex into separate date and time index, you can also use the .between_time() function to filter the time, as follows:

df.between_time("11:00:00", "11:00:00")

You can specify a range of time to filter, instead of just a point of time, if you specify different values for the start_time and end_time.

Result:

                     A  B   C
DateTime                     
2021-09-01 11:00:00  2  6  10

As you can see, .between_time() allows you to enter the time in simple string to filter, instead of requiring the use of datetime objects. This should be nearest to your tried ideal (but invalid) syntax of using df.loc["11:00:00"] to filter.

As a suggestion, if you split the DatetimeIndex into separate date and time index simply for the sake of filtering by time, you can consider using the .between_time() function instead.

edited Aug 31, 2021 at 22:37

answered Aug 31, 2021 at 20:45

SeaBean

23.4k3 gold badges16 silver badges28 bronze badges

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

2 Comments

SeaBean Over a year ago

@Mahome Welcome! As a suggestion, if you split the DatetimeIndex into separate date and time index simply for the sake of filtering by time, you can also consider using the .between_time() function I mentioned above.

Mahome Over a year ago

it's kind of you that you have covered multiple ways. I really appreciate all your help.

BENY · Accepted Answer · 2021-08-31 20:55:01Z

1

We can just do the correct value slice with IndexSlice

import datetime
out = df.loc[pd.IndexSlice[:,datetime.time(11, 0)],:]
Out[76]: 
                     A  B   C            DateTime
Date       Time                                  
2021-09-01 11:00:00  2  6  10 2021-09-01 11:00:00

answered Aug 31, 2021 at 20:55

BENY

324k22 gold badges176 silver badges250 bronze badges

Comments

Corralien · Accepted Answer · 2021-08-31 21:07:56Z

0

Why do you need to split your datetime into two parts?

You can use indexer_at_time

>>> df
                     A  B   C
DateTime
2021-09-01 10:00:00  1  5   9
2021-09-01 11:00:00  2  6  10
2021-09-01 12:00:00  3  7  11
2021-09-01 13:00:00  4  8  12

# Extract 11:00:00 from any day
>>> df.iloc[df.index.indexer_at_time('11:00:00')]
                     A  B   C
DateTime
2021-09-01 11:00:00  2  6  10

You can also create a proxy to save time typing:

T = df.index.indexer_at_time
df.iloc[T('11:00:00')]

edited Aug 31, 2021 at 21:07

answered Aug 31, 2021 at 21:02

Corralien

121k8 gold badges44 silver badges69 bronze badges

Collectives™ on Stack Overflow

Pandas DataFrame Time index using .loc function error

3 Answers 3

1. If you want to use `.loc`, you can just specify the time by:

2. If you want to select just for one day, you can use:

3. You can also use `.xs` to access the MultiIndex row index, as follows:

4. Alterative way if you haven't split DateTime index into multi-index Date & Time

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1. If you want to use .loc, you can just specify the time by:

2. If you want to select just for one day, you can use:

3. You can also use .xs to access the MultiIndex row index, as follows:

4. Alterative way if you haven't split DateTime index into multi-index Date & Time

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related

1. If you want to use `.loc`, you can just specify the time by:

3. You can also use `.xs` to access the MultiIndex row index, as follows: