Using BeautifulSoup to Extract Data from Wikipedia

There is a multitude of data available on the Wikipedia site just waiting to be extracted and used for data science!

Keeping on the premiership football theme, this article explains how to extract data about premiership managers over the years from the following URL on wikipedia: https://en.wikipedia.org/wiki/List_of_Premier_League_managers

The Jupyter notebook is available to download at the bottom of the post.

Here is the table that we want to extract the rows/columns of, and then create a pandas dataframe from.

The trick is to view the source of the HTML, find the unique attributes that identify the table, and then loop through the rows of the table in order to grab the necessary column values. In the example above, the table has a unique class of wikitable sortable plainrowheaders, which is enough to identify it on the page. There was some extra work to do here, as we needed to know the status of the manager e.g. past, present or caretaker, and also their country of origin (which as you can see from the screenshot is an image rather than text).

Find and Extract the tabular data

from bs4 import BeautifulSoup
import requests
import pandas as pd

url = "en.wikipedia.org/wiki/List_of_Premier_League_managers"
r  = requests.get("https://" +url)
data = r.text
soup = BeautifulSoup(data)

table = soup.find('table', attrs={'class':'wikitable sortable plainrowheaders'})

Extract the table data to a dataframe

managers = []
# Loop through the rows
table_rows = table.find_all('tr')
for tr in table_rows:
    # Table row header contains managers name and if they are past, incumbent or caretaker
    th = tr.find('th')
    mgr = th.get_text().strip()
    
    status = "past"
    if "dagger" in str(th):
        status = "present"
    if "double-dagger" in str(th):
        status = "caretaker"
    
    country = ""
    for img in tr.find_all('img', alt=True):
        if "dagger" not in img['alt']:
            country = img['alt']
    
    td = tr.find_all('td')
    row = [tr.text.strip() for tr in td]
    row.insert(0, mgr)
    row.insert(1, status)
    row.insert(2, country)
    managers.append(row)

df = pd.DataFrame( managers )
df.columns =['Name','Status', 'Country', 'Empty', 'Club', 'From', 'Until', 'Duration', 'Years in Leage', 'Ref']
# Get rid of empty row and column
df.drop([0], inplace=True)
df.drop(["Empty"], axis=1, inplace=True)
df.drop(["Ref"], axis=1, inplace=True)
df

406 rows × 8 columns