Reading Data from CSV, Excel, and JSON with Pandas

The previous article built DataFrames by hand, from Python dictionaries — genuinely useful for learning, but real projects need to load data that already exists as a file. pandas read csv excel json functions — pd.read_csv, read_excel, read_json — are how you get there. This article goes past the one-liner default calls into the parameters that actually matter for real-world files: encoding, dtype, usecols, and the honest performance differences between formats.

From Manual DataFrames to Real-World Files

Where real data actually comes from

Creating DataFrames from Python dictionaries, as covered in the previous article, works well for learning and small examples — but real projects need to load data stored in files: CSV exports from spreadsheets or databases, Excel workbooks shared by colleagues, JSON returned from a web API.

What pandas handles for you

Pandas provides robust, purpose-built functions for reading each of these formats, intelligently handling data types, missing values, encoding issues, and structural quirks that would make manual parsing — writing your own file-reading and type-conversion logic by hand — genuinely tedious and error-prone.

What this article covers

read_csv(), read_excel(), and read_json(), the specific parameters that matter most for real, messy, real-world files (not just clean tutorial examples), and honest guidance on when each format is actually the right choice.

Reading CSV Files with read_csv()

The basic one-liner
import pandas as pd

df = pd.read_csv("employees.csv")
print(df.head())

This single line handles comma detection, header inference (using the first row as column names by default), and basic type guessing — genuinely works well, out of the box, for clean, well-formed files. Real-world CSVs, though, usually need considerably more control.

usecols: loading only the columns you need
df = pd.read_csv("employees.csv", usecols=["name", "department", "salary"])

If a CSV has twenty columns but you only need three, usecols loads only those specified columns — avoiding wasted memory holding data you'll never actually use. For a genuinely large file, this can meaningfully reduce both memory usage and load time.

dtype: preventing uncontrolled type inference
df = pd.read_csv("employees.csv", dtype={"employee_id": str})

This is a genuinely important, commonly-needed fix: without an explicit dtype, pandas' automatic type inference can silently convert something like an employee ID column into a float, if every ID happens to look numeric — turning "00123" into 123.0, silently losing the leading zero, and changing the value's actual meaning. Explicitly specifying dtype={"employee_id": str} forces that column to stay a string, exactly as intended, regardless of what pandas' automatic inference would have guessed on its own.

encoding: avoiding garbled text
df = pd.read_csv("employees.csv", encoding="utf-8")

Exactly the same reasoning covered in the earlier text files article applies here directly: without an explicit encoding, pandas falls back to a default that may not match the file's actual encoding, turning legitimate non-ASCII characters (accented letters, non-English text) into garbled, unreadable text — or raising an outright error. Always specify encoding="utf-8" explicitly (or whatever encoding you know the source file actually uses) rather than relying on a guessed default.

Handling files too large to fit in memory
# Sample just the first 1000 rows, for a quick look without loading everything
sample = pd.read_csv("huge_file.csv", nrows=1000)

# Process a huge file in manageable chunks
for chunk in pd.read_csv("huge_file.csv", chunksize=10000):
    process(chunk)   # each chunk is a DataFrame with (up to) 10,000 rows

nrows loads just the first N rows — genuinely useful for a quick look at a file's structure before committing to loading the whole thing. chunksize returns an iterator yielding successive chunks of the file as separate DataFrames, letting you process a file larger than available memory piece by piece, rather than attempting (and potentially failing) to load it all at once. This connects directly back to the memory-conscious, line-by-line processing pattern covered in the earlier CSV module article — chunksize is pandas' higher-level equivalent of that same underlying principle.

Reading Excel Files with read_excel()

Basic usage
df = pd.read_excel("report.xlsx")

This returns a DataFrame the same way read_csv() does — but it requires an additional dependency, openpyxl, installed alongside pandas:

pip install openpyxl

Without openpyxl installed, read_excel() raises an ImportError the moment you try to use it — worth knowing about upfront, since it's an easy thing to be caught off guard by if you've only ever used read_csv() before.

sheet_name: choosing which sheet to load
df = pd.read_excel("report.xlsx", sheet_name="Q1 Sales")

An Excel workbook can contain multiple sheets, and sheet_name lets you specify exactly which one to load — by name, or by numeric position (sheet_name=0 for the first sheet).

all_sheets = pd.read_excel("report.xlsx", sheet_name=None)
print(all_sheets.keys())   # dict_keys(['Q1 Sales', 'Q2 Sales', 'Summary'])
print(all_sheets["Q1 Sales"].head())

Passing sheet_name=None loads every sheet at once, returned as a dictionary mapping each sheet's name to its own DataFrame — genuinely convenient when you need to work with several (or all) sheets from the same workbook.

skiprows and usecols for messy report layouts
df = pd.read_excel("report.xlsx", skiprows=3, usecols="B:E")

Real-world Excel files exported from business reporting tools frequently have several title or header rows above the actual data table — a report title, a generation date, a blank row — none of which belong in the actual data. skiprows=3 skips exactly that many rows before pandas starts reading the real header and data. usecols="B:E" (using Excel's own column-letter notation) restricts loading to a specific column range, useful when a sheet has extra notes or unrelated data in columns you don't need.

An honest performance caveat

Worth stating plainly: read_excel() is significantly slower than read_csv(), since parsing Excel's binary/XML-based file format is inherently more computationally expensive than parsing plain, delimited text. For datasets with genuinely large row counts (millions of rows), the practical recommendation is converting the Excel file to CSV — or, for internal analytics pipelines, to Parquet, covered in Section 5 — before doing any heavy, repeated processing, rather than repeatedly calling read_excel() directly against the original file every time.

A caution about hidden formatting

Real-world spreadsheets frequently contain merged cells, hidden rows or columns, or formatting-driven layouts that don't map cleanly onto a simple, flat data table — these can cause genuinely unexpected parsing results (misaligned columns, unexpected NaN values, or data appearing in the wrong place entirely). If a loaded DataFrame looks structurally wrong immediately after loading, it's worth opening the original Excel file directly and checking for exactly this kind of non-tabular formatting before assuming there's a bug in your reading code.

Reading JSON Data with read_json()

Why JSON needs special handling

Big datasets are often stored or extracted as JSON — particularly from web APIs, as covered in the earlier JSON files article. But JSON's inherently object-like, nested structure doesn't map onto a flat, tabular DataFrame nearly as directly as CSV's row-and-column layout does.

Basic usage
df = pd.read_json("data.json")
print(df.head())
The orient parameter
df = pd.read_json("data.json", orient="records")

orient controls exactly how pandas interprets the JSON's structure when converting it into rows and columns. Common values include "records" (a JSON array of objects, each representing one row — probably the most common real-world shape) and "columns" (a JSON object where each key maps to a column, and that column's values are themselves keyed by row index). When pandas' default guess doesn't match your actual data's shape, explicitly specifying the correct orient value resolves the mismatch.

Handling nested JSON with json_normalize()

Real-world API responses are frequently nested — an object containing another object, or an array of objects, inside one of its fields — and read_json() alone often isn't enough to flatten that nested structure into clean, flat columns.

import json
import pandas as pd

data = [
    {"name": "Alex", "address": {"city": "Nagpur", "zip": "440001"}},
    {"name": "Sam", "address": {"city": "Austin", "zip": "73301"}},
]

df = pd.json_normalize(data)
print(df)
#    name  address.city  address.zip
# 0  Alex        Nagpur        440001
# 1   Sam        Austin        73301

pd.json_normalize() flattens nested objects (and, with additional parameters, nested arrays) directly into flat, dotted column names — address.city, address.zip — rather than leaving the nested structure as a single column containing raw dictionaries, which would be considerably harder to filter, aggregate, or analyze using pandas' normal column-based tools. For genuinely nested API payloads, json_normalize() is frequently the more practical starting point than read_json() alone.

Choosing the Right Format and Writing Data Back Out

A practical rule of thumb for format choice

  • CSV — the right choice for data exchange with non-Python systems: it's a simple, universally supported, plain-text format that essentially every tool (spreadsheets, databases, other programming languages) can read and write.

  • Parquet — the right choice for internal analytics pipelines needing genuine speed and storage efficiency: it's a columnar, compressed binary format, considerably faster to read and write than CSV for large datasets, and it preserves data types precisely (no more silently-converted ID columns) — but it's not human-readable, and isn't universally supported outside data-analysis tooling the way CSV is.

  • Excel — reach for it specifically when required by stakeholders who genuinely work directly in spreadsheets — not as a default choice for data you'll only ever process programmatically, given the performance caveat covered in Section 3.

The mirror-image write methods

Each read function has a directly corresponding write method, following an identical, predictable pattern:

df.to_csv("output.csv", index=False)
df.to_excel("output.xlsx", index=False, sheet_name="Results")
df.to_json("output.json", orient="records")

index=False is worth calling out specifically: without it, pandas writes the DataFrame's row index out as an extra column in the output file — usually not what you actually want, since that index is often just the default 0, 1, 2, ... sequence, carrying no real information of its own.

A quick troubleshooting checklist

When something goes wrong loading a real-world file, these three patterns cover the overwhelming majority of actual causes:

  • A UnicodeDecodeError almost always means a wrong encoding guess — explicitly specify encoding="utf-8" (or whatever the source file's actual encoding is) rather than relying on a platform-dependent default, exactly as covered in Section 2.

  • Unexpected NaN values appearing after loading often trace back to inconsistent missing-value markers in the source file — a source system might represent "missing" as an empty string, "N/A", "null", or "-", all inconsistently within the same file, and pandas' default missing-value detection might not catch every variant automatically. The na_values parameter on read_csv() lets you explicitly list every marker your source file actually uses for "missing," ensuring they're all consistently recognized and converted to genuine NaN values.

  • Silently-wrong numeric columns (an ID column that should have stayed a string, a currency column with unexpected decimal rounding) are usually an uncontrolled dtype inference issue, exactly as covered in Section 2 — the fix is specifying dtype explicitly for the affected columns, rather than trusting pandas' automatic guess.

All three of these are fixable directly through the parameters covered throughout this article, rather than requiring manual, after-the-fact post-processing to patch up a DataFrame that loaded incorrectly in the first place.

PREVIOUSNEXT LESSON