Reading and Writing Data (CSV, Excel, JSON)

Reading a CSV file

CSV is the most common format in analytics. Under the hood, read_csv is doing several jobs at once: splitting text into a table, inferring each column’s dtype, and deciding what counts as a row/column index — which is why it has dozens of optional arguments.

Read CSV

import pandas as pd
 
df = pd.read_csv("data/sales.csv")
print(df.head())

Useful read_csv() options

read_csv options

import pandas as pd
 
df = pd.read_csv(
    "data/sales.csv",
    sep=",",              # delimiter
    encoding="utf-8",     # encoding
    na_values=["NA", "", "null"],
)
 
print(df.info())

Writing a CSV file

Write to CSV

import pandas as pd
 
df = pd.DataFrame({"a": [1, 2], "b": [3, 4]})
 
df.to_csv("output/cleaned.csv", index=False)

Reading Excel files

Excel needs an engine (often openpyxl).

Read Excel

import pandas as pd
 
df = pd.read_excel("data/sales.xlsx", sheet_name="Sheet1")
print(df.head())

Writing Excel

Write Excel

import pandas as pd
 
df = pd.DataFrame({"a": [1, 2], "b": [3, 4]})
 
df.to_excel("output/cleaned.xlsx", index=False)

Reading and writing JSON

Reading JSON

Read JSON

import pandas as pd
 
df = pd.read_json("data/users.json")
print(df.head())

Writing JSON

Write JSON

import pandas as pd
 
df = pd.DataFrame({"user": ["a", "b"], "score": [10, 20]})
 
df.to_json("output/users.json", orient="records", indent=2)

From the book

McKinney notes that pandas.read_json’s default options assume each JSON object in an array is one table row — but real-world JSON is often nested (lists inside objects, objects inside objects). For anything more complex than flat records, load with json.loads first and hand the resulting list of dicts to pd.DataFrame() yourself.

Visualize it

diagram File formats into a DataFrame mermaid

  flowchart LR
  A["CSV"] --> D["pandas.DataFrame"]
  B["Excel"] --> D
  C["JSON"] --> D
  D --> E["to_csv / to_excel / to_json"]

Different file formats all converge on the same in-memory DataFrame, and can be exported back out to any of them.

Practical checklist for file IO

• Always confirm shape: df.shape
• Inspect columns: df.columns
• Preview: df.head() and df.sample(5)
• Validate types: df.dtypes
• Watch for missing values: df.isna().sum()

🧪 Try It Yourself

Exercise 1 – Build a DataFrame from JSON Records

pch.exerciseTag pch.exerciseTitle

Exercise 2 – Write to CSV Without the Index

pch.exerciseTag pch.exerciseTitle

Exercise 3 – Treat Custom Strings as Missing Values

pch.exerciseTag pch.exerciseTitle

Next

For faster or larger-than-memory data, continue to Binary Formats and Web APIs (Parquet, pickle, requests).