CODEWITHSUNDEEP
pythonpandasdataframedatetimeindexing
Mahome
Mahome

Reputation: 132

Pandas DataFrame Time index using .loc function error

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?

Upvotes: 1

Views: 1462

Answers (3)

SeaBean
SeaBean

Reputation: 23227

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.

Upvotes: 1

Corralien
Corralien

Reputation: 120519

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')]

Upvotes: 0

BENY
BENY

Reputation: 323366

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

Upvotes: 1

Related Questions