#!/usr/bin/env python3 """Independent accuracy audit of the built food index (site + bundle). Fresh code: does NOT import any pipeline module. Re-derives sampled records from the raw downloads. Subcommands (all write to FoodIndex/qa/accuracy/): scan read every site shard once: hash-sample records per source, plus global unit / portion / title-case checks derive re-derive the sampled records from the raw files (USDA F/S/N/B, OFF, MenuStat, CoFID, CIQUAL) known known-food sanity checks via an independent implementation of the contract's search Usage: python3 -I qa_accuracy.py scan|derive|known [--only SRC,...] """ import collections import csv import gzip import hashlib import json import math import os import random import re import sys import unicodedata import xml.etree.ElementTree as ET import zipfile ROOT = "/FoodIndex" SITE = os.path.join(ROOT, "out", "site") DL = os.path.join(ROOT, "downloads") OUT = os.path.join(ROOT, "qa", "accuracy") BUNDLE = "/LosingIt/Resources/GenericFoods.json" TARGET_PER_SRC = 400 FLOZ = 29.5735295625 OZ = 28.349523125 csv.field_size_limit(1 << 30) def jdump(path, obj): with open(path, "w", encoding="utf-8") as f: json.dump(obj, f, ensure_ascii=False, indent=1) def manifest(): with open(os.path.join(SITE, "manifest.json"), encoding="utf-8") as f: return json.load(f) def hfrac(s): return int(hashlib.md5(s.encode("utf-8")).hexdigest()[:13], 16) / float(16 ** 13) # ---------------------------------------------------------------- independent normalisation helpers def my_barcode(s): d = "".join(ch for ch in (s or "") if ch.isdigit() and ch in "0123456789") if len(d) == 12: d = "0" + d elif len(d) == 14 and d[0] == "0": d = d[1:] if 6 <= len(d) <= 14: return d return "" STOP = set("and with of the in an for or to on de la le et en con mit und from".split()) def tokenize(s): s = unicodedata.normalize("NFKD", s) s = "".join(c for c in s if unicodedata.category(c) != "Mn") s = s.lower() toks, cur = [], [] for c in s: if unicodedata.category(c)[0] in "LN": cur.append(c) else: if cur: toks.append("".join(cur)) cur = [] if cur: toks.append("".join(cur)) out = [] for t in toks: if len(t) < 2: continue if len(t) >= 4 and re.fullmatch(r"[a-z]+", t): if t.endswith("ies"): t = t[:-3] + "y" elif t.endswith("sses"): t = t[:-2] elif t.endswith(("oes", "ches", "shes", "xes", "zes")): t = t[:-2] elif t.endswith("s") and not t.endswith(("ss", "us", "is")): t = t[:-1] if t in STOP: continue if t not in out: out.append(t) return out def fnv(s): h = 0x811C9DC5 for b in s.encode("utf-8"): h = ((h ^ b) * 0x01000193) & 0xFFFFFFFF return h # ---------------------------------------------------------------- portion label sanity (unit words) NUMW = r"(\d+\s+\d+/\d+|\d+/\d+|\d*\.\d+|\d+)" def parse_qty(s): s = s.strip() m = re.fullmatch(r"(\d+)\s+(\d+)/(\d+)", s) if m: return int(m.group(1)) + int(m.group(2)) / int(m.group(3)) m = re.fullmatch(r"(\d+)/(\d+)", s) if m: return int(m.group(1)) / int(m.group(2)) if int(m.group(2)) else None try: return float(s) except ValueError: return None # per-1-unit plausible range in g (or ml) for unit-g records, and exact for ml records VOL_UNITS = { # name regex -> ml per unit r"cups?|c\.?": 236.6, r"tbsps?|tablespoons?|tbs|tbl": 14.79, r"tsps?|teaspoons?": 4.93, r"fl\.? ?oz|fluid ounces?|floz": FLOZ, r"pints?|pt": 473.2, r"quarts?|qt": 946.4, r"gallons?|gal": 3785.4, r"liters?|litres?|l": 1000.0, r"ml|milliliters?|millilitres?|mls": 1.0, r"cl": 10.0, r"dl": 100.0, } MASS_UNITS = {r"oz|ounces?|onz": OZ, r"lbs?|pounds?": 453.59, r"g|grams?|gr|gm|grm": 1.0, r"kg|kilograms?": 1000.0, r"mg": 0.001} def label_unit_check(label, amount, unit): """Return (kind, expected_per_unit, ratio) when the label states a measurable unit, else None.""" low = label.lower().strip() m = re.match(NUMW + r"\s*(.*)$", low) if not m: return None q = parse_qty(m.group(1)) rest = m.group(2).strip() if not q or q <= 0: return None for pat, mlper in MASS_UNITS.items(): if re.match(r"(%s)(?![a-z])" % pat, rest): if re.match(r"(oz|ounces?)\b", rest) and re.match(r"(fl)", rest): continue exp = q * mlper return ("mass", exp, amount / exp) for pat, mlper in VOL_UNITS.items(): if re.match(r"(%s)(?![a-z])" % pat, rest): exp = q * mlper return ("vol", exp, amount / exp) return None METRIC_IN_LABEL = re.compile(r"(? 450: add(kcal_issues, "ml_over_450", r) # unit sanity low = name.lower() if unit == "g" and BEV_NAME.search(low) and not BEV_NEG.search(low): st["unit_bev_named_as_g_" + src] += 1 add(unit_bev_g, src, r, 80) if unit == "ml" and SOLID_NAME.search(low) and not SOLID_NEG.search(low): st["unit_solid_named_as_ml_" + src] += 1 add(unit_solid_ml, src, r, 80) # portion sanity if len(portions) > 6: add(portion_issues, "more_than_6", r) labels = set() for p in portions: lab, amt = p if lab.lower() in labels: add(portion_issues, "duplicate_label", r) labels.add(lab.lower()) if not (0 < amt <= 5000): add(portion_issues, "amount_out_of_range", r) if abs(amt - 100) < 0.05 and lab.lower() in ("1 serving", "100 g", "100 ml", "100g", "100ml"): add(portion_issues, "plain_100", r) if METRIC_IN_LABEL.search(lab): st["portion_label_metric_" + src] += 1 mm = METRIC_IN_LABEL.search(lab) add(portion_issues, "label_has_metric_amount_" + src, [lab, amt, rid, name]) chk = label_unit_check(lab, amt, unit) if chk: kind2, exp, ratio = chk st["portion_unit_checked"] += 1 if kind2 == "mass": bad = not (0.93 <= ratio <= 1.07) if unit == "g" else not (0.6 <= ratio <= 1.6) else: if unit == "ml": bad = not (0.9 <= ratio <= 1.1) else: # volume label, weight amount: density 0.08 (puffed cereal) .. 1.8 (honey/salt) bad = not (0.08 <= ratio <= 1.9) if bad: st["portion_unit_contradiction_" + src] += 1 add(portion_issues, "label_unit_contradicts_amount_" + src, [lab, amt, unit, round(exp, 1), round(ratio, 2), rid, name, kcal], 150) if amt > 2000 and unit == "g": add(portion_issues, "portion_over_2kg_" + src, [lab, amt, rid, name]) if re.fullmatch(r"1 (cup|tbsp|tsp)\b.*", lab.lower()) and kcal * amt / 100 > 2500: add(portion_issues, "portion_energy_over_2500kcal", [lab, amt, rid, name, kcal]) # title-case ugliness, branded/USDA names if src in ("B", "S", "N", "F", "M"): for tag, rx in UGLY: if rx.search(name): st["title_%s_%s" % (tag, src)] += 1 add(title_issues, "%s_%s" % (tag, src), [rid, name, brand], 40) # finalize sample: deterministic random subset of TARGET_PER_SRC rnd = random.Random(20261115) final = {} for s, d in sample.items(): ids = sorted(d) rnd.shuffle(ids) final[s] = [d[i] for i in ids[:TARGET_PER_SRC]] st["sampled_" + s] = len(final[s]) only_barcode = in_barcode_shard - tok_ids st["records_only_in_barcode_shards"] = len(only_barcode) st["records_total_unique"] = len(seen) jdump(os.path.join(OUT, "sample.json"), final) jdump(os.path.join(OUT, "scan.json"), { "stats": dict(sorted(st.items())), "src_unit": {"%s:%s" % k: v for k, v in sorted(src_unit.items())}, "barcode_key_checks": dict(bc_key_vs_rec), "barcode_key_mismatch_examples": bc_mismatch, "kcal_issues": kcal_issues, "unit_bev_named_as_g": unit_bev_g, "unit_solid_named_as_ml": unit_solid_ml, "portion_issues": portion_issues, "title_issues": title_issues}) print(json.dumps(dict(sorted(st.items())), indent=1)) # ================================================================ derive def load_sample(): with open(os.path.join(OUT, "sample.json"), encoding="utf-8") as f: return json.load(f) def close(a, b, tol=0.051): return a is not None and b is not None and abs(a - b) <= tol def r1(x): return None if x is None else round(x + 1e-9, 1) class Report: def __init__(self, src): self.src = src self.rows = [] self.stats = collections.Counter() def check(self, rec, field, site, raw, ok, note=""): self.stats[field + ("_ok" if ok else "_MISMATCH")] += 1 if not ok: self.rows.append({"id": rec[0], "name": rec[1], "brand": rec[2], "field": field, "site": site, "raw": raw, "note": note}) def info(self, rec, field, detail): self.stats[field] += 1 self.rows.append({"id": rec[0], "name": rec[1], "brand": rec[2], "field": field, "info": detail}) def portions_vs_raw(rep, rec, raw_portions, unit_factor=1.0, borrowed=None): """raw_portions: list of (label_text, grams). Check every site portion appears in raw (amount within 0.06 or 0.5 %).""" site = rec[5] for lab, amt in site: hits = [rp for rp in raw_portions if rp[1] is not None and abs(rp[1] * unit_factor - amt) <= max(0.06, 0.005 * amt)] if hits: rep.stats["portion_amount_ok"] += 1 # label sanity: the label's leading number should equal the raw portion's stated number (if any) lm = re.match(NUMW, lab) okl = False for rl, _ in hits: rlm = re.match(NUMW, rl or "") if not lm or not rlm: okl = True break a, b = parse_qty(lm.group(1)), parse_qty(rlm.group(1)) if a is not None and b is not None and abs(a - b) < 0.02: okl = True break if not okl: rep.check(rec, "portion_label_number", [lab, amt], [h[0] for h in hits], False) continue if borrowed is not None: bh = [bp for bp in borrowed if abs(bp[1] - amt) <= max(0.06, 0.005 * amt)] if bh: rep.stats["portion_borrowed_from_same_name_food"] += 1 continue rep.check(rec, "portion_amount", [lab, amt], raw_portions[:8], False, "site portion amount not in raw") if not site and raw_portions: rep.stats["site_no_portions_raw_has"] += 1 # ---------------------------------------------------------------- USDA generic CSV readers def usda_dir(sub, pat): d = os.path.join(DL, "usda", sub) for x in sorted(os.listdir(d)): if x.startswith(pat) and os.path.isdir(os.path.join(d, x)): return os.path.join(d, x) raise SystemExit("no dir " + sub) def read_csv(path): with open(path, encoding="utf-8", newline="") as f: yield from csv.DictReader(f) def energy_ids(d): by_id, by_nbr = {}, {} for r in read_csv(os.path.join(d, "nutrient.csv")): by_id[r["id"]] = (r["name"], r["unit_name"]) if r.get("nutrient_nbr"): nb = r["nutrient_nbr"].split(".")[0] by_nbr[nb] = r["id"] return by_id, by_nbr def usda_generic_raw(src, d, ids): """Return {fdc_id: {'desc','kcal':{nid:amt}, 'portions':[(label, g)], 'all_desc'}} for wanted ids, plus desc map.""" ids = set(ids) by_id, by_nbr = energy_ids(d) want = {"1008", "2047", "2048", "1062"} nbr2id = {"208": "1008", "268": "1062", "957": "2047", "958": "2048"} nutr = collections.defaultdict(dict) for r in read_csv(os.path.join(d, "food_nutrient.csv")): if r["fdc_id"] not in ids: continue nid = r["nutrient_id"] if src == "N" and nid in nbr2id: nid = nbr2id[nid] if nid in want and r["amount"].strip(): nutr[r["fdc_id"]][nid] = float(r["amount"]) mu = {r["id"]: r["name"] for r in read_csv(os.path.join(d, "measure_unit.csv"))} por = collections.defaultdict(list) for r in read_csv(os.path.join(d, "food_portion.csv")): gw = float(r["gram_weight"]) if r["gram_weight"].strip() else None un = mu.get(r["measure_unit_id"], "?") lab = " ".join(x for x in (r["amount"].strip(), "" if un == "undetermined" else un, r["modifier"].strip() if src == "S" else "", r["portion_description"].strip()) if x) por[r["fdc_id"]].append((lab, gw)) foods = {} desc_index = collections.defaultdict(list) for r in read_csv(os.path.join(d, "food.csv")): desc_index[r["description"].strip().lower()].append(r["fdc_id"]) if r["fdc_id"] in ids: foods[r["fdc_id"]] = {"desc": r["description"], "dt": r["data_type"]} return foods, nutr, por, desc_index def pick_kcal(n): for k in ("1008", "2048", "2047"): if k in n: return n[k], k if "1062" in n: return n["1062"] / 4.184, "1062" return None, None def derive_usda_generic(sample, reps): # Raw portions of every USDA generic food, keyed by lower-cased description, for the borrowed-portion check allpor = collections.defaultdict(list) raw = {} # S and N for src, sub, pat in (("S", "sr_legacy", "FoodData_Central_sr_legacy"), ("N", "fndds", "FoodData_Central_survey")): d = usda_dir(sub, pat) ids = [r[0][2:] for r in sample.get(src, [])] foods, nutr, por, desc_index = usda_generic_raw(src, d, ids + []) raw[src] = (foods, nutr, por) # borrowed-portion universe (same description) fdesc = {} for r in read_csv(os.path.join(d, "food.csv")): fdesc[r["fdc_id"]] = r["description"].strip().lower() mu = {r["id"]: r["name"] for r in read_csv(os.path.join(d, "measure_unit.csv"))} for r in read_csv(os.path.join(d, "food_portion.csv")): if r["gram_weight"].strip(): allpor[fdesc.get(r["fdc_id"], "")].append((r["portion_description"] or r["modifier"], float(r["gram_weight"]))) # F from JSON jd = os.path.join(DL, "usda", "foundation_json") jp = [os.path.join(jd, x) for x in os.listdir(jd) if x.endswith(".json")][0] with open(jp, encoding="utf-8") as f: fj = json.load(f)["FoundationFoods"] ffoods, fnutr, fpor = {}, collections.defaultdict(dict), collections.defaultdict(list) for food in fj: fid = str(food["fdcId"]) ffoods[fid] = {"desc": food["description"], "dt": food.get("dataType")} for fn in food.get("foodNutrients", []): nu = fn.get("nutrient") or {} nid = str(nu.get("id")) if nid in ("1008", "2047", "2048", "1062") and fn.get("amount") is not None: fnutr[fid][nid] = float(fn["amount"]) for p in food.get("foodPortions", []): mun = ((p.get("measureUnit") or {}).get("name") or "") lab = " ".join(str(x) for x in (p.get("amount", ""), "" if mun == "undetermined" else mun, p.get("modifier") or "", p.get("portionDescription") or "") if x != "") gw = p.get("gramWeight") fpor[fid].append((lab, float(gw) if gw is not None else None)) allpor[food["description"].strip().lower()].append((lab, float(gw) if gw is not None else None)) raw["F"] = (ffoods, fnutr, fpor) for src in ("F", "S", "N"): rep = reps[src] foods, nutr, por = raw[src] for rec in sample.get(src, []): fid = rec[0][2:] if fid not in foods: rep.check(rec, "exists_in_raw", True, False, False) continue fd = foods[fid] kc, path = pick_kcal(nutr.get(fid, {})) rep.stats["energy_path_" + str(path)] += 1 rep.check(rec, "kcal", rec[3], r1(kc), close(rec[3], r1(kc)), "energy nutrient %s" % path) n = nutr.get(fid, {}) if path != "1008" and "2047" in n and "2048" in n and abs(n["2047"] - n["2048"]) > 0.05 * max(n["2047"], n["2048"]): rep.info(rec, "foundation_2047_vs_2048_differ_gt5pct", {"2047": n["2047"], "2048": n["2048"]}) rep.check(rec, "unit", rec[4], "g", rec[4] == "g") rep.check(rec, "barcode", rec[6], "", rec[6] == "") nm_ok = re.sub(r"\s+", " ", fd["desc"]).strip() == rec[1] if not nm_ok: rep.info(rec, "name_changed", {"raw": fd["desc"], "site": rec[1]}) borrowed = allpor.get(fd["desc"].strip().lower(), []) portions_vs_raw(rep, rec, [p for p in por.get(fid, []) if p[1]], borrowed=borrowed) # ---------------------------------------------------------------- USDA branded def derive_branded(sample, rep): d = usda_dir("branded", "FoodData_Central_branded_food_csv") recs = sample.get("B", []) want = {r[0][2:] for r in recs} want_bc = {r[6] for r in recs} bf = {} by_bc = collections.defaultdict(list) # all rows sharing a sampled GTIN print("B: branded_food.csv", file=sys.stderr) for r in read_csv(os.path.join(d, "branded_food.csv")): nb = my_barcode(r["gtin_upc"]) if r["fdc_id"] in want: bf[r["fdc_id"]] = r if nb in want_bc: by_bc[nb].append(r) other_ids = {r["fdc_id"] for rows in by_bc.values() for r in rows} food = {} print("B: food.csv", file=sys.stderr) with open(os.path.join(d, "food.csv"), encoding="utf-8", newline="") as f: rd = csv.reader(f) next(rd) for row in rd: if row[0] in other_ids or row[0] in want: food[row[0]] = {"desc": row[2], "pub": row[4]} print("B: food_nutrient.csv", file=sys.stderr) nutr = collections.defaultdict(dict) allids = want | other_ids with open(os.path.join(d, "food_nutrient.csv"), encoding="utf-8", newline="") as f: f.readline() for line in f: # "id","fdc_id","nutrient_id","amount",... p = line.split(",", 4) fid = p[1].strip('"') if fid not in allids: continue nid = p[2].strip('"') if nid in ("1008", "2047", "2048", "1062"): a = p[3].strip('"') if a: nutr[fid][nid] = float(a) for rec in recs: fid = rec[0][2:] r = bf.get(fid) if r is None: rep.check(rec, "exists_in_raw", True, False, False) continue kc, path = pick_kcal(nutr.get(fid, {})) rep.stats["energy_path_" + str(path)] += 1 rep.check(rec, "kcal", rec[3], r1(kc), close(rec[3], r1(kc)), "nutrient %s" % path) su = r["serving_size_unit"].strip() unit = {"g": "g", "GRM": "g", "ml": "ml", "MLT": "ml"}.get(su) rep.check(rec, "unit", rec[4], unit, rec[4] == unit, "serving_size_unit=%s" % su) bc = my_barcode(r["gtin_upc"]) rep.check(rec, "barcode", rec[6], bc, rec[6] == bc, "gtin_upc=%s" % r["gtin_upc"]) if len(bc) not in (8, 13): rep.info(rec, "barcode_unscannable_length_%d" % len(bc), {"gtin_upc": r["gtin_upc"], "site": rec[6]}) # portion try: ss = float(r["serving_size"]) except ValueError: ss = None if rec[5]: lab, amt = rec[5][0] rep.check(rec, "portion_amount", amt, r1(ss), ss is not None and abs(round(ss, 1) - amt) <= 0.06, "household=%r" % r["household_serving_fulltext"]) hh = r["household_serving_fulltext"].lower() lm = re.match(NUMW, lab) if lab != "1 serving" and lm: q = parse_qty(lm.group(1)) hm = re.search(NUMW, hh.replace("½", " 1/2").replace("¼", " 1/4").replace("¾", " 3/4")) if hm and q is not None and parse_qty(hm.group(1)) is not None and abs(parse_qty(hm.group(1)) - q) > 0.02: rep.check(rec, "portion_label_vs_household", lab, r["household_serving_fulltext"], False) else: rep.stats["portion_label_vs_household_ok"] += 1 elif ss: rep.info(rec, "site_no_portion_raw_has_serving", {"serving_size": ss, "unit": su, "household": r["household_serving_fulltext"]}) # newest version for this GTIN? rows = by_bc.get(rec[6], []) if len(rows) > 1: def k(x): fo = food.get(x["fdc_id"], {}) return (fo.get("pub", ""), x["available_date"], x["modified_date"], int(x["fdc_id"])) newest = max(rows, key=k) if newest["fdc_id"] != fid: nk, _ = pick_kcal(nutr.get(newest["fdc_id"], {})) rep.check(rec, "is_newest_gtin_version", fid, {"newest": newest["fdc_id"], "newest_kcal": r1(nk), "key": k(newest), "site_key": k(r)}, False) else: rep.stats["gtin_multiple_versions_site_is_newest"] += 1 # compare with previous version kcal prev = sorted(rows, key=k)[-2] pk, _ = pick_kcal(nutr.get(prev["fdc_id"], {})) if pk is not None and kc is not None and abs(pk - kc) > max(0.1 * max(pk, kc), 10): rep.info(rec, "previous_gtin_version_kcal_differs_gt10pct", {"site": rec[3], "prev_fdc": prev["fdc_id"], "prev_kcal": r1(pk)}) if r["discontinued_date"].strip(): rep.info(rec, "raw_has_discontinued_date", r["discontinued_date"]) # name sanity vs raw description (words preserved?) desc = food.get(fid, {}).get("desc", "") rt = set(tokenize(desc)) stt = set(tokenize(rec[1])) extra = stt - rt if extra: rep.check(rec, "name_tokens_not_in_raw", rec[1], desc, False, sorted(extra)) # ---------------------------------------------------------------- OFF def off_unq(v): if len(v) >= 2 and v[0] == '"' and v[-1] == '"': return v[1:-1].replace('""', '"') return v def fnum(s): s = s.strip() if not s: return None try: x = float(s) except ValueError: return "BAD" if math.isnan(x) or math.isinf(x): return "BAD" return x AMT = re.compile(r"(\d+(?:[.,]\d+)?)\s*(fl\.?\s*oz|fluid ounces?|ml|mℓ|cl|dl|l|ltr|litre|liter|g|gr|grs|gm|gms|gram|" r"grams|gramm|kg|oz|ounces?|lbs?)(?![a-z])", re.I) def amts(text): out = [] for m in AMT.finditer(text or ""): v = float(m.group(1).replace(",", ".")) u = re.sub(r"\s+", "", m.group(2).lower()).rstrip(".") if u in ("ml", "mℓ"): out.append((v, "ml")) elif u == "cl": out.append((v * 10, "ml")) elif u == "dl": out.append((v * 100, "ml")) elif u in ("l", "ltr", "litre", "liter"): out.append((v * 1000, "ml")) elif u.startswith("fl") or u.startswith("fluid"): out.append((v * FLOZ, "ml")) elif u in ("kg",): out.append((v * 1000, "g")) elif u in ("oz", "ounce", "ounces"): out.append((v * OZ, "g")) elif u in ("lb", "lbs"): out.append((v * 453.59, "g")) else: out.append((v, "g")) return out def derive_off(sample, rep): recs = sample.get("O", []) want = {r[6]: r for r in recs} rows = collections.defaultdict(list) path = os.path.join(DL, "off", "csv", "en.openfoodfacts.org.products.csv.gz") print("O: streaming OFF", file=sys.stderr) with gzip.open(path, "rb") as f: hdr = f.readline().decode("utf-8").rstrip("\r\n").split("\t") H = {c: i for i, c in enumerate(hdr)} n = 0 for raw in f: n += 1 tab = raw.find(b"\t") code = raw[:tab].decode("utf-8", "replace").strip().strip('"') nb = my_barcode(code) if code.isdigit() else "" if nb not in want: continue row = raw.decode("utf-8", "replace").rstrip("\r\n").split("\t") if len(row) != len(hdr): rows[nb].append({"_badrow": True}) continue g = {c: off_unq(row[H[c]]) for c in ("code", "product_name", "brands", "serving_size", "serving_quantity", "quantity", "energy-kcal_100g", "energy_100g", "energy-kj_100g", "unique_scans_n", "last_modified_t", "categories_tags", "fat_100g", "carbohydrates_100g", "proteins_100g", "fiber_100g", "alcohol_100g", "countries_tags")} rows[nb].append(g) print("O: %d rows read" % n, file=sys.stderr) for bc, rec in want.items(): cand = [x for x in rows.get(bc, []) if not x.get("_badrow")] if not cand: rep.check(rec, "exists_in_raw", True, False, False) continue if len(cand) > 1: rep.stats["raw_duplicate_codes"] += 1 # pick the row the site most likely used: matching name first def kc_of(g): k = fnum(g["energy-kcal_100g"]) if isinstance(k, float): return k, "kcal" j = fnum(g["energy-kj_100g"]) if not isinstance(j, float): j = fnum(g["energy_100g"]) if isinstance(j, float): return j / 4.184, "kj" return None, None best = None for g in cand: k, how = kc_of(g) if k is not None and close(r1(k), rec[3], 0.051): best = g break if best is None: best = cand[0] k, how = kc_of(best) rep.check(rec, "kcal", rec[3], [r1(kc_of(g)[0]) for g in cand], False, "energy-kcal=%s energy-kj=%s energy=%s" % (best["energy-kcal_100g"], best["energy-kj_100g"], best["energy_100g"])) else: k, how = kc_of(best) rep.stats["kcal_ok"] += 1 rep.stats["kcal_from_" + how] += 1 # independent cross-check: kcal vs kJ when both present kj = fnum(best["energy-kj_100g"]) kk = fnum(best["energy-kcal_100g"]) if isinstance(kj, float) and isinstance(kk, float) and abs(kj / 4.184 - kk) > max(0.1 * kk, 10): rep.info(rec, "raw_kcal_vs_kj_disagree_gt10pct", {"kcal": kk, "kj": kj}) # Atwater cross-check (raw macros) fa, ca, pr, fi, al = (fnum(best[c]) for c in ("fat_100g", "carbohydrates_100g", "proteins_100g", "fiber_100g", "alcohol_100g")) if all(isinstance(x, float) for x in (fa, ca, pr)): est = 9 * fa + 4 * ca + 4 * pr + (2 * fi if isinstance(fi, float) else 0) + ( 5.5 * al if isinstance(al, float) else 0) est2 = est - (4 * fi if isinstance(fi, float) else 0) if min(abs(rec[3] - est), abs(rec[3] - est2)) > max(0.25 * rec[3], 30): rep.info(rec, "atwater_off_gt25pct", {"kcal": rec[3], "est": round(est, 1), "est_fiber_incl": round(est2, 1), "fat": fa, "carb": ca, "prot": pr, "fiber": fi}) # unit (independent rule: stated serving unit, then package quantity unit, else g) sa, qa = amts(best["serving_size"]), amts(best["quantity"]) su = {u for _, u in sa} qu = {u for _, u in qa} if su == {"ml"}: exp_unit = "ml" elif su == {"g"}: exp_unit = "g" elif qu == {"ml"} and not su: exp_unit = "ml" elif qu == {"g"} and not su: exp_unit = "g" else: exp_unit = None # ambiguous if exp_unit is None: rep.stats["unit_ambiguous_raw"] += 1 rep.info(rec, "unit_ambiguous_raw", {"site": rec[4], "serving": best["serving_size"], "quantity": best["quantity"]}) else: rep.check(rec, "unit", rec[4], exp_unit, rec[4] == exp_unit, "serving=%r quantity=%r" % (best["serving_size"], best["quantity"])) # portion sq = fnum(best["serving_quantity"]) if rec[5]: lab, amt = rec[5][0] ok = isinstance(sq, float) and abs(round(sq, 1) - amt) <= 0.06 rep.check(rec, "portion_amount", amt, sq, ok, "serving_size=%r" % best["serving_size"]) # the stated serving must contain the amount in the record's unit family fam = [v for v, u in sa if u == rec[4]] if fam and not any(abs(v - amt) <= max(0.03 * v, 0.6) for v in fam): rep.check(rec, "portion_vs_serving_text", [lab, amt], best["serving_size"], False) elif not fam: rep.check(rec, "portion_unit_family_in_text", [lab, amt, rec[4]], best["serving_size"], False) if rec[3] * amt / 100 > 2000: rep.info(rec, "portion_energy_over_2000kcal", [lab, amt, rec[3]]) else: if isinstance(sq, float) and sq > 0: rep.stats["site_no_portion_raw_has_serving_quantity"] += 1 # barcode rep.check(rec, "barcode", rec[6], my_barcode(best["code"]), rec[6] == my_barcode(best["code"])) # name present in raw rn = " ".join(best["product_name"].split()) if not set(tokenize(rec[1])) <= set(tokenize(rn)) | set(tokenize(best["brands"])): rep.info(rec, "name_differs_from_raw", {"raw": rn[:120], "site": rec[1]}) # ---------------------------------------------------------------- MenuStat def derive_menustat(sample, rep): p = os.path.join(DL, "extra_M", "menustat_historical", "DOHMH_MenuStat_Historical.csv") want = {r[0][2:]: r for r in sample.get("M", [])} rows = collections.defaultdict(dict) for r in read_csv(p): if r["Menu_Item_ID"].strip() in want: rows[r["Menu_Item_ID"].strip()][r["Year"].strip()] = r for mid, rec in want.items(): yrs = rows.get(mid) if not yrs: rep.check(rec, "exists_in_raw", True, False, False) continue r = yrs.get("2018") or yrs[max(yrs)] try: cal = float(r["Calories"]) except ValueError: cal = None # serving: prefer this year's, else other year's rr = r if not r["Serving_Size"].strip(): for y in sorted(yrs, reverse=True): if yrs[y]["Serving_Size"].strip(): rr = yrs[y] rep.info(rec, "serving_from_other_year", y) break try: ss = float(rr["Serving_Size"]) except ValueError: ss = None su = rr["Serving_Size_Unit"].strip() if ss is None or cal is None: rep.check(rec, "raw_has_serving_and_calories", rec[3], [r["Calories"], rr["Serving_Size"], su], False) continue if su == "g": amt, unit = ss, "g" elif su == "ml": amt, unit = ss, "ml" elif su == "fl oz": amt, unit = ss * FLOZ, "ml" elif su == "oz": amt, unit = ss * OZ, "g" else: amt, unit = None, None rep.check(rec, "unit", rec[4], unit, rec[4] == unit, "Serving_Size_Unit=%s cat=%s" % (su, r["Food_Category"])) if amt: k = cal / amt * 100 rep.check(rec, "kcal", rec[3], r1(k), close(rec[3], r1(k), 0.06), "Calories=%s Serving=%s %s" % (r["Calories"], ss, su)) c100 = fnum(rr["Calories_100g"]) if isinstance(c100, float) and abs(c100 - rec[3]) > max(0.05 * c100, 2) and unit == "g": rep.info(rec, "raw_Calories_100g_differs", {"site": rec[3], "Calories_100g": c100, "serving": [ss, su]}) if rec[5]: rep.check(rec, "portion_amount", rec[5][0][1], r1(amt), close(rec[5][0][1], r1(amt), 0.06)) if su == "oz" and r["Food_Category"] != "Beverages": rep.info(rec, "oz_converted_as_weight", {"cat": r["Food_Category"], "name": r["Item_Name"], "household": rr["Serving_Size_household"]}) # ---------------------------------------------------------------- CoFID (own minimal xlsx reader) def xlsx_rows(path, sheet_name): ns = {"m": "http://schemas.openxmlformats.org/spreadsheetml/2006/main", "r": "http://schemas.openxmlformats.org/officeDocument/2006/relationships"} with zipfile.ZipFile(path) as z: wb = ET.fromstring(z.read("xl/workbook.xml")) rels = ET.fromstring(z.read("xl/_rels/workbook.xml.rels")) rmap = {r.get("Id"): r.get("Target") for r in rels} target = None for s in wb.find("m:sheets", ns): if s.get("name") == sheet_name: target = rmap[s.get("{%s}id" % ns["r"])] if target is None: raise SystemExit("sheet not found: " + sheet_name) target = target.lstrip("/") if not target.startswith("xl/"): target = "xl/" + target ss = [] if "xl/sharedStrings.xml" in z.namelist(): for si in ET.fromstring(z.read("xl/sharedStrings.xml")).findall("m:si", ns): ss.append("".join(t.text or "" for t in si.iter("{%s}t" % ns["m"]))) root = ET.fromstring(z.read(target)) for row in root.iter("{%s}row" % ns["m"]): out = {} for c in row.findall("m:c", ns): ref = c.get("r") col = re.match(r"[A-Z]+", ref).group(0) t = c.get("t") v = c.find("m:v", ns) if t == "s" and v is not None: val = ss[int(v.text)] elif t == "inlineStr": val = "".join(x.text or "" for x in c.iter("{%s}t" % ns["m"])) else: val = v.text if v is not None else "" out[col] = val yield out def derive_cofid(sample, rep): p = os.path.join(DL, "extra_U", "cofid_2021", "McCance_Widdowsons_CoFID_2021.xlsx") want = {r[0][2:]: r for r in sample.get("U", [])} it = xlsx_rows(p, "1.3 Proximates") hdr = None data = {} for row in it: vals = list(row.values()) if hdr is None: if any((v or "").strip() == "Food Code" for v in vals): hdr = {v.strip(): k for k, v in row.items() if v} continue code = (row.get(hdr["Food Code"]) or "").strip() if code: data[code] = row kcol = [k for k in hdr if k.lower().startswith("energy (kcal)")] kcol = hdr[kcol[0]] gcol = hdr.get("Group") for code, rec in want.items(): row = data.get(code) if row is None: rep.check(rec, "exists_in_raw", True, False, False) continue v = (row.get(kcol) or "").strip() k = 0.0 if v == "Tr" else fnum(v) rep.check(rec, "kcal", rec[3], k, isinstance(k, float) and close(rec[3], r1(k)), "raw=%r" % v) grp = (row.get(gcol) or "").strip() if gcol else "" exp_unit = "ml" if grp.startswith("Q") else "g" rep.check(rec, "unit", rec[4], exp_unit, rec[4] == exp_unit, "group=%s" % grp) rep.check(rec, "portions_empty", rec[5], [], rec[5] == []) rn = " ".join((row.get(hdr["Food Name"]) or "").split()) if rn != rec[1]: rep.info(rec, "name_changed", {"raw": rn, "site": rec[1]}) # ---------------------------------------------------------------- CIQUAL def derive_ciqual(sample, rep): d = os.path.join(DL, "extra_C", "ciqual_2025") want = {r[0][2:]: r for r in sample.get("C", [])} names = {} for _, el in ET.iterparse(os.path.join(d, "alim_2025_11_03.xml")): if el.tag == "ALIM": c = {x.tag: (x.text or "").strip() for x in el} names[c["alim_code"]] = (c.get("alim_nom_eng", ""), c.get("alim_grp_code", "")) el.clear() energy = {} for _, el in ET.iterparse(os.path.join(d, "compo_2025_11_03.xml")): if el.tag == "COMPO": c = {x.tag: (x.text or "").strip() for x in el} if c["alim_code"] in want and c["const_code"] in ("328", "327"): energy.setdefault(c["alim_code"], {})[c["const_code"]] = c["teneur"] el.clear() for code, rec in want.items(): e = energy.get(code, {}) v = e.get("328", "") vv = v.replace(",", ".").strip() if vv == "traces": k = 0.0 elif vv.startswith("<"): k = "LT:" + vv else: k = fnum(vv) rep.check(rec, "kcal", rec[3], k, isinstance(k, float) and close(rec[3], r1(k)), "raw328=%r raw327=%r" % (v, e.get("327"))) if isinstance(k, str) and k.startswith("LT"): rep.info(rec, "kcal_is_less_than_value", v) kj = fnum(e.get("327", "").replace(",", ".")) if isinstance(kj, float) and isinstance(k, float) and abs(kj / 4.184 - k) > max(0.03 * k, 2): rep.info(rec, "raw_kj_kcal_disagree", {"kcal": k, "kj": kj}) rep.check(rec, "unit", rec[4], "g", rec[4] == "g") nm = " ".join(names.get(code, ("", ""))[0].split()) if nm != rec[1]: rep.info(rec, "name_changed", {"raw": nm, "site": rec[1]}) def cmd_derive(only): sample = load_sample() srcs = only or ["F", "S", "N", "M", "U", "C", "B", "O"] reps = {s: Report(s) for s in srcs} if any(s in srcs for s in "FSN"): full = {s: Report(s) for s in "FSN"} derive_usda_generic(sample, full) for s in "FSN": if s in reps: reps[s] = full[s] if "M" in srcs: derive_menustat(sample, reps["M"]) if "U" in srcs: derive_cofid(sample, reps["U"]) if "C" in srcs: derive_ciqual(sample, reps["C"]) if "B" in srcs: derive_branded(sample, reps["B"]) if "O" in srcs: derive_off(sample, reps["O"]) for s, rep in reps.items(): jdump(os.path.join(OUT, "derive_%s.json" % s), {"sampled": len(sample.get(s, [])), "stats": dict(sorted(rep.stats.items())), "rows": rep.rows}) print(s, len(sample.get(s, [])), json.dumps(dict(sorted(rep.stats.items())))) # ================================================================ known foods (independent reference search) class Index: def __init__(self): self.m = manifest() self.base = os.path.join(SITE, self.m["path"]) self.cache = {} with open(BUNDLE, encoding="utf-8") as f: self.bundle = json.load(f)["r"] def shard(self, kind, b): key = (kind, b) if key not in self.cache: p = os.path.join(self.base, kind, "%d.json" % b) if os.path.exists(p): with open(p, encoding="utf-8") as f: self.cache[key] = json.load(f) else: self.cache[key] = None return self.cache[key] def barcode(self, code): bc = my_barcode(code) sh = self.shard("b", fnv(bc) % self.m["barcodeBuckets"]) return (sh or {}).get(bc) def rank(self, rec, q, req): nt = tokenize(rec[1]) bt = tokenize(rec[2]) allt = set(nt) | set(bt) if not all(t in allt for t in req): return None in_name = all(t in nt for t in req) exact = set(nt) == set(q) starts = bool(nt) and bool(q) and nt[0] == q[0] extra = len([t for t in nt if t not in q]) brand_only = any(t not in nt and t in bt for t in q) generic = rec[7] in ("F", "S", "N", "U", "C") return rec[8] + 250 * in_name + 200 * exact + 80 * starts - 12 * min(extra, 10) + 120 * (generic and not brand_only) def search(self, query, n=8): q = tokenize(query) if not q: return [] if len(q) > 5: keep = sorted(range(len(q)), key=lambda i: (-len(q[i]), i))[:5] q = [q[i] for i in sorted(keep)] lists = {} for t in q: sh = self.shard("t", fnv(t) % self.m["tokenBuckets"]) if sh and t in sh["w"]: lists[t] = [sh["r"][i] for i in sh["w"][t]] req = [t for t in q if lists.get(t)] res = {} if req: shortest = min((lists[t] for t in req), key=len) for rec in shortest: s = self.rank(rec, q, req) if s is not None: res[rec[0]] = (s, rec) for rec in self.bundle: s = self.rank(rec, q, q) if s is not None and rec[0] not in res: res[rec[0]] = (s, rec) out = sorted(res.values(), key=lambda x: (-x[0], x[1][0])) return [(s, r) for s, r in out[:n]] KNOWN = [ # (query, expected kcal/100, unit, tolerance-fraction, portion expectations [(label regex, grams)], note) ("banana raw", 89, "g", [("medium", 118)], "USDA SR 09040"), ("bananas raw", 89, "g", [("medium", 118)], ""), ("banana", 89, "g", [("medium", 118)], ""), ("egg whole raw", 143, "g", [("large", 50)], "large egg ~72 kcal at 50 g"), ("large egg", 143, "g", [("large", 50)], ""), ("egg", 143, "g", [("large", 50)], ""), ("whole milk", 61, None, [("cup", 244)], "61/100 g (244 g/cup); per 100 ml ~63"), ("milk", 61, None, [], ""), ("white rice cooked", 130, "g", [("cup", 158)], "SR 20045 rice white long-grain cooked 130"), ("rice cooked", 130, "g", [], ""), ("chicken breast roasted", 165, "g", [], "SR 05064 165"), ("chicken breast", 165, "g", [], "raw is ~120, roasted 165"), ("olive oil", 884, None, [("tbsp", 13.5)], "884/100 g; ~810/100 ml"), ("coca cola", 42, "ml", [], "~42/100 ml; 140 per 12 fl oz"), ("coca-cola classic", 42, "ml", [], ""), ("coke", 42, "ml", [], ""), ("nutella", 539, "g", [], ""), ("big mac", 257, "g", [], "550 kcal / ~214 g"), ("cheerios", 375, "g", [], "~140 per 39 g (US 2019+ label) = 359..380"), ("oreo", 480, "g", [], "~470-490"), ("chobani nonfat plain greek yogurt", 59, "g", [], "~80 per 170 g = 47..59"), ("chobani plain greek yogurt", 59, "g", [], ""), ("quaker oats", 375, "g", [], "old-fashioned dry ~375 (150/40 g)"), ("oats", 375, "g", [], ""), ("peanut butter", 588, "g", [("tbsp", 16)], "SR 16098 588"), ("apple raw", 52, "g", [("medium", 182)], ""), ("apple", 52, "g", [], ""), ("avocado", 160, "g", [], ""), ("butter", 717, "g", [("tbsp", 14.2)], ""), ("bread white", 266, "g", [("slice", 25)], ""), ("pizza", 266, "g", [], "cheese pizza ~266"), ("orange juice", 45, None, [("cup", 248)], ""), ("beer", 43, None, [], ""), ("red wine", 85, None, [], ""), ("sugar", 387, "g", [("tsp", 4.2)], ""), ("salmon", 208, "g", [], "raw atlantic farmed 208; wild 142"), ("potato baked", 93, "g", [], ""), ("broccoli", 34, "g", [], ""), ("cheddar cheese", 403, "g", [], "SR 404; F 408"), ("almonds", 579, "g", [], ""), ] KNOWN_BARCODES = [ ("3017620422003", "Nutella 400 g", 539, "g"), ("3017620425035", "Nutella 1 kg", 539, "g"), ("049000028911", "Coca-Cola Classic 12 fl oz can (UPC-A 0 49000 02891 1)", 42, "ml"), ("5449000000996", "Coca-Cola 330 ml EU", 42, "ml"), ("5449000131805", "Coca-Cola Zero", 0.3, "ml"), ("016000275287", "Cheerios 8.9 oz", 357, "g"), ("044000032029", "Oreo original", 480, "g"), ("818290011695", "Chobani nonfat plain greek 32 oz", 53, "g"), ("030000010204", "Quaker Old Fashioned Oats 42 oz", 375, "g"), ("051500255162", "Jif Creamy PB 16 oz", 590, "g"), ("028400090858", "Lay's Classic", 536, "g"), ("038000138416", "Pringles original", 536, "g"), ("012000001291", "Pepsi 12 oz", 41, "ml"), ("7622210449283", "Milka? / Cadbury", None, "g"), ("8000500037560", "Kinder Bueno", 572, "g"), ("5000112576009", "Coca-Cola Zero UK", 0.2, "ml"), ("3274080005003", "Cristaline water", 0, "ml"), ("8076800195057", "Barilla spaghetti n5", 359, "g"), ("5000159407236", "Snickers", 488, "g"), ("040000002673", "M&M's milk chocolate", 492, "g"), ("021000658831", "Kraft Mac & Cheese Original box", 371, "g"), ("041196910759", "Progresso?", None, "g"), ("078742195360", "Great Value?", None, "g"), ] def cmd_known(): ix = Index() out = {"search": [], "barcode": []} for query, exp, unit, ports, note in KNOWN: res = ix.search(query, 8) rows = [] for s, r in res: dev = None if not exp else round((r[3] - exp) / exp * 100, 1) rows.append({"final": s, "id": r[0], "name": r[1], "brand": r[2], "kcal": r[3], "unit": r[4], "portions": r[5], "score": r[8], "dev_pct": dev}) out["search"].append({"query": query, "expect": exp, "unit": unit, "note": note, "top": rows}) for code, label, exp, unit in KNOWN_BARCODES: r = ix.barcode(code) e = {"code": code, "label": label, "expect": exp, "unit": unit, "norm": my_barcode(code)} if r: e.update({"id": r[0], "name": r[1], "brand": r[2], "kcal": r[3], "unit_site": r[4], "portions": r[5], "dev_pct": None if not exp else round((r[3] - exp) / exp * 100, 1)}) out["barcode"].append(e) jdump(os.path.join(OUT, "known.json"), out) for s in out["search"]: print("==", s["query"], "expect", s["expect"], s["unit"], s["note"]) for r in s["top"][:6]: print(" %5d %-12s %-60s | %-18s %6.1f %s %s" % (r["final"], r["id"], r["name"][:60], r["brand"][:18], r["kcal"], r["unit"], json.dumps(r["portions"][:3]))) for b in out["barcode"]: print("BC", b["code"], b["label"], "->", b.get("id"), b.get("name"), b.get("brand"), b.get("kcal"), b.get("unit_site"), b.get("portions")) PLAUS = [ # (rule, name regex, exclusion regex, {unit: (lo, hi)}) ("plain_oil", r"^(organic |pure |100% |premium |classic |light tasting |extra light |extra virgin |virgin |refined |" r"cold pressed )*(olive|canola|vegetable|sunflower|rapeseed|peanut|avocado|grapeseed|sesame|corn|" r"safflower|soybean)( seed)? oil$|^huile d['’ ]?(olive|arachide|tournesol|colza)\b|" r"^olio (extra ?vergine|di oliva|di semi)|^aceite de (oliva|girasol)\b|^(extra virgin )?olive oil\b", r"spray|in oil|dressing|mayo|with |flavou?r|infused|garlic|chili|truffle|basil|lemon|herb|blend with butter|" r"spread|popcorn|aerosol|mist|tuna|sardine", {"g": (790, 900), "ml": (740, 845)}), ("ice_cream", r"\bice cream\b", r"cone|mix|sauce|topping|syrup|creamer|coffee|tea\b|drink|soda|cereal|candy|" r"cookie|scoop|sprinkle|wafer|cake|bar\b|bars\b|sandwich|pop\b|popcorn|flavou?r|" r"protein|keto|powder|freeze|maker|chew|gum|lip|balm|shake|float|cup cake", {"g": (60, 450), "ml": (40, 300)}), ("chocolate_bar", r"^(swiss |belgian |organic |fine |premium |pure |extra )*(milk|dark|white) chocolate( bar| bars|" r" tablet)?$|^(chocolat (noir|au lait))( extra)?$|^(vollmilch|zartbitter)schokolade$", r"milk chocolate milk|drink|syrup|sauce|beverage", {"g": (400, 650)}), ("peanut_butter", r"^(creamy |crunchy |smooth |natural |organic |chunky |old fashioned |extra crunchy )*" r"peanut butter$", r"powder|reduced|light|lite", {"g": (540, 700)}), ("nuts", r"^(raw |roasted |dry roasted |salted |unsalted |lightly salted |whole |natural |organic |shelled |" r"sliced |slivered |chopped |california )*(almonds|walnuts|cashews|pecans|pistachios|hazelnuts|" r"macadamia nuts|peanuts|brazil nuts)$", r"", {"g": (520, 740)}), ("plain_water", r"^(natural |pure |still |sparkling |purified |spring |mineral |natural mineral |natural spring |" r"artesian |alkaline |premium |drinking |distilled )+water$|^eau (min[ée]rale|de source)", r"coconut|flavou?r|vitamin|tonic|enhanced", {"g": (0, 5), "ml": (0, 5)}), ("diet_soda", r"\b(diet (coke|cola|pepsi|soda|sprite|7up|dr pepper|mountain dew|root beer|ginger ale)|coke zero|" r"coca[- ]cola zero|pepsi (max|zero)|zero sugar (cola|soda))\b", r"", {"g": (0, 10), "ml": (0, 10)}), ("regular_cola", r"^(coca[- ]cola|coke|pepsi|cola)( classic| original| original taste)?$", r"", {"g": (30, 60), "ml": (30, 60)}), ("sugar", r"^(granulated |white |pure cane |cane |pure |organic cane |extra fine |fine )*sugar$", r"", {"g": (370, 405)}), ("honey", r"^(pure |raw |organic |clover |wildflower |natural |local |manuka |acacia |creamed |unfiltered )*honey$", r"", {"g": (260, 340), "ml": (350, 460)}), ("butter", r"^(salted |unsalted |sweet cream |organic |pure |irish |european style |grass[- ]fed |cultured )*" r"butter( sticks| quarters)?$", r"", {"g": (650, 770)}), ("cheddar", r"^(sharp |mild |medium |extra sharp |aged |vermont |white |yellow |natural |organic |irish |" r"farmhouse |mature |extra mature )*cheddar( cheese)?$", r"", {"g": (330, 460)}), ("leafy_raw", r"^(lettuce|spinach|cucumber|celery|kale|arugula|romaine|baby spinach|iceberg lettuce)" r"( hearts| leaves)?(, raw)?$", r"", {"g": (5, 60)}), ("banana", r"^bananas?(, raw)?$", r"", {"g": (80, 100)}), ("egg", r"^(large |extra large |medium |jumbo |fresh |brown |white |organic |free range |cage free |grade a |" r"pasture raised )*eggs?$", r"", {"g": (120, 165)}), ("whole_milk", r"^(organic |vitamin d |fresh )?(whole milk|milk, whole)$", r"", {"g": (55, 70), "ml": (55, 72)}), ("flour", r"^(all[- ]purpose |unbleached |enriched |bread |whole wheat |plain |self[- ]raising |organic )*flour$", r"", {"g": (320, 380)}), ("rolled_oats", r"^(old fashioned |rolled |quick |steel cut |organic |whole grain |gluten free )+oats$", r"", {"g": (350, 410)}), ("mayonnaise", r"^(real |classic |original )?mayonnaise$", r"", {"g": (600, 760), "ml": (550, 720)}), ] PLAUS_C = [(n, re.compile(a, re.I), re.compile(b, re.I) if b else None, r) for n, a, b, r in PLAUS] SPOON = {"tsp": 4.93, "teaspoon": 4.93, "teaspoons": 4.93, "tbsp": 14.79, "tbs": 14.79, "tablespoon": 14.79, "tablespoons": 14.79, "tbsps": 14.79, "tsps": 4.93} def portion_class(lab, amt, unit, kcal): """Classify one portion; return a list of problem tags.""" tags = [] low = lab.lower().strip() if "�" in lab: tags.append("label_replacement_char") if '""' in lab: tags.append("label_doubled_quotes") if re.fullmatch(r"(g|gr|gram|grams|ml|oz|serving|servings|portion|unit|units|\.|-)", low): tags.append("label_is_bare_unit_word") if re.match(r"^\d+\.0\s", low): tags.append("label_trailing_point_zero") m = re.match(NUMW + r"\s*([a-z][a-z.]*)", low) if not m: return tags q = parse_qty(m.group(1)) word = m.group(2).rstrip(".") if not q: return tags if word in SPOON or word in ("c", "cup", "cups") or word in ("fl", "floz") or word in ("oz", "ounce", "ounces", "lb", "lbs"): if word in SPOON: exp = q * SPOON[word] kind = "spoon" elif word in ("c", "cup", "cups"): if re.match(r"c\.? (à|a|�) ", low[len(m.group(1)):].strip()): return tags # French "c. à soupe/thé" exp = q * 236.6 kind = "cup" elif word in ("fl", "floz"): exp = q * FLOZ kind = "floz" elif word in ("lb", "lbs"): exp = q * 453.59 kind = "mass" else: if re.match(r"(oz|ounces?)\.?\s*(fl|fluid)", low[len(m.group(1)):].strip()): exp, kind = q * FLOZ, "floz" else: exp, kind = q * OZ, "mass" ratio = amt / exp if exp else 0 if q >= 10 and kind in ("spoon", "cup") and ratio < 0.3 or (kind == "mass" and q >= 10 and ratio < 0.2): tags.append("fraction_lost_in_label") # "14 cup" for 1/4 cup, "23 cup" for 2/3 cup elif kind == "spoon": if unit == "g" and ratio > 3.0: tags.append("spoon_label_amount_too_big") elif unit == "g" and ratio < 0.12: tags.append("spoon_label_amount_too_small") elif unit == "ml" and not (0.7 <= ratio <= 1.4): tags.append("spoon_label_ml_mismatch") elif kind == "cup": if unit == "ml" and not (0.75 <= ratio <= 1.3): tags.append("cup_label_ml_mismatch") elif unit == "g" and ratio > 2.2: tags.append("cup_label_amount_too_big") elif kind == "floz": if unit == "ml" and not (0.85 <= ratio <= 1.18): tags.append("floz_label_ml_mismatch") elif unit == "g" and not (0.5 <= ratio <= 1.6): tags.append("floz_label_g_mismatch") elif kind == "mass": if unit == "g" and not (0.85 <= ratio <= 1.15): tags.append("mass_label_g_mismatch_gt15pct") return tags def cmd_audit2(): m = manifest() st = collections.Counter() ex = collections.defaultdict(list) plaus = collections.defaultdict(collections.Counter) plaus_ex = collections.defaultdict(list) seen = set() b_names = {} def add(k, item, cap=40): if len(ex[k]) < cap: ex[k].append(item) for kind, bucket, data in iter_shards(): recs = data["r"] if kind == "t" else list(data.values()) for r in recs: if r[0] in seen: continue seen.add(r[0]) rid, name, brand, kcal, unit, portions, barcode, src, score = r if src == "B": b_names[rid[2:]] = (name, brand) # oils / impossible ml energies if unit == "ml" and kcal > 845: st["ml_over_845_impossible_%s" % src] += 1 add("ml_over_845_impossible_%s" % src, [rid, name, brand, kcal]) # plausibility low = name.lower().strip() for rule, rx, exc, rng in PLAUS_C: if rx.search(low) and not (exc and exc.search(low)): if unit not in rng: continue lo, hi = rng[unit] plaus[rule]["checked_" + src] += 1 if kcal < lo or kcal > hi: side = "low" if kcal < lo else "high" plaus[rule]["%s_%s" % (side, src)] += 1 if len(plaus_ex[rule + "_" + src]) < 25: plaus_ex[rule + "_" + src].append([rid, name, brand, kcal, unit, portions[:2]]) break # portions for lab, amt in portions: for t in portion_class(lab, amt, unit, kcal): st["%s_%s" % (t, src)] += 1 add("%s_%s" % (t, src), [lab, amt, unit, rid, name, kcal]) if unit == "g" and amt >= 1000 and kcal < 25 and not BEV_NAME.search(low): st["huge_serving_and_tiny_kcal_%s" % src] += 1 add("huge_serving_and_tiny_kcal_%s" % src, [lab, amt, rid, name, kcal]) if amt > 1500 and lab.lower().startswith(("1 serving", "1 portion")) and unit == "g": st["serving_over_1500g_%s" % src] += 1 st["records_" + src] += 1 if portions: st["records_with_portions_" + src] += 1 # B title-casing attribution: compare with raw description d = usda_dir("branded", "FoodData_Central_branded_food_csv") raw_desc = {} with open(os.path.join(d, "food.csv"), encoding="utf-8", newline="") as f: rd = csv.reader(f) next(rd) for row in rd: if row[0] in b_names: raw_desc[row[0]] = row[2] tc = collections.Counter() tc_ex = collections.defaultdict(list) for fid, (name, brand) in b_names.items(): rdsc = raw_desc.get(fid, "") letters = [c for c in rdsc if c.isalpha()] caps = sum(1 for c in letters if c.isupper()) / len(letters) if letters else 0 if caps < 0.8: continue tc["title_cased_by_pipeline"] += 1 for tag, rx in UGLY: if tag in ("pack_size_in_name", "ct_count"): continue if rx.search(name): tc[tag] += 1 if len(tc_ex[tag]) < 25: tc_ex[tag].append([fid, rdsc[:100], name[:100], brand]) if re.search(r"\b(Oz|Lb|Lbs|Ct|Pk|Fl)\b", name): tc["unit_word_capitalised"] += 1 if len(tc_ex["unit_word_capitalised"]) < 25: tc_ex["unit_word_capitalised"].append([fid, rdsc[:100], name[:100], brand]) if re.search(r"\b[A-Z][a-z]?[bcdfghjklmnpqrstvwxz]{2,}\b", name) and re.search( r"\b(Brwnie|Chz|Chkn|Ckn|Brst|Frzn|Vnla|Choc|Strwbry|Wht|Whl|Grn|Pkg|Sdwch|Crm|Pnt|Btr|Bty|Crk|Rte|" r"Bbq|Org|Swt|Sft|Hrd|Frsh|Mlk|Ylw|Blk|Rd|Chsck|Crnch)\b", name): tc["abbreviation_soup"] += 1 if len(tc_ex["abbreviation_soup"]) < 25: tc_ex["abbreviation_soup"].append([fid, rdsc[:100], name[:100], brand]) for fid, (name, brand) in b_names.items(): if '"' in name and (name.startswith('"') or '""' in name): tc["literal_quotes_in_name"] += 1 if len(tc_ex["literal_quotes_in_name"]) < 25: tc_ex["literal_quotes_in_name"].append([fid, name[:100], brand]) if brand and re.search(r"\bMc[a-z]", brand): tc["brand_Mc_lower"] += 1 if len(tc_ex["brand_Mc_lower"]) < 25: tc_ex["brand_Mc_lower"].append([fid, name[:60], brand]) jdump(os.path.join(OUT, "audit2.json"), {"stats": dict(sorted(st.items())), "examples": ex, "plausibility": {k: dict(v) for k, v in plaus.items()}, "plausibility_examples": plaus_ex, "title_case_B": dict(tc), "title_case_B_examples": tc_ex}) print(json.dumps(dict(sorted(st.items())), indent=0)) print(json.dumps({k: dict(v) for k, v in plaus.items()}, indent=0)) print(json.dumps(dict(tc), indent=0)) def cmd_offraw(codes): """Print the raw OFF rows (key fields) for the given barcodes (matched after normalisation).""" want = {my_barcode(c) for c in codes} path = os.path.join(DL, "off", "csv", "en.openfoodfacts.org.products.csv.gz") cols = ("code", "product_name", "brands", "quantity", "serving_size", "serving_quantity", "energy-kcal_100g", "energy-kj_100g", "energy_100g", "fat_100g", "carbohydrates_100g", "proteins_100g", "fiber_100g", "unique_scans_n", "data_quality_errors_tags", "countries_tags", "categories_tags", "last_modified_t", "no_nutrition_data") with gzip.open(path, "rb") as f: hdr = f.readline().decode("utf-8").rstrip("\r\n").split("\t") H = {c: i for i, c in enumerate(hdr)} for raw in f: tab = raw.find(b"\t") code = raw[:tab].decode("utf-8", "replace").strip().strip('"') if not code.isdigit() or my_barcode(code) not in want: continue row = raw.decode("utf-8", "replace").rstrip("\r\n").split("\t") if len(row) != len(hdr): print("BADROW", code, len(row)) continue print(json.dumps({c: off_unq(row[H[c]])[:300] for c in cols}, ensure_ascii=False)) def cmd_offpop(minpop=50): """Which popular OFF products (unique_scans_n >= minpop) are absent from the site's barcode shards, and why?""" m = manifest() keys = set() bdir = os.path.join(SITE, m["path"], "b") for fn in os.listdir(bdir): with open(os.path.join(bdir, fn), encoding="utf-8") as f: keys.update(json.load(f).keys()) path = os.path.join(DL, "off", "csv", "en.openfoodfacts.org.products.csv.gz") st = collections.Counter() missing = [] with gzip.open(path, "rb") as f: hdr = f.readline().decode("utf-8").rstrip("\r\n").split("\t") H = {c: i for i, c in enumerate(hdr)} for raw in f: row = raw.decode("utf-8", "replace").rstrip("\r\n").split("\t") if len(row) != len(hdr): continue g = lambda c: off_unq(row[H[c]]) p = fnum(g("unique_scans_n")) if not isinstance(p, float) or p < minpop: continue code = g("code").strip() nb = my_barcode(code) if code.isdigit() else "" st["popular_rows"] += 1 if nb and nb in keys: st["present"] += 1 continue kc = fnum(g("energy-kcal_100g")) if not isinstance(kc, float) and not isinstance(fnum(g("energy_100g")), float): st["absent_no_energy"] += 1 continue if not g("product_name").strip(): st["absent_no_name"] += 1 continue dq = [t for t in g("data_quality_errors_tags").split(",") if t] fa, ca, pr, fi = (fnum(g(c)) for c in ("fat_100g", "carbohydrates_100g", "proteins_100g", "fiber_100g")) est = None if all(isinstance(x, float) for x in (fa, ca, pr)): est = 9 * fa + 4 * ca + 4 * pr + (2 * fi if isinstance(fi, float) else 0) reason = "dq_tag" if dq else "other_rule" st["absent_with_energy_" + reason] += 1 ok_at = isinstance(kc, float) and est is not None and abs(kc - est) <= max(0.15 * kc, 20) if ok_at: st["absent_with_energy_%s_atwater_consistent" % reason] += 1 missing.append({"code": code, "name": g("product_name")[:80], "brands": g("brands")[:40], "pop": p, "kcal": kc, "atwater_est": None if est is None else round(est, 1), "atwater_consistent": ok_at, "serving": g("serving_size")[:40], "dq": [t.replace("en:nutrition-", "")[:90] for t in dq][:3]}) missing.sort(key=lambda x: -x["pop"]) jdump(os.path.join(OUT, "off_popular_missing.json"), {"min_pop": minpop, "stats": dict(st), "missing": missing}) print(json.dumps(dict(st), indent=1)) for x in missing[:60]: print(json.dumps(x, ensure_ascii=False)[:300]) def main(): if len(sys.argv) >= 2 and sys.argv[1] == "offraw": cmd_offraw(sys.argv[2:]) return if len(sys.argv) >= 2 and sys.argv[1] == "offpop": cmd_offpop(int(sys.argv[2]) if len(sys.argv) > 2 else 50) return if len(sys.argv) < 2: raise SystemExit(__doc__) cmd = sys.argv[1] only = None if "--only" in sys.argv: only = sys.argv[sys.argv.index("--only") + 1].split(",") if cmd == "scan": cmd_scan() elif cmd == "derive": cmd_derive(only) elif cmd == "known": cmd_known() elif cmd == "audit2": cmd_audit2() else: raise SystemExit(__doc__) if __name__ == "__main__": main()