7.3 Expression Evaluation via eval() (Experimental)

7.3.1 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 and arctan2.

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 simple nor compound statements are allowed. This includes things like for, while, and if.

7.3.2 eval() Examples

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

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

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

In [2]: 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 versus eval():

In [3]: %timeit df1 + df2 + df3 + df4
100 loops, best of 3: 15.6 ms per loop

In [4]: %timeit pd.eval('df1 + df2 + df3 + df4')
100 loops, best of 3: 8.33 ms per loop

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

In [5]: %timeit (df1 > 0) & (df2 > 0) & (df3 > 0) & (df4 > 0)
10 loops, best of 3: 26.2 ms per loop

In [6]: %timeit pd.eval('(df1 > 0) & (df2 > 0) & (df3 > 0) & (df4 > 0)')
100 loops, best of 3: 9.88 ms per loop

eval() also works with unaligned pandas objects:

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

In [8]: %timeit df1 + df2 + df3 + df4 + s
10 loops, best of 3: 24.2 ms per loop

In [9]: %timeit pd.eval('df1 + df2 + df3 + df4 + s')
100 loops, best of 3: 8.97 ms per loop

7.3.3 The DataFrame.eval method (Experimental)

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

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

In [11]: df.eval('a + b')
Out[11]: 
0   -0.370440
1   -2.054412
2    1.049276
3   -1.119499
4    2.146432
dtype: float64

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.

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.

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

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

In [13]: df.eval('c = a + b', inplace=True)

In [14]: df.eval('d = a + b + c', inplace=True)

In [15]: df.eval('a = 1', inplace=True)

In [16]: df
Out[16]: 
   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 the new or modified columns is returned and the original frame is unchanged.

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

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

In [20]: df.eval("""
   ....: c = a + b
   ....: d = a + b + c
   ....: a = 1""", inplace=False)
   ....: 
Out[20]: 
   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 [21]: df = pd.DataFrame(dict(a=range(5), b=range(5, 10)))

In [22]: df['c'] = df.a + df.b

In [23]: df['d'] = df.a + df.b + df.c

In [24]: df['a'] = 1

In [25]: df
Out[25]: 
   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

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

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

In [27]: df.query('a > 2')
Out[27]: 
   a  b
3  3  8
4  4  9

In [28]: df.query('a > 2', inplace=True)

In [29]: df
Out[29]: 
   a  b
3  3  8
4  4  9

7.3.4 Local Variables

In pandas version 0.14 the local variable API has changed. In pandas 0.13.x, you could refer to local variables the same way you would in standard Python. For example,

df = pd.DataFrame(np.random.randn(5, 2), columns=['a', 'b'])
newcol = np.random.randn(len(df))
df.eval('b + newcol')

UndefinedVariableError: name 'newcol' is not defined

As you can see from the exception generated, this syntax is no longer allowed. You must explicitly reference any local variable that you want to use in an expression by placing the @ character in front of the name. For example,

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

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

In [32]: df.eval('b + @newcol')
Out[32]: 
0    1.901131
1    1.623853
2    1.409689
3    0.003389
4   -1.711278
dtype: float64

In [33]: df.query('b < @newcol')
Out[33]: 
          a         b
0  1.992326  0.566227
2 -0.182539 -0.635867

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

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 [34]: a = np.random.randn()

In [35]: df.query('@a < a')
Out[35]: 
          a         b
0  1.992326  0.566227

In [36]: df.loc[a < df.a]  # same as the previous expression
Out[36]: 
          a         b
0  1.992326  0.566227

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

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

In [38]: pd.eval('@a + b')
  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 in standard Python.

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

7.3.5 pandas.eval() Parsers

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

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.

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

In [40]: expr = '(df1 > 0) & (df2 > 0) & (df3 > 0) & (df4 > 0)'

In [41]: x = pd.eval(expr, parser='python')

In [42]: expr_no_parens = 'df1 > 0 & df2 > 0 & df3 > 0 & df4 > 0'

In [43]: y = pd.eval(expr_no_parens, parser='pandas')

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

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

In [45]: expr = '(df1 > 0) & (df2 > 0) & (df3 > 0) & (df4 > 0)'

In [46]: x = pd.eval(expr, parser='python')

In [47]: expr_with_ands = 'df1 > 0 and df2 > 0 and df3 > 0 and df4 > 0'

In [48]: y = pd.eval(expr_with_ands, parser='pandas')

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

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

7.3.6 pandas.eval() Backends

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

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

In [50]: %timeit df1 + df2 + df3 + df4
100 loops, best of 3: 15.6 ms per loop

In [51]: %timeit pd.eval('df1 + df2 + df3 + df4', engine='python')
100 loops, best of 3: 16.6 ms per loop

7.3.7 pandas.eval() Performance

eval() is intended to speed up certain kinds of operations. In particular, those operations involving complex expressions with large DataFrame/Series objects should see a significant performance benefit. 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.

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

7.3.8 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 for this behavior is to maintain backwards compatibility with versions of numpy < 1.7. In those versions of numpy a call to ndarray.astype(str) will truncate any strings that are more than 60 characters in length. Second, we can’t pass object arrays to numexpr thus string comparisons must be evaluated in Python space.

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

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

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

[9 rows x 2 columns]

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

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

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