Python Read CSV and Write CSV with DictReader

In lesson 6 we started working with files, and we had a simple file like this:

Each line had one URL, so things were simple.

But in real SEO work, when you crawl a site or export data from a tool, you usually won't have just a URL. You might have data like this:

So each page has more than one piece of information. We have URL, Status Code, Title and Indexability.

And here, the plain text file we used before isn't the best shape for the data. That's why we'll start working with one of the most important file types you'll meet as an SEO: CSV.

And once we understand it, we'll understand how it relates to Excel files.

First, what is a CSV?

Let's start with the file itself, before Python. If we have this table:

it might be saved in a file called crawl.csv. And if we open the file's contents as text, we might find it like this:

Look at the first line, URL,Status,Title. Those are the column names. And after it, https://example.com/,200,Home is the first row of data.

The values here are separated by ,, a comma. That's where the name CSV comes from: Comma-Separated Values.

But for us, understanding the file's shape matters more than what the name means.

Each line is a row. And each value in the line is a column.

Making a first CSV file

Make a file called crawl.csv and put this in it:

Now you might say: we learned files in lesson 6, can I read this file with open()? You can. Let's try:

URL,Status,Title
https://example.com/,200,Home
https://example.com/about/,200,About Us
https://example.com/old-page/,404,Old Page
https://example.com/blog/,200,Blog

The code works. But the problem is that Python read the whole file as text.

We don't want URL,Status,Title as one piece of text. We want Python to understand that there are three columns: URL, Status and Title.

And for that, we need a tool made for CSV.

Using the csv library

Python has a ready-made tool called csv. To use it, we write import csv.

We met import in the last lesson, when we used from urllib.parse import urlparse. Same idea. We're telling Python: I need the CSV tools in this program.

Let's try a first read:

['URL', 'Status', 'Title']
['https://example.com/', '200', 'Home']
['https://example.com/about/', '200', 'About Us']
['https://example.com/old-page/', '404', 'Old Page']
['https://example.com/blog/', '200', 'Blog']

Notice the difference. Before csv.reader(), each line was just text. Now each row is a list.

The first row is ['URL', 'Status', 'Title']. The second is ['https://example.com/', '200', 'Home']. So Python has started to understand that the line holds separate values.

What's reader?

We have the line reader = csv.reader(file). We took the file file, sent it to csv.reader(), and told it:

Then we kept the result in reader. Then we used for row in reader:, meaning:

In the first round, row is ['URL', 'Status', 'Title']. In the second, row is ['https://example.com/', '200', 'Home']. And so on.

Notice that we're back to using things we've learned: files, loops and lists.

The only new thing is that csv.reader() helped us understand the shape of a CSV.

How do we get one value from the row?

Take this row:

We learned that lists have indexes. The first item is row[0], the second row[1], the third row[2]. Let's try:

https://example.com/
200
Home

So if we're reading a CSV:

URL
https://example.com/
https://example.com/about/
https://example.com/old-page/
https://example.com/blog/

We printed the first column. And if we use:

Status
200
200
404
200

There's a small problem

Our first result is URL, and the first result in the status column is Status. But those aren't page data. They're the column names.

The first line of a CSV is usually the header, the column titles. So URL,Status,Title isn't a page on the site.

We need to work with the data by column name instead of saying row[0], row[1], row[2] all the time.

And there's a better way, especially since we've already learned dictionaries.

Using DictReader

Instead of csv.reader(), we'll use csv.DictReader(). Let's try:

{'URL': 'https://example.com/', 'Status': '200', 'Title': 'Home'}
{'URL': 'https://example.com/about/', 'Status': '200', 'Title': 'About Us'}
{'URL': 'https://example.com/old-page/', 'Status': '404', 'Title': 'Old Page'}
{'URL': 'https://example.com/blog/', 'Status': '200', 'Title': 'Blog'}

Now things are much easier. Each row is a dictionary. So instead of saying row[0] and trying to remember what column zero was, we can say row["URL"].

For the status: row["Status"]. For the title: row["Title"]. Let's try:

https://example.com/
https://example.com/about/
https://example.com/old-page/
https://example.com/blog/

Notice that URL itself didn't appear this time. Why? Because DictReader understood that the first line, URL,Status,Title, is the column names, and used them as the dictionary's keys.

Printing more about each page

https://example.com/ 200 Home
https://example.com/about/ 200 About Us
https://example.com/old-page/ 404 Old Page
https://example.com/blog/ 200 Blog

Now we can get at any piece of information in the crawl file.

A first SEO use: pulling out the 404 pages

Now we have crawl.csv with:

We want the 404 pages. We make an empty list:

Then we check each row:

['https://example.com/old-page/']

The code is very simple, because we know everything in it. The only new thing is that the data comes from a CSV.

But why did we write "404" and not 404?

Look at the condition if row["Status"] == "404":. We didn't write if row["Status"] == 404:. Why?

Let's see what Python read the status as.

200 <class 'str'>
200 <class 'str'>
404 <class 'str'>
200 <class 'str'>

So the values arrive as strings, even when they look like numbers. That's very important to know about CSV files.

A CSV doesn't tell Python: this column is an integer and this one is a string. At heart it's a text file. That's why 404 reached Python as a string.

If we need to treat it as a number, we can convert it with int(). For example:

404
<class 'int'>

But in our current example we don't need any math on the status code. So we can simply compare row["Status"] == "404".

Let's add a bit more data

Let's make the file closer to something you'd see in a crawl. Make crawl.csv like this:

Now we want the pages that are Non-Indexable. We write:

['https://example.com/old-page/', 'https://example.com/private/']

Now we're using CSV the way we really will. Instead of a file with URLs only, we have a data set with several pieces of information about each URL.

We can use more than one condition

We want the pages where Status = 200 and, at the same time, Indexability = Non-Indexable. We learned and before. So we write:

['https://example.com/private/']

Why wasn't old-page added? It's Non-Indexable, true. But its status is 404, and we asked for both conditions together.

A very important problem: the column name has to be exact

Say the file has Address,Status Code,Title 1, but you wrote row["URL"]. Python won't understand that, to you, Address and URL mean the same thing.

The key has to match the column name. Let's see the column names that are actually there. We can use reader.fieldnames. Let's try:

['URL', 'Status', 'Title', 'Indexability']

This is a very useful move whenever you get a new CSV. Before you start writing conditions on the columns, find out which column names are actually there, especially since column names differ from one tool to another.

Writing a new CSV

So far we're reading data. But just as we did with TXT files, we want to save the results.

Say we pulled out the 404 pages:

We could save them in a TXT, of course. But now we want a CSV. We'll use csv.writer(). Let's try the simplest example:

URL,Issue
https://example.com/old-page/,404

Two new things appeared here: csv.writer() and writer.writerow().

The first sets up a tool that writes CSV. The second writes one row. So writer.writerow(["URL", "Issue"]) wrote URL,Issue. And then writer.writerow(["https://example.com/old-page/", "404"]) wrote the second row.

So what's newline=""?

Notice we wrote with open("results.csv", "w", encoding="utf-8", newline="") as file:

With CSV files we usually use newline="" so that Python and the csv library handle the ends of rows correctly, mainly to avoid extra empty lines between rows on some systems.

You don't need to learn any more of the inner details right now. Just take this as the shape we use when writing a CSV: with open("results.csv", "w", encoding="utf-8", newline="") as file:

Pulling out the 404 pages and saving them in a CSV

Back to the file:

First we read it:

[{'URL': 'https://example.com/old-page/', 'Status': '404', 'Title': 'Old Page', 'Indexability': 'Non-Indexable'}]

This time we didn't store only the URL. We wrote broken_pages.append(row), meaning we kept the whole row.

That's useful, because when we save the result, we may need URL, Status, Title and Indexability, not just URL.

Using DictWriter

Since our data is dictionaries, there's a tool that fits perfectly: csv.DictWriter(). Let's try:

URL,Status,Title,Indexability
https://example.com/old-page/,404,Old Page,Non-Indexable

The code looks bigger, but if we break it down, you'll find it simple.

Understanding fieldnames

We wrote fieldnames = ["URL", "Status", "Title", "Indexability"]. We're setting the columns the new file will have.

Then writer = csv.DictWriter(file, fieldnames=fieldnames) means: I'll write a CSV using dictionaries, and these are the column names.

Then writer.writeheader() wrote the first row, URL,Status,Title,Indexability. Then for row in broken_pages: writer.writerow(row) wrote each row.

Why do DictReader and DictWriter matter to us?

Picture a file with 25 columns. If we used row[17] and you came back to the code a week later, would you remember what column 17 was? Probably not.

But when you see row["Status"] or row["Indexability"], you know straight away what we're working with.

That's why, in SEO work, when a CSV has clear headers, working with dictionaries is usually easier to follow.

So where does Excel come in?

So far we've worked with .csv. But you might have a file called crawl.xlsx, which is an Excel file.

And here we need to tell two things apart.

A CSV file, crawl.csv, is at heart a text file. That's why we could open it with open() and the csv library.

But an Excel file, crawl.xlsx, isn't just a text file separated by commas. It can hold:

• More than one sheet.
• Cell formatting.
• Formulas.
• Different data types.
• Properties specific to Excel files.

That's why we can't do:

and expect to read Excel like TXT or CSV.

CSV or Excel?

If you open both with your own eyes, you may see a table either way. But to Python, they aren't the same type of file.

You can picture a CSV file like this:

While Excel is a workbook that can hold:

And each sheet holds rows and columns. So if you export data from an SEO tool, you need to know whether you have a .csv or an .xlsx, because the way you read them is different.

Will we use Excel directly now?

We can work with .xlsx in Python using dedicated libraries.

But if we jumped into a new library now and started learning workbooks, worksheets, cells, rows and columns, we'd drift away from the core idea we want to understand.

The goal at this stage is to understand tabular data itself: rows, columns, headers and values.

And we understood that with CSV. In the next lesson, when we get to Pandas, we'll read CSV and Excel in a much easier way, and start really analyzing the data.

But it's important, starting now, not to treat .csv and .xlsx as the same file just because both can open in Excel.

Can I turn Excel into CSV?

Yes. If you have an Excel file and the goal is just to apply today's lesson, you can save or export the sheet you need as a CSV, then use the code we learned on it.

For example, crawl.xlsx becomes crawl.csv. Then we can read it:

The output depends on the data in the file, but it looks like:

A problem you might hit with real files

Sometimes you get a CSV, open it, and find the first column name has a strange symbol in it, or Python handles the first header in an unexpected way.

That can happen because of how the file was saved and encoded.

So far we've used encoding="utf-8". But with some CSV files that come from Excel or certain tools, you may need encoding="utf-8-sig". For example:

['URL', 'Status', 'Title']

You don't need to use utf-8-sig on every file right now. But I want you to know about it, because you'll get real files from different tools, and if the first header looks odd, the encoding is one of the first things to check.

A full SEO program

Now let's build a program that brings together what we learned. We have a crawl.csv file containing:

We want the program to do two things: pull out the 404 pages, and pull out the 200 pages that aren't indexable.

404 Pages:
https://example.com/old-page/
https://example.com/missing/

200 Non-Indexable Pages:
https://example.com/private/

Notice we used if twice, not if and then elif, because we're running two separate tests.

In this example a page won't be both 404 and 200 at the same time, but we're building each group from its own separate condition.

Saving the results

We'll save the 404 pages in 404-pages.csv, and the non-indexable 200 pages in non-indexable-200.csv. The code:

404 Pages:
https://example.com/old-page/
https://example.com/missing/

200 Non-Indexable Pages:
https://example.com/private/

And in the same folder you'll see crawl.csv, non-indexable-200.csv and 404-pages.csv.

Python did something very close to real SEO work here.

Let's stop at an important point

So far we've been doing things like this: for row in reader:, then if row["Status"] == "404":, then broken_pages.append(row).

And that's great for understanding Python itself. But picture a file with 100,000 rows where you want to do:

• Filtering.
• Sorting.
• Grouping.
• Working out averages.
• Counting each status code.
• Comparing two files.
• Finding the pages with more than a certain number of clicks.
• Merging a crawl with Search Console data.

We could do all of that the current way. But we'd write a lot of code.

That's where, in the next lesson, we'll see why the Pandas library matters so much to an SEO. Not because it's a famous library, but because it lets us treat the table as a table, instead of building every analysis by hand with loops and lists.

But if we went into Pandas without today's lesson, you might memorize commands like read_csv() and DataFrame without understanding the data Python is reading in the first place.

Now you have the foundation.

What you should understand from this lesson

When you see a crawl.csv file, you understand it's tabular data. There are rows and columns, and the first row usually holds the headers.

With csv.reader(), each row comes back as a list. For example ['https://example.com/', '200', 'Home'].

With csv.DictReader(), it becomes a dictionary like {"URL": "https://example.com/", "Status": "200", "Title": "Home"}. That lets us write row["URL"] instead of row[0].

And we can write a CSV with csv.writer() or csv.DictWriter().

You also need to understand that CSV and Excel aren't the same type of file. .csv is a text file holding tabular data. .xlsx is an Excel file with a different structure, and we'll handle it more easily in the next stage with Pandas.

The practical test

Make a file called seo-data.csv and put this in it:

First, write a program that opens the file with csv.DictReader() and prints the column names in the file.

The expected output:

Then make three lists:

• broken_pages should hold the pages whose status is 404.
• redirect_pages should hold the pages whose status is 301.
• non_indexable_pages should hold the pages where Indexability = Non-Indexable.

Then print each group. Notice that some pages can show up in more than one group, and that's fine, because each group answers a different question.

For the last part of the test, save the 404 pages in a file called 404-pages.csv. But don't save only the URL. The new file should have URL, Status, Title and Indexability, so you keep the whole row.

If you managed this part yourself, you now understand the whole journey: SEO tool, then CSV export, then Python, then rows and columns, then filtering, then a new CSV.

In lesson 9 we start Pandas, and we'll see how the same analysis we wrote with loops and lists can be done in a more powerful and easier way once the data starts to grow.