Learning to code, whether with Python, JavaScript, or another programming language, has a whole host of benefits, including the ability to work with larger datasets and automate repetitive tasks.
But despite the benefits, many SEO professionals are yet to make the transition – and I completely understand why! It isn’t an essential skill for SEO, and we’re all busy people.
If you’re pressed for time, and you already know how to accomplish a task within Excel or Google Sheets, then changing tack can feel like reinventing the wheel.
When I first started coding, I initially only used Python for tasks that I couldn’t accomplish in Excel – and it’s taken several years to get to the point where it’s my defacto choice for data processing.
Looking back, I’m incredibly glad that I persisted, but at times it was a frustrating experience, with many an hour spent scanning threads on Stack Overflow.
This post is designed to spare other SEO pros the same fate.
Within it, we’ll cover the Python equivalents of the most commonly used Excel formulas and features for SEO data analysis – all of which are available within a Google Colab notebook linked in the summary.
Specifically, you’ll learn the equivalents of:
- LEN.
- Drop Duplicates.
- Text to Columns.
- SEARCH/FIND.
- CONCATENATE.
- Find and Replace.
- LEFT/MID/RIGHT.
- IF.
- IFS.
- VLOOKUP.
- COUNTIF/SUMIF/AVERAGEIF.
- Pivot Tables.
Amazingly, to accomplish all of this, we’ll primarily be using a singular library – Pandas – with a little help in places from its big brother, NumPy.
Prerequisites
For the sake of brevity, there are a few things we won’t be covering today, including:
- Installing Python.
- Basic Pandas, like importing CSVs, filtering, and previewing dataframes.
If you’re unsure about any of this, then Hamlet’s guide on Python data analysis for SEO is the perfect primer.
Now, without further ado, let’s jump in.
LEN
LEN provides a count of the number of characters within a string of text.
For SEO specifically, a common use case is to measure the length of title tags or meta descriptions to determine whether they’ll be truncated in search results.
Within Excel, if we wanted to count the second cell of column A, we’d enter:
=LEN(A2)
- Screenshot from Microsoft Excel, November 2022
Python isn’t too dissimilar, as we can rely on the inbuilt len function, which can be combined with Pandas’ loc[] to access a specific row of data within a column:
len(df['Title'].loc[0])
In this example, we’re getting the length of the first row in the “Title” column of our dataframe.
- Screenshot of VS Code, November, 2022
Finding the length of a cell isn’t that useful for SEO, though. Normally, we’d want to apply a function to an entire column!
In Excel, this would be achieved by selecting the formula cell on the bottom right-hand corner and either dragging it down or double-clicking.
When working with a Pandas dataframe, we can …….