使用DuckDB选择行的DataFrames上的SQL语句

使用DuckDB，我们可以用SQL语句以高性能的方式查询panda DataFrames。

由于问题是如何根据列值从DataFrame中选择行？，问题中的示例是一个SQL查询，这个答案在本主题中看起来很合理。

例子：

In [1]: import duckdb

In [2]: import pandas as pd

In [3]: con = duckdb.connect()

In [4]: df = pd.DataFrame({"A": range(11), "B": range(11, 22)})

In [5]: df
Out[5]:
     A   B
0    0  11
1    1  12
2    2  13
3    3  14
4    4  15
5    5  16
6    6  17
7    7  18
8    8  19
9    9  20
10  10  21

In [6]: results = con.execute("SELECT * FROM df where A > 2").df()

In [7]: results
Out[7]:
    A   B
0   3  14
1   4  15
2   5  16
3   6  17
4   7  18
5   8  19
6   9  20
7  10  21

使用带有panda>=0.25.00的.query更灵活：

由于panda>=0.25.00，我们可以使用查询方法来使用panda方法过滤数据帧，甚至可以使用带有空格的列名。通常，列名中的空格会给出一个错误，但现在我们可以使用backtick（`）来解决这个问题-请参见GitHub：

# Example dataframe
df = pd.DataFrame({'Sender email':['ex@example.com', "reply@shop.com", "buy@shop.com"]})

     Sender email
0  ex@example.com
1  reply@shop.com
2    buy@shop.com

将.query与方法str.endswith一起使用：

df.query('`Sender email`.str.endswith("@shop.com")')

输出

     Sender email
1  reply@shop.com
2    buy@shop.com

---

此外，我们还可以通过在查询中用@前缀来使用局部变量：

domain = 'shop.com'
df.query('`Sender email`.str.endswith(@domain)')

输出

     Sender email
1  reply@shop.com
2    buy@shop.com

1.在query（）调用中使用f-string

如果用于过滤数据帧的列名来自本地变量，则f-string可能有用。例如

col = 'A'
df.query(f"{col} == 'foo'")

事实上，f-string也可以用于查询变量（datetime除外）：

col = 'A'
my_var = 'foo'
df.query(f"{col} == '{my_var}'") # if my_var is a string

my_num = 1
df.query(f"{col} == {my_num}") # if my_var is a number

my_date = '2022-12-10'
df.query(f"{col} == @my_date") # must use @ for datetime though

2.安装numexpr以加快query（）调用

panda文档建议在使用query（）时安装numexpr以加速数值计算。使用pipinstallnumexpr（或conda、sudo等，具体取决于您的环境）来安装它。

对于更大的数据帧（性能非常重要），带有numexpr引擎的df.query（）比df[mask]执行得更快。特别是，它在以下情况下表现更好。

字符串列上的逻辑和/或比较运算符

如果将一列字符串与其他字符串进行比较，并且要选择匹配的行，即使是单个比较操作，query（）的执行速度也比df[mask]快。例如，对于具有80k行的数据帧，速度快30%1，对于具有800k行的数据框架，速度快60%。2

df[df.A == 'foo']
df.query("A == 'foo'")  # <--- performs 30%-60% faster

这一差距随着操作数量的增加而增加（如果链接了4个比较df.query（）比df[mask]快2-2.3倍）1,2和/或数据帧长度的增加而增大。2

数字列上的多个操作

如果需要计算多个算术、逻辑或比较操作来创建布尔掩码以过滤df，则query（）执行速度更快。例如，对于一个有80k行的帧，它的速度快20%1，而对于一个800k行的帧来说，速度快2倍。2

df[(df.B % 5) **2 < 0.1]
df.query("(B % 5) **2 < 0.1")  # <--- performs 20%-100% faster.

随着操作数量的增加和/或数据帧长度的增加，性能差距也会增加。2

下图显示了随着数据帧长度的增加，这些方法的性能。3

3.在query（）中调用panda方法

Numexpr当前仅支持逻辑（&，|，~）、比较（==，>，<，>=，<=，！=）和基本算术运算符（+，-，*，/，**，%）。

例如，它不支持整数除法（//）。然而，调用等效的panda方法（floordiv（））是有效的。

df.query('B.floordiv(2) <= 3')  # or 
df.query('B.floordiv(2).le(3)')

# for pandas < 1.4, need `.values`
df.query('B.floordiv(2).values <= 3')

---

1使用80k行框架的基准代码

import numpy as np
df = pd.DataFrame({'A': 'foo bar foo baz foo bar foo foo'.split()*10000, 
                   'B': np.random.rand(80000)})

%timeit df[df.A == 'foo']
# 8.5 ms ± 104.5 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
%timeit df.query("A == 'foo'")
# 6.36 ms ± 95.7 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

%timeit df[((df.A == 'foo') & (df.A != 'bar')) | ((df.A != 'baz') & (df.A != 'buz'))]
# 29 ms ± 554 µs per loop (mean ± std. dev. of 10 runs, 100 loops each)
%timeit df.query("A == 'foo' & A != 'bar' | A != 'baz' & A != 'buz'")
# 16 ms ± 339 µs per loop (mean ± std. dev. of 10 runs, 100 loops each)

%timeit df[(df.B % 5) **2 < 0.1]
# 5.35 ms ± 37.6 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
%timeit df.query("(B % 5) **2 < 0.1")
# 4.37 ms ± 46.3 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

2使用800k行框架的基准代码

df = pd.DataFrame({'A': 'foo bar foo baz foo bar foo foo'.split()*100000, 
                   'B': np.random.rand(800000)})

%timeit df[df.A == 'foo']
# 87.9 ms ± 873 µs per loop (mean ± std. dev. of 10 runs, 100 loops each)
%timeit df.query("A == 'foo'")
# 54.4 ms ± 726 µs per loop (mean ± std. dev. of 10 runs, 100 loops each)

%timeit df[((df.A == 'foo') & (df.A != 'bar')) | ((df.A != 'baz') & (df.A != 'buz'))]
# 310 ms ± 3.4 ms per loop (mean ± std. dev. of 10 runs, 100 loops each)
%timeit df.query("A == 'foo' & A != 'bar' | A != 'baz' & A != 'buz'")
# 132 ms ± 2.43 ms per loop (mean ± std. dev. of 10 runs, 100 loops each)

%timeit df[(df.B % 5) **2 < 0.1]
# 54 ms ± 488 µs per loop (mean ± std. dev. of 10 runs, 100 loops each)
%timeit df.query("(B % 5) **2 < 0.1")
# 26.3 ms ± 320 µs per loop (mean ± std. dev. of 10 runs, 100 loops each)

3：用于生成字符串和数字的两种方法的性能图的代码。

from perfplot import plot
constructor = lambda n: pd.DataFrame({'A': 'foo bar foo baz foo bar foo foo'.split()*n, 'B': np.random.rand(8*n)})
plot(
    setup=constructor,
    kernels=[lambda df: df[(df.B%5)**2<0.1], lambda df: df.query("(B%5)**2<0.1")],
    labels= ['df[(df.B % 5) **2 < 0.1]', 'df.query("(B % 5) **2 < 0.1")'],
    n_range=[2**k for k in range(4, 24)],
    xlabel='Rows in DataFrame',
    title='Multiple mathematical operations on numbers',
    equality_check=pd.DataFrame.equals);
plot(
    setup=constructor,
    kernels=[lambda df: df[df.A == 'foo'], lambda df: df.query("A == 'foo'")],
    labels= ["df[df.A == 'foo']", """df.query("A == 'foo'")"""],
    n_range=[2**k for k in range(4, 24)],
    xlabel='Rows in DataFrame',
    title='Comparison operation on strings',
    equality_check=pd.DataFrame.equals);

您也可以使用.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'])]]

在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

我发现前面答案的语法是多余的，很难记住。Pandas在v0.13中引入了query（）方法，我更喜欢它。对于您的问题，您可以使用df.query（'col==val'）。

转载自query（）方法（实验）：

In [167]: n = 10

In [168]: df = pd.DataFrame(np.random.rand(n, 3), columns=list('abc'))

In [169]: df
Out[169]:
          a         b         c
0  0.687704  0.582314  0.281645
1  0.250846  0.610021  0.420121
2  0.624328  0.401816  0.932146
3  0.011763  0.022921  0.244186
4  0.590198  0.325680  0.890392
5  0.598892  0.296424  0.007312
6  0.634625  0.803069  0.123872
7  0.924168  0.325076  0.303746
8  0.116822  0.364564  0.454607
9  0.986142  0.751953  0.561512

# pure python
In [170]: df[(df.a < df.b) & (df.b < df.c)]
Out[170]:
          a         b         c
3  0.011763  0.022921  0.244186
8  0.116822  0.364564  0.454607

# query
In [171]: df.query('(a < b) & (b < c)')
Out[171]:
          a         b         c
3  0.011763  0.022921  0.244186
8  0.116822  0.364564  0.454607

您还可以通过在环境中添加@来访问变量。

exclude = ('red', 'orange')
df.query('color not in @exclude')