0Pricing
Pandas & NumPy Academy · Lesson

Reading Excel and JSON Files

Import Excel workbooks with pd.read_excel and JSON records with pd.read_json, handling common format variations.

Reading Excel and JSON Files is a free Pandas & NumPy Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Pandas & NumPy Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Excel and JSON Matter

While CSV is the universal format, Excel files (.xlsx/.xls) are the dominant format for business data shared via email and reporting tools. JSON is the native format of REST APIs and NoSQL databases. Pandas provides pd.read_excel() and pd.read_json() that mirror the CSV reader's API, so skills transfer directly.

import pandas as pd

# The three most common load functions share the same philosophy
df_csv   = pd.read_csv('data.csv')
df_excel = pd.read_excel('data.xlsx')
df_json  = pd.read_json('data.json')

Installing the Excel Engine

Reading Excel files requires an engine: openpyxl for .xlsx files (modern format) and xlrd for older .xls files. Install with pip install openpyxl. Pandas auto-selects the engine based on the file extension. For writing, use xlsxwriter for advanced formatting. Without the engine installed, read_excel() raises a ModuleNotFoundError.

# Install the required engine:
# pip install openpyxl          # for .xlsx (modern)
# pip install xlrd==1.2.0       # for .xls (legacy)

import pandas as pd
df = pd.read_excel('report.xlsx', engine='openpyxl')

Selecting a Sheet with sheet_name

Excel workbooks can have multiple sheets. sheet_name= specifies which one to read: pass a string (sheet name), an integer (0-based position), or a list to read multiple sheets into a dict. Pass sheet_name=None to read all sheets into a dict keyed by sheet name. Inspecting the available sheets with pd.ExcelFile('file.xlsx').sheet_names is a good starting point.

import pandas as pd

# Read the second sheet
df = pd.read_excel('workbook.xlsx', sheet_name=1)

# Read by name
df = pd.read_excel('workbook.xlsx', sheet_name='Sales')

# Inspect available sheets
xl = pd.ExcelFile('workbook.xlsx')
print(xl.sheet_names)  # ['Summary', 'Sales', 'Costs']

Common Excel Parameters

pd.read_excel() supports most of the same parameters as read_csv(): header=, index_col=, usecols=, skiprows=, dtype=, and nrows=. For Excel, usecols also accepts an Excel column range string like 'A:C' or 'A,C,E', which is convenient when the column letters are known from the spreadsheet layout.

import pandas as pd

df = pd.read_excel(
    'sales_report.xlsx',
    sheet_name='Q1',
    header=2,          # header is on row 3 (0-indexed)
    usecols='A:D',     # Excel column range
    skiprows=[3, 4],   # skip rows 4 and 5
    nrows=100
)

Reading JSON: Orient Formats

pd.read_json() reads JSON into a DataFrame. The orient= parameter specifies the JSON structure: 'records' (list of row dicts), 'columns' (dict of column arrays, the default), 'index' (dict of row dicts keyed by index), or 'values' (raw array). Most REST APIs return 'records' format — always inspect the raw JSON first to determine the correct orient.

import pandas as pd

# REST API response: list of records
json_str = '[{"name": "Alice", "age": 30}, {"name": "Bob", "age": 25}]'
df = pd.read_json(json_str, orient='records')
print(df)

Reading JSON from a File or URL

pd.read_json() accepts a file path, a URL, or a JSON string directly. For deeply nested JSON (e.g., API responses with nested objects), use json_normalize() from the pandas.io.json module instead, which flattens nested dicts into columns with dot-separated names.

import pandas as pd
from pandas.io.json import json_normalize

# From a file
df = pd.read_json('events.json')

# Flatten nested JSON
nested = [{'id': 1, 'user': {'name': 'A', 'age': 30}},
          {'id': 2, 'user': {'name': 'B', 'age': 25}}]
df_flat = json_normalize(nested)
print(df_flat.columns.tolist())  # ['id', 'user.name', 'user.age']

JSON Lines Format

JSON Lines (NDJSON) stores one JSON object per line, making it easy to stream large datasets. Use pd.read_json('file.jsonl', lines=True) to parse this format. It is common in log files, Kafka exports, and machine learning dataset formats. Each line must be a valid JSON object; malformed lines cause the read to fail.

import pandas as pd

# file.jsonl contains one JSON record per line:
# {"id": 1, "event": "click"}
# {"id": 2, "event": "view"}
df = pd.read_json('events.jsonl', lines=True)
print(df)

Handling Date Parsing in JSON

JSON has no native date type — dates are stored as strings or Unix timestamps (milliseconds or seconds since epoch). Set convert_dates=True (default) to let Pandas attempt auto-conversion of columns whose names contain 'date', 'time', or 'at'. For custom column names or timestamps, convert explicitly with pd.to_datetime(df['col'], unit='ms').

import pandas as pd

# Unix milliseconds timestamp column
df = pd.read_json('events.json', orient='records')
df['created_at'] = pd.to_datetime(df['created_at'], unit='ms')
print(df['created_at'].dtype)  # datetime64[ns]

Comparing Excel and JSON Gotchas

Excel gotchas: merged cells produce NaN rows, hidden rows/columns are included in the output, and number formatting (e.g., dates stored as floats) must be corrected after loading. JSON gotchas: inconsistent field presence across records produces NaN, and integer keys in a JSON object become string column names.

import pandas as pd

# Excel date stored as float (Excel serial date)
df = pd.read_excel('old_report.xls')
# If dates appear as floats (e.g., 44927.0), convert:
# from xlrd import xldate_as_datetime
# df['date'] = df['date'].apply(lambda x: xldate_as_datetime(x, 0))

pd.ExcelFile for Multiple Sheets

When you need to read multiple sheets from the same file efficiently, open a pd.ExcelFile context manager and call parse(sheet_name) for each sheet. This avoids re-opening and re-parsing the file for each sheet — important for large workbooks. The context manager closes the file handle automatically.

import pandas as pd

with pd.ExcelFile('annual_report.xlsx') as xf:
    df_q1 = xf.parse('Q1')
    df_q2 = xf.parse('Q2')

print(df_q1.shape, df_q2.shape)

Using requests to Load JSON APIs

For REST API data, use the requests library to fetch JSON and pass the parsed Python object to pd.DataFrame() or pd.json_normalize(). This pattern separates the HTTP concern from the data parsing concern and gives you access to headers, authentication, and pagination before Pandas touches the data.

import pandas as pd
import requests

response = requests.get('https://api.example.com/records')
data = response.json()   # Python list of dicts
df = pd.DataFrame(data)
print(df.head())

Quick Check

Test your understanding of reading Excel and JSON files with Pandas from this lesson.

Lesson Recap

In this lesson you learned: pd.read_excel() reads xlsx files and lets you specify which sheet to load via sheet_name, pd.read_json() handles multiple JSON structures controlled by the orient parameter, and json_normalize() flattens nested JSON objects into a flat DataFrame. Next up we export DataFrames to CSV and Excel files for sharing and downstream processing.

Frequently asked questions

Is the “Reading Excel and JSON Files” lesson free?

Yes — the full text of “Reading Excel and JSON Files” is free to read here on the web, and the Pandas & NumPy Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Pandas & NumPy Academy course, upgrade to CoddyKit PRO.

What will I learn in “Reading Excel and JSON Files”?

Import Excel workbooks with pd.read_excel and JSON records with pd.read_json, handling common format variations. You practise Pandas & NumPy Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Pandas & NumPy Academy?

No prior experience is required. Pandas & NumPy Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Reading Excel and JSON Files” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Pandas & NumPy Academy lesson?

Yes. Every Pandas & NumPy Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Reading CSV Files
  2. Reading Excel and JSON Files
  3. Writing DataFrames to Files
  4. Reading from URLs and StringIO
← Back to Pandas & NumPy Academy