#!/usr/bin/env python3 """Normalize USDA FoodData Central into the Losing It food-index intermediate format. Inputs (downloaded and unzipped by hand, see FoodIndex/downloads/usda/): foundation_json/ FoodData_Central_foundation_food_json_.json (src F) (the Oct 2026 Foundation *CSV* zip on fdc.nal.usda.gov ships without food.csv, food_nutrient.csv and food_portion.csv, so the same release's JSON is used) foundation/ FoodData_Central_foundation_food_csv_/ (nutrient.csv, foundation_food.csv: cross-checks) sr_legacy/ FoodData_Central_sr_legacy_food_csv_2018-04/ (src S) fndds/ FoodData_Central_survey_food_csv_/ (src N) branded/ FoodData_Central_branded_food_csv_/ (src B) Outputs: normalized/usda_generic.jsonl F, S, N (generic=true, brand "") normalized/usda_branded.jsonl B (generic=false) qa/usda_atwater_flags.jsonl records whose energy differs from 4P+4C+9F+7A by >35% and >30 kcal qa/usda_stats.json counts, drop reasons, samples Python stdlib only. Run: python3 -I normalize_usda.py [--root /FoodIndex] """ import argparse import collections import csv import datetime import glob import html import json import os import random import re import sys import unicodedata sys.path.insert(0, os.path.dirname(os.path.abspath(__file__))) # python3 -I does not add the script's directory import plausibility # noqa: E402 csv.field_size_limit(1 << 30) BUILD_DATE = datetime.date.today().isoformat() # ---------------------------------------------------------------- nutrients # Expected (id -> (name, unit_name)); verified against every nutrient.csv we read. NUT_EXPECT = { 1008: ("Energy", "KCAL"), 2047: ("Energy (Atwater General Factors)", "KCAL"), 2048: ("Energy (Atwater Specific Factors)", "KCAL"), 1062: ("Energy", "kJ"), 1003: ("Protein", "G"), 1004: ("Total lipid (fat)", "G"), 1005: ("Carbohydrate, by difference", "G"), 1050: ("Carbohydrate, by summation", "G"), 1018: ("Alcohol, ethyl", "G"), } WANT = set(NUT_EXPECT) KJ_PER_KCAL = 4.184 def load_nutrient_table(path): by_id, by_nbr = {}, {} with open(path, encoding="utf-8", newline="") as fh: for r in csv.DictReader(fh): by_id[r["id"]] = r if r.get("nutrient_nbr"): by_nbr.setdefault(r["nutrient_nbr"], []).append(r) for nid, (name, unit) in NUT_EXPECT.items(): r = by_id.get(str(nid)) if r is None or r["name"] != name or r["unit_name"] != unit: raise SystemExit(f"nutrient.csv {path}: id {nid} is {r and (r['name'], r['unit_name'])}, expected {(name, unit)}") return by_id, by_nbr def nutrient_key_map(by_id, by_nbr, sample_ids): """Return dict raw food_nutrient.nutrient_id -> canonical nutrient id (int) for the WANT ids. FDC files normally store nutrient.id in food_nutrient.nutrient_id; the FNDDS 2021-2023 CSV stores the legacy nutrient_nbr (208 = energy kcal) instead. Detect which and refuse if ambiguous.""" in_id = sum(1 for x in sample_ids if x in by_id) in_nbr = sum(1 for x in sample_ids if x in by_nbr) both = [x for x in sample_ids if x in by_id and x in by_nbr] if both: raise SystemExit(f"ambiguous nutrient ids (both id and nutrient_nbr): {both[:10]}") if in_id and in_nbr: raise SystemExit(f"food_nutrient mixes ids ({in_id}) and nutrient_nbr ({in_nbr})") if in_id: return {str(i): i for i in WANT}, "id" m = {} for i in WANT: nbr = by_id[str(i)]["nutrient_nbr"] rows = by_nbr.get(nbr, []) # nutrient_nbr is not unique in nutrient.csv (e.g. 205 maps to 1005 and others); # only accept a mapping when the number maps to exactly one row with the expected name+unit. hits = [r for r in rows if r["name"] == NUT_EXPECT[i][0] and r["unit_name"] == NUT_EXPECT[i][1]] if len(rows) == 1 and len(hits) == 1: m[nbr] = i elif len(hits) == 1 and i in (1003, 1004, 1005, 1008, 1018, 1062): # primary proximates: the first row with this number is the canonical one m[nbr] = i return m, "nutrient_nbr" def pick_energy(n, chain): """n: dict nutrient id -> amount. Returns (kcal, path) or (None, None).""" for nid in chain: v = n.get(nid) if v is None: continue if nid == 1062: return v / KJ_PER_KCAL, "kJ/4.184" return v, str(nid) return None, None def atwater(n): p, f = n.get(1003), n.get(1004) c = n.get(1005) if c is None: c = n.get(1050) if p is None or f is None or c is None: return None return 4 * p + 4 * c + 9 * f + 7 * (n.get(1018) or 0.0) def atwater_flag(kcal, est): if est is None: return None diff = abs(kcal - est) if diff <= 30: return False if kcal <= 0: return True return diff > 0.35 * kcal # ---------------------------------------------------------------- text helpers WS = re.compile(r"\s+") TM = re.compile(r"[®™©℠]") MOJIBAKE_RE = re.compile("[\u00c2\u00c3][\u0080-\u00bf]|\u00e2\u20ac|\u00c3\u00a9|\u00c3\u00a8") def fix_mojibake(s): """UTF-8 text that was decoded as cp1252/latin-1 ("Nestlé", "Miyoko’s"): re-encode and decode as UTF-8 when that gives valid UTF-8; otherwise leave the text alone.""" if not s or not MOJIBAKE_RE.search(s): return s for enc in ("cp1252", "latin-1"): try: t = s.encode(enc).decode("utf-8") except (UnicodeEncodeError, UnicodeDecodeError): continue if t != s: return t return s def clean_ws(s): s = fix_mojibake(s or "") if "&" in s: s = html.unescape(s) # a few branded descriptions carry & / & / ™ s = s.replace('""', '"') # CSV-style doubled quotes left in some descriptions: 6"" Ciabatta, ""Ham,SMKD,..."" s = re.sub(r"(\d)\s+%", r"\1%", s) return WS.sub(" ", TM.sub("", s)).strip() def norm_key(s): """Name key for generic dedupe: NFKD, strip marks, casefold, alnum tokens joined by space.""" s = unicodedata.normalize("NFKD", s) s = "".join(ch for ch in s if unicodedata.category(ch) != "Mn").casefold() return " ".join(t for t in re.split(r"[^0-9a-z]+", s) if t) def alnum(s): return re.sub(r"[^0-9a-z]+", "", (s or "").casefold()) def tokens(s): return set(t for t in re.split(r"[^0-9a-z]+", (s or "").casefold()) if t) SMALL = {"and", "or", "of", "the", "with", "in", "on", "for", "to", "from", "by", "at", "an", "as", "n", "nor", "per", "vs", "via", "w", "de", "del", "la", "le", "en", "et"} UNITS = {"oz", "fl", "lb", "lbs", "g", "gm", "kg", "mg", "mcg", "ml", "l", "ct", "pk", "pkg", "pc", "pcs", "gal", "qt", "pt", "cal", "kcal", "cc", "tbsp", "tsp", "cnt"} ACRONYMS = {"bbq", "blt", "usa", "pb", "xl", "xxl", "xxxl", "ii", "iii", "iv", "vi", "vii", "viii", "ix", "xi", "xii", "dha", "epa", "mct", "gmo", "ipa", "nfs", "ny", "nyc", "uk", "uht", "ihop", "kfc", "cbd", "msg", "rtd", "ufo", "tv", "diy", "bk", "aa", "aaa", "abc", "bpa", "hfcs", "usda", "fda", "dc", "nfl", "nba", "mlb", "nhl", "ncaa", "ufc", "lsu", "ucla", "nz", "pbj", "ihop", "gf", "rx", "hp", "pbs", "oj", "bbq's"} NOT_ACRONYM = {"dr", "mr", "mrs", "ms", "st", "jr", "sr", "th", "nd", "rd", "hwy", "lt", "sm", "md", "lg", "pkg", "pk", "ct", "pcs", "pc", "tbsp", "tsp", "lb", "lbs", "oz", "fl", "qt", "pt", "mg", "ml", "kg", "mcg", "gm", "cnt", "ltd", "wm", "ny's", "pts", "spry", "mrs."} APOS_LOWER = {"s", "t", "d", "m", "ll", "re", "ve", "n"} WORD_RE = re.compile(r"[^\W\d_]+(?:['’][^\W\d_]+)*") NUMISH = re.compile(r"[\d][\d.,/\-]*%?|\d+(?:\.\d+)?[xX×]\d+(?:\.\d+)?") # 12, 1/2, 8x2.75 def _cap(p): if len(p) > 3 and p[:2].lower() == "mc" and p[2].isalpha(): return "Mc" + p[2].upper() + p[3:].lower() # McCormick, McIntosh, McQueen return p[:1].upper() + p[1:].lower() def _cap_apos(w): parts = re.split(r"(['’])", w) out = [_cap(parts[0])] for i in range(1, len(parts), 2): seg = parts[i + 1] out.append(parts[i]) out.append(seg.lower() if seg.lower() in APOS_LOWER else _cap(seg)) return "".join(out) def _unit_case(lw): return "L" if lw == "l" else lw def smart_title(s): """Deterministic title case for ALL-CAPS USDA branded strings.""" out, last, first = [], 0, True for m in WORD_RE.finditer(s): a, b = m.span() out.append(s[last:a]) w = m.group(0) lw = w.lower() before = s[a - 1] if a > 0 else "" after = s[b] if b < len(s) else "" prev_tok = s[:a].rstrip().split(" ")[-1].strip("(),;:[]") if s[:a].strip() else "" nxt = s[b:].lstrip().lower() amp_pair = (before == "&" and len(lw) == 1 and a >= 2 and s[a - 2].isalpha() and (a < 3 or not s[a - 3].isalpha()) and not (b < len(s) and s[b].isalpha())) base_apos = lw[:-2] if lw.endswith(("'s", "’s")) else None if amp_pair: r = w.upper() # A&W, H&W, S&W elif base_apos and base_apos in ACRONYMS: r = w[:-2].upper() + w[-2:].lower() # ABC'S -> ABC's elif before.isdigit(): if lw in UNITS: r = _unit_case(lw) elif lw == "x": r = "x" elif len(lw) <= 2: r = w.upper() else: r = lw elif lw in UNITS and prev_tok and NUMISH.fullmatch(prev_tok): r = _unit_case(lw) elif lw == "oz" and prev_tok.lower() == "fl": r = "oz" elif lw == "fl" and nxt.startswith("oz") and prev_tok and NUMISH.fullmatch(prev_tok): r = "fl" elif after.isdigit(): r = w.upper() if len(w) <= 2 else _cap(w) elif lw in ACRONYMS: r = w.upper() elif not first and lw in SMALL: r = lw elif len(lw) == 1: r = w.upper() elif (2 <= len(lw) <= 4 and lw.isascii() and lw.isalpha() and not (set(lw) & set("aeiouy")) and lw not in NOT_ACRONYM): r = w.upper() else: r = _cap_apos(w) out.append(r) first = False last = b out.append(s[last:]) return "".join(out) def mostly_caps(s): cased = [c for c in s if c.isupper() or c.islower()] if len(cased) < 2: return False lower = sum(1 for c in cased if c.islower()) return lower <= 0.10 * len(cased) CAPS_WORD = re.compile(r"(?= 2 else s CORP_SUFFIX = re.compile(r"(?:,?\s+(?:inc\.?|incorporated|llc\.?|l\.l\.c\.?|ltd\.?|limited|corp\.?|corporation|" r"s\.a\.|gmbh|l\.p\.|lp|plc|pty|co\.,?\s*ltd\.?))+\s*$", re.I) def clean_brand(s): s = clean_ws(s) s2 = CORP_SUFFIX.sub("", s).strip().rstrip(",").strip() if len(s2) >= 2: s = s2 if mostly_caps(s): s = smart_title(s) else: s = fix_caps_words(s) if s.lower() in PLACEHOLDER_BRANDS: s = "" return s PLACEHOLDER_BRANDS = {"n/a", "na", "nan", "none", "null", "unknown", "no brand", "generic", "brand", "0", "-", "not applicable", "unbranded", "sans marque", "no name"} NAME_COMPRESS = [ # applied in order, only while a USDA generic name is longer than 120 characters (r"\s*\(Includes foods for USDA's Food Distribution Program\)", ""), (r"\bseparable lean and fat\b", "lean and fat"), (r"\bseparable lean only\b", "lean only"), (r"\btrimmed to (0|1/8|1/4)\" fat\b", r'\1" fat trim'), (r"\bAust\. marble score\b", "marble score"), (r"\bincluding\b", "incl."), (r"\bexcluding\b", "excl."), (r"\bassorted brands including\b", "incl."), (r"\bcream sauce, white sauce, or mushroom sauce\b", "cream, white, or mushroom sauce"), ] def shorten_name(s, limit=120): """Shorten a long USDA generic name. Order: phrase compressions, drop parentheticals (last first), then keep head and tail around a middle ellipsis so the distinguishing end (raw / cooked, roasted / no dressing / sauce type) survives. Returns (name, changed).""" if len(s) <= limit: return s, False for pat, rep in NAME_COMPRESS: if len(s) <= limit: break s = clean_ws(re.sub(pat, rep, s)) while len(s) > limit and re.search(r"\s*\([^()]*\)", s): m = list(re.finditer(r"\s*\([^()]*\)", s))[-1] s = clean_ws(s[:m.start()] + s[m.end():]).replace(" ,", ",") if len(s) <= limit: return s, True head = s[:80] head = head[:head.rfind(" ")].rstrip(" ,;") if " " in head else head room = limit - len(head) - 3 tail = s[-room:] if " " in tail and not s[-room - 1:-room].isspace(): tail = tail[tail.find(" ") + 1:] return f"{head} \u2026 {tail.lstrip(' ,;')}", True def truncate_name(s, limit=120): if len(s) <= limit: return s, False cut = s[:limit + 1] i = cut.rfind(", ") if i >= 60: return cut[:i].strip(" ,;-"), True i = cut.rfind(" ") return cut[:i if i > 0 else limit].strip(" ,;-"), True # ---------------------------------------------------------------- portions FRACTIONS = {0.125: "1/8", 0.167: "1/6", 0.1667: "1/6", 0.25: "1/4", 0.33: "1/3", 0.333: "1/3", 0.3333: "1/3", 0.5: "1/2", 0.66: "2/3", 0.67: "2/3", 0.667: "2/3", 0.6667: "2/3", 0.75: "3/4"} def fmt_amount(x): if x is None: return "1" if abs(x - round(x)) < 1e-9: return str(int(round(x))) whole = int(x) frac = round(x - whole, 4) for k, v in FRACTIONS.items(): if abs(frac - k) < 1e-6: return f"{whole} {v}" if whole else v s = f"{x:.3f}".rstrip("0").rstrip(".") return s VOL_ML = {"cup": 236.588, "cups": 236.588, "tbsp": 14.787, "tablespoon": 14.787, "tablespoons": 14.787, "tsp": 4.929, "teaspoon": 4.929, "teaspoons": 4.929, "fl oz": 29.574, "fluid ounce": 29.574, "fluid ounces": 29.574, "ml": 1.0, "milliliter": 1.0, "milliliters": 1.0, "liter": 1000.0, "liters": 1000.0, "l": 1000.0, "quart": 946.353, "quarts": 946.353, "qt": 946.353, "pint": 473.176, "pints": 473.176, "gallon": 3785.41, "gallons": 3785.41, "gal": 3785.41} WT_G = {"oz": 28.3495, "ounce": 28.3495, "ounces": 28.3495, "lb": 453.592, "lbs": 453.592, "pound": 453.592, "pounds": 453.592} NUM_RE = r"(\d+\s+\d+/\d+|\d+-\d+/\d+|\d+/\d+|\d+(?:\.\d+)?|\.\d+)" LEAD_RE = re.compile(r"^" + NUM_RE + r"\s*(.*)$") GRAM_LABEL = re.compile(r"^" + NUM_RE + r"\s*(g|gm|gr|grs|gram|grams|grm|mg|kg)\.?$", re.I) GRAM_PAREN = re.compile(r"\s*\([^()]*\d\s*(?:g|gm|gr|grams?|grm)\b[^()]*\)", re.I) GRAM_MENTION = re.compile(r"(?:[/=]\s*)?(?:about\s+|approx\.?\s+)?\d[\d.,]*\s*(?:g|gr|grs|gram|grams|gm|grm|mg|kg|" r"ml|mls|milliliters?|mlt)\b\.?|\bgrams?\b", re.I) GRAM_ONLY = re.compile(r"\d[\d.,]*\s*(?:g|gr|grs|gram|grams|gm|grm|mg|kg)\b\.?|\bgrams?\b", re.I) def parse_num(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)) if int(m.group(3)) else None 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 def measure_check(label, amount, unit): """Returns (ok, kind). Checks volume labels against plausible density and weight labels against the gram weight. unit is the record's base unit ('g' or 'ml').""" m = LEAD_RE.match(label.lower()) if not m: return True, None num = parse_num(m.group(1)) rest = m.group(2) if not num or num <= 0: return False, "bad-number" word = None for w in sorted(VOL_ML, key=len, reverse=True): if re.match(re.escape(w) + r"\b", rest): word = ("vol", w) break if word is None: for w in sorted(WT_G, key=len, reverse=True): if re.match(re.escape(w) + r"\b", rest): word = ("wt", w) break if word is None: return True, None kind, w = word if kind == "vol": ml = num * VOL_ML[w] ratio = amount / ml if unit == "g": # 0.012 g/ml: freeze-dried herbs; 1.6 g/ml: denser than honey or syrup (nothing sold by the cup is) return (0.012 <= ratio <= 1.6), "vol" return (0.85 <= ratio <= 1.2), "vol" g = num * WT_G[w] ratio = amount / g if unit == "g": return (0.85 <= ratio <= 1.18), "wt" return False, "wt-on-ml" def shorten_label(label, limit=72): while len(label) > limit and re.search(r"\s*\([^()]*\)\s*$", label): label = re.sub(r"\s*\([^()]*\)\s*$", "", label).strip(" ,") while len(label) > limit and re.search(r"\s*\([^()]*\)", label): # drop the last remaining parenthetical anywhere idx = [m for m in re.finditer(r"\s*\([^()]*\)", label)][-1] label = (label[:idx.start()] + label[idx.end():]).strip(" ,") return label EXAMPLES = collections.defaultdict(list) def example(key, item, k=8): if len(EXAMPLES[key]) < k: EXAMPLES[key].append(item) def finalize_portions(cands, unit, stats, src, strict=True): """cands: list of (label, grams, cls, seq). Returns list of [label, amount] (max 6). strict: drop portions failing the measure check (generic); otherwise caller handles it.""" out, seen = [], set() for label, grams, cls, seq in sorted(cands, key=lambda c: (c[2], c[3])): label = canon_label(clean_ws(GRAM_PAREN.sub("", label)).strip(" ,;")) if not label: stats[src]["portion_drop_empty_label"] += 1 continue if GRAM_LABEL.match(label) or (strict and GRAM_ONLY.search(label)): stats[src]["portion_drop_gram_label"] += 1 example(f"{src}:portion_drop_gram_label", [label, grams]) continue label = shorten_label(label) if len(label) > 72: stats[src]["portion_drop_long_label"] += 1 example(f"{src}:portion_drop_long_label", label) continue if grams is None: stats[src]["portion_drop_no_weight"] += 1 continue amt = round(grams, 1) if not (0 < amt <= 5000): stats[src]["portion_drop_weight_range"] += 1 example(f"{src}:portion_drop_weight_range", [label, grams]) continue if strict: ok, kind = measure_check(label, amt, unit) if not ok: stats[src][f"portion_drop_measure_check_{kind}"] += 1 example(f"{src}:portion_drop_measure_check_{kind}", [label, amt]) continue key = label.lower() if key in seen: stats[src]["portion_drop_duplicate_label"] += 1 continue seen.add(key) out.append([label, amt]) out = order_portions(out) if len(out) > 6: stats[src]["portion_trimmed_over_6"] += len(out) - 6 out = out[:6] return out def canon_label(label): label = re.sub(r"\btablespoons?\b", "tbsp", label, flags=re.I) label = re.sub(r"\bteaspoons?\b", "tsp", label, flags=re.I) label = re.sub(r"\bfluid ounces?\b", "fl oz", label, flags=re.I) return label def order_portions(ps): """Stable: a '1 medium ...' portion goes first, a bare '1 serving' (RACC/unspecified) goes last.""" first = [p for p in ps if re.match(r"^1 medium\b", p[0])][:1] last = [p for p in ps if p[0] == "1 serving"] mid = [p for p in ps if p not in first and p not in last] return first + mid + last UNIT_DISPLAY = {"tablespoon": "tbsp", "teaspoon": "tsp", "milliliter": "ml", "liter": "L", "fl oz": "fl oz", "oz": "oz", "lb": "lb", "cubic inch": "cubic inch", "cubic centimeter": "cc", "Tablespoons": "tbsp", "RACC": "serving"} NO_PLURAL = {"tbsp", "tsp", "ml", "L", "fl oz", "oz", "lb", "cc", "cubic inch"} SKIP_UNITS = {"paired cooked w", "paired raw w", "dripping w", "orig ckd g", "orig rw g"} SIZE_WORDS = ("extra small", "extra large", "small", "medium", "large", "jumbo", "regular") def unit_label(amount, unit_name, modifier): """Foundation-style label from amount + measure unit + modifier.""" a = fmt_amount(amount) mod = clean_ws(modifier) if re.fullmatch(r"[A-Z][a-z]+", mod): mod = mod.lower() if unit_name == "each": ml = mod.lower() for sw in SIZE_WORDS: if ml.startswith(sw): return f"{a} {mod}" return f"{a} whole" + (f" ({mod})" if mod else "") u = UNIT_DISPLAY.get(unit_name, unit_name.lower()) if amount is not None and abs(amount - 1) > 1e-9 and u not in NO_PLURAL and not u.endswith("s"): u = u + "es" if u.endswith(("ch", "sh")) else u + "s" if unit_name == "RACC": return f"{a} serving" return f"{a} {u}" + (f", {mod}" if mod else "") # ---------------------------------------------------------------- record def make_record(src, sid, name, brand, kcal, unit, portions, barcode, generic, date, countries, category, pop=None, full_name=None): return {"_key": norm_key(full_name or name), "id": f"{src}:{sid}", "name": name, "brand": brand, "kcal": round(kcal + 0.0, 1), "unit": unit, "portions": portions, "barcode": barcode, "src": src, "generic": generic, "pop": pop, "date": date, "countries": countries, "lang": "en", "category": category} def iso_date(s): s = (s or "").strip() if not s: return "" m = re.fullmatch(r"(\d{4})-(\d{2})-(\d{2}).*", s) if m: return f"{m.group(1)}-{m.group(2)}-{m.group(3)}" m = re.fullmatch(r"(\d{1,2})/(\d{1,2})/(\d{4})", s) if m: return f"{m.group(3)}-{int(m.group(1)):02d}-{int(m.group(2)):02d}" return "" def norm_barcode(s): d = "".join(ch for ch in (s or "") if "0" <= ch <= "9") if len(d) == 12: d = "0" + d elif len(d) == 14 and d[0] == "0": d = d[1:] return d if 6 <= len(d) <= 14 else "" def check_kcal(kcal, stats, src, what=None): if what is not None and kcal is not None and round(kcal, 1) > 905: example(f"{src}:drop_energy_over_905", [what, kcal]) if kcal is None: stats[src]["drop_no_energy"] += 1 return None if kcal != kcal or kcal < 0: stats[src]["drop_energy_negative_or_nan"] += 1 return None if round(kcal, 1) > 905: stats[src]["drop_energy_over_905"] += 1 return None if round(kcal, 1) > 900: # pure fats are 902 kcal/100 g in SR/FNDDS; the contract caps kcal at 900 (0.2% change) stats[src]["energy_clamped_to_900"] += 1 example(f"{src}:energy_clamped_to_900", [what, kcal]) return 900.0 return kcal class Ctx: def __init__(self, root): self.root = root self.dl = os.path.join(root, "downloads", "usda") self.stats = collections.defaultdict(collections.Counter) self.flags = [] self.version_conflicts = [] self.serving_drops = [] self.energy_path = collections.defaultdict(collections.Counter) self.atwater = collections.defaultdict(collections.Counter) self.meta = {} def one(self, pattern): hits = sorted(h for h in glob.glob(os.path.join(self.dl, pattern)) if not h.endswith(".zip")) if len(hits) != 1: raise SystemExit(f"expected exactly one match for {pattern}, got {hits}") return hits[0] def qa(self, rec, n, est): flag = atwater_flag(rec["kcal"], est) src = rec["src"] if flag is None: self.atwater[src]["unchecked_missing_macros"] += 1 return self.atwater[src]["checked"] += 1 if flag: self.atwater[src]["flagged"] += 1 if rec["kcal"] == 0: self.atwater[src]["flagged_kcal_zero"] += 1 self.flags.append({"id": rec["id"], "name": rec["name"], "brand": rec["brand"], "kcal": rec["kcal"], "unit": rec["unit"], "atwater_4_4_9_7": round(est, 1), "protein": n.get(1003), "fat": n.get(1004), "carb": n.get(1005) if n.get(1005) is not None else n.get(1050), "alcohol": n.get(1018)}) # ---------------------------------------------------------------- Foundation (F) def build_foundation(ctx): st = ctx.stats["F"] jpath = ctx.one("foundation_json/*.json") csvdir = ctx.one("foundation/FoodData_Central_foundation_food_csv_*") load_nutrient_table(os.path.join(csvdir, "nutrient.csv")) with open(os.path.join(csvdir, "foundation_food.csv"), encoding="utf-8", newline="") as fh: csv_ids = {int(r["fdc_id"]) for r in csv.DictReader(fh)} with open(jpath, encoding="utf-8") as fh: foods = json.load(fh)["FoundationFoods"] json_ids = {f["fdcId"] for f in foods} ctx.meta["F"] = {"json": os.path.basename(jpath), "foods_in_json": len(foods), "foundation_food_csv_ids": len(csv_ids), "ids_match": csv_ids == json_ids} recs = [] for f in foods: st["input"] += 1 if f.get("dataType") != "Foundation": st["drop_not_foundation"] += 1 continue n = {} for fn in f.get("foodNutrients", []): nut = fn.get("nutrient", {}) nid = nut.get("id") if nid in WANT and fn.get("amount") is not None: exp = NUT_EXPECT[nid] if nut.get("name") != exp[0] or (nut.get("unitName") or "").lower() != exp[1].lower(): raise SystemExit(f"Foundation nutrient mismatch {nid}: {nut}") n[nid] = float(fn["amount"]) kcal, path = pick_energy(n, (1008, 2048, 2047, 1062)) kcal = check_kcal(kcal, ctx.stats, "F", f"{f['fdcId']} {f.get('description')}") if kcal is None: continue ctx.energy_path["F"][path] += 1 name = clean_ws(f.get("description")) name, cut = shorten_name(name) if cut: st["name_shortened"] += 1 if len(name) < 2: st["drop_name_short"] += 1 continue cands = [] for i, p in enumerate(f.get("foodPortions", [])): un = (p.get("measureUnit") or {}).get("name") or "" if un in SKIP_UNITS: st["portion_drop_yield_unit"] += 1 continue amount = p.get("amount", p.get("value")) amount = float(amount) if amount is not None else 1.0 if amount <= 0: st["portion_drop_bad_amount"] += 1 continue mod = clean_ws(p.get("modifier") or "") pdsc = clean_ws(p.get("portionDescription") or "") if pdsc and pdsc != mod: mod = f"{mod}, {pdsc}" if mod else pdsc if un == "undetermined": label = f"{fmt_amount(amount)} {mod}" if mod else "" else: label = unit_label(amount, un, mod) cls = 2 if un == "RACC" else (1 if un in ("oz", "lb") else 0) gw = p.get("gramWeight") cands.append((label, float(gw) if gw is not None else None, cls, (p.get("sequenceNumber") or 0, i))) portions = finalize_portions(cands, "g", ctx.stats, "F") rec = make_record("F", f["fdcId"], name, "", kcal, "g", portions, "", True, iso_date(f.get("publicationDate")), ["us"], clean_ws((f.get("foodCategory") or {}).get("description")), full_name=f.get("description")) ctx.qa(rec, n, atwater(n)) recs.append(rec) return recs # ---------------------------------------------------------------- CSV generic (S, N) def read_food_nutrients(path, keymap, fdc_filter=None, energy_only=None): """fdc_filter: read all WANT nutrients for these fdc_ids; energy_only: also read energy (1008/1062/2047/2048) for these ids (used for the previous version of each GTIN in QA).""" out = collections.defaultdict(dict) energy = {1008, 1062, 2047, 2048} with open(path, encoding="utf-8", newline="") as fh: rd = csv.reader(fh) hdr = next(rd) i_f, i_n, i_a = hdr.index("fdc_id"), hdr.index("nutrient_id"), hdr.index("amount") for row in rd: nid = keymap.get(row[i_n]) if nid is None: continue fid = row[i_f] if fdc_filter is not None and fid not in fdc_filter: if not (energy_only and nid in energy and fid in energy_only): continue a = row[i_a].strip() if a == "": continue try: out[fid][nid] = float(a) except ValueError: continue return out def sample_nutrient_ids(path, k=200000): s = set() with open(path, encoding="utf-8", newline="") as fh: rd = csv.reader(fh) hdr = next(rd) i_n = hdr.index("nutrient_id") for j, row in enumerate(rd): s.add(row[i_n]) if j >= k: break return s def build_csv_generic(ctx, src, d): st = ctx.stats[src] by_id, by_nbr = load_nutrient_table(os.path.join(d, "nutrient.csv")) fn_path = os.path.join(d, "food_nutrient.csv") keymap, mode = nutrient_key_map(by_id, by_nbr, sample_nutrient_ids(fn_path)) if set(keymap.values()) < {1008, 1003, 1004, 1005}: raise SystemExit(f"{src}: could not map core nutrients: {keymap}") ctx.meta[src] = {"dir": os.path.basename(d), "food_nutrient_key": mode, "key_map": {k: v for k, v in sorted(keymap.items())}} nutr = read_food_nutrients(fn_path, keymap) with open(os.path.join(d, "measure_unit.csv"), encoding="utf-8", newline="") as fh: mu = {r["id"]: r["name"] for r in csv.DictReader(fh)} upd = {} p = os.path.join(d, "food_update_log_entry.csv") if os.path.exists(p): with open(p, encoding="utf-8", newline="") as fh: for r in csv.DictReader(fh): upd[r["id"]] = iso_date(r["last_updated"]) if src == "S": with open(os.path.join(d, "food_category.csv"), encoding="utf-8", newline="") as fh: cats = {r["id"]: r["description"] for r in csv.DictReader(fh)} want_type = "sr_legacy_food" else: with open(os.path.join(d, "wweia_food_category.csv"), encoding="utf-8", newline="") as fh: cats = {r["wweia_food_category"]: r["wweia_food_category_description"] for r in csv.DictReader(fh)} want_type = "survey_fndds_food" portions_by = collections.defaultdict(list) with open(os.path.join(d, "food_portion.csv"), encoding="utf-8", newline="") as fh: for r in csv.DictReader(fh): portions_by[r["fdc_id"]].append(r) recs = [] with open(os.path.join(d, "food.csv"), encoding="utf-8", newline="") as fh: for r in csv.DictReader(fh): st["input"] += 1 if r["data_type"] != want_type: st["drop_wrong_data_type"] += 1 continue fid = r["fdc_id"] n = nutr.get(fid, {}) kcal, path = pick_energy(n, (1008, 2048, 2047, 1062)) kcal = check_kcal(kcal, ctx.stats, src, f"{fid} {r['description']}") if kcal is None: continue ctx.energy_path[src][path] += 1 name, cut = shorten_name(clean_ws(r["description"])) if cut: st["name_shortened"] += 1 if len(name) < 2: st["drop_name_short"] += 1 continue rows = sorted(portions_by.get(fid, []), key=lambda x: (int(x["seq_num"]) if x["seq_num"].strip() else 9999, int(x["id"]))) cands, fallback = [], [] for i, pr in enumerate(rows): try: gw = float(pr["gram_weight"]) if pr["gram_weight"].strip() else None except ValueError: gw = None seq = (int(pr["seq_num"]) if pr["seq_num"].strip() else 9999, i) if src == "S": amt_s = pr["amount"].strip() amount = float(amt_s) if amt_s else 1.0 if amount <= 0: st["portion_drop_bad_amount"] += 1 continue un = mu.get(pr["measure_unit_id"], "undetermined") mod = clean_ws(pr["modifier"]) if un in SKIP_UNITS: st["portion_drop_yield_unit"] += 1 continue if un == "undetermined": if not mod: st["portion_drop_empty_label"] += 1 continue label = f"{fmt_amount(amount)} {mod}" else: label = unit_label(amount, un, mod) lw = mod.lower() cls = 1 if lw in ("oz", "lb") or lw.startswith(("oz ", "oz,", "lb ")) else 0 cands.append((label, gw, cls, seq)) else: desc = clean_ws(pr["portion_description"]) ld = desc.lower() if ld.startswith("quantity not specified"): fallback.append(("1 serving", gw, 3, seq)) continue if ld.startswith("guideline amount"): st["portion_skip_guideline_amount"] += 1 continue cls = 0 if "cubic inch" in ld or "surface inch" in ld or re.search(r"\byields?\b", ld): cls = 2 elif re.match(r"^\d[\d/. ]*\s*(oz|lb)\b", ld): cls = 1 cands.append((desc, gw, cls, seq)) portions = finalize_portions(cands, "g", ctx.stats, src) if not portions and fallback: portions = finalize_portions(fallback[:1], "g", ctx.stats, src) if portions: st["portion_used_quantity_not_specified"] += 1 date = max(iso_date(r["publication_date"]), upd.get(fid, "")) rec = make_record(src, fid, name, "", kcal, "g", portions, "", True, date, ["us"], clean_ws(cats.get(r["food_category_id"], "")), full_name=r["description"]) ctx.qa(rec, n, atwater(n)) recs.append(rec) return recs # ---------------------------------------------------------------- generic dedupe SRC_RANK = {"F": 0, "S": 1, "N": 2} def kcal_close(a, b): return abs(a - b) <= max(0.10 * max(a, b), 2.0) def dedupe_generic(recs, ctx): groups = collections.defaultdict(list) for r in recs: groups[r["_key"]].append(r) keep, dropped = [], [] for key, g in groups.items(): g.sort(key=lambda r: (SRC_RANK[r["src"]], -int(r["date"].replace("-", "") or 0), -int(r["id"][2:]))) kept = [] for r in g: dup = next((k for k in kept if kcal_close(k["kcal"], r["kcal"])), None) if dup is None: kept.append(r) continue # same food (same USDA name, energy within 10%): keep the higher-priority record and # borrow household portions it lacks (portion weights describe the same physical food) have = {p[0].lower() for p in dup["portions"]} added = 0 for p in r["portions"]: if len(dup["portions"]) >= 6: break if p[0].lower() not in have: dup["portions"].append(p) have.add(p[0].lower()) added += 1 dup["portions"] = order_portions(dup["portions"]) ctx.stats["dedupe"][f"drop_{r['src']}_dup_of_{dup['src']}"] += 1 if added: ctx.stats["dedupe"]["portions_borrowed_records"] += 1 dropped.append({"dropped": r["id"], "kept": dup["id"], "name": r["name"], "kcal_dropped": r["kcal"], "kcal_kept": dup["kcal"], "portions_borrowed": added}) if len(kept) > 1: ctx.stats["dedupe"]["same_name_kept_kcal_differs"] += len(kept) - 1 keep.extend(kept) return keep, dropped # ---------------------------------------------------------------- Branded (B) COUNTRY = {"united states": "us", "us": "us", "usa": "us", "new zealand": "nz", "nz": "nz", "canada": "ca", "australia": "au", "mexico": "mx", "united kingdom": "gb"} HH_UNIT = [ # (regex on lowercased unit text, replacement) (r"^onz\b\.?", "oz"), (r"^oza\b\.?", "fl oz"), (r"^(fl\s*\.?\s*oz\.?|floz|fluid\s+ounces?|fl\.?\s+ounces?)", "fl oz"), (r"^oz\.?\s+serving\b", "oz"), (r"^fl\.?$", "fl oz"), (r"^(ounces?|oz\.)", "oz"), (r"^(tbsp\.?|tbs\.?|tablespoons?)\b\.?", "tbsp"), (r"^(tsp\.?|teaspoons?)\b\.?", "tsp"), (r"^(pcs\.?|pc's|pc\.?)(?=\s|$)", "pieces"), (r"^(pkg\.?)(?=\s|$)", "package"), (r"^mlt\b", "ml"), (r"^grm\b", "g"), (r"^(sec\.?\s+spray|second\s+spray)", "second spray"), (r"^lbr\b", "lb"), (r"^ltr\b", "L"), ] HH_EMPTY = {"", "none", "n/a", "na", "amount per serving", "amount/serving", "per serving", "serving", "1 serving", "as prepared", "as packaged", "dry mix"} def household_label(text, amount, unit, stats): """Turn household_serving_fulltext into a short label for exactly `amount` (g or ml).""" t = clean_ws(text).replace("½", " 1/2").replace("¼", " 1/4").replace("¾", " 3/4") t = clean_ws(t.replace("*", "")) if t.lower() in HH_EMPTY: return "1 serving", "empty" t = re.split(r"[(|,:;]", t)[0].strip() t = re.sub(r"^(about|approx\.?|approximately|per|makes|serving size|serving)\s+", "", t, flags=re.I).strip() gm = GRAM_MENTION.search(t) if gm: # "4 OUNCE/112 GRAM", "1/4 cup 30g", "1 bottle500 ml": keep the household part before the metric amount t = t[:gm.start()].strip(" /=-") if not t: return "1 serving", "weight-label" if not t: return "1 serving", "empty" m = LEAD_RE.match(t) if not m: w = t.lower() if re.fullmatch(r"[a-z][a-z \-]{1,20}", w) and len(w.split()) <= 2 and w not in HH_EMPTY: t = "1 " + w m = LEAD_RE.match(t) else: return "1 serving", "no-number" num_s, rest = m.group(1), m.group(2).strip() num = parse_num(num_s) if not num or num <= 0: return "1 serving", "bad-number" if not rest: return "1 serving", "no-unit" rest_l = rest.lower() for pat, rep in HH_UNIT: mm = re.match(pat, rest_l) if mm: rest_l = rep + rest_l[mm.end():] break rest_l = clean_ws(rest_l).strip(" .-") if re.fullmatch(r"(g|gm|gr|grs|gram|grams|mg|kg|ml|milliliters?|mls?)\.?", rest_l): return "1 serving", "weight-label" label = f"{fmt_amount(num)} {rest_l}" if len(label) > 40 or not re.search(r"[a-z]", rest_l): return "1 serving", "junk" ok, kind = measure_check(label, amount, unit) if not ok: stats["B"][f"label_replaced_measure_check_{kind}"] += 1 return "1 serving", "mismatch" return label, "ok" # ---------------------------------------------------------------- Branded serving-size plausibility # USDA Branded per-100 g values are the label's per-serving values divided by serving_size. When serving_size is # wrong (very often the whole package weight typed as the serving), the per-100 g energy is wrong by the same factor # and the Atwater check cannot see it (the macros are scaled the same way). These rules compare serving_size with # the label's own household text and package weight. _FR = {"½": " 1/2", "¼": " 1/4", "¾": " 3/4", "⅓": " 1/3", "⅔": " 2/3", "⅛": " 1/8"} _HNUM = r"(\d+\s+\d+/\d+|\d+-\d+/\d+|\d+/\d+|\d+(?:\.\d+)?|\.\d+)" HH_METRIC_RE = re.compile(r"(? amount in the record's unit family, or None when it cannot be read safely.""" low = (pw or "").lower() has_g = bool(re.search(r"\d\s*(lbr|lbs?|pounds?|onz|grm|grams?|gr|g|kg)(?![a-z])", low)) has_ml = bool(re.search(r"\d\s*(oza|fl\.?\s*oz|mlt|ml|ltr|liters?|litres?|l|pt|pints?|qt|quarts?|gal|gallons?)" r"(?![a-z])", low)) if (unit == "ml" and has_g) or (unit == "g" and has_ml and not has_g): return None # the package is given in the other family: not comparable vals = [] for m in PKG_RE.finditer(pw or ""): v, u = float(m.group(1)), re.sub(r"\s+", " ", m.group(2).lower()).rstrip(".") if u.startswith("fl"): u = "oza" if u in ("oz", "ounce", "ounces"): vals.append(v * (28.349523125 if unit == "g" else 29.5735295625)) elif unit == "g" and u in PKG_G: vals.append(v * PKG_G[u]) elif unit == "ml" and u in PKG_ML: vals.append(v * PKG_ML[u]) return max(vals) if vals else None def serving_problems(ssize, sunit, hh, pw, unit, name=""): """Problems of a Branded serving size. Returns a list of tags; tags starting with 'drop_' make the per-100 g values doubtful (the record is dropped), 'label_' ones only make the household label unusable ('1 serving').""" out = [] try: amt = float(ssize) except (TypeError, ValueError): return out if not amt > 0: return out text = _hh_text(hh) fam = "g" if unit == "g" else "ml" per100 = abs(amt - 100.0) < 1e-6 # the label itself is per 100 g / 100 ml: per-100 values are direct complex_text = bool(HH_COMPLEX_RE.search(text)) # 1. an explicit metric amount in the household text that contradicts serving_size exp = [] for m in HH_METRIC_RE.finditer(text): v = parse_num(m.group(1)) u = m.group(2).lower() f = "ml" if u.startswith("m") and "l" in u else "g" if v and f == fam: exp.append(v) pkg = package_amount(pw, unit) # serving_size equal to the whole (multi-serving) package: the classic data-entry error eq_pkg = pkg is not None and pkg >= 150 and abs(amt - pkg) <= 0.03 * pkg and not per100 confirmed = False if exp and not complex_text: r = min(max(amt / v, v / amt) for v in exp) confirmed = r <= 1.15 if r > 1.5 and eq_pkg and amt > max(exp): out.append("drop_serving_is_package_household_smaller") elif r > 1.15: out.append("label_household_metric_differs") # usually the household text is the odd one out # 2. serving bigger than the package if pkg is not None and pkg >= 5 and amt > 1.1 * pkg and not per100: out.append("drop_serving_over_package") # 3. huge servings drinkish = unit == "ml" or bool(DRINK_NAME_RE.search(name or "")) if unit == "g" and amt >= 1000 and not drinkish: out.append("drop_serving_over_1kg") if unit == "ml" and amt >= 2000: out.append("drop_serving_over_2l") # 4. the household measure against the amount (not when the text's own metric amount confirms the serving, # and not for multi-part texts such as "2 tbsp dry (makes 1 cup)") m = None if (confirmed or complex_text or per100) else HH_MEASURE_RE.match(text.lower()) if m: num = parse_num(m.group(1)) mu = _measure_unit(m.group(2)) if num and num > 0: if mu in MEASURE_VOL: ml = num * MEASURE_VOL[mu] ratio = amt / ml if num >= 10 and mu != "fl oz" and ratio < 0.3: out.append("label_lost_fraction_slash") elif unit == "g": if eq_pkg and ratio > MAX_G_PER_ML: out.append("drop_measure_far_below_serving") elif ratio > MAX_G_PER_ML: out.append("label_measure_g_per_ml_too_high") elif ratio < 0.012: out.append("label_measure_g_per_ml_too_low") else: if eq_pkg and ratio > 1.2: out.append("drop_measure_far_below_serving") elif not (0.85 <= ratio <= 1.2): out.append("label_measure_ml_mismatch") elif mu in MEASURE_WT and unit == "g": g = num * MEASURE_WT[mu] ratio = amt / g if num >= 10 and ratio < 0.2: out.append("label_lost_fraction_slash") elif ratio > 2.0 or ratio < 0.5 or (eq_pkg and ratio > 1.18): out.append("drop_weight_measure_far_from_serving") elif not (0.85 <= ratio <= 1.18): out.append("label_weight_measure_mismatch") return out def build_branded(ctx): st = ctx.stats["B"] d = ctx.one("branded/FoodData_Central_branded_food_csv_*") by_id, by_nbr = load_nutrient_table(os.path.join(d, "nutrient.csv")) fn_path = os.path.join(d, "food_nutrient.csv") keymap, mode = nutrient_key_map(by_id, by_nbr, sample_nutrient_ids(fn_path)) ctx.meta["B"] = {"dir": os.path.basename(d), "food_nutrient_key": mode} print("B: reading food.csv", file=sys.stderr) food = {} with open(os.path.join(d, "food.csv"), encoding="utf-8", newline="") as fh: rd = csv.reader(fh) hdr = next(rd) i_id, i_dt, i_desc, i_pub, i_mc = (hdr.index(x) for x in ("fdc_id", "data_type", "description", "publication_date", "market_country")) for row in rd: if row[i_dt] != "branded_food": st["food_csv_not_branded"] += 1 continue food[row[i_id]] = (row[i_desc], iso_date(row[i_pub]), row[i_mc]) print(f"B: {len(food)} foods; reading branded_food.csv", file=sys.stderr) best, prev = {}, {} with open(os.path.join(d, "branded_food.csv"), encoding="utf-8", newline="") as fh: rd = csv.DictReader(fh) for r in rd: st["input"] += 1 fid = r["fdc_id"] f = food.get(fid) if f is None: st["drop_no_food_row"] += 1 continue bc = norm_barcode(r["gtin_upc"]) if not bc: st["drop_invalid_gtin"] += 1 continue key = (f[1], iso_date(r["available_date"]), iso_date(r["modified_date"]), int(fid)) cur = best.get(bc) if cur is not None: st["gtin_duplicate_rows_superseded"] += 1 if cur[0] >= key: p = prev.get(bc) if p is None or p[0] < key: prev[bc] = (key, fid, r["serving_size_unit"].strip()) continue prev[bc] = (cur[0], cur[1], cur[5].strip()) best[bc] = (key, fid, r["brand_owner"], r["brand_name"], r["serving_size"], r["serving_size_unit"], r["household_serving_fulltext"], r["branded_food_category"], r["discontinued_date"], r["market_country"], r.get("package_weight") or "") print(f"B: {len(best)} distinct GTINs; reading food_nutrient.csv", file=sys.stderr) chosen = {v[1] for v in best.values()} nutr = read_food_nutrients(fn_path, keymap, chosen, energy_only={v[1] for v in prev.values()}) unit_of = {"g": "g", "GRM": "g", "ml": "ml", "MLT": "ml"} print("B: building records", file=sys.stderr) recs = [] for bc, (key, fid, owner, bname, ssize, sunit, hh, cat, disc, mcountry, pw) in best.items(): st["gtin_latest_rows"] += 1 desc, pub, fmc = food[fid] disc_d = iso_date(disc) if disc.strip(): if not disc_d or disc_d <= BUILD_DATE: st["drop_discontinued"] += 1 continue u = sunit.strip() if u in ("g", "GRM"): unit = "g" elif u in ("ml", "MLT"): unit = "ml" else: st[f"drop_serving_unit_{u or 'empty'}"] += 1 continue n = nutr.get(fid, {}) kcal, path = pick_energy(n, (1008, 2048, 2047, 1062)) kcal = check_kcal(kcal, ctx.stats, "B", f"{fid} {desc}") if kcal is None: continue ctx.energy_path["B"][path] += 1 # brand brand_raw = (bname if clean_ws(bname) else owner).replace("*", " ") brand = clean_brand(brand_raw) # name ("*" is a label footnote marker, e.g. "Vanilla Light* Ice Cream") name = clean_ws(desc.replace("*", " ")) segs = name.split(", ") if len(segs) >= 2: for cand in (bname, owner, brand_raw): ck = alnum(CORP_SUFFIX.sub("", clean_ws(cand))) if ck and alnum(segs[0]) == ck and len(", ".join(segs[1:])) >= 2: segs = segs[1:] st["name_brand_prefix_removed"] += 1 break while len(segs) >= 2: last_t = tokens(segs[-1]) if last_t and last_t <= tokens(", ".join(segs[:-1])): segs = segs[:-1] st["name_repeated_variant_removed"] += 1 else: break name = clean_ws(", ".join(s.strip() for s in segs)).strip(" ,;") if len(name) >= 2 and name[0] == '"' and name[-1] == '"' and name.count('"') == 2: name = name[1:-1].strip() if mostly_caps(name): name = smart_title(name) st["name_title_cased"] += 1 else: t = fix_caps_words(name) if t != name: name = t st["name_caps_words_title_cased"] += 1 t = strip_pack_text(name) if t != name: name = t st["name_pack_text_removed"] += 1 name, cut = truncate_name(name) name = name.strip(" ,;") if cut: st["name_truncated"] += 1 if len(name) < 2: st["drop_name_short"] += 1 continue # serving size and energy density plausibility (see serving_problems and plausibility.py) probs = serving_problems(ssize, sunit, hh, pw, unit, name) hard = [p for p in probs if p.startswith("drop_")] band = None if hard else plausibility.check(name, kcal, unit, usda_category=clean_ws(cat), brand=brand) if hard or band: reason = hard[0] if hard else "drop_density_band_" + band[0] st[reason] += 1 ctx.serving_drops.append({"id": f"B:{fid}", "reason": reason, "name": name, "brand": brand, "kcal": round(kcal, 1), "unit": unit, "serving_size": ssize, "serving_unit": sunit, "household": clean_ws(hh), "package_weight": pw, "category": clean_ws(cat), "band": list(band) if band else None}) continue soft = [p for p in probs if p.startswith("label_")] # portion portions = [] try: amt = float(ssize) if ssize.strip() else None except ValueError: amt = None if re.search(r"\bwith\b.*\bmilk\b", hh, re.I): # "3/4 cup with 1/2 cup skim milk" = 150 g: the serving weight includes the milk while the # per-100 g values are often for the dry product, so the amount cannot be trusted st["portion_none_serving_with_milk"] += 1 elif amt is not None and 0 < round(amt, 1) <= 5000: label, why = household_label(hh, round(amt, 1), unit, ctx.stats) if soft and label != "1 serving": label, why = "1 serving", "serving_check_" + soft[0] st[f"label_{why}"] += 1 portions = finalize_portions([(label, amt, 0, 0)], unit, ctx.stats, "B", strict=False) else: st["portion_none_bad_serving_size"] += 1 mc = clean_ws(mcountry or fmc) country = COUNTRY.get(mc.lower(), mc.lower()) if mc else "" rec = make_record("B", fid, name, brand, kcal, unit, portions, bc, False, pub, [country] if country else [], clean_ws(cat)) p = prev.get(bc) if p is not None: pk, _ = pick_energy(nutr.get(p[1], {}), (1008, 2048, 2047, 1062)) if pk is not None and unit_of.get(p[2]) == unit: st["gtin_with_previous_version_same_unit"] += 1 if abs(pk - kcal) > max(0.30 * max(pk, kcal), 20): # USDA's two most recent label records for this GTIN disagree on energy by >30%: # the value is doubtful (mostly serving-size scaling errors), so drop it st["drop_kcal_conflicts_with_previous_version"] += 1 ctx.version_conflicts.append({"id": rec["id"], "barcode": bc, "name": name, "brand": brand, "kcal": rec["kcal"], "unit": unit, "date": pub, "previous_fdc_id": int(p[1]), "previous_kcal": round(pk, 1), "previous_date": p[0][0], "dropped": True}) continue ctx.qa(rec, n, atwater(n)) recs.append(rec) recs.sort(key=lambda r: int(r["id"][2:])) return recs # ---------------------------------------------------------------- main def validate(rec): assert re.fullmatch(r"[A-Z]:\S+", rec["id"]), rec assert 2 <= len(rec["name"]) <= 120, rec assert rec["name"] == rec["name"].strip() and " " not in rec["name"], rec assert 0 <= rec["kcal"] <= 900, rec assert rec["unit"] in ("g", "ml"), rec assert len(rec["portions"]) <= 6, rec for lab, amt in rec["portions"]: assert lab and 0 < amt <= 5000, rec if rec["barcode"]: assert rec["barcode"].isdigit() and 6 <= len(rec["barcode"]) <= 14 and len(rec["barcode"]) != 12, rec def write_jsonl(path, recs): tmp = path + ".tmp" try: with open(tmp, "w", encoding="utf-8", newline="\n") as fh: for r in recs: r.pop("_key", None) validate(r) fh.write(json.dumps(r, ensure_ascii=False, separators=(",", ":")) + "\n") except BaseException: os.remove(tmp) raise os.replace(tmp, path) def main(): ap = argparse.ArgumentParser() ap.add_argument("--root", default=os.path.dirname(os.path.dirname(os.path.abspath(__file__)))) ap.add_argument("--skip-branded", action="store_true") args = ap.parse_args() ctx = Ctx(args.root) out_dir = os.path.join(args.root, "normalized") qa_dir = os.path.join(args.root, "qa") os.makedirs(out_dir, exist_ok=True) os.makedirs(qa_dir, exist_ok=True) f = build_foundation(ctx) s = build_csv_generic(ctx, "S", ctx.one("sr_legacy/FoodData_Central_sr_legacy_food_csv_*")) nn = build_csv_generic(ctx, "N", ctx.one("fndds/FoodData_Central_survey_food_csv_*")) generic, dropped = dedupe_generic(f + s + nn, ctx) generic.sort(key=lambda r: (SRC_RANK[r["src"]], int(r["id"][2:]))) write_jsonl(os.path.join(out_dir, "usda_generic.jsonl"), generic) with open(os.path.join(qa_dir, "usda_generic_dedupe.jsonl"), "w", encoding="utf-8") as fh: for x in dropped: fh.write(json.dumps(x, ensure_ascii=False) + "\n") branded = [] if args.skip_branded else build_branded(ctx) if not args.skip_branded: write_jsonl(os.path.join(out_dir, "usda_branded.jsonl"), branded) if not args.skip_branded: with open(os.path.join(qa_dir, "usda_branded_version_conflicts.jsonl"), "w", encoding="utf-8") as fh: for x in sorted(ctx.version_conflicts, key=lambda x: x["id"]): fh.write(json.dumps(x, ensure_ascii=False) + "\n") with open(os.path.join(qa_dir, "usda_branded_serving_drops.jsonl"), "w", encoding="utf-8") as fh: for x in sorted(ctx.serving_drops, key=lambda x: int(x["id"][2:])): fh.write(json.dumps(x, ensure_ascii=False) + "\n") with open(os.path.join(qa_dir, "usda_atwater_flags.jsonl"), "w", encoding="utf-8") as fh: for x in sorted(ctx.flags, key=lambda x: x["id"]): fh.write(json.dumps(x, ensure_ascii=False) + "\n") counts = collections.Counter(r["src"] for r in generic + branded) rnd = random.Random(20261115) samples = {} for src in ("F", "S", "N", "B"): pool = [r for r in (branded if src == "B" else generic) if r["src"] == src] samples[src] = [json.dumps(r, ensure_ascii=False, separators=(",", ":")) for r in rnd.sample(pool, min(10, len(pool)))] port = collections.Counter() for r in generic + branded: port[(r["src"], "with_portions" if r["portions"] else "no_portions")] += 1 stats = {"build_date": BUILD_DATE, "counts": dict(counts), "meta": ctx.meta, "stats": {k: dict(v) for k, v in ctx.stats.items()}, "energy_path": {k: dict(v) for k, v in ctx.energy_path.items()}, "atwater": {k: dict(v) for k, v in ctx.atwater.items()}, "portions": {f"{a}:{b}": v for (a, b), v in sorted(port.items())}, "examples": dict(EXAMPLES), "samples": samples} with open(os.path.join(qa_dir, "usda_stats.json"), "w", encoding="utf-8") as fh: json.dump(stats, fh, ensure_ascii=False, indent=1) print(json.dumps({k: stats[k] for k in ("counts", "stats", "energy_path", "atwater", "portions", "examples")}, indent=1, ensure_ascii=False)) if __name__ == "__main__": main()