Web scraping in Python: from a Wikipedia table to a CSV file

Overview

In this lesson, you will learn how to scrape data from a real website—specifically, a Wikipedia page listing the largest companies in the United States by revenue—using Python, BeautifulSoup, and Requests.

0:00 Introduction to Web Scraping from a Real Website

• Goal: Scrape tabular data from a real website, put it into a Pandas DataFrame, and optionally export it to a CSV file.
• Target Page: Wikipedia page titled "List of the largest companies in the United States by revenue" (rather than a simplified practice page).
• Complexity: Working with real Wikipedia tables introduces extra formatting steps and requires selecting the correct table when multiple tables share similar classes.

1:09 Importing Libraries and Requesting the Web Page

• Start by importing BeautifulSoup from bs4 and importing the requests library.
• Set the target Wikipedia URL in a variable and use requests.get() to fetch the page.
• Create a BeautifulSoup object by passing page.text and the parser html.

Run the following code in your Jupyter notebook:

from bs4 import BeautifulSoup
import requests

url = 'https://en.wikipedia.org/wiki/List_of_largest_companies_in_the_United_States_by_revenue'

page = requests.get(url)

soup = BeautifulSoup(page.text, 'html')

print(soup)

2:33 Inspecting the Web Page Structure for Data Extraction

• Inspect the Wikipedia page in the browser to identify the data to extract (rank, name, industry, revenue, etc.).
• Notice there are multiple tables on the page (e.g., list of companies by revenue, and further down, a list of companies by profit).
• Use the browser's developer tools inspector to examine the table classes. The primary table uses classes like wikitable, sortable, and jquery-tablesorter.
• Copy outer HTML snippets if needed to verify classes and structure, noting that multiple tables on the page might share the exact same class attributes (wikitable sortable jquery-tablesorter).

5:00 Finding All Tables on the Page

We need to target the tables on the Wikipedia page. Looking at the browser's Elements panel, we see a box with more citations, and below that, the table elements.

Let's use soup.find_all('table') to extract all tables from the parsed HTML.

soup.find_all('table')

Running this command returns a list of multiple tables. Inspecting the output:

• The first table (index 0) contains something else (like the references/edit links box).
• Scrolling further down, we see tables with classes like wikitable sortable.
• Index 1 contains the table for "List of the largest companies in the United States by revenue".
• Index 3 (or further down) contains tables like "List of the largest companies by profit" (e.g., Ford Motor, Wells Fargo, Goldman Sachs).

To target a specific table using indexing:

soup.find_all('table')[1]

This returns the first company table (ranked by revenue), containing rows (<tr>), table headers (<th>), and table data cells (<td>).

6:54 Refining Table Selection with Attributes

We can make our selection more specific by filtering on attributes like class instead of relying solely on list indices.

Initially, we try filtering by class_:

soup.find('table', class_ = 'wikitable sortable')

However, this may return a TypeError: 'NoneType' object is not subscriptable if the class string doesn't match precisely. Inspecting the element in the browser reveals the full class string:

<table class="wikitable sortable jquery-tablesorter">

If we want to pull specifically by table class, let's assign the result of soup.find_all('table') to a variable:

table = soup.find_all('table')

And if we print table[1], we get the exact table data we want:

print(table)

8:59 Extracting Table Headers (<th>)

Now that we have isolated our target table, the first objective is to extract the table titles (headers).

Inspecting the table structure, the column headers are wrapped inside <th> tags:

• Rank
• Name
• Industry
• Revenue (USD millions)
• Revenue growth
• Employees
• Headquarters

We can extract all <th> tags from our table using .find_all('th'):

soup.find_all('th')

Because <th> tags are unique to the headers (whereas row data uses <td> tags), this makes pulling the headers very straightforward.

Let's store the extracted titles into a variable called world_titles:

world_titles = soup.find_all('th')

Printing world_titles shows a list of all <th> elements containing the header strings. In the next steps, we will loop through this list to clean and format the column titles.

10:00 Extracting and Cleaning Table Titles

Use list comprehension to extract the text from each table header element:

world_table_titles = [title.text for title in world_titles]

Print the titles to check the output:

print(world_table_titles)

Output:

['Rank\n', 'Name\n', 'Industry\n', 'Revenue (USD millions)\n', 'Revenue growth\n', 'Employees\n', 'Headquarters\n', 'Rank\n', 'Name\n', 'Industry\n', 'Profits(USD millions)\n']

The output contains newline characters (\n) and duplicate titles from multiple tables on the page. To remove the newline characters, apply .strip() to the text inside the list comprehension:

world_table_titles = [title.text.strip() for title in world_titles]

Print the cleaned titles:

print(world_table_titles)

Output:

['Rank', 'Name', 'Industry', 'Revenue (USD millions)', 'Revenue growth', 'Employees', 'Headquarters', 'Rank', 'Name', 'Industry', 'Profits(USD millions)']

11:19 Fixing Table Selection and DataFrame Creation

The list of titles contains headers from two different tables on the Wikipedia page (one for revenue and one for profits). Examining the table finding step, soup.find_all('th') pulled <th> elements from all tables on the page instead of just the first target table.

To target only the first table, find all tables first:

table = soup.find_all('table')

Then search within the correct table (index 0) for the header elements:

table = soup.find_all('table')[0]
world_titles = table.find_all('th')
world_table_titles = [title.text.strip() for title in world_titles]
print(world_table_titles)

Output:

['Rank', 'Name', 'Industry', 'Revenue (USD millions)', 'Revenue growth', 'Employees', 'Headquarters']

Now, import Pandas and create the initial DataFrame using these column titles:

import pandas as pd

df = pd.DataFrame(columns = world_table_titles)
df

Running this outputs an empty DataFrame with the correct column headers:

• Rank
• Name
• Industry
• Revenue (USD millions)
• Revenue growth
• Employees
• Headquarters

13:38 Inspecting Table Rows (<tr>) and Data (<td>)

To populate the DataFrame, extract the row data.

• <th> tags represent table headers (R for rows, headers/titles).
• <td> tags represent table data inside the rows (R for rows, D for data).
• These are all encapsulated within <tr> tags (where TR represents rows).

Test finding rows within the table:

table.find_all('tr')

When inspecting the output of table.find_all('tr'), rows start with the headers (<th>), followed by rows of data (<td>) separated by commas, with subsequent rows numbered sequentially (td1, td2, td3, etc.).

15:00 Extracting Table Rows (<tr>) and Cells (<td>)

To extract the column data from the table rows, loop through column_data (which contains all <tr> tags):

column_data = table.find_all('tr')

for row in column_data:
    row.find_all('td')

If we want to inspect the cell data inside each row, we print out the find results:

for row in column_data:
    print(row.find_all('td'))

Running this block outputs a list of all <td> HTML elements for every row in the table.

16:03 Extracting and Cleaning Cell Text

To extract the text from the HTML tags and clean up whitespace using .text.strip(), we loop through each row's data:

for row in column_data:
    individual_row_data = [data.text.strip() for data in row.find_all('td')]
    print(individual_row_data)

• Tip: Using .text.strip() removes unwanted whitespace, newlines, and padding around the text inside the <td> tags.

17:32 Appending Individual Rows to a DataFrame

Currently, the loop prints individual lists of row data one by one as it iterates. To store this data inside our DataFrame (df), we need to append each row dynamically as the loop runs.

To insert rows one at a time at the correct position, we use .loc combined with len(df) to find the current length of the DataFrame:

df = pd.DataFrame(columns = world_table_titles)

for row in column_data:
    individual_row_data = [data.text.strip() for data in row.find_all('td')]
    
    length = len(df)
    df.loc[length] = individual_row_data

How it works:

• len(df): Checks the current number of rows in the DataFrame each time the loop runs.
• df.loc[length]: Places the individual_row_data into the next available row index in the DataFrame, ensuring data is added sequentially.

20:00 Appending Row Data to the DataFrame and Fixing Mismatched Columns

• Loop through column_data to find all row tags (tr), then find all data tags (td) within each row:

  for row in column_data:
      row_data = row.find_all('tr')
      for row in row_data:
          row_data = row.find_all('td')
          individual_row_data = [data.text.strip() for data in row_data]
          length = len(individual_row_data)
          df.loc[length] = individual_row_data
  

• Running this block causes a ValueError: Cannot set a row with mismatched columns.
• Inspecting individual_row_data reveals that the first element in the list is empty ([]). This happens because the first <tr> inside column_data contains header/title data rather than standard table data (td).
• To fix the mismatched columns error, change the index from length to 1 (or use a list [1]) to target the correct table row list.

Updated cell (In [42]):

for row in column_data:
    row_data = row.find_all('tr')
    for row in row_data:
        row_data = row.find_all('td')
        individual_row_data = [data.text.strip() for data in row_data]
        length = len(individual_row_data)
        df.loc[len(df)] = individual_row_data

21:34 Viewing the Populated DataFrame

• Pulling up the DataFrame (df):

  df
  

• Output displays a 100 rows × 7 columns table:
  • Rank
  • Name
  • Industry
  • Revenue (USD millions)
  • Revenue growth
  • Employees
  • Headquarters

22:10 Exporting the DataFrame to a CSV File

• To export the DataFrame into a CSV file, use the .to_csv() method with the file path and name:

  df.to_csv(r'C:\Users\alex\OneDrive\Documents\Python Web Scraping\Folder for Output\Companies.csv')
  

• Checking the output folder shows Companies.csv. Opening it in Excel reveals an unwanted extra index column at the front of the data.

23:10 Removing the Index Column on Export

• To prevent the index from being exported into the CSV file, set index=False:

  df.to_csv(r'C:\Users\alex\OneDrive\Documents\Python Web Scraping\Folder for Output\Companies.csv', index = False)
  

• Refreshing the folder and reopening Companies.csv in Excel shows a clean table without the unwanted index column.

23:46 Review of the Complete Web Scraping Project

Reviewing the full code built across the project steps:

1. Importing Libraries and Fetching the URL:

   from bs4 import BeautifulSoup
   import requests
   import pandas as pd
   
   url = 'https://en.wikipedia.org/wiki/List_of_largest_companies_in_the_United_States_by_revenue'
   page = requests.get(url)
   soup = BeautifulSoup(page.text, 'html')
   

2. Finding the Table:

   world_table = soup.find('table', class_ = 'wikitable sortable')
   

3. Extracting and Cleaning Table Titles:

   world_table_titles = world_table.find_all('th')
   world_table_titles = [title.text.strip() for title in world_table_titles]
   

4. Creating the DataFrame with Column Headers:

   df = pd.DataFrame(columns = world_table_titles)
   

5. Extracting Row Data, Cleaning Cells, and Appending to DataFrame:

   column_data = world_table.find_all('tr')
   
   for row in column_data:
       row_data = row.find_all('td')
       individual_row_data = [data.text.strip() for data in row_data]
       length = len(individual_row_data)
       df.loc[len(df)] = individual_row_data
   

6. Exporting to CSV:

   df.to_csv(r'C:\Users\alex\OneDrive\Documents\Python Web Scraping\Folder for Output\Companies.csv', index = False)
   

25:00 Exporting to CSV and Conclusion

The final line of code exports the pandas DataFrame to a CSV file:

df.to_csv(r'C:\Users\alexf\OneDriveDocuments\Python Web Scraping\Folder for Output\Companies.csv', index = False)

By setting index = False, the row numbers (0, 1, 2...) are excluded from the output file so they aren't written as an extra column.

Key Takeaways

• Fetch Web Pages: Use the requests library to fetch HTML content and BeautifulSoup from bs4 to parse it for data extraction.
• Inspect Page Structure: Use browser developer tools to inspect elements, identify specific table classes (like wikitable sortable), and distinguish between multiple tables on a page.
• Target Specific Elements: Isolate target tables using list indexing or class attributes to avoid pulling unwanted data from other sections.
• Extract Table Headers: Use .find_all('th') to locate table column headers and apply .text.strip() via list comprehension to remove unwanted newline characters and whitespace.
• Initialize Pandas DataFrames: Create an empty Pandas DataFrame using the cleaned header titles as column names.
• Extract Row Data: Loop through table rows (<tr>) and extract cell data (<td>), applying .text.strip() to clean text values.
• Dynamically Populate DataFrames: Use df.loc[len(df)] within a loop to sequentially append extracted row lists into the DataFrame.
• Handle Mismatched Rows: Filter out empty lists or header rows to prevent shape errors when appending data to a DataFrame.
• Export to CSV: Use the .to_csv() method to save the final DataFrame to a file, setting index=False to prevent exporting unwanted row index numbers.