如何根据Pandas中某列的值从DataFrame中选择行?
在SQL中,我会使用:
SELECT *
FROM table
WHERE column_name = some_value
如何根据Pandas中某列的值从DataFrame中选择行?
在SQL中,我会使用:
SELECT *
FROM table
WHERE column_name = some_value
当前回答
下面是一个简单的例子
from pandas import DataFrame
# Create data set
d = {'Revenue':[100,111,222],
'Cost':[333,444,555]}
df = DataFrame(d)
# mask = Return True when the value in column "Revenue" is equal to 111
mask = df['Revenue'] == 111
print mask
# Result:
# 0 False
# 1 True
# 2 False
# Name: Revenue, dtype: bool
# Select * FROM df WHERE Revenue = 111
df[mask]
# Result:
# Cost Revenue
# 1 444 111
其他回答
tl;博士
熊猫相当于
select * from table where column_name = some_value
is
table[table.column_name == some_value]
多种条件:
table[(table.column_name == some_value) | (table.column_name2 == some_value2)]
or
table.query('column_name == some_value | column_name2 == some_value2')
代码示例
import pandas as pd
# Create data set
d = {'foo':[100, 111, 222],
'bar':[333, 444, 555]}
df = pd.DataFrame(d)
# Full dataframe:
df
# Shows:
# bar foo
# 0 333 100
# 1 444 111
# 2 555 222
# Output only the row(s) in df where foo is 222:
df[df.foo == 222]
# Shows:
# bar foo
# 2 555 222
在上面的代码中,是df[df.foo==222]行根据列值给出行,在本例中为222。
也可能出现多种情况:
df[(df.foo == 222) | (df.bar == 444)]
# bar foo
# 1 444 111
# 2 555 222
但在这一点上,我建议使用查询函数,因为它不那么冗长,并产生相同的结果:
df.query('foo == 222 | bar == 444')
下面是一个简单的例子
from pandas import DataFrame
# Create data set
d = {'Revenue':[100,111,222],
'Cost':[333,444,555]}
df = DataFrame(d)
# mask = Return True when the value in column "Revenue" is equal to 111
mask = df['Revenue'] == 111
print mask
# Result:
# 0 False
# 1 True
# 2 False
# Name: Revenue, dtype: bool
# Select * FROM df WHERE Revenue = 111
df[mask]
# Result:
# Cost Revenue
# 1 444 111
在Pandas的更新版本中,受文档启发(查看数据):
df[df["colume_name"] == some_value] #Scalar, True/False..
df[df["colume_name"] == "some_value"] #String
通过将子句放在括号()中,并用&和|(和/或)组合来组合多个条件。这样地:
df[(df["colume_name"] == "some_value1") & (pd[pd["colume_name"] == "some_value2"])]
其他过滤器
pandas.notna(df["colume_name"]) == True # Not NaN
df['colume_name'].str.contains("text") # Search for "text"
df['colume_name'].str.lower().str.contains("text") # Search for "text", after converting to lowercase
您也可以使用.apply:
df.apply(lambda row: row[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
结果与@unsubu提到的使用相同
df[[df['B'].isin(['one','three'])]]
使用numpy.where可以获得更快的结果。
例如,使用unubtu的设置-
In [76]: df.iloc[np.where(df.A.values=='foo')]
Out[76]:
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
时间比较:
In [68]: %timeit df.iloc[np.where(df.A.values=='foo')] # fastest
1000 loops, best of 3: 380 µs per loop
In [69]: %timeit df.loc[df['A'] == 'foo']
1000 loops, best of 3: 745 µs per loop
In [71]: %timeit df.loc[df['A'].isin(['foo'])]
1000 loops, best of 3: 562 µs per loop
In [72]: %timeit df[df.A=='foo']
1000 loops, best of 3: 796 µs per loop
In [74]: %timeit df.query('(A=="foo")') # slowest
1000 loops, best of 3: 1.71 ms per loop