Python Working with CSV Files

Table of Contents

In the last lesson, we learned about Python Append Mode. After learning how to read and write text files, let us learn about CSV files in Python. CSV means “Comma Separated Values.” It is a very common format for storing data.

In this tutorial, we will learn how to read CSV files, write CSV files, and work with CSV data using dictionaries.

What Is a CSV File?

A CSV file is a text file where each line is a row of data. And each value in the row is separated by a comma.

Here is an example:

name,age,city
Alex,25,London
Sam,30,Manchester
John,22,Birmingham

The first line is the header. It tells us what each column means.

CSV files are used everywhere. Excel files can be saved as CSV. Databases can export to CSV. Data analysis often starts with CSV files.

Why Use CSV Files in Python?

CSV files are simple. They are just text. We can open them in any text editor. We can read them in Excel or Google Sheets. And Python can read and write them easily.

That is why they are so popular.

How to Read a CSV File in Python

Python has a built-in module called csv. We import it and use its functions.

Let’s say we have a file called people.csv:

name,age,city
Alex,25,London
Sam,30,Manchester
John,22,Birmingham

We can read it like this:

import csv


with open("people.csv", "r") as file:
    reader = csv.reader(file)
    for row in reader:
        print(row)

Output 

['name', 'age', 'city']
['Alex', '25', 'London']
['Sam', '30', 'Manchester']
['John', '22', 'Birmingham']

Each row is a list of strings. The first row is the header.

If we want to skip the header, we can use next().

import csv


with open("people.csv", "r") as file:
    reader = csv.reader(file)
    header = next(reader)
    print(f"Header: {header}")
    for row in reader:
        print(row)

Output 

Header: ['name', 'age', 'city']
['Alex', '25', 'London']
['Sam', '30', 'Manchester']
['John', '22', 'Birmingham']

How to Read CSV as Dictionaries in Python

It is often more convenient to read CSV as dictionaries. The csv.DictReader does that. It uses the header row as keys.

import csv


with open("people.csv", "r") as file:
    reader = csv.DictReader(file)
    for row in reader:
        print(row)

Output 

{'name': 'Alex', 'age': '25', 'city': 'London'}
{'name': 'Sam', 'age': '30', 'city': 'Manchester'}
{'name': 'John', 'age': '22', 'city': 'Birmingham'}

Now each row is a dictionary. We can access values by key.

with open("people.csv", "r") as file:
    reader = csv.DictReader(file)
    for row in reader:
        print(f"{row['name']} is {row['age']} years old and lives in {row['city']}")

Output 

Alex is 25 years old and lives in London
Sam is 30 years old and lives in Manchester
John is 22 years old and lives in Birmingham

That is much easier to work with.

How to Write a CSV File in Python

To write a CSV file, we use csv.writer.

import csv


with open("output.csv", "w", newline="") as file:
    writer = csv.writer(file)
    writer.writerow(["name", "age", "city"])
    writer.writerow(["Alex", 25, "London"])
    writer.writerow(["Sam", 30, "Manchester"])
    writer.writerow(["John", 22, "Birmingham"])

After running this, output.csv contains:

name,age,city
Alex,25,London
Sam,30,Manchester
John,22,Birmingham

Notice the newline="" in the open() call. That is important. Without it, on Windows, we might get extra blank lines.

How to Write Multiple Rows to a CSV File in Python

We can use writerows() to write multiple rows at once.

import csv


data = [
    ["name", "age", "city"],
    ["Alex", 25, "London"],
    ["Sam", 30, "Manchester"],
    ["John", 22, "Birmingham"]
]


with open("output.csv", "w", newline="") as file:
    writer = csv.writer(file)
    writer.writerows(data)

That does the same thing.

How to Write CSV as Dictionaries in Python

We can use csv.DictWriter to write dictionaries.

import csv


data = [
    {"name": "Alex", "age": 25, "city": "London"},
    {"name": "Sam", "age": 30, "city": "Manchester"},
    {"name": "John", "age": 22, "city": "Birmingham"}
]


with open("output.csv", "w", newline="") as file:
    fieldnames = ["name", "age", "city"]
    writer = csv.DictWriter(file, fieldnames=fieldnames)
    writer.writeheader()
    writer.writerows(data)

writeheader() writes the header row. writerows() writes the data rows.

Python CSV Example – Reading and Calculating Averages

Let’s read a CSV of students and calculate their average score.

import csv


with open("students.csv", "r") as file:
    reader = csv.DictReader(file)
    for row in reader:
        name = row["name"]
        score1 = int(row["score1"])
        score2 = int(row["score2"])
        average = (score1 + score2) / 2
        print(f"{name}: Average = {average:.1f}")

If students.csv contains:

name,score1,score2
Alex,85,90
Sam,92,88
John,78,82

The output is:

Alex: Average = 87.5
Sam: Average = 90.0
John: Average = 80.0

That is a simple way to process data from a CSV file.

Another Example – Writing Results to CSV in Python

import csv


results = [
    {"name": "Alex", "average": 87.5},
    {"name": "Sam", "average": 90.0},
    {"name": "John", "average": 80.0}
]


with open("results.csv", "w", newline="") as file:
    fieldnames = ["name", "average"]
    writer = csv.DictWriter(file, fieldnames=fieldnames)
    writer.writeheader()
    writer.writerows(results)

The file results.csv will contain:

name,average
Alex,87.5
Sam,90.0
John,80.0

That is clean and easy.

Common Mistakes When Working with CSV Files in Python

Mistake 1: Forgetting newline="" when writing

with open("output.csv", "w") as file:
    writer = csv.writer(file)
    writer.writerow(["a", "b"])

On Windows, this adds an extra blank line between rows.

Fix: Add newline="".

with open("output.csv", "w", newline="") as file:
    writer = csv.writer(file)
    writer.writerow(["a", "b"])

Mistake 2: Not converting numbers

When we read a CSV, all values are strings. If we want to do math, we need to convert them.

age = int(row["age"])

Mistake 3: Using the wrong delimiter

Some CSV files use semicolons or tabs instead of commas. We can specify the delimiter.

reader = csv.reader(file, delimiter=";")

Mistake 4: Forgetting to import csv

with open("data.csv", "r") as file:
    reader = csv.reader(file)

This gives an error if we did not import csv.

Fix:

import csv

Next, we will learn Python Working with JSON Files, which helps us store nested data in a format many apps and APIs use.

Previous Python Append Mode Next Python Working with JSON Files