How to select row index by column values

Question

I am using Dataframe in python and trying to select row index based on multiple column values. Below is the 100th row's value of my dataframe:

>>> df.loc[100]
id                                       100
iid     9cdb7709-38f8-442a-812a-986b5b148161
lat                                 -37.8294
lon                                  144.979
name                      Doryanthes excelsa
Name: 100, dtype: object

I want to select the rows whose id is 100 and lat is -37.8294 by below command:

>>> df[(df['id'] == 100) & (df['lat'] == -37.8294)].index
Int64Index([], dtype='int64')

the above command returns an empty index. I don't understand that I can get the value by df.loc[100] command but why can't I get the row index from the above command?

are all your dtypes consistent? is 'lat' isn't a string or anthing like that? Your output would make me think that it is not finding anything that matches those conditions, and dtype inconsistencies is a common way for that to happen. — Stael
– Stael, Commented Sep 11, 2017 at 12:13

jezrael · Accepted Answer · 2017-09-11 12:23:58Z

5

You want select float, but there is precision problem, so get no match and return empty dataframe

So need numpy.isclose:

df1 = df[(df['id'] == 100) & (np.isclose(df['lat'],-37.8294))]

Sample:

df = pd.DataFrame({'id':[100,200],
                   'lat':[-37.82940007,-37.82]})
print (df)
    id      lat
0  100 -37.8294
1  200 -37.8200

df1 = df[(df['id'] == 100) & (df['lat'] == -37.8294)]
print (df1)
Empty DataFrame
Columns: [id, lat]
Index: []

df1 = df[(df['id'] == 100) & (np.isclose(df['lat'],-37.8294))]
print (df1)
    id      lat
0  100 -37.8294

edited Sep 11, 2017 at 12:23

answered Sep 11, 2017 at 12:14

jezrael

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

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

7 Comments

jezrael Over a year ago

@Stael - Thank you

Stael Over a year ago

this is a good shout - I wouldn't have thought of this, but in this example is it actually the problem? With default display options pandas seems to show floats to 6dp, so it seems unlikely that this is a precision thing. I assumed he's got some strings in there by accident.

Joey Yi Zhao Over a year ago

yes this solved my issue. It is a precision problem. I will accept the answer in 5 minutes. Thanks.

Stael Over a year ago

@ZhaoYi huh, what was the actual value for 'lat' that produced that output then?

Joey Yi Zhao Over a year ago

@Stael the output of that command prints an array of index not the value.

|

Collectives™ on Stack Overflow

How to select row index by column values

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related