Lesson 11 of 16 · Working With Text and Data
Working With CSV Files
Read and write CSV files with Python's csv module: reader and writer, DictReader and DictWriter, converting numbers, and summarising a table of data.
- 7 min read
- Intermediate
- Reviewed
- What a CSV file is
- How to write and read CSV files with the csv module
- How DictReader and DictWriter make rows easier to work with
- How to convert text to numbers and summarise a table
What is a CSV file?
CSV stands for comma-separated values. It is a plain text file that holds a table: one row per line, with commas between the columns. The first line usually holds the column names.
name,department,salary
Ada,Engineering,95000
Grace,Research,88000Every spreadsheet program can open and save CSV files, and most datasets you will download for machine learning come as CSV.
It is tempting to read a CSV by splitting each line on commas, but that breaks as soon as a value contains a comma, such as "London, UK". Python's built-in csv module handles these cases correctly.
Writing a CSV file
import csv
rows = [
["name", "department", "salary"],
["Ada", "Engineering", 95000],
["Grace", "Research", 88000],
["Alan", "Engineering", 79000],
["Linus", "Research, Systems", 83000],
]
with open("staff.csv", "w", newline="", encoding="utf-8") as file:
writer = csv.writer(file)
writer.writerows(rows)
with open("staff.csv", encoding="utf-8") as file:
print(file.read())Look at Linus's row: the writer put quotation marks around "Research, Systems" automatically, so the comma inside the value is not mistaken for a column break.
Always open CSV files with newline="". Without it, some computers add blank lines between the rows.
Reading a CSV file
csv.reader gives you each row as a list of strings.
import csv
with open("staff.csv", "w", newline="", encoding="utf-8") as file:
csv.writer(file).writerows([
["name", "department", "salary"],
["Ada", "Engineering", 95000],
["Grace", "Research", 88000],
])
with open("staff.csv", newline="", encoding="utf-8") as file:
reader = csv.reader(file)
header = next(reader)
print("Columns:", header)
for row in reader:
print(row)next(reader) takes the first row, the header, so the loop only sees the data. Notice that the salaries come back as text: '95000', not 95000. A CSV file only stores text.
Rows as dictionaries: DictReader
Positions like row[2] are easy to get wrong. csv.DictReader uses the header row to turn every row into a dictionary, so you can write row["salary"] instead.
import csv
with open("staff.csv", "w", newline="", encoding="utf-8") as file:
csv.writer(file).writerows([
["name", "department", "salary"],
["Ada", "Engineering", 95000],
["Grace", "Research", 88000],
["Alan", "Engineering", 79000],
])
with open("staff.csv", newline="", encoding="utf-8") as file:
for row in csv.DictReader(file):
print(row["name"], "works in", row["department"])Converting and summarising
To do maths, convert the text to numbers with int() or float(). Here is a small summary of the table: the total salary bill and the average per department.
import csv
with open("staff.csv", "w", newline="", encoding="utf-8") as file:
csv.writer(file).writerows([
["name", "department", "salary"],
["Ada", "Engineering", 95000],
["Grace", "Research", 88000],
["Alan", "Engineering", 79000],
["Mei", "Research", 91000],
])
with open("staff.csv", newline="", encoding="utf-8") as file:
staff = list(csv.DictReader(file))
total = sum(int(person["salary"]) for person in staff)
print(f"Total salaries: {total:,}")
by_department = {}
for person in staff:
by_department.setdefault(person["department"], []).append(int(person["salary"]))
for department, salaries in by_department.items():
print(f"{department}: average {sum(salaries) / len(salaries):,.0f}")list(csv.DictReader(file)) reads every row into a list, so you can use the data after the file is closed. setdefault creates an empty list for a department the first time it appears.
Writing dictionaries: DictWriter
csv.DictWriter writes dictionaries. You tell it the column order with fieldnames.
import csv
results = [
{"model": "baseline", "accuracy": 0.81},
{"model": "improved", "accuracy": 0.874},
]
with open("results.csv", "w", newline="", encoding="utf-8") as file:
writer = csv.DictWriter(file, fieldnames=["model", "accuracy"])
writer.writeheader()
writer.writerows(results)
with open("results.csv", encoding="utf-8") as file:
print(file.read())Common mistakes
Read a CSV file of exam scores and summarise it.
import csv
with open("scores.csv", "w", newline="", encoding="utf-8") as file:
csv.writer(file).writerows([
["student", "score"],
["Ada", "92"],
["Grace", "85"],
["Alan", "58"],
["Mei", "77"],
])
# Read scores.csv with csv.DictReader.
# Print how many students passed (score of 60 or more) and the average score.
# Output must be exactly:
# Passed: 3
# Average: 78.0
What type is row['salary'] when you read a CSV file with csv.DictReader?
Summary
- A CSV file is a table stored as text: one row per line, columns separated by commas.
- Use the
csvmodule, and open files withnewline="". csv.readergives lists, andcsv.DictReadergives dictionaries keyed by the header.- Every value is a string: convert with
int()orfloat()before calculating. csv.writerandcsv.DictWriterwrite rows, and quote values that contain commas.
Next: working with dates and times.
Finished reading? Tick it off to track your progress.