Background

The initial motivation for this came from a conversation at a local watering hole. Why did one restaurant nearby not have any inspections for the last eight years, while one has had them on schedule every 18 months? Was it small-town politics or incompetence?

My initial run showed 35 businesses out of tolerance. I inquired about it. The head inspector told me of some challenges she faced and that she was hiring more inspectors.

I waited over a year, updated the code, and ran it again. Now nobody has gone more than two years without an inspection.

The code is broken down into four primary sections: scraping, HTML parsing, data cleaning, and analysis.

Scraping

I used Selenium to scrape. Thankfully, modern Selenium automatically detects Chrome and manages the driver itself.

The function scrape_health_deparment opened the main page and identified the rows. Then scrape_popup_data went row by row and captured the HTML of each pop-up window. It then saved this data as an HTML file.

The website that the rows are extracted from.

The pop-up when a row is selected. Each pop-up is saved as a separate file.

HTML Parsing

Each of the saved pop-ups is then parsed so that the business name, date, inspection type, start time, end time, and inspector name of each inspection is added to a pandas dataframe. The dataframe is then saved as a CSV file.

The code that examines the HTML for inspections. Handles missing values easily.

Data Cleaning

After the initial loading of the CSV file, I determined there were a lot of missing inspection times. These were duplicate entries from the HTML parsing. One entry would have the proper times; the next one had no times.

Because the entry with times was before the entry with missing values, the fix for this was relatively easy – just take the first entry for the same date.

I checked to see how many missing values existed after I applied the fix. The number had been greatly reduced, and now only included missing phone numbers and missing end times.

I then checked the unique values of start time, end time, inspector, and inspection type. Other than needing to change the data type to datetime, the start and end times were all entered correctly. Inspector and inspection type had multiple ways of spelling the same name or the same inspection.

To correct these typos, I used simple mapping to join similar values.

Start and End Times were formatted as text. As a result, I converted them to Python time objects.

Data Analysis

I started out with general analyses: inspection counts by type and day of the week, inspection count by start time, average inspection duration by inspector, average routine inspection duration by inspector. I ended with a heat map that shows the most likely inspection times.

This makes sense as inspectors are government employees and do not like weekends. Further analysis could determine if the few weekend inspections are due to the opening hours of the establishments.

The start times make sense for a traditional workday.

These two are interesting. Routine is the required inspection, so it would make sense that a routine inspection should be the longest. It would be interesting to see why Dan Cordova’s time drops dramatically between routine and all inspection types while Amy Osborn increases.

For business owners, the most likely inspection time is the start of the week at 10 or 11 am.

I ran a code to see how many facilities were out of tolerance by the health department guidelines, that is, no inspection within 18 months, or 540 days.

This is a great improvement from when I ran the initial code in November of 2024, and multiple facilities were overdue up to 10 years.

Future Work

Further granular analysis of the inspector and inspection type dimensions. Actual report can be seen here.

Add a part to the web scraping code to download each inspection report for further analysis, such as determining likely violations.

See the full code here