Lesson 21 · Real-World Data Analytics
Scrape a Paginated API in Python: Social Media Posts to Pandas | Data Analytics #21
Collect and structure social media text data in Python. We serve real social posts through a local paginated API, then fetch every page with requests…
- CourseReal-World Data Analytics
- Lesson21 of 100
- Video18 min
- FormatJupyter notebook · 31 code cells
- Data1 dataset
What you'll learn
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
- social_posts_raw.csv preview87.9 KB
📓 Full notebook
Download .ipynbData Analytics 100, Video 21: Scraping and Structuring Real Social Discussion Data#
- Video twenty-one of the hundred-video real-world data analytics series, and the start of the Marketing and Social Media domain.
- Real genuine social posts, served through a local API and scraped with the exact same mechanics a live platform API would require.
- Let's get into it.
This lesson is for learners who can load and summarise a CSV with pandas and now want to collect data from a web API themselves. It opens the Marketing and Social Media part of the series with a practical question: how do you pull every post from a paginated API, check you got all of it, and turn the raw text into features you can analyse?
The data is a set of real social posts with three columns: id, label (1 means the post was flagged by moderators, 0 means it was not) and tweet, the post text with usernames masked as @user. The posts are loaded from social_posts_raw.csv and served through a small local API that behaves like a real platform API, with pages, JSON responses, a robots.txt file and 404 errors.
In this lesson you will:
- build and start a tiny paginated JSON API with Python's
http.server - scrape every page with
requestsand awhileloop, then verify the result - extract hashtags, mentions, length and word counts as structured columns
Prerequisites: you should be comfortable with pandas basics (read_csv, describe, value_counts) and Python functions. Earlier lessons in the series cover these.
Part 1: Real Posts, A Genuine API Constraint#
Real platform APIs never hand you everything in one go, so it pays to practise against something that behaves the same way. This step imports pandas, json, threading and the BaseHTTPRequestHandler and ThreadingHTTPServer classes from Python's built-in http.server module, which is all you need to run a small web server inside the notebook. It then reads social_posts_raw.csv into posts_df. There is no output yet: the posts are simply sitting in memory, ready to be served page by page.
import pandas as pd
import json
import threading
from http.server import BaseHTTPRequestHandler, ThreadingHTTPServer
posts_df = pd.read_csv('social_posts_raw.csv')
Part 2: Real Dataset Preview#
Before serving data through an API, look at what you are serving. The code asks for posts_df.shape and then posts_df.head(3), but Jupyter only displays the last expression, so the output is just the first three rows. You can see the three columns: id, label and tweet. Notice that usernames are already masked as @user, and the text has quirks such as bihday instead of birthday. That messiness is normal for real social data, so do not expect clean English.
posts_df.shape
posts_df.head(3)
Part 3: Real Server-Side Data Structuring#
An API that serves pages needs to know how many pages exist. The code sets POSTS_PER_PAGE = 100, turns the DataFrame into a list of dictionaries with to_dict('records') (one dictionary per post, which is exactly the shape JSON wants), and works out the page count with the ceiling-division trick (n + 100 - 1) // 100. The output is 10 pages. Plain n // 100 would round down and silently drop the last partial page, a common off-by-one mistake.
POSTS_PER_PAGE = 100
posts_records = posts_df.to_dict('records')
total_pages = (len(posts_records) + POSTS_PER_PAGE - 1) // POSTS_PER_PAGE
total_pages
Part 4: Real Local API Handler#
A web server needs a handler: a class that decides what to send back for each request. This step defines PostsAPIHandler, a subclass of BaseHTTPRequestHandler, with a first version of do_GET, the method called for every GET request. For now it only answers /robots.txt with a status 200, a plain-text header and a short body allowing access to /api/posts. There is no output. The chr(10) calls are simply newline characters. This method is replaced with a fuller version shortly.
class PostsAPIHandler(BaseHTTPRequestHandler):
def do_GET(self):
if self.path == '/robots.txt':
self.send_response(200)
self.send_header('Content-Type', 'text/plain')
self.end_headers()
self.wfile.write(('User-agent: *' + chr(10) + 'Allow: /api/posts' + chr(10)).encode())
return
Part 5: Real Pagination Logic in the Handler#
Pagination is the heart of most real APIs, so it helps to see it from the server's side. The function handle_posts_request reads the page number from the URL by splitting on ?page=, defaulting to page 1. It then slices posts_records[start:end], where start = (page - 1) * 100, and sets has_next to True while there are more posts after this slice. The returned dictionary carries the page number, total pages, the has_next flag and the posts. The next line attaches this function to the class as a method, so every handler can call it.
def handle_posts_request(self):
query = self.path.split('?page=')
page = int(query[1]) if len(query) > 1 else 1
start = (page - 1) * POSTS_PER_PAGE
end = start + POSTS_PER_PAGE
page_data = posts_records[start:end]
has_next = end < len(posts_records)
return {'page': page, 'total_pages': total_pages, 'has_next': has_next, 'posts': page_data}
PostsAPIHandler.handle_posts_request = handle_posts_request
Part 6: Real do_GET Routing#
Now the server needs routing: sending each URL to the right response. The new do_GET answers /robots.txt with a short text body, sends anything starting with /api/posts to handle_posts_request and returns the result as JSON with json.dumps(...).encode(), and returns a 404 status for every other path. Assigning PostsAPIHandler.do_GET = do_GET replaces the earlier version. The last line overrides log_message with a function that does nothing, which stops the server printing a log line for every request into your notebook.
def do_GET(self):
if self.path == '/robots.txt':
self.send_response(200)
self.end_headers()
self.wfile.write(('User-agent: *' + chr(10)).encode())
elif self.path.startswith('/api/posts'):
response_data = self.handle_posts_request()
self.send_response(200)
self.send_header('Content-Type', 'application/json')
self.end_headers()
self.wfile.write(json.dumps(response_data).encode())
else:
self.send_response(404)
self.end_headers()
PostsAPIHandler.do_GET = do_GET
PostsAPIHandler.log_message = lambda self, format, *args: None
Part 7: Starting the Real Local Server#
With the handler ready, the server has to run in the background so the notebook can still send it requests. ThreadingHTTPServer(('localhost', 0), ...) asks the operating system for any free port (that is what port 0 means), and server.server_address[1] reads back which one it got. serve_forever runs in a separate thread with daemon=True, so it will not block the notebook or keep Python alive on exit. The output shows the base URL, here http://localhost:49679. Your port number will almost certainly differ, which is expected.
server = ThreadingHTTPServer(('localhost', 0), PostsAPIHandler)
server_port = server.server_address[1]
server_thread = threading.Thread(target=server.serve_forever, daemon=True)
server_thread.start()
BASE_URL = f'http://localhost:{server_port}'
BASE_URL
Part 8: Real robots.txt Check#
Checking robots.txt first is good scraping etiquette: it is the file where a site states which paths automated clients may visit. The code uses requests.get to fetch it and shows the status code and text together. The output is (200, 'User-agent: *\n'), meaning the request succeeded and the rules apply to all crawlers. On a real site, read this file carefully and also check the terms of service; robots.txt is guidance, not a legal permission slip.
import requests
robots_response = requests.get(f'{BASE_URL}/robots.txt')
robots_response.status_code, robots_response.text
Part 9: Real First Page Fetch#
Fetching one page before writing a loop lets you confirm the response shape without risk. The code requests /api/posts?page=1, parses the body into a Python dictionary with .json(), and reports the total page count and how many posts came back. The output (10, 100) confirms the API reports 10 pages and that page 1 is full with 100 posts. If a single page looks wrong, fix that first: a loop will only repeat the mistake ten times.
first_page_response = requests.get(f'{BASE_URL}/api/posts?page=1')
first_page_data = first_page_response.json()
first_page_data['total_pages'], len(first_page_data['posts'])
Part 10: Real Full Pagination Loop#
This is the actual scrape. The loop starts at page 1, requests each page, adds its posts to all_scraped_posts with .extend() (which adds each item rather than nesting a list inside a list), and stops as soon as the server reports has_next as False. Relying on the server's flag is safer than hard-coding 10 pages, because page counts change. The output shows 944 posts collected, so the tenth page held the final 44. On a live API, add a short pause between requests to avoid hammering the server.
all_scraped_posts = []
current_page = 1
while True:
page_response = requests.get(f'{BASE_URL}/api/posts?page={current_page}')
page_json = page_response.json()
all_scraped_posts.extend(page_json['posts'])
if not page_json['has_next']:
break
current_page += 1
len(all_scraped_posts)
Part 11: Real Structuring into a DataFrame#
Raw JSON is awkward to analyse, so the scraped list of dictionaries goes straight into a DataFrame with pd.DataFrame(all_scraped_posts). Each dictionary becomes a row and each key becomes a column. As before, only the last expression is displayed, so you see head(3) rather than the shape. The first three rows match the original CSV exactly, with the same id, label and tweet values, which is a good first sign the scrape worked.
scraped_df = pd.DataFrame(all_scraped_posts)
scraped_df.shape
scraped_df.head(3)
Part 12: Real Round-Trip Verification#
Never assume a scrape is complete: prove it. The code compares row counts and then checks that the set of id values in the scraped data equals the set in the source file. Only the second comparison is displayed, and it prints True, so every original post id was captured with nothing extra. Comparing sets of ids is stronger than comparing row counts, because a scrape could fetch one page twice and miss another while still ending with the same number of rows.
scraped_df.shape[0] == posts_df.shape[0]
set(scraped_df['id']) == set(posts_df['id'])
Part 13: Real 404 Handling Check#
A good scraper must cope with errors as well as successes. This step requests a path the server does not know, /api/does-not-exist, and reads the status code. The output is 404, the standard HTTP code for not found, which confirms the routing falls through to the error branch as designed. In real scraping code, check status_code (or call raise_for_status()) before calling .json(), otherwise an error page can crash your loop or quietly corrupt your data.
bad_response = requests.get(f'{BASE_URL}/api/does-not-exist')
bad_response.status_code
Part 14: Real Hashtag Extraction#
Hashtags show what a post is about, so they are a natural first feature to pull from raw text. The code uses .str.findall(r'#\w+'), a regular expression that matches a # followed by letters, digits or underscores, to store a list of hashtags per post. .str.len() on that list column counts them. The describe() output shows an average of 2.35 hashtags per post, a median of 2, and a maximum of 20 in a single post. At least a quarter of posts have none, since the 25th percentile is 0.
import re
scraped_df['hashtags'] = scraped_df['tweet'].str.findall(r'#\w+')
scraped_df['hashtag_count'] = scraped_df['hashtags'].str.len()
scraped_df['hashtag_count'].describe().round(2)
Part 15: Real Top Hashtags#
Counting which hashtags appear most often tells you the dominant topics. Because each row holds a list, the code first uses .explode() to give every hashtag its own row, .dropna() to remove the empty entries from posts with no hashtags, and then .value_counts().head(10). The output puts #love well ahead with 46 uses, then #positive with 28 and #thankful with 19. Note that hashtags are case-sensitive here, so #Love and #love would be counted separately.
all_hashtags = scraped_df['hashtags'].explode().dropna()
top_hashtags = all_hashtags.value_counts().head(10)
top_hashtags
Part 16: Real Posts Without Any Hashtags#
Knowing how many posts have no hashtags at all tells you how far hashtag-based analysis can reach. The comparison hashtag_count == 0 gives True or False per post, and .mean() on those booleans gives the share of Trues, because True counts as 1 and False as 0. Multiplying by 100 and rounding gives a percentage. The output shows 26.5% of posts have no hashtags, so any topic analysis based only on hashtags would miss roughly a quarter of the conversation.
no_hashtag_pct = round((scraped_df['hashtag_count'] == 0).mean() * 100, 1)
no_hashtag_pct
Part 17: Real Post Length Analysis#
Post length is a simple feature that often relates to engagement and tone. The code stores the number of characters in each post with .str.len() and summarises it with describe(). Posts average 84.6 characters with a median of 86, ranging from 18 up to 151 characters. Half of all posts fall between 63 and 107 characters. Remember this counts every character, including spaces, @user tags and hashtags, so it measures raw length rather than meaningful content.
scraped_df['post_length'] = scraped_df['tweet'].str.len()
scraped_df['post_length'].describe().round(1)
Part 18: Real Mentions Extraction#
Mentions show how conversational a dataset is: posts that tag others are replies or shout-outs rather than standalone statements. Since usernames are masked, the code can simply count the literal text @user in each post with .str.count('@user'), then add them all up with .sum(). The output shows 515 mentions across the 944 posts. Be aware that .str.count treats its argument as a regular expression, which is fine here but matters if you search for characters such as . or +.
scraped_df['mention_count'] = scraped_df['tweet'].str.count('@user')
scraped_df['mention_count'].sum()
Part 19: Real Label Distribution#
The label is what a moderation model would try to predict, so you need to know how balanced it is. The code calls value_counts() and then takes the mean of label, but only the last result is displayed. Because the label is 0 or 1, its mean is the share of flagged posts, and the output shows 7.5%. That is a heavily imbalanced dataset: a model that never flags anything would already be about 92.5% accurate, which is why accuracy alone is a poor measure for this kind of problem.
scraped_df['label'].value_counts()
round(scraped_df['label'].mean() * 100, 1)
Part 20: Real Flagged Posts and Real Hashtag Usage#
A natural question is whether flagged posts look different from normal ones on the features you have just built. The code uses .loc with a boolean condition to select flagged rows (label == 1) and normal rows (label == 0), then averages hashtag_count for each group. The output shows 2.15 hashtags per flagged post against 2.37 for normal posts. The gap is small, so hashtag count alone is unlikely to separate flagged content, especially with so few flagged posts.
flagged_hashtag_avg = round(scraped_df.loc[scraped_df['label'] == 1, 'hashtag_count'].mean(), 2)
normal_hashtag_avg = round(scraped_df.loc[scraped_df['label'] == 0, 'hashtag_count'].mean(), 2)
flagged_hashtag_avg, normal_hashtag_avg
Part 21: Real Word Count per Post#
Word count is often more meaningful than character count, because it ignores the length of individual words. The code splits each post on whitespace with .str.split(), which returns a list of words, then counts the list with .str.len(). The summary shows posts average 13.1 words with a median of 12, from as few as 3 words up to 30. Hashtags and @user tags count as words here, so treat this as a rough measure of post size.
scraped_df['word_count'] = scraped_df['tweet'].str.split().str.len()
scraped_df['word_count'].describe().round(1)
Part 22: Real Longest and Real Shortest Posts#
Looking at extreme cases is a quick way to spot oddities such as spam or broken records. idxmax() and idxmin() return the row label of the longest and shortest post, and .loc uses that label to pull out the tweet text. The output shows only the lengths, 151 and 18 characters, matching the maximum and minimum from the length summary. A useful habit is to print the actual text as well, since extremes are often where data problems hide.
longest_post = scraped_df.loc[scraped_df['post_length'].idxmax(), 'tweet']
shortest_post = scraped_df.loc[scraped_df['post_length'].idxmin(), 'tweet']
len(longest_post), len(shortest_post)
Part 23: Real Cleaning, Removing Extra Whitespace#
Scraped text often carries stray spaces at the start or end and repeated spaces in the middle, which can make identical posts look different. The code chains .str.strip() to trim the ends with .str.replace(r'\s+', ' ', regex=True), which collapses any run of whitespace into a single space. The output compares the original and cleaned columns. In the first row you can see the original starts with a space while tweet_clean does not, so the preview shows slightly more of the text.
scraped_df['tweet_clean'] = scraped_df['tweet'].str.strip().str.replace(r'\s+', ' ', regex=True)
scraped_df[['tweet', 'tweet_clean']].head(3)
Part 24: Real Duplicate Post Check#
Duplicate posts can come from reposts, bots or copy-paste campaigns, and they can inflate counts. The code uses .duplicated(subset='tweet_clean'), which flags every repeat of a cleaned post after its first appearance, and .sum() counts them. The output shows 23 duplicate posts. Checking on the cleaned text rather than the raw text matters, because otherwise an extra space would hide a duplicate. Decide deliberately whether to drop these, since repeats may themselves be a signal worth studying.
duplicate_count = scraped_df.duplicated(subset='tweet_clean').sum()
duplicate_count
Part 25: Saving the Real Scraped and Structured Dataset#
Saving the structured result means the next lesson can start from clean features rather than repeating the scrape. The code chooses the columns to keep, including the cleaned text and the new count features, and writes them with to_csv(..., index=False) so the row index is not saved as an extra column. It then reads the file back and compares row counts. The output True confirms the file holds every row. Reloading a file you have just written is a cheap check that catches write errors early.
output_cols = ['id', 'label', 'tweet_clean', 'hashtag_count', 'mention_count', 'word_count', 'post_length']
scraped_df[output_cols].to_csv('scraped_social_posts.csv', index=False)
reloaded = pd.read_csv('scraped_social_posts.csv')
reloaded.shape[0] == scraped_df.shape[0]
Part 26: Real Hashtag Frequency Export#
A separate hashtag frequency table is handy for reporting and for building charts later. The code takes the exploded hashtag series, counts every hashtag with value_counts(), and turns the result into a two-column DataFrame with reset_index(), then renames the columns to hashtag and count. The output shows 1,514 distinct hashtags. With 944 posts and an average of about 2.35 hashtags each, most hashtags appear only once or twice, which is typical of social data.
hashtag_freq = all_hashtags.value_counts().reset_index()
hashtag_freq.columns = ['hashtag', 'count']
hashtag_freq.to_csv('hashtag_frequency.csv', index=False)
hashtag_freq.shape[0]
Part 27: Shutting Down the Real Server#
Once the scrape is done, the server should be stopped so it does not keep holding a port and a background thread. server.shutdown() stops the serve_forever loop, and server.server_close() releases the network socket. There is no output. Forgetting this step is harmless in a short notebook session, but in longer scripts or repeated runs it can leave ports in use. Any later request to BASE_URL would now fail, which is expected.
server.shutdown()
server.server_close()
Part 28: Real Sanity Check, Hashtag Counts Match#
Vectorised string methods are fast but easy to get subtly wrong, so it is worth checking one result by hand. The code runs re.findall with the same #\w+ pattern directly on the first post's text and compares its length with the stored hashtag_count for that row. The output is True, so the pandas column agrees with the manual count. Checking a single row is only a spot check; trying a few more rows, especially ones with many hashtags, gives more confidence.
manual_hashtag_count = len(re.findall(r'#\w+', scraped_df['tweet'].iloc[0]))
manual_hashtag_count == scraped_df['hashtag_count'].iloc[0]
Part 29: Real Top Hashtag Bar Summary#
A dictionary of the top hashtags is a compact summary you can paste into a report or pass straight to a plotting function. top_hashtags.to_dict() turns the series into a dictionary of hashtag to count. The output repeats the top ten: #love with 46, #positive with 28, #thankful with 19, down to #altwaystoheal with 12. Notice that #gold, #silver and #blog tie on 15, so their order in the list is not meaningful.
top_hashtags.to_dict()
Part 30: Real Recap Print#
A one-line recap is useful for logs and for anyone reading the notebook quickly. The code uses an f-string to drop the key figures into a sentence. The output reports 944 posts scraped across 10 API pages, 1,514 distinct hashtags found, and a perfect round-trip against the source data. Building such summaries from variables, rather than typing numbers by hand, means they stay correct if the underlying data changes.
print(f'Scraped {len(scraped_df)} real posts across {total_pages} real API pages, found {hashtag_freq.shape[0]} distinct real hashtags, and confirmed a perfect round-trip against the original real source data.')
Wrap-Up: What You Learned#
- Real scraping mechanics, robots.txt checks, pagination loops, and JSON parsing, work identically whether the API is live on the internet or running locally for a lesson.
- A real round-trip check, confirming every scraped id matches the original source, is the honest way to verify a scrape actually captured everything.
- Real hashtags, mentions, and word counts turn raw unstructured text into real structured features ready for further analysis.
- Real content moderation labels are usually genuinely imbalanced, only a small fraction of posts were flagged in this dataset, a pattern that shows up across real platforms.
- Structuring scraped data immediately, rather than leaving it as raw JSON, is what makes the rest of a real social media analytics pipeline possible.
- Next video: real sentiment analysis on this exact same text data, going beyond structure into the actual meaning of these real posts.
Practice on your own
- Change
POSTS_PER_PAGEto 250, restart the server, and re-run the scrape loop. Check that you still get 944 posts and that the round-trip id check still returns True. - Lower-case the text before extracting hashtags (for example with
.str.lower()) and compare the new number of distinct hashtags with the 1,514 found here. - Compare average
post_length,word_countandmention_countfor flagged (label == 1) and normal posts usinggroupby('label'), and note which feature shows the biggest gap.
The next video applies sentiment analysis to this same text, moving from the structure of the posts to what they actually say. Watch the video walkthrough alongside this notebook to see each step run live.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



