Query Halo from a Python notebook

Pull a Halo query into a pandas DataFrame, with paging, retries and typed dates, ready to analyze in a notebook.

When you're done, a one-line call turns any query into a pandas DataFrame, however many windows it takes. The example reads revenue by month and region.

Before you start

• An API key with data.query and datasets.read.

• A dataset's id, and its field names: steps 3 and 4 of the Quickstart find them. The examples use region, order_date and revenue.

Set the key before you start the notebook, so it stays out of the notebook's file and outputs:

pip install requests pandas jupyterlab
read -rs HALO_API_KEY && export HALO_API_KEY
jupyter lab

1. Make a small client

The client sends the key on every request, waits when Halo says to, and stops with the problem's code otherwise.

import os
import time

import pandas as pd
import requests

API = "https://api.usehalo.net/v1/developer"

session = requests.Session()
session.headers["Authorization"] = f"Bearer {os.environ['HALO_API_KEY']}"


def call(method, path, **kwargs):
    while True:
        response = session.request(method, f"{API}{path}", timeout=330, **kwargs)
        if response.ok:
            return response.json()
        problem = response.json()
        if problem.get("code") == "rate_limited":
            time.sleep(int(response.headers.get("Retry-After", "60")))
            continue
        raise RuntimeError(f"{problem.get('code')}: {problem.get('detail')} (request {problem.get('requestId')})")

The timeout sits just above the longest time any plan lets a query run.

2. Read a query into a DataFrame

keyset paging reads every window without rescanning the ones before it. columns names the DataFrame's columns and says which hold dates.

def query_frame(dataset_id, **query):
    body = {**query, "limit": 1000, "keyset": True}
    columns, rows, offset, after = [], [], 0, None
    while True:
        window = {**body, "offset": offset}
        if after:
            window["cursor"] = after
        page = call("POST", f"/datasets/{dataset_id}/query", json=window)
        columns = page["columns"]
        rows.extend(page["rows"])
        for warning in page["warnings"]:
            print("Halo says:", warning)
        if not page["stats"]["hasMore"]:
            break
        offset += page["stats"]["rowCount"]
        after = page["stats"].get("nextCursor")

    frame = pd.DataFrame(rows, columns=[column["key"] for column in columns])
    for column in columns:
        if column["type"] in ("date", "timestamp"):
            frame[column["key"]] = pd.to_datetime(frame[column["key"]])
    return frame

3. Query

Arguments are the query body.

DATASET_ID = "0195f3a2-…"

revenue = query_frame(
    DATASET_ID,
    dimensions=[{"field": "order_date", "grain": "month"}, {"field": "region"}],
    measures=[{"field": "revenue"}],
    filters=[
        {
            "field": "order_date",
            "op": "date_range",
            "range": {"kind": "relative", "preset": "rolling_12_months"},
        }
    ],
)
revenue.head()

A month grain adds __month to the column's key, so the date column is order_date__month:

  order_date__month region    revenue
0        2025-11-01  North  148220.50
1        2025-11-01  South   92310.00
2        2025-11-01   West  120455.75
3        2025-12-01  North  161870.25
4        2025-12-01  South   97640.00

4. Analyze

From here it's ordinary pandas.

by_region = revenue.pivot_table(index="order_date__month", columns="region", values="revenue")
by_region.pct_change().tail(3).round(3)

To draw it, add by_region.plot() with matplotlib installed.

Watch your row budget

Every row returned counts toward the person's daily budget: 100,000 on Standard. Grouping costs you the groups, not the source rows, so aggregate in the query where you can rather than in pandas.

call("GET", "/whoami")["limits"]["rowBudget"]

Past the budget the client above raises row_budget_exceeded. Wait for rows to free up over the next 24 hours, or aggregate more.

Before you share the notebook

• Check no output shows the key. Reading it from the environment keeps it out of the file.

• Your outputs hold what you can see, under your own access and row policies. Someone reading the notebook sees your rows, not theirs.