#!/usr/bin/env python3 """Normalize the Open Food Facts full CSV export into FoodIndex/normalized/off.jsonl. Usage: python3 -I normalize_off.py [--stats ] [--rejects ] [--limit N] Input: the nightly export https://static.openfoodfacts.org/data/en.openfoodfacts.org.products.csv.gz (UTF-8, tab separated, one product per line, 211 columns in the 2026-11 export). Output: one JSON object per line (see FoodIndex/CONTRACT.md and the stage brief): {"id":"O:","name":…,"brand":…,"kcal":…,"unit":"g"|"ml","portions":[[label,amount]], "barcode":…,"src":"O","generic":false,"pop":|null,"date":"YYYY-MM-DD"|null, "countries":[…],"lang":null,"category":…} Parsing: the file is read as raw bytes and split on '\n' and '\t'. Inspection of the whole export showed that every line has exactly 211 fields (the exporter strips tabs/newlines from values), and that every value containing a double quote is written CSV-style ("...", inner quotes doubled). Values are therefore un-quoted only when they start and end with '"'. A generic csv.reader is NOT used because a stray quote would make it swallow following lines. Every rule below drops the record when data is doubtful. The first failing rule is counted in the stats. Python 3 stdlib only. """ import argparse import datetime as dt import gzip import html import json import math import re import sys import os import unicodedata from collections import Counter sys.path.insert(0, os.path.dirname(os.path.abspath(__file__))) # python3 -I does not add the script's directory import plausibility # noqa: E402 # ---------------------------------------------------------------------------------------------------------------------- # small helpers def unq(v): """Undo the exporter's CSV-style quoting of a single field.""" if len(v) >= 2 and v[0] == '"' and v[-1] == '"': return v[1:-1].replace('""', '"') return v def num(v): """Parse a numeric cell. Returns (ok, value): ok=False for garbage, value=None for empty.""" v = v.strip() if not v: return True, None try: x = float(v) except ValueError: return False, None if math.isnan(x) or math.isinf(x): return False, None return True, x def collapse(s): return ' '.join(s.split()) def strip_accents_lower(s): s = unicodedata.normalize('NFKD', s) s = ''.join(ch for ch in s if unicodedata.category(ch) != 'Mn') return s.lower() def clean_text(s): """Undo (possibly repeated) HTML escaping, drop backslash escapes and control characters, collapse spaces.""" for _ in range(3): u = html.unescape(s) if u == s: break s = u s = s.replace('\\', '') s = ''.join(' ' if unicodedata.category(ch) == 'Cc' else ch for ch in s) return collapse(s) def has_letter(s): return any(unicodedata.category(ch).startswith('L') for ch in s) # ---------------------------------------------------------------------------------------------------------------------- # barcodes (CONTRACT.md "Barcode normalization") def normalize_barcode(code): d = re.sub(r'[^0-9]', '', code) # ASCII 0-9 only (\D would keep other Unicode decimal digits) if len(d) == 12: d = '0' + d elif len(d) == 14 and d[0] == '0': d = d[1:] if not (6 <= len(d) <= 14): return None return d def gs1_check_ok(d): """GS1 mod-10 check digit (valid for GTIN-8/12/13/14 and for any left-zero-padded form).""" s = 0 for i, ch in enumerate(reversed(d[:-1])): s += int(ch) * (3 if i % 2 == 0 else 1) return (10 - s % 10) % 10 == int(d[-1]) def upce_to_upca(d): """Expand an 8-digit UPC-E (number system 0/1) to 12-digit UPC-A, or None.""" if len(d) != 8 or d[0] not in '01': return None ns, m, chk = d[0], d[1:7], d[7] last = m[5] if last in '012': body = m[0:2] + last + '0000' + m[2:5] elif last == '3': body = m[0:3] + '00000' + m[3:5] elif last == '4': body = m[0:4] + '00000' + m[4] else: body = m[0:5] + '0000' + last return ns + body + chk def barcode_check_ok(d): if gs1_check_ok(d): return True if len(d) == 8: a = upce_to_upca(d) if a and gs1_check_ok(a): return True return False def restricted_circulation(d): """GS1 restricted-circulation / in-store numbers (prefix 02, 04, 20-29 on the 13-digit form; 2 on EAN-8).""" if len(d) == 13: return d[0] == '2' or d[:2] in ('02', '04') if len(d) == 8: return d[0] == '2' return False # ---------------------------------------------------------------------------------------------------------------------- # data quality tags: drop when OFF itself flags energy or macro values as impossible/inconsistent. # Only the "as sold" sets matter (the *_100g columns are as-sold values); "prepared" sets are ignored. # Micronutrient-only "over-105" tags (vitamin-a, calcium, ...) are ignored: they do not affect kcal. MACRO_NUTS = ('energy', 'fat', 'saturated-fat', 'monounsaturated-fat', 'polyunsaturated-fat', 'trans-fat', 'carbohydrates', 'carbohydrates-total', 'sugars', 'added-sugars', 'starch', 'polyols', 'fiber', 'soluble-fiber', 'insoluble-fiber', 'proteins', 'salt', 'sodium', 'alcohol') _mac = '|'.join(re.escape(x) for x in sorted(MACRO_NUTS, key=len, reverse=True)) DQ_DROP_RE = re.compile( r'^en:(?:nutrition-(?:packaging|manufacturer)-as-sold-(?:100g|100ml|serving)-)?(?:' r'energy-value-in-(?:kcal|kj)-does-not-match-value-(?:in-kj|in-kcal|computed-from-other-nutrients)' r'|energy-value-in-kcal-greater-than-in-kj' r'|energy-value-in-kcal-and-kj-are-reversed' r'|(?:nutrition-)?value-over-(?:105|1000|3800|3911)-(?:' + _mac + r')' r'|(?:nutrition-)?value-total-over-(?:105|1000)' r'|(?:nutrition-)?values-are-all-identical' r'|(?:nutrition-)?saturated-fat-greater-than-fat' r'|nutrition-value-negative-(?:' + _mac + r')' r'|nutrition-data-per-serving-missing-serving-size' r'|nutrition-data-per-serving-serving-quantity-is-0' r')$') # Older un-prefixed spellings ("en:nutrition-value-over-105-fat", "en:energy-value-in-kcal-does-not-match-value-in-kj") # are covered by the optional prefix group; "nutrition-value-..." keeps its own "nutrition-" literal. DQ_DROP_RE2 = re.compile(r'^en:nutrition-(?:value-over-(?:105|1000|3800|3911)-(?:' + _mac + r')|value-total-over-(?:105|1000))$') DQ_PORTION_BAD = {'en:serving-size-is-missing-digits', 'en:nutrition-data-per-serving-serving-quantity-is-0', 'en:nutrition-data-per-serving-missing-serving-size'} # Since 2026-11 (QA): OFF's data-quality tags are NOT dropped on any more. Many describe the per-serving set, a # serving size, or a nutrition set (packaging/manufacturer) other than the exported _100g values (Nutella # 3017620422003 carried "saturated-fat-greater-than-fat" for a set whose exported values are fine). The pipeline's own # checks on the exported values decide instead (kcal/kJ, macros <= 100, sum <= 105, saturated fat <= fat, all-identical # values, Atwater). When OFF flags the per-100 g / per-100 ml energy as not matching the other nutrients, the Atwater # check is run with a tight tolerance (max(12 %, 15 kcal)) instead of the usual max(35 %, 40 kcal). DQ_ENERGY_100_RE = re.compile( r'^en:(?:nutrition-(?:packaging|manufacturer)-as-sold-(?:100g|100ml)-)?' r'energy-value-in-(?:kcal|kj)-does-not-match-value-computed-from-other-nutrients$') # Physically impossible energy density in ml: pure fat is ~830 kcal/100 ml (900 kcal/100 g x 0.92 g/ml). ML_MAX_KCAL = 845.0 FAT_DENSITY = 0.92 FAT_TAGS = {'en:fats', 'en:vegetable-oils', 'en:olive-oils', 'en:extra-virgin-olive-oils', 'en:vegetable-fats', 'en:animal-fats', 'en:butters', 'en:spreadable-fats', 'en:margarines', 'en:dairy-spreads', 'en:coconut-oils', 'en:sunflower-oils', 'en:rapeseed-oils', 'en:ghee'} NA_COUNTRY_SET = {'united-states', 'canada', 'puerto-rico'} DRINKISH_RE = re.compile(r'\b(drink|juice|soda|water|tea|coffee|milk|beverage|lemonade|smoothie|shake|kombucha|broth|' r'stock|soup|beer|wine|cola|kefir|yogurt|yoghurt)\b') # ---------------------------------------------------------------------------------------------------------------------- # units and categories NUM_RE = r'(\d+(?:[.,]\d+)?)' MASS_UNIT = {'g': 1.0, 'gr': 1.0, 'grs': 1.0, 'gm': 1.0, 'gms': 1.0, 'gram': 1.0, 'grams': 1.0, 'gramm': 1.0, 'gramme': 1.0, 'grammes': 1.0, 'gramos': 1.0, 'gramo': 1.0, 'grammi': 1.0, 'grammo': 1.0, 'gramas': 1.0, 'grama': 1.0, 'grammas': 1.0, 'kg': 1000.0, 'mg': 0.001} IMP_MASS_UNIT = {'oz': 28.349523125, 'ounce': 28.349523125, 'ounces': 28.349523125, 'onz': 28.349523125, 'onza': 28.349523125, 'onzas': 28.349523125, 'lb': 453.59237, 'lbs': 453.59237} VOL_UNIT = {'ml': 1.0, 'mℓ': 1.0, 'cl': 10.0, 'dl': 100.0, 'l': 1000.0, 'lt': 1000.0, 'ltr': 1000.0, 'litre': 1000.0, 'litres': 1000.0, 'liter': 1000.0, 'liters': 1000.0, 'litro': 1000.0, 'litros': 1000.0} FLOZ = 29.5735295625 AMOUNT_RE = re.compile(NUM_RE + r'\s*(fl\.?\s*oz\.?|fluid\s+ounces?|[a-zℓ]+)\b\.?', re.I) def amounts(text): """Return list of (value, family, kind, span) for every ' ' in text. family: 'g' or 'ml'; kind: 'metric' or 'imperial'.""" out = [] for m in AMOUNT_RE.finditer(text): v = float(m.group(1).replace(',', '.')) u = re.sub(r'\s+', ' ', m.group(2).lower()).rstrip('.') if u.startswith('fl') or u.startswith('fluid'): out.append((v * FLOZ, 'ml', 'imperial', m.span())) elif u in MASS_UNIT: out.append((v * MASS_UNIT[u], 'g', 'metric', m.span())) elif u in VOL_UNIT: out.append((v * VOL_UNIT[u], 'ml', 'metric', m.span())) elif u in IMP_MASS_UNIT: out.append((v * IMP_MASS_UNIT[u], 'g', 'imperial', m.span())) return out def tagset(v): return {t for t in v.split(',') if t} # Categories that are drinks sold by volume (exact OFF taxonomy tags). BEV_TAGS = {'en:beverages', 'en:alcoholic-beverages', 'en:non-alcoholic-beverages', 'en:waters', 'en:milks', 'en:plant-based-milk-alternatives', 'en:milk-substitutes', 'en:fruit-juices', 'en:juices-and-nectars', 'en:fruit-based-beverages', 'en:sodas', 'en:carbonated-drinks', 'en:soft-drinks', 'en:colas', 'en:beers', 'en:wines', 'en:ciders', 'en:liqueurs', 'en:hard-liquors', 'en:distilled-beverages', 'en:energy-drinks', 'en:iced-teas', 'en:tea-based-beverages', 'en:coffee-drinks', 'en:drinkable-yogurts', 'en:dairy-drinks', 'en:fermented-milk-drinks', 'en:flavoured-milks', 'en:chocolate-milks', 'en:cereal-based-drinks', 'en:oat-based-drinks', 'en:soy-based-drinks', 'en:nut-based-drinks', 'en:nectars', 'en:fruit-nectars', 'en:smoothies', 'en:lemonades'} # Beverage *preparations* or dry goods that live under "beverages" but are sold by weight. DRY_BEV_TAGS = {'en:beverages-preparations', 'en:beverage-preparations', 'en:instant-beverages', 'en:dehydrated-beverages', 'en:dried-products-to-be-rehydrated', 'en:coffees', 'en:ground-coffees', 'en:coffee-beans', 'en:coffee-capsules', 'en:coffee-pods', 'en:instant-coffees', 'en:teas', 'en:tea-bags', 'en:tea-leaves', 'en:green-teas', 'en:black-teas', 'en:herbal-teas', 'en:cocoa-and-chocolate-powders', 'en:cocoa-powders', 'en:chocolate-powders', 'en:powdered-drinks', 'en:drink-mixes', 'en:powdered-milks', 'en:milk-powders', 'en:infant-formulas', 'en:protein-powders'} # Low-density foods where "per 100 ml" and "per 100 g" differ by up to ~2x (ice cream overrun, whipped cream, mousse). # With only volume information we cannot tell which basis the label used: drop. LOWDENS_TAGS = {'en:ice-creams-and-sorbets', 'en:ice-creams', 'en:frozen-desserts', 'en:ice-cream-tubs', 'en:ice-cream-bars', 'en:ice-cream-cones', 'en:ice-cream-sandwiches', 'en:sorbets', 'en:chantilly', 'en:mousses', 'en:dessert-mousses', 'en:frozen-yogurts'} # North-American labels state nutrition per serving only; when that serving is a volume, OFF's per-100 values were # computed per 100 ml, so the basis is known even for ice cream. NA_COUNTRIES = {'united-states', 'canada', 'puerto-rico'} LOWDENS_NAME_RE = re.compile(r'\b(ice ?cream|gelato|(? "(about 15 chips)" s = re.sub(r'\s*[/,;:\-=]+\s*\)', ')', s) s = collapse(s) s = s.strip(' ,;:-/|=*+.') # unbalanced parentheses: drop them if s.count('(') != s.count(')'): s = s.replace('(', ' ').replace(')', ' ') s = collapse(s).strip(' ,;:-/|=*+.') if s.startswith('(') and s.endswith(')') and s.count('(') == 1: s = s[1:-1].strip() letters = [ch for ch in s if ch.isalpha()] if len(letters) >= 2 and all(ch.isupper() for ch in letters): s = s.lower() # USDA "household serving" codes that OFF imported verbatim: ONZ = ounce (weight), OZA = fluid ounce s = re.sub(r'(?i)(\d)\s*oza\b\.?', r'\1 fl oz', s) s = re.sub(r'(?i)(\d)\s*onz\b\.?', r'\1 oz', s) s = re.sub(r'(?i)\bpc\.(?=\s|$)', 'piece', s) # leading decimal fractions written by the USDA import: "0.667 cup" -> "2/3 cup" (not for oz/lb/fl oz amounts) m = re.match(r'^(\d*)\.(\d+)\s+(?!(?:fl\b|oz\b|ounces?\b|lbs?\b))', s, re.I) if m: frac = FRACTIONS.get(m.group(2)) if frac: whole = m.group(1).lstrip('0') s = ((whole + ' ') if whole else '') + frac + ' ' + s[m.end():] # leftovers of multi-part serving texts s = re.sub(r'(?i)[,;]?\s*(?:per container|servings? per container)\b.*$', '', s) s = re.sub(r'(?i)[,;(]?\s*\b(?:about|approx\.?|approximately|ca\.?|env\.?|environ|aprox\.?|circa)\s*\)?$', '', s) s = collapse(s).strip(' ,;:-/|=*+.') if '|' in s or not balanced(s) or re.search(r'(?i)\d\s*(?:g|gr|grams?|kg|mg|ml|cl|dl|l)(?![a-z])', s): return '' if re.match(r'^0?\.\d', s): # leftover decimal fraction we could not name ("0.067 de portion") return '' return s FRACTIONS = {'25': '1/4', '33': '1/3', '333': '1/3', '3333': '1/3', '5': '1/2', '50': '1/2', '66': '2/3', '667': '2/3', '67': '2/3', '6667': '2/3', '75': '3/4', '125': '1/8', '375': '3/8', '625': '5/8', '875': '7/8', '2': '1/5', '1': '1/10', '083': '1/12', '0833': '1/12', '042': '1/24', '0417': '1/24', '167': '1/6', '1667': '1/6', '17': '1/6', '0625': '1/16', '063': '1/16', '143': '1/7', '111': '1/9', '067': '1/15', '0667': '1/15', '071': '1/14', '077': '1/13', '091': '1/11', '056': '1/18', '05': '1/20', '04': '1/25', '033': '1/30', '6': '3/5', '4': '2/5', '8': '4/5'} def balanced(s): depth = 0 for ch in s: if ch == '(': depth += 1 elif ch == ')': depth -= 1 if depth < 0: return False return depth == 0 def make_portion(serving_text, sq_ok, sq, unit, st): """Return [label, amount] or None. Counts reasons in st (Counter).""" t = collapse(serving_text) if not t and sq is None: return None am = amounts(t) fam = [a for a in am if a[1] == unit] other = [a for a in am if a[1] != unit] if not sq_ok: st['portion_skip_bad_serving_quantity'] += 1 return None if len(fam) >= 2: vals = [a[0] for a in fam] if max(vals) > 1.05 * min(vals): # "8 ml (8 fl oz)", "28 g (99 oz)": the text contradicts itself, so no amount can be trusted st['portion_skip_text_amounts_disagree'] += 1 return None amount = None if fam: cands = [a[0] for a in fam] if sq is not None and sq > 0: if any(abs(sq - c) <= max(0.03 * c, 0.6) for c in cands): amount = sq else: st['portion_skip_quantity_text_mismatch'] += 1 return None else: metric = [a[0] for a in fam if a[2] == 'metric'] if len(set(metric)) == 1: amount = metric[0] else: st['portion_skip_no_quantity'] += 1 return None else: if sq is not None and sq > 0: if other: st['portion_skip_unit_family_mismatch'] += 1 else: st['portion_skip_text_without_unit'] += 1 else: st['portion_skip_no_amount'] += 1 return None if not (0 < amount <= 5000): st['portion_skip_amount_out_of_range'] += 1 return None metric_spans = [a[3] for a in am if a[2] == 'metric'] label = clean_label(t, metric_spans) low = strip_accents_lower(label) if (not label or not has_letter(label) or len(label) > 40 or low in GENERIC_SERVING_LABELS or strip_accents_lower(label) in {strip_accents_lower(x) for x in GENERIC_SERVING_LABELS} or re.fullmatch(r'(per|pour|por|pro|je|for)\b.*', low)): label = '1 serving' st['portion_label_generic'] += 1 amount = round(amount, 1) if amount <= 0: st['portion_skip_amount_out_of_range'] += 1 return None if abs(amount - 100.0) < 0.05 and label == '1 serving': st['portion_skip_plain_100'] += 1 return None st['portion_kept'] += 1 return [label, amount] # ---------------------------------------------------------------------------------------------------------------------- def main(): ap = argparse.ArgumentParser() ap.add_argument('src') ap.add_argument('out') ap.add_argument('--stats') ap.add_argument('--rejects') ap.add_argument('--rejects-per-reason', type=int, default=40) ap.add_argument('--limit', type=int, default=0) args = ap.parse_args() drop = Counter() info = Counter() pst = Counter() dq_seen_drop = Counter() rej_out = open(args.rejects, 'w', encoding='utf-8') if args.rejects else None rej_n = Counter() best = {} # barcode -> (pop, mtime, json_line) now_t = dt.datetime.now(dt.timezone.utc).timestamp() + 86400 def reject(reason, row=None, extra=None): drop[reason] += 1 if rej_out is not None and rej_n[reason] < args.rejects_per_reason and row is not None: rej_n[reason] += 1 rec = {'reason': reason, 'code': row[H['code']], 'name': unq(row[H['product_name']])[:120], 'kcal': row[H['energy-kcal_100g']], 'kj': row[H['energy_100g']], 'fat': row[H['fat_100g']], 'carb': row[H['carbohydrates_100g']], 'prot': row[H['proteins_100g']], 'fiber': row[H['fiber_100g']], 'alc': row[H['alcohol_100g']], 'qty': unq(row[H['quantity']]), 'serving': unq(row[H['serving_size']]), 'cat': row[H['main_category']], 'countries': row[H['countries_tags']][:60]} if extra: rec['extra'] = extra rej_out.write(json.dumps(rec, ensure_ascii=False) + '\n') rows = 0 with gzip.open(args.src, 'rb') as f: header = f.readline().decode('utf-8').rstrip('\r\n').split('\t') global H H = {c: i for i, c in enumerate(header)} need = ['code', 'creator', 'product_name', 'brands', 'countries_tags', 'serving_size', 'serving_quantity', 'energy-kcal_100g', 'energy_100g', 'energy-kj_100g', 'fat_100g', 'carbohydrates_100g', 'carbohydrates-total_100g', 'proteins_100g', 'fiber_100g', 'alcohol_100g', 'polyols_100g', 'erythritol_100g', 'unique_scans_n', 'last_modified_t', 'data_quality_errors_tags', 'quantity', 'product_quantity', 'categories_tags', 'main_category', 'main_category_en', 'no_nutrition_data'] missing = [c for c in need if c not in H] if missing: sys.exit('missing columns: %s' % missing) ncol = len(header) for raw in f: rows += 1 if args.limit and rows > args.limit: rows -= 1 break try: line = raw.decode('utf-8') except UnicodeDecodeError: reject('bad_utf8') continue line = line.rstrip('\n').rstrip('\r') row = line.split('\t') if len(row) != ncol: reject('bad_field_count') continue g = lambda c: unq(row[H[c]]) # --- barcode code = g('code').strip() if not code or re.search(r'[^0-9]', code): reject('barcode_not_digits', row) continue bc = normalize_barcode(code) if bc is None: reject('barcode_bad_length', row) continue if len(bc) < 8 or len(bc.lstrip('0')) < 6: reject('barcode_internal_short', row) continue # --- nutrition declared absent if g('no_nutrition_data').strip().lower() in ('on', 'true', '1', 'yes'): reject('no_nutrition_data_flag', row) continue # --- name name = clean_text(g('product_name')) if not name: reject('name_missing', row) continue if '\ufffd' in name: reject('name_garbled_encoding', row) continue if len(name) > 120: cut = name[:121] pos = cut.rfind(', ') if pos < 40: pos = cut.rfind(' ') name = cut[:pos].rstrip(' ,;:-/(') if pos >= 40 else '' if name.count('(') != name.count(')'): name = name.replace('(', '').replace(')', '') name = collapse(name) if not name: reject('name_length', row) continue info['name_shortened_over_120'] += 1 if len(name) < 2: reject('name_length', row) continue if not has_letter(name): reject('name_no_letters', row) continue if strip_accents_lower(name) in PLACEHOLDER_NAMES or name == code or name.lstrip('0') == code.lstrip('0'): reject('name_placeholder', row) continue # --- OFF data quality flags: counted only (see DQ_ENERGY_100_RE); the record is judged by our own checks dqt = [t for t in g('data_quality_errors_tags').split(',') if t] bad = [t for t in dqt if DQ_DROP_RE.match(t) or DQ_DROP_RE2.match(t)] for t in bad: dq_seen_drop[t] += 1 dq_energy_100 = any(DQ_ENERGY_100_RE.match(t) for t in dqt) if bad: info['dq_tagged_records_checked_by_own_rules'] += 1 # --- energy ok1, kcal_raw = num(g('energy-kcal_100g')) ok2, e_100 = num(g('energy_100g')) ok3, kj_raw = num(g('energy-kj_100g')) if not (ok1 and ok2 and ok3): reject('energy_unparseable', row) continue kjs = [x for x in (kj_raw, e_100) if x is not None] if kcal_raw is None and not kjs: reject('energy_missing', row) continue def disagree(a, b): d = abs(a - b) return d > 0.15 * max(a, b) and d > 20 if kcal_raw is not None: kcal = kcal_raw if any(disagree(kcal, x / 4.184) for x in kjs): reject('energy_kcal_kj_mismatch', row) continue info['energy_kcal_and_kj' if kjs else 'energy_kcal_only'] += 1 else: kcal = kjs[0] / 4.184 if len(kjs) == 2 and disagree(kjs[0] / 4.184, kjs[1] / 4.184): reject('energy_kcal_kj_mismatch', row) continue info['energy_from_kj_only'] += 1 if kcal < 0: reject('kcal_negative', row) continue if kcal > 900: reject('kcal_over_900', row) continue # --- macros vals = {} badnum = False for k in ('fat_100g', 'carbohydrates_100g', 'carbohydrates-total_100g', 'proteins_100g', 'fiber_100g', 'alcohol_100g', 'polyols_100g', 'erythritol_100g', 'saturated-fat_100g', 'sugars_100g'): ok, v = num(g(k)) if not ok: badnum = True vals[k] = v if badnum: reject('macro_unparseable', row) continue fat, carb, carb_tot = vals['fat_100g'], vals['carbohydrates_100g'], vals['carbohydrates-total_100g'] prot, fib, alc = vals['proteins_100g'], vals['fiber_100g'], vals['alcohol_100g'] pol, ery = vals['polyols_100g'], vals['erythritol_100g'] sfat, sug = vals['saturated-fat_100g'], vals['sugars_100g'] present = [x for x in (fat, carb, carb_tot, prot, fib, alc, pol, ery, sfat, sug) if x is not None] if any(x < 0 for x in present): reject('macro_negative', row) continue if any(x > 100 for x in (fat, carb, carb_tot, prot, fib, pol, ery, sfat, sug) if x is not None) or \ (alc is not None and alc > 100): reject('macro_over_100', row) continue if sfat is not None and fat is not None and sfat > fat + 0.5: info['saturated_fat_greater_than_fat_kept'] += 1 # a fat-detail error; energy is checked below if fat is not None and fat >= 1 and fat == carb == prot == sug == sfat: reject('values_all_identical', row) # placeholder entry: every field the same continue msum = sum(x for x in (fat, carb if carb is not None else carb_tot, prot) if x is not None) if msum > 105: reject('macro_sum_over_105', row) continue countries = [t[3:] for t in g('countries_tags').split(',') if t.startswith('en:') and len(t) > 3] na_label = any(c in ('united-states', 'canada') for c in countries) # --- Atwater check pre_cats = g('categories_tags') pre_lname = strip_accents_lower(name) if pol is None and (ERYTHRITOL_RE.search(pre_lname) or SWEETENER_RE.search(pre_lname) or any(t in pre_cats for t in ('en:sugar-substitutes', 'en:tabletop-sweeteners', 'en:table-top-sweeteners'))): carb_factors = (4.0, 2.4, 0.0) elif pol is None and (POLYOL_RE.search(pre_lname) or 'sugar-free' in pre_cats): carb_factors = (4.0, 2.4) else: carb_factors = (4.0,) c_used, c_incl_fiber = carb, None if c_used is None and carb_tot is not None: c_used, c_incl_fiber = carb_tot, True if fat is not None and c_used is not None and prot is not None: fb = fib or 0.0 alc_kcal = 7.0 * 0.789 * (alc or 0.0) # alcohol_100g is % vol: g ethanol = 0.789 * %vol pol_adj = 0.0 if pol: pol_adj -= 1.6 * min(pol, c_used) # polyols ~2.4 kcal/g instead of 4 if ery: pol_adj -= 2.4 * min(ery, pol if pol else ery) if pol else 4.0 * min(ery, c_used) base = 4.0 * prot + 9.0 * fat + alc_kcal + pol_adj tol = max(0.12 * kcal, 15.0) if dq_energy_100 else max(0.35 * kcal, 40.0) ests = [] for cf in carb_factors: est_excl = base + cf * c_used + 2.0 * fb # carbs exclude fiber (EU style) est_incl = base + cf * max(c_used - fb, 0.0) + 2.0 * fb # carbs include fiber (US/CA style) if c_incl_fiber: ests.append(est_incl) elif fib is None: ests.append(est_excl) else: # labeling convention is regional and many products are multi-country: accept either one ests += [est_incl, est_excl] if na_label else [est_excl, est_incl] if all(abs(kcal - e) > tol for e in ests): reject('atwater_mismatch_off_flagged' if dq_energy_100 else 'atwater_mismatch', row, {'est': [round(e, 1) for e in ests]}) continue info['atwater_checked'] += 1 elif dq_energy_100: reject('off_energy_flag_and_macros_incomplete', row) # cannot clear OFF's own energy flag continue else: # Incomplete macro set: the macros that ARE present still give a lower bound for the energy. lb = 9.0 * (fat or 0.0) + 4.0 * (prot or 0.0) + 7.0 * 0.789 * (alc or 0.0) cc = c_used if c_used is not None else 0.0 lb += min(carb_factors) * max(cc - (fib or 0.0) - (pol or 0.0), 0.0) if lb - kcal > max(0.35 * kcal, 40.0): reject('atwater_partial_lower_bound', row, {'lower_bound': round(lb, 1)}) continue info['atwater_unchecked_missing_macros'] += 1 kcal = round(kcal + 0.0, 1) cats = tagset(g('categories_tags')) lname = strip_accents_lower(name) # --- zero kcal only where believable if kcal == 0.0: zero_ok = bool(cats & ZERO_TAGS) zname = lname for a, b in ZERO_NEG_SAFE: zname = zname.replace(a, b) if not zero_ok and not ZERO_NEG_RE.search(zname): if ZERO_NAME_RE.search(lname): zero_ok = True elif ZERO_DIET_RE.search(lname) and (DRINK_RE.search(lname) or (cats & BEV_TAGS)): zero_ok = True if not zero_ok: reject('kcal_zero_not_plausible', row) continue info['kcal_zero_kept'] += 1 # --- unit serving_text = collapse(g('serving_size')) qty_text = collapse(g('quantity')) sa, qa = amounts(serving_text), amounts(qty_text) serv_mass = any(a[1] == 'g' for a in sa) serv_vol = any(a[1] == 'ml' for a in sa) qty_mass = any(a[1] == 'g' for a in qa) qty_vol = any(a[1] == 'ml' for a in qa) bev = bool(cats & BEV_TAGS) and not (cats & DRY_BEV_TAGS) if serv_mass and not serv_vol: unit, why = 'g', 'serving_mass' elif serv_vol and not serv_mass: unit, why = 'ml', 'serving_volume' elif serv_mass and serv_vol: unit, why = ('ml', 'serving_both_beverage') if bev else ('g', 'serving_both') elif qty_vol and not qty_mass: unit, why = 'ml', 'quantity_volume' elif qty_mass and not qty_vol: unit, why = 'g', 'quantity_mass' elif bev: unit, why = 'ml', 'beverage_category' else: unit, why = 'g', 'default' info['unit_reason_' + why] += 1 # --- drinks: physically implausible energy or alcohol for the category (catches per-bottle values, # kJ typed as kcal, and the recurring bogus "660.1 kcal / 94.3 % vol" wine and beer entries) if alc is not None and alc > 75: reject('alcohol_implausible', row) continue if alc is not None and alc > 25 and (cats & (WINE_TAGS | BEER_TAGS)) and not (cats & SPIRIT_TAGS): reject('alcohol_implausible', row) continue if unit == 'ml' and not (cats & DRINK_BOUND_EXCL_TAGS) and not DRINK_BOUND_EXCL_NAME_RE.search(lname): if cats & SPIRIT_TAGS: bound = 450 elif cats & (WINE_TAGS | BEER_TAGS): bound = 200 elif cats & {'en:alcoholic-beverages'} or (alc is not None and alc >= 1.2): bound = 450 else: # non-alcoholic: skip when the stated serving is a small volume (concentrate, creamer, shot) small = [a[0] for a in amounts(collapse(g('serving_size'))) if a[1] == 'ml' and a[0] < 100] nb = [b for ts, b in DRINK_BOUNDS if cats & ts and ts is not WINE_TAGS and ts is not BEER_TAGS and ts is not SPIRIT_TAGS and b < 450] + ([200] if cats & MILKDRINK_TAGS else []) bound = max(nb) if nb and not small else None if bound is not None and kcal > bound: reject('kcal_implausible_for_drink_category', row, {'bound': bound}) continue lowdens = bool(cats & LOWDENS_TAGS) or (bool(LOWDENS_NAME_RE.search(lname)) and not (cats & BEV_TAGS)) if unit == 'ml' and lowdens: if why == 'serving_volume' and countries and set(countries) <= NA_COUNTRIES: info['unit_ml_low_density_kept_na_per_serving'] += 1 else: reject('unit_ambiguous_low_density', row) continue # --- energy-density bands for the category (per-serving values typed as per 100 g, see plausibility.py) band = plausibility.check(name, kcal, unit, off_tags=cats, brand=clean_text(g('brands')), name_only=not cats) # English head-noun patterns only (plausibility.py) if band: reject('density_band_' + band[0], row, {'band': list(band)}) continue # --- North-American labels state nutrition per serving; OFF's per-100 values were computed from the # serving quantity. A serving that the label's own text contradicts, or one over 1 kg of a food, # makes those per-100 values wrong by the same factor (Atwater cannot see it). sq_ok0, sq0 = num(g('serving_quantity')) if sq_ok0 and sq0 is None: met = [a[0] for a in amounts(serving_text) if a[1] == unit and a[2] == 'metric'] sq0 = met[0] if len(set(met)) == 1 else None per_serving_label = (g('creator').strip() == 'usda-ndb-import' or (countries and set(countries) <= NA_COUNTRY_SET)) if per_serving_label and sq_ok0 and sq0 and sq0 > 0 and not abs(sq0 - 100.0) < 1e-6: drinkish = unit == 'ml' or bool(cats & BEV_TAGS) or bool(DRINKISH_RE.search(lname)) if unit == 'g' and sq0 >= 1000 and not drinkish: reject('serving_over_1kg_per_serving_label', row, {'serving_quantity': sq0}) continue # (A serving text that contradicts serving_quantity is NOT a reason: in the USDA import it is mostly # a lost decimal point in the household part, "75 ONZ (21 g)", while the grams are right.) # --- impossible energy per 100 ml: the value is per 100 g (fats), or the unit guess was wrong (solids) ml_to_g = None if unit == 'ml' and kcal > ML_MAX_KCAL: unit, ml_to_g = 'g', FAT_DENSITY info['unit_ml_over_845_kcal_to_g'] += 1 elif unit == 'ml' and kcal > 450 and not (cats & (FAT_TAGS | SPIRIT_TAGS)) and \ not re.search(r'\b(oil|huile|aceite|olio|ghee|butter|beurre)\b', lname): unit, ml_to_g = 'g', 0.0 info['unit_ml_over_450_kcal_solid_to_g'] += 1 # --- GS1 check digit (tested after all data checks: this count is what the rule alone costs) if not barcode_check_ok(bc): reject('barcode_bad_check_digit', row) continue # --- portion portions = [] if serving_text or g('serving_quantity').strip(): if any(t in DQ_PORTION_BAD for t in dqt): pst['portion_skip_dq_serving'] += 1 elif ml_to_g == 0.0: pst['portion_skip_unit_changed_to_g'] += 1 else: sq_ok, sq = num(g('serving_quantity')) p = make_portion(serving_text, sq_ok, sq, 'ml' if ml_to_g else unit, pst) if p and ml_to_g: p = [p[0], round(p[1] * ml_to_g, 1)] if p: portions.append(p) # --- remaining fields brand = '' braw = clean_text(g('brands')) if braw: brand = collapse(braw.split(',')[0]).strip(' ,;') if len(brand) > 80 or not has_letter(brand) and not brand.isdigit(): brand = '' okp, popv = num(g('unique_scans_n')) pop = int(popv) if okp and popv is not None and popv >= 0 else None okt, mt = num(g('last_modified_t')) date = None if okt and mt is not None and 946684800 <= mt <= now_t: date = dt.datetime.fromtimestamp(mt, dt.timezone.utc).date().isoformat() else: mt = 0 category = collapse(g('main_category_en')) if category.lower() in ('', 'undefined', 'unknown', 'en:undefined'): category = '' elif re.match(r'^[a-z]{2}:', category): category = category[3:].replace('-', ' ').strip().capitalize() rec = {'id': 'O:' + bc, 'name': name, 'brand': brand, 'kcal': kcal, 'unit': unit, 'portions': portions, 'barcode': bc, 'src': 'O', 'generic': False, 'pop': pop, 'date': date, 'countries': countries, 'lang': None, 'category': category} jl = json.dumps(rec, ensure_ascii=False, separators=(',', ':')) key = (pop if pop is not None else -1, mt) prev = best.get(bc) if prev is not None: if key > prev[0]: best[bc] = (key, jl) drop['duplicate_barcode'] += 1 continue best[bc] = (key, jl) if restricted_circulation(bc): info['kept_restricted_circulation_barcode'] += 1 kept = 0 units = Counter() us = 0 with open(args.out, 'w', encoding='utf-8') as out: for bc, (key, jl) in best.items(): out.write(jl + '\n') kept += 1 r = json.loads(jl) units[r['unit']] += 1 if 'united-states' in r['countries']: us += 1 if r['portions']: info['records_with_portion'] += 1 if r['pop'] is not None: info['records_with_pop'] += 1 if r['brand']: info['records_with_brand'] += 1 if r['category']: info['records_with_category'] += 1 if not r['countries']: info['records_without_countries'] += 1 if rej_out: rej_out.close() stats = {'rows_read': rows, 'kept': kept, 'dropped_total': sum(drop.values()), 'dropped_by_rule': dict(drop.most_common()), 'unit_split': dict(units), 'us_records': us, 'info': dict(sorted(info.items())), 'portions': dict(pst.most_common()), 'dq_tags_causing_drop': dict(dq_seen_drop.most_common())} assert rows == kept + sum(drop.values()), (rows, kept, sum(drop.values())) s = json.dumps(stats, ensure_ascii=False, indent=1) if args.stats: with open(args.stats, 'w', encoding='utf-8') as fs: fs.write(s + '\n') print(s) if __name__ == '__main__': main()