Python CSV and JSON: Reading and Writing Structured Data

Most data you will meet comes in two shapes. CSV (comma-separated values) is a flat table: rows and columns, the format of every spreadsheet export. JSON (JavaScript Object Notation) is a tree: objects inside objects and lists inside lists, the format of almost every web API. Python ships with excellent tools for both: the csv and json modules.

You could parse these formats yourself with split(","), and you would be right most of the time, which is the dangerous part. A product name like Dal, Toor 1kg contains a comma; a customer address can contain a line break. The standard modules handle quoting and escaping correctly so that you do not have to. In this lesson you will read and write both formats using FreshBasket data, flatten the nested daily_sales.json into a table, and learn the moment to switch to pandas.

Worked examples

Example 1: csv.reader

Question: Read the stores file as plain lists.

import csv

with open("stores.csv", newline="", encoding="utf-8") as f:
    reader = csv.reader(f)
    header = next(reader)
    rows = list(reader)

print(header)
print(len(rows))
print(rows[0])

['store_id', 'city', 'store_name', 'manager', 'opened_on']
11
['S01', 'Bengaluru', 'FreshBasket Bengaluru North', 'Karan Menon', '2024-03-21']

How it works: csv.reader yields each row as a list of strings. The first call to next(reader) pulls off the header row. Always open CSV files with newline="" so that the module can handle line breaks inside quoted fields correctly. Every value arrives as text, even numbers.

Reading the result: The header has 5 columns, and the file holds 11 stores. The first store is FreshBasket Bengaluru North in Bengaluru.

Example 2: csv.DictReader

Question: Read the products file with column names as keys.

with open("products.csv", newline="", encoding="utf-8") as f:
    products = list(csv.DictReader(f))

print(len(products))
print(products[0])
print(type(products[0]["unit_price"]))

expensive = [p["product_name"] for p in products if float(p["unit_price"]) > 300]
print(expensive)

50
{'product_id': 'P001', 'product_name': 'Banana Robusta', 'category': 'Fruits & Veg', 'brand': 'Farm Fresh', 'unit_price': '48', 'cost_price': '36.31', 'shelf_life_days': '6'}
<class 'str'>
['Mango Alphonso', 'Basmati Rice 5kg', 'Detergent 2kg']

How it works: DictReader uses the header row as keys, giving a list of dictionaries, one per row, which is far easier to read than row[3]. Numbers are still strings, so we convert with float() before comparing.

Reading the result: There are 50 products, and 3 of them cost more than ₹300. If you forget the conversion, "95" > "300" compares text and gives wrong answers.

Example 3: Writing CSV

Question: Write a small summary table to a CSV file.

summary = [
    {"city": "Bengaluru", "orders": 1301, "revenue": 453210.5},
    {"city": "Mumbai", "orders": 945, "revenue": 398000.0},
    {"city": "Delhi, NCR", "orders": 1159, "revenue": 410500.25},
]

with open("city_summary.csv", "w", newline="", encoding="utf-8") as f:
    writer = csv.DictWriter(f, fieldnames=["city", "orders", "revenue"])
    writer.writeheader()
    writer.writerows(summary)

with open("city_summary.csv", encoding="utf-8") as f:
    print(f.read())

city,orders,revenue
Bengaluru,1301,453210.5
Mumbai,945,398000.0
"Delhi, NCR",1159,410500.25

How it works: DictWriter needs the column names in order. writeheader() writes them, and writerows() writes every dictionary. Notice that the city Delhi, NCR contains a comma, so the writer automatically wrapped it in quotes. Doing this by hand with string joins would have corrupted the file.

Reading the result: The file has 4 lines including the header, and the quoted city name stays one field when read back.

Example 4: Delimiters and quoting

Question: Read semicolon- or tab-separated data.

import io

text = 'id;name;note\n1;Milk;"fresh; chilled"\n2;Curd;plain\n'
rows = list(csv.reader(io.StringIO(text), delimiter=";"))
print(rows)

tsv = "id\tname\n1\tMilk\n"
print(list(csv.reader(io.StringIO(tsv), delimiter="\t")))

buf = io.StringIO()
csv.writer(buf, quoting=csv.QUOTE_ALL).writerow(["a", 1, "b c"])
print(buf.getvalue().strip())

[['id', 'name', 'note'], ['1', 'Milk', 'fresh; chilled'], ['2', 'Curd', 'plain']]
[['id', 'name'], ['1', 'Milk']]
"a","1","b c"

How it works: The delimiter argument switches the separator; European exports often use semicolons and many database dumps use tabs. io.StringIO lets us treat a string like a file, which is handy for tests and examples. QUOTE_ALL wraps every field in quotes.

Reading the result: The quoted note fresh; chilled kept its inner semicolon. Detecting the delimiter is the most common first step with unknown files.

Example 5: Convert types and aggregate

Question: Total the delivered orders by payment method from the raw CSV.

from collections import defaultdict

delivered_by_method = defaultdict(int)
total_rows = 0
with open("orders.csv", newline="", encoding="utf-8") as f:
    for row in csv.DictReader(f):
        total_rows += 1
        if row["order_status"] == "Delivered":
            delivered_by_method[row["payment_method"]] += 1

print(total_rows)
for method, n in sorted(delivered_by_method.items(), key=lambda kv: -kv[1]):
    print(f"{method:<8}{n:>6,}")

6000
UPI      2,990
Card     1,106
COD        871
Wallet     424

How it works: We stream through 6000 rows without loading them all, filter on a text column and count with a defaultdict. This is a good approach when the file is too big for memory. For analysis that needs many passes, pandas is the better tool.

Reading the result: Across 6,000 orders, the most used method for delivered orders is UPI.

Example 6: JSON basics

Question: Convert between Python objects and JSON text.

import json

order = {"order_id": "O1", "items": [{"sku": "P001", "qty": 2}], "prime": True, "coupon": None, "total": 450.5}

text = json.dumps(order)
print(text)

back = json.loads(text)
print(back == order)

print(json.dumps(order, indent=2, sort_keys=True))

{"order_id": "O1", "items": [{"sku": "P001", "qty": 2}], "prime": true, "coupon": null, "total": 450.5}
True
{
  "coupon": null,
  "items": [
    {
      "qty": 2,
      "sku": "P001"
    }
  ],
  "order_id": "O1",
  "prime": true,
  "total": 450.5
}

How it works: dumps turns a Python object into a JSON string, and loads parses it back. The mapping is: dict becomes object, list/tuple becomes array, str stays string, numbers stay numbers, True/False/None become true/false/null. indent makes the text readable and sort_keys makes the order predictable.

Reading the result: The round trip preserved the object exactly (True). Note that JSON uses true and null in lowercase, unlike Python.

Example 7: Reading and writing JSON files

Question: Load the daily sales file and inspect its structure.

with open("daily_sales.json", encoding="utf-8") as f:
    data = json.load(f)

print(list(data.keys()))
print(data["company"], data["report_month"], data["currency"])
print(len(data["stores"]))
store = data["stores"][0]
print(list(store.keys()))
print(store["days"][0])

['company', 'report_month', 'currency', 'stores']
FreshBasket 2026-06 INR
3
['store_id', 'city', 'manager', 'days']
{'date': '2026-06-01', 'orders': 2, 'revenue': 1106.0, 'payment_mix': {'UPI': 2}}

How it works: json.load(f) reads a file object directly (loads takes a string). The result is ordinary dictionaries and lists. Exploring a new JSON file starts with keys(), len() and printing a single element; always look at one example before writing loops.

Reading the result: The report covers 2026-06 for 3 stores, and the first store, S01 in Bengaluru, has 5 daily records.

Example 8: Flatten nested JSON into rows

Question: Turn the store → days hierarchy into one flat table.

rows = []
for s in data["stores"]:
    for day in s["days"]:
        rows.append({
            "store_id": s["store_id"],
            "city": s["city"],
            "date": day["date"],
            "orders": day["orders"],
            "revenue": day["revenue"],
        })

print(len(rows))
print(rows[0])

with open("daily_flat.csv", "w", newline="", encoding="utf-8") as f:
    w = csv.DictWriter(f, fieldnames=rows[0].keys())
    w.writeheader()
    w.writerows(rows)

total = sum(r["revenue"] for r in rows)
print(f"{total:,.2f}")

13
{'store_id': 'S01', 'city': 'Bengaluru', 'date': '2026-06-01', 'orders': 2, 'revenue': 1106.0}
12,205.00

How it works: A nested loop copies the parent fields (store id and city) onto each child record. This flattening is the first step whenever an API response needs to become a table. Pandas offers pd.json_normalize for the same job.

Reading the result: This sample extract holds 13 store-day rows with revenue of ₹12,205.00 in total. We also saved them to daily_flat.csv.

Example 9: Custom types and errors

Question: What happens with dates and with invalid JSON?

from datetime import date
import json

try:
    json.dumps({"when": date(2026, 6, 1)})
except TypeError as e:
    print("TypeError:", e)

print(json.dumps({"when": date(2026, 6, 1)}, default=str))

try:
    json.loads("{'a': 1}")
except json.JSONDecodeError as e:
    print("JSONDecodeError:", e.msg)

TypeError: Object of type date is not JSON serializable
{"when": "2026-06-01"}
JSONDecodeError: Expecting property name enclosed in double quotes

How it works: JSON has no date type, so json.dumps refuses a date object. The default= argument names a function to use for unknown types; str converts them to text. JSON also demands double quotes, so Python-style single quotes fail to parse with JSONDecodeError.

Reading the result: The fallback serialised the date as {"when": "2026-06-01"}. When saving analysis results, convert dates and numpy numbers explicitly.

Example 10: CSV or pandas?

Question: Compare plain csv with pandas for the same job.

import pandas as pd

df = pd.read_csv("orders.csv")
by_method = df[df["order_status"] == "Delivered"].groupby("payment_method").size().sort_values(ascending=False)
print(by_method)
print(df.shape)
print(df.dtypes["delivery_minutes"])

payment_method
UPI       2990
Card      1106
COD        871
Wallet     424
dtype: int64
(6000, 9)
float64

How it works: One line of pandas read the whole table, guessed the types (note delivery_minutes became a float because cancelled orders are blank) and a short chain reproduced the earlier count. Use the csv module for streaming, simple tasks and when you want no dependencies; use pandas for analysis.

Reading the result: The table has 6,000 rows and 9 columns, and the top method is UPI with 2,990 delivered orders, matching our manual count.

Common mistakes

1. Forgetting newline="" when opening CSV files, which can create blank lines on Windows.

2. Treating numbers as numbers without converting them from strings.

3. Splitting CSV lines manually with split(","), which breaks quoted fields.

4. Using single quotes in JSON text.

import json
json.loads("{'city': 'Pune'}")

Traceback (most recent call last):
  File "example.py", line 2, in <module>
    json.loads("{'city': 'Pune'}")
    ~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^
  File "example.py", line 352, in loads
    return _default_decoder.decode(s)
           ~~~~~~~~~~~~~~~~~~~~~~~^^^
  File "example.py", line 345, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
               ~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
  File "example.py", line 361, in raw_decode
    obj, end = self.scan_once(s, idx)
               ~~~~~~~~~~~~~~^^^^^^^^
json.decoder.JSONDecodeError: Expecting property name enclosed in double quotes: line 1 column 2 (char 1)

5. Forgetting that JSON keys are always strings: {1: "a"} comes back as {"1": "a"}.

Practice questions

Practice 1 (Easy): Count cities

Using csv.DictReader, count how many stores each city has in stores.csv.

from collections import Counter

with open("stores.csv", newline="", encoding="utf-8") as f:
    store_cities = Counter(row["city"] for row in csv.DictReader(f))
print(store_cities.most_common(3))

[('Bengaluru', 2), ('Mumbai', 2), ('Delhi', 2)]

Practice 2 (Medium): Average rating file

Read feedback.txt into dictionaries and write ratings.csv with columns order and rating. Then read it back and print the number of rows.

pairs = []
with open("feedback.txt", encoding="utf-8") as f:
    for line in f:
        _, order, rating, _ = [p.strip() for p in line.split("|", 3)]
        pairs.append({"order": order, "rating": int(rating.split(":")[1])})

with open("ratings.csv", "w", newline="", encoding="utf-8") as f:
    w = csv.DictWriter(f, fieldnames=["order", "rating"])
    w.writeheader()
    w.writerows(pairs)

with open("ratings.csv", newline="", encoding="utf-8") as f:
    print(len(list(csv.DictReader(f))))

40

Practice 3 (Hard): Busiest day per store

From daily_sales.json, find for each store the date with the highest revenue and print store id, date and revenue sorted by revenue descending.

best = []
for s in data["stores"]:
    top = max(s["days"], key=lambda d: d["revenue"])
    best.append((s["store_id"], top["date"], top["revenue"]))

for sid, d, rev in sorted(best, key=lambda t: -t[2]):
    print(sid, d, f"{rev:,.0f}")

S05 2026-06-05 2,816
S03 2026-06-07 1,396
S01 2026-06-01 1,106

Key takeaways

• Use csv.DictReader and csv.DictWriter for tables, and open files with newline="" and encoding="utf-8".
• CSV values are all strings: convert to numbers and dates before analysis.
• Never split CSV lines by hand; quoted fields contain commas.
• json.load/json.dump work with files, json.loads/json.dumps with strings.
• JSON maps to dicts, lists, strings, numbers, booleans and None; dates need conversion.
• Flatten nested JSON with nested loops (or json_normalize) before analysis.
• Switch to pandas when you need filtering, grouping or joining on tables.