Reading and Writing Data: CSV, Excel, JSON, SQL, Parquet

Load and save pandas DataFrames with read_csv, read_excel, read_json, read_sql and read_parquet, and control types, dates and missing values on the way in.

• Beginner
• 18 min read
• 4 objectives

Before this lessonLesson 1: DataFrames and Series

Almost every pandas session starts with the same line: load some data. In the first lesson you saw pd.read_csv in its simplest form. Real files are rarely that polite. They use semicolons instead of commas, store dates as text, write N/A or - for missing values, and contain ID columns that pandas helpfully turns into numbers (dropping the leading zeros you needed).

This lesson is about getting data in correctly and getting it out in the right format. Fixing a problem at read time is almost always cheaper than cleaning it up afterwards, because the reader already knows which column is which and can apply the rule in one pass.

All examples below use io.StringIO, which wraps a string so pandas can read it as if it were a file. That keeps every snippet runnable in your browser. In your own projects you simply replace StringIO(...) with a path such as "orders.csv" or a URL.

read_csv: the options that matter

read_csv has dozens of parameters, but a handful solve 90% of real problems: sep for the delimiter, dtype to force column types, parse_dates to turn text into datetimes, na_values to declare extra missing markers, and usecols to load only what you need.

import pandas as pd
from io import StringIO

raw = """order_id;customer;zip;amount;ordered_at
0001;Ada;02139;120.50;2026-03-01
0002;Grace;-;N/A;2026-03-02
0003;Linus;94105;80.00;2026-03-02
"""

df = pd.read_csv(
    StringIO(raw),
    sep=";",                          # semicolon-separated
    dtype={"order_id": "string", "zip": "string"},  # keep leading zeros
    na_values=["-", "N/A"],           # extra markers for missing
    parse_dates=["ordered_at"],       # text -> datetime64
)
print(df)
print(df.dtypes)

  order_id customer    zip  amount ordered_at
0     0001      Ada  02139   120.5 2026-03-01
1     0002    Grace   <NA>     NaN 2026-03-02
2     0003    Linus  94105    80.0 2026-03-02
order_id              string
customer                 str
zip                   string
amount               float64
ordered_at    datetime64[us]
dtype: object

Notice that order_id kept its leading zeros because we declared it as a string, and both - and N/A became proper missing values. Without dtype, pandas would read 0001 as the integer 1 and the ZIP code 02139 as 2139, which is silently wrong.

Loading only what you need

Big CSVs often have far more columns than you care about. usecols skips the rest at parse time, which saves memory and time. nrows reads just the first few rows, which is perfect for peeking at a huge file before committing to a full load.

import pandas as pd
from io import StringIO

raw = """id,name,email,country,signup,plan,notes
1,Ada,ada@example.com,UK,2026-01-04,pro,likes math
2,Grace,grace@example.com,US,2026-01-09,free,
3,Linus,linus@example.com,FI,2026-02-11,pro,kernel
"""

slim = pd.read_csv(StringIO(raw), usecols=["id", "country", "plan"])
print(slim)

peek = pd.read_csv(StringIO(raw), nrows=1)
print(peek.shape)

   id country  plan
0   1      UK   pro
1   2      US  free
2   3      FI   pro
(1, 7)

JSON: flat and nested

APIs usually return JSON. If the JSON is a list of flat objects, pd.read_json handles it directly. Most API responses are nested, though, with objects inside objects. For those, parse the JSON with the standard library and flatten it with pd.json_normalize, which turns nested keys into dotted column names.

import json
import pandas as pd
from io import StringIO

flat = '[{"user": "Ada", "score": 91}, {"user": "Grace", "score": 88}]'
print(pd.read_json(StringIO(flat)))

nested = """[
  {"id": 1, "user": {"name": "Ada", "city": "London"}, "tags": ["admin"]},
  {"id": 2, "user": {"name": "Grace", "city": "NYC"}, "tags": []}
]"""
records = json.loads(nested)
print(pd.json_normalize(records))

    user  score
0    Ada     91
1  Grace     88
   id     tags user.name user.city
0   1  [admin]       Ada    London
1   2       []     Grace       NYC

For JSON Lines files (one JSON object per line, common for logs and exports), use pd.read_json(path, lines=True). Writing works the other way around: df.to_json("out.json", orient="records", lines=True).

SQL databases

When data lives in a database, let the database do the filtering and pull only the result into pandas. pd.read_sql takes a query and a connection. For SQLite you can pass a standard sqlite3 connection; for Postgres, MySQL and others, pass a SQLAlchemy engine created with create_engine("postgresql+psycopg://...").

import sqlite3
import pandas as pd

con = sqlite3.connect(":memory:")
orders = pd.DataFrame({
    "customer": ["Ada", "Grace", "Ada", "Linus"],
    "amount": [120.5, 99.0, 30.0, 80.0],
})
orders.to_sql("orders", con, index=False)   # write a table

query = """
SELECT customer, SUM(amount) AS total
FROM orders
GROUP BY customer
ORDER BY total DESC
"""
print(pd.read_sql(query, con))

# Parameters keep user input out of the SQL string
big = pd.read_sql("SELECT * FROM orders WHERE amount > ?", con, params=(90,))
print(big)

  customer  total
0      Ada  150.5
1    Grace   99.0
2    Linus   80.0
  customer  amount
0      Ada   120.5
1    Grace    99.0

Excel and Parquet

Excel and Parquet need extra libraries, and they work with real files, so the snippet below is a reference rather than something to run here. Install the engines once with pip install openpyxl pyarrow.

# Excel (needs openpyxl)
df = pd.read_excel("report.xlsx", sheet_name="Q1")          # one sheet
sheets = pd.read_excel("report.xlsx", sheet_name=None)       # dict of all sheets
df.to_excel("out.xlsx", sheet_name="summary", index=False)

# Several sheets in one workbook
with pd.ExcelWriter("out.xlsx") as writer:
    sales.to_excel(writer, sheet_name="sales", index=False)
    costs.to_excel(writer, sheet_name="costs", index=False)

# Parquet (needs pyarrow)
df.to_parquet("orders.parquet", index=False)
df = pd.read_parquet("orders.parquet", columns=["customer", "amount"])

Parquet is a columnar binary format. It stores the column types alongside the data, compresses well and reads much faster than CSV. If a file is only going to be read by code (yours or a teammate's), Parquet is usually the right default. Reach for Excel when a human needs to open the file, and CSV when you need maximum compatibility.

• CSV: universal and human readable, but no types, so you re-declare dtypes and dates every time.
• Excel: great for business users, slow for large data, limited to about a million rows per sheet.
• JSON: natural for APIs and nested data, verbose on disk.
• Parquet: fast, compact, typed; the standard for analytics pipelines and data lakes.
• SQL: keep data in the database and pull only query results.

Writing data back out

Every reader has a matching writer. The most common surprise is the extra unnamed column that appears when you round-trip a CSV: that is the index being written. Pass index=False unless the index carries real information.

import pandas as pd
from io import StringIO

df = pd.DataFrame({"name": ["Ada", "Grace"], "score": [91.24, 88.5]})

buf = StringIO()
df.to_csv(buf, index=False, float_format="%.1f")
print(buf.getvalue())

print(df.to_json(orient="records"))

name,score
Ada,91.2
Grace,88.5

[{"name":"Ada","score":91.24},{"name":"Grace","score":88.5}]

Recap

• Fix types at read time with dtype, parse_dates and na_values instead of cleaning later.
• Read ID-like columns as strings so leading zeros survive.
• Flatten nested API responses with pd.json_normalize.
• Let SQL filter and aggregate, then read_sql the result, using params for values.
• Prefer Parquet for data read by code, Excel for humans, and always consider index=False when writing.

# Write your solution here