My aim in this article is to show how we can quickly do the work we do in Excel using Python's Excel Pandas library with the most basic and simple codes . I will even try to quickly analyze and gain insight into the things we can't think of, or the things that would take a long time if they did, with a few lines of code.
Don't be afraid, coding and learning languages is a whole homeowner database different world, a whole different endeavor, but knowing enough code to do your daily routine quickly, (I will try to give most of them here) and even getting enough to do your job will save you a lot of time. If you like it a lot, you can continue with modeling and up to Machine Learning and Deep Learning of course If you want to see what you can do in SEO when you learn Python at an advanced level, Hamlet Batista's writings or JR Oakes' writings will broaden your horizons a lot.
Let's get started now: There is a lot of information on the internet about how to download and use Python on your computer. Let's get straight to the code.
Loading Libraries
In this article, we will only use Pandas for dataframes and Mathplotlib for graphs , but there are many libraries in Python. I am adding other libraries here as examples, and if you want to progress in data analysis, these libraries will be very useful.
Here we read our scan file with pd.read_xlsx and saved the information of this file in df1 . We can also use the pd.read_csv extension for the 'csv' file. We also display our first five rows with the head() command. The reason we use skiprows=1 is that the column names are in the second row of the Screaming Frog output, not the first row. Let's say you want to see your pages that give a 404 response code and save them to a different file :
df404 = df1[df1[ 'Status Code' ] == 404 ]
df404
In Python, we use the sign to show equality , and the != sign to show not equal . Now let's save it in a separate file:
We transferred the pages that gave a 404 response code to a file called export404.csv . Therefore, with the codes you will write on different lines , you can keep duplicate pages, redirect pages, pages without title tags and many other crawl outputs in different places and by running only these codes in the next crawl analysis, you can have your crawl outputs ready in seconds before manual review. For example, let's detect pages without title tags:
Here, the isnull() command detects NaN, or non-existent values, in the Title 1 column. .loc is required to detect and print those rows. If you write ' Meta Description 1 ' instead of Title 1, this time it will show you the pages that do not have a meta description tag. Again, as we showed in the 404 example above, you can continue your analysis by saving this data to different files. For example, you want to know the density of NaN rows that do not have information. Imagine doing this by filtering them one by one in Excel. Instead, you can get an idea about your data with a single line of code in Python:
Combining Crawl Analysis with Google Analytics Data
Now that we have completed our basic crawl analysis, we can do a little more in-depth analysis by matching this data with Analytics data . First, get the Landing Page report for the last month or the last 3 months depending on the data you want to examine . We will combine the two files based on URLs. But first, let's read our file as we did with the crawl file.
