Pandas + Odds API: Turn a 59 MB Board into 79,503 Rows

Q: How do I keep stale prices out of an odds DataFrame?

Keep changedAt as a column. Drop a book when its newest price on the fixture is older than your window, and drop a price when it trails that book's newest price by more than the same window. Two days out, 24 hours works; on match day, use an hour.

Q: Can I store the odds DataFrame between runs?

Yes. Parquet keeps dtypes and loads faster than CSV: df.to_parquet('odds.parquet'). For long-running capture, write one file per fixture and poll: df.to_parquet(f'odds/{fixture_id}/{ts}.parquet').

How To Guides May 20, 2026

One OddsPapi /v4/odds call on Chelsea v Tottenham returned 59 MB of JSON nested four levels deep: bookmaker, market, outcome, player. This post turns that board into a flat pandas DataFrame of 79,503 rows across 275 bookmakers, then runs five recipes on it: best price per outcome, a stale-feed filter, margin per book with duplicate feeds grouped, price spread per outcome, and an arb scanner with guards.

Every output block below comes from one run at 13:13 UTC on 22 October 2026, about 51 hours before kick-off at Stamford Bridge. To follow along, grab an OddsPapi API key and pick any soccer fixture ID. The structure is the same across sports. If you have never called the API before, start with your first odds API call in Python.

Why You Want a DataFrame, Not a Dict

Most odds-API tutorials hand you a Python dict and stop. That works when you check one bookmaker on one market. When you compare prices, compute fair odds or scan for arbs, you write the same nested loops again for every question.

Flatten once into a long table, one row per bookmaker, market and outcome, and each question becomes a groupby. The work is getting from the raw payload to that table fast, then leaving the dict alone.

Question	With nested dicts	With pandas
Best price per outcome	Loop over books, compare by hand, track the max	`df.groupby("outcome")["price"].idxmax()`
Margin per bookmaker	Re-shape per book, sum inverse prices by hand	`pivot_table` plus one vector sum
Which books share one feed	Hash every book’s prices yourself	`groupby` on the price columns
Arbs across 275 books	Three nested loops	`groupby("market_id")` and a filter

Step 1: The JSON-to-DataFrame Snippet

Pull the board, flatten it with one comprehension, parse the timestamps. Keep changedAt: Step 3 needs it.

import requests, pandas as pd

API_KEY = "YOUR_API_KEY"
odds = requests.get("https://api.oddspapi.io/v4/odds", params={
    "apiKey": API_KEY,
    "fixtureId": "id1000001772221336",  # Chelsea v Tottenham, 24 Oct
}).json()

df = pd.DataFrame([
    {"book": book, "market_id": int(mid), "outcome_id": int(oid),
     "price": p["price"], "changed": p["changedAt"]}
    for book, b in odds["bookmakerOdds"].items()
    for mid, m in b["markets"].items()
    for oid, o in m["outcomes"].items()
    for p in [o["players"].get("0")] if p and p.get("active") and p["price"] > 0
])
df["changed"] = pd.to_datetime(df["changed"], utc=True)

Output:

>>> df.shape
(79503, 5)
>>> df.book.nunique()
275
>>> df.market_id.nunique()
593
>>> df.head()
     book  market_id  outcome_id  price                          changed
0  198bet      10264       10264  19.00 2026-10-15 17:18:16.133000+00:00
1  198bet      10172       10173   2.31        2026-10-22 11:16:11+00:00
2  198bet      10172       10172   1.60        2026-10-22 11:16:11+00:00
3  198bet      10602       10602   1.83 2026-10-22 11:04:07.687000+00:00
4  198bet      10602       10603   1.99 2026-10-22 11:04:07.687000+00:00

That is 79,503 active match-market prices from 275 books on 593 markets, one row each. The download took 1.5 seconds.

Three filters inside the comprehension

players.get("0") keeps match markets and skips player props. The 1X2, totals, handicaps, corners and bookings ship one price under the key "0". Props key each price by player ID instead, and on this board they are a large share of the payload: Pitfall 4 builds them into their own DataFrame.
active drops suspended prices. The raw board held 176,962 price rows, and 24,817 of them were inactive.
price > 0 guards your min() and division. No active row on this board had a zero price. The filter costs nothing, so keep it.

Step 2: Add Human-Readable Names

Market and outcome IDs are fine for maths and hard to read. The /v4/markets catalogue gives you the lookup tables, plus marketLength, the number of outcomes a market should have. The arb scanner in Step 6 uses it.

cat = requests.get("https://api.oddspapi.io/v4/markets",
    params={"apiKey": API_KEY, "sportId": 10}).json()

mkt_name = {m["marketId"]: m["marketName"] for m in cat}
mkt_len = {m["marketId"]: m["marketLength"] for m in cat}
out_name = {(m["marketId"], o["outcomeId"]): o["outcomeName"]
            for m in cat for o in m["outcomes"]}

df["market"] = df.market_id.map(mkt_name)
df["outcome"] = [out_name.get(k) for k in zip(df.market_id, df.outcome_id)]

With sportId=10 the catalogue returned 1,122 soccer market definitions, and every one of the 593 market IDs on the board found a name. Without sportId the same call returns 46,901 definitions across all sports, so pass it.

Step 3: Best Price Per Outcome, Then Cut the Stale Feeds

The first question a line shopper asks: which book pays the most on each outcome?

m101 = df[df.market_id == 101]  # Full Time Result (1X2)
best = m101.loc[m101.groupby("outcome")["price"].idxmax()]
print(best[["outcome", "book", "price", "changed"]])

      outcome           book  price                          changed
10313       1  bet365.bet.br   1.76 2026-10-16 20:53:55.273000+00:00
8622        2        bet3000   5.50 2026-10-22 07:45:41.184000+00:00
8623        X        bet3000   4.50 2026-10-22 07:45:41.184000+00:00

Read the changed column before you act on that Chelsea price. The 1.76 last moved on 16 October, six days before the pull, and that book had changed no price on the fixture since then. A price can stay active: true after the book stops updating it, so give every best-price query two clocks:

Book age: how long since the book’s newest price on this fixture changed. If the whole feed is quiet, drop the book.
Price lag: how far one price trails that same book’s newest price. A market the book has stopped trading falls behind the rest of its board.

now = pd.Timestamp.now(tz="UTC")

def drop_stale(d, book_max_age="24h", price_lag_max="24h"):
    newest = d.groupby("book")["changed"].transform("max")
    fresh_book = (now - newest) <= pd.Timedelta(book_max_age)
    fresh_price = (newest - d["changed"]) <= pd.Timedelta(price_lag_max)
    return d[fresh_book & fresh_price]

live = drop_stale(df)
print(f"{len(df):,} rows -> {len(live):,} rows, "
      f"{df.book.nunique()} books -> {live.book.nunique()} books")

m101 = live[live.market_id == 101]
best = m101.loc[m101.groupby("outcome")["price"].idxmax()]
print(best[["outcome", "book", "price"]])

79,503 rows -> 62,040 rows, 275 books -> 270 books
      outcome     book  price
44225       1   kalshi  1.754
8622        2  bet3000  5.500
8623        X  bet3000  4.500

Five books fail the book-age test. The rest of the cut comes from the price-lag test. Set both windows from the horizon: two days out, many soft books leave the 1X2 alone for hours while they trade props, so 24 hours is a fair window. On match day, tighten both to an hour. The Chelsea leg now goes to Kalshi at 1.754. This is the core of line shopping, and pandas does it in three lines.

Step 4: Margin Per Book, With Shared Feeds Grouped

The margin (vig) is the bookmaker’s built-in edge: sum the inverse decimal prices across a market’s outcomes, and the excess over 1.0 is the margin. Pivot the 1X2 so each book is one row, compute the margin, then group books that quote the exact same three prices.

wide = m101.pivot_table(index="book", columns="outcome", values="price").dropna()
wide["margin_pct"] = ((1 / wide[["1", "X", "2"]]).sum(axis=1) - 1) * 100

feeds = (wide.reset_index()
             .groupby(["1", "X", "2", "margin_pct"])["book"]
             .agg(["count", "first"])
             .reset_index()
             .sort_values("margin_pct"))
print(f"{len(wide)} books quote the 1X2, {len(feeds)} distinct price tuples")
print(feeds.head(8).to_string(index=False))
print("...")
print(feeds.tail(4).to_string(index=False))

201 books quote the 1X2, 88 distinct price tuples
    1     X     2  margin_pct  count      first
1.750 4.500 4.700    0.641675      5 betfair-ex
1.754 4.348 4.762    1.011203      1     kalshi
1.743 4.444 4.706    1.124066      1     sx.bet
1.746 4.400 4.700    1.277637      1   prophetx
1.748 4.455 4.575    1.512851      1      1xbet
1.741 4.440 4.550    1.938798      1   4casters
1.724 4.348 4.762    2.003300      1 polymarket
1.739 4.348 4.651    2.004145      1   novig.us
...
   1    X    2  margin_pct  count       first
1.60 4.00 4.25   11.029412      2  pokerstars
1.63 3.95 4.10   11.056393      1         pmu
1.62 3.90 4.10   11.759665      1 leovegas.it
1.61 3.75 3.80   15.094257      1     bwin.fr

Two findings sit in that table. First, the 201 fresh books quote only 88 distinct 1X2 prices. Five slugs (betfair-ex, fair999-ex, orbitx, piwi247-ex, sharpxch) carry the Betfair Exchange price to the third decimal. If you average across slugs to build a fair price, those five count five times. Group on the price tuple first, the way the consensus odds calculator does.

Second, the margin ladder runs from 0.64% to 15.09% on one match. The exchanges and prediction markets (Betfair, Kalshi, SX Bet, ProphetX, Polymarket, Novig) sit at the top because they take their cut as commission or trading fees, which the price does not show. For a sportsbook-only ranking, filter them out by slug. Pinnacle quoted the same market at 3.67%.

Step 5: Price Spread Per Outcome

Where do the books disagree most? Group on market ID and outcome, never on the market name: every handicap and total line shares a name, so a name group mixes a -1 line with a +2 line. Use the 10th and 90th percentiles rather than min and max. One wild row moves the max, and it cannot move the 90th percentile of 150 quotes.

key = ["market_id", "market", "outcome"]
spread = live.groupby(key)["price"].quantile([0.1, 0.9]).unstack()
spread.columns = ["p10", "p90"]
spread["count"] = live.groupby(key).size()
spread["spread_pct"] = (spread["p90"] / spread["p10"] - 1) * 100

deep = spread[spread["count"] >= 150].reset_index()
deep = deep[~deep.market.str.contains("Score")]  # long-shot scorelines swamp the list
print(deep.sort_values("spread_pct", ascending=False).head(8).to_string(index=False))

 market_id                          market outcome   p10    p90  count  spread_pct
    101919           Half Time / Full Time     1/2 29.00 43.190    161   48.931034
    101919           Half Time / Full Time     2/1 16.00 22.000    175   37.500000
    101919           Half Time / Full Time     2/2  6.09  7.705    160   26.518883
     10137               European Handicap       2  8.50 10.320    174   21.411765
    102222    Over Under Team 2 First Half    Over  6.70  8.000    152   19.402985
     10930 Bookings - Over Under Full Time   Under  2.10  2.500    155   19.047619
      1016            Over Under Full Time    Over  6.40  7.500    196   17.187500
     10137               European Handicap       X  5.75  6.725    166   16.956522

Correct-score rows top the unfiltered list because a 0:4 scoreline is a long shot that every book prices its own way. With them gone, Half Time / Full Time leads: 161 books quote Chelsea-at-half, Tottenham-at-full-time, and the 90th percentile pays 49% more than the 10th. For comparison, the Tottenham win in the 1X2 spread 11.0% across 239 fresh quotes. Wide spreads on deep markets are where a value betting scanner earns its keep.

Step 6: Arb Scanner With Guards

An arb exists when the inverse sum of the best prices across every outcome is under 1.0. The scanner below checks that each market carries all its outcomes (marketLength from Step 2) and that the best legs come from at least two books. A single book never pays you to back both sides, so a one-book “arb” means two of its prices are out of step.

def guard(d, band=1.2, min_books=5):
    key = ["market_id", "outcome_id"]
    median = d.groupby(key)["price"].transform("median")
    books = d.groupby(key)["book"].transform("nunique")
    return d[(books >= min_books) & (d["price"] <= median * band)]

def scan(d):
    hits = []
    for mid, sub in d.groupby("market_id"):
        if sub.outcome_id.nunique() != mkt_len.get(mid):
            continue  # a leg is missing, the sum means nothing
        legs = sub.loc[sub.groupby("outcome_id")["price"].idxmax()]
        if legs.book.nunique() < 2:
            continue  # one book never pays you to bet both sides
        invsum = (1 / legs["price"]).sum()
        if invsum < 1:
            hits.append({"market": mkt_name[mid], "margin_pct": round((1 - invsum) * 100, 2),
                         "legs": " / ".join(f"{b} {p}" for b, p in zip(legs.book, legs.price))})
    return pd.DataFrame(hits).sort_values("margin_pct", ascending=False)

raw = scan(df)
safe = scan(guard(live))
safe = safe[safe.margin_pct < 5]
print(f"raw scan: {len(raw)} hits, guarded scan: {len(safe)} hits")
print(safe.head(6).to_string(index=False))

raw scan: 47 hits, guarded scan: 15 hits
                        market  margin_pct                                              legs
   Corners - Over Under Team 2        1.88                       pokerstars 2.1 / 20bet 1.98
   Corners - Over Under Team 2        1.64                          atg.se 1.53 / 20bet 3.03
Corners - Over Under Full Time        1.00                    fanduel 2.0 / polymarket 2.041
Corners - Over Under Full Time        0.82                   fanduel 2.62 / polymarket 1.639
             First Half Result        0.78 polymarket 2.326 / polymarket 2.703 / bet3000 5.2
          Over Under Full Time        0.59                    polymarket 2.439 / 1xbet 1.712

What each guard removes

drop_stale (Step 3) removes legs whose book stopped trading the market. On this board, several of the widest raw hits had a leg that trailed its own book’s newest price by 40 to 66 hours.
The median band drops any price more than 20% above the median for that outcome. A fresh price far off the market usually means the book mapped an outcome to the wrong ID, and the band catches it without a per-book whitelist.
The five-book floor keeps thin markets out. Two books on a corners line give you no median to check against.
The 5% ceiling is a last net. On this board every guarded hit sat under 2%. Treat a bigger number as a data question first.

Treat the survivors as candidates. Re-poll the fixture before you stake, check each leg’s changed stamp again, and confirm that the book takes your stake size. The arbitrage betting bot tutorial adds stake splitting and alerting on top of this scan.

From One Fixture to a Full Round

To run the same recipes over a round, pull the fixture list, then build one frame per fixture. Filter /v4/fixtures on tournamentId: a bare sportId=10 window returns every soccer fixture in the world, and each /odds board on a top tournament runs to tens of megabytes.

from datetime import datetime, timedelta, timezone
import time

now = datetime.now(timezone.utc)
end = now + timedelta(days=4)

fixtures = requests.get("https://api.oddspapi.io/v4/fixtures", params={
    "apiKey": API_KEY,
    "sportId": 10,
    "tournamentId": 17,  # English Premier League
    "from": now.strftime("%Y-%m-%dT%H:%M:%S"),
    "to": end.strftime("%Y-%m-%dT%H:%M:%S"),
}).json()

frames = []
for f in fixtures:
    if not f.get("hasOdds"):
        continue
    r = requests.get("https://api.oddspapi.io/v4/odds", params={
        "apiKey": API_KEY, "fixtureId": f["fixtureId"]
    })
    if r.status_code != 200:
        print(f["fixtureId"], r.status_code, r.text[:200])
        time.sleep(1.0)
        continue
    o = r.json()
    rows = [
        {
            "fixture_id": f["fixtureId"],
            "match": f"{f['participant1Name']} v {f['participant2Name']}",
            "book": book, "market_id": int(mid),
            "outcome_id": int(oid), "price": p["price"],
        }
        for book, b in o.get("bookmakerOdds", {}).items()
        for mid, m in b["markets"].items()
        for oid, oc in m["outcomes"].items()
        for p in [oc["players"].get("0")]
        if p and p.get("active") and p["price"] > 0
    ]
    frames.append(pd.DataFrame(rows))
    time.sleep(1.0)  # one call per second to the same endpoint

slate = pd.concat(frames, ignore_index=True)
print(f"Slate: {len(slate):,} rows across {slate.fixture_id.nunique()} fixtures")
print(f"{slate.memory_usage(deep=True).sum() / 1e6:.0f} MB in memory")
print(slate.groupby("match").book.nunique().sort_values())

Slate: 766,644 rows across 10 fixtures
174 MB in memory
match
Hull City v Brentford FC                 272
Liverpool FC v Brighton & Hove Albion    273
Arsenal FC v Everton FC                  274
Aston Villa v Manchester City            274
Crystal Palace v Newcastle United        275
Coventry City v Fulham FC                275
Sunderland AFC v Leeds United            275
Chelsea FC v Tottenham Hotspur           276
Manchester United v AFC Bournemouth      276
Ipswich Town v Nottingham Forest         277

The full Premier League round took 30 seconds and fits in 174 MB of RAM, with 272 to 277 books on every fixture. Keep the one-second sleep between /odds calls, and never fire them in parallel: concurrent calls to the same endpoint come back 429 (rate limited). Check status_code before you read a body, because a 429 body is valid JSON too.

Why OddsPapi Pairs Well With Pandas

Capability	What it means for a pandas workflow
350+ bookmakers in one response	No feed merging. One `/v4/odds` call put 275 books on this fixture into one nested dict, ready to flatten.
Free historical data	The same flatten works on `/v4/historical-odds`, with a list of snapshots per outcome in place of one price. Backtest without buying snapshots.
One ID scheme across books	Every book’s Full Time Result is market 101, so a groupby across 275 books needs no name matching.
Exchanges and prediction markets in the same payload	Betfair Exchange, Kalshi, Polymarket, ProphetX, Novig and SX Bet flatten into the same DataFrame as the sportsbooks. Exchange depth lives under `exchangeMeta`.
Native player props	Props ship on the same endpoint, keyed by player ID. Pitfall 4 flattens them.

Common Pitfalls

1. Do not loop with `iterrows()`

Row-by-row Python is the slow path in pandas. Before you write for row in df.iterrows(), reach for groupby, transform, pivot_table or merge.

2. Do not trust the widest arb

The raw scan in Step 6 flagged 47 markets and the guarded scan kept 15. The guards removed the widest raw hits first, so run them on every scan.

3. Parse timestamps as UTC

Both changedAt on /odds and createdAt on /historical-odds are ISO strings in UTC. Parse with pd.to_datetime(..., utc=True) and compare against pd.Timestamp.now(tz="UTC"), never a naive local time.

4. Player props need their own DataFrame

The Step 1 snippet keeps only players["0"]. Props key each price by player ID and carry a playerName. Build a parallel frame:

props = pd.DataFrame([
    {"book": book, "market_id": int(mid), "outcome_id": int(oid),
     "player_id": pid, "player_name": p.get("playerName"), "price": p["price"]}
    for book, b in odds["bookmakerOdds"].items()
    for mid, m in b["markets"].items()
    for oid, o in m["outcomes"].items()
    for pid, p in o["players"].items()
    if pid != "0" and p.get("active") and p["price"] > 0
])

On the same Chelsea v Tottenham board that frame held 72,635 rows: 178 books pricing 73 players across 52 prop markets. Names come as “Last, First”, for example “Colwill, Levi”. The player props API tutorial goes deeper.

5. Historical odds need a list-aware flattener

/v4/historical-odds takes up to three bookmakers per call, names its top-level key bookmakers (not bookmakerOdds), and ships players["0"] as a list of snapshots. Use .get("0", []): prop outcomes have no "0" key, and a hard ["0"] raises KeyError on the first one.

hist = requests.get("https://api.oddspapi.io/v4/historical-odds", params={
    "apiKey": API_KEY,
    "fixtureId": "id1000001772221318",  # Everton v Chelsea, played 17 Oct
    "bookmakers": "pinnacle,bet365,singbet",  # max 3 per call
}).json()

hist_df = pd.DataFrame([
    {"book": book, "market_id": int(mid), "outcome_id": int(oid),
     "price": snap["price"], "ts": snap["createdAt"]}
    for book, b in hist["bookmakers"].items()
    for mid, m in b["markets"].items()
    for oid, o in m["outcomes"].items()
    for snap in o["players"].get("0", [])  # props are keyed by player ID
    if snap.get("active") and snap["price"] > 0
])
hist_df["ts"] = pd.to_datetime(hist_df["ts"], utc=True)

# Pinnacle's 1X2 close: the last active snapshot before kick-off
ko = pd.Timestamp("2026-10-17T11:30:00Z")
close = (hist_df[(hist_df.book == "pinnacle") & (hist_df.market_id == 101) & (hist_df.ts < ko)]
         .sort_values("ts").groupby("outcome_id").last())
print(close)

                book  market_id  price                               ts
outcome_id
101         pinnacle        101   3.00 2026-10-17 11:29:16.863000+00:00
102         pinnacle        101   3.67 2026-10-17 11:29:16.863000+00:00
103         pinnacle        101   2.37 2026-10-17 11:29:16.863000+00:00

That call returned 110,358 snapshot rows for the three books, back to 19 September. Pinnacle alone logged 1,220 snapshots on the 1X2, with 356 to 396 actual price changes per outcome. Count changes, not snapshots. For CSV export and a backtest loop, see Historical Odds CSV Export.

What to Build With This DataFrame

Value betting scanner: compare each book’s price against a fair-odds benchmark and flag positive-EV prices.
Consensus odds calculator: group duplicate feeds as in Step 4, then de-vig the consensus into a fair probability per outcome.
Kelly criterion stake sizing: size the stake on each +EV price your scanner finds.
Live odds dashboard: the same DataFrame in Streamlit with auto-refresh.
Steam move detector: use changedAt and the price history to catch sharp moves. For sub-second updates, swap polling for the WebSocket feed.

FAQ

Why use pandas instead of dicts and loops?

A long-format DataFrame turns each question (best price, margin, spread, arbs) into a groupby of a line or two. The dict version needs its own nested loops for every question.

How big does the odds DataFrame get?

One Premier League fixture flattened to about 79,500 match-market rows. A ten-fixture Premier League round came to 766,644 rows and 174 MB in memory, which a laptop handles. For many rounds, write each one to Parquet and load only what you need.

Why is `players["0"]` the convention?

Match markets (1X2, totals, handicaps, corners) are about the fixture, not one player, so OddsPapi files their price under the key "0". Player props key each price by the player’s ID. Filter on "0" to split match markets from props.

How do I keep stale prices out of the DataFrame?

Keep changedAt as a column. Drop a book when its newest price on the fixture is older than your window, and drop a price when it trails that book’s newest price by more than the same window. Two days out, 24 hours works; on match day, use an hour.

Can I store the DataFrame between runs?

Yes. Parquet keeps dtypes and loads faster than CSV: df.to_parquet("odds.parquet"). For long-running capture, write one file per fixture and poll: df.to_parquet(f"odds/{fixture_id}/{ts}.parquet").

Does this work with Polars?

Yes. The flatten is plain Python, so pass the same list of dicts to pl.DataFrame(...). The groupby and aggregation calls change name but keep the same shape.

Get Your API Key

Every recipe above ran on one call per fixture: 350+ bookmakers, exchanges and prediction markets in one payload, with free historical odds on the same key. Get your OddsPapi API key and flatten your first board.