Enhancing performance

Enhancing performance

In this part of the tutorial, we will investigate how to speed up certainfunctions operating on pandas DataFrames using three different techniques:Cython, Numba and pandas.eval(). We will see a speed improvement of ~200when we use Cython and Numba on a test function operating row-wise on theDataFrame. Using pandas.eval() we will speed up a sum by an order of~2.

Cython (writing C extensions for pandas)

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

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

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

Pure Python

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]

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
   ...:

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

In [7]: %timeit df.apply(lambda x: integrate_f(x['a'], x['b'], x['N']), axis=1)
10 loops, best of 3: 174 ms per loop

But clearly this isn’t fast enough for us. Let’s take a look and see where thetime is spent during this operation (limited to the most time consumingfour calls) using the prun ipython magic function:

In [5]: %prun -l 4 df.apply(lambda x: integrate_f(x['a'], x['b'], x['N']), axis=1)  # noqa E999
         672332 function calls (667306 primitive calls) in 0.279 seconds
 
   Ordered by: internal time
   List reduced from 221 to 4 due to restriction <4>
 
   ncalls  tottime  percall  cumtime  percall filename:lineno(function)
     1000    0.144    0.000    0.214    0.000 <ipython-input-4-c2a74e076cf0>:1(integrate_f)
   552423    0.070    0.000    0.070    0.000 <ipython-input-3-c138bdd570e3>:1(f)
     3000    0.009    0.000    0.043    0.000 base.py:4702(get_value)
     3000    0.005    0.000    0.050    0.000 series.py:1068(__getitem__)

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

Note

In Python 2 replacing the range with its generator counterpart (xrange)would mean the range line would vanish. In Python 3 range is already a generator.

Plain Cython

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

In [6]: %load_ext Cython

Now, let’s simply copy our functions over to Cython as is (the suffixis here to distinguish between function versions):

In [7]: %%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
   ...:

Note

If you’re having trouble pasting the above into your ipython, you may needto be using bleeding edge ipython for paste to play well with cell magics.

In [4]: %timeit df.apply(lambda x: integrate_f_plain(x['a'], x['b'], x['N']), axis=1)
10 loops, best of 3: 85.5 ms per loop

Already this has shaved a third off, not too bad for a simple copy and paste.

Adding type

We get another huge improvement simply by providing type information:

In [8]: %%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 [4]: %timeit df.apply(lambda x: integrate_f_typed(x['a'], x['b'], x['N']), axis=1)
10 loops, best of 3: 20.3 ms per loop

Now, we’re talking! It’s now over ten times faster than the original pythonimplementation, and we haven’t really modified the code. Let’s have anotherlook at what’s eating up time:

In [9]: %prun -l 4 df.apply(lambda x: integrate_f_typed(x['a'], x['b'], x['N']), axis=1)
         119905 function calls (114879 primitive calls) in 0.064 seconds
 
   Ordered by: internal time
   List reduced from 216 to 4 due to restriction <4>
 
   ncalls  tottime  percall  cumtime  percall filename:lineno(function)
     3000    0.008    0.000    0.042    0.000 base.py:4702(get_value)
     3000    0.005    0.000    0.048    0.000 series.py:1068(__getitem__)
     6001    0.005    0.000    0.012    0.000 {pandas._libs.lib.values_from_object}
     3000    0.004    0.000    0.004    0.000 {method 'get_value' of 'pandas._libs.index.IndexEngine' objects}

Using ndarray

It’s calling series… a lot! It’s creating a Series from each row, and get-ting from boththe index and the series (three times for each row). Function calls are expensivein Python, so maybe we could minimize these by cythonizing the apply part.

Note

We are now passing ndarrays into the Cython function, fortunately Cython playsvery nicely with NumPy.

In [10]: %%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.float
   ....:             and col_b.dtype == np.float and col_N.dtype == np.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
   ....:

The implementation is simple, it creates an array of zeros and loops overthe rows, applying our integrate_f_typed, and putting this in the zeros array.

Warning

You can not pass a Series directly as a ndarray typed parameterto a Cython function. Instead pass the actual ndarray using theSeries.to_numpy(). The reason is that the Cythondefinition is specific to an ndarray and not the passed Series.

So, do not do this:

apply_integrate_f(df['a'], df['b'], df['N'])

But rather, use Series.to_numpy() to get the underlying ndarray:

apply_integrate_f(df['a'].to_numpy(),
                  df['b'].to_numpy(),
                  df['N'].to_numpy())

Note

Loops like this would be extremely slow in Python, but in Cython loopingover NumPy arrays is fast.

In [4]: %timeit apply_integrate_f(df['a'].to_numpy(),
                                  df['b'].to_numpy(),
                                  df['N'].to_numpy())
1000 loops, best of 3: 1.25 ms per loop

We’ve gotten another big improvement. Let’s check again where the time is spent:

In [11]: %%prun -l 4 apply_integrate_f(df['a'].to_numpy(),
   ....:                               df['b'].to_numpy(),
   ....:                               df['N'].to_numpy())
   ....: 
         269 function calls in 0.002 seconds
 
   Ordered by: internal time
   List reduced from 64 to 4 due to restriction <4>
 
   ncalls  tottime  percall  cumtime  percall filename:lineno(function)
        1    0.001    0.001    0.001    0.001 {built-in method _cython_magic_9f00aa39789b3d75a0663c577df7094e.apply_integrate_f}
        3    0.000    0.000    0.000    0.000 frame.py:2964(__getitem__)
        3    0.000    0.000    0.000    0.000 managers.py:971(iget)
        1    0.000    0.000    0.002    0.002 {built-in method builtins.exec}

As one might expect, the majority of the time is now spent in apply_integrate_f,so if we wanted to make anymore efficiencies we must continue to concentrate ourefforts here.

More advanced techniques

There is still hope for improvement. Here’s an example of using some moreadvanced Cython techniques:

In [12]: %%cython
   ....: cimport 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
   ....: @cython.boundscheck(False)
   ....: @cython.wraparound(False)
   ....: cpdef np.ndarray[double] apply_integrate_f_wrap(np.ndarray[double] col_a,
   ....:                                                 np.ndarray[double] col_b,
   ....:                                                 np.ndarray[int] col_N):
   ....:     cdef int 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(n):
   ....:         res[i] = integrate_f_typed(col_a[i], col_b[i], col_N[i])
   ....:     return res
   ....:

In [4]: %timeit apply_integrate_f_wrap(df['a'].to_numpy(),
                                       df['b'].to_numpy(),
                                       df['N'].to_numpy())
1000 loops, best of 3: 987 us per loop

Even faster, with the caveat that a bug in our Cython code (an off-by-one error,for example) might cause a segfault because memory access isn’t checked.For more about boundscheck and wraparound, see the Cython docs oncompiler directives.

Using Numba

A recent alternative to statically compiling Cython code, is to use a dynamic jit-compiler, Numba.

Numba gives you the power to speed up your applications with high performance functions written directly in Python. With a few annotations, array-oriented and math-heavy Python code can be just-in-time compiled to native machine instructions, similar in performance to C, C++ and Fortran, without having to switch languages or Python interpreters.

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.

Note

You will need to install Numba. This is easy with conda, by using: conda install numba, see installing using miniconda.

Note

As of Numba version 0.20, pandas objects cannot be passed directly to Numba-compiled functions. Instead, one must pass the NumPy array underlying the pandas object to the Numba-compiled function as demonstrated below.

Jit

We demonstrate how to use Numba to just-in-time compile our code. We simplytake the plain Python code from above and annotate with the @jit decorator.

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')

Note that we directly pass NumPy arrays to the Numba function. compute_numba is just a wrapper that provides anicer interface by passing/returning pandas objects.

In [4]: %timeit compute_numba(df)
1000 loops, best of 3: 798 us per loop

In this example, using Numba was faster than Cython.

Vectorize

Numba can also be used to write vectorized functions that do not require the user to explicitlyloop over the observations of a vector; a vectorized function will be applied to each row automatically.Consider the following toy 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

Note

Numba will execute on any function, but can only accelerate certain classes of functions.

Numba is best at accelerating functions that apply numerical functions to NumPyarrays. When passed a function that only uses operations it knows how toaccelerate, it will execute in nopython mode.

If Numba is passed a function that includes something it doesn’t know how towork with – a category that currently includes sets, lists, dictionaries, orstring functions – it will revert to object mode. In object mode,Numba will execute but your code will not speed up significantly. If you wouldprefer that Numba throw an error if it cannot compile a function in a way thatspeeds up your code, pass Numba the argumentnopython=True (e.g. @numba.jit(nopython=True)). For more ontroubleshooting Numba modes, see the Numba troubleshooting page.

Expression evaluation via eval()

The top-level function pandas.eval() implements expression evaluation ofSeries and DataFrame objects.

Note

To benefit from using eval() you need toinstall numexpr. See the recommended dependencies section for more details.

The point of using eval() for expression evaluation rather thanplain Python is two-fold: 1) large DataFrame objects areevaluated more efficiently and 2) large arithmetic and boolean expressions areevaluated all at once by the underlying engine (by default numexpr is usedfor evaluation).

Note

You should not use eval() for simpleexpressions or for expressions involving small DataFrames. In fact,eval() is many orders of magnitude slower forsmaller expressions/objects than plain ol’ Python. A good rule of thumb isto only use eval() when you have aDataFrame with more than 10,000 rows.

eval() supports all arithmetic expressions supported by theengine in addition to some extensions available only in pandas.

Note

The larger the frame and the larger the expression the more speedup you willsee from using eval().

Supported syntax

These operations are supported by pandas.eval():

Arithmetic operations except for the left shift (<<) and right shift(>>) operators, e.g., df + 2 pi / s * 4 % 42 - the_golden_ratio
Comparison operations, including chained comparisons, e.g., 2 < df < df2
Boolean operations, e.g., df < df2 and df3 < df4 or not df_bool
list and tuple literals, e.g., [1, 2] or (1, 2)
Attribute access, e.g., df.a
Subscript expressions, e.g., df[0]
Simple variable evaluation, e.g., pd.eval('df') (this is not very useful)
Math functions: sin, cos, exp, log, expm1, log1p,sqrt, sinh, cosh, tanh, arcsin, arccos, arctan, arccosh,arcsinh, arctanh, abs, arctan2 and log10.

This Python syntax is not allowed:

Expressions

Function calls other than math functions.
is/is not operations
if expressions
lambda expressions
list/set/dict comprehensions
Literal dict and set expressions
yield expressions
Generator expressions
Boolean expressions consisting of only scalar values

Statements

Neither simplenor compoundstatements are allowed. This includes things like for, while, andif.

eval() examples

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

First let’s create a few decent-sized arrays to play with:

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

Now let’s compare adding them together using plain ol’ Python versuseval():

In [15]: %timeit df1 + df2 + df3 + df4
13 ms +- 1.02 ms per loop (mean +- std. dev. of 7 runs, 100 loops each)

In [16]: %timeit pd.eval('df1 + df2 + df3 + df4')
9.8 ms +- 573 us per loop (mean +- std. dev. of 7 runs, 100 loops each)

Now let’s do the same thing but with comparisons:

In [17]: %timeit (df1 > 0) & (df2 > 0) & (df3 > 0) & (df4 > 0)
219 ms +- 25.8 ms per loop (mean +- std. dev. of 7 runs, 10 loops each)

In [18]: %timeit pd.eval('(df1 > 0) & (df2 > 0) & (df3 > 0) & (df4 > 0)')
22.2 ms +- 2.36 ms per loop (mean +- std. dev. of 7 runs, 10 loops each)

eval() also works with unaligned pandas objects:

In [19]: s = pd.Series(np.random.randn(50))
 
In [20]: %timeit df1 + df2 + df3 + df4 + s
89.5 ms +- 3.73 ms per loop (mean +- std. dev. of 7 runs, 10 loops each)

In [21]: %timeit pd.eval('df1 + df2 + df3 + df4 + s')
18.2 ms +- 2.79 ms per loop (mean +- std. dev. of 7 runs, 100 loops each)

Note

Operations such as

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

should be performed in Python. An exception will be raised if you try toperform any boolean/bitwise operations with scalar operands that are notof type bool or np.bool_. Again, you should perform these kinds ofoperations in plain Python.

The DataFrame.eval method

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

In [22]: df = pd.DataFrame(np.random.randn(5, 2), columns=['a', 'b'])
 
In [23]: df.eval('a + b')
Out[23]: 
0   -0.246747
1    0.867786
2   -1.626063
3   -1.134978
4   -1.027798
dtype: float64

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

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

New in version 0.18.0.

The inplace keyword determines whether this assignment will performedon the original DataFrame or return a copy with the new column.

Warning

For backwards compatibility, inplace defaults to True if notspecified. This will change in a future version of pandas - if yourcode depends on an inplace assignment you should update to explicitlyset inplace=True.

In [24]: df = pd.DataFrame(dict(a=range(5), b=range(5, 10)))
 
In [25]: df.eval('c = a + b', inplace=True)
 
In [26]: df.eval('d = a + b + c', inplace=True)
 
In [27]: df.eval('a = 1', inplace=True)
 
In [28]: df
Out[28]: 
   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

When inplace is set to False, a copy of the DataFrame with thenew or modified columns is returned and the original frame is unchanged.

In [29]: df
Out[29]: 
   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 [30]: df.eval('e = a - c', inplace=False)
Out[30]: 
   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 [31]: df
Out[31]: 
   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

New in version 0.18.0.

As a convenience, multiple assignments can be performed by using amulti-line string.

In [32]: df.eval("""
   ....: c = a + b
   ....: d = a + b + c
   ....: a = 1""", inplace=False)
   ....: 
Out[32]: 
   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

The equivalent in standard Python would be

In [33]: df = pd.DataFrame(dict(a=range(5), b=range(5, 10)))
 
In [34]: df['c'] = df.a + df.b
 
In [35]: df['d'] = df.a + df.b + df.c
 
In [36]: df['a'] = 1
 
In [37]: df
Out[37]: 
   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

New in version 0.18.0.

The query method gained the inplace keyword which determineswhether the query modifies the original frame.

In [38]: df = pd.DataFrame(dict(a=range(5), b=range(5, 10)))
 
In [39]: df.query('a > 2')
Out[39]: 
   a  b
3  3  8
4  4  9
 
In [40]: df.query('a > 2', inplace=True)
 
In [41]: df
Out[41]: 
   a  b
3  3  8
4  4  9

Warning

Unlike with eval, the default value for inplace for queryis False. This is consistent with prior versions of pandas.

Local variables

You must explicitly reference any local variable that you want to use in anexpression by placing the @ character in front of the name. For example,

In [42]: df = pd.DataFrame(np.random.randn(5, 2), columns=list('ab'))
 
In [43]: newcol = np.random.randn(len(df))
 
In [44]: df.eval('b + @newcol')
Out[44]: 
0   -0.173926
1    2.493083
2   -0.881831
3   -0.691045
4    1.334703
dtype: float64
 
In [45]: df.query('b < @newcol')
Out[45]: 
          a         b
0  0.863987 -0.115998
2 -2.621419 -1.297879

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

When using DataFrame.eval() and DataFrame.query(), this allows youto have a local variable and a DataFrame column with the samename in an expression.

In [46]: a = np.random.randn()
 
In [47]: df.query('@a < a')
Out[47]: 
          a         b
0  0.863987 -0.115998
 
In [48]: df.loc[a < df.a]  # same as the previous expression
Out[48]: 
          a         b
0  0.863987 -0.115998

With pandas.eval() you cannot use the @ prefix at all, because itisn’t defined in that context. pandas will let you know this if you try touse @ in a top-level call to pandas.eval(). For example,

In [49]: a, b = 1, 2
 
In [50]: pd.eval('@a + b')
Traceback (most recent call last):
 
  File "/opt/conda/envs/pandas/lib/python3.7/site-packages/IPython/core/interactiveshell.py", line 3326, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
 
  File "<ipython-input-50-af17947a194f>", line 1, in <module>
    pd.eval('@a + b')
 
  File "/pandas/pandas/core/computation/eval.py", line 311, in eval
    _check_for_locals(expr, level, parser)
 
  File "/pandas/pandas/core/computation/eval.py", line 166, in _check_for_locals
    raise SyntaxError(msg)
 
  File "<string>", line unknown
SyntaxError: The '@' prefix is not allowed in top-level eval calls, 
please refer to your variables by name without the '@' prefix

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

In [51]: pd.eval('a + b')
Out[51]: 3

pandas.eval() parsers

There are two different parsers and two different engines you can use asthe backend.

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

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

In [52]: expr = '(df1 > 0) & (df2 > 0) & (df3 > 0) & (df4 > 0)'
 
In [53]: x = pd.eval(expr, parser='python')
 
In [54]: expr_no_parens = 'df1 > 0 & df2 > 0 & df3 > 0 & df4 > 0'
 
In [55]: y = pd.eval(expr_no_parens, parser='pandas')
 
In [56]: np.all(x == y)
Out[56]: True

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

In [57]: expr = '(df1 > 0) & (df2 > 0) & (df3 > 0) & (df4 > 0)'
 
In [58]: x = pd.eval(expr, parser='python')
 
In [59]: expr_with_ands = 'df1 > 0 and df2 > 0 and df3 > 0 and df4 > 0'
 
In [60]: y = pd.eval(expr_with_ands, parser='pandas')
 
In [61]: np.all(x == y)
Out[61]: True

The and and or operators here have the same precedence that they wouldin vanilla Python.

pandas.eval() backends

There’s also the option to make eval() operate identical to plainol’ Python.

Note

Using the 'python' engine is generally not useful, except for testingother evaluation engines against it. You will achieve no performancebenefits using eval() with engine='python' and in fact mayincur a performance hit.

You can see this by using pandas.eval() with the 'python' engine. Itis a bit slower (not by much) than evaluating the same expression in Python

In [62]: %timeit df1 + df2 + df3 + df4
17.6 ms +- 565 us per loop (mean +- std. dev. of 7 runs, 100 loops each)

In [63]: %timeit pd.eval('df1 + df2 + df3 + df4', engine='python')
19 ms +- 334 us per loop (mean +- std. dev. of 7 runs, 100 loops each)

pandas.eval() performance

eval() is intended to speed up certain kinds of operations. Inparticular, those operations involving complex expressions with largeDataFrame/Series objects should see asignificant performance benefit. Here is a plot showing the running time ofpandas.eval() as function of the size of the frame involved in thecomputation. The two lines are two different engines.

Note

Operations with smallish objects (around 15k-20k rows) are faster usingplain Python:

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

Technical minutia regarding expression evaluation

Expressions that would result in an object dtype or involve datetime operations(because of NaT) must be evaluated in Python space. The main reason forthis behavior is to maintain backwards compatibility with versions of NumPy <1.7. In those versions of NumPy a call to ndarray.astype(str) willtruncate any strings that are more than 60 characters in length. Second, wecan’t pass object arrays to numexpr thus string comparisons must beevaluated in Python space.

The upshot is that this only applies to object-dtype expressions. So, ifyou have an expression–for example

In [64]: df = pd.DataFrame({'strings': np.repeat(list('cba'), 3),
   ....:                    'nums': np.repeat(range(3), 3)})
   ....: 
 
In [65]: df
Out[65]: 
  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 [66]: df.query('strings == "a" and nums == 1')
Out[66]: 
Empty DataFrame
Columns: [strings, nums]
Index: []

the numeric part of the comparison (nums == 1) will be evaluated bynumexpr.

In general, DataFrame.query()/pandas.eval() willevaluate the subexpressions that can be evaluated by numexpr and thosethat must be evaluated in Python space transparently to the user. This is doneby inferring the result type of an expression from its arguments and operators.