Pandas Tutorial for SEO: read_csv, merge and groupby

In the last lesson we worked on a CSV file like this:

With the csv library, we read the rows, checked each one, and then pulled out, for example, the 404 pages:

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

There's nothing wrong with this code. In fact, it was very important to learn this way first, because now you understand what's happening.

But picture your crawl file with 100,000 URLs and 40 columns, and you want to do things like:

• Get the 404 pages.
• Get the 200 pages that aren't indexable.
• Sort the pages by word count.
• Find out how many pages there are for each status code.
• Get the pages with an empty title.
• Pick just 5 columns out of 40.
• Save the result in a new CSV.

We could do all of that with loops and lists. But we'd write a lot of code. And this is where the Pandas library comes in.

The goal of today's lesson isn't to memorize Pandas commands. It's to understand how Pandas changes the way we work with SEO data.

First, what is Pandas?

The Pandas library is made for working with tabular data. That is, data like this:

And that's roughly the shape of a very large part of the data we'll handle in SEO.

Whether it comes from:

In the end, you usually have rows + columns.

Pandas lets us treat that table as one unit.

Instead of writing for row in reader: for every analysis, we can tell Pandas something closer to:

And it does the filtering. But before we use it, we need to install it.

Installing Pandas

The csv library we used in the last lesson comes with Python. That's why we just wrote import csv.

But Pandas is an external library, so it has to be installed on your computer first. Open the Terminal or Command Prompt and type:

Once it's installed, you can use it in Python.

If that command didn't work for you, you can use:

You install it once on the Python setup you're working with, not every time you write a program.

Once it's installed, we can start.

Importing Pandas

We write:

You might ask: why not just write import pandas? You can. But the convention almost everywhere is to import Pandas under the short name pd.

So instead of writing pandas.read_csv() later, we'll write pd.read_csv().

So import pandas as pd means:

Reading a first CSV with Pandas

We'll use the same crawl.csv file, containing:

We write:

                             URL  Status         Title   Indexability
0           https://example.com/     200          Home      Indexable
1     https://example.com/about/     200      About Us      Indexable
2  https://example.com/old-page/     404      Old Page  Non-Indexable
3   https://example.com/private/     200  Private Page  Non-Indexable
4      https://example.com/blog/     200          Blog      Indexable

At first glance, you'll notice Python now shows the data as a table, not each row as a separate dictionary.

That's because pd.read_csv() read the CSV file and turned it into something called a DataFrame. And that's the most important idea in today's lesson.

What's a DataFrame?

I don't want you to memorize an academic definition. Look at the data: it's a table. It has rows. And it has columns.

Pandas represents that table inside Python as a DataFrame. So when we wrote df = pd.read_csv("crawl.csv"), we were saying:

And the name df is a common short form of DataFrame. You could call the variable crawl or seo_data or anything else. But we'll use df to start with, because you'll see it a lot in Pandas code.

Checking that df really is a DataFrame

<class 'pandas.DataFrame'>

So the data in df is a DataFrame.

The first thing to do when you open a big file

If you have a file with 100,000 rows, it doesn't make sense to write print(df) and print the whole file.

We usually just want to look at the first few rows, to make sure the data was read correctly. For that, we use head(). Let's try:

                             URL  Status         Title   Indexability
0           https://example.com/     200          Home      Indexable
1     https://example.com/about/     200      About Us      Indexable
2  https://example.com/old-page/     404      Old Page  Non-Indexable
3   https://example.com/private/     200  Private Page  Non-Indexable
4      https://example.com/blog/     200          Blog      Indexable

head() shows the first 5 rows by default (and our file has exactly 5 rows, so that's the whole file). If you want just the first two:

                          URL  Status     Title Indexability
0        https://example.com/     200      Home    Indexable
1  https://example.com/about/     200  About Us    Indexable

The number 2 says: show the first two rows. It's one of the first things you'll do almost every time you open a new data set.

Finding the column names

In the last lesson we used reader.fieldnames to see a CSV's column names. In Pandas it's simpler. We use df.columns. Let's try:

Index(['URL', 'Status', 'Title', 'Indexability'], dtype='str')

What matters to us for now is URL, Status, Title and Indexability: those are the columns in the file.

And with real SEO files, this step is very useful before you start writing any analysis, because column names differ from one export to another.

Finding the number of rows and columns

With a big file, it's natural to ask: how many rows and how many columns does it have?

We can use df.shape. Let's try:

(5, 4)

The two numbers mean 5 rows and 4 columns. The first number is the number of rows, and the second is the number of columns.

We can print them more clearly:

Rows: 5
Columns: 4

Notice we used indexes. df.shape gave back (5, 4). So df.shape[0] got the first value, and df.shape[1] got the second.

Picking one column

Now we have a whole DataFrame. But maybe I just want the URLs. We write:

0             https://example.com/
1       https://example.com/about/
2    https://example.com/old-page/
3     https://example.com/private/
4        https://example.com/blog/
Name: URL, dtype: str

Look at df["URL"]. We're saying: from the DataFrame called df, get the URL column. It's one of the most common things you'll do with Pandas.

So what are the numbers on the left?

We have 0, 1, 2, 3 and 4. They aren't part of the URL, and they aren't a column in the CSV at all.

Pandas gives each row a number that helps it identify the row. That number is called the Index. The first row is 0, then 1, and so on.

And notice the same rule we met in Python before: counting starts at 0, not 1.

We don't need to do anything with the index right now. What matters is that you know what it is, so you don't think it's part of the data.

Picking more than one column

If you want URL and Status, we write:

                             URL  Status
0           https://example.com/     200
1     https://example.com/about/     200
2  https://example.com/old-page/     404
3   https://example.com/private/     200
4      https://example.com/blog/     200

There's an important detail here. One column:

More than one column:

Notice the [[]]. Why? The inner list, ["URL", "Status"], is a list of the column names we want. And the outer brackets are what Pandas uses to pick columns from df.

You don't need to memorize that sentence; what matters is knowing the difference in how they're used.

A first real filter

This is the point where you'll see why Pandas is useful. We want the 404 pages.

The old way, we did:

In Pandas, we'll start with something simple. We write:

0    False
1    False
2     True
3    False
4    False
Name: Status, dtype: bool

The result may look odd at first. But what happened is very simple.

Pandas went to each row and asked: is the status equal to 404?

Using the result to filter

Now we can say:

We write:

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

And that's our first filter with Pandas.

The part df["Status"] == 404 is the condition. And the part df[ ... ] says:

So when we put them together, df[df["Status"] == 404] means: from df, get the rows where the status equals 404.

Why did we write 404 as a number this time?

In the last lesson we wrote row["Status"] == "404", with 404 as a string. But here we wrote df["Status"] == 404 as a number. Why?

Because when Pandas read the column, it saw that all the values were numbers, so it turned the column into a numeric type automatically.

We can check with df.dtypes:

URL               str
Status          int64
Title             str
Indexability      str
dtype: object

What matters here is Status int64: Pandas treats the status as whole numbers.

You don't need to dig into the difference between int and int64 right now. For this stage, think of int64 as a whole-number type.

Getting only the URLs of the 404 pages

The current result has every column. But if you only want the URL:

2    https://example.com/old-page/
Name: URL, dtype: str

We could do it in one step, but while you're learning, the first form is clearer. So don't rush to shorten the code just because you can. What matters now is understanding each step.

Filtering text

We want the non-indexable pages. We write:

                             URL  Status         Title   Indexability
2  https://example.com/old-page/     404      Old Page  Non-Indexable
3   https://example.com/private/     200  Private Page  Non-Indexable

Notice we put "Non-Indexable" in quotes. Why? Because it's a string. So the same comparison rules we've used since the start of the course still apply.

The difference is that Pandas applies the comparison to the whole column, instead of us writing a loop by hand.

More than one condition

We want the pages where Status = 200 and, at the same time, Indexability = Non-Indexable. Naturally, you'd think of writing and, because that's what we learned.

But with Pandas conditions we use &. Here, this symbol means: the first condition and the second condition both have to be true.

But there's a very important rule. Each condition has to be inside brackets:

And the second condition:

With & between them. So:

Let's try:

                            URL  Status         Title   Indexability
3  https://example.com/private/     200  Private Page  Non-Indexable

One page met both conditions.

Why didn't we use and?

This needs to be clear. With two ordinary variables, you can do:

True

Here each condition gives back one value. But in Pandas, df["Status"] == 200 doesn't give back one value. It gives back a result for every row.

And to combine two sets of conditions row by row, we use & instead of and. For now, remember this practical rule:

And put each condition in brackets.

Trying or in Pandas

If we want status 404 or 301, we use |. We write:

So with &, both have to be met. With |, one of them is enough.

Picking columns after filtering

Real crawl files might have 30 or 40 columns. But when you pull out the 404 pages, you may only need URL, Status and Title.

We do:

                             URL  Status     Title
2  https://example.com/old-page/     404  Old Page

We did two separate operations. First we filtered the rows. Then we picked the columns.

That's a very important point. In data analysis you have two different questions: which rows do I want? And which columns do I want? They're not the same thing.

Sorting the data

Say we have this file:

And we want to sort the pages by word count. We'll use sort_values(). Let's try:

                           URL  Status  Words
3  https://example.com/page-4/     404     50
1  https://example.com/page-2/     200    120
0  https://example.com/page-1/     200    850
2  https://example.com/page-3/     200   2200

Pandas sorted from smallest to largest. But if we want the largest first, we use ascending=False. So:

ascending means going up. When it's True, the order is smallest to largest. When we write False, we reverse it.

How many pages do I have for each status code?

You'll do this one a lot. Say we have:

I want to know how many times 200 appears, how many times 404, how many times 301. Pandas has value_counts(). Let's try:

Status
200    3
404    2
301    1
Name: count, dtype: int64

So 200 = 3 pages, 404 = 2 pages, 301 = 1 page.

So value_counts() counts how many times each value appears in the column.

Missing data

In SEO you'll run into something very important. You might have:

The last two pages have no title. When Pandas reads the file, you'll see:

                           URL  Status     Title
0         https://example.com/     200      Home
1   https://example.com/about/     200  About Us
2  https://example.com/page-1/     200       NaN
3  https://example.com/page-2/     200       NaN

Something new appeared: NaN. In the context that matters to us now, you can simply read NaN as: a missing value. The cell has no value.

How do we get the rows with an empty title?

Pandas turned the empty values into NaN. So the best way is to use isna(). Let's try it first:

0    False
1    False
2     True
3     True
Name: Title, dtype: bool

Now we use it to filter:

                           URL  Status Title
2  https://example.com/page-1/     200   NaN
3  https://example.com/page-2/     200   NaN

And that's the first time we've used Pandas to find an SEO problem straight from the data.

What if you want the pages that do have a title? There's the opposite, notna(), meaning the value isn't missing. Let's try:

                          URL  Status     Title
0        https://example.com/     200      Home
1  https://example.com/about/     200  About Us

So isna() means the value is missing, and notna() means it's there.

Combining several conditions with missing values

We want the 200 pages whose title is missing. There are two conditions: df["Status"] == 200 and df["Title"].isna(). We join them with &:

                                URL  Status Title Indexability  Words
3  https://example.com/blog/python/     200   NaN    Indexable   1500
7   https://example.com/empty-page/     200   NaN    Indexable     80

Now we're not just pulling out any empty title cell. We've set exactly which kind of page we want to review.

Working with text inside columns

In lesson 7 we used "/blog/" in url to find out whether a URL had /blog/ in it.

With Pandas we're not dealing with one URL. We have a whole column. If we want the rows whose URL contains /blog/, we use str.contains().

We have df["URL"], the URL column. Since the values in it are strings, we use .str and then .contains("/blog/"). So it looks like:

Let's try:

                                URL  Status      Title Indexability  Words
2     https://example.com/blog/seo/     200  SEO Guide    Indexable   2200
3  https://example.com/blog/python/     200        NaN    Indexable   1500

Now we're applying the same logic as the earlier lessons, but to thousands of rows at once.

Watch out for ? in str.contains()

In lesson 7 we pulled out the URLs with parameters. What if we want to do it with Pandas? The first thing that comes to mind:

If you run it, you'll get an odd-looking error: nothing to repeat at position 0

Why? Because by default, str.contains() reads the text you give it as a regular expression, not plain text. And in regex, ? has a special meaning.

We won't get into regex now. All you need to know is: if you want to search for the text exactly as it is, add regex=False:

                                    URL  Status
1      https://example.com/shop/?page=2     200
2  https://example.com/shop/?sort=price     200

Same goes if you're searching for a mark like ., + or (. And watch out: . in particular won't throw an error. It'll give you a wrong result without you noticing. So if you don't mean regex, use regex=False and save yourself the trouble.

Title length

One of the first things checked in any audit: is the title too long?

In plain Python we got the length of text with len(). In Pandas we have the same idea for a whole column: str.len().

We have this file:

Let's try:

                                    URL  Title Length
0                  https://example.com/             4
1  https://example.com/blog/python-seo/            69
2            https://example.com/about/             8
3         https://example.com/services/            12

Look at this line:

Here we made a new column called Title Length that wasn't in the file at all. We're telling Pandas:

That's an idea you'll use a lot: work something out from the existing columns, and put it in a new column you can filter on.

Now let's pull out the titles longer than 60 characters:

                                    URL  Title Length
1  https://example.com/blog/python-seo/            69

But keep in mind: 60 characters isn't a law. It's a threshold you chose. Google truncates titles in the results by their width, not by an exact character count, and a long title isn't always a problem. The program here gives you pages to review, not errors that must be fixed.

Duplicate titles

Another very well-known problem: more than one page with the same title.

We have this file:

Pandas has duplicated(). Let's try:

0    False
1     True
2    False
3     True
4    False
Name: Title, dtype: bool

Notice something important. The first page, /shoes/, came out False, even though its title is duplicated.

Why? Because duplicated() treats the first appearance of a value as the original, and every later one as the duplicate.

But in SEO we want to see every page involved in the duplication, so we can compare them. For that, we use keep=False, meaning: don't treat any of them as the original; mark them all.

Without running it, predict the output:

Let's see the rows themselves:

                                     URL  Status             Title
0             https://example.com/shoes/     200  Buy Shoes Online
1   https://example.com/shoes/?color=red     200  Buy Shoes Online
3  https://example.com/shoes/?color=blue     200  Buy Shoes Online

And notice that these three pages are the same page with different parameters. That can be fine if they have the right canonical. So once again: the program finds, and you decide.

And if the rows themselves are duplicated?

Sometimes you collect URLs from several exports, so the same URL repeats as a whole row. That's when we use drop_duplicates():

Before: 5
After: 3

subset="URL" means: treat the row as a duplicate if its URL repeats. And notice we wrote df = before it, because drop_duplicates() doesn't change df itself; it gives back a new copy without the duplicates.

It's the same idea as the set we learned in lesson 2, but on a whole table.

Saving the result in a CSV

Say we pulled out the 404 pages and want to save them. We'll use to_csv(). We write:

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

That makes the file. But if you open it, you'll find Pandas also saved the index. We don't need it in the report. So we use index=False:

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

Reading an Excel file

So far we've used pd.read_csv(). But in the last lesson we said Excel is different from CSV.

The good news is that Pandas can read Excel too. If you have crawl.xlsx, you can use:

But reading .xlsx files usually needs an extra library called openpyxl. If you don't have it, install it from the Terminal:

Then df = pd.read_excel("crawl.xlsx") reads the Excel data and turns it into the same thing we've worked with all lesson: a DataFrame.

That's a very important point. Whether the data comes from CSV or Excel, once Pandas reads it, we work with it as a DataFrame.

Saving the result as Excel

Just as we have to_csv(), we have to_excel(). For example:

And if openpyxl is installed, an Excel file is created.

Let's do a complete SEO analysis

We'll use a slightly bigger crawl.csv, containing:

We'll read the file, and we want the 404 pages, the redirects, the 200 pages that aren't indexable, the 200 pages with no title, and the 200 pages with fewer than 100 words.

Notice the condition df["Words"] < 100. There's nothing new about it in Python terms. We learned < before. The only new thing is that Pandas applies the comparison to every row in the column.

First Rows:
                                   URL  Status      Title   Indexability  Words
0                 https://example.com/     200       Home      Indexable    850
1           https://example.com/about/     200   About Us      Indexable    600
2        https://example.com/blog/seo/     200  SEO Guide      Indexable   2200
3     https://example.com/blog/python/     200        NaN      Indexable   1500
4  https://example.com/product/laptop/     200     Laptop  Non-Indexable    300

Columns:
Index(['URL', 'Status', 'Title', 'Indexability', 'Words'], dtype='str')

File Shape:
(8, 5)

Status Codes:
Status
200    6
404    1
301    1
Name: count, dtype: int64

404 Pages:
                             URL  Status
5  https://example.com/old-page/     404

Redirects:
                             URL  Status
6  https://example.com/redirect/     301

200 Non-Indexable Pages:
                                   URL   Indexability
4  https://example.com/product/laptop/  Non-Indexable

Missing Titles:
                                URL Title
3  https://example.com/blog/python/   NaN
7   https://example.com/empty-page/   NaN

Low Word Pages:
                               URL  Words
7  https://example.com/empty-page/     80

And in the same folder, we can save each group in its own file:

So one program reads one crawl and produces several reports from it. That's a big part of the value we'll build on in this stage's final project.

A real Screaming Frog file doesn't look like ours

So far, every file we've worked on had simple columns: URL, Status and Title.

But when you do a real export from Screaming Frog, the columns look like this:

Let's see the columns:

Index(['Address', 'Status Code', 'Title 1', 'Indexability', 'Word Count'], dtype='str')

If you write the code we learned as it is:

you'll get KeyError: 'Status', because the column is called Status Code, not Status.

You have two options.

Option one: use the names as they are

Column names can have spaces in them, as long as you write them correctly between the brackets:

1    https://example.com/old-page/
Name: Address, dtype: str

Option two: rename the columns

If you want the code you've written all lesson to work as it is, rename the columns to the names you're used to, with rename():

Index(['URL', 'Status', 'Title', 'Indexability', 'Words'], dtype='str')

Inside rename() we sent a dictionary. The key is the old name, and the value is the new name. Exactly as we learned in lesson 2.

And watch the df = at the start. Like drop_duplicates(), rename() doesn't change df itself; it gives back a new copy. If you forget df =, the names stay as they were.

And no two tools name their columns the same way. Ahrefs is different from Semrush, which is different from Search Console. That's why the first thing to do with any new file is print(df.columns), and then decide.

Combining data from two files: merge()

In lesson 8 we said one of the most important things we'd do with Pandas is merging a crawl with Search Console data. Let's think about why that matters.

The crawl file tells you whether a page works and whether it's indexable. But it knows nothing about traffic. The Search Console file tells you how many clicks each page got, but it doesn't know the page's status.

When we put them side by side, we can ask questions we couldn't ask of either file on its own.

Here's the crawl file:

And the Search Console file:

First, let's look at the Search Console columns:

Index(['Top pages', 'Clicks', 'Impressions', 'CTR', 'Position'], dtype='str')

The URL column here is called Top pages, and in the crawl it's called URL. To join the two files, the shared column has to have the same name. So we use rename(), which we just learned.

Then we use merge():

                                        URL  Status  Clicks
0                      https://example.com/     200  1200.0
1             https://example.com/blog/seo/     200   340.0
2          https://example.com/blog/python/     200     NaN
3   https://example.com/services/seo-audit/     200    90.0
4  https://example.com/services/consulting/     200    25.0
5        https://example.com/blog/old-post/     404     4.0

Let's understand this line:

And notice two things.

First: the /blog/python/ page has NaN clicks. It's a page in the crawl, but it has no row at all in Search Console.

Second: the numbers became 1200.0 instead of 1200. That's because the column now has a NaN in it, and Pandas turns it into a numeric type that allows missing values. We won't stop on that now.

Now the questions we couldn't ask before

200 pages with no data at all in Search Console:

2    https://example.com/blog/python/
Name: URL, dtype: str

A page that works, with no visibility at all. Why? Maybe it's new. Maybe it isn't actually indexed. Maybe no internal links reach it. It's a page worth reviewing.

And 404 pages that still get clicks:

                                  URL  Clicks
5  https://example.com/blog/old-post/     4.0

This is one of the most important findings in any audit: a broken page that Google is still sending people to. It's usually the first page you'd think of redirecting. But as we said: the program shows you the page, and the decision is yours.

How many clicks does each section of the site get? groupby()

In lesson 7 we found the section a page belongs to with split("/"). In Pandas we can do it on the whole column at once:

['', 'blog', 'blog', 'services', 'services', 'blog']

Exactly as in lesson 7: str.split("/") split each URL into a list, and str[3] took item 3 from each list. And tolist() turned the column into an ordinary list so we can see it easily.

The home page has no section, so it came out as empty text. We'll call it home to make it clear, using replace(), which we learned in lesson 7:

Now we want to know how many clicks each section gets. That's where we use groupby():

Section
blog         344.0
home        1200.0
services     115.0
Name: Clicks, dtype: float64

Read this line like this:

In seconds, you know the blog gets 344 clicks and services get 115. And if the site has 50,000 pages, the same line works the same way.

And notice that 4 of those 344 clicks come from a 404 page. So even grouped numbers need you to look at what's inside them before you base a decision on them.

Now you try:

What did Pandas save us?

Let's compare a small part. The old way:

And with Pandas:

But the most important point isn't that the code is shorter. The real value is that we now work with the data as a table.

So on the same data we can filter, sort, count, pick columns, find missing values and export, without rebuilding each operation with a new loop.

And that's the real reason Pandas matters in SEO.

Don't use Pandas and try to forget Python

There's a mistake that happens a lot while learning. Someone starts Pandas and says:

That's wrong. What we learned before Pandas is what lets you understand what you're doing in the first place.

When you see df["Status"] == 404, you understand the comparison.

When you see (df["Status"] == 200) & (df["Words"] < 100), you understand there are two conditions.

When you see ["URL", "Status", "Title"], you know it's a list.

And when we write functions later to organize the analysis, you already understand functions.

Pandas isn't a replacement for Python. It's a tool we use on top of the foundation we've built.

What you need to understand from this lesson

When you read a CSV, df = pd.read_csv("crawl.csv"), the result is a DataFrame.

You can see the first rows with df.head(), find the column names with df.columns, and the number of rows and columns with df.shape.

You pick one column with df["URL"], and more than one with df[["URL", "Status"]].

You filter with df[df["Status"] == 404]. And with two conditions, df[(df["Status"] == 200) & (df["Indexability"] == "Non-Indexable")].

You find missing values with df["Title"].isna(), count values with df["Status"].value_counts(), and sort with df.sort_values("Words").

You save the result with df.to_csv("result.csv", index=False). You can also read Excel with pd.read_excel("file.xlsx") and save Excel with df.to_excel("result.xlsx", index=False).

You get text length with df["Title"].str.len(), duplicates with df["Title"].duplicated(keep=False), and rename columns with df.rename(columns={...}).

And you merge two files with pd.merge(crawl, gsc, on="URL", how="left"), and group by a value with df.groupby("Section")["Clicks"].sum().

You don't need to memorize this list. What matters is understanding why we used each one.

The practical test

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

First, read the file with Pandas, pd.read_csv(). Then print the first 5 rows. Then print the column names. Then print df.shape and work out for yourself how many rows and how many columns there are.

Then make a DataFrame called broken_pages with only the 404 pages.

And make redirects with the 301 pages.

And make non_indexable_200 with the pages that meet both conditions: Status = 200 and Indexability = Non-Indexable.

And make missing_titles with the 200 pages whose title is missing.

And make low_word_pages with the 200 pages with fewer than 100 words.

Then print the number of pages for each status with value_counts(). The result should be 200 seven times, 301 once, and 404 once.

Next, pull out only the blog pages with str.contains(). You should get https://example.com/blog/seo/ and https://example.com/blog/python/.

Then sort the 200 pages by word count, from fewest to most.

Finally, save broken_pages in 404-pages.csv, missing_titles in missing-titles.csv, and low_word_pages in low-word-pages.csv, without letting Pandas save the index in the files. So you need index=False.

If you managed this test yourself, you've reached a very important point in the first stage. You can now take a real SEO file, read it, understand its columns, filter it, combine conditions, find missing values, sort the data, count values, and produce new reports.

And that's the foundation for the next lessons, where we won't learn a new Python command just for the sake of it. We'll start bringing everything we've learned together, all the way to a full SEO Audit Analyzer project.