如何过滤熊猫数据框架使用'在'和'不在'喜欢在SQL

我如何才能实现SQL的IN和NOT IN的等价?

我有一个所需值的列表。场景如下:

df = pd.DataFrame({'country': ['US', 'UK', 'Germany', 'China']})
countries_to_keep = ['UK', 'China']

# pseudo-code:
df[df['country'] not in countries_to_keep]

我目前的做法如下:

df = pd.DataFrame({'country': ['US', 'UK', 'Germany', 'China']})
df2 = pd.DataFrame({'country': ['UK', 'China'], 'matched': True})

# IN
df.merge(df2, how='inner', on='country')

# NOT IN
not_in = df.merge(df2, how='left', on='country')
not_in = not_in[pd.isnull(not_in['matched'])]

但这似乎是一个可怕的拼凑。有人能改进吗?

当前回答

我通常对行进行泛型过滤，像这样:

criterion = lambda row: row['countries'] not in countries
not_in = df[df.apply(criterion, axis=1)]

2013-11-13 17:14:32

其他回答

使用.query()方法的替代解决方案:

In [5]: df.query("countries in @countries_to_keep")
Out[5]:
  countries
1        UK
3     China

In [6]: df.query("countries not in @countries_to_keep")
Out[6]:
  countries
0        US
2   Germany

2017-07-19 12:19:40

从答案中整理出可能的解决方案:

用于:df[df['A']。isin ([3], 6)]

对于不在:

df [-df[“A”]。isin ([3], 6)] df [~ df[“A”]。isin ([3], 6)] df [df[“A”]。isin([3,6]) == False] df [np。logical_not“A”(df[]。isin ([3], 6))]

2019-06-01 15:21:29

我想过滤出dfbc行，有一个BUSINESS_ID，也是在dfProfilesBusIds的BUSINESS_ID

dfbc = dfbc[~dfbc['BUSINESS_ID'].isin(dfProfilesBusIds['BUSINESS_ID'])]

2017-07-13 03:12:17

我通常对行进行泛型过滤，像这样:

criterion = lambda row: row['countries'] not in countries
not_in = df[df.apply(criterion, axis=1)]

2013-11-13 17:14:32

你也可以在.query()中使用.isin():

df.query('country.isin(@countries_to_keep).values')

# Or alternatively:
df.query('country.isin(["UK", "China"]).values')

要否定你的查询，使用~:

df.query('~country.isin(@countries_to_keep).values')

更新:

另一种方法是使用比较操作符:

df.query('country == @countries_to_keep')

# Or alternatively:
df.query('country == ["UK", "China"]')

要对查询求反，使用!=:

df.query('country != @countries_to_keep')

2022-02-22 20:38:25

如何过滤熊猫数据框架使用'在'和'不在'喜欢在SQL

推荐文章

最新文章

标签