Query in Multiindex for datetime

Question

I have this dataframe (relevant here is the Multiindex):

                           VAL  
FINT                ID                                     
2021-07-01 00:00:00 C7    32.0
                    C6    38.0
                    C5    94.0
2021-07-01 12:00:00 C7    28.0
                    C6    34.0
                    C5    95.0
2021-07-02 00:00:00 C7    29.0 
                    C6    33.0
                    C5    22.1
2021-07-02 12:00:00 C7    29.0 
                    C6    33.0
                    C5    62.2 
...

I want to create a new dataframe selecting data for hour at 12. I wanted to use query for this, but I don't know how to select the hour inside the query selection. I mean, I am searching something like

df.query('FINT.hour == 12')

which it doesn't work (while something like df.query('ID=="C7"') works well). I am using pandas 1.2.4. Thx.

jezrael · Accepted Answer · 2022-07-04 11:52:21Z

1

For me working select level in MultiIndex like Series with .dt:

df = df.query('FINT.dt.hour == 12')
print (df)
                         VAL
FINT                ID      
2021-07-01 12:00:00 C7  28.0
                    C6  34.0
                    C5  95.0
2021-07-02 12:00:00 C7  29.0
                    C6  33.0
                    C5  62.2

With boolean indexing solution is:

df[df.index.get_level_values('FINT').hour == 12]

edited Jul 4, 2022 at 11:52

answered Jul 4, 2022 at 11:36

jezrael

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

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

Comments

Ynjxsjmh · Accepted Answer · 2022-07-04 11:50:55Z

1

You can also try getting datetime values from first level and use DatetimeIndex.indexer_at_time to get index locations of values at particular time of day.

out = df.iloc[df.index.get_level_values(0).indexer_at_time('12:00')]

print(out)

                         VAL
FINT                ID
2021-07-01 12:00:00 C7  28.0
                    C6  34.0
                    C5  95.0
2021-07-02 12:00:00 C7  29.0
                    C6  33.0
                    C5  62.2

answered Jul 4, 2022 at 11:50

Ynjxsjmh

30.3k7 gold badges43 silver badges64 bronze badges

Collectives™ on Stack Overflow

Query in Multiindex for datetime

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related