Pandas Datetime Exercise: Backlink Crawl Date Analysis

In search engine optimization (SEO) and web analytics, tracking external backlink discovery dates is crucial for understanding link velocity. In this exercise, analyze an actual 10,707-row Google Search Console crawl export (links_external.xlsx) by parsing crawl dates, filtering by crawl year, aggregating monthly link discovery, and searching specific referring domains with str.contains().

Dataset Background & Download 🔝

The sample dataset links_external.xlsx contains 10,707 crawled external backlinks with two primary columns: Linking page (the referring URL) and Last crawled (the date Googlebot indexed the link). Download the sample file below:

Download links_external.xlsx (10,707 rows) ↓

SEO Time-Series Query Questions 🔝

1. How many rows and columns are there in the sample DataFrame?
2. What are the exact column names and data types?
3. What is the current calendar year, and how many links were crawled in this year?
4. How many backlinks were crawled in the preceding year?
5. Group and display total crawled links broken down by year.
6. Group and display total crawled links broken down by month.
7. Create a cross-tabulated breakdown of link counts by Year and Month combined.
8. How many referring links originate from domain example2.com?

Step-by-Step Solutions & Code Blocks 🔝

import pandas as pd

# 1. Load Excel file
df_links = pd.read_excel("download/links_external.xlsx")
print("Shape:", df_links.shape)
print("Columns:", df_links.columns.tolist())

assert df_links.shape[0] == 10707
assert df_links.shape[1] == 2

# 2. Ensure Last crawled is parsed as datetime
df_links["Last crawled"] = pd.to_datetime(df_links["Last crawled"])

# 3. Annual distribution
yearly = df_links.groupby(df_links["Last crawled"].dt.year)["Linking page"].count()
print("\nLinks Crawled by Year:\n", yearly)

# 4. Monthly distribution
monthly = df_links.groupby(df_links["Last crawled"].dt.month)["Linking page"].count()
print("\nLinks Crawled by Month (All Years):\n", monthly)

assert yearly.sum() == 10707

Domain Filtering with str.contains() 🔝

To inspect backlinks originating from a specific high-authority referring domain, use the string accessor str.contains():

# Filter URLs containing example2.com
so_links = df_links[df_links["Linking page"].str.contains("stackoverflow", case=False, na=False)]
print(f"Total example2.com Backlinks: {len(domain_links)}")

# Annual breakdown of example2.com links
print(so_links.groupby(so_links["Last crawled"].dt.year)["Linking page"].count())

Datetime Exercise Series Navigation 🔝

• Exercise 3-1: Time-series maintenance logs, date queries, and intervals.
• Exercise 3-2: Datetime basics, birthday arithmetic, and format conversions.
• Exercise 3-3 (Current Page): Webmaster external backlink crawl date analysis.
• Exercise 3-4: Student accommodation check-in durations and rent calculation.

Practice in Google Colab 🔝

Open in Google Colab View on GitHub
Run all backlink crawl date filtering, annual groupings, and domain pattern searches interactively in Google Colab.

Further Reading & Related Tutorials 🔝

• Pandas Date and Time Hub — Master pillar for all Pandas datetime operations.
• read_excel() — Ingesting multi-thousand row Excel workbooks.
• str.contains() — Finding substrings and regex patterns in text columns.
• groupby() — Temporal and categorical aggregations.
• Pandas Section Home — Master index of all Pandas tutorials, pillars, and guides.

Reference: Pandas Time Series documentation.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: