Mathew K Analytics

Lesson 46 · Pandas Full Course

Faster, Cleaner Pandas with eval() and query() (Big Data Tips) | Pandas Course #46

In this lesson you'll be able to: Filter rows with query, using and, or, not and in Bring Python variables into a query with @ Handle awkward column names…

What you'll learn

Datasets used in this lesson

Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.

📓 Full notebook

Download .ipynb

Pandas 46: Using eval() and query() for Performance#

In this lesson you'll be able to:

  • Filter rows with query, using and, or, not and in
  • Bring Python variables into a query with @
  • Handle awkward column names with backticks
  • Build one or several new columns with eval
  • Time code fairly, and know when eval and query pay off Dataset: Online Retail, 541,909 real shop sales (online_retail.csv.gz)

Warm-up: load the data slim#

Lesson 45 chose dtypes while loading. We'll start the same way.

import pandas as pd
cats = {"StockCode": "category", "Description": "category",
        "Country": "category"}
df = pd.read_csv("data/pandas/online_retail.csv.gz", dtype=cats)
print(df.shape)
(541909, 8)
print(df["Country"].cat.categories.size)
df[["Quantity", "UnitPrice", "Country"]].head(3)
38
Quantity UnitPrice Country
0 6 2.55 United Kingdom
1 6 3.39 United Kingdom
2 8 2.75 United Kingdom

Filter rows with query#

Problem: boolean masks repeat df[...] and need brackets everywhere.

mask = (df["Quantity"] >= 100) & (df["UnitPrice"] > 1)
bulk = df[mask]
print(len(bulk))
3968
bulk_q = df.query("Quantity >= 100 and UnitPrice > 1")
print(len(bulk_q))
print(bulk_q.equals(bulk))
3968
True
two = df.query('Country in ["France", "Germany"] and Quantity > 50')
print(len(two))
two["Country"].value_counts().head(2)
348
Country
France     192
Germany    156
Name: count, dtype: int64
odd = df.query("not (Quantity > 0 or UnitPrice == 0)")
print(len(odd))
odd["InvoiceNo"].str[0].value_counts()
9288
InvoiceNo
C    9288
Name: count, dtype: int64
top = (df.query("Quantity > 0")
         .groupby("Country", observed=True)["Quantity"]
         .sum()
         .nlargest(3))
top
Country
United Kingdom    4733819
Netherlands        200937
EIRE               147447
Name: Quantity, dtype: int64

Common mistake: comparing to text without quotes

df.query("Country == France")
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\scope.py:232, in Scope.resolve(self, key, is_local)
    231 if self.has_resolvers:
--> 232     return self.resolvers[key]
    234 # if we're here that means that we have no locals and we also have
    235 # no resolvers

File ~\AppData\Local\Programs\Python\Python312\Lib\collections\__init__.py:1014, in ChainMap.__getitem__(self, key)
   1013         pass
-> 1014 return self.__missing__(key)

File ~\AppData\Local\Programs\Python\Python312\Lib\collections\__init__.py:1006, in ChainMap.__missing__(self, key)
   1005 def __missing__(self, key):
-> 1006     raise KeyError(key)

KeyError: 'France'

During handling of the above exception, another exception occurred:

KeyError                                  Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\scope.py:243, in Scope.resolve(self, key, is_local)
    239 try:
    240     # last ditch effort we look in temporaries
    241     # these are created when parsing indexing expressions
    242     # e.g., df[df > 0]
--> 243     return self.temps[key]
    244 except KeyError as err:

KeyError: 'France'

The above exception was the direct cause of the following exception:

UndefinedVariableError                    Traceback (most recent call last)
Cell In[9], line 1
----> 1 df.query("Country == France")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py:5195, in DataFrame.query(self, expr, parser, engine, local_dict, global_dict, resolvers, level, inplace)
   5192     msg = f"expr must be a string to be evaluated, {type(expr)} given"
   5193     raise ValueError(msg)
-> 5195 res = self.eval(
   5196     expr,
   5197     level=level + 1,
   5198     parser=parser,
   5199     target=None,
   5200     engine=engine,
   5201     local_dict=local_dict,
   5202     global_dict=global_dict,
   5203     resolvers=resolvers or (),
   5204 )
   5206 try:
   5207     result = self.loc[res]

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py:5375, in DataFrame.eval(self, expr, inplace, **kwargs)
   5372     kwargs["target"] = self
   5373 kwargs["resolvers"] = tuple(kwargs.get("resolvers", ())) + resolvers
-> 5375 return _eval(expr, inplace=inplace, **kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\eval.py:370, in eval(expr, parser, engine, local_dict, global_dict, resolvers, level, target, inplace)
    361 # get our (possibly passed-in) scope
    362 env = ensure_scope(
    363     level + 1,
    364     global_dict=global_dict,
   (...)
    367     target=target,
    368 )
--> 370 parsed_expr = Expr(expr, engine=engine, parser=parser, env=env)
    372 if engine == "numexpr" and (
    373     (
    374         is_extension_array_dtype(parsed_expr.terms.return_type)
   (...)
    383     )
    384 ):
    385     warnings.warn(
    386         "Engine has switched to 'python' because numexpr does not support "
    387         "extension array dtypes. Please set your engine to python manually.",
    388         RuntimeWarning,
    389         stacklevel=find_stack_level(),
    390     )

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:819, in Expr.__init__(self, expr, engine, parser, env, level)
    817 self.parser = parser
    818 self._visitor = PARSERS[parser](self.env, self.engine, self.parser)
--> 819 self.terms = self.parse()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:838, in Expr.parse(self)
    834 def parse(self):
    835     """
    836     Parse an expression.
    837     """
--> 838     return self._visitor.visit(self.expr)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:420, in BaseExprVisitor.visit(self, node, **kwargs)
    418 method = f"visit_{type(node).__name__}"
    419 visitor = getattr(self, method)
--> 420 return visitor(node, **kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:426, in BaseExprVisitor.visit_Module(self, node, **kwargs)
    424     raise SyntaxError("only a single expression is allowed")
    425 expr = node.body[0]
--> 426 return self.visit(expr, **kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:420, in BaseExprVisitor.visit(self, node, **kwargs)
    418 method = f"visit_{type(node).__name__}"
    419 visitor = getattr(self, method)
--> 420 return visitor(node, **kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:429, in BaseExprVisitor.visit_Expr(self, node, **kwargs)
    428 def visit_Expr(self, node, **kwargs):
--> 429     return self.visit(node.value, **kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:420, in BaseExprVisitor.visit(self, node, **kwargs)
    418 method = f"visit_{type(node).__name__}"
    419 visitor = getattr(self, method)
--> 420 return visitor(node, **kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:729, in BaseExprVisitor.visit_Compare(self, node, **kwargs)
    727     op = self.translate_In(ops[0])
    728     binop = ast.BinOp(op=op, left=node.left, right=comps[0])
--> 729     return self.visit(binop)
    731 # recursive case: we have a chained comparison, a CMP b CMP c, etc.
    732 left = node.left

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:420, in BaseExprVisitor.visit(self, node, **kwargs)
    418 method = f"visit_{type(node).__name__}"
    419 visitor = getattr(self, method)
--> 420 return visitor(node, **kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:541, in BaseExprVisitor.visit_BinOp(self, node, **kwargs)
    540 def visit_BinOp(self, node, **kwargs):
--> 541     op, op_class, left, right = self._maybe_transform_eq_ne(node)
    542     left, right = self._maybe_downcast_constants(left, right)
    543     return self._maybe_evaluate_binop(op, op_class, left, right)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:462, in BaseExprVisitor._maybe_transform_eq_ne(self, node, left, right)
    460     left = self.visit(node.left, side="left")
    461 if right is None:
--> 462     right = self.visit(node.right, side="right")
    463 op, op_class, left, right = self._rewrite_membership_op(node, left, right)
    464 return op, op_class, left, right

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:420, in BaseExprVisitor.visit(self, node, **kwargs)
    418 method = f"visit_{type(node).__name__}"
    419 visitor = getattr(self, method)
--> 420 return visitor(node, **kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:551, in BaseExprVisitor.visit_Name(self, node, **kwargs)
    550 def visit_Name(self, node, **kwargs) -> Term:
--> 551     return self.term_type(node.id, self.env, **kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\ops.py:91, in Term.__init__(self, name, env, side, encoding)
     89 tname = str(name)
     90 self.is_local = tname.startswith(LOCAL_TAG) or tname in DEFAULT_GLOBALS
---> 91 self._value = self._resolve_name()
     92 self.encoding = encoding

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\ops.py:115, in Term._resolve_name(self)
    110 if local_name in self.env.scope and isinstance(
    111     self.env.scope[local_name], type
    112 ):
    113     is_local = False
--> 115 res = self.env.resolve(local_name, is_local=is_local)
    116 self.update(res)
    118 if hasattr(res, "ndim") and isinstance(res.ndim, int) and res.ndim > 2:

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\scope.py:245, in Scope.resolve(self, key, is_local)
    243     return self.temps[key]
    244 except KeyError as err:
--> 245     raise UndefinedVariableError(key, is_local) from err

UndefinedVariableError: name 'France' is not defined
france = df.query("Country == 'France'")
print(len(france))
8557

Bring in Python variables with @#

Problem: thresholds change, so they belong in variables, not in the string.

Common mistake: using a Python variable without @

min_qty = 500
df.query("Quantity >= min_qty")
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\scope.py:232, in Scope.resolve(self, key, is_local)
    231 if self.has_resolvers:
--> 232     return self.resolvers[key]
    234 # if we're here that means that we have no locals and we also have
    235 # no resolvers

File ~\AppData\Local\Programs\Python\Python312\Lib\collections\__init__.py:1014, in ChainMap.__getitem__(self, key)
   1013         pass
-> 1014 return self.__missing__(key)

File ~\AppData\Local\Programs\Python\Python312\Lib\collections\__init__.py:1006, in ChainMap.__missing__(self, key)
   1005 def __missing__(self, key):
-> 1006     raise KeyError(key)

KeyError: 'min_qty'

During handling of the above exception, another exception occurred:

KeyError                                  Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\scope.py:243, in Scope.resolve(self, key, is_local)
    239 try:
    240     # last ditch effort we look in temporaries
    241     # these are created when parsing indexing expressions
    242     # e.g., df[df > 0]
--> 243     return self.temps[key]
    244 except KeyError as err:

KeyError: 'min_qty'

The above exception was the direct cause of the following exception:

UndefinedVariableError                    Traceback (most recent call last)
Cell In[11], line 2
      1 min_qty = 500
----> 2 df.query("Quantity >= min_qty")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py:5195, in DataFrame.query(self, expr, parser, engine, local_dict, global_dict, resolvers, level, inplace)
   5192     msg = f"expr must be a string to be evaluated, {type(expr)} given"
   5193     raise ValueError(msg)
-> 5195 res = self.eval(
   5196     expr,
   5197     level=level + 1,
   5198     parser=parser,
   5199     target=None,
   5200     engine=engine,
   5201     local_dict=local_dict,
   5202     global_dict=global_dict,
   5203     resolvers=resolvers or (),
   5204 )
   5206 try:
   5207     result = self.loc[res]

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py:5375, in DataFrame.eval(self, expr, inplace, **kwargs)
   5372     kwargs["target"] = self
   5373 kwargs["resolvers"] = tuple(kwargs.get("resolvers", ())) + resolvers
-> 5375 return _eval(expr, inplace=inplace, **kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\eval.py:370, in eval(expr, parser, engine, local_dict, global_dict, resolvers, level, target, inplace)
    361 # get our (possibly passed-in) scope
    362 env = ensure_scope(
    363     level + 1,
    364     global_dict=global_dict,
   (...)
    367     target=target,
    368 )
--> 370 parsed_expr = Expr(expr, engine=engine, parser=parser, env=env)
    372 if engine == "numexpr" and (
    373     (
    374         is_extension_array_dtype(parsed_expr.terms.return_type)
   (...)
    383     )
    384 ):
    385     warnings.warn(
    386         "Engine has switched to 'python' because numexpr does not support "
    387         "extension array dtypes. Please set your engine to python manually.",
    388         RuntimeWarning,
    389         stacklevel=find_stack_level(),
    390     )

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:819, in Expr.__init__(self, expr, engine, parser, env, level)
    817 self.parser = parser
    818 self._visitor = PARSERS[parser](self.env, self.engine, self.parser)
--> 819 self.terms = self.parse()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:838, in Expr.parse(self)
    834 def parse(self):
    835     """
    836     Parse an expression.
    837     """
--> 838     return self._visitor.visit(self.expr)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:420, in BaseExprVisitor.visit(self, node, **kwargs)
    418 method = f"visit_{type(node).__name__}"
    419 visitor = getattr(self, method)
--> 420 return visitor(node, **kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:426, in BaseExprVisitor.visit_Module(self, node, **kwargs)
    424     raise SyntaxError("only a single expression is allowed")
    425 expr = node.body[0]
--> 426 return self.visit(expr, **kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:420, in BaseExprVisitor.visit(self, node, **kwargs)
    418 method = f"visit_{type(node).__name__}"
    419 visitor = getattr(self, method)
--> 420 return visitor(node, **kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:429, in BaseExprVisitor.visit_Expr(self, node, **kwargs)
    428 def visit_Expr(self, node, **kwargs):
--> 429     return self.visit(node.value, **kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:420, in BaseExprVisitor.visit(self, node, **kwargs)
    418 method = f"visit_{type(node).__name__}"
    419 visitor = getattr(self, method)
--> 420 return visitor(node, **kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:729, in BaseExprVisitor.visit_Compare(self, node, **kwargs)
    727     op = self.translate_In(ops[0])
    728     binop = ast.BinOp(op=op, left=node.left, right=comps[0])
--> 729     return self.visit(binop)
    731 # recursive case: we have a chained comparison, a CMP b CMP c, etc.
    732 left = node.left

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:420, in BaseExprVisitor.visit(self, node, **kwargs)
    418 method = f"visit_{type(node).__name__}"
    419 visitor = getattr(self, method)
--> 420 return visitor(node, **kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:541, in BaseExprVisitor.visit_BinOp(self, node, **kwargs)
    540 def visit_BinOp(self, node, **kwargs):
--> 541     op, op_class, left, right = self._maybe_transform_eq_ne(node)
    542     left, right = self._maybe_downcast_constants(left, right)
    543     return self._maybe_evaluate_binop(op, op_class, left, right)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:462, in BaseExprVisitor._maybe_transform_eq_ne(self, node, left, right)
    460     left = self.visit(node.left, side="left")
    461 if right is None:
--> 462     right = self.visit(node.right, side="right")
    463 op, op_class, left, right = self._rewrite_membership_op(node, left, right)
    464 return op, op_class, left, right

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:420, in BaseExprVisitor.visit(self, node, **kwargs)
    418 method = f"visit_{type(node).__name__}"
    419 visitor = getattr(self, method)
--> 420 return visitor(node, **kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:551, in BaseExprVisitor.visit_Name(self, node, **kwargs)
    550 def visit_Name(self, node, **kwargs) -> Term:
--> 551     return self.term_type(node.id, self.env, **kwargs)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\ops.py:91, in Term.__init__(self, name, env, side, encoding)
     89 tname = str(name)
     90 self.is_local = tname.startswith(LOCAL_TAG) or tname in DEFAULT_GLOBALS
---> 91 self._value = self._resolve_name()
     92 self.encoding = encoding

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\ops.py:115, in Term._resolve_name(self)
    110 if local_name in self.env.scope and isinstance(
    111     self.env.scope[local_name], type
    112 ):
    113     is_local = False
--> 115 res = self.env.resolve(local_name, is_local=is_local)
    116 self.update(res)
    118 if hasattr(res, "ndim") and isinstance(res.ndim, int) and res.ndim > 2:

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\scope.py:245, in Scope.resolve(self, key, is_local)
    243     return self.temps[key]
    244 except KeyError as err:
--> 245     raise UndefinedVariableError(key, is_local) from err

UndefinedVariableError: name 'min_qty' is not defined
huge = df.query("Quantity >= @min_qty")
print(len(huge))
huge["Country"].value_counts().head(3)
459
Country
United Kingdom    407
Japan              12
Netherlands        11
Name: count, dtype: int64
print(len(df.query(f"Quantity >= {min_qty}")))
459
nordics = ["Sweden", "Norway", "Denmark", "Finland", "Iceland"]
nordic = df.query("Country in @nordics")
nordic["Country"].value_counts().head(5)
Country
Norway     1086
Finland     695
Sweden      462
Denmark     389
Iceland     182
Name: count, dtype: int64
Quantity = 1000
print(len(df.query("Quantity > @Quantity")))
116

Your turn#

Set country = 'Germany' and cap = 10. Count German lines priced over cap.

country = "Germany"
cap = 10
pricey = df.query("Country == @country and UnitPrice > @cap")
print(len(pricey))
pricey["Description"].value_counts().head(3)
651
Description
POSTAGE                     383
REGENCY CAKESTAND 3 TIER     81
RED RETROSPOT CAKE STAND     19
Name: count, dtype: int64

Query dates, ranges and text#

Problem: real filters mix dates, between-ranges and words.

fmt = "%m/%d/%Y %H:%M"
df["InvoiceDate"] = pd.to_datetime(df["InvoiceDate"], format=fmt)
dec = df.query("InvoiceDate >= '2011-12-01'")
print(len(dec), dec["InvoiceDate"].max())
25525 2011-12-09 12:50:00
cheap = df.query("1 <= UnitPrice <= 2")
print(len(cheap))
140770
heart = df.query('Description.str.contains("HEART", na=False)',
                 engine="python")
print(len(heart))
heart["Description"].value_counts().head(3)
48627
Description
WHITE HANGING HEART T-LIGHT HOLDER    2369
NATURAL SLATE HEART CHALKBOARD        1280
HEART OF WICKER SMALL                 1237
Name: count, dtype: int64

Quick check: how many lines were sold on 9 December 2011?

last = df.query("'2011-12-09' <= InvoiceDate < '2011-12-10'")
print(len(last), last["InvoiceNo"].nunique())
1632 49

Handle awkward column names with backticks#

Problem: names with spaces break the query parser.

nice = df.rename(columns={"UnitPrice": "Unit Price",
                          "Quantity": "Qty Sold"})
list(nice.columns)
['InvoiceNo',
 'StockCode',
 'Description',
 'Qty Sold',
 'InvoiceDate',
 'Unit Price',
 'CustomerID',
 'Country']

Common mistake: a column name with a space in a query

nice.query("Unit Price > 100")
Traceback (most recent call last):

  File ~\AppData\Roaming\Python\Python312\site-packages\IPython\core\interactiveshell.py:3577 in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)

  Cell In[22], line 1
    nice.query("Unit Price > 100")

  File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py:5195 in query
    res = self.eval(

  File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py:5375 in eval
    return _eval(expr, inplace=inplace, **kwargs)

  File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\eval.py:370 in eval
    parsed_expr = Expr(expr, engine=engine, parser=parser, env=env)

  File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:819 in __init__
    self.terms = self.parse()

  File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:838 in parse
    return self._visitor.visit(self.expr)

  File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:416 in visit
    raise e

  File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\computation\expr.py:412 in visit
    node = ast.fix_missing_locations(ast.parse(clean))

  File ~\AppData\Local\Programs\Python\Python312\Lib\ast.py:52 in parse
    return compile(source, filename, mode, flags,

  File <unknown>:1
    Unit Price >100
         ^
SyntaxError: invalid syntax
dear = nice.query("`Unit Price` > 100 and `Qty Sold` > 0")
print(len(dear))
dear["Description"].value_counts().head(3)
811
Description
DOTCOM POSTAGE                 604
Manual                          75
VINTAGE RED KITCHEN CABINET     36
Name: count, dtype: int64

Quick check: how would you query a column called 'Order-Date'?

dashed = df.rename(columns={"InvoiceNo": "Invoice-No"})
print(len(dashed.query("`Invoice-No` == '536365'")))
7

Build new columns with eval#

Problem: formulas full of df["..."] are hard to read.

df.eval("Quantity * UnitPrice").head(3)
0    15.30
1    20.34
2    22.00
dtype: float64

Common mistake: expecting eval to change df by itself

df.eval("Revenue = Quantity * UnitPrice")
df["Revenue"].sum()
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\base.py:3641, in Index.get_loc(self, key)
   3640 try:
-> 3641     return self._engine.get_loc(casted_key)
   3642 except KeyError as err:

File pandas/_libs/index.pyx:168, in pandas._libs.index.IndexEngine.get_loc()

File pandas/_libs/index.pyx:197, in pandas._libs.index.IndexEngine.get_loc()

File pandas/_libs/hashtable_class_helper.pxi:7668, in pandas._libs.hashtable.PyObjectHashTable.get_item()

File pandas/_libs/hashtable_class_helper.pxi:7676, in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: 'Revenue'

The above exception was the direct cause of the following exception:

KeyError                                  Traceback (most recent call last)
Cell In[26], line 2
      1 df.eval("Revenue = Quantity * UnitPrice")
----> 2 df["Revenue"].sum()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py:4378, in DataFrame.__getitem__(self, key)
   4376 if self.columns.nlevels > 1:
   4377     return self._getitem_multilevel(key)
-> 4378 indexer = self.columns.get_loc(key)
   4379 if is_integer(indexer):
   4380     indexer = [indexer]

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\base.py:3648, in Index.get_loc(self, key)
   3643     if isinstance(casted_key, slice) or (
   3644         isinstance(casted_key, abc.Iterable)
   3645         and any(isinstance(x, slice) for x in casted_key)
   3646     ):
   3647         raise InvalidIndexError(key) from err
-> 3648     raise KeyError(key) from err
   3649 except TypeError:
   3650     # If we have a listlike key, _check_indexing_error will raise
   3651     #  InvalidIndexError. Otherwise we fall through and re-raise
   3652     #  the TypeError.
   3653     self._check_indexing_error(key)

KeyError: 'Revenue'
df = df.eval("Revenue = Quantity * UnitPrice")
print(round(df["Revenue"].sum(), 2))
9747747.93
vat = 0.2
df = df.eval("""
Gross = Revenue * (1 + @vat)
Tax = Gross - Revenue
IsReturn = Quantity < 0
""")
df[["Revenue", "Gross", "Tax", "IsReturn"]].head(3)
Revenue Gross Tax IsReturn
0 15.30 18.360 3.060 False
1 20.34 24.408 4.068 False
2 22.00 26.400 4.400 False
print(df.eval("IsReturn").sum())
print(df.eval("Revenue > 1000").sum())
10624
388
flag = df.eval("Revenue > 1000 and not IsReturn")
big_sales = df.loc[flag, ["Description", "Quantity", "Revenue"]]
big_sales.nlargest(3, "Revenue")
Description Quantity Revenue
540421 PAPER CRAFT , LITTLE BIRDIE 80995 168469.6
61619 MEDIUM CERAMIC TOP STORAGE JAR 74215 77183.6
222680 PICNIC BASKET WICKER 60 PIECES 60 38970.0

Your turn#

Add a column Discounted = UnitPrice * (1 - @rate) with rate = 0.1.

rate = 0.1
df = df.eval("Discounted = UnitPrice * (1 - @rate)")
df[["UnitPrice", "Discounted"]].head(3)
UnitPrice Discounted
0 2.55 2.295
1 3.39 3.051
2 2.75 2.475
kept = df.query("Discounted < UnitPrice")
print(len(kept), len(df))
539392 541909

Time it fairly: when do they pay off?#

Problem: "faster" depends on table size and the engine installed.

from timeit import repeat
best = lambda f: min(repeat(f, number=5, repeat=9))
tiny = df.head(5)
t_mask = best(lambda: tiny[tiny["Quantity"] > 10])
t_query = best(lambda: tiny.query("Quantity > 10"))
print(len(tiny), "rows, query / mask:", round(t_query / t_mask, 1))
5 rows, query / mask: 3.3
f_mask = best(lambda: df[df["Quantity"] > 10])
f_query = best(lambda: df.query("Quantity > 10"))
print(len(df), "rows, query / mask:", round(f_query / f_mask, 1))
541909 rows, query / mask: 1.4

eval and query hand the maths to an engine:

  • numexpr: fast on big tables (pip install numexpr)
  • python: always available, but adds parsing overhead
expr = "Quantity * UnitPrice * (1 + @vat) - UnitPrice"
py = lambda: df.eval(expr, engine="python")
plain = lambda: df["Quantity"] * df["UnitPrice"] * (1 + vat) - df["UnitPrice"]
ratio = best(py) / best(plain)
print(len(df), "rows, python engine / plain:", round(ratio, 1))
541909 rows, python engine / plain: 2.5
gap = (df.eval(expr) - plain()).abs().max()
print("Same results:", gap < 1e-9)
Same results: True

Your turn#

Filter UnitPrice > 5 with query and with a mask. Same rows? Which is faster here?

m = lambda: df[df["UnitPrice"] > 5]
q = lambda: df.query("UnitPrice > 5")
print("Same rows:", q().equals(m()), len(m()))
print("query / mask time:", round(best(q) / best(m), 1))
Same rows: True 87992
query / mask time: 1.6

When to reach for them:

  • query and eval: for readable filters and formulas, any size
  • For speed: big tables, long formulas, numexpr installed, and a timing

Mini-project: which big markets return the most?#

Return value as a share of sales, for countries selling over 50,000 pounds.

admin = ["POST", "DOT", "M", "C2", "D", "S", "BANK CHARGES",
         "AMAZONFEE", "CRUK"]
goods = df.query("StockCode not in @admin")
print(len(df) - len(goods))
2908
sold = goods.query("Quantity > 0").groupby(
    "Country", observed=True)["Revenue"].sum()
back = goods.query("IsReturn").groupby(
    "Country", observed=True)["Revenue"].sum()
summary = pd.DataFrame({"sold": sold, "returned": -back})
summary.head(3).round(0)
sold returned
Country
Australia 138171.0 1181.0
Austria 8743.0 44.0
Bahrain 754.0 206.0
summary = summary.fillna(0).eval("rate = returned / sold")
big = summary.query("sold > 50000")
big = big.sort_values("rate", ascending=False)
big.round(3)
sold returned rate
Country
Spain 55725.110 3959.91 0.071
United Kingdom 8737187.963 445843.38 0.051
EIRE 271164.300 11500.84 0.042
Germany 205569.890 4761.49 0.023
France 184582.740 2419.35 0.013
Switzerland 53087.900 582.55 0.011
Australia 138171.310 1181.31 0.009
Netherlands 283889.340 409.80 0.001
ax = big["rate"].mul(100).sort_values().plot(kind="barh",
                                             figsize=(7, 4))
ax.set_title("Returns as a share of sales, big markets")
ax.set_xlabel("Returned value (% of sales)")
ax.set_ylabel("Country")
No description has been provided for this image
worst = big.index[0]
spain = goods.query("Country == @worst and IsReturn")
lost = spain.groupby("Description", observed=True)["Revenue"].sum()
lost.sort_values().head(3)
Description
CINDERELLA CHANDELIER               -417.50
REGENCY CAKESTAND 3 TIER            -388.65
VANILLA SCENT CANDLE JEWELLED BOX   -360.00
Name: Revenue, dtype: float64
worst, best = big.index[0], big.index[-1]
print(f"{worst}: {big.loc[worst, 'rate']:.1%} returned")
print(f"{best}: {big.loc[best, 'rate']:.1%} returned")
Spain: 7.1% returned
Netherlands: 0.1% returned

Recap#

  • query filters rows with readable strings: and, or, not, in
  • @ brings in Python variables; text values need quotes
  • Backticks wrap column names with spaces or dashes
  • eval builds columns; assign the result back to df
  • Speed depends on size and engine: measure, don't assume

Challenges#

  1. Query the lines from 'EIRE' with Revenue over 500.
  2. Use eval to add a column UnitGross = UnitPrice * (1 + @vat).
  3. Time eval against plain pandas for Revenue on df.head(1000).

What's next#

Lesson 47: Handling Large Files with Chunking and Dask Process files piece by piece, even when they don't fit in memory.

Found this useful?

All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.