Cleaning 17,545 Historical Menus Without Erasing the Evidence

The project question sounded simple: count historical menus by city, decade, and meal type. The raw NYPL menu table made every part of that question unstable. Place values mixed cities, addresses, hotels, ships, and routes; dates contained missing and impossible years; event labels used many languages and spellings.

The goal was not to make every field look tidy. It was to make one analysis question answerable while preserving uncertainty and source evidence.

Define the output before the cleaning rules

The 17,545 input rows were preserved. Raw columns remained untouched; fourteen derived fields recorded normalized values and provenance, including:

canonical_city, city_source, city_rule, is_transit
menu_year, menu_decade, date_status, date_rule
canonical_event, event_source, event_rule

This made the cleaned dataset (D’) an extension of (D), not a replacement pretending the original ambiguity never existed.

The pipeline had two levels

The outer workflow profiled, cleaned, validated, audited inference, and packaged evidence. The inner workflow processed each row through nine conceptual stages:

1. normalize text;
2. classify ship, train, and en-route records;
3. extract a city candidate from place;
4. tally location evidence and veto ambiguous venue names;
5. apply a curated gazetteer and learned crosswalk;
6. resolve the final city by fixed precedence;
7. parse and repair dates;
8. map event text into meal types;
9. emit provenance and a change log.

One step required dataset-level evidence: a hotel name seen in several cities could not safely imply one city. Row-by-row code alone could not discover that ambiguity.

In plain terms: a value can look obvious in one row and become unsafe after seeing the rest of the table.

Canonicalization changed the analytical grain

The pipeline reduced 3,708 distinct raw place strings to 233 canonical cities and 1,769 event strings to eight meal types. DINNER alone absorbed 736 raw variants.

Before cleaning, the analysis returned 7,559 menus across 5,049 groups. Ships dominated the largest groups, and synonymous meal labels fragmented counts. After cleaning, it returned 6,268 attributable menus across 757 groups.

The lower row count was intentional:

7,559 raw answer rows
- 2,858 transit menus
- 1,259 non-city or unresolved place values
+ 2,825 cities recovered from location
+     1 date repaired into range
= 6,268 cleaned answer rows

Cleaning did not maximize coverage. It changed the population from “rows with something in place” to “menus attributable to a city.”

Inference was measured, not declared correct

When place was unusable, location could sometimes recover a city through a curated venue gazetteer or a learned majority crosswalk. The learned mapping required support of at least three and confidence of at least 0.75; generic venue names that mapped to several cities were vetoed.

A held-out check used rows where both signals were available:

Inference path	Rows checked	Agreement
Curated gazetteer	381	99.7%
Learned crosswalk, leave-one-out	455	93.6%
Combined	836	96.4%

The caveat matters: these test rows had a parseable place and may be easier than rows requiring inference. The measured agreement is evidence, not a guarantee for the target population.

Constraints protected the interpretation

Eight denial-style constraints checked transit values used as cities, impossible dates, missing IDs, meal vocabulary, non-city place values, and provenance for derived values. All post-cleaning counts were zero.

Missing dates were reported separately from invalid dates. There were 586 missing dates but only six present dates outside the accepted range before cleaning. Combining them would have produced a more dramatic but less honest improvement.

What the project taught me

1. Preserve raw fields and put interpretations in derived columns.
2. Profile globally before trusting a local pattern.
3. Treat controlled vocabularies as explicit analytical decisions.
4. Reconcile every row entering or leaving an answer set.
5. Measure inference on held-out evidence and state sample bias.
6. Make rules, thresholds, tests, and provenance part of the deliverable.

The finished pipeline used standard-library Python and SQLite, with 44 unit tests and machine-readable audit reports. Tool choice mattered less than making every cleaning decision inspectable and rerunnable.

Review card

• Start from the analytical question and its required grain.
• Preserve raw data; add canonical values and provenance.
• Dataset-level ambiguity can defeat row-local rules.
• Better quality may legitimately reduce coverage.
• Reconcile before-and-after populations rather than comparing only totals.
• An accuracy percentage needs a sampling caveat.

canonical_city, city_source, city_rule, is_transit
menu_year, menu_decade, date_status, date_rule
canonical_event, event_source, event_rule

7,559 raw answer rows
- 2,858 transit menus
- 1,259 non-city or unresolved place values
+ 2,825 cities recovered from location
+     1 date repaired into range
= 6,268 cleaned answer rows