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…
- CoursePandas Full Course
- Lesson46 of 65
- FormatJupyter notebook · 42 code cells
- Data1 dataset
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.
- online_retail.csv.gz preview7.1 MB
📓 Full notebook
Download .ipynbPandas 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)
print(df["Country"].cat.categories.size)
df[["Quantity", "UnitPrice", "Country"]].head(3)
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))
bulk_q = df.query("Quantity >= 100 and UnitPrice > 1")
print(len(bulk_q))
print(bulk_q.equals(bulk))
two = df.query('Country in ["France", "Germany"] and Quantity > 50')
print(len(two))
two["Country"].value_counts().head(2)
odd = df.query("not (Quantity > 0 or UnitPrice == 0)")
print(len(odd))
odd["InvoiceNo"].str[0].value_counts()
top = (df.query("Quantity > 0")
.groupby("Country", observed=True)["Quantity"]
.sum()
.nlargest(3))
top
Common mistake: comparing to text without quotes
df.query("Country == France")
france = df.query("Country == 'France'")
print(len(france))
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")
huge = df.query("Quantity >= @min_qty")
print(len(huge))
huge["Country"].value_counts().head(3)
print(len(df.query(f"Quantity >= {min_qty}")))
nordics = ["Sweden", "Norway", "Denmark", "Finland", "Iceland"]
nordic = df.query("Country in @nordics")
nordic["Country"].value_counts().head(5)
Quantity = 1000
print(len(df.query("Quantity > @Quantity")))
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)
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())
cheap = df.query("1 <= UnitPrice <= 2")
print(len(cheap))
heart = df.query('Description.str.contains("HEART", na=False)',
engine="python")
print(len(heart))
heart["Description"].value_counts().head(3)
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())
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)
Common mistake: a column name with a space in a query
nice.query("Unit Price > 100")
dear = nice.query("`Unit Price` > 100 and `Qty Sold` > 0")
print(len(dear))
dear["Description"].value_counts().head(3)
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'")))
Build new columns with eval#
Problem: formulas full of df["..."] are hard to read.
df.eval("Quantity * UnitPrice").head(3)
Common mistake: expecting eval to change df by itself
df.eval("Revenue = Quantity * UnitPrice")
df["Revenue"].sum()
df = df.eval("Revenue = Quantity * UnitPrice")
print(round(df["Revenue"].sum(), 2))
vat = 0.2
df = df.eval("""
Gross = Revenue * (1 + @vat)
Tax = Gross - Revenue
IsReturn = Quantity < 0
""")
df[["Revenue", "Gross", "Tax", "IsReturn"]].head(3)
print(df.eval("IsReturn").sum())
print(df.eval("Revenue > 1000").sum())
flag = df.eval("Revenue > 1000 and not IsReturn")
big_sales = df.loc[flag, ["Description", "Quantity", "Revenue"]]
big_sales.nlargest(3, "Revenue")
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)
kept = df.query("Discounted < UnitPrice")
print(len(kept), len(df))
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))
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))
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))
gap = (df.eval(expr) - plain()).abs().max()
print("Same results:", gap < 1e-9)
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))
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))
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)
summary = summary.fillna(0).eval("rate = returned / sold")
big = summary.query("sold > 50000")
big = big.sort_values("rate", ascending=False)
big.round(3)
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")
worst = big.index[0]
spain = goods.query("Country == @worst and IsReturn")
lost = spain.groupby("Description", observed=True)["Revenue"].sum()
lost.sort_values().head(3)
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")
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#
- Query the lines from 'EIRE' with Revenue over 500.
- Use eval to add a column UnitGross = UnitPrice * (1 + @vat).
- 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.



