1

Website in question: http://www-odi.nhtsa.dot.gov/ewr/qb/index.cfm

Automakers are required to report on a quarterly basis to NHTSA any claims brought against them that involve death, injury or property damage.

While NHTSA makes other databases open to the public in the form of large flat files (text) EWR reports are still segregated by automaker, by Report Type and by Quarter.

I'm trying to find the best tool (and tool operator) to use to scrape all this data from the website.

Currently, if you select an Manufacturer, Report Type and Report Period - then click on, in my case "light duty vehicle" you are taken to a new page where you have the option of downloading into a text file the results of that one inquiry.

With 26 automakers (listed below) and multiple types of reports and multiple quarters - doing this by hand will take an eternity.

Any thoughts on an easier, faster way to pull all of this data into a single file?

UPDATE:

I was able, with the help of a friend, to come up with the following code that works for production data, but doesn't seem to work for Death and Injury.When I chane "prod" to "di" I get the correct "app" option value, but for some reason the script skips over the "yr_qtr" selection and loops back to the next manufacturerCode.

import csv
import itertools
import os
from selenium import webdriver
from selenium.webdriver.support.select import Select
from selenium.webdriver.support.ui import WebDriverWait


def createDir(filepath):
    if os.path.exists(filepath):
        pass
    else:
        os.mkdir(filepath)



# Create directory at "C:\Data" to hold all of the files
createDir(r"C:\Data")

# Base URL to receive queries        
url = "http://www-odi.nhtsa.dot.gov/ewr/qb/index.cfm"

# Read in the file containing manufacturer names and EWR codes
# Data is in the format "EWR code,ManufacturerName"
with open("C:/Data/manufacturer_codes.csv", "r") as f:
    csvreader = csv.reader(f)
    manufacturerDict = {row[0]: row[1] for row in csvreader}


# Define which time periods we are interested in.  The data comes quarterly with
# period values in the format YYYY,m
requestedPeriods = list(itertools.product(range(2001,2016),range(1,5)))


# Iterate through each of the manufacturer entries and download the entire history
# for that manufacturer
for manufacturerCode, manufacturerName in manufacturerDict.items():
    # Get rid of characters that might complicate file/folder naming
    charactersToReplace = [".", ",", "-"]
    manufacturerName = manufacturerName.strip().translate("".join(charactersToReplace))
    # Set the location for file downloads to a folder with the manufacturer's name
    filepath = os.path.join(r"C:\Data", manufacturerName)
    createDir(filepath)


    # Set the selenium browser profile to control how it handles the dialog box
    # asking us to download the EWR text file
    profile = webdriver.FirefoxProfile()
    # Setting Firefox folderList option to 2 tells Firefox to direct downloads to 
    # a user-defined folder 
    profile.set_preference("browser.download.folderList",2)
    profile.set_preference("browser.helperApps.neverAsk.saveToDisk","text/plain") 
    profile.set_preference("browser.download.dir",filepath)
    # Create the selenium web driver using the profile settings we created earlier
    wb = webdriver.Firefox(firefox_profile=profile)

    def fetchData():
        wb.get(url)
        try:    
            wb.find_element_by_name("ewr_id")
            wb.find_element_by_xpath('//select[@name="ewr_id"]/option[@value='+ manufacturerCode + ']').click()
            wb.find_element_by_xpath('//select[@name="app"]/option[@value="prod"]').click()
            currentPeriod = "{},{}".format(periodval[0],periodval[1])
            wb.find_element_by_xpath('//select[@name="yr_qtr"]/option[@value="' + currentPeriod +'"]').click()
            wb.find_element_by_css_selector("a[href*='javascript:document.ewrsearch']").click()
            wb.find_element_by_xpath('//input[@name="Download"]').click()

            return True
        except:
            return False


    for periodval in requestedPeriods:
        failureCounter = 0
        # If it fails to download (e.g. because the site is slow to respond), try again
        while failureCounter < 3:
            if not fetchData():
                failureCounter += 1
            else:
                break

    wb.close()
wb.quit()
2

Selenium is a good option to interact with the site and eventually get to the page where you can download/scrape the data you need. I've used the python (https://selenium-python.readthedocs.org/) version with good success.

Start with:

from selenium import webdriver
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
website = "http://www-odi.nhtsa.dot.gov/ewr/qb/index.cfm"
driver = webdriver.Firefox()
wait = WebDriverWait(driver, 100)
driver.get(website)

This will get you to the main form page. I use Firefox since I haven't gotten Chrome to work consistently. Then you'll need to have Selenium select each of the drop-downs. You can use the Inspect Element (right-click or CMD/Ctrl+Shift+C) tool in Chrome to find the name/identifier for the element.

The manufacturer has the name ewr_id, so next write:

driver.find_element_by_name("ewr_id")
driver.find_element_by_xpath('//select[@name="ewr_id"]/option[@value="76"]').click()
driver.find_element_by_xpath('//select[@name="app"]/option[@value="di"]').click()
driver.find_element_by_xpath('//select[@name="yr_qtr"]/option[@value="2008,2"]').click()

to select 76 (for 4-Star Trailers), death and injury records, and Q2 2008, for example. Then you'll have to select the link to go to the next page:

driver.find_element_by_css_selector("a[href*='javascript:document.ewrsearch']").click()

At this point, you can scrape the information in the table, or select the download button:

driver.find_element_by_xpath('//input[@name="Download"]').click()

To download the file. Close the session with

driver.quit()

To iterate through many options, you'll want to have the option values as a string variable and run a for loop through a list of the values you're interested in pulling.

  • Thanks Aaron. I manage to come up with the following script and while it works for the Production Data, I can't get it to work with the Death and Injury - – DCCarGeek Sep 9 '15 at 13:29
  • Your line: wb.find_element_by_xpath('//select[@name="app"]/option[@value="prod"]').click() only looks at Production data. You'll need to change "prod" to "di" (similar to my example to look at Death and Injury records") – Aaron Sep 9 '15 at 19:25
  • When I made that change is when the script failed to go on and select the correct "yr_qtr" and did not retrieve any data. It simply went back to the next ewr_id and continued in that loop. I think it has something to do with this line of code; currentPeriod = "{},{}".format(periodval[0],periodval[1]) – DCCarGeek Sep 10 '15 at 20:44
  • I figured it out. I put in a print function and the script was working, but the issue was there is no 2001-2003 data for Death and Injury. So I was seeing the date values print, but the script kept running because there wasn't a value that matched those dates. – DCCarGeek Sep 11 '15 at 21:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.