Drayage Webscraper: Limited to table structure

Question

This is my first working scraper. I'm sure a lot can be improved. My biggest question is how can I better specify what data to pull? All the data I'm currently grabbing is needed, but I couldn't think of another way to prevent the scraper from pulling header and footer data. This is why I used < 14 as a requirement. Any other advise on how I can improve is welcome.

import requests
from bs4 import BeautifulSoup
import pandas as pd
import os 

url = 'https://www.drayage.com/directory/dray-rates.cfm?metro=LAX'
response = requests.get(url)

soup = BeautifulSoup(response.text, 'html.parser')

# find all table rows
table_rows = soup.find_all('tr')

# create a list to store column data
column_data = []

# determine the row to start from
start_row = 2

# flag to indicate when to start scraping
start_scraping = False

# iterate over each row, starting from the third row
for row in table_rows[start_row:]:
    # extract data from each cell in the row
    row_data = [cell.text.strip() for cell in row.find_all('td')]

    # find an img tag with the specific src attribute within the row
    img = row.find('img', src='https://www.loadmatch.com/images/arrow_black_horz.gif')

    # if found, replace 'Arrow' column data with "right"; otherwise, "left" 
    # (left image has different url name)
    if img and len(row_data) >= 3:  # make sure 'Arrow' column exists 
        row_data[2] = 'Right'
    elif len(row_data) >= 3:
        row_data[2] = 'Left'

    # if the row has 14 cells, start scraping - refers to column #
    if len(row_data) == 14:
        start_scraping = True

    # if the row has less than 14 cells, stop scraping
    elif len(row_data) < 14 and start_scraping:
        break

    # if the row has data and scraping has started, append it to column_data
    if row_data and start_scraping:
        column_data.append(row_data)

# define column names
column_names = ['Terminal Name', 'Terminal', 'Arrow', 'Zip Code', 'State', 'Province', 'Seven', 'Eight', 
                'Total', 'Notes', 'One-Way Miles', 'Per Mile (fuel incl)', 'Date','Blank']

# convert scraped data to df
df = pd.DataFrame(column_data, columns=column_names)

# write to csv
df.to_csv('fullrun.csv', index=False)

Answer 1

fragility

The trouble with scrapers (and with unit tests) is that they can be fragile w.r.t. minor changes in the target text. That is, a "trivial" table formatting change on the origin server can break an automated scraper or an automated test.

    if len(row_data) == 14:

Author and Reviewer share a concern that this is on the fragile side. Web pages tables are designed with an eye toward satisfying a human user, rather than a scraping end point. Adding a 15th column would not knock a human end user for a loop. Triggering on human-readable text tends to be more robust to changes than triggering on synthetic aspects like 14 columns. So consider scanning tables till you find one with an initial TD that starts "Dray Rate History Over..."

silent acceptance vs fatal error

If table structure changes, there's several possible outcomes:

• Cosmetic change, no effect on correctness of outputs.
• Change silently causes outputs to be truncated / incorrect.
• Change causes fatal error with stack trace.

The middle outcome is deadly, causing us to trust bad output. Fatal errors are much better, as we have an opportunity to notice the trouble and adjust the scraping code.

tags

The page HTML has three <table>s organized like this:

<table>constant header boilerplate</table>
<table><tr><td>
    <table>...

So we have three tables. The first one we can just skip, it's uninteresting. The second exists purely for centering purposes, something that most folks would address with CSS. The third (nested) table is the one of interest.

Regrettably, at the table level we don't see id or css class tags that would be useful selectors. The third table does start with <tr class="tabletitlebackground" and also <td class="tabletitle" ... >Dray Rate History Over ..., so maybe we could exploit one of those classes in a loop.

Alternatively, you might prefer to codify the "three tables" setup, in the hope that it won't change. Find first table, and discard it. Find second table, and within it immediately find third table, the one of interest. Pass it to a helper function.

Answer 2

Using requests/bs4 here is of dubious value when you're targeting Pandas. Just use Pandas for everything. Note - if you really care about parsing the "arrow", you're going to have to get creative.

Focus less on attempting to manoeuvre through the DOM and more on data sanity, since this table needs a lot of it.

Generally speaking, this page markup is a nightmare. I suppose that's a mixed blessing, because it's useful experience for you to learn how scraping works and how ungainly it is compared to other data exchange methods; but keep in the back of your mind - if you were to write HTML to display these data, please, please don't do it like this. I won't go into much more detail because I assume you're not the author ("LoadMatch"). I suppose it's also a product of its time - 1999!

import pandas as pd

# The markup is cursed: there's table within a table. Pandas picks up both
# and we only want the inner table; take the larger of the two.
df = max(
    pd.read_html(
        'https://www.drayage.com/directory/dray-rates.cfm?metro=LAX',
        match='Dray Rate History Over',
        skiprows=1,
        header=0,
        parse_dates=[12],
    ),
    key=lambda df: df.size,
)
df.columns = [
    'Terminal Name', 'Terminal', 'Arrow', 'Zip Code', 'State', 'City',
    'Total', 'FSC', 'Formatted Total', 'Notes', 'One-Way Miles',
    'Per Mile (fuel incl)', 'Date', 'Blank',
]

# drop footer and junk cols
df = df.iloc[:-3, :].drop(
    labels=['Arrow', 'Formatted Total', 'Blank'],
    axis='columns',
)

# data sanity
df['City'] = df['City'].str.removesuffix(' ()')
df['Per Mile (fuel incl)'] = df['Per Mile (fuel incl)'].str.removeprefix('$').astype(float)
df['FSC'] = df['FSC'].str.removesuffix('%').astype(float) / 100
df['One-Way Miles'] = df['One-Way Miles'].str.extract('^(.+) miles').astype(float)

pd.options.display.max_columns = 20
pd.options.display.width = 200
print(df)

                          Terminal Name Terminal Zip Code State              City Total   FSC                                        Notes  One-Way Miles  Per Mile (fuel incl)       Date
0                                   40'      LAX    92551    CA     Moreno Valley   650  0.00  Chassis\t$50/per day Triaxle Chassis $80...           75.0                  4.33 2024-04-09
1     40' Long Beach Container Terminal      LAX      NaN    CA  Santa Fe Springs   450  0.00  Chassis\t$50/per day Triaxle Chassis $80...           18.0                 12.50 2024-04-09
2     40' Long Beach Container Terminal      LAX      NaN    NV         Las Vegas  1400  0.00  Chassis\t$50/per day Triaxle Chassis $80...          275.0                  2.55 2024-04-09
3     40' Long Beach Container Terminal      LAX      NaN    NV         Las Vegas  1285  0.36    chassis:$40/day Storage:$40/day Detent...          275.0                  3.18 2024-04-09
4     40' Long Beach Container Terminal      LAX      NaN    CA  Santa Fe Springs   380  0.00    chassis:$40/day Storage:$40/day Detent...           18.0                 10.56 2024-04-09
...                                 ...      ...      ...   ...               ...   ...   ...                                          ...            ...                   ...        ...
1995                                40'      LAX    92029    CA         Escondido   875  0.00  1) CHASSIS FEE: $40.00 PER CALENDAR DAY ...          110.0                  3.98 2024-03-15
1996     APM Terminals - San Pedro W185      LAX    86001    AZ         Flagstaff  2725  0.00  1) CHASSIS FEE: $40.00 PER CALENDAR DAY ...          454.0                  3.00 2024-03-15
1997                                40'      LAX    89030    NV   North Las Vegas  1975  0.00  1) CHASSIS FEE: $40.00 PER CALENDAR DAY ...          268.0                  3.68 2024-03-15
1998      Long Beach Container Terminal      LAX    93030    CA            Oxnard   825  0.00  1) CHASSIS FEE: $40.00 PER CALENDAR DAY ...           73.0                  5.65 2024-03-15
1999         Yusen Terminals - YTI Y790      LAX    85034    AZ           Phoenix  1975  0.00  1) CHASSIS FEE: $40.00 PER CALENDAR DAY ...          411.0                  2.40 2024-03-15

[2000 rows x 11 columns]