Reading JSON, Excel, HTML & SQL

CSV is the most common format, but cricket data also arrives as JSON from web APIs (live scores, player profiles), as Excel workbooks from a team analyst, as HTML tables on stats websites, and from SQL databases behind a web app. Pandas has a read_* function for each one, and they all return the same thing — a DataFrame — so everything you learn later works no matter where the data came from.

Definition

The pd.read_* functions are pandas' family of readers: each one understands a single file format or data source, parses it, and returns a DataFrame (read_html returns a list of DataFrames).

• read_json — JSON text: a list of records, a dict of columns, or "JSON Lines" (one JSON object per line).
• read_excel — .xlsx/.xls workbooks; one sheet at a time or several at once. Needs the openpyxl package for .xlsx.
• read_html — finds every <table> in a web page. Needs lxml (or beautifulsoup4 + html5lib).
• read_sql — runs an SQL query on a database connection and returns the result.

Syntax

pd.read_json(path_or_buffer, orient=None, lines=False, dtype=None)
pd.json_normalize(data, sep=".")
pd.read_excel(io, sheet_name=0, header=0, usecols=None, nrows=None)
pd.ExcelFile(io).sheet_names
pd.read_html(io, match=".+")
pd.read_sql(sql, con)

Example — read_json with a list of records

The most common API shape is a list of objects — one object per row:

import pandas as pd
import io

records = '[{"player": "Virat Kohli", "team": "RCB", "runs": 741}, {"player": "Travis Head", "team": "SRH", "runs": 567}, {"player": "Sunil Narine", "team": "KKR", "runs": 488}]'
df = pd.read_json(io.StringIO(records))
print(df)
print(df.dtypes)

         player team  runs
0   Virat Kohli  RCB   741
1   Travis Head  SRH   567
2  Sunil Narine  KKR   488
player      str
team        str
runs      int64
dtype: object

How it works: every object became a row and every key became a column. pandas auto-detects the layout, so orient is rarely needed when reading. A "dict of columns" such as {"player": {"0": "Virat Kohli", ...}, "runs": {"0": 741, ...}} is also recognised automatically.

Note (pandas 3): passing the JSON text directly — pd.read_json(records) — no longer works. pandas treats any plain string as a file path and raises FileNotFoundError: File [{"player": ... does not exist. Wrap literal JSON in io.StringIO(...) (pandas 2 only warned about this).

Example — JSON Lines and nested JSON

Logs and big exports often use JSON Lines: one JSON object per line, no surrounding [ ].

overs = """{"over": 1, "runs": 8}
{"over": 2, "runs": 3}
{"over": 3, "runs": 14}
"""
print(pd.read_json(io.StringIO(overs), lines=True))

nested = [{"player": "Virat Kohli", "stats": {"runs": 741, "sr": 154.7}},
          {"player": "Travis Head", "stats": {"runs": 567, "sr": 191.55}}]
print(pd.json_normalize(nested))

   over  runs
0     1     8
1     2     3
2     3    14
        player  stats.runs  stats.sr
0  Virat Kohli         741    154.70
1  Travis Head         567    191.55

How it works: lines=True reads each line as its own record. For nested JSON (a dict inside a dict), pd.json_normalize flattens the inner keys into columns named parent.child — here stats.runs and stats.sr. Note that json_normalize takes Python objects (lists/dicts), not text; use json.loads(text) first if you have a string.

Example — read_excel and sheet_name (not runnable in the browser)

Excel reading needs the openpyxl package, which the browser playground does not have, so this one is for your own computer (pip install openpyxl):

path = r"C:\Users\Admin\Desktop\data\ipl_stats.xlsx"

pd.ExcelFile(path).sheet_names               # list the sheets first, e.g. ['Batting', 'Bowling']
batting = pd.read_excel(path)                 # first sheet (sheet_name=0)
bowling = pd.read_excel(path, sheet_name="Bowling")
second  = pd.read_excel(path, sheet_name=1)   # by position
both    = pd.read_excel(path, sheet_name=["Batting", "Bowling"])   # dict of DataFrames
every   = pd.read_excel(path, sheet_name=None)                     # ALL sheets, dict

How it works: a workbook can hold many sheets, so sheet_name picks which one. With a single name/position you get one DataFrame; with a list or None you get a dict like {"Batting": DataFrame, "Bowling": DataFrame}. pd.ExcelFile(path).sheet_names (used in Munna's notebook) is the quick way to discover sheet names before reading. All the read_csv ideas — header, usecols, nrows, skiprows, dtype, na_values — work the same here.

read_html and read_sql (brief)

pd.read_html(url_or_html) scans a page for <table> tags and returns a list of DataFrames, one per table:

tables = pd.read_html("https://en.wikipedia.org/wiki/List_of_Indian_Premier_League_seasons_and_results")
print(len(tables))     # how many tables were found
results = tables[0]    # pick the one you need (use match="Winner" to filter)

It needs lxml (or beautifulsoup4 + html5lib) and internet access, so it can't run in the playground.

pd.read_sql(query, connection) runs a query and gives you the result as a DataFrame. With Python's built-in sqlite3:

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE players (name TEXT, team TEXT, runs INTEGER)")
conn.executemany("INSERT INTO players VALUES (?, ?, ?)",
                 [("Virat Kohli", "RCB", 741), ("Travis Head", "SRH", 567), ("Sunil Narine", "KKR", 488)])

df = pd.read_sql("SELECT name, runs FROM players WHERE runs > 500", conn)
print(df)
conn.close()

          name  runs
0  Virat Kohli   741
1  Travis Head   567

For PostgreSQL/MySQL you pass a SQLAlchemy engine instead of a sqlite3 connection. If you know SQL (see the SQL notes), this is the bridge: filter in SQL, analyse in pandas.

Comparison — which reader?

Common mistakes

• ❌ pd.read_json('[{"a": 1}]') in pandas 3 → FileNotFoundError. ✅ pd.read_json(io.StringIO('[{"a": 1}]')).
• ❌ ValueError: Expected object or value / "Trailing data" on a JSON Lines file. ✅ Add lines=True.
• ❌ Columns full of dicts after read_json on nested data. ✅ Load with json.loads and flatten with pd.json_normalize.
• ❌ ImportError: Missing optional dependency 'openpyxl'. ✅ pip install openpyxl — pandas needs it for .xlsx.
• ❌ Treating read_html(...) as a DataFrame. ✅ It returns a list — index it: tables[0].

Interview tip

Q: How do you read every sheet of an Excel workbook at once? A: pd.read_excel(path, sheet_name=None) returns a dict {sheet_name: DataFrame}. You can then loop over .items(), or combine them with pd.concat(sheets.values(), ignore_index=True) if they share the same columns.