CODEWITHSUNDEEP
pythonpandasdataframe
Msquare
Msquare

Reputation: 835

Python index of a day-column greater than x in the dataframe of many days

I have a big dataframe of one column consisting data of many days. One a given day I want to find the datetime index of those values greater than x. Also, I want to know the integer index of the day. 

df =                           POA0
datetime                       
2019-07-05 07:53:53   81.605703
2019-07-05 08:33:57  120.913055
2019-07-05 09:13:25  290.052470
2019-07-06 09:53:29  515.109924
2019-07-06 10:33:27  644.393595
2019-07-06 11:13:28  745.543610
2019-07-06 11:53:25  831.441294
2019-07-07 12:33:23  889.082789
2019-07-07 13:53:23  906.169942
2019-07-07 14:33:25  874.149780
2019-07-08 15:13:25  809.388652
2019-07-08 15:53:22  439.487053
2019-07-08 16:33:28  246.558506
2019-07-08 17:13:49  193.730774
2019-07-08 17:53:57  145.934008

I want to find the index of those values greater than 700. My code is: 

day = '2019-07-06'
bool_idx = df['POA0'].loc[day]>700
time_idx = df[bool_idx].index

print(time_idx)
    [2019-07-05 07:53:53
    2019-07-05 08:33:57
    2019-07-05 09:13:25
    2019-07-06 09:53:29
    2019-07-06 10:33:27
    2019-07-06 11:13:28
    2019-07-06 11:53:25
    2019-07-07 12:33:23
    2019-07-07 13:53:23
    2019-07-07 14:33:25
    2019-07-08 15:13:25
    2019-07-08 15:53:22
    2019-07-08 16:33:28
    2019-07-08 17:13:49
    2019-07-08 17:53:57]

Above output is not what I wanted. Moreover, to get above output I wanted to use one-line code only but I used two lines. Is it possible to do it in one line? In between, I wanted to output only those indexes of the day as given below

print(time_idx)
    [2019-07-06 11:13:28
    2019-07-06 11:53:25]

Also, I want to know the integer index of above output as 

print(int_idx)
   [2,3]

Upvotes: 0

Views: 534

Answers (1)

Scott Boston
Scott Boston

Reputation: 153460

Try:

day = '2019-07-06'
limit = 700

df.loc[day].query('POA0 > @limit').index

Output:

DatetimeIndex(['2019-07-06 11:13:28', '2019-07-06 11:53:25'], dtype='datetime64[ns]', name='datetime', freq=None)

And for integer index:

day = '2019-07-06'
limit = 700

df.loc[day].index.get_indexer(df.loc[day].query('POA0 > @limit').index)

Output:

array([2, 3], dtype=int64)

Per Comment using Python 3.6+ use f-string:

day = '2019-07-06'
limit = 700
col_name = 'POA0'

df.loc[day].query(f'{col_name} > {limit}').index.tolist()

As a list?

day = '2019-07-06'
limit = 700
col_name = ['POA0']

df.loc[day].query(f'{col_name[0]} > {limit}').index.tolist()

Upvotes: 1

Related Questions