Web scraping many URLs and writing to Excel file

Question

I am using requests and BeautifulSoup to scrape 20000 URLs, each web page containing a table of information. Essentially each web page is like a combo, and it has several items, each item having a description. I am scraping two elements - item, description from each row in the table for all the 20000+ combos.

I will then write this information to an excel file. Each row represents a combo, with the first cell of each row containing the URL of the combo. The header of the file contains the variables item . So for a particular combo (a particular row number) and a particular item (a particular column), there is a description. Any two combos could have some item in common; they could also have some item not in common. So I want to have an exhaustive list of all items available in my excel file header.

So for each row in a web page, I first check if the item already existed in my excel header.

have = False #boolean to check if the header already contains the name 
item_position = 1 #if found, find out its column number 
for cell in list(ws1.rows)[0]:
 value = cell.value
 val = value.encode('utf-8')
 if item == val:
 have = True
 break
 else:
 item_position += 1

If so then I will note down the column number and put description accordingly; if not I will append the item to the next empty cell in the header empty_header_cell and note down the column number and put description.

However, after like less than hundred URLs scraped, the speed becomes drastically slower. I think it is both due to web page requesting as well as finding existing item? I wonder if there is any improvement to the code to speed up the process. Here is my complete code:

from lxml import html
from bs4 import BeautifulSoup
import requests
import csv
import openpyxl
from openpyxl.workbook import Workbook
wb=openpyxl.load_workbook('Destination.xlsx')
ws1=wb.get_sheet_by_name('Sheet1')
empty_header_cell = 2 
#maintains the column number of the next empty cell in the excel file header
with open ('urls.csv') as f:
f_csv = csv.reader(f)
header = next(f_csv)
row_number = 2 #maintains a row number which increments after each url is scraped
for row in f_csv:
 url = row[0]
 ws1.cell(row=row_number, column=1).value = url
 wb.save(filename="Destination.xlsx") 
 try:
 page = requests.get(url)
 web = page.text
 soup = BeautifulSoup(web, 'lxml')
 table = soup.find('table', {'class': "tc_table"}) #find the table in each web page that I am goinf to scrape
 trs = table.find_all('tr')
 for tr in trs:
 ls = []
 for td in tr.find_all('td'):
 ls.append(td.text)
 ls = [x.encode('utf-8') for x in ls]
 try:
 item = ls[1]
 description = ls[2]
 have = False #boolean to check if the header already contains the name 
 item_position = 1 #if found, find out its column number 
 for cell in list(ws1.rows)[0]:
 value = cell.value
 val = value.encode('utf-8')
 if item == val:
 have = True
 break
 else:
 item_position += 1
 if have == True: #if item found
 ws1.cell(row=row_number, column=item_position).value = description
 wb.save(filename = 'Destination.xlsx')
 elif have == False: #if item not found
 ws1.cell(row=1, column=empty_header_cell).value = item #append item to the next empty header cell 
 ws1.cell(row=row_number, column=empty_header_cell).value = description
 empty_header_cell += 1 #update next empty header cell
 wb.save(filename = 'Destination.xlsx')
 except IndexError:
 print("i am an IndexError")
 row_number += 1 #start scraping the next url
 except IndexError: #to skip those webpages that have slightly different format so data cannot be located
 print("skipping this website") 
 row_number += 1 
 except AttributeError:
 print("attribute error")
 row_number += 1

Answer 1

Let me take it from the performance perspective.

The main bottleneck

You are scraping the pages sequentially in the blocking manner - processing urls one at a time, not proceeding to the next url until you are done with the current one.

There are a number of tools that can help to switch to an asynchronous strategy. Look into Scrapy web-scraping framework. There is also aiohttp which is based on AsyncIO.

Gathering scraping results

I think you don't actually need an Excel writer here since you are only writing simple text data - you are not concerned with advanced data types or workbook style and formatting. Use a CSV writer - Python has a built-in csv module.

Note that if you'll switch to Scrapy, you'll get the built-in CSV exporter "for free".