CODEWITHSUNDEEP
pythonpandas
emax
emax

Reputation: 7245

Python: how select pandas rows based on condition on other columns?

I have a dataframe that looks like the following

df
     city   val
0   London   3
1   London   -1
2   London   -1
3   Paris    -5
4   Paris    -2
5   Rome     2
6   Rome     2

I want to select only the city that have at least one val < 0. I would like to have the following:

df
     city   val
0   London   3
1   London   -1
2   London   -1
3   Paris    -5
4   Paris    -2

Upvotes: 3

Views: 63

Answers (2)

jezrael
jezrael

Reputation: 862406

Create mask and filter rows with GroupBy.transform:

df = df[df['val'].lt(1).groupby(df['city']).transform('any')]
print (df)
     city  val
0  London    3
1  London   -1
2  London   -1
3   Paris   -5
4   Paris   -2

Or filter city with at least one row match less like 1 and filter original city column by Series.isin:

df[df['city'].isin(df.loc[df['val'].lt(1), 'city'])]

Upvotes: 1

U13-Forward
U13-Forward

Reputation: 71560

Use loc with groupby and .transform(min):

>>> df.loc[df.groupby('city')['val'].transform(min).lt(0)]
     city  val
0  London    3
1  London   -1
2  London   -1
3   Paris   -5
4   Paris   -2
>>>

Since you're filtering under 0, just filter if the minimum value of each group is lower than 0.

Upvotes: 1

Related Questions