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
_id | int |
businessname | text |
dbaname | text |
legalowner | text |
namelast | text |
namefirst | text |
licenseno | text |
issdttm | text |
expdttm | text |
licstatus | text |
licensecat | text |
descript | text |
result | text |
resultdttm | text |
violation | text |
viol_level | text |
violdesc | text |
violdttm | text |
viol_status | text |
status_date | text |
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 LicensesExample 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.