After this lesson you will be able to save a table of data as a CSV file, read one back, and store nested data as JSON. These two formats cover most of the simple data files you will meet.
CSV: a table as text
A CSV (comma-separated values) file is a table written as plain text. Each line is a row and commas separate the columns. Spreadsheet programs can open and save it. Python's csv module reads and writes it for you. First, write a file of book loans:
import csv
rows = [
["title", "member", "days"],
["Dune", "Asha", 14],
["Emma", "Ben", 7],
["Ulysses", "Chloe", 21],
]
with open("loans.csv", "w", newline="", encoding="utf-8") as f:
writer = csv.writer(f)
writer.writerows(rows)
with open("loans.csv", encoding="utf-8") as f:
print(f.read())title,member,days
Dune,Asha,14
Emma,Ben,7
Ulysses,Chloe,21Always open CSV files with newline="". This lets the csv module manage line endings itself and prevents blank lines appearing between rows on some systems.
Reading rows
csv.reader gives you each row as a list of strings.
with open("loans.csv", newline="", encoding="utf-8") as f:
reader = csv.reader(f)
header = next(reader)
for row in reader:
print(row)
print(header)['Dune', 'Asha', '14']
['Emma', 'Ben', '7']
['Ulysses', 'Chloe', '21']
['title', 'member', 'days']Calling next(reader) takes the first row off, which is the header. Note that 14 comes back as the text "14". The csv module never converts types, so do it yourself:
total_days = 0
with open("loans.csv", newline="", encoding="utf-8") as f:
reader = csv.reader(f)
next(reader)
for title, member, days in reader:
total_days += int(days)
print(total_days)42Using column names with DictReader
Remembering that column 2 means "days" is error-prone. DictReader uses the header row as keys, so each row becomes a dictionary.
with open("loans.csv", newline="", encoding="utf-8") as f:
for row in csv.DictReader(f):
print(row["title"], "borrowed by", row["member"], "for", row["days"], "days")Dune borrowed by Asha for 14 days
Emma borrowed by Ben for 7 days
Ulysses borrowed by Chloe for 21 daysWriting works the same way with DictWriter, where you list the column names and write a header once:
loans = [
{"title": "Hamlet", "member": "Daniel", "days": 10},
{"title": "Persuasion", "member": "Elena", "days": 5},
]
with open("new-loans.csv", "w", newline="", encoding="utf-8") as f:
writer = csv.DictWriter(f, fieldnames=["title", "member", "days"])
writer.writeheader()
writer.writerows(loans)
with open("new-loans.csv", encoding="utf-8") as f:
print(f.read())title,member,days
Hamlet,Daniel,10
Persuasion,Elena,5Why not just split on commas?
A value can itself contain a comma. The csv module quotes such values when writing and understands the quotes when reading. A plain split(",") breaks them apart.
import io
line = '"Smith, Asha",Dune,14'
print(line.split(","))
print(next(csv.reader(io.StringIO(line))))['"Smith', ' Asha"', 'Dune', '14']
['Smith, Asha', 'Dune', '14']JSON: nested data as text
JSON is a text format for structured data and the standard way programs exchange information. It looks very much like Python dictionaries and lists, and has these types: objects (dictionaries), arrays (lists), strings, numbers, true, false and null. The json module converts in both directions.
import json
shop = {
"name": "Corner Books",
"open": True,
"owner": None,
"stock": [{"title": "Dune", "copies": 3}, {"title": "Emma", "copies": 0}],
}
text = json.dumps(shop)
print(text)
print(type(text)){"name": "Corner Books", "open": true, "owner": null, "stock": [{"title": "Dune", "copies": 3}, {"title": "Emma", "copies": 0}]}
<class 'str'>Notice that Python's True and None became true and null. Add indent for a readable layout, and use loads to turn text back into Python objects:
print(json.dumps(shop["stock"][0], indent=2))
back = json.loads(text)
print(back["stock"][1]["title"])
print(back == shop){
"title": "Dune",
"copies": 3
}
Emma
TrueSaving JSON to a file
json.dump writes to a file object and json.load reads from one. The s in dumps and loads stands for string.
with open("shop.json", "w", encoding="utf-8") as f:
json.dump(shop, f, indent=2)
with open("shop.json", encoding="utf-8") as f:
loaded = json.load(f)
print(loaded["name"], len(loaded["stock"]))Corner Books 2What JSON cannot hold
JSON has fewer types than Python. Dictionary keys become strings, tuples become lists, and objects such as sets or dates cannot be written at all.
data = {1: "one", "pair": (3, 4)}
print(json.loads(json.dumps(data))){'1': 'one', 'pair': [3, 4]}json.dumps({"tags": {"new", "sale"}})TypeError: Object of type set is not JSON serializableConvert sets and dates to lists or strings first. Also, invalid JSON text raises an error that you can catch:
try:
json.loads("{'name': 'Corner Books'}")
except json.JSONDecodeError as err:
print("Bad JSON:", err)Bad JSON: Expecting property name enclosed in double quotes: line 1 column 2 (char 1)JSON requires double quotes, which is why the single-quoted text above is rejected.
Choosing between them
Use CSV for flat tables, especially when the data might be opened in a spreadsheet. Use JSON when the data is nested or has lists inside records, as when a shop holds categories and each category holds products.
Common mistakes
- Leaving out
newline="". On some systems you get an extra blank line after each row. - Treating CSV values as numbers. They are strings.
"9" + "1"is"91", not 10. - Splitting rows with
split(","). It breaks as soon as a value contains a comma. - Mixing up
loadandloads. The one withsworks with strings, the other with files. - Using single quotes in JSON. The format requires double quotes. A Python dictionary printed with
print()is not valid JSON.
Exercise
Using json, save the dictionary {"student": "Asha", "marks": [72, 88, 91]} to marks.json, load it back, and print the average mark rounded to one decimal place.
Show one possible answer
import json
record = {"student": "Asha", "marks": [72, 88, 91]}
with open("marks.json", "w", encoding="utf-8") as f:
json.dump(record, f)
with open("marks.json", encoding="utf-8") as f:
loaded = json.load(f)
average = sum(loaded["marks"]) / len(loaded["marks"])
print(loaded["student"], round(average, 1))Asha 83.7Next: paths with pathlib shows a cleaner way to build file names and work with folders. A video version of this topic is in the video course at /learn/python/.
Key points
- CSV stores a table as plain text, one row per line with values separated by commas. Use the csv module rather than splitting on commas yourself.
- Open CSV files with newline='' and use csv.DictReader and csv.DictWriter to work with column names.
- Everything read from a CSV file is a string, so convert numbers yourself.
- JSON stores nested data of dictionaries, lists, strings, numbers, true, false and null. json.dumps and json.loads convert to and from text, and json.dump and json.load work with files.
- JSON keys are always strings, and tuples come back as lists.