Velamuvi AIAcademy

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
In this lesson you will learn
  • 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,88000

Every 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

Try it yourselfPython
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.

Try it yourselfPython
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.

Try it yourselfPython
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.

Try it yourselfPython
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.

Try it yourselfPython
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

Practice exercise

Read a CSV file of exam scores and summarise it.

Your solutionPython
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
Knowledge check

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 csv module, and open files with newline="".
  • csv.reader gives lists, and csv.DictReader gives dictionaries keyed by the header.
  • Every value is a string: convert with int() or float() before calculating.
  • csv.writer and csv.DictWriter write rows, and quote values that contain commas.

Next: working with dates and times.

Finished reading? Tick it off to track your progress.