#!/usr/bin/env python3 """Normalize NYC DOHMH MenuStat (Historical) -> FoodIndex/normalized/extra_M.jsonl Source: NYC Open Data dataset qgc5-ecnb "DOHMH MenuStat (Historical)" (data years 2017 and 2018), https://data.cityofnewyork.us/Health/DOHMH-MenuStat-Historical-/qgc5-ecnb Licence: NYC public data set, NYC Admin. Code 23-502(d): no registration, licence or use restrictions; the City may require third parties to name the source and version and describe modifications. Rules (accuracy first; a doubtful value means the item is dropped): * One record per Menu_Item_ID, and only items on the newest (2018) menu with calories stated in 2018. 2017-only items are dropped as stale (discontinued or re-coded under a new id). The serving amount comes from 2018; if 2018 has none, the 2017 amount of the same item id is used only when the 2017 calories are identical to 2018 (same item); otherwise the item is dropped. * Non-beverage items whose serving in oz equals an "N oz" in the item name are dropped ("12 oz New York Strip", "Pork Chops & Eggs, 4 oz"): that is the advertised (often raw) weight of one component, not the weight served, so kcal/100 g would be wrong. * Serving amount must be stated by the source: g -> g, ml -> ml, fl oz -> ml (US fl oz = 29.5735295625 ml), oz -> g (avoirdupois oz = 28.349523125 g). "oz" is NOT accepted for Beverages (the source uses it for fluid ounces there, e.g. "Wine, 6 oz"), so those are dropped. Items without a serving amount are skipped: kcal per 100 g cannot be expressed without it, and weights are never guessed. * Calorie ranges / text values, limited-time offers and items with broken names are dropped. * Restaurant calories are rounded (FDA: <5 -> 0, <=50 to nearest 5, >50 to nearest 10). When that rounding error alone could move kcal/100 by more than max(5, 10 % of the value), the item is dropped (typical case: a 5 g pizza topping declared as "0" or "5" kcal). * kcal/100 must be 0..900 (g) and 0..450 (ml); non-beverage servings over 2 kg are dropped as implausible. * Cross-checks with the item's own macros (when present): protein+carbs+fat may not weigh more than the serving, and calories must agree with 4/4/9 Atwater energy within 20 % + 20 kcal (fibre-adjusted; beverages are only checked from below because alcohol adds energy). Failing items are dropped. * Portion label: the source's household serving when it starts with a count ("1 slice", "1/2 cup"), otherwise "1 serving" (plus the stated fl oz / oz size). The amount is the source's serving amount. Usage: python3 -I normalize_extra_M.py """ import csv import json import re import sys import unicodedata from collections import Counter, defaultdict SRC = "M" DATASET_DATE = "2019-02-04" # NYC Open Data rowsUpdatedAt of qgc5-ecnb (epoch 1549293332) FLOZ_ML = 29.5735295625 LATEST_YEAR = "2018" # newest data year in the dataset; older-only items are treated as stale OZ_G = 28.349523125 BRAND_FIX = {"Dominos": "Domino's", "Ci Ci's Pizza": "CiCi's Pizza"} def clean_text(s: str) -> str: s = unicodedata.normalize("NFC", s or "") s = s.replace("31░ Below", "31° Below").replace("31� Below", "31° Below") s = re.sub(r"\s+", " ", s).strip() return s def num(s): s = (s or "").strip() if not s: return None try: v = float(s) except ValueError: return None if v != v or v in (float("inf"), float("-inf")): return None return v def rounding_error(cal: float) -> float: """Max absolute error (kcal) of an FDA-rounded calorie declaration.""" if cal < 5: return 5.0 if cal <= 50: return 2.5 return 5.0 def portion_label(household: str) -> str: h = clean_text(household) # Only use household text that starts with a count/fraction and has no weight/volume unit in it. if h and re.match(r"^\d+(/\d+)?\s+[A-Za-z]", h) and not re.search(r"\b(oz|g|ml|fl|lb|grams?)\b", h, re.I): parts = [p.strip() for p in h.split(";") if p.strip()] label = parts[0].lower() if len(parts) > 1: label += " (" + "; ".join(p.lower() for p in parts[1:]) + ")" if len(label) <= 40: return label return "1 serving" def main() -> None: src_csv, out_path = sys.argv[1], sys.argv[2] with open(src_csv, newline="", encoding="utf-8") as f: rows = list(csv.DictReader(f)) by_id = defaultdict(dict) for r in rows: by_id[r["Menu_Item_ID"].strip()][r["Year"].strip()] = r drops = Counter() candidates = [] def is_usable(r) -> bool: return ( num(r["Calories"]) is not None and not r["Calories_text"].strip() and not r["Serving_Size_text"].strip() and num(r["Serving_Size"]) is not None and r["Serving_Size_Unit"].strip() in ("g", "ml", "fl oz", "oz") ) for mid, years in by_id.items(): ys = sorted(years, reverse=True) if any(years[y]["Limited_Time_Offer"].strip() == "1" for y in ys): drops["limited_time_offer"] += 1 continue if LATEST_YEAR not in years: drops["not_on_latest_(%s)_menu" % LATEST_YEAR] += 1 continue if num(years[LATEST_YEAR]["Calories"]) is None: drops["no_calories_in_latest_year"] += 1 continue usable = next((years[y] for y in ys if is_usable(years[y])), None) if usable is None: drops["no_serving_amount"] += 1 continue ucal = num(usable["Calories"]) stale = False for y in ys: if y <= usable["Year"].strip(): break newer = years[y] ncal = num(newer["Calories"]) # A newer year that states different calories (or a range) makes the older record stale. if newer["Calories_text"].strip() or (ncal is not None and abs(ncal - ucal) > 0.01): stale = True if stale: drops["stale(newer year has different calories but no serving amount)"] += 1 continue candidates.append(usable) n_ids = len(by_id) out = [] seen_keys = {} for r in candidates: mid = r["Menu_Item_ID"].strip() brand_raw = clean_text(r["restaurant"]) brand = BRAND_FIX.get(brand_raw, brand_raw) name = clean_text(r["Item_Name"]) if "�" in name or "░" in name: drops["broken_characters_in_name"] += 1 continue if len(name) < 2: drops["name_too_short"] += 1 continue cat = clean_text(r["Food_Category"]) cal = num(r["Calories"]) size = num(r["Serving_Size"]) su = r["Serving_Size_Unit"].strip() if cal is None or cal < 0: drops["bad_calories"] += 1 continue if size is None or size <= 0: drops["bad_serving_amount"] += 1 continue if su == "g": unit, amount = "g", size elif su == "ml": unit, amount = "ml", size elif su == "fl oz": if cat != "Beverages" and size > 8: # a fluid-ounce serving bigger than a cup outside beverages ("Pepperjack Cheese, 30 fl oz" for 80 kcal) # is a unit error in the source: kcal/100 ml would be several times too low drops["fl_oz_serving_over_8_outside_beverages"] += 1 continue unit, amount = "ml", size * FLOZ_ML elif su == "oz": if cat == "Beverages": drops["oz_for_beverage(ambiguous weight vs fluid oz)"] += 1 continue unit, amount = "g", size * OZ_G else: drops["unknown_unit"] += 1 continue if su in ("oz", "g") and cat != "Beverages": # e.g. "12 oz New York Strip" or "Pork Chops & Eggs, 4 oz": the stated serving is the advertised # (often raw) weight of one component, not the weight of what is served -> kcal/100 g is doubtful. if re.search(r"(? 2000: drops["implausible_serving_weight(>2 kg, not a beverage)"] += 1 continue # Consistency checks against the item's own macronutrients (source values, per serving). P, C, F, FIB = num(r["Protein"]), num(r["Carbohydrates"]), num(r["Total_Fat"]), num(r["Dietary_Fiber"]) if None not in (P, C, F): if P + C + F > amount * (1.05 if unit == "g" else 1.10) + 1: drops["macros_heavier_than_serving(wrong serving amount)"] += 1 continue at_hi = 4 * P + 4 * C + 9 * F at_lo = at_hi - 2 * (FIB or 0.0) # US carbs include fibre (~2 kcal/g) if cal < at_lo * 0.80 - 20: drops["calories_below_macros(atwater)"] += 1 continue if cat != "Beverages" and cal > at_hi * 1.20 + 20: # beverages may contain alcohol drops["calories_above_macros(atwater)"] += 1 continue if amount > 5000: drops["serving_over_5000"] += 1 continue kcal = cal / amount * 100.0 err = rounding_error(cal) / amount * 100.0 if err > max(5.0, 0.10 * kcal): drops["imprecise(rounded calories on a small serving)"] += 1 continue limit = 900.0 if unit == "g" else 450.0 if kcal > limit: drops["implausible_kcal_per_100"] += 1 continue amount_r = round(amount, 1) kcal_r = round(kcal, 1) label = portion_label(r["Serving_Size_household"]) if label == "1 serving" and su in ("fl oz", "oz"): label = "1 serving (%g %s)" % (size, su) if len(name) > 120: name = name[:120].rsplit(" ", 1)[0].rstrip(",;- ") key = (brand.lower(), name.lower()) rec = { "id": f"{SRC}:{mid}", "name": name, "brand": brand, "kcal": kcal_r, "unit": unit, "portions": [[label, amount_r]], "barcode": "", "src": SRC, "generic": False, "pop": None, "date": DATASET_DATE, "countries": ["us"], "lang": "en", "category": cat, "_year": r["Year"].strip(), "_su": su, "_size": size, } seen_keys.setdefault(key, []).append(rec) # Same brand + same name: if the serving differs, make the names distinct with the source's serving size; # if everything is identical, keep one (lowest id). for key, recs in seen_keys.items(): if len(recs) == 1: out.append(recs[0]) continue uniq = {} for rec in sorted(recs, key=lambda x: int(x["id"].split(":")[1]) if x["id"].split(":")[1].isdigit() else 0): sig = (rec["kcal"], rec["unit"], rec["portions"][0][1]) if sig in uniq: drops["duplicate_same_values"] += 1 continue uniq[sig] = rec recs2 = list(uniq.values()) if len(recs2) == 1: out.append(recs2[0]) continue sizes = Counter((r_["_size"], r_["_su"]) for r_ in recs2) for rec in recs2: s = rec["_size"] s_txt = ("%g" % s) + " " + rec["_su"] if sizes[(rec["_size"], rec["_su"])] > 1: # same size but different calories: ambiguous duplicate -> drop all of that size drops["conflicting_duplicates(same name+size, different kcal)"] += 1 continue if s_txt.lower() not in rec["name"].lower(): rec["name"] = (rec["name"] + ", " + s_txt)[:120] out.append(rec) # Beverage sizes of one drink at one chain ("Sprite, Small/Medium/Large"): kcal per 100 ml should agree across # sizes. An item more than 2x away from the median of its own sizes is a data error ("Sprite, Large, 44 oz" at # 1.2 kcal/100 ml while the other sizes are 32-42): drop it. size_re = re.compile(r"(?i),?\s*\b(small|medium|large|regular|kids?|child|junior|jr\.?|value|x-?large|" r"extra large|jumbo|mini|mega|super|xl|lg|sm|md|\d+(\.\d+)?\s*(fl\.? ?)?oz\.?|" r"\d+\s*ml|\d+\s*ounce)\b") groups = defaultdict(list) for rec in out: if rec["unit"] == "ml" and rec["category"] == "Beverages": base = re.sub(r"\s+", " ", size_re.sub(" ", rec["name"])).strip(" ,-").lower() groups[(rec["brand"].lower(), base)].append(rec) bad_ids = set() for key, recs in groups.items(): if len(recs) < 3: continue vals = sorted(x["kcal"] for x in recs) med = vals[len(vals) // 2] if len(vals) % 2 else (vals[len(vals) // 2 - 1] + vals[len(vals) // 2]) / 2 if med < 10: continue for x in recs: if x["kcal"] > 2 * med or x["kcal"] < med / 2: bad_ids.add(x["id"]) if bad_ids: drops["beverage_size_inconsistent_with_other_sizes"] += len(bad_ids) out = [x for x in out if x["id"] not in bad_ids] out.sort(key=lambda x: (x["brand"].lower(), x["name"].lower(), x["id"])) years = Counter(x["_year"] for x in out) units = Counter(x["_su"] for x in out) with open(out_path, "w", encoding="utf-8") as f: for rec in out: for k in ("_year", "_su", "_size"): rec.pop(k, None) f.write(json.dumps(rec, ensure_ascii=False, separators=(",", ":")) + "\n") print("menu item ids:", n_ids) print("records with a usable year:", len(candidates)) print("written:", len(out), "->", out_path) print("by data year:", dict(years)) print("by source serving unit:", dict(units)) print("brands:", len({x['brand'] for x in out})) print("dropped:", dict(drops)) if __name__ == "__main__": main()