Pandas 中文参考指南

Enhancing performance

在教程的本部分，我们将研究如何使用 Cython、Numba 和 pandas.eval() 加快在 pandas DataFrame 上运行的特定函数。通常，使用 Cython 和 Numba 比使用 pandas.eval() 提供更大的加速，但需要更多代码。

In this part of the tutorial, we will investigate how to speed up certain functions operating on pandas DataFrame using Cython, Numba and pandas.eval(). Generally, using Cython and Numba can offer a larger speedup than using pandas.eval() but will require a lot more code.

除了遵循本教程中的步骤外，强烈建议有兴趣提高性能的用户为 pandas 安装 recommended dependencies。这些依赖项通常没有默认安装，但如果存在，将提供速度提升。

In addition to following the steps in this tutorial, users interested in enhancing performance are highly encouraged to install the recommended dependencies for pandas. These dependencies are often not installed by default, but will offer speed improvements if present.

Cython (writing C extensions for pandas)

对于许多使用情况，用纯 Python 和 NumPy 编写 pandas 就足够了。然而，在一些计算密集型应用程序中，通过将工作卸载到 cython，有可能实现显著的速度提升。

For many use cases writing pandas in pure Python and NumPy is sufficient. In some computationally heavy applications however, it can be possible to achieve sizable speed-ups by offloading work to cython.

本教程假设您已尽可能地在 Python 中重构，例如尝试移除 for 循环并使用 NumPy 向量化。通常值得优先在 Python 中进行优化。

This tutorial assumes you have refactored as much as possible in Python, for example by trying to remove for-loops and making use of NumPy vectorization. It’s always worth optimising in Python first.

本教程详细介绍了对缓慢计算进行 Cython 化的“典型”过程。我们使用 example from the Cython documentation 但在 pandas 上下文中。我们最终的 Cython 化解决方案比纯 Python 解决方案快约 100 倍。

This tutorial walks through a “typical” process of cythonizing a slow computation. We use an example from the Cython documentation but in the context of pandas. Our final cythonized solution is around 100 times faster than the pure Python solution.

Pure Python

我们有一个 DataFrame，我们希望对其每一行应用一个函数。

We have a DataFrame to which we want to apply a function row-wise.

In [1]: df = pd.DataFrame(
   ...:     {
   ...:         "a": np.random.randn(1000),
   ...:         "b": np.random.randn(1000),
   ...:         "N": np.random.randint(100, 1000, (1000)),
   ...:         "x": "x",
   ...:     }
   ...: )
   ...:

In [2]: df
Out[2]:
            a         b    N  x
0    0.469112 -0.218470  585  x
1   -0.282863 -0.061645  841  x
2   -1.509059 -0.723780  251  x
3   -1.135632  0.551225  972  x
4    1.212112 -0.497767  181  x
..        ...       ...  ... ..
995 -1.512743  0.874737  374  x
996  0.933753  1.120790  246  x
997 -0.308013  0.198768  157  x
998 -0.079915  1.757555  977  x
999 -1.010589 -1.115680  770  x

[1000 rows x 4 columns]

这是纯 Python 中的函数：

Here’s the function in pure Python:

In [3]: def f(x):
   ...:     return x * (x - 1)
   ...:

In [4]: def integrate_f(a, b, N):
   ...:     s = 0
   ...:     dx = (b - a) / N
   ...:     for i in range(N):
   ...:         s += f(a + i * dx)
   ...:     return s * dx
   ...:

我们通过使用 DataFrame.apply()（每一行）来实现我们的结果：

We achieve our result by using DataFrame.apply() (row-wise):

In [5]: %timeit df.apply(lambda x: integrate_f(x["a"], x["b"], x["N"]), axis=1)
74.9 ms +- 728 us per loop (mean +- std. dev. of 7 runs, 10 loops each)

让我们看看在使用 prun ipython magic function 执行此操作过程中，时间花在哪里：

Let’s take a look and see where the time is spent during this operation using the prun ipython magic function:

# most time consuming 4 calls
In [6]: %prun -l 4 df.apply(lambda x: integrate_f(x["a"], x["b"], x["N"]), axis=1)  # noqa E999
         605956 function calls (605938 primitive calls) in 0.167 seconds

   Ordered by: internal time
   List reduced from 163 to 4 due to restriction 4

   ncalls  tottime  percall  cumtime  percall filename:lineno(function)
     1000    0.097    0.000    0.148    0.000 <ipython-input-4-c2a74e076cf0>:1(integrate_f)
   552423    0.051    0.000    0.051    0.000 <ipython-input-3-c138bdd570e3>:1(f)
     3000    0.003    0.000    0.012    0.000 series.py:1095(__getitem__)
     3000    0.002    0.000    0.005    0.000 series.py:1220(_get_value)

到目前为止，绝大部分时间都花在 integrate_f 或 f 中，因此我们将集中精力对这两个函数进行 Cython 化。

By far the majority of time is spend inside either integrate_f or f, hence we’ll concentrate our efforts cythonizing these two functions.

Plain Cython

首先，我们需要将 Cython magic 函数导入到 IPython：

First we’re going to need to import the Cython magic function to IPython:

In [7]: %load_ext Cython

现在，我们只需将函数复制到 Cython：

Now, let’s simply copy our functions over to Cython:

In [8]: %%cython
   ...: def f_plain(x):
   ...:     return x * (x - 1)
   ...: def integrate_f_plain(a, b, N):
   ...:     s = 0
   ...:     dx = (b - a) / N
   ...:     for i in range(N):
   ...:         s += f_plain(a + i * dx)
   ...:     return s * dx
   ...:
In [9]: %timeit df.apply(lambda x: integrate_f_plain(x["a"], x["b"], x["N"]), axis=1)
46.6 ms +- 466 us per loop (mean +- std. dev. of 7 runs, 10 loops each)

与纯 Python 方法相比，这将性能提升了三分之一。

This has improved the performance compared to the pure Python approach by one-third.

Declaring C types

我们可以注释函数变量和返回类型，也可以使用 cdef 和 cpdef 来提升性能：

We can annotate the function variables and return types as well as use cdef and cpdef to improve performance:

In [10]: %%cython
   ....: cdef double f_typed(double x) except? -2:
   ....:     return x * (x - 1)
   ....: cpdef double integrate_f_typed(double a, double b, int N):
   ....:     cdef int i
   ....:     cdef double s, dx
   ....:     s = 0
   ....:     dx = (b - a) / N
   ....:     for i in range(N):
   ....:         s += f_typed(a + i * dx)
   ....:     return s * dx
   ....:
In [11]: %timeit df.apply(lambda x: integrate_f_typed(x["a"], x["b"], x["N"]), axis=1)
7.76 ms +- 83.8 us per loop (mean +- std. dev. of 7 runs, 100 loops each)

使用 C 类型对函数进行注释后，与最初的 Python 实现相比，性能提升了十倍以上。

Annotating the functions with C types yields an over ten times performance improvement compared to the original Python implementation.

Using ndarray

在重新分析时，时间用于从每一行创建 Series，并从索引和序列（每一行执行三次）调用 getitem。这些 Python 函数调用很耗时，可以通过传递 np.ndarray 来改进。

When re-profiling, time is spent creating a Series from each row, and calling getitem from both the index and the series (three times for each row). These Python function calls are expensive and can be improved by passing an np.ndarray.

In [12]: %prun -l 4 df.apply(lambda x: integrate_f_typed(x["a"], x["b"], x["N"]), axis=1)
         52533 function calls (52515 primitive calls) in 0.019 seconds

   Ordered by: internal time
   List reduced from 161 to 4 due to restriction 4

   ncalls  tottime  percall  cumtime  percall filename:lineno(function)
     3000    0.003    0.000    0.012    0.000 series.py:1095(__getitem__)
     3000    0.002    0.000    0.005    0.000 series.py:1220(_get_value)
     3000    0.002    0.000    0.002    0.000 base.py:3777(get_loc)
     3000    0.002    0.000    0.002    0.000 indexing.py:2765(check_dict_or_set_indexers)
In [13]: %%cython
   ....: cimport numpy as np
   ....: import numpy as np
   ....: cdef double f_typed(double x) except? -2:
   ....:     return x * (x - 1)
   ....: cpdef double integrate_f_typed(double a, double b, int N):
   ....:     cdef int i
   ....:     cdef double s, dx
   ....:     s = 0
   ....:     dx = (b - a) / N
   ....:     for i in range(N):
   ....:         s += f_typed(a + i * dx)
   ....:     return s * dx
   ....: cpdef np.ndarray[double] apply_integrate_f(np.ndarray col_a, np.ndarray col_b,
   ....:                                            np.ndarray col_N):
   ....:     assert (col_a.dtype == np.float64
   ....:             and col_b.dtype == np.float64 and col_N.dtype == np.dtype(int))
   ....:     cdef Py_ssize_t i, n = len(col_N)
   ....:     assert (len(col_a) == len(col_b) == n)
   ....:     cdef np.ndarray[double] res = np.empty(n)
   ....:     for i in range(len(col_a)):
   ....:         res[i] = integrate_f_typed(col_a[i], col_b[i], col_N[i])
   ....:     return res
   ....:
Content of stderr:
In file included from /home/runner/micromamba/envs/test/lib/python3.10/site-packages/numpy/core/include/numpy/ndarraytypes.h:1929,
                 from /home/runner/micromamba/envs/test/lib/python3.10/site-packages/numpy/core/include/numpy/ndarrayobject.h:12,
                 from /home/runner/micromamba/envs/test/lib/python3.10/site-packages/numpy/core/include/numpy/arrayobject.h:5,
                 from /home/runner/.cache/ipython/cython/_cython_magic_96d1519457caba8fa4f96b759be00659f51c6b18.c:1215:
/home/runner/micromamba/envs/test/lib/python3.10/site-packages/numpy/core/include/numpy/npy_1_7_deprecated_api.h:17:2: warning: #warning "Using deprecated NumPy API, disable it with " "#define NPY_NO_DEPRECATED_API NPY_1_7_API_VERSION" [-Wcpp]
   17 | #warning "Using deprecated NumPy API, disable it with " \
      |  ^~~~~~~

此实现将创建一个零数组，并将应用于每一行的 integrate_f_typed 的结果插入其中。在 ndarray 上循环比在 Series 对象上循环更快。

This implementation creates an array of zeros and inserts the result of integrate_f_typed applied over each row. Looping over an ndarray is faster in Cython than looping over a Series object.

由于 apply_integrate_f 被类型化为接受 np.ndarray，因此需要 Series.to_numpy() 调用才能利用此函数。

Since apply_integrate_f is typed to accept an np.ndarray, Series.to_numpy() calls are needed to utilize this function.

In [14]: %timeit apply_integrate_f(df["a"].to_numpy(), df["b"].to_numpy(), df["N"].to_numpy())
834 us +- 4.04 us per loop (mean +- std. dev. of 7 runs, 1,000 loops each)

性能比之前的实现提高了近十倍。

Performance has improved from the prior implementation by almost ten times.

Disabling compiler directives

现在大部分时间都花在了 apply_integrate_f 中。禁用 Cython 的 boundscheck 和 wraparound 检查可以提高性能。

The majority of the time is now spent in apply_integrate_f. Disabling Cython’s boundscheck and wraparound checks can yield more performance.

In [15]: %prun -l 4 apply_integrate_f(df["a"].to_numpy(), df["b"].to_numpy(), df["N"].to_numpy())
         78 function calls in 0.001 seconds

   Ordered by: internal time
   List reduced from 21 to 4 due to restriction 4

   ncalls  tottime  percall  cumtime  percall filename:lineno(function)
        1    0.001    0.001    0.001    0.001 <string>:1(<module>)
        1    0.000    0.000    0.001    0.001 {built-in method builtins.exec}
        3    0.000    0.000    0.000    0.000 frame.py:4062(__getitem__)
        3    0.000    0.000    0.000    0.000 base.py:541(to_numpy)
In [16]: %%cython
   ....: cimport cython
   ....: cimport numpy as np
   ....: import numpy as np
   ....: cdef np.float64_t f_typed(np.float64_t x) except? -2:
   ....:     return x * (x - 1)
   ....: cpdef np.float64_t integrate_f_typed(np.float64_t a, np.float64_t b, np.int64_t N):
   ....:     cdef np.int64_t i
   ....:     cdef np.float64_t s = 0.0, dx
   ....:     dx = (b - a) / N
   ....:     for i in range(N):
   ....:         s += f_typed(a + i * dx)
   ....:     return s * dx
   ....: @cython.boundscheck(False)
   ....: @cython.wraparound(False)
   ....: cpdef np.ndarray[np.float64_t] apply_integrate_f_wrap(
   ....:     np.ndarray[np.float64_t] col_a,
   ....:     np.ndarray[np.float64_t] col_b,
   ....:     np.ndarray[np.int64_t] col_N
   ....: ):
   ....:     cdef np.int64_t i, n = len(col_N)
   ....:     assert len(col_a) == len(col_b) == n
   ....:     cdef np.ndarray[np.float64_t] res = np.empty(n, dtype=np.float64)
   ....:     for i in range(n):
   ....:         res[i] = integrate_f_typed(col_a[i], col_b[i], col_N[i])
   ....:     return res
   ....:
Content of stderr:
In file included from /home/runner/micromamba/envs/test/lib/python3.10/site-packages/numpy/core/include/numpy/ndarraytypes.h:1929,
                 from /home/runner/micromamba/envs/test/lib/python3.10/site-packages/numpy/core/include/numpy/ndarrayobject.h:12,
                 from /home/runner/micromamba/envs/test/lib/python3.10/site-packages/numpy/core/include/numpy/arrayobject.h:5,
                 from /home/runner/.cache/ipython/cython/_cython_magic_3bb7bde31cdaf5ab952bfe5a612c6edef03550d0.c:1216:
/home/runner/micromamba/envs/test/lib/python3.10/site-packages/numpy/core/include/numpy/npy_1_7_deprecated_api.h:17:2: warning: #warning "Using deprecated NumPy API, disable it with " "#define NPY_NO_DEPRECATED_API NPY_1_7_API_VERSION" [-Wcpp]
   17 | #warning "Using deprecated NumPy API, disable it with " \
      |  ^~~~~~~
In [17]: %timeit apply_integrate_f_wrap(df["a"].to_numpy(), df["b"].to_numpy(), df["N"].to_numpy())
620 us +- 2.65 us per loop (mean +- std. dev. of 7 runs, 1,000 loops each)

然而，一个访问数组中无效位置的循环索引器 i 将会引起段错误，因为不检查内存访问。有关 boundscheck 和 wraparound 的详细信息，请参见 Cython 在 compiler directives 中的文档。

However, a loop indexer i accessing an invalid location in an array would cause a segfault because memory access isn’t checked. For more about boundscheck and wraparound, see the Cython docs on compiler directives.

Numba (JIT compilation)

静态编译 Cython 代码的一个替代方法是使用带有 Numba 的动态即时（JIT）编译器。

An alternative to statically compiling Cython code is to use a dynamic just-in-time (JIT) compiler with Numba.

Numba 允许您编写纯 Python 函数，该函数可以 JIT 编译为本机机器指令，性能类似于 C、C++ 和 Fortran，方法是用 @jit 修饰您的函数。

Numba allows you to write a pure Python function which can be JIT compiled to native machine instructions, similar in performance to C, C++ and Fortran, by decorating your function with @jit.

Numba 在导入时间、运行时或静态地（使用包含的 pycc 工具）使用 LLVM 编译器基础设施生成优化的机器码，从而发挥作用。Numba 支持将 Python 编译为在 CPU 或 GPU 硬件上运行，且旨在与 Python 科学软件堆栈集成。

Numba works by generating optimized machine code using the LLVM compiler infrastructure at import time, runtime, or statically (using the included pycc tool). Numba supports compilation of Python to run on either CPU or GPU hardware and is designed to integrate with the Python scientific software stack.

@jit 编译将在函数的运行时增加开销，因此当使用小数据集时，可能无法实现性能提升。考虑 caching 您函数，以在每次运行函数时避免编译开销。

The @jit compilation will add overhead to the runtime of the function, so performance benefits may not be realized especially when using small data sets. Consider caching your function to avoid compilation overhead each time your function is run.

Numba 可通过 2 种方式与 pandas 配合使用：

Numba can be used in 2 ways with pandas:

  1. Specify the engine="numba" keyword in select pandas methods

  2. Define your own Python function decorated with @jit and pass the underlying NumPy array of Series or DataFrame (using Series.to_numpy()) into the function

pandas Numba Engine

如果已安装 Numba，则可以在选定的 pandas 方法中指定 engine="numba" 以使用 Numba 执行该方法。支持 engine="numba" 的方法还将具有 engine_kwargs 关键字，该关键字接受允许人们用布尔值指定 "nogil"、"nopython" 和 "parallel" 键的字典以传递到 @jit 修饰器中。如果未指定 engine_kwargs，则它将默认为 {"nogil": False, "nopython": True, "parallel": False}，除非另有指定。

If Numba is installed, one can specify engine="numba" in select pandas methods to execute the method using Numba. Methods that support engine="numba" will also have an engine_kwargs keyword that accepts a dictionary that allows one to specify "nogil", "nopython" and "parallel" keys with boolean values to pass into the @jit decorator. If engine_kwargs is not specified, it defaults to {"nogil": False, "nopython": True, "parallel": False} unless otherwise specified.

从性能角度而言，使用 Numba 引擎首次运行函数时会很慢，因为 Numba 会产生一些函数编译开销。但是，JIT 编译的函数会进行缓存，并且后续调用将很快。总体而言，Numba 引擎对于大量数据点（例如 100 万以上）性能良好。

In terms of performance, the first time a function is run using the Numba engine will be slow as Numba will have some function compilation overhead. However, the JIT compiled functions are cached, and subsequent calls will be fast. In general, the Numba engine is performant with a larger amount of data points (e.g. 1+ million).

 
In [1]: data = pd.Series(range(1_000_000))  # noqa: E225

In [2]: roll = data.rolling(10)

In [3]: def f(x):
   ...:     return np.sum(x) + 5
# Run the first time, compilation time will affect performance
In [4]: %timeit -r 1 -n 1 roll.apply(f, engine='numba', raw=True)
1.23 s ± 0 ns per loop (mean ± std. dev. of 1 run, 1 loop each)
# Function is cached and performance will improve
In [5]: %timeit roll.apply(f, engine='numba', raw=True)
188 ms ± 1.93 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

In [6]: %timeit roll.apply(f, engine='cython', raw=True)
3.92 s ± 59 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

如果您的计算硬件包含多个 CPU，则可以通过将 parallel 设置为 True 来利用 1 个以上的 CPU，从而实现最大的性能提升。在内部，pandas 利用 numba 对 DataFrame 的列中的计算进行并行化；因此，此性能提升仅适用于具有大量列的 DataFrame。

If your compute hardware contains multiple CPUs, the largest performance gain can be realized by setting parallel to True to leverage more than 1 CPU. Internally, pandas leverages numba to parallelize computations over the columns of a DataFrame; therefore, this performance benefit is only beneficial for a DataFrame with a large number of columns.

In [1]: import numba

In [2]: numba.set_num_threads(1)

In [3]: df = pd.DataFrame(np.random.randn(10_000, 100))

In [4]: roll = df.rolling(100)

In [5]: %timeit roll.mean(engine="numba", engine_kwargs={"parallel": True})
347 ms ± 26 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

In [6]: numba.set_num_threads(2)

In [7]: %timeit roll.mean(engine="numba", engine_kwargs={"parallel": True})
201 ms ± 2.97 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

Custom Function Examples

可以通过使用 Series.to_numpy() 传递其 NumPy 数组表示形式，用 @jit 修饰的自定义 Python 函数与 pandas 对象配合使用。

A custom Python function decorated with @jit can be used with pandas objects by passing their NumPy array representations with Series.to_numpy().

import numba


@numba.jit
def f_plain(x):
    return x * (x - 1)


@numba.jit
def integrate_f_numba(a, b, N):
    s = 0
    dx = (b - a) / N
    for i in range(N):
        s += f_plain(a + i * dx)
    return s * dx


@numba.jit
def apply_integrate_f_numba(col_a, col_b, col_N):
    n = len(col_N)
    result = np.empty(n, dtype="float64")
    assert len(col_a) == len(col_b) == n
    for i in range(n):
        result[i] = integrate_f_numba(col_a[i], col_b[i], col_N[i])
    return result


def compute_numba(df):
    result = apply_integrate_f_numba(
        df["a"].to_numpy(), df["b"].to_numpy(), df["N"].to_numpy()
    )
    return pd.Series(result, index=df.index, name="result")
In [4]: %timeit compute_numba(df)
1000 loops, best of 3: 798 us per loop

在该示例中，使用 Numba 比使用 Cython 更快。

In this example, using Numba was faster than Cython.

Numba 还可以用来编写不需求用户显式循环遍历向量的观测值的矢量化函数；矢量化函数将自动应用于每一行。考虑以下将每个观测值加倍的示例：

Numba can also be used to write vectorized functions that do not require the user to explicitly loop over the observations of a vector; a vectorized function will be applied to each row automatically. Consider the following example of doubling each observation:

import numba


def double_every_value_nonumba(x):
    return x * 2


@numba.vectorize
def double_every_value_withnumba(x):  # noqa E501
    return x * 2
# Custom function without numba
In [5]: %timeit df["col1_doubled"] = df["a"].apply(double_every_value_nonumba)  # noqa E501
1000 loops, best of 3: 797 us per loop

# Standard implementation (faster than a custom function)
In [6]: %timeit df["col1_doubled"] = df["a"] * 2
1000 loops, best of 3: 233 us per loop

# Custom function with numba
In [7]: %timeit df["col1_doubled"] = double_every_value_withnumba(df["a"].to_numpy())
1000 loops, best of 3: 145 us per loop

Caveats

Numba 最擅长将数值函数应用于 NumPy 数组的加速函数。如果您尝试 @jit 一个包含不受支持的 Python 或 NumPy 代码的函数，则编译将会 object mode，这很可能不会加速您的函数。如果您希望 Numba 在无法以加速代码的方式编译函数时抛出错误，请向 Numba 传递参数 nopython=True（例如 @jit(nopython=True)）。有关 Numba 模式故障排除的详细信息，请参见 Numba troubleshooting page。

Numba is best at accelerating functions that apply numerical functions to NumPy arrays. If you try to @jit a function that contains unsupported Python or NumPy code, compilation will revert object mode which will mostly likely not speed up your function. If you would prefer that Numba throw an error if it cannot compile a function in a way that speeds up your code, pass Numba the argument nopython=True (e.g. @jit(nopython=True)). For more on troubleshooting Numba modes, see the Numba troubleshooting page.

使用 parallel=True（例如 @jit(parallel=True)）可能会导致 SIGABRT，如果线程层导致不安全的行为。您可以在使用 parallel=True 运行 JIT 函数前首先 specify a safe threading layer。

Using parallel=True (e.g. @jit(parallel=True)) may result in a SIGABRT if the threading layer leads to unsafe behavior. You can first specify a safe threading layer before running a JIT function with parallel=True.

一般而言，如果您在使用 Numba 时遇到段错误 (SIGSEGV），请向 Numba issue tracker. 报告该问题。

Generally if the you encounter a segfault (SIGSEGV) while using Numba, please report the issue to the Numba issue tracker.

Expression evaluation via eval()

顶层函数 pandas.eval() 执行 Series 和 DataFrame 的高效表达式评估。表达式评估允许将运算表示为字符串，并且可以通过一次评估对大量 DataFrame 的算术和布尔表达式来潜在提供性能改进。

The top-level function pandas.eval() implements performant expression evaluation of Series and DataFrame. Expression evaluation allows operations to be expressed as strings and can potentially provide a performance improvement by evaluate arithmetic and boolean expression all at once for large DataFrame.

您不应将 eval() 用于简单表达式或涉及小 DataFrame 的表达式。事实上，与纯 Python 相比， eval() 在较小的表达式或对象上慢很多个数量级。一个好的经验法则是仅在您使用 DataFrame 时才使用 eval()，该 DataFrame 具有 10,000 行以上。

You should not use eval() for simple expressions or for expressions involving small DataFrames. In fact, eval() is many orders of magnitude slower for smaller expressions or objects than plain Python. A good rule of thumb is to only use eval() when you have a DataFrame with more than 10,000 rows.

Supported syntax

以下操作由 pandas.eval() 支持：

These operations are supported by pandas.eval():

  1. Arithmetic operations except for the left shift (<<) and right shift (>>) operators, e.g., df + 2 * pi / s ** 4 % 42 - the_golden_ratio

  2. Comparison operations, including chained comparisons, e.g., 2 < df < df2

  3. Boolean operations, e.g., df < df2 and df3 < df4 or not df_bool

  4. list and tuple literals, e.g., [1, 2] or (1, 2)

  5. Attribute access, e.g., df.a

  6. Subscript expressions, e.g., df[0]

  7. Simple variable evaluation, e.g., pd.eval("df") (this is not very useful)

  8. Math functions: sin, cos, exp, log, expm1, log1p, sqrt, sinh, cosh, tanh, arcsin, arccos, arctan, arccosh, arcsinh, arctanh, abs, arctan2 and log10.

不允许以下 Python 语法：

The following Python syntax is not allowed:

  1. Expressions

  2. Function calls other than math functions.

  3. is/is not operations

  4. if expressions

  5. lambda expressions

  6. list/set/dict comprehensions

  7. Literal dict and set expressions

  8. yield expressions

  9. Generator expressions

  10. Boolean expressions consisting of only scalar values

  11. Statements

  12. Neither simple or compound statements are allowed. This includes for, while, and if.

Local variables

必须通过在名称前放置 @ 字符，显式引用要用于表达式的任何局部变量。该机制对 DataFrame.query() 和 DataFrame.eval() 相同。例如，

You must explicitly reference any local variable that you want to use in an expression by placing the @ character in front of the name. This mechanism is the same for both DataFrame.query() and DataFrame.eval(). For example,

In [18]: df = pd.DataFrame(np.random.randn(5, 2), columns=list("ab"))

In [19]: newcol = np.random.randn(len(df))

In [20]: df.eval("b + @newcol")
Out[20]:
0   -0.206122
1   -1.029587
2    0.519726
3   -2.052589
4    1.453210
dtype: float64

In [21]: df.query("b < @newcol")
Out[21]:
          a         b
1  0.160268 -0.848896
3  0.333758 -1.180355
4  0.572182  0.439895

如果不使用 @ 前缀局部变量，pandas 会引发异常，提示你该变量未定义。

If you don’t prefix the local variable with @, pandas will raise an exception telling you the variable is undefined.

使用 DataFrame.eval() 和 DataFrame.query() 时，这允许你在一个表达式中拥有局部变量和具有相同名称的 DataFrame 列。

When using DataFrame.eval() and DataFrame.query(), this allows you to have a local variable and a DataFrame column with the same name in an expression.

In [22]: a = np.random.randn()

In [23]: df.query("@a < a")
Out[23]:
          a         b
0  0.473349  0.891236
1  0.160268 -0.848896
2  0.803311  1.662031
3  0.333758 -1.180355
4  0.572182  0.439895

In [24]: df.loc[a < df["a"]]  # same as the previous expression
Out[24]:
          a         b
0  0.473349  0.891236
1  0.160268 -0.848896
2  0.803311  1.662031
3  0.333758 -1.180355
4  0.572182  0.439895

警告

Warning

如果你不能使用 @ 前缀，因为该前缀在那上下文中未定义， pandas.eval() 将引发异常。

pandas.eval() will raise an exception if you cannot use the @ prefix because it isn’t defined in that context.

In [25]: a, b = 1, 2

In [26]: pd.eval("@a + b")
Traceback (most recent call last):

  File ~/micromamba/envs/test/lib/python3.10/site-packages/IPython/core/interactiveshell.py:3577 in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)

  Cell In[26], line 1
    pd.eval("@a + b")

  File ~/work/pandas/pandas/pandas/core/computation/eval.py:325 in eval
    _check_for_locals(expr, level, parser)

  File ~/work/pandas/pandas/pandas/core/computation/eval.py:167 in _check_for_locals
    raise SyntaxError(msg)

  File <string>
SyntaxError: The '@' prefix is not allowed in top-level eval calls.
please refer to your variables by name without the '@' prefix.

在这种情况下，你应该像在标准 Python 中一样引用变量。

In this case, you should simply refer to the variables like you would in standard Python.

In [27]: pd.eval("a + b")
Out[27]: 3

pandas.eval() parsers

有两个不同的表达式语法解析器。

There are two different expression syntax parsers.

默认 'pandas' 解析器允许更直观的语法，用于表达类似于查询的操作（比较、连接和析取）。特别是，& 和 | 运算符的优先级等于相应布尔操作 and 和 or 的优先级。

The default 'pandas' parser allows a more intuitive syntax for expressing query-like operations (comparisons, conjunctions and disjunctions). In particular, the precedence of the & and | operators is made equal to the precedence of the corresponding boolean operations and and or.

例如，以上连接可以不带括号写入。另外，你可以使用 'python' 解析器强制执行严格的 Python 语义。

For example, the above conjunction can be written without parentheses. Alternatively, you can use the 'python' parser to enforce strict Python semantics.

In [28]: nrows, ncols = 20000, 100

In [29]: df1, df2, df3, df4 = [pd.DataFrame(np.random.randn(nrows, ncols)) for _ in range(4)]

In [30]: expr = "(df1 > 0) & (df2 > 0) & (df3 > 0) & (df4 > 0)"

In [31]: x = pd.eval(expr, parser="python")

In [32]: expr_no_parens = "df1 > 0 & df2 > 0 & df3 > 0 & df4 > 0"

In [33]: y = pd.eval(expr_no_parens, parser="pandas")

In [34]: np.all(x == y)
Out[34]: True

同一个表达式可以用单词 and “并且”连接：

The same expression can be “anded” together with the word and as well:

In [35]: expr = "(df1 > 0) & (df2 > 0) & (df3 > 0) & (df4 > 0)"

In [36]: x = pd.eval(expr, parser="python")

In [37]: expr_with_ands = "df1 > 0 and df2 > 0 and df3 > 0 and df4 > 0"

In [38]: y = pd.eval(expr_with_ands, parser="pandas")

In [39]: np.all(x == y)
Out[39]: True

这里的 and 和 or 运算符具有与在 Python 中相同的优先级。

The and and or operators here have the same precedence that they would in Python.

pandas.eval() engines

有两个不同的表达式引擎。

There are two different expression engines.

'numexpr' 引擎是性能更好的引擎，与针对大型 DataFrame 的标准 Python 语法相比，它可以带来性能改进。此引擎要求安装可选依赖项 numexpr。

The 'numexpr' engine is the more performant engine that can yield performance improvements compared to standard Python syntax for large DataFrame. This engine requires the optional dependency numexpr to be installed.

'python' 引擎通常无用，除非用来对照它测试其他求值引擎。将 eval() 与 engine='python' 一起使用不会带来任何性能好处，反而可能造成性能下降。

The 'python' engine is generally not useful except for testing other evaluation engines against it. You will achieve no performance benefits using eval() with engine='python' and may incur a performance hit.

In [40]: %timeit df1 + df2 + df3 + df4
7.42 ms +- 81.8 us per loop (mean +- std. dev. of 7 runs, 100 loops each)
In [41]: %timeit pd.eval("df1 + df2 + df3 + df4", engine="python")
8.11 ms +- 161 us per loop (mean +- std. dev. of 7 runs, 100 loops each)

The DataFrame.eval() method

除了顶级 pandas.eval() 函数之外，你还可以评估 DataFrame 中的表达式。

In addition to the top level pandas.eval() function you can also evaluate an expression in the “context” of a DataFrame.

In [42]: df = pd.DataFrame(np.random.randn(5, 2), columns=["a", "b"])

In [43]: df.eval("a + b")
Out[43]:
0   -0.161099
1    0.805452
2    0.747447
3    1.189042
4   -2.057490
dtype: float64

任何有效的 pandas.eval() 表达式也是有效的 DataFrame.eval() 表达式，此外还有一个优点，即你无需在要评估的列之前添加 DataFrame 的名称。

Any expression that is a valid pandas.eval() expression is also a valid DataFrame.eval() expression, with the added benefit that you don’t have to prefix the name of the DataFrame to the column(s) you’re interested in evaluating.

此外，你可以在表达式中执行列赋值。这允许进行公式评估。赋值目标可以是新列名或现有列名，它还必须是有效的 Python 标识符。

In addition, you can perform assignment of columns within an expression. This allows for formulaic evaluation. The assignment target can be a new column name or an existing column name, and it must be a valid Python identifier.

In [44]: df = pd.DataFrame(dict(a=range(5), b=range(5, 10)))

In [45]: df = df.eval("c = a + b")

In [46]: df = df.eval("d = a + b + c")

In [47]: df = df.eval("a = 1")

In [48]: df
Out[48]:
   a  b   c   d
0  1  5   5  10
1  1  6   7  14
2  1  7   9  18
3  1  8  11  22
4  1  9  13  26

返回带新列或修改列的 DataFrame 的副本，并且原始框架保持不变。

A copy of the DataFrame with the new or modified columns is returned, and the original frame is unchanged.

In [49]: df
Out[49]:
   a  b   c   d
0  1  5   5  10
1  1  6   7  14
2  1  7   9  18
3  1  8  11  22
4  1  9  13  26

In [50]: df.eval("e = a - c")
Out[50]:
   a  b   c   d   e
0  1  5   5  10  -4
1  1  6   7  14  -6
2  1  7   9  18  -8
3  1  8  11  22 -10
4  1  9  13  26 -12

In [51]: df
Out[51]:
   a  b   c   d
0  1  5   5  10
1  1  6   7  14
2  1  7   9  18
3  1  8  11  22
4  1  9  13  26

可以通过使用多行字符串对多列赋值进行执行。

Multiple column assignments can be performed by using a multi-line string.

In [52]: df.eval(
   ....:     """
   ....: c = a + b
   ....: d = a + b + c
   ....: a = 1""",
   ....: )
   ....:
Out[52]:
   a  b   c   d
0  1  5   6  12
1  1  6   7  14
2  1  7   8  16
3  1  8   9  18
4  1  9  10  20

标准 Python 中的等效项如下：

The equivalent in standard Python would be

In [53]: df = pd.DataFrame(dict(a=range(5), b=range(5, 10)))

In [54]: df["c"] = df["a"] + df["b"]

In [55]: df["d"] = df["a"] + df["b"] + df["c"]

In [56]: df["a"] = 1

In [57]: df
Out[57]:
   a  b   c   d
0  1  5   5  10
1  1  6   7  14
2  1  7   9  18
3  1  8  11  22
4  1  9  13  26

eval() performance comparison

pandas.eval() 与包含大型数组的表达式协作良好。

pandas.eval() works well with expressions containing large arrays.

In [58]: nrows, ncols = 20000, 100

In [59]: df1, df2, df3, df4 = [pd.DataFrame(np.random.randn(nrows, ncols)) for _ in range(4)]

DataFrame 算术：

DataFrame arithmetic:

In [60]: %timeit df1 + df2 + df3 + df4
7.34 ms +- 117 us per loop (mean +- std. dev. of 7 runs, 100 loops each)
In [61]: %timeit pd.eval("df1 + df2 + df3 + df4")
2.85 ms +- 58.8 us per loop (mean +- std. dev. of 7 runs, 100 loops each)

DataFrame 比较：

DataFrame comparison:

In [62]: %timeit (df1 > 0) & (df2 > 0) & (df3 > 0) & (df4 > 0)
5.98 ms +- 37 us per loop (mean +- std. dev. of 7 runs, 100 loops each)
In [63]: %timeit pd.eval("(df1 > 0) & (df2 > 0) & (df3 > 0) & (df4 > 0)")
9.38 ms +- 36.7 us per loop (mean +- std. dev. of 7 runs, 100 loops each)

DataFrame 具有不对齐轴的算术。

DataFrame arithmetic with unaligned axes.

In [64]: s = pd.Series(np.random.randn(50))

In [65]: %timeit df1 + df2 + df3 + df4 + s
12.6 ms +- 105 us per loop (mean +- std. dev. of 7 runs, 100 loops each)
In [66]: %timeit pd.eval("df1 + df2 + df3 + df4 + s")
3.69 ms +- 62 us per loop (mean +- std. dev. of 7 runs, 100 loops each)

诸如

Operations such as

 
1 and 2  # would parse to 1 & 2, but should evaluate to 2
3 or 4  # would parse to 3 | 4, but should evaluate to 3
~1  # this is okay, but slower when using eval

这样的操作应该在 Python 中执行。如果你尝试对不是 bool 或 np.bool_ 类型的标量操作数执行任何布尔/按位运算，将引发异常。

should be performed in Python. An exception will be raised if you try to perform any boolean/bitwise operations with scalar operands that are not of type bool or np.bool_.

这是一张展示了 pandas.eval() 的运行时间的图表，该运行时间在于计算中所涉及的帧的大小。这两条线是两个不同的引擎。

Here is a plot showing the running time of pandas.eval() as function of the size of the frame involved in the computation. The two lines are two different engines.

只有当你的 DataFrame 拥有超过约 100,000 行时，你才能看到使用 numexpr 引擎的性能优势。

You will only see the performance benefits of using the numexpr engine with pandas.eval() if your DataFrame has more than approximately 100,000 rows.

此图表是使用具有 3 列的 DataFrame 创建的，每列都包含使用 numpy.random.randn() 生成的浮点值。

This plot was created using a DataFrame with 3 columns each containing floating point values generated using numpy.random.randn().

Expression evaluation limitations with numexpr

会因为 NaT 而导致对象数据类型或涉及 datetime 操作的表达式必须在 Python 空间中得到求值，但表达式的部分内容仍可以使用 numexpr 求值。例如：

Expressions that would result in an object dtype or involve datetime operations because of NaT must be evaluated in Python space, but part of an expression can still be evaluated with numexpr. For example:

In [67]: df = pd.DataFrame(
   ....:     {"strings": np.repeat(list("cba"), 3), "nums": np.repeat(range(3), 3)}
   ....: )
   ....:

In [68]: df
Out[68]:
  strings  nums
0       c     0
1       c     0
2       c     0
3       b     1
4       b     1
5       b     1
6       a     2
7       a     2
8       a     2

In [69]: df.query("strings == 'a' and nums == 1")
Out[69]:
Empty DataFrame
Columns: [strings, nums]
Index: []

比较数字部分（nums == 1）将由 numexpr 求值，比较对象部分（"strings == 'a'）将由 Python 求值。

The numeric part of the comparison (nums == 1) will be evaluated by numexpr and the object part of the comparison ("strings == 'a') will be evaluated by Python.