Read JSON and HTML Tables

When your data arrives as a nested JSON API response or a dozen HTML tables scraped from a webpage, pandas can transform it into clean DataFrames with almost no effort. The pain: manually flattening JSON dicts or listing every <td> tag wastes hours and invites subtle bugs. This lesson shows you how pd.read_json() and pd.read_html() do that heavy lifting, so you can spend your time analyzing, not parsing.

The problem this lesson solves

Real-world data doesn't always come in neat CSV files. APIs return JSON with nested objects and arrays, and publications publish statistics as HTML tables. Trying to read those with custom Python loops is slow, error-prone, and you'll reinvent the wheel. Without pandas' built-in readers, you end up writing hundreds of lines of dictionary-key-checking or BeautifulSoup soup.select() logic just to get tabular data into a DataFrame.

The pain it eliminates: - Flattening nested JSON by hand using loops or ugly list comprehensions. - Handling malformed JSON with try/except blocks before you even see the data. - Scraping HTML tables with regex or manual string splitting — fragile and slow. - Losing the type inference and convenient indexing that DataFrame offers.

Pandas gives you read_json() and read_html() — two high-level, battle-tested functions that parse these formats and return DataFrame (or list of DataFrames) ready for analysis.

Core concept / mental model

Think of read_json() and read_html() as adapter functions. They take a file-like source or raw string in one format and convert it into pandas' central tabular structure, the DataFrame. You tell them what format you have; they handle the messy details.

• pd.read_json() – Parses JSON (string, file, or URL) and creates a DataFrame. It can interpret the JSON structure in several orientations (split, records, index, columns, table, values) depending on how your data is organized.
• pd.read_html() – Reads all <table> elements from an HTML document (URL, file, or raw HTML string) and returns a list of DataFrames (one per table). It uses parser libraries (like lxml or html5lib) under the hood.

A helpful analogy: JSON is like a messy physical inbox — sometimes letters are nested in boxes within boxes. read_json is your assistant who knows how to flatten certain inbox layouts. HTML tables are like a catalog with multiple product pages; read_html reads each page (table) and gives you a stack of tidy spreadsheets.

Definitions to keep in your pocket

• DataFrame: A 2D tabular data structure with labeled rows and columns — pandas' heart.
• Orientation (JSON): The layout of the JSON relative to how pandas should map it to rows and columns.
• List of DataFrames: read_html returns a list because a single HTML page can contain multiple tables.

How it works step by step

1. Reading JSON with pd.read_json()

Step one: understand your JSON's structure. Is it a flat array of objects? A dict of lists? Nested objects? That determines the orient parameter.

• orient='records' – Each JSON object is a row. Most common for API responses.
• orient='split' – JSON has keys like index, columns, and data — good for round-tripping DataFrames.
• orient='index' – JSON keys become DataFrame index labels.
• orient='table' – A full pandas table schema with index and columns metadata.
• orient='values' – Just a flat array of arrays; DataFrame gets default integer columns.

Step two: call pd.read_json() with your source (string, path, or URL). pandas tries to infer orientation automatically, but it pays to be explicit.

Step three: inspect the result with df.head() or df.info().

2. Reading HTML tables with pd.read_html()

Step one: pass the HTML source — a URL (HTTP request handled internally), an HTML string, or a file handle.

Step two: parse all tables. Since multiple tables may exist, read_html returns a list of DataFrames. You often need to select the right one.

Step three: use df.iloc[2] or loop to check each table. Optionally, use match (regex) or attrs to target a specific table.

3. Clean up and analyze

Once you have a DataFrame, the world of pandas is open: you can handle missing values, rename columns, or merge with other frames — all without returning to the raw file.

Hands-on walkthrough

Let's put theory into action with two complete examples you can run yourself.

Example 1: Reading a JSON API response

import pandas as pd
import json

# Sample JSON as a string (emulating an API response)
json_data = '''
[
  {"name": "Alice", "score": 88},
  {"name": "Bob", "score": 72},
  {"name": "Charlie", "score": 93}
]
'''

# Read JSON with explicit orientation for array of objects
df = pd.read_json(json_data, orient='records')

print(df)
print('\nData types:')
print(df.dtypes)

Expected output:

      name  score
0    Alice     88
1      Bob     72
2  Charlie     93

Data types:
name     object
score     int64
dtype: object

Example 2: Reading HTML tables from a webpage

import pandas as pd

# Sample HTML with a simple table
html_string = '''
<table>
  <tr><th>Product</th><th>Price</th></tr>
  <tr><td>Laptop</td><td>1200</td></tr>
  <tr><td>Mouse</td><td>25</td></tr>
</table>
'''

# read_html returns a list of DataFrames
table_list = pd.read_html(html_string)

print(f"Number of tables found: {len(table_list)}")

df_table = table_list[0]  # select the first table
print(df_table)

Expected output:

Number of tables found: 1
   Product  Price
0  Laptop   1200
1   Mouse     25

Pro tip: If you're working with an online HTML page, you can pass the URL directly: pd.read_html('https://example.com/data_table.html'). pandas will fetch and parse it.

Example 3: Flattening nested JSON

Let's tackle a common messy API response — nested objects.

import pandas as pd

nested_json = '''
{
  "report": "Q1 sales",
  "region": "North",
  "items": [
    {"id": 1, "details": {"product": "Widget", "qty": 10}},
    {"id": 2, "details": {"product": "Gadget", "qty": 5}}
  ]
}
'''

# read_json with default orient won't flatten nested dicts automatically
raw = pd.read_json(nested_json)
print("Raw DataFrame:")
print(raw[['id', 'details']])

# Use json_normalize to flatten nested 'items'
norm = pd.json_normalize(nested_json, 'items', ['report', 'region'])
print("\nFlattened DataFrame:")
print(norm)

Expected output:

Raw DataFrame:
   id  ...
0   1  ...
1   2  ...

Flattened DataFrame:
   id  details.product  details.qty      report      region
0   1          Widget           10   Q1 sales   North
1   2          Gadget            5   Q1 sales   North

Compare options / when to choose what

The table below compares the main read_json orientations and how they fit common data layouts.

For HTML, your choice is mainly about which parser to use (lxml vs html5lib) and whether to filter with match or attrs.

Key insight: Use orient='records' for most API data. Use orient='split' when you save DataFrames to JSON and want to restore them exactly.

When reading HTML, if the page has multiple tables, you must select the right one. That selection is a mini-decision: by index, by match regex, or by attrs (like {'class': 'data'}).

Troubleshooting & edge cases

Common errors and fixes

Edge cases

• Missing values: JSON nulls appear as NaN in DataFrame; HTML empty rows become rows of NaN—drop them with dropna() if needed.
• Type inference: Numeric strings stay as text. Convert with pd.to_numeric() after reading.
• Nested JSON: read_json won't flatten; you need json_normalize (or pd.json_normalize) as shown earlier.
• Header rows in HTML tables: Sometimes the first row is not a header; set header=None and assign column names manually.
• Mixed JSON structures: When JSON varies from record to record, pandas might create many columns or a lot of NaNs — consider json_normalize with max_level to flatten deeper.
• HTTP errors: When reading from a URL, you may get HTTPError — check response status first or use requests.get() for more control.

Pro tips

• Before reading JSON, always inspect a sample with json.loads() to understand structure — it saves debugging time.
• For HTML tables, if you get a LookupError about lxml, install it: pip install lxml or use engine='html5lib'.
• Use df.head() right after reading to sanity-check your columns and rows.

What you learned & what's next

You've mastered the core idea behind reading JSON and HTML tables with pandas, and you applied it with hands-on examples. Specifically you learned:

• The fundamental role of pd.read_json() and pd.read_html() as adapters.
• Common JSON orientations and how to choose the right one.
• How to extract tables from a webpage and select the one you need.
• How to troubleshoot typical parsing and orientation errors.

This skill removes the biggest barrier between you and the data locked in APIs or web pages. Now that you can load JSON and HTML into DataFrames, the natural next step is cleaning and reshaping that data — handling missing values, renaming columns, and aggregating. That's exactly where the next lesson in this track leads you, enabling you to turn raw parsed data into analysis-ready datasets.