如何过滤熊猫数据框架使用'在'和'不在'喜欢在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'])]

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

当前回答

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

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

2017-07-13 03:12:17

其他回答

你可以使用pd.Series.isin。

对于“IN”的用法:something.isin(某处)

或者对于"NOT IN": ~something.isin(某处)

举个例子:

>>> 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

2013-11-13 17:13:39

如果你想保持列表的顺序，有一个小技巧:

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


ind=[df.index[df['country']==i].tolist() for i in countries_to_keep]
flat_ind=[item for sublist in ind for item in sublist]

df.reindex(flat_ind)

   country
2  Germany
0       US

2020-09-16 10:38:18

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

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

2013-11-13 17:14:32

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

实现:

df[df.countries.isin(countries)]

实施不像在其他国家:

df[df.countries.isin([x for x in np.unique(df.countries) if x not in countries])]

2018-04-04 11:51:01

我的2c价值: 我需要一个数据框架的in和ifelse语句的组合，这对我来说很有用。

sale_method = pd.DataFrame(model_data["Sale Method"].str.upper())
sale_method["sale_classification"] = np.where(
    sale_method["Sale Method"].isin(["PRIVATE"]),
    "private",
    np.where(
        sale_method["Sale Method"].str.contains("AUCTION"), "auction", "other"
    ),
)

2021-06-24 01:41:58

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

推荐文章

最新文章

标签