[Python] How to Scrape Multiple Pages and Consolidate Data into a Single Table Using Python?

I am trying to scrape data from a directory on a website (using Python) that has multiple pages, and I want to combine the results from all the pages into one table. However, I am encountering an issue where the same table data is being printed multiple times, corresponding to each page, but I want the tables from the next pages to be combined into a single table.

Here's the code I'm currently using:

import requests


import pandas as pd

df = {} for page_number in range(1, 9): # Loop through pages HEADERS = { 'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/99.0.4844.51 Safari/537.36', 'Accept-Language': 'en-US, en;q=0.5' } url = f'https://fairfaxhs.fcps.edu/staff-directory?keywords=&field_last_name_from=&field_last_name_to=&items_per_page=10&page={page_number}' html = requests.get(url, headers=HEADERS).content df_list = pd.read_html(html) df = df_list[-1] # Get the last table from the page

replacement = {
",": ".",
" ": ""
}

df['Email'] = df.Name.replace(replacement, regex=True) + '@fcps.edu'
print(df) # Prints data for each page
df.to_csv('my_data.csv') # Saves data to CSV


Problem: Each page's table is being printed and saved separately, but I want to combine all tables into one table. Specifically, I want to concatenate the data from all pages into a single DataFrame and then save it once. What I’ve Tried: I tried using df.to_csv() inside the loop, but it keeps saving the tables from each page individually rather than appending them together. What I Need Help With: How can I combine the data from all pages into one DataFrame while keeping the data from each page in the correct order? How can I save the combined DataFrame once all pages are scraped? Expected Outcome: After scraping, I want to have a single CSV file containing all the data from all the pages, rather than having separate files or repeated tables for each page.

Continue reading...