Python find particular index of a row among many satisfying rows in a dataframe

Question

I have big dataframe with datetime as index:

df = 
                           vol     element
2019-10-20 14:24:22       99499     8157_S1
2019-10-20 15:04:23       99500     8157_S2
2019-10-20 15:47:04       99501     8157_S1
2019-10-20 16:27:20       99502     8157_S2
2019-10-21 07:44:59       99503     8157_S1
2019-10-21 08:24:49       99504     8157_S2
2019-10-21 09:04:58       99505     8157_S2

I wanted to find the datetime index and value of vol on a particular day for the particular element

item = '8157_S2'  ### element I am searching for
day  = '2019-10-21' ### date I am searching for
ses  = 1  #### which session of the day I am searching for
vol  =   df['vol'][df['element'] == item].loc[day].iloc[ses]     
dt_idx = df['vol'][df['element'] == item].loc[day].iloc[ses].index

Present output:

print(vol)    >> 99505
print(dt_idx) >> AttributeError: 'list' object has no attribute 'loc'

Expected output:

print(vol)    >> 99505    
print(dt_idx) >> 2019-10-21 09:04:58

What could be wrong in my code in finding the index of a particular row among many satisfying rows?

I got `KeyError: '2019-10-21'`, is that the real data, on indexes ? — azro, Nov 05 '19 at 21:37
https://stackoverflow.com/questions/11350770/select-by-partial-string-from-a-pandas-dataframe seems that partial match require a bit more — azro, Nov 05 '19 at 21:57

Florian Bernard · Answer 1 · 2019-11-06T21:43:50.507

0

Note that the Dataframe you post do not contain all the elements you are searching. That said, this should works.

item = '8157_S2' 
day = '2019-10-21' 
ses = 1  
selected = df[(df.element == item)].loc[day]
vol = selected.iloc[ses]["vol"]
dt_idx = selected.index.values[ses]
print(vol, dt_idx)

Maybe you should think about Transforming your index into a column, it's will be easier to work with.

edited Nov 06 '19 at 21:43

answered Nov 05 '19 at 23:44

Florian Bernard

2,561
1
9
22

But there is no column called `[ses]` in my dataframe? – Mainland Nov 06 '19 at 20:06
@Mainland I have updated my awnser, tell me if its work for you – Florian Bernard Nov 06 '19 at 20:43
Thanks for updating the answer. again `[day]` column is not there. – Mainland Nov 06 '19 at 21:01
So where is day ... its an index ? – Florian Bernard Nov 06 '19 at 21:02
Yes it is in the index which has date and time – Mainland Nov 06 '19 at 21:02

Python find particular index of a row among many satisfying rows in a dataframe

1 Answers1