DEV Community

Haku
Haku

Posted on

I filtered the FMCSA census (4.5M rows) down to carriers that look real

#python #opensource #webdev #dataengineering

The FMCSA Company Census is a public file of about 4.5 million US motor carriers, updated daily through the Socrata API (dataset az4n-8mr2). It is free. It is also full of junk.

I turned it into a static directory, one page per city, and spent most of the time on the filtering. Here is what I found.

The raw file lies about fleet size

Sort Houston carriers by power_units and the biggest one claims 4,872 trucks while reporting 0 miles. Another Texas carrier claims 100,000 trucks. These are self-reported fields, and nobody validates them.

Rules that fixed most of it:

  • Keep only status_code = 'A' and classdef like '%AUTHORIZED FOR HIRE%'.
  • Keep carriers with 3 or more power units, to drop one-truck operators.
  • Drop fleets of 50+ trucks with mcs150_mileage = 0.
  • Drop rows where power_units is more than 20 times total_drivers.
  • Drop rows where legal_name is an email address (yes, there are some).

Query shape:

where = ("status_code='A' AND classdef like '%AUTHORIZED FOR HIRE%' "
         "AND power_units::number>=3 AND phy_country='US' AND phy_state='TX'")
Enter fullscreen mode Exit fullscreen mode

What I kept out

The file includes email_address and company_officer_1. I did not publish either. Phone, address, DOT number and fleet size are the business listing; the rest is personal data.

Output

Plain Python, no framework. A build script pulls TX, OH and GA and writes about 1,290 static pages: a state page, a page per city with at least 5 carriers, a sitemap, and a short summary and FAQ per city written as plain sentences so search and AI answers can quote them. It deploys as static assets on a Cloudflare Worker. There are 8 tests, mostly on the filters.

Houston ends up with 2,548 carriers running 42,957 trucks, 240 of them hazmat.

Live, free, no signup: https://trucking-directory.agentbets.workers.dev/?utm_source=devto

Questions

  1. If you work with this file: which field do you trust least? I am not confident in total_drivers.
  2. Would you want the filtered list as a CSV or an API by state? There is a one-field form at /export/ if so. I am checking whether anyone does before I build it.

Top comments (0)