Convert the Grid Back into Data#

A <table> is one of the few places where a website hands you structure for free. Rows, columns and header labels are already encoded in the markup, and turning them into a list of dictionaries takes a few lines. When a page shows statistics, prices, schedules, rankings or comparison matrices, it very often uses a real table. The difficulty is not the simple case. It is merged cells, two-row headers, footnote markers, nested tables and "tables" built from <div>s. This lesson covers the simple case quickly and spends the rest on the ones that break scrapers.

The Anatomy of a Table#

A well-formed table has up to four zones: an optional <caption>, a <thead> with header cells (<th>), one or more <tbody> sections with data rows, and an optional <tfoot> for totals. Each row is a <tr> whose children are <th> or <td> cells. Rows can also start with a <th scope="row"> that labels the row rather than holding data.

import httpx
from bs4 import BeautifulSoup

html = httpx.get("https://example.com/stats", timeout=15).text
soup = BeautifulSoup(html, "lxml")
table = soup.select_one("table#stats")

headers = [th.get_text(strip=True) for th in table.select("thead th")]
records = []
for tr in table.select("tbody tr"):
    cells = [c.get_text(strip=True) for c in tr.find_all(["td", "th"], recursive=False)]
    records.append(dict(zip(headers, cells)))

print(headers)
print(records[0])

['Country', 'Population', 'Area (km²)', 'Capital']
{'Country': 'Norway', 'Population': '5,550,203', 'Area (km²)': '385,207', 'Capital': 'Oslo'}

find_all(["td", "th"], recursive=False) collects both cell types (so row headers are included) and only direct children, so a table nested inside a cell does not leak its cells into the outer row. If there is no <thead>, the first <tr> usually holds the headers: take it, then slice it off the rows.

One silent failure to know about: zip stops at the shorter list. If a row has fewer cells than there are headers, the missing columns simply vanish from that record instead of raising an error. That is the first symptom of merged cells.

Finding the Right Table#

Pages often contain several tables, including layout tables from older sites. Select by something stable rather than by position:

  • An id or a distinctive class: table#stats, table.wikitable.
  • The caption text: find the <caption> containing "Population", then take caption.find_parent("table").
  • A header you know must be present: loop over soup.find_all("table") and keep the one whose first row contains "Population".

Selecting soup.find_all("table")[3] works until the site adds a banner table above it. See CSS selectors for building selectors that survive redesigns.

Rowspan and Colspan: Rebuild the Grid#

Real tables merge cells. rowspan="2" makes one cell occupy the same column in two rows; colspan="3" makes it span three columns. The HTML for the second row then has fewer cells, because the merged cell is only written once. Ignore spans and every value after the merged cell shifts one column to the left.

The reliable fix is to place each cell into a coordinate grid, marking every slot it covers, and skipping slots already filled by a span from a row above:

def span(cell, attr):
    try:
        return max(1, int(cell.get(attr, 1)))
    except ValueError:
        return 1                                   # malformed values like "2;"

def table_to_grid(table):
    rows = table.select(":scope > thead > tr, :scope > tbody > tr, :scope > tr, :scope > tfoot > tr")
    grid = {}
    for r, tr in enumerate(rows):
        c = 0
        for cell in tr.find_all(["th", "td"], recursive=False):
            while (r, c) in grid:                   # slot taken by a rowspan from above
                c += 1
            text = " ".join(cell.get_text(" ").split())
            for dr in range(span(cell, "rowspan")):
                for dc in range(span(cell, "colspan")):
                    grid[(r + dr, c + dc)] = text
            c += span(cell, "colspan")
    if not grid:
        return []
    n_rows = max(r for r, _ in grid) + 1
    n_cols = max(c for _, c in grid) + 1
    return [[grid.get((r, c), "") for c in range(n_cols)] for r in range(n_rows)]

Try it on a table with both kinds of merge:

html = '''
<table>
  <tr><th>Region</th><th>City</th><th colspan="2">Sales</th></tr>
  <tr><td rowspan="2">North</td><td>Oslo</td><td>120</td><td>130</td></tr>
  <tr><td>Bergen</td><td>80</td><td>95</td></tr>
</table>'''
table = BeautifulSoup(html, "lxml").table
for row in table_to_grid(table):
    print(row)

['Region', 'City', 'Sales', 'Sales']
['North', 'Oslo', '120', '130']
['North', 'Bergen', '80', '95']

"North" is copied into the row it covers, and the colspan header is repeated for both columns. Notes on the non-obvious lines:

  • The :scope > ... selector takes rows of this table only, in document order, including header and footer sections. A plain find_all("tr") would also collect rows of nested tables.
  • " ".join(cell.get_text(" ").split()) joins text from child elements with a space (so <br>-separated lines do not run together) and collapses all whitespace, including non-breaking spaces, which Python treats as whitespace.
  • max(1, ...) guards against rowspan="0", which in HTML means "to the end of the section" and would otherwise make the cell disappear.

After building the grid, check that it is rectangular with the width you expect. If one row is wider than the rest, a span was mis-declared in the source HTML.

Two-Row Headers#

Statistical tables often have grouped headers: a top row "2025" spanning "Q1 Q2 Q3 Q4" in the row below. After table_to_grid, each column has one value per header row. Combine them into a single name:

def combine_headers(grid, n_header_rows):
    names = []
    for col in zip(*grid[:n_header_rows]):
        parts = []
        for p in col:
            if p and p not in parts:
                parts.append(p)
        names.append(" / ".join(parts))
    return names

grid = table_to_grid(table)
headers = combine_headers(grid, 2)          # e.g. 'Region', '2025 / Q1', '2025 / Q2'
records = [dict(zip(headers, row)) for row in grid[2:]]

Removing repeated parts keeps "Region" from becoming "Region / Region" when a header cell has rowspan="2".

The Shortcut: pandas.read_html#

pandas.read_html implements the same span handling and returns a list of DataFrames, one per table. It is the fastest route when you want the whole table and it is well formed.

from io import StringIO
import pandas as pd

frames = pd.read_html(StringIO(html), match="Population", thousands=",")
df = frames[0]
records = df.to_dict("records")

Practical details:

  • Fetch the page yourself (with your session, headers and rate limiting) and pass the HTML wrapped in StringIO. Passing a URL lets pandas fetch it without any of your client settings, and recent pandas versions deprecate passing raw HTML strings directly.
  • match= keeps only tables containing that text, which is sturdier than picking by index.
  • thousands="," converts "5,550,203" to a number. Columns with any unparseable value stay as strings, so check df.dtypes.
  • Two-row headers become a MultiIndex; flatten them with df.columns = [" / ".join(map(str, c)) for c in df.columns].
  • Links are lost by default. extract_links="body" returns (text, href) tuples for body cells.

Use the manual approach when you need links or attributes from cells, when cells contain nested markup you need to interpret, or when the table is too irregular for pandas to guess.

Clean Cell Values Before Storing#

Tables are formatted for people. Before cells go to storage, deal with:

  • Footnote markers. "5,550,203[3]" or a trailing <sup>. Remove sup elements with for s in table.select("sup"): s.decompose() before extraction.
  • Placeholders. "—", "–", "n/a", "..." and empty cells all mean missing. Map them to None explicitly rather than letting them fail number parsing later.
  • Units and symbols. "$1,234.56", "12%", "385,207 km²". Strip them into typed values, and put the unit in the column name.
  • Negative numbers. Some financial tables write "(3.4)" or use the Unicode minus sign instead of a hyphen.
  • Hidden text. Some tables include visually hidden spans for screen readers or sort keys (<span style="display:none">), which appear in get_text(). Inspect the raw cell HTML when a value looks doubled.

The full toolkit for typed conversion is in data cleaning and validation.

When the "Table" Is Not a Table#

Many modern interfaces render grids from <div>s. Good ones still describe the structure with ARIA roles for accessibility, and those roles are more stable than generated class names:

header = [h.get_text(strip=True) for h in soup.select('[role="columnheader"]')]
rows = []
for row in soup.select('[role="row"]'):
    cells = [c.get_text(strip=True) for c in row.select('[role="cell"], [role="gridcell"]')]
    if cells:
        rows.append(dict(zip(header, cells)))

Without roles, find the repeated element that forms a row and the labelled children that form cells, often via data- attributes. Always print len(rows) before trusting the selector: zero means the markup differs from what you assumed, or the grid is drawn by JavaScript. For JavaScript-rendered grids, look in the Network tab first. The grid is almost always filled from a JSON endpoint that already contains typed values, and scraping that is cleaner than scraping the rendered cells. See XHR and API reverse engineering.

Large grids are also often virtualised: only the rows visible on screen exist in the DOM. If you scrape the rendered page you get the first 30 rows of 5,000, which is another reason to go to the API.

Verify the Result#

Before shipping a table scraper, check three numbers: every row has the same number of fields as the header, the row count matches any count the page shows ("Showing 1 to 50 of 312"), and any totals row in <tfoot> equals the sum of the column you extracted. If the table is split across pages, apply the pagination patterns and run the row count check on the combined result.

Practice#

Pick a public reference table that has merged cells and a two-row header. Extract it with table_to_grid and with pandas.read_html, compare the two outputs cell by cell, and write a validation that fails if any row has a different width than the header.