Python pandas 查询过滤某列的值的方法及示例代码

在SQL语句中,可以使用select查询指定where条件来查询过滤某列的值。但在Python pandas中需要使用特定的方法实现,本文主要介绍一下Python pandas 查询过滤某列的值的方法及示例代码。

1、使用loc[]实现

1)要选择列值等于some_value

df.loc[df['column_name'] == some_value]

2)要选择列包含在可迭代的some_values

df.loc[df['column_name'].isin(some_values)]

3)多个条件可以使用&

df.loc[(df['column_name'] >= A) & (df['column_name'] <= B)]

4)要选择列值不等于some_value

df.loc[df['column_name'] != some_value]

5)isin返回布尔值是在其中的条件,不在其中可以使用~

df.loc[~df['column_name'].isin(some_values)]

例如,

import pandas as pd
import numpy as np
df = pd.DataFrame({'A': 'foo bar foo bar foo bar foo foo'.split(),
                   'B': 'one one two three two two one three'.split(),
                   'C': np.arange(8), 'D': np.arange(8) * 2})
print(df)
#      A      B  C   D
# 0  foo    one  0   0
# 1  bar    one  1   2
# 2  foo    two  2   4
# 3  bar  three  3   6
# 4  foo    two  4   8
# 5  bar    two  5  10
# 6  foo    one  6  12
# 7  foo  three  7  14
print(df.loc[df['A'] == 'foo'])
# 输出:
#      A      B  C   D
# 0  foo    one  0   0
# 2  foo    two  2   4
# 4  foo    two  4   8
# 6  foo    one  6  12
# 7  foo  three  7  14
print(df.loc[df['B'].isin(['one','three'])])
# 输出:
#      A      B  C   D
# 0  foo    one  0   0
# 1  bar    one  1   2
# 3  bar  three  3   6
# 6  foo    one  6  12
# 7  foo  three  7  14
df = df.set_index(['B'])
print(df.loc['one'])
# 输出:
#        A  C   D
# B              
# one  foo  0   0
# one  bar  1   2
# one  foo  6  12
df.loc[df.index.isin(['one','two'])]
# 输出:
#        A  C   D
# B              
# one  foo  0   0
# one  bar  1   2
# two  foo  2   4
# two  foo  4   8
# two  bar  5  10
# one  foo  6  12

2、使用query()

pandas >= 0.25.0 可以使用该query()方法来过滤带有pandas 方法的DataFrame。通常,列名中的空格会产生错误,但现在我们可以使用反勾号(')来解决这个问题。

例如,

import pandas as pd
import numpy as np
df = pd.DataFrame({'Sender email':['ex@example.com', "reply@cjavapy.com", "buy@cjavapy.com"]})
#      Sender email
# 0  ex@example.com
# 1  reply@cjavapy.com
# 2    buy@cjavapy.com
df.query('`Sender email`.str.endswith("@cjavapy.com")')
#      Sender email
# 1  reply@cjavapy.com
# 2    buy@cjavapy.com
domain = 'cjavapy.com'
df.query('`Sender email`.str.endswith(@domain)')
#      Sender email
# 1  reply@cjavapy.com
# 2    buy@cjavapy.com

推荐阅读
cjavapy编程之路首页