Lesson 13: Working with Data — CSV, JSON, and APIs

Programs rarely own their data. Marks live in a teacher's spreadsheet (CSV), weather apps trade forecasts as JSON, and live data — GitHub, weather services, timetables — is served by APIs: URLs that return structured data when a program asks. This lesson covers the three formats and the gracious way to consume them: with the csv and json modules (never hand-splitting), and with requests for the web.

CSV: rows, columns, and the quoting trap

A CSV file is plain text: one row per line, fields separated by commas. Reading it with split(",") works until a field legitimately *contains* a comma — "Doe, Jane" is one name that naive splitting tears in two. The csv module understands the quoting rules, and DictReader hands you each row as a dictionary keyed by the header line:

import csv

with open("scores.csv", "w", newline="", encoding="utf-8") as f:
    f.write("name,score\nAda,95\nBen,82\nCid,78\n")

with open("scores.csv", newline="", encoding="utf-8") as f:
    for row in csv.DictReader(f):
        print(row["name"], int(row["score"]))

Output:

Ada 95
Ben 82
Cid 78

(The first block of code writes the file so the example runs anywhere; real programs would read a file that already exists.) Two professional habits are embedded here: the newline="" argument when opening CSV files — the csv documentation requires it to keep line endings correct on every platform — and converting types yourself: every value arrives as a string, so int(row["score"]) is explicit. CSV carries no type information; assuming numbers stay numbers is how "95" + "10" becomes "9510".

Worked example 1 — analysing a sales file

The original lesson's analysis, complete and self-contained: write a small sales file, read it, and compute totals, per-product revenue and the best seller.

import csv

DATA = """date,product,quantity,price
2026-03-01,notebook,3,4.50
2026-03-01,pen,10,1.25
2026-03-02,notebook,2,4.50
2026-03-03,backpack,1,45.00
2026-03-03,pen,25,1.25
"""

with open("sales.csv", "w", newline="", encoding="utf-8") as f:
    f.write(DATA)

def load_sales(path):
    sales = []
    with open(path, newline="", encoding="utf-8") as f:
        for row in csv.DictReader(f):
            row["quantity"] = int(row["quantity"])
            row["price"] = float(row["price"])
            sales.append(row)
    return sales

def total_revenue(sales):
    return sum(r["quantity"] * r["price"] for r in sales)

def revenue_by_product(sales):
    totals = {}
    for r in sales:
        earned = r["quantity"] * r["price"]
        totals[r["product"]] = totals.get(r["product"], 0) + earned
    return totals

sales = load_sales("sales.csv")
print("Orders:", len(sales))
print("Total revenue: $", round(total_revenue(sales), 2))
print("Revenue by product:", revenue_by_product(sales))
print("Best seller:", max(revenue_by_product(sales), key=revenue_by_product(sales).get))
print("Average order value: $", round(total_revenue(sales) / len(sales), 2))

Output:

Orders: 5
Total revenue: $ 111.25
Revenue by product: {'notebook': 22.5, 'pen': 43.75, 'backpack': 45.0}
Best seller: backpack
Average order value: $ 22.25

Study the shape: conversion happens once, at load time — load_sales returns clean rows with an int quantity and a float price, so every analysis function works with real numbers and never re-parses strings. Then each question is a small function over the clean data: sum a generator expression for the total, accumulate by key for the breakdown (Lesson 5's counting pattern), and max with the dictionary's .get as key function names the best seller. This load-once, analyse-many structure is how real data pipelines are shaped, from a class spreadsheet to a million-row export.

JSON and APIs: data as services publish it

JSON should feel familiar — it is dictionaries and lists in text form, which is why Python's json.loads (load from a *string*) turns API responses into objects you already know how to use:

import json

data = json.loads('{"city": "Boston", "temp_c": 18, "conditions": "Sunny"}')
print(f"{data['city']}: {data['temp_c']}C and {data['conditions']}")
print(type(data))

Output:

Boston: 18C and Sunny
<class 'dict'>

Fetching from a live API replaces the hand-written string with requests.get(url). Always look at the status code before trusting the body — 200 means success; 404, 403 and 500 are the server declining, each needing different handling (Lesson 14 takes this further). GitHub's "zen" endpoint is a friendly first API because it needs no key:

import requests

response = requests.get("https://api.github.com/zen", timeout=10)
print("Status:", response.status_code)

Output: Status: 200. The body (response.text) is a short programming aphorism that changes every call, so the lesson claims only the stable part: the status. When the API returns JSON, .json() parses the body in one step:

import requests

todo = requests.get("https://jsonplaceholder.typicode.com/todos/1", timeout=10).json()
print(todo["title"])
print("Completed?", todo["completed"])

Output:

delectus aut autem
Completed? False

(The title is Latin placeholder text — that free test API exists precisely so learners can practise against real responses.) Note the timeout=10 on both calls: a program without a timeout can hang forever on a sick server. Network code is optimistic by design here — Lesson 14 wraps it in the error handling production code needs.

Worked example 2 — a week of weather from JSON

Analysis skills do not care whether JSON came from the web or a string. Given a week's forecast as raw JSON text, find the hottest day, the average temperature and the rainy days:

import json

raw = """[
  {"day": "Mon", "temp_c": 21, "rain_mm": 0},
  {"day": "Tue", "temp_c": 24, "rain_mm": 2},
  {"day": "Wed", "temp_c": 19, "rain_mm": 12},
  {"day": "Thu", "temp_c": 23, "rain_mm": 0},
  {"day": "Fri", "temp_c": 26, "rain_mm": 0}
]"""
week = json.loads(raw)
hottest = max(week, key=lambda d: d["temp_c"])
average = sum(d["temp_c"] for d in week) / len(week)
rainy = [d["day"] for d in week if d["rain_mm"] > 0]
print("Hottest:", hottest["day"], hottest["temp_c"])
print("Average temp:", average)
print("Rainy days:", rainy)

Output:

Hottest: Fri 26
Average temp: 22.6
Rainy days: ['Tue', 'Wed']

This is Lesson 2's warmest-day project in modern dress: JSON parsed into a list of dictionaries, then max with a key lambda, a generator-fed sum, and a filtering comprehension. The transfer matters more than the example — the same three moves summarise sales rows, quiz results, sensor logs and stock ticks, because all of them arrive as "a list of records with fields".

Common mistakes

• Splitting CSV by hand. One quoted comma ("Doe, Jane") and your columns silently shift. The csv module exists precisely because the format has rules; use it always.
• Forgetting that CSV values are strings. "10" + "5" is "105", while 10 + 5 is 15 — convert at load time, once.
• Confusing json.load and json.loads. load reads from an open file; loads parses a string (the s is for string). API responses go through .json() instead — all three produce the same dicts and lists.
• Assuming the API always succeeds. No status check, no timeout, and a hard-coded key that the service later renames — three classic causes of "it worked yesterday". Check the status, handle failure paths, and print the actual response when debugging.
• Hard-coding secrets. Real APIs use keys; keys belong in environment variables or a private config file, never in shared code.

Real-World Challenge

The task. A teacher exports quiz marks as CSV. Build a grade-book report: load the file, compute each student's average across three quizzes, print a formatted report, and name the top student.

Worked solution.

import csv

DATA = """name,quiz1,quiz2,quiz3
Ada,95,88,92
Ben,72,81,79
Cid,60,75,70
Dee,88,91,84
"""

with open("grades.csv", "w", newline="", encoding="utf-8") as f:
    f.write(DATA)

def student_averages(path):
    results = {}
    with open(path, newline="", encoding="utf-8") as f:
        for row in csv.DictReader(f):
            scores = [int(row[k]) for k in ("quiz1", "quiz2", "quiz3")]
            results[row["name"]] = sum(scores) / len(scores)
    return results

averages = student_averages("grades.csv")
for name, avg in averages.items():
    print(f"{name}: {avg:.1f}")
top = max(averages, key=averages.get)
print("Top student:", top, f"({averages[top]:.1f})")

Output:

Ada: 91.7
Ben: 77.3
Cid: 68.3
Dee: 87.7
Top student: Ada (91.7)

How it works. The solution follows the lesson's pipeline exactly: a file in a standard format, read with DictReader, converted at load (int(row[k]) for each quiz column, gathered by iterating the known column names), stored as a name → average dictionary, then reported. The {avg:.1f} formatting hides the repeating decimals (91.666…) that the raw averages carry — display rounding, while the stored values stay exact for comparisons, so max ranks by truth rather than by the rounded printout. The top-student line reuses the max(..., key=dict.get) idiom for the third time in this course; by now it should read as plain English — "the key with the greatest value".

Extension. Teachers need letter grades (Lesson 2's bands) and alerts. Add a grade(avg) function, print it beside each average, and list every student averaging under 70 under a "Needs support" heading. Then make the column handling generic — average all columns except "name" — so next term's four-quiz export works without code changes.

Going further — writing CSV, and keeping secrets out of code

Reading is half the traffic; reports and exports need writing. csv.DictWriter mirrors the reader: declare the field names once, write a header, then hand it dictionaries and let it handle quoting — a product named "Doe, Jane" is escaped correctly on the way out just as it was parsed correctly on the way in:

import csv

rows = [
    {"name": "Ada", "score": 95},
    {"name": "Ben", "score": 82},
]
with open("out.csv", "w", newline="", encoding="utf-8") as f:
    writer = csv.DictWriter(f, fieldnames=["name", "score"])
    writer.writeheader()
    writer.writerows(rows)
print(open("out.csv", encoding="utf-8").read())

Output:

name,score
Ada,95
Ben,82

A mismatch between a row's keys and the declared fieldnames raises an error rather than silently writing garbage — strictness you want at a format boundary. The second professional habit belongs to APIs specifically: keys and passwords never live in source code, which gets shared, committed and screenshotted. The standard pattern reads them from environment variables — values set in the terminal or the hosting platform, fetched with os.environ.get("MY_API_KEY") — so the code is public-safe and each machine supplies its own secret. Relatedly, very large CSVs should be *streamed*: the row-by-row loops in this lesson already do that, never loading a million-row file into memory at once. Format fluency plus secret hygiene plus streaming is most of what "data engineering basics" means at this level.

Practice corner — defensive reading with .get

API records are promises, not guarantees: an optional field that existed yesterday vanishes today, and data["phone"] turns a display hiccup into a KeyError crash. Reading with .get and a fallback keeps the program honest about uncertainty:

user = {"name": "Ada", "email": "ada@example.com"}
print(user.get("phone", "no phone on file"))

Output: no phone on file. Choose the pattern per field: required fields deserve the direct data["name"] lookup, whose loud failure exposes a broken contract early; optional fields deserve .get with a sensible default, so absence degrades gracefully instead of detonating. The same judgment applies to nested API structures — check each level you cannot guarantee, and prefer failing loudly at the boundary (Lesson 14's validation) over propagating a quiet None through five functions.