Boston Food Establishment Inspections and Active Licenses

Provider
City of Boston (Inspectional Services Department, Health Division)
License
Open Data Commons Public Domain Dedication and License (PDDL) 1.0
Size
2 files
Records
899,708 rows
Stored at
data.boston.gov
Access
Free and public. No account with the data provider is needed.
Category
Public Health
Source
https://data.boston.gov/dataset/food-establishment-inspections

About Boston Food Establishment Inspections and Active Licenses

Twenty years of restaurant and food-establishment health inspections by the City of Boston Inspectional Services Department, from 4 April 2006 to the present: 896,379 rows, paired with a companion resource of 3,329 currently active food establishment licenses. The critical structural fact, and the one that makes most published analyses of this dataset wrong, is that a row is not an inspection. A row is one violation cited at one inspection, so an inspection that cited nine violations appears nine times with identical business, license, result and inspection-time fields, and an inspection that cited none appears once with null violation columns. The 896,379 rows resolve to 217,084 distinct (licenseno, resultdttm) inspections, an average of 4.1 rows per inspection, so counting rows overstates inspection counts fourfold. Each row carries the business name and doing-business-as name, the legal owner and contact name, the license number, category (FS food service, RF retail food, MFW mobile food walk-up, and others) and status, the license issue and expiry timestamps, the inspection result (HE_Fail, HE_Pass, HE_FailExt, HE_Hold and about a dozen more), the inspection timestamp, the violation code against the Boston sanitary code, the violation level (asterisk for non-critical, double asterisk for critical, triple for foodborne-illness-risk), the violation description, the inspector's free-text comment, the violation status and status date, and the address with ZIP, assessing property id and a (latitude, longitude) string. The companion active-licenses resource is a clean current-state snapshot with separate latitude and longitude columns and a cleaned daytime phone number, so it is the right join target for mapping. The paired structure supports work on inspection frequency and equity, repeat-offender identification, critical-violation rates by cuisine and neighborhood, and the relationship between inspection outcomes and license churn. The schema card documents the row grain, the result and violation-level vocabularies, and the fact that the location column is a single parenthesised string rather than two numeric columns. Published by the Inspectional Services Department under PDDL 1.0.

How to load Boston Food Establishment Inspections and Active Licenses in Python

import io, json, urllib.error, urllib.parse, urllib.request
import pandas as pd

CKAN = "https://data.boston.gov/api/3/action"

def ckan(action, **params):
    """Any CKAN action endpoint. Plain urllib works; no auth, no headers."""
    url = f"{CKAN}/{action}?" + urllib.parse.urlencode(params)
    with urllib.request.urlopen(url, timeout=300) as r:
        return json.load(r)["result"]

def sql(query):
    """Server-side SQL over the DataStore. Warns when the 32,000-row cap clips."""
    res = ckan("datastore_search_sql", sql=query)
    if res.get("records_truncated"):
        print("WARNING: clipped at the 32,000-row cap; aggregate or page with OFFSET")
    return pd.DataFrame(res["records"])

INSPECTIONS = "4582bec6-2b4f-4f9e-bc55-cbaa73117f4c"   # 896,379 violation rows

# Count inspections at the correct grain, and the critical-violation rate per
# inspection. count(*) here would count violations and be 4.1x too high.
df = sql(f'''
    SELECT date_part(\'year\', "resultdttm"::timestamp)::int AS yr,
           count(DISTINCT ("licenseno" || \'|\' || "resultdttm")) AS inspections,
           count("violation") AS violations,
           sum(CASE WHEN "viol_level" LIKE \'**%\' THEN 1 ELSE 0 END) AS critical
    FROM "{INSPECTIONS}"
    WHERE "resultdttm" IS NOT NULL AND "resultdttm" <> \'\'
    GROUP BY 1 ORDER BY 1
''')
df["violations_per_inspection"] = df["violations"] / df["inspections"]
print(df.to_string(index=False))

Data structure

Fields

_idint
businessnametext
dbanametext
legalownertext
namelasttext
namefirsttext
licensenotext
issdttmtext
expdttmtext
licstatustext
licensecattext
descripttext
resulttext
resultdttmtext
violationtext
viol_leveltext
violdesctext
violdttmtext
viol_statustext
status_datetext

and 7 more

Files

THE ROW GRAIN IS THE WHOLE STORY. A row is NOT an inspection. A row is one VIOLATION cited at one inspection. An inspection that cited nine violations appears as nine rows with identical business, license, result and resultdttm fields; an inspection that cited none appears once with null violation columns. Measured: 896,379 rows resolve to 217,084 distinct (licenseno, resultdttm) inspections, so counting rows overstates inspections by 4.1x. Nearly every published "Boston has N restaurant inspections" figure derived from this file is that error.
TWO RESOURCES. The inspections and violations history (896,379 rows, verified range 2006-04-04 to 2026-08-05, 27 columns) and a current-state snapshot of active licenses (3,329 rows, 15 columns). The snapshot is the better join target for mapping because it has separate numeric-looking latitude and longitude columns, where the history file only has a single parenthesised "location" string.
CONTROLLED VOCABULARIES you will group on:
  result       HE_Pass, HE_Fail, HE_FailExt, HE_Hold, HE_Filed, HE_NotReq, HE_Closure, ...
  viol_level   "*" non-critical, "**" critical, "***" foodborne-illness risk factor
  viol_status  Fail, Pass
  licensecat   FS food service, RF retail food, MFW mobile food walk-up, FT food truck, ...
  licstatus    Active, Inactive, Deleted
The violation column holds the Boston sanitary code citation ("13-2-304/402.11") and violdesc its short label ("Clean Cloths Hair Restraint"). comments is the inspector's free-text note and is often the only place the specific finding is recorded.
RESOURCES:
  Food Establishment Inspections (data.boston.gov/dataset/food-establishment-inspections): 1 DataStore resources of 1 total
    4582bec6-2b4f-4f9e-bc55-cbaa73117f4c     896379 rows  Food Establishment Inspections
  Active Food Establishment Licenses (data.boston.gov/dataset/active-food-establishment-licenses): 1 DataStore resources of 1 total
    f1e13724-284d-478c-b8bc-ef042aa5b70b       3329 rows  Active Food Establishment Licenses

Example file: Critical-violation rate per inspection by license category, counted at the right grain. sum(CASE WHEN ...) rather than count(*) FILTER (WHERE ...), which this portal 403s: SELECT "licensecat", count(DISTINCT ("licenseno" || '|' || "resultdttm")) AS inspections, sum(CASE WHEN "vio

Sample notebook

Sample notebook for Boston Food Establishment Inspections and Active Licenses: worked code with the output it printed.

Analyze this dataset without downloading it

On Scigantic, Boston Food Establishment Inspections and Active Licenses opens in a hosted JupyterLab notebook with the data already mounted, so you can read files where they are stored instead of copying them first. Each notebook includes an AI assistant that can see the mounted files and write and run analysis code. Open the dataset page to launch a notebook, or browse the full catalog.

This page is also available as Markdown and as JSON.

Related Public Health datasets