Pandas DataFrame Exercise 2: Search Console Query Analytics

Analyze organic website performance data in Pandas. In this exercise, ingest a 50-row Google Search Console query export (kw1_short.xlsx) containing search queries and visitor click counts. Perform substring filtering with str.contains(), compute aggregated click volumes, and isolate queries by character length extremes.

Dataset Overview & Excel Download 🔝

The practice workbook kw1_short.xlsx contains 50 rows of real-world Google search telemetry with two primary columns: Query (the search query string typed by users) and Clicks (the total organic visitors delivered to the website). Download the sample file below:

Download kw1_short.xlsx (49 Records) ↓

Search Query Practice Problems (Q1 to Q6) 🔝

1. Read the Excel workbook into a Pandas DataFrame and inspect initial records.
2. Filter and list all queries containing the keyword 'HTML' (case-insensitive).
3. Calculate the total number of clicks generated by queries containing 'HTML'.
4. Identify the single query that generated the maximum clicks among 'HTML' queries.
5. Find the shortest search query containing the word 'HTML'.
6. Find the longest search query containing the word 'HTML'.

Step-by-Step Solutions & Verified Output 🔝

import pandas as pd

# 1. Read Excel workbook
df = pd.read_excel("download/kw1_short.xlsx")
print("Total Records:", len(df))
assert len(df) == 49

# 2. Filter queries containing 'HTML' (case-insensitive)
html_df = df[df["Query"].str.contains("HTML", case=False, na=False)].copy()
print(f"
Queries containing 'HTML': {len(html_df)}")
print(html_df.head())
assert len(html_df) == 15

# 3. Total clicks from HTML queries
total_html_clicks = html_df["Clicks"].sum()
print(f"
Total Clicks for 'HTML' queries: {total_html_clicks}")
assert total_html_clicks == 3528

# 4. Maximum click generating query
max_clicks = html_df["Clicks"].max()
top_query_row = html_df[html_df["Clicks"] == max_clicks]
print(f"
Top Click Query ({max_clicks} clicks):
", top_query_row)
assert max_clicks == 406
assert top_query_row.iloc[0]["Query"] == "marquee speed html"

Query String Length Analysis (Shortest & Longest) 🔝

In search marketing, comparing short-tail versus long-tail queries helps prioritize optimization efforts. Compute the character length of each query using str.len():

# 5 & 6. Query character length extremes
html_df["LENGTH"] = html_df["Query"].str.len()

# Shortest query
min_len = html_df["LENGTH"].min()
shortest_queries = html_df[html_df["LENGTH"] == min_len]
print(f"Shortest Query ({min_len} chars):
", shortest_queries[["Query", "Clicks", "LENGTH"]])

# Longest query
max_len = html_df["LENGTH"].max()
longest_queries = html_df[html_df["LENGTH"] == max_len]
print(f"
Longest Query ({max_len} chars):
", longest_queries[["Query", "Clicks", "LENGTH"]])

assert min_len == 13
assert "html calendar" in shortest_queries["Query"].values

Exercise Series Navigation 🔝

• Exercise 1: Basic student dataset queries, marks, and unique values.
• Exercise 1-1: Data binning with cut(), groupby distributions, and charts.
• Exercise 2 (Current Page): Search Console query analytics, string length, and click analysis.
• Exercise 3 Series: Time-series maintenance logs and datetime queries.

Practice in Google Colab & GitHub 🔝

Open in Google Colab View on GitHub
Run all search console query filters, click calculations, and character length analyses interactively in Google Colab.

More Details & Related Tutorials 🔝

• str.contains() — Substring searching and regular expressions.
• str.len() — Calculating text length across Series.
• sort_values() — Ordering rows by clicks or text length.
• max() and min() — Numeric boundary calculations.
• SEO Reports & Keyword Frequency — Advanced multi-word analytics and sitemap generation.
• Data Analysis Hub — Exploratory data analysis workflows.

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: