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.

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

Useful 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

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).

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

Writing 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

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

Writing JSON

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

Visualize it

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

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

Press Run to see what your code prints.

Exercise 2 – Write to CSV Without the Index

Press Run to see what your code prints.

Exercise 3 – Treat Custom Strings as Missing Values

Press Run to see what your code prints.

Check your understanding

Next

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