Web Scraping With Pandas: Scrape Tables in One Line

I scrape a lot of tables, and for anything that’s a real HTML <table> I skip BeautifulSoup and reach for pandas. One function, pandas.read_html, reads every table on a page into DataFrames. This tutorial covers the whole loop: read a table, pick the right one, clean it, and export to CSV and Excel. Every snippet here ran against the S&P 500 list on Wikipedia in June 2026.

What does pandas.read_html do?

pandas.read_html parses all the <table> elements on a page and returns a list of DataFrames, one per table. You give it a URL, a file path, or a string of HTML, and it hands back ready-to-use tabular data with headers already set. It reads tables only, so it ignores prices in <div> grids, product cards, and anything that is not a real HTML table.

That single-purpose focus is the appeal. For tabular data you write one line instead of a loop over rows and cells.

How do you install pandas for read_html?

Install pandas and a parser with pip in one command:

pip install pandas lxml

read_html does not parse HTML on its own. It needs a parser backend, and lxml is the fastest and the one I use. Without lxml (or html5lib plus beautifulsoup4) installed, the call raises ImportError. I ran everything below on pandas 2.3.2 and lxml 6.0.2.

How do you scrape a table with read_html?

Pass the HTML to read_html and index the list it returns. The catch on many real sites is the fetch: Wikipedia rejects the default urllib User-Agent with a 403, so I fetch the page with Requests, set a normal User-Agent, and hand the HTML string to pandas:

import pandas as pd
import requests
from io import StringIO

url = "https://en.wikipedia.org/wiki/List_of_S%26P_500_companies"
html = requests.get(url, headers={"User-Agent": "Mozilla/5.0"}).text

tables = pd.read_html(StringIO(html), attrs={"id": "constituents"})
df = tables[0]

print("shape:", df.shape)
print("columns:", list(df.columns))
print("first symbol:", df.loc[0, "Symbol"])
print("first security:", df.loc[0, "Security"])
print(df[["Symbol", "Security", "GICS Sector"]].head(3).to_string(index=False))

When I ran it, it pulled the full constituents table:

shape: (503, 8)
columns: ['Symbol', 'Security', 'GICS Sector', 'GICS Sub-Industry', 'Headquarters Location', 'Date added', 'CIK', 'Founded']
first symbol: MMM
first security: 3M
Symbol            Security GICS Sector
   MMM                  3M Industrials
   AOS         A. O. Smith Industrials
   ABT Abbott Laboratories Health Care

Three details from that code:

• I wrap the HTML in StringIO. Passing a raw string still works but raises a FutureWarning in current pandas, and StringIO silences it.
• attrs={"id": "constituents"} tells pandas to read only the <table id="constituents">. More on that next.
• df.loc[0, "Symbol"] reads a single cell by row label and column name.

How do you pick the right table?

Use attrs for an exact HTML match and match for a fuzzy text match. A page often has several tables, and a bare read_html returns all of them. The S&P 500 page holds three:

all_tables = pd.read_html(StringIO(html))
print("total tables on page:", len(all_tables))
for i, t in enumerate(all_tables):
    print(f"  [{i}] shape {t.shape}")

total tables on page: 3
  [0] shape (503, 8)
  [1] shape (399, 6)
  [2] shape (11, 2)

Counting index positions is brittle because a page edit can reorder tables. Two arguments make the selection stable:

attrs={"id": "constituents"} is the precise choice here and returns exactly one table. match="Symbol" is the fuzzy fallback when a table has no id: it keeps every table whose text contains that word, and the constituents table comes back first. Reach for match when the page gives you nothing to grab by attribute.

How do you clean a scraped table?

Slice the columns you want, rename them, and let pandas keep the types it inferred. read_html already parses numeric columns as numbers, so the cleanup is mostly selection and naming:

clean = df[["Symbol", "Security", "GICS Sector", "Headquarters Location"]].copy()
clean.columns = ["symbol", "company", "sector", "hq"]

read_html typed the CIK column as int64 on its own, so no string-stripping was needed there. When a real site gives you messy cells (a $ on prices, stray whitespace, thousands separators), the usual fixes are df["price"].str.replace("$", "") followed by pd.to_numeric(...), and df["name"].str.strip() to trim whitespace. Selecting four columns and renaming them is enough for this table.

How do you export to CSV and Excel?

Call to_csv for CSV and to_excel for Excel, one line each:

clean.to_csv("sp500.csv", index=False)
clean.to_excel("sp500.xlsx", index=False)

Running both wrote the files and confirmed the row count:

wrote 503 rows
csv bytes: 29185
xlsx exists: True

Pass index=False so pandas does not add its row numbers as a leftover first column. to_excel needs the openpyxl package installed for .xlsx output (pip install openpyxl); to_csv has no extra dependency. The first rows of the CSV came out clean:

symbol,company,sector,hq
MMM,3M,Industrials,"Saint Paul, Minnesota"
AOS,A. O. Smith,Industrials,"Milwaukee, Wisconsin"
ABT,Abbott Laboratories,Health Care,"North Chicago, Illinois"

read_html vs BeautifulSoup for tables

For real HTML tables read_html wins on speed to write; for anything that is not a table, you need BeautifulSoup. Here is how I choose between them:

My rule is simple. If the data sits in a <table>, read_html gets it in one line. The moment you need data from <div> grids, single attributes, or odd nesting, switch to BeautifulSoup or XPath and CSS selectors for cell-level control.

When read_html gets blocked

read_html reads the HTML in the response, so two limits show up on real targets. The 403 I hit on Wikipedia is the mild version: a missing User-Agent, fixed by fetching with Requests. Heavier sites build their tables with JavaScript or block repeat visitors, and then there is no table in the HTML pandas receives. The pattern that keeps working is to get the rendered HTML another way, then feed the string to read_html. A scraper API like ChocoData handles that fetch at scale: it returns the rendered page from a universal endpoint or one of its 453 dedicated endpoints, and your read_html line stays exactly the same. I cover that whole tradeoff in the web scraping guide.

For tables that are already in the HTML, though, read_html is the shortest path I know. Point it at a page with a table, narrow it with attrs or match, and you have a DataFrame ready to export.