像SQL一样的大 pandas 文本匹配? [英] Pandas text matching like SQL's LIKE?

问题描述

有没有办法做类似于在熊猫文本DataFrame列上使用SQL的LIKE语法，以便它返回索引列表或可用于为数据帧建立索引的布尔值列表?例如，我希望能够匹配该列以'prefix_'开头的所有行，类似于SQL中的WHERE <col> LIKE prefix_%.

解决方案

您可以使用Series方法 str.contains (使用正则表达式):

In [13]: s.str.contains('^a', na=False)
Out[13]: 
0     True
1     True
2    False
3    False
dtype: bool

因此您可以执行df[col].str.startswith ...

另请参阅文档的SQL比较部分.

注意:(如OP所指出)，默认情况下，NaN会传播(因此，如果您要将结果用作布尔掩码，则会导致索引错误)，我们使用此标志来表示NaN应该映射为False.

In [14]: s.str.startswith('a')  # can't use as boolean mask
Out[14]:
0     True
1     True
2    False
3      NaN
dtype: object

Is there a way to do something similar to SQL's LIKE syntax on a pandas text DataFrame column, such that it returns a list of indices, or a list of booleans that can be used for indexing the dataframe? For example, I would like to be able to match all rows where the column starts with 'prefix_', similar to WHERE <col> LIKE prefix_% in SQL.

解决方案

You can use the Series method str.startswith (which takes a regex):

In [11]: s = pd.Series(['aa', 'ab', 'ca', np.nan])

In [12]: s.str.startswith('a', na=False)
Out[12]: 
0     True
1     True
2    False
3    False
dtype: bool

You can also do the same with str.contains (using a regex):

In [13]: s.str.contains('^a', na=False)
Out[13]: 
0     True
1     True
2    False
3    False
dtype: bool

So you can do df[col].str.startswith...

See also the SQL comparison section of the docs.

Note: (as pointed out by OP) by default NaNs will propagate (and hence cause an indexing error if you want to use the result as a boolean mask), we use this flag to say that NaN should map to False.

In [14]: s.str.startswith('a')  # can't use as boolean mask
Out[14]:
0     True
1     True
2    False
3      NaN
dtype: object

这篇关于像SQL一样的大 pandas 文本匹配?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！