Scraping table (several pages) to Pandas Dataframe

user_12777976 0
Viewed 88

I'm trying to transfer the data of a long table (24 pages) to a Pandas Dataframe, but facing some issues with (i think) the for-loop code.

import requests
from bs4 import BeautifulSoup
import pandas as pd

base_url = 'https://scrapethissite.com/pages/forms/?page_num={}'
res = requests.get(base_url.format('1'))
soup = BeautifulSoup(res.text, 'lxml')

table = soup.select('table.table')[0]
columns = table.find('tr').find_all('th')
columns_names = [str(c.get_text()).strip() for c in columns]
table_rows = table.find_all('tr', class_='team')

l = []
for n in range(1, 25):
    scrape_url = base_url.format(n)
    res = requests.get(scrape_url)
    soup = BeautifulSoup(res.text, 'lxml')
    for tr in table_rows:
        td = tr.find_all('td')
        row = [str(tr.get_text()).strip() for tr in td]
        l.append(row)

df = pd.DataFrame(l, columns=columns_names)

The Dataframe comes out as a repetition of the first page only, rather than a copy of all the data in the table.

pandas beautifulsoup python
2 Answers

I agree with @mxbi.

Try it:

import requests
from bs4 import BeautifulSoup
import pandas as pd

base_url = 'https://scrapethissite.com/pages/forms/?page_num={}'

l = []
for n in range(1, 25):
    scrape_url = base_url.format(n)
    res = requests.get(scrape_url)
    soup = BeautifulSoup(res.text, 'lxml')

    table = soup.select('table.table')[0]
    columns = table.find('tr').find_all('th')
    columns_names = [str(c.get_text()).strip() for c in columns]
    table_rows = table.find_all('tr', class_='team')

    for tr in table_rows:
        td = tr.find_all('td')
        row = [str(tr.get_text()).strip() for tr in td]
        l.append(row)

df = pd.DataFrame(l, columns=columns_names)
user_152399510

requests is needed because the server wants an user-agent header and pandas read_html doesn't allow for that. As you still want to use pandas to generate the dataframe you could gain some efficiency through using multiprocessing to handle the requests, and within an user defined function extract the table of interest and pass as string to read_html. You will get a list of dataframes which can be combined with pandas concat.

Note: This can't be run from within Jupyter as will block.

import pandas as pd
from multiprocessing import Pool, cpu_count
import requests
from bs4 import BeautifulSoup as bs

def get_table(url:str)-> pd.DataFrame:
    soup = bs(requests.get(url).text, 'lxml')
    df = pd.read_html(str(soup.select_one('.table')))[0]
    df['page_num'] = url.split("=")[-1]
    return df

if __name__ == '__main__':
    
    urls = [f'https://scrapethissite.com/pages/forms/?page_num={i}' for i in range(1, 25)]

    with Pool(cpu_count()-1) as p:
        results = p.map(get_table, urls)

    final = pd.concat(results)
    print(final)
    # final.to_csv('data.csv', index = False, encoding = 'utf-8-sig')
user_62412350
Related