◀ Course contents Part 4 · Module 4-05

Working with Data

The job that is ninety per cent of the job

Read a file, work out what is wrong with it, reshape what is left, and write something somebody can act on. That is most data work, and it needs no library beyond what Python ships with. This module assembles the pieces you already have — dictionaries, generators, exceptions, files — into the pipeline they were always for.

Ready?

`split(",")` Is Not a CSV Parser

It works right up until a field contains a comma, which real data does constantly — addresses, product names, anything with a list in it:

row = 'SKU-1,"Tea, green",3,1250'

row.split(",")        # 5 fields — wrong
csv.reader([row])     # ['SKU-1', 'Tea, green', '3', '1250']

The quotes are the escaping mechanism, and handling them properly means tracking quote state across the line. The csv module does that, along with embedded newlines, doubled quotes and several dialects you would rather not know about.

csv.DictReader goes further and uses the header row as keys, so the rest of the code stops caring about column positions:

with open("stock.csv", newline="") as f:
    for row in csv.DictReader(f):
        row["name"], row["quantity"]

newline="" is not optional. The csv module does its own line-ending handling, and without it a field containing a newline is split in the wrong place on some platforms.

Every value comes back as text

DictReader does no conversion at all, so row["quantity"] is "3", not 3. The type bugs from module 1-03 are waiting at exactly this point, which is why converting is part of reading rather than something done later.

Quick check

Why not split a CSV line on commas?

Grouping, Aggregating, Pivoting

Three shapes cover most of what anyone asks for, and you have written all three already:

# group: the rows belonging to each key
groups = defaultdict(list)
groups[row["region"]].append(row)

# aggregate: one number per key
totals[row["region"]] = totals.get(row["region"], 0) + value

# pivot: one number per pair of keys
cells[(row["region"], row["month"])] += value

The pivot is the interesting one, and it is only the aggregate with a tuple as the key — which works because a tuple is hashable, from module 2-06. That single idea replaces a nested dictionary and all the first-time checks that go with it.

Sorting a result usually needs a key function: a function passed to sorted that says what to compare. Passing a function as an argument is the same thing default_factory was doing:

def by_total(pair):
    return pair[1]

sorted(totals.items(), key=by_total, reverse=True)[:3]

Note there are no brackets after by_total. You are handing sorted the function itself, for it to call once per item — calling it yourself would pass the result of one call instead.

Quick check

What makes (region, month) usable as a dictionary key?

The Rows That Are Wrong

Real files have broken rows, and the decision about each one is a judgement rather than a technique. Three things to separate:

Malformed

The wrong number of fields, or a number that will not parse. Reject the row, count it, say which one it was.

Missing

An empty cell. Not zero — None, so the difference between "nobody reported" and "reported nothing" survives.

Implausible

A negative quantity, a date in 1970, a price of nine million. Syntactically fine and almost certainly wrong.

The None point is the one that costs money. Treating a missing value as zero drags every average down and nothing in the output says so:

present = [v for v in values if v is not None]
mean = statistics.mean(present) if present else None
coverage = f"{len(present)}/{len(values)}"

Reporting the coverage alongside the number is what makes the average honest. An average of four values out of two hundred is not wrong, but it is not what anybody assumed either.

Quick check

A quantity column has empty cells. What should they become?

Getting the Answer Back Out

Two formats, two audiences. CSV for a person with a spreadsheet; JSON for the next program.

with open("report.csv", "w", newline="") as f:
    writer = csv.writer(f)
    writer.writerow(["region", "total"])
    writer.writerows(rows)

with open("summary.json", "w") as f:
    json.dump(summary, f, sort_keys=True)

csv.writer handles the escaping on the way out too — a region called "North, East" gets its quotes back automatically, which hand-built ",".join(...) would not do.

And the discipline from module 3-06 still applies, more strongly here: the reading, the reshaping and the writing are separate functions. The reshaping is where the thinking is, and it should take a list and return a list with no file anywhere near it.

Report what you dropped

Every pipeline that filters should say how much it filtered. "Loaded 1,182 rows, rejected 4" is a report somebody can trust. A total with no denominator is a number that might be missing half its input, and nobody downstream can tell.

The count of what you threw away belongs in the output, not in the log nobody reads.

Quick check

Why use csv.writer rather than joining with commas?

0 of 9 completed

Real Python runs right here in your browser — nothing to install, nothing sent to a server. The interpreter downloads once the first time you press Run, then stays cached.

To do

Splitting a CSV line on commas works until a field contains one — which real data does constantly. The quotes are the escaping mechanism, and handling them means tracking quote state across the line.

row = 'SKU-1,"Tea, green",3,1250'
row.split(",")       # 5 fields — wrong
csv.reader([row])    # 4 fields — right

Your task: print how many fields each approach finds, then the correctly parsed row and its product name:

5
4
['SKU-1', 'Tea, green', '3', '1250']
Tea, green

your_code.py

import csv

row = 'SKU-1,"Tea, green",3,1250'

naive =
parsed =

print(len(naive))
print(len(parsed))
print(parsed)
print(parsed[1])

Python

Hint

naive is row.split(","). csv.reader takes an iterable of lines, so csv.reader([row]) — and next() pulls the one parsed row out of it.

Output

To do

csv.DictReader uses the header row as keys, so the rest of the code stops caring which column is third. Add a column to the file and nothing downstream breaks.

with open("stock.csv", newline="") as f:
    for row in csv.DictReader(f):
        print(row["name"])

newline="" is not optional — the csv module does its own line-ending handling, and without it a field containing a newline is cut in the wrong place.

Your task: read the file into rows and print the count, one name, and the type of a quantity:

3
Tea, green
str

The last line is the reminder that DictReader converts nothing.

your_code.py

import csv

with open("stock.csv", "w", newline="") as f:
    f.write('name,quantity,price\n"Tea, green",3,1250\nWhisk,12,990\nCloth,5,450\n')

rows =

print(len(rows))
print(rows[0]["name"])
print(type(rows[0]["quantity"]).__name__)

Python

Hint

Open the file with newline="" and wrap it in csv.DictReader, then list() the reader inside the with block — outside it the file is closed and there is nothing left to read.

Output

To do

Every value from a CSV is text, so the type bugs from module 1-03 are waiting at exactly this point. Convert as you read, and reject what will not convert rather than letting it travel.

Your task: read the rows, converting the quantity and price. Keep the good rows as dictionaries with real numbers, count the bad ones, and report each rejection:

rejected row 3: invalid literal for int() with base 10: 'x'
rejected row 5: invalid literal for int() with base 10: ''
Loaded: 3
Value: 14430

The row number counts data rows, starting at 1 for the first one after the header.

your_code.py

import csv

with open("stock.csv", "w", newline="") as f:
    f.write("name,quantity,price\ntea,3,1250\nwhisk,12,990\ntray,x,2200\nmat,-4,300\ncup,,500\n")

good = []
rejected = 0

# Read, convert, and report what will not convert.

print("Loaded:", len(good))
print("Value:", sum(r["quantity"] * r["price"] for r in good))

Python

Hint

Loop enumerate(csv.DictReader(f), start=1). Inside a try, convert both numbers with int(); in `except ValueError as error`, print f"rejected row {number}: {error}" and count it. The mat row converts fine — a negative quantity is a different kind of wrong, and this exercise is only about conversion.

Output

To do

Grouping is defaultdict(list) and one line, from module 3-05. It is the shape behind every "break this down by" question anybody asks.

groups = defaultdict(list)
for row in rows:
    groups[row["region"]].append(row["name"])

Your task: group the item names by region, then print the groups sorted by region, one line each, and the number of regions:

eu: cup, tea
jp: mat
us: cloth, whisk
Regions: 3

The names within each region are sorted too, so the report is stable.

your_code.py

from collections import defaultdict

rows = [
    {"region": "eu", "name": "tea"},
    {"region": "us", "name": "whisk"},
    {"region": "eu", "name": "cup"},
    {"region": "jp", "name": "mat"},
    {"region": "us", "name": "cloth"},
]

groups = defaultdict(list)
# Group the names by region.

# One line per region, sorted, then the count.

Python

Hint

groups[row["region"]].append(row["name"]) inside the loop. Then for region in sorted(groups): print with ", ".join(sorted(groups[region])).

Output

To do

A pivot is the aggregate with a tuple as the key. That works because a tuple is hashable, from module 2-06 — and it replaces a nested dictionary along with every first-time check inside it.

cells[(row["region"], row["month"])] += row["value"]

The key can be unpacked again on the way out, which is what makes the report readable.

Your task: total the value by region and month, then print one line per cell in sorted key order, and the number of cells:

eu 09: 4750
eu 10: 1250
us 09: 1200
us 10: 3000
Cells: 4

your_code.py

from collections import defaultdict

rows = [
    ("eu", "09", 3, 1250), ("eu", "09", 2, 500), ("us", "09", 4, 300),
    ("eu", "10", 1, 1250), ("us", "10", 6, 500),
]

cells = defaultdict(int)
# Total by region and month together.

# One line per cell, then the count.

Python

Hint

cells[(region, month)] += quantity * price inside the loop, unpacking each row into four names. Then for (region, month) in sorted(cells): — the tuple unpacks straight into the for line.

Output

To do

sorted takes a key: a function it calls once per item to decide what to compare. Passing a function as an argument is the same thing default_factory was doing.

def by_total(pair):
    return pair[1]

sorted(totals.items(), key=by_total, reverse=True)

No brackets after by_total. You are handing over the function itself; calling it yourself would pass the result of one call instead.

Your task: rank the regions by total, biggest first, and print the top two and the whole ranking:

us: 9000
eu: 6000
[('us', 9000), ('eu', 6000), ('jp', 4200)]

your_code.py

totals = {"eu": 6000, "us": 9000, "jp": 4200}

def by_total(pair):
    # What sorted should compare.
    pass

ranked =

for region, total in ranked[:2]:
    print(f"{region}: {total}")

print(ranked)

Python

Hint

by_total takes one (region, total) pair and returns pair[1]. Then ranked = sorted(totals.items(), key=by_total, reverse=True) — note the function is named, not called.

Output

To do

Treating an empty cell as zero drags every average down, and nothing in the output says so. None keeps the difference between "nobody reported" and "reported nothing" — and forces every later step to decide what to do about it.

Reporting the coverage alongside the number is what makes the average honest. An average of three values out of five is not wrong, but it is not what anybody assumed either.

Your task: convert the readings, using None for the blanks, then report both averages and the coverage:

[10, None, 30, None, 50]
Mean of present: 30
Mean if blanks were zero: 18
Coverage: 3/5

The second average is the wrong one, printed so the gap is visible.

your_code.py

import statistics

raw = ["10", "", "30", "", "50"]

readings = []
# Convert, using None for the blanks.

present = []
# The ones that are actually there.

print(readings)
print("Mean of present:", statistics.mean(present))
print("Mean if blanks were zero:", statistics.mean([r or 0 for r in readings]))
print(f"Coverage: {len(present)}/{len(readings)}")

Python

Hint

For each value: append int(value) when it is non-empty, and None when it is not — a conditional expression does it in one line. present is a comprehension keeping the ones that `is not None`.

Output

To do

Two formats, two audiences: CSV for a person with a spreadsheet, JSON for the next program.

csv.writer handles the escaping on the way out too — a region called North, East gets its quotes back automatically, which a hand-built ",".join(...) would not do.

Your task: write both files, then read them back and print what landed:

region,total
"North, East",4200
eu,6000
{'regions': 2, 'total': 10200}

The first three lines are the CSV as written; the last is the JSON read back.

your_code.py

import csv, json

rows = [("North, East", 4200), ("eu", 6000)]
summary = {"regions": 2, "total": 10200}

# Write report.csv with a header, then summary.json.

with open("report.csv", newline="") as f:
    for line in f.read().splitlines():
        print(line)

with open("summary.json") as f:
    print(json.load(f))

Python

Hint

Open report.csv with "w" and newline="", make a csv.writer, writerow the header ["region", "total"] and writerows(rows). Then json.dump(summary, f) into summary.json.

Output

To do

The discipline from module 3-06, applied to the messiest part of real work. The reshaping is where the thinking is, so it takes a list and returns a list with no file anywhere near it — and can therefore be checked in one line.

Your task: write totals_by_region(rows), taking a list of dictionaries and returning a sorted list of (region, total) pairs, largest first. Then read the CSV, pass it through, and write the result:

[('eu', 4750), ('us', 1200)]
region,total
eu,4750
us,1200

totals_by_region must not open, read or write anything.

your_code.py

import csv
from collections import defaultdict

with open("sales.csv", "w", newline="") as f:
    f.write("region,quantity,price\neu,3,1250\nus,4,300\neu,2,500\n")

def totals_by_region(rows):
    # A list of dicts in, a sorted list of pairs out. No files.
    pass

# Read the CSV, convert, pass it through, and write report.csv.

with open("report.csv", newline="") as f:
    for line in f.read().splitlines():
        print(line)

Python

Hint

Inside totals_by_region, accumulate into a defaultdict(int) and return sorted(totals.items(), key=..., reverse=True) with a small key function. Outside it, read with DictReader, convert both numbers with int(), call the function, print the result, then write the header and the pairs with csv.writer.

Output

The sales report

To do

One export, four questions, two output files. Everything this module covered, in the order real work does it.

The export is a CSV with a header. Some rows are broken; one product name contains a comma, so hand-parsing it would shift every column after it.

Write these three, none of which may open a file:

convert(row) — takes one DictReader dictionary and returns it with quantity and price as int. Lets int()'s own ValueError through for anything unconvertible.
pivot(rows) — returns a dict keyed by (region, month) holding the total value of each cell.
ranked(cells) — returns a list of ((region, month), total) pairs, largest total first, using a named key function.

Then run it. Read the export with DictReader, convert each row, report and count the ones that fail, pivot what is left, and write both report.csv (header region,month,total) and summary.json.

Print exactly seven lines:

rejected row 3: invalid literal for int() with base 10: 'x'
rejected row 5: invalid literal for int() with base 10: ''
Loaded 5, rejected 2
eu 09: 4750
us 10: 3000
Top cell: eu 09
{'cells': 4, 'loaded': 5, 'rejected': 2, 'total': 10200}

The two cell lines are the top two from ranked. Row numbers count data rows, starting at 1 after the header.

your_code.py

import csv, json
from collections import defaultdict

with open("sales.csv", "w", newline="") as f:
    f.write(
        'region,month,name,quantity,price\n'
        'eu,09,"Tea, green",3,1250\n'
        'eu,09,cup,2,500\n'
        'us,09,mat,x,300\n'
        'eu,10,tea,1,1250\n'
        'us,10,cup,6,\n'
        'us,09,mat,4,300\n'
        'us,10,whisk,6,500\n'
    )

def convert(row):
    # quantity and price as int. Let ValueError through.
    pass

def pivot(rows):
    # A dict keyed by (region, month).
    pass

def ranked(cells):
    # Largest total first, using a named key function.
    pass

# Read, convert, report rejections, pivot, rank, write both files.

# Seven lines of output.

Python

Hint

convert assigns int() to both fields and returns the row — no try, so the ValueError travels. In the reading loop, enumerate(csv.DictReader(f), start=1) and wrap the convert call in try/except ValueError as error, printing f"rejected row {number}: {error}". pivot accumulates into defaultdict(int) keyed by (row["region"], row["month"]). ranked sorts cells.items() with a key function returning pair[1] and reverse=True. For the CSV, writerow the header then one row per cell as (region, month, total). The summary is a plain dict written with json.dump and sort_keys=True.

Output

Sign in to open this module

Working with Data

split(",") Is Not a CSV Parser

Every value comes back as text

Quick check

Grouping, Aggregating, Pivoting

Quick check

The Rows That Are Wrong

Malformed

Missing

Implausible

Quick check

Getting the Answer Back Out

Report what you dropped

Quick check

The comma inside the field

Columns by name

Converting is part of reading

The rows belonging to each key

One number per pair of keys

Handing sorted a function

Missing is not zero

Out to CSV, and out to JSON

Read, reshape, write

The sales report

Notification

`split(",")` Is Not a CSV Parser