JSON and CSV 🧾

JSON: how programs talk to each other

JSON looks like Python's dictionaries and lists, because both borrowed the notation from JavaScript. Every web API you will ever call, including the AI models in Level 6, speaks it.

import json

crew = {
    "ship": "Sea Monkey",
    "captain": "Guybrush",
    "crew": ["Otis", "Meathook"],
    "seaworthy": False,
    "cargo_tons": 12.5,
    "insurance": None,
}

text = json.dumps(crew, indent=2)
print(text)

{
  "ship": "Sea Monkey",
  "captain": "Guybrush",
  "crew": [
    "Otis",
    "Meathook"
  ],
  "seaworthy": false,
  "cargo_tons": 12.5,
  "insurance": null
}

Note the translations. They matter when you read someone else's JSON:

The four functions

import json
from pathlib import Path

data = {"ship": "Sea Monkey", "crew": ["Otis"]}

text = json.dumps(data)          # to a string  ("dump s"tring)
back = json.loads(text)          # from a string
print(text)
print(back["crew"][0])

with open("ship.json", "w", encoding="utf-8") as f:
    json.dump(data, f, indent=2)     # to a file

with open("ship.json", encoding="utf-8") as f:
    loaded = json.load(f)            # from a file

print(loaded == data)

{"ship": "Sea Monkey", "crew": ["Otis"]}
Otis
True

The JSON traps

import json

# 1. Not everything can be encoded
from datetime import date
try:
    json.dumps({"when": date(1990, 10, 15)})
except TypeError as err:
    print("TypeError:", err)

# The fix: convert it yourself
print(json.dumps({"when": date(1990, 10, 15).isoformat()}))

# 2. Dictionary keys always come back as strings
original = {1: "one", 2: "two"}
round_tripped = json.loads(json.dumps(original))
print(original)
print(round_tripped)

# 3. Broken input raises, so handle it
try:
    json.loads("{not json at all}")
except json.JSONDecodeError as err:
    print(f"JSONDecodeError at line {err.lineno} column {err.colno}")

TypeError: Object of type date is not JSON serializable
{"when": "1990-10-15"}
{1: 'one', 2: 'two'}
{'1': 'one', '2': 'two'}
JSONDecodeError at line 1 column 2

That second one bites people constantly: JSON objects can only have string keys, so numeric keys are silently converted. If your data is keyed by id, either accept strings or store a list of records instead.

Non-English text

import json

data = {"name": "Zoë", "ship": "Sjøhesten", "emoji": "🐒"}

print(json.dumps(data))
print(json.dumps(data, ensure_ascii=False))

{"name": "Zo\u00eb", "ship": "Sj\u00f8hesten", "emoji": "\ud83d\udc12"}
{"name": "Zoë", "ship": "Sjøhesten", "emoji": "🐒"}

Both are valid JSON and both decode to the same thing. Pass ensure_ascii=False when a human is going to read the file, and make sure you also opened it with encoding="utf-8".

CSV: how spreadsheets talk

import csv

rows = [
    ["name", "role", "pay"],
    ["Guybrush", "captain", 100],
    ["Elaine", "governor", 250],
    ["Otis, the prisoner", "lookout", 40],
]

with open("crew.csv", "w", newline="", encoding="utf-8") as f:
    csv.writer(f).writerows(rows)

print(open("crew.csv", encoding="utf-8").read())

name,role,pay
Guybrush,captain,100
Elaine,governor,250
"Otis, the prisoner",lookout,40

Reading CSV properly

import csv

with open("crew.csv", "w", newline="", encoding="utf-8") as f:
    csv.writer(f).writerows([
        ["name", "role", "pay"],
        ["Guybrush", "captain", 100],
        ["Otis, the prisoner", "lookout", 40],
    ])

# As lists
with open("crew.csv", newline="", encoding="utf-8") as f:
    for row in csv.reader(f):
        print(row)

print("---")

# As dictionaries, keyed by the header row. Much better.
with open("crew.csv", newline="", encoding="utf-8") as f:
    for row in csv.DictReader(f):
        print(f"{row['name']:20} {row['role']:10} {row['pay']}")

['name', 'role', 'pay']
['Guybrush', 'captain', '100']
['Otis, the prisoner', 'lookout', '40']
---
Guybrush             captain    100
Otis, the prisoner   lookout    40

Writing dictionaries out

import csv

crew = [
    {"name": "Guybrush", "role": "captain", "pay": 100},
    {"name": "Elaine", "role": "governor", "pay": 250},
]

with open("crew.csv", "w", newline="", encoding="utf-8") as f:
    writer = csv.DictWriter(f, fieldnames=["name", "role", "pay"])
    writer.writeheader()
    writer.writerows(crew)

print(open("crew.csv", encoding="utf-8").read().strip())

name,role,pay
Guybrush,captain,100
Elaine,governor,250

Which format when?

import json
from pathlib import Path

game = {
    "title": "The Secret of Monkey Island",
    "year": 1990,
    "characters": [
        {"name": "Guybrush", "hero": True, "insults": 8},
        {"name": "LeChuck", "hero": False, "insults": 3},
    ],
}

Path("game.json").write_text(json.dumps(game, indent=2), encoding="utf-8")
loaded = json.loads(Path("game.json").read_text(encoding="utf-8"))

print(loaded == game)
print(loaded["characters"][0]["name"])
print(sum(c["insults"] for c in loaded["characters"]))

True
Guybrush
11

import csv
from pathlib import Path

Path("sales.csv").write_text("""region,seller,amount
North,Elaine,1200
South,Otis,340
North,Guybrush,890
South,Meathook,1150
East,Stan,4200
""", encoding="utf-8")

totals = {}
with open("sales.csv", newline="", encoding="utf-8") as f:
    for row in csv.DictReader(f):
        amount = int(row["amount"])
        totals[row["region"]] = totals.get(row["region"], 0) + amount

for region, total in sorted(totals.items(), key=lambda pair: pair[1], reverse=True):
    print(f"{region:6} {total:6,}")

East    4,200
North   2,090
South   1,490

import csv, json
from pathlib import Path


def looks_numeric(value):
    """True if this text should become a number."""
    try:
        float(value)
        return True
    except ValueError:
        return False


def csv_to_json(csv_path, json_path):
    """Convert a CSV file to a JSON array of objects."""
    with open(csv_path, newline="", encoding="utf-8") as f:
        rows = []
        for row in csv.DictReader(f):
            clean = {}
            for key, value in row.items():
                if looks_numeric(value):
                    clean[key] = float(value) if "." in value else int(value)
                else:
                    clean[key] = value
            rows.append(clean)

    Path(json_path).write_text(json.dumps(rows, indent=2), encoding="utf-8")
    return len(rows)


Path("crew.csv").write_text("name,pay,rating\nGuybrush,100,4.5\nOtis,40,3.0\n",
                            encoding="utf-8")

count = csv_to_json("crew.csv", "crew.json")
print(f"{count} rows converted")
print(Path("crew.json").read_text(encoding="utf-8"))

2 rows converted
[
  {
    "name": "Guybrush",
    "pay": 100,
    "rating": 4.5
  },
  {
    "name": "Otis",
    "pay": 40,
    "rating": 3.0
  }
]