Query in Multiindex for datetime-CodePudding

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.

CodePudding user response：

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]

CodePudding user response：

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