Get first row of dataframe in Python Pandas based on criteria without iterating over the whole dataframe

Question

Let's say I want to get the first row of a dataframe where a certain column has negative value:

import pandas as pd
df = pd.DataFrame(columns=['c'],data=[[2.4,2.3,-1.0]])
index = df.loc[df['c'] < 0].index[0]

Now this works but in case I have a dataframe with millions of rows, I don't want to iterate over all of them to get the index. Is there a way to get the index without having to loop over each row? (Would be slow)

Hmm yes he does mention the df has millions of rows @jezrael — yatu, Mar 21 '19 at 15:10
@yatu - exactly, it is only reason for close, else not. your solution is nice. — jezrael, Mar 21 '19 at 15:11

yatu · Answer 1 · 2019-03-21T15:12:22.090

1

You can check which rows in a given column are negative with Series.lt and use idxmax to take the index of the first True:

df = pd.DataFrame(columns=['c'],data=[2.4,2.3,-1.0])
print(df)
    c
0  2.4
1  2.3
2 -1.0

df.c.lt(0).idxmax()
# 2

edited Mar 21 '19 at 15:12

answered Mar 21 '19 at 15:02

yatu

86,083
12
84
139

Doesn't df.c.lt(0) eval each row before idxmax() is applied? – Zachary Ryan Smith Dec 23 '20 at 12:09
It does yes, it performs a search over the boolean result @ZacharyRyanSmith – yatu Dec 23 '20 at 14:14
If df.c.lt(0) evals each row, how is it better than `index = df.loc[df['c'] < 0].index[0]`? – Zachary Ryan Smith Dec 23 '20 at 16:47
Because idxmax returns the first true. df['c'] < 0].index[0] looks at the index, no filtering here. @ZacharyRyanSmith – yatu Dec 23 '20 at 16:54

Get first row of dataframe in Python Pandas based on criteria without iterating over the whole dataframe

1 Answers1

Linked