How to Filter Pandas Dataframe Using 'In' and 'Not In' Like in Sql

How to filter Pandas dataframe using 'in' and 'not in' like in SQL

You can use pd.Series.isin.

For "IN" use: something.isin(somewhere)

Or for "NOT IN": ~something.isin(somewhere)

As a worked example:

import pandas as pd

>>> df
  country
0        US
1        UK
2   Germany
3     China
>>> countries_to_keep
['UK', 'China']
>>> df.country.isin(countries_to_keep)
0    False
1     True
2    False
3     True
Name: country, dtype: bool
>>> df[df.country.isin(countries_to_keep)]
  country
1        UK
3     China
>>> df[~df.country.isin(countries_to_keep)]
  country
0        US
2   Germany

Py Polars: How to filter using 'in' and 'not in' like in SQL

You were close.

df.filter(~pl.col('fruits').is_in(exclude_fruit))

shape: (3, 5)
┌─────┬────────┬─────┬────────┬──────────┐
│ A   ┆ fruits ┆ B   ┆ cars   ┆ optional │
│ --- ┆ ---    ┆ --- ┆ ---    ┆ ---      │
│ i64 ┆ str    ┆ i64 ┆ str    ┆ i64      │
╞═════╪════════╪═════╪════════╪══════════╡
│ 1   ┆ banana ┆ 5   ┆ beetle ┆ 28       │
├╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌┤
│ 2   ┆ banana ┆ 4   ┆ audi   ┆ 300      │
├╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌┤
│ 5   ┆ banana ┆ 1   ┆ beetle ┆ -30      │
└─────┴────────┴─────┴────────┴──────────┘

Filter dataframe rows if value in column is in a set list of values

Use the isin method:

rpt[rpt['STK_ID'].isin(stk_list)]

Filtering pandas with multiple conditions in a better way

You can simply us isin() as follows:

df4['subtestID'].isin([325,341,1164,1200]) 

Find the column value based on condition

The Pandas query function allows for SQL-like queries to filter a data frame. Then use unique() on the results to return the unique name.

rows = df.query('age == 2 and cond == 9')
print(rows["name"].unique())

For more query examples, see here.

Using the in operator in pandas.DataFrame without getting ambiguous error

df[df['year'].isin(['1990','2020'])]

or

df.loc[df['year'].isin(['1990','2020'])]

---

Related Topics