Pandas Read HTML Tables

Pandas'pd.read_html()The function can automatically parse HTML table data in web pages and convert it into a DataFrame. This is very useful in scenarios such as web scraping, stock price analysis, and obtaining financial information.

Basic Usage

read_html()The function finds and reads all HTML table elements in a web page, returning a list of DataFrames.

Read a Single Table

Example

import pandas as pd

# Read all tables on the web page
# Note: requires lxml and beautifulsoup4 libraries
# pip install lxml beautifulsoup4

# Read from URL
tables = pd.read_html("https://example.com/table.html")

# Check the number of tables found
print(f"Found {len(tables)} tables")

# Get the first table
df = tables[0]
print(df.head())

Read Multiple Tables

A web page may contain multiple tables.read_html()Returns a list, which can be selected by index:

Example

import pandas as pd

# Assume the web page has multiple tables
tables = pd.read_html("https://example.com/data.html")

# Iterate over all tables
for i, table in enumerate(tables):
    print(f"\n=== Table {i+1} ===)
    print(f"Shape: {table.shape}")
    print(table.head(3))

Detailed Explanation of Common Parameters

Parameter Description Example
io Input source, can be a URL, file path, or string "page.html"
match Regex matching, filter tables containing specific text match="销售额"
header Specify the header row index header=0
attrs Filter tables by HTML attributes attrs={"id": "table1"}
skiprows Skip specified rows skiprows=[0, 1]
na_values Specify strings treated as missing values na_values=["N/A"]

Use the match Parameter to Filter Tables

Example

import pandas as pd

# Use the match parameter to only return tables containing specific text
# For example: only read tables containing the word "stock"
tables = pd.read_html(
    "https://example.com/finance.html",
    match="stock"
)

if tables:
    df = tables[0]
    print(df)

Use the attrs Parameter to Precisely Locate Tables

Example

import pandas as pd

# Locate tables via HTML attributes
# For example: get the table with id="stock-table"
tables = pd.read_html(
    "https://example.com/data.html",
    attrs={"id": "stock-table"}  # Find the table element with id="stock-table"
)

# Or locate by class
tables = pd.read_html(
    "https://example.com/data.html",
    attrs={"class": "data-table"}
)

df = tables[0]
print(df)

Practical Example: Scraping Stock Data from a Web Page

The following example demonstrates how to scrape stock list data from a financial website:

Example

import pandas as pd
from io import StringIO


# Create mock HTML content for demonstration
# Replace with a real URL in actual use
html_content = '''
<table border="1">
    <tr>
<th>Stock Code</th>
<th>Stock Name</th>
<th>Closing Price</th>
<th>Change Percent</th>
    </tr>
    <tr>
        <td>600519</td>
<td>Kweichow Moutai</td>
        <td>1850.00</td>
        <td>2.35%</td>
    </tr>
    <tr>
        <td>000858</td>
<td>Wuliangye</td>
        <td>185.50</td>
        <td>-1.20%</td>
    </tr>
    <tr>
        <td>601318</td>
<td>Ping An of China</td>
        <td>52.30</td>
        <td>0.85%</td>
    </tr>
</table>
'''

# Read table from string

tables = pd.read_html(StringIO(html_content))

df = tables[0]

print("Original table:")
print(df)
print()

# Data cleaning: process the change percent column
df["Change Percent"] = df["Change Percent"].str.replace("%", "").astype(float)
df["Closing Price"] = df["Closing Price"].astype(float)

print("Cleaned data:")
print(df)

The role of StringIO is to disguise a string as a file object, so pandas processes it according to the logic of reading a file stream rather than mistaking it for a path or URL.

Output:

原始表格：
     股票代码  股票名称     收盘价     涨跌幅
0  600519  贵州茅台  1850.0   2.35%
1     858   五粮液   185.5  -1.20%
2  601318  中国平安    52.3   0.85%

清洗后的数据：
     股票代码  股票名称     收盘价   涨跌幅
0  600519  贵州茅台  1850.0  2.35
1     858   五粮液   185.5 -1.20
2  601318  中国平安    52.3  0.85

Read Wikipedia Tables

Example

import pandas as pd

# Read tables from a Wikipedia page
# Using the list of countries by population as an example
url = "https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)"

try:
    tables = pd.read_html(url)
    print(f"This page has {len(tables)} tables in total")

    # Usually the first table is the one we need
    df = tables[0]
    print(df.head(10))
except Exception as e:
    print(f"Read failed: {e}")
    print("Please ensure the necessary libraries are installed: pip install lxml beautifulsoup4")

Data Cleaning and Processing

Tables read from web pages usually need further cleaning before they can be used for analysis.

Common Cleaning Operations

Example

import pandas as pd
from io import StringIO

# Simulate a table containing various dirty data
html_content = '''
<table>
<tr><th>Date</th><th>Product</th><th>Sales</th><th>Remarks</th></tr>
<tr><td>2024-01-01</td><td>Product A</td><td>1,234</td><td>N/A</td></tr>
<tr><td>2024-01-02</td><td>Product B</td><td>567</td><td>Out of stock</td></tr>
<tr><td>2024-01-03</td><td>Product C</td><td>--</td><td>Not counted</td></tr>
</table>
'''

df = pd.read_html(StringIO(html_content))[0]
print("Original data:")
print(df)
print()

# Cleaning steps
# 1. Rename columns
df.columns = ["Date", "Product", "Sales", "Remarks"]

# 2. Handle missing values
df = df.replace(["N/A", "--", "Not counted", "Out of stock"], pd.NA)

# 3. Clean numeric columns (remove commas)
df["Sales"] = df["Sales"].str.replace(",", "").replace(pd.NA, None)
df["Sales"] = pd.to_numeric(df["Sales"], errors="coerce")

# 4. Convert dates
df["Date"] = pd.to_datetime(df["Date"])

print("After cleaning:")
print(df)
print("\n"Data types:")
print(df.dtypes)

Output:

原始数据：
           日期   产品    销量   备注
0  2024-01-01  产品A  1234  NaN
1  2024-01-02  产品B   567   缺货
2  2024-01-03  产品C    --  未统计

清洗后：
          日期   产品      销量    备注
0 2024-01-01  产品A  1234.0   NaN
1 2024-01-02  产品B   567.0  <NA>
2 2024-01-03  产品C     NaN  <NA>

数据类型：
日期    datetime64[ns]
产品            object
销量           float64
备注            object
dtype: object

Notes and Common Issues

1. Install dependency libraries

read_html()Requiredlxmlandbeautifulsoup4libraries:

pip install lxml beautifulsoup4

2. Network issues

When reading a network URL, you may encounter network latency or access restrictions. It is recommended to add appropriate timeout settings or use local caching.

3. Complex table structure

Some web page tables use complex structures such as merged cells and nested tables, which may cause parsing failures. In this case, you can try using BeautifulSoup to parse the HTML directly.

4. Data integrity

Web page data may change at any time. It is recommended to verify data integrity after reading and compare it with the original data source.

When scraping web data, please comply with the website's robots.txt rules and relevant laws and regulations, and do not make frequent requests to avoid placing a burden on the server.

Summary

pd.read_html()It is a powerful tool for scraping web table data, quickly converting HTML tables into DataFrames. In actual use, you need to pay attention to installing dependency libraries, handling network issues, cleaning dirty data, and other issues. Scraped data usually needs further processing before it can be used for analysis. It is recommended to use it together with Pandas' data cleaning features.

Other Extensions