JSON and CSV

The problem we are solving

Since chapter twenty-three we have been splitting files with .split(","), and it worked. Now a file where one field contains a comma inside it:

name,note,price
pen,blue ink,15.0
bag,"large, sturdy",850.0

with open("items.csv", "r", encoding="utf-8") as fh:
    for line in fh:
        print(line.strip().split(","))

['name', 'note', 'price']
['pen', 'blue ink', '15.0']
['bag', '"large', ' sturdy"', '850.0']

The last row came out with four fields instead of three, and the quote marks became part of the data.

The quoting is not a mistake — it is CSV's rule, and it exists for exactly this situation. The mistake is our reader. And writing that rule yourself means handling quotes inside quotes and newlines inside fields; a small job steadily becoming a large one.

The job has already been done.

import csv

with open("items.csv", "r", encoding="utf-8", newline="") as fh:
    for row in csv.reader(fh):
        print(row)

['name', 'note', 'price']
['pen', 'blue ink', '15.0']
['bag', 'large, sturdy', '850.0']

This chapter is two formats — CSV for tables, JSON for structure — and a module for each ships with Python.

By the end of this chapter you can

• Read with csv.reader and csv.DictReader, and write with csv.writer
• Say why newline="" is written
• Say what separates json.dumps/loads from json.dump/load
• Say which Python types come back from JSON and which do not
• Decide between CSV and JSON
• Read a JSONDecodeError and find the problem

Prerequisites: Exceptions.

Reading CSV

csv.reader gives each row as a list. But then positions have to be remembered — what was row[2] again?

csv.DictReader treats the first row as headings and makes each row a dictionary:

import csv

with open("items.csv", "r", encoding="utf-8", newline="") as fh:
    for row in csv.DictReader(fh):
        print(row["name"], "-", row["price"], type(row["price"]))

pen - 15.0 <class 'str'>
bag - 850.0 <class 'str'>

Two things.

Access is by name, so reordering the columns does not break the code. DictReader is nearly always the better choice.

But price is still text. The csv module separates fields, it does not convert them — nothing in a CSV file says which column is a number. float() and int() are yours to call, exactly as in chapter twenty-three.

Writing CSV

import csv

rows = [["pen", "blue ink", 15.0], ["bag", "large, sturdy", 850.0]]

with open("out.csv", "w", encoding="utf-8", newline="") as fh:
    writer = csv.writer(fh)
    writer.writerow(["name", "note", "price"])
    writer.writerows(rows)

name,note,price
pen,blue ink,15.0
bag,"large, sturdy",850.0

Notice that large, sturdy was quoted by itself — and blue ink was not, because it did not need to be. What we could not read ourselves, we did not have to write either.

For a list of dictionaries, DictWriter:

import csv

rows = [
    {"name": "pen", "price": 15.0},
    {"name": "bag", "price": 850.0},
]

with open("out.csv", "w", encoding="utf-8", newline="") as fh:
    writer = csv.DictWriter(fh, fieldnames=["name", "price"])
    writer.writeheader()
    writer.writerows(rows)

name,price
pen,15.0
bag,850.0

fieldnames also fixes the column order, and without writeheader() there is no heading row.

Why newline=""? The csv module decides for itself what ends a row. Opening the file normally makes Python translate line endings a second time on some systems, leaving a blank line after every row. newline="" turns that second translation off. Write it for reading and for writing alike.

JSON — structure a table cannot hold

CSV is a table: rows and columns, everything flat. But what if an order contains a list of lines, and each line contains a list of tags? A table cannot hold that.

import json

order = {"name": "pen", "price": 15.0, "tags": ["ink", "blue"], "stocked": True, "note": None}

print(json.dumps(order))

{"name": "pen", "price": 15.0, "tags": ["ink", "blue"], "stocked": true, "note": null}

json.dumps turns a Python object into text. The shape looks like Python's, with two differences: True became true and None became null. JSON is a separate language shared between many programming languages, so it has its own spelling.

For something a person will read:

import json

order = {"name": "pen", "tags": ["ink", "blue"]}

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

{
  "name": "pen",
  "tags": [
    "ink",
    "blue"
  ]
}

And bringing it back:

import json

text = '{"name": "pen", "price": 15.0, "stocked": true, "note": null}'
order = json.loads(text)

print(order)
print(type(order["price"]), type(order["stocked"]), order["note"] is None)

{'name': 'pen', 'price': 15.0, 'stocked': True, 'note': None}
<class 'float'> <class 'bool'> True

This is the largest difference from CSV. price came back as a number and stocked came back as True, with no conversion called at all. JSON carries the types; CSV does not.

The four names are easy to keep straight: dumps/loads work with text, dump/load work with files. The trailing s is for string.

import json
from pathlib import Path

order = {"name": "pen", "price": 15.0}

Path("order.json").write_text(json.dumps(order, indent=2) + "\n", encoding="utf-8")

with open("order.json", "r", encoding="utf-8") as fh:
    back = json.load(fh)

print(back)

{'name': 'pen', 'price': 15.0}

What JSON does not give back

Not every Python object can go into JSON, and not everything that goes in comes back the same.

import json

data = {"pair": (1, 2), "numbers": {3, 4}}

print(json.dumps({"pair": data["pair"]}))
print(json.dumps(data))

{"pair": [1, 2]}
TypeError: Object of type set is not JSON serializable

A tuple silently becomes a list. JSON has no tuple, so writing raises no complaint — but what comes back is a list. Chapter fourteen's promise that it will not change is lost in transit.

A set does not go at all, and that is the better behaviour — stopping beats being quietly wrong. Send sorted(...) as a list if you need it.

One more, which bites harder:

import json

data = {1: "pen", 2: "bag"}
text = json.dumps(data)

print(text)
print(json.loads(text))

{"1": "pen", "2": "bag"}
{'1': 'pen', '2': 'bag'}

JSON keys are always text. Give it numeric keys and they quietly become text, and come back as text. So data[1] worked before, and data["1"] is needed after.

The list to remember is short: dictionaries, lists, text, numbers, True/False and None are safe. Tuples change. Sets, dates and objects of your own do not go.

JSON is strict

import json

print(json.loads("{'name': 'pen'}"))

json.decoder.JSONDecodeError: Expecting property name enclosed in double quotes: line 1 column 2 (char 1)

In Python single and double quotes are equal. In JSON they are not — only double quotes, and no comma after the last item.

The message gives a line and a column, so the spot can be found even in a large file. And JSONDecodeError is really a ValueError — so except ValueError catches it too, as the previous chapter's family rule says.

Which, when

Take CSV when the data is a flat table and a person will open it in a spreadsheet. With many rows the file stays smaller too.

Take JSON when there is structure — lists inside, dictionaries inside — or when the types have to come back intact. Settings files and web APIs are almost always JSON.

A complete example

items.csv:

name,note,price,quantity
pen,blue ink,15.0,3
bag,"large, sturdy",850.0,1
ink,,120.0,2
clip,broken,,4

main.py:

"""Read a CSV of items, total it, and write the result as JSON."""

import csv
import json
from pathlib import Path

TAX_RATE = 0.15


def read_items(path):
    """Returns the usable rows and the complaints. csv handles the quoting."""
    items = []
    problems = []

    with open(path, "r", encoding="utf-8", newline="") as fh:
        for number, row in enumerate(csv.DictReader(fh), start=2):
            try:
                items.append({
                    "name": row["name"],
                    "note": row["note"],
                    "amount": round(
                        float(row["price"]) * int(row["quantity"]) * (1 + TAX_RATE), 2),
                })
            except ValueError as err:
                problems.append(f"line {number}: {err}")

    return items, problems


def main():
    items, problems = read_items(Path("items.csv"))

    summary = {
        "items": items,
        "total": round(sum(item["amount"] for item in items), 2),
        "skipped": problems,
    }

    Path("summary.json").write_text(
        json.dumps(summary, indent=2) + "\n", encoding="utf-8")

    print(Path("summary.json").read_text(encoding="utf-8"), end="")


if __name__ == "__main__":
    main()

{
  "items": [
    {
      "name": "pen",
      "note": "blue ink",
      "amount": 51.75
    },
    {
      "name": "bag",
      "note": "large, sturdy",
      "amount": 977.5
    },
    {
      "name": "ink",
      "note": "",
      "amount": 276.0
    }
  ],
  "total": 1305.25,
  "skipped": [
    "line 5: could not convert string to float: ''"
  ]
}

Four things worth looking at.

CSV comes in and JSON goes out, and that follows from what each format is. The input is a flat table, which a spreadsheet can also open. The output is not flat — a list of things, each a dictionary, with a second list of complaints beside it. That could not have been written as CSV.

large, sturdy survives the whole trip. csv read it correctly and json wrote it correctly. The problem this chapter opened with is nowhere in sight.

The empty note and the empty price are both empty text, and they end differently. An empty CSV field is "", not None. note stays as text and causes nothing; float("") raises a ValueError, so the clip row is skipped with a complaint. CSV has no "missing", only "empty" — and what that means is yours to decide.

The start=2 is not arbitrary. DictReader consumes the first row as headings, so the first data row it yields is the file's second line. A message somebody will check against the file has to count the way the file counts.

When it breaks

A blank line after every row of my CSV newline="" was not passed. Pass it when writing.

KeyError: 'price' — but the column is right there The heading does not match, in spelling or in whitespace. Print reader.fieldnames to see what Python actually read.

A TypeError doing arithmetic with the numbers csv gives everything as text. float() or int() has to be called.

The first row is being treated as data You are using csv.reader, which knows nothing about headings. Take DictReader, or remove the first row yourself.

json.decoder.JSONDecodeError: Expecting property name enclosed in double quotes Single quotes, or a trailing comma. JSON is not Python.

TypeError: Object of type set is not JSON serializable A set, a date or an object of your own was passed. Convert it to a list or text first.

My tuples came back as lists As they should — JSON has no tuple. Call tuple(...) yourself afterwards if you need one.

data[1] worked, and after a round trip it raises KeyError JSON keys are always text. It is data["1"] now.

Your turn

Make a books.csv with the columns title,author,pages,year and at least six rows. Put a comma in at least one title (so it has to be quoted), leave pages empty in one row, and put letters where pages belongs in another.

Then write library.py which:

1. Reads the file with DictReader, collecting a complaint with a line number for each bad row
2. Builds a summary by author — how many books each, and how many pages in total
3. Writes the whole thing to library.json with indent=2
4. Reads that file back and confirms the total is unchanged

Then five experiments:

• Use line.split(",") instead of DictReader. What happens to the title with the comma?
• Remove newline="" when writing, then open the CSV file and look.
• Put a set in the summary (the set of authors, say) and try to write the JSON. Read the message, then fix it.
• Open library.json by hand, change one double quote to a single quote, and try to read it. Does the message give a line and a column?
• Use the year as a dictionary key — {2020: [...]} — then write it and read it back. What do you have to look it up with now?

Those last two make this chapter's point: writing to a file is translating, and every translation changes something. Knowing what changes lets you choose the format; not knowing it means the format chooses for you.