Stop 06 · Python for Data AnalysisTopics · 10/12

Reading/Writing CSV, Excel & JSON

In short

Data never arrives in one neat format. Finance sends Excel, the app team sends JSON, the vendor sends a CSV with semicolons. Pandas reads them all into the same DataFrame.

The everyday I/O operations that connect Python to the rest of your workflow — pulling data in from whatever format it arrives in, and exporting results back out for others to use.

The jaadu pours sweets from a box, a tin and a jar into one bowl.

Think of it like this

Same sweets, different boxes

Diwali gifts arrive as a cardboard box, a tin and a glass jar. The packing differs, but once you open them, it's all sweets in one bowl. CSV, Excel and JSON are just packing. Once loaded, you work with one DataFrame.

Watch it happen

Three formats, one DataFrame

DataFrame

read_csv

read_excel

read_json

sep, encoding

sheet_name

json_normalize

1/4Whatever the source, you want a DataFrame at the end.

The big ideas

What you need to know

CSV options that matter

sep=";" for semicolon files, encoding="utf-8", dtype={"pincode": str} so leading zeros survive.

Excel needs a helper

read_excel uses the openpyxl package for .xlsx files. Install it once if you see an import error.

JSON can be nested

API data often has objects inside objects. pd.json_normalize turns customer.city into a column.

Write back out

to_csv(index=False) avoids an extra unnamed column. to_excel and to_json work the same way.

See it in code

Walk through the code

python

1import pandas as pd2 3sales = pd.read_csv("sales.csv", dtype={"pincode": str})4budget = pd.read_excel("budget.xlsx", sheet_name="FY26")5 6import json7with open("orders.json") as f:8    raw = json.load(f)9orders = pd.json_normalize(raw, sep="_")10 11sales.to_csv("sales_clean.csv", index=False)12orders.to_excel("orders.xlsx", index=False)

1/4Read pincode as text so '011002' doesn't turn into 11002.

Read three formats, flatten the nested one, and write clean files back out without the extra index column.

Save yourself the pain

Common mistakes

Oops

Letting phone numbers and pincodes load as numbers.

Do this instead

Pass dtype=str for ID-like columns. You never do maths on them, and leading zeros matter.

Oops

Saving with to_csv() and getting a mystery 'Unnamed: 0' column on reload.

Do this instead

Use index=False when writing, unless the index really holds meaningful labels.

Your mission

One dataset, three formats

~20 min

Quick check

After reading a CSV, pincodes like 011002 show as 11002. What's the fix?

Go deeper

1Practice reading a CSV, an Excel file, and a JSON file into Pandas.
2Export a cleaned DataFrame back to Excel for a non-technical stakeholder.
3Learn common encoding and delimiter issues that break naive CSV reads.