"""Open Food Facts, CoFID 2021 and USDA FoodData Central foods → Wrought food database rows. THIS FILE IS THE PUBLISHED METHOD (ODbL 1.0 §4.6(b)); DevDocs/FoodDatabase/PLAN.md Part B §4-§6, §9. - Open Food Facts (https://world.openfoodfacts.org) is made available under the Open Database License (ODbL) 1.0, and its individual contents under the Database Contents License (DbCL) 1.0. The Wrought food database rows this file derives from Open Food Facts are licensed under the ODbL 1.0, and their contents under the DbCL 1.0. - CoFID 2021 (McCance and Widdowson's Composition of Foods Integrated Dataset, Public Health England) is used under the Open Government Licence v3.0, from the GOV.UK copy only. - USDA FoodData Central (FNDDS and SR Legacy) is CC0 1.0. The method is a deterministic function of the downloaded file: no hand edits, no override lists, no rows from anywhere else (no API lookups, no member data). A future correction must be a rule in this file, so publishing this file publishes the alteration. METHOD.md states every rule in plain words; off_countries.tsv is the country table; rowcheck.py holds the content hash. Imports stay per job (Part B §2.2): this file has no database code, and openpyxl and orjson are imported inside the readers that need them. It reads no environment variable and no secret. """ import csv import io import re import unicodedata import zipfile from collections import Counter from dataclasses import dataclass from decimal import ROUND_HALF_UP, Decimal, InvalidOperation import rowcheck class TransformError(Exception): """A source file the method cannot convert safely. It stops the run.""" class PinMissing(TransformError): """A base source has no pinned SHA-256 yet: the owner checks the file and commits its hash.""" # ---- sources ----------------------------------------------------------------------------------- @dataclass(frozen=True) class SourceSpec: key: str version_name: str # the release label's readable part; Open Food Facts adds its dump date url: str file_date: str # ISO date; empty for Open Food Facts, whose date is Last-Modified (UTC) pinned_sha256: str # empty until the owner pins it; Open Food Facts uses checksum_url instead checksum_url: str licence_name: str licence_url: str credit_text: str carbohydrate_basis: str SOURCES = { "OPEN_FOOD_FACTS": SourceSpec( key="OPEN_FOOD_FACTS", version_name="OFF", url="https://static.openfoodfacts.org/data/openfoodfacts-products.jsonl.gz", file_date="", pinned_sha256="", checksum_url="https://static.openfoodfacts.org/data/gz-sha256sum", licence_name="Open Database License (ODbL) 1.0", licence_url="https://opendatacommons.org/licenses/odbl/1-0/", credit_text=("Contains information from Open Food Facts (https://world.openfoodfacts.org), made " "available under the Open Database License (ODbL) 1.0. Individual contents are under " "the Database Contents License 1.0."), carbohydrate_basis="LABEL", ), # The GOV.UK copy only (OGL v3.0). Quadram's CoFID API and labelling dataset are CC BY-NC-SA # and are never fetched. The unreviewed "old foods" file is not imported. "COFID": SourceSpec( key="COFID", version_name="CoFID 2021", url=("https://assets.publishing.service.gov.uk/media/60538b91e90e07527df82ae4/" "McCance_Widdowsons_Composition_of_Foods_Integrated_Dataset_2021..xlsx"), file_date="2021-03-19", # Pinned 2026-11-10: the first dry run printed this hash, and a separate download matched it. # That file was checked: 2,886 food codes, every code in COFID_CODES present once. Every later # run refuses a different file. pinned_sha256="436e9445ef2adb2a75f3d7edd51302de3adad25385f9795fc94ba58bd030e97d", checksum_url="", licence_name="Open Government Licence v3.0", licence_url="https://www.nationalarchives.gov.uk/doc/open-government-licence/version/3/", credit_text=("Contains public sector information licensed under the Open Government Licence v3.0. " "Source: McCance and Widdowson's Composition of Foods Integrated Dataset 2021, " "Public Health England."), carbohydrate_basis="AVAILABLE_MONOSACCHARIDE", ), # Both zips pinned the same way as CoFID's on 2026-11-10. They were checked: every member # read_usda needs is present, with 5,432 survey_fndds_food and 7,793 sr_legacy_food foods. "USDA_FNDDS": SourceSpec( key="USDA_FNDDS", version_name="FNDDS 2021-2023 (FDC 2024-10-31)", url="https://fdc.nal.usda.gov/fdc-datasets/FoodData_Central_survey_food_csv_2024-10-31.zip", file_date="2024-10-31", pinned_sha256="5ccc25ec2777a8982fbb61378a42f415316173eb11e48c9a8ba4cb19f5a4f29c", checksum_url="", licence_name="CC0 1.0 Universal", licence_url="https://creativecommons.org/publicdomain/zero/1.0/", credit_text=("U.S. Department of Agriculture, Agricultural Research Service. FoodData Central, 2024. " "fdc.nal.usda.gov."), carbohydrate_basis="BY_DIFFERENCE", ), "USDA_SR_LEGACY": SourceSpec( key="USDA_SR_LEGACY", version_name="SR Legacy 2018-04", url="https://fdc.nal.usda.gov/fdc-datasets/FoodData_Central_sr_legacy_food_csv_2018-04.zip", file_date="2018-04-01", pinned_sha256="b80817294b8850530aaedf2e515c02593b1824f763a0ff356e5c2081643e6fd0", checksum_url="", licence_name="CC0 1.0 Universal", licence_url="https://creativecommons.org/publicdomain/zero/1.0/", credit_text=("U.S. Department of Agriculture, Agricultural Research Service. FoodData Central, 2018. " "fdc.nal.usda.gov."), carbohydrate_basis="BY_DIFFERENCE", ), } def check_pinned(spec, actual_sha256): """Base sources: the downloaded file must be exactly the pinned one (Part B §3.3, §3.4).""" if not spec.pinned_sha256: raise PinMissing(f"{spec.key}: no SHA-256 is pinned yet. The file downloaded from {spec.url} has " f"SHA-256 {actual_sha256}. Check that file, set pinned_sha256 for {spec.key} in " "SOURCES in .github/scripts/food_database/transform.py, commit, and run again.") if actual_sha256 != spec.pinned_sha256: raise TransformError(f"{spec.key}: the file at {spec.url} has SHA-256 {actual_sha256}, not the pinned " f"{spec.pinned_sha256}. The source changed: check the new file, then update the pin.") # ---- numbers ----------------------------------------------------------------------------------- STEP = Decimal("0.001") ZERO = Decimal("0.000") HUGE = Decimal("1E+15") # far outside every limit; keeps Decimal arithmetic exact and small THOUSAND = Decimal(1000) MILLION = Decimal(1000000) KJ_PER_KCAL = Decimal("4.184") SALT_TO_SODIUM_MG = Decimal(400) # salt is sodium x 2.5, so 1 g salt = 400 mg sodium ABV_TO_UNITS = Decimal("0.1") # one UK unit is 10 ml of ethanol ETHANOL_G_PER_UNIT = Decimal("7.89") IU_PER_MCG_VITAMIN_D = Decimal(40) def num(value): """A source value as a finite Decimal, or None: missing, blank, NaN, infinity and non-numbers.""" if value is None or isinstance(value, bool): return None if isinstance(value, (int, float)): text = str(value) elif isinstance(value, str): text = value.strip() if not text: return None else: return None try: number = Decimal(text) except InvalidOperation: return None if not number.is_finite(): return None if number.copy_abs() > HUGE: number = HUGE.copy_sign(number) return number def scale(value, factor): return None if value is None else value * factor def divide(value, divisor): return None if value is None else value / divisor def q3(value): """Rounded to 3 decimals, half away from zero: what NUMERIC(10,3) stores. -0 becomes 0.""" if value is None: return None if value.copy_abs() > HUGE: value = HUGE.copy_sign(value) rounded = value.quantize(STEP, rounding=ROUND_HALF_UP) return ZERO if rounded.is_zero() else rounded def fmt3(value): """The stored and hashed string of a rounded value ("12.300"), or None.""" return None if value is None else format(value, "f") # ---- text -------------------------------------------------------------------------------------- SURROGATE = re.compile(r"[\ud800-\udfff]") WHITESPACE = re.compile(r"\s+") CONTROL = re.compile(r"[\x00-\x1f\x7f-\x9f]") def as_text(value): if isinstance(value, str): return value if isinstance(value, bool): return None if isinstance(value, int): return str(value) if isinstance(value, float) and value.is_integer(): return str(int(value)) return None def clean_text(value, limit=None): """NFC, lone surrogates replaced, control characters removed, whitespace collapsed, trimmed, cut.""" text = as_text(value) if text is None: return None text = SURROGATE.sub("�", text) text = unicodedata.normalize("NFC", text) text = WHITESPACE.sub(" ", text) text = CONTROL.sub("", text) text = WHITESPACE.sub(" ", text).strip() if limit is not None and len(text) > limit: text = text[:limit].rstrip() return text or None # ---- the shared row rules ---------------------------------------------------------------------- SKIP_NAME = "01 no usable name" SKIP_ENERGY = "02 no energy" SKIP_MACRO_MISSING = "03 protein, carbohydrate or fat missing" SKIP_NEGATIVE = "04 energy or a macro negative" SKIP_CALORIES = "05 calories over 1000 per 100" SKIP_CODE = "06 code missing or over 64 characters" SKIP_DUPLICATE = "07 same code twice in the file" SKIP_OBSOLETE = "08 obsolete" SKIP_MACRO_OVER = "09 a macro over 100 g per 100 g (150 per 100 ml)" SKIP_MACRO_SUM = "10 macros sum over 105 g per 100 g (157.5 per 100 ml)" SKIP_ENERGY_MISMATCH = "11 energy disagrees with the macros" SKIP_NOT_JSON = "12 line is not valid JSON" BASIS_UNIT = {"PER_100G": "g", "PER_100ML": "ml"} MAX_CALORIES = Decimal(1000) MACRO_CAP = {"PER_100G": Decimal(100), "PER_100ML": Decimal(150)} MACRO_SUM_CAP = {"PER_100G": Decimal(105), "PER_100ML": Decimal("157.5")} ENERGY_CHECK_FROM = Decimal(13) ENERGY_TOLERANCE = Decimal("0.30") ENERGY_SLACK = Decimal(5) HALF_GRAM = Decimal("0.5") MAX_SERVING = Decimal(10000) MAX_PORTION = Decimal(10000) GRAM_MICROS = ("fibre_g", "sugar_g", "sat_fat_g") MG_MICROS = ("iron_mg", "calcium_mg", "vit_c_mg", "magnesium_mg", "omega3_mg", "potassium_mg", "sodium_mg") MCG_MICROS = ("vit_d_mcg", "vit_b12_mcg") MG_CAP = Decimal(100000) MCG_CAP = Decimal("9999999.999") # the column's capacity ALCOHOL_CAP = Decimal("12.7") # pure ethanol, in units per 100 def micro_cap(column, basis): if column in GRAM_MICROS: return MACRO_CAP[basis] if column in MG_MICROS: return MG_CAP if column in MCG_MICROS: return MCG_CAP return ALCOHOL_CAP class SourceStats: """Counts for one source: what was read, kept and skipped, and why. Goes into manifest.json.""" def __init__(self, source_key): self.source_key = source_key self.read = 0 self.kept = 0 self.skipped = Counter() self.nulled = Counter() self.filled = Counter() self.basis = Counter() self.unknown_countries = Counter() self.other_text = Counter() self.review_only = Counter() self.groups = Counter() self.portions = 0 self.portions_skipped = Counter() self.nutrition_from = Counter() # Open Food Facts: which shape each named product's nutrition came from self.notes = [] def skip(self, reason): self.skipped[reason] += 1 return None def keep(self, row): self.kept += 1 self.basis[row["basis"]] += 1 for column in rowcheck.MICROS + ("serving_size",): if row[column] is not None: self.filled[column] += 1 self.portions += len(row["portions"]) def to_manifest(self): kept = self.kept or 1 return { "rows_read": self.read, "rows_kept": self.kept, "rows_skipped": sum(self.skipped.values()), "skipped_by_reason": dict(sorted(self.skipped.items())), "values_set_to_null": dict(sorted(self.nulled.items())), "fill_rate": {c: round(self.filled[c] / kept, 4) for c in rowcheck.MICROS + ("serving_size",)}, "basis": dict(sorted(self.basis.items())), "unknown_country_tags": self.unknown_countries.most_common(20), "other_text_cells": dict(sorted(self.other_text.items())), "review_only_would_fail": dict(sorted(self.review_only.items())), "groups": dict(sorted(self.groups.items())), "portions": self.portions, "portions_skipped": dict(sorted(self.portions_skipped.items())), "nutrition_read_from": dict(sorted(self.nutrition_from.items())), "notes": self.notes, } def label_failure(calories, protein, carbs, fat, values, basis): """Filters 9-11 (label-tuned): the reason a row fails, or None.""" cap = MACRO_CAP[basis] if protein > cap or carbs > cap or fat > cap: return SKIP_MACRO_OVER if protein + carbs + fat > MACRO_SUM_CAP[basis]: return SKIP_MACRO_SUM if calories > ENERGY_CHECK_FROM: fibre = values["fibre_g"] or ZERO alcohol_g = (values["alcohol_units"] or ZERO) * ETHANOL_G_PER_UNIT estimate = 4 * protein + 4 * carbs + 9 * fat + 2 * fibre + 7 * alcohol_g if abs(calories - estimate) > ENERGY_TOLERANCE * estimate + ENERGY_SLACK: return SKIP_ENERGY_MISMATCH return None def finish_row(stats, *, source_key, code, name, brand, countries, basis, energy, macros, micros, serving_quantity=None, serving_label=None, portions=(), label_checks): """Filters 2-5, the value-level rules, filters 9-11 (Open Food Facts only; base foods only count them), then the row with its content hash. None when the row is skipped. energy, macros and micros are unrounded Decimals (or None) already in our units.""" if energy is None: return stats.skip(SKIP_ENERGY) protein, carbs, fat = q3(macros["protein"]), q3(macros["carbohydrates"]), q3(macros["fats"]) if protein is None or carbs is None or fat is None: return stats.skip(SKIP_MACRO_MISSING) calories = q3(energy) if min(calories, protein, carbs, fat) < 0: return stats.skip(SKIP_NEGATIVE) if calories > MAX_CALORIES: return stats.skip(SKIP_CALORIES) values = {column: q3(micros.get(column)) for column in rowcheck.MICROS} for column in rowcheck.MICROS: value = values[column] if value is not None and (value < 0 or value > micro_cap(column, basis)): values[column] = None stats.nulled[column] += 1 if label_checks: if values["sugar_g"] is not None and values["sugar_g"] > carbs + HALF_GRAM: values["sugar_g"] = None stats.nulled["sugar_g over carbohydrate"] += 1 if values["sat_fat_g"] is not None and values["sat_fat_g"] > fat + HALF_GRAM: values["sat_fat_g"] = None stats.nulled["sat_fat_g over fat"] += 1 failure = label_failure(calories, protein, carbs, fat, values, basis) if failure: if label_checks: return stats.skip(failure) stats.review_only[failure] += 1 serving_size = q3(serving_quantity) if serving_size is None or serving_size <= 0 or serving_size > MAX_SERVING or not serving_label: if serving_quantity is not None: stats.nulled["serving"] += 1 serving_size, serving_label = None, None row = { "source_key": source_key, "source_code": code, "kind": "BRANDED" if source_key in rowcheck.BRANDED_SOURCES else "BASE", "name": name, "brand": brand, "countries": countries, "basis": basis, "calories": fmt3(calories), "protein": fmt3(protein), "carbohydrates": fmt3(carbs), "fats": fmt3(fat), } for column in rowcheck.MICROS: row[column] = fmt3(values[column]) row["serving_size"] = fmt3(serving_size) row["serving_label"] = serving_label row["portions"] = [{"sort_order": order, "label": label, "amount": fmt3(amount)} for order, (label, amount) in enumerate(portions)] row["content_hash"] = rowcheck.canonical_hash(row) return row def claim_code(stats, raw, seen): """Filters 6 and 7: the cleaned code, or None when the row is skipped. The first row with a code claims it, whatever happens to that row later.""" code = clean_text(raw) if code is None or len(code) > rowcheck.MAX_CODE: return stats.skip(SKIP_CODE) if code in seen: return stats.skip(SKIP_DUPLICATE) seen.add(code) return code # ---- Open Food Facts (JSONL product documents) ------------------------------------------------- OFF_GRAMS = {"fibre_g": "fiber_100g", "sugar_g": "sugars_100g", "sat_fat_g": "saturated-fat_100g"} OFF_GRAMS_TO_MG = {"iron_mg": "iron_100g", "calcium_mg": "calcium_100g", "vit_c_mg": "vitamin-c_100g", "magnesium_mg": "magnesium_100g", "potassium_mg": "potassium_100g", "omega3_mg": "omega-3-fat_100g", "sodium_mg": "sodium_100g"} OFF_GRAMS_TO_MCG = {"vit_d_mcg": "vitamin-d_100g", "vit_b12_mcg": "vitamin-b12_100g"} # Open Food Facts' 2026 nutrition model (product schema 1003 on, live since February 2026): a product # saved since keeps its nutrition in nutrition.aggregated_set, and its nutriments is empty or gone. # The export holds both shapes. The set's nutrients are {value, unit, source, source_index, ...}, # named as nutriments' _100g keys and already per 100 g or 100 ml. Two kinds of value are left # out, as the old shape kept both apart from its _100g keys: one from an estimate (worked out from the # ingredients), and one Open Food Facts computed (energy from the macros, sodium from salt), which the # input set it came from (input_sets[source_index]) holds only as value_computed. OFF_SET_BASIS = {"100g": "PER_100G", "100ml": "PER_100ML"} OFF_SET_UNITS = {"energy-kcal": "kcal", "energy-kj": "kJ", "energy": "kJ", "alcohol": "% vol"} # all else g OFF_SET_NUTRIENTS = ("energy-kcal", "energy-kj", "energy", "proteins", "carbohydrates", "fat", "salt", "alcohol", *(key.removesuffix("_100g") for mapping in (OFF_GRAMS, OFF_GRAMS_TO_MG, OFF_GRAMS_TO_MCG) for key in mapping.values())) OFF_SET_ESTIMATE = "estimate" VOLUME_QUANTITY = re.compile(r"(?i)(?:^|[\d\s.,])(?:ml|cl|l)\.?$") OBSOLETE_TRUE = frozenset(("on", "1", "true", "yes")) def load_off_countries(path): """off_countries.tsv: Open Food Facts country tag → ISO 3166 alpha-2 code, or "-" for a tag that names no single country (en:world). '#' starts a comment line.""" mapping = {} with open(path, encoding="utf-8") as tsv: for number, line in enumerate(tsv, 1): line = line.rstrip("\r\n") if not line.strip() or line.lstrip().startswith("#"): continue parts = line.split("\t") if len(parts) != 2: raise TransformError(f"{path}:{number}: expected 'tagcode'") tag, code = parts[0].strip().lower(), parts[1].strip() if code != "-" and not re.fullmatch(r"[A-Z]{2}", code): raise TransformError(f"{path}:{number}: {code!r} is not a two-letter code or '-'") if mapping.get(tag, code) != code: raise TransformError(f"{path}:{number}: {tag} is listed twice with different codes") mapping[tag] = code return mapping def _obsolete(value): if value is True: return True if isinstance(value, bool) or value is None: return False if isinstance(value, (int, float)): return value == 1 return isinstance(value, str) and value.strip().lower() in OBSOLETE_TRUE def off_name(doc): """product_name_en, then product_name, then product_name_ (the product's main language).""" keys = ["product_name_en", "product_name"] lang = doc.get("lang") if isinstance(lang, str) and lang.strip(): keys.append(f"product_name_{lang.strip().lower()}") for key in keys: name = clean_text(doc.get(key), rowcheck.MAX_NAME) if name: return name return None def off_brand(value): if not isinstance(value, str): return None return clean_text(value.split(",", 1)[0], rowcheck.MAX_NAME) def off_basis(doc): unit = doc.get("product_quantity_unit") if isinstance(unit, str) and unit.strip(): return "PER_100ML" if unit.strip().lower() == "ml" else "PER_100G" quantity = doc.get("quantity") if isinstance(quantity, str) and VOLUME_QUANTITY.search(quantity.strip()): return "PER_100ML" return "PER_100G" def off_countries(tags, mapping, stats): codes = set() if isinstance(tags, list): for tag in tags: if not isinstance(tag, str) or not tag.strip(): continue tag = tag.strip().lower() code = mapping.get(tag) if code is None: stats.unknown_countries[tag] += 1 elif code != "-": codes.add(code) return sorted(codes) def _off_set_label(value): return value if isinstance(value, str) and len(value) <= 20 else "other" def _off_printed(input_sets, entry, name): """True when the input set an aggregated value came from holds a value for it, not only value_computed.""" index = entry.get("source_index") if not isinstance(input_sets, list) or type(index) is not int or not 0 <= index < len(input_sets): return False source = input_sets[index] nutrients = source.get("nutrients") if isinstance(source, dict) else None item = nutrients.get(name) if isinstance(nutrients, dict) else None return isinstance(item, dict) and "value" in item def off_aggregated_nutriments(doc, stats): """The 2026 nutrition model → (nutriments with _100g keys, basis, shape). The nutriments are None when the product has no as-sold aggregated set per 100 g or 100 ml; shape then names the set that was not read, or is None when there is none. A value in an unexpected unit is left out and counted.""" nutrition = doc.get("nutrition") aggregated = nutrition.get("aggregated_set") if isinstance(nutrition, dict) else None if not isinstance(aggregated, dict): return None, None, None preparation, per = aggregated.get("preparation"), aggregated.get("per") basis = OFF_SET_BASIS.get(per) if isinstance(per, str) else None nutrients = aggregated.get("nutrients") if preparation != "as_sold" or basis is None or not isinstance(nutrients, dict): return None, None, (f"nutrition.aggregated_set not read ({_off_set_label(preparation)}/" f"{_off_set_label(per)})") input_sets = nutrition.get("input_sets") flat = {} for name in OFF_SET_NUTRIENTS: entry = nutrients.get(name) if (not isinstance(entry, dict) or entry.get("source") == OFF_SET_ESTIMATE or not _off_printed(input_sets, entry, name)): continue unit = OFF_SET_UNITS.get(name, "g") if entry.get("unit") != unit: stats.nulled[f"{name} not in {unit}"] += 1 continue flat[f"{name}_100g"] = entry.get("value") return flat, basis, "nutrition.aggregated_set" def off_energy(nutriments): """energy-kcal_100g; else energy-kj_100g ÷ 4.184; else energy_100g (always kJ) ÷ 4.184.""" kcal = num(nutriments.get("energy-kcal_100g")) if kcal is not None: return kcal for key in ("energy-kj_100g", "energy_100g"): kj = num(nutriments.get(key)) if kj is not None: return kj / KJ_PER_KCAL return None def off_micros(nutriments): values = {} for column, key in OFF_GRAMS.items(): values[column] = num(nutriments.get(key)) for column, key in OFF_GRAMS_TO_MG.items(): values[column] = scale(num(nutriments.get(key)), THOUSAND) for column, key in OFF_GRAMS_TO_MCG.items(): values[column] = scale(num(nutriments.get(key)), MILLION) if values["sodium_mg"] is None: values["sodium_mg"] = scale(num(nutriments.get("salt_100g")), SALT_TO_SODIUM_MG) # alcohol_100g is % vol. For a per-100 g product, 100 g is taken as about 100 ml. values["alcohol_units"] = scale(num(nutriments.get("alcohol_100g")), ABV_TO_UNITS) return values def convert_off_product(doc, countries_map, stats, seen): """One product document → a row, or None after counting why it was skipped (filters 1-11). Only the fields named here are read: never contributor fields, images, ingredients or packaging.""" code = claim_code(stats, doc.get("code"), seen) if code is None: return None if _obsolete(doc.get("obsolete")): return stats.skip(SKIP_OBSOLETE) name = off_name(doc) if name is None: return stats.skip(SKIP_NAME) basis = off_basis(doc) nutriments = doc.get("nutriments") if not isinstance(nutriments, dict): nutriments = {} # The aggregated set first, as Open Food Facts' own exports read it; the old nutriments otherwise. aggregated, set_basis, shape = off_aggregated_nutriments(doc, stats) if aggregated is not None: nutriments, basis = aggregated, set_basis elif any(isinstance(key, str) and key.endswith("_100g") for key in nutriments): shape = "nutriments" stats.nutrition_from[shape or "none"] += 1 serving_quantity = num(doc.get("serving_quantity")) serving_unit = doc.get("serving_quantity_unit") if (serving_quantity is not None and isinstance(serving_unit, str) and serving_unit.strip() and serving_unit.strip().lower() != BASIS_UNIT[basis]): stats.nulled["serving"] += 1 serving_quantity = None return finish_row( stats, source_key="OPEN_FOOD_FACTS", code=code, name=name, brand=off_brand(doc.get("brands")), countries=off_countries(doc.get("countries_tags"), countries_map, stats), basis=basis, energy=off_energy(nutriments), macros={"protein": num(nutriments.get("proteins_100g")), "carbohydrates": num(nutriments.get("carbohydrates_100g")), "fats": num(nutriments.get("fat_100g"))}, micros=off_micros(nutriments), serving_quantity=serving_quantity, serving_label=clean_text(doc.get("serving_size"), rowcheck.MAX_LABEL), label_checks=True, ) def convert_off_lines(lines, countries_map, stats): """JSONL lines (bytes) → rows. Filter 12 counts lines that are not a JSON object.""" import orjson seen = set() for line in lines: stats.read += 1 try: doc = orjson.loads(line) except orjson.JSONDecodeError: stats.skip(SKIP_NOT_JSON) continue if not isinstance(doc, dict): stats.skip(SKIP_NOT_JSON) continue row = convert_off_product(doc, countries_map, stats, seen) if row is not None: stats.keep(row) yield row def off_tripwires(stats): """Stops on an export whose shape may have changed or that was cut short (Part B §3.2). The message carries the skip counts, because the run stops before its summary is written.""" if stats.read == 0: raise TransformError("OPEN_FOOD_FACTS: the export held no products") counts = (f"Skipped, by filter: {', '.join(f'{r} {n}' for r, n in sorted(stats.skipped.items()))}. " f"Nutrition read from: {', '.join(f'{s} {n}' for s, n in sorted(stats.nutrition_from.items()))}.") bad = stats.skipped[SKIP_NOT_JSON] if bad * 1000 > stats.read: raise TransformError(f"OPEN_FOOD_FACTS: {bad} of {stats.read} lines are not JSON (over 0.1%). {counts}") if stats.kept * 4 < stats.read: raise TransformError(f"OPEN_FOOD_FACTS: only {stats.kept} of {stats.read} products gave a loggable " f"row (under 25%); the export's shape may have changed. {counts}") # ---- CoFID 2021 (GOV.UK xlsx) ------------------------------------------------------------------ COFID_CODES = { "kcal": "KCALS", "kj": "KJ", "protein": "PROT", "carbohydrates": "CHO", "fats": "FAT", "fibre_g": "AOACFIB", "sugar_g": "TOTSUG", "sat_fat_g": "SATFOD", "iron_mg": "FE", "calcium_mg": "CA", "vit_c_mg": "VITC", "magnesium_mg": "MG", "potassium_mg": "K", "sodium_mg": "NA", "vit_d_mcg": "VITD", "vit_b12_mcg": "VITB12", "omega3_mg": "TOTn3PFOD", "alcohol_units": "ALCO", } COFID_LABELS = ("food code", "food name", "group") COFID_HEADER_ROWS = 6 # Alcoholic beverages are given per 100 ml: the file's own 1.1 Notes sheet says every value is per # 100 g except alcoholic beverages, which are per 100 ml. They are food group Q, matched as a prefix # of the Group code. Checked against the pinned file on 2026-11-10: its Q groups hold 31 foods, all # alcoholic drinks (Q pre-mixed spirit drinks, QA beers, QC ciders, QE wines, QF fortified wines, # QG vermouth, QI liqueurs, QK spirits), and no other group starts with Q. Every other food, milk, # juices and soft drinks included, is per 100 g. COFID_PER_100ML_GROUPS = ("Q",) def cofid_number(cell, code, stats): """A number as given; Tr (trace) → 0; N (no reliable figure) and blank → None; other text → None, counted per column.""" if cell is None: return None if isinstance(cell, (int, float)) and not isinstance(cell, bool): return num(cell) text = str(cell).strip() if not text: return None if text.lower() == "tr": return Decimal(0) if text.upper() == "N": return None number = num(text) if number is None: stats.other_text[code] += 1 return number def _cell(row, column): return row[column] if row is not None and column < len(row) else None def _cofid_header(title, rows): """{label: column}, {code: column} and the last header row index, from a sheet's first rows.""" labels, codes, last = {}, {}, -1 wanted = set(COFID_CODES.values()) for index, row in enumerate(rows[:COFID_HEADER_ROWS]): for column, cell in enumerate(row): if not isinstance(cell, str): continue text = cell.strip() if text.casefold() in COFID_LABELS: labels.setdefault(text.casefold(), column) last = max(last, index) elif text in wanted: if text in codes: raise TransformError(f"COFID: {text} appears twice in the header of sheet {title!r}") codes[text] = column last = max(last, index) return labels, codes, last def _borrows_food_code(header_rows, labels, reference): """True when a sheet that holds codes but has no Food Code label can be joined on the Food Code column of the sheet holding PROT: its Food Name and Group labels sit in the same columns, and its own header cells in that column are blank. GOV.UK's CoFID 2021 file leaves that header cell blank on its 1.4 Inorganics sheet. read_cofid then checks the column holds the same food codes.""" if reference is None: return False if any(labels.get(label) != reference.get(label) for label in ("food name", "group")): return False return all(clean_text(_cell(row, reference["food code"])) is None for row in header_rows) def read_cofid(path, stats): """The GOV.UK CoFID 2021 workbook → rows. Each code is found by scanning the header rows of every sheet that has a Food Code column, or whose blank Food Code header can borrow the PROT sheet's (_borrows_food_code); rows are joined on Food Code.""" import openpyxl workbook = openpyxl.load_workbook(path, read_only=True, data_only=True) try: sheets = [] for worksheet in workbook.worksheets: sheets.append((worksheet.title, list(worksheet.iter_rows(values_only=True)))) finally: workbook.close() headed = [(title, rows) + _cofid_header(title, rows) for title, rows in sheets] reference = next((labels for _, _, labels, codes, _ in headed if "PROT" in codes and "food code" in labels), None) located = {} # code -> (sheet index, column) parsed = [] # per sheet: (title, labels, data rows) borrowed = [] # indexes into parsed of sheets joined on the PROT sheet's Food Code column for title, rows, labels, codes, last in headed: if "food code" not in labels: if not codes or not _borrows_food_code(rows[:last + 1], labels, reference): continue labels = {**labels, "food code": reference["food code"]} borrowed.append(len(parsed)) index = len(parsed) parsed.append((title, labels, rows[last + 1:])) for code, column in codes.items(): if code in located: raise TransformError(f"COFID: {code} is in two sheets ({parsed[located[code][0]][0]!r} " f"and {title!r})") located[code] = (index, column) missing = sorted(set(COFID_CODES.values()) - set(located)) if missing: raise TransformError(f"COFID: no sheet has {', '.join(missing)} in its header rows") main_index = located["PROT"][0] main_title, main_labels, main_rows = parsed[main_index] for label in COFID_LABELS: if label not in main_labels: raise TransformError(f"COFID: sheet {main_title!r} (which holds PROT) has no {label!r} column") by_code = [] # per sheet: Food Code -> its first row for title, labels, rows in parsed: index = {} for row in rows: code = clean_text(_cell(row, labels["food code"])) if code is not None: index.setdefault(code, row) by_code.append(index) for sheet in borrowed: if set(by_code[sheet]) != set(by_code[main_index]): raise TransformError(f"COFID: sheet {parsed[sheet][0]!r} has a blank Food Code header and its food " f"codes are not the same as those of {main_title!r}, so it cannot be joined") seen = set() for row in main_rows: raw_code = _cell(row, main_labels["food code"]) if clean_text(raw_code) is None: continue # blank and unit rows are not foods stats.read += 1 code = claim_code(stats, raw_code, seen) if code is None: continue name = clean_text(_cell(row, main_labels["food name"]), rowcheck.MAX_NAME) if name is None: stats.skip(SKIP_NAME) continue group = clean_text(_cell(row, main_labels["group"])) or "" stats.groups[group] += 1 basis = "PER_100ML" if group.upper().startswith(COFID_PER_100ML_GROUPS) else "PER_100G" def value(key): sheet, column = located[COFID_CODES[key]] source = row if sheet == main_index else by_code[sheet].get(code) return cofid_number(_cell(source, column), COFID_CODES[key], stats) energy = value("kcal") if energy is None: energy = divide(value("kj"), KJ_PER_KCAL) micros = {column: value(column) for column in ("fibre_g", "sugar_g", "sat_fat_g", "iron_mg", "calcium_mg", "vit_c_mg", "magnesium_mg", "potassium_mg", "sodium_mg", "vit_d_mcg", "vit_b12_mcg")} micros["omega3_mg"] = scale(value("omega3_mg"), THOUSAND) # ALCO is g per 100 g, or per 100 ml for drinks; 7.89 g of ethanol is one UK unit. micros["alcohol_units"] = divide(value("alcohol_units"), ETHANOL_G_PER_UNIT) result = finish_row( stats, source_key="COFID", code=code, name=name, brand=None, countries=["GB"], basis=basis, energy=energy, macros={"protein": value("protein"), "carbohydrates": value("carbohydrates"), "fats": value("fats")}, micros=micros, label_checks=False, ) if result is not None: stats.keep(result) yield result # ---- USDA FoodData Central (FNDDS and SR Legacy CSV zips) -------------------------------------- USDA_DATA_TYPES = {"USDA_FNDDS": "survey_fndds_food", "USDA_SR_LEGACY": "sr_legacy_food"} # FDC nutrient.id → (a fragment of its nutrient.csv name, its unit). A nutrient.csv row with this # id must carry this unit and a name containing the fragment (case-insensitive), or the run stops. USDA_NUTRIENTS = { 1008: ("energy", "KCAL"), 2048: ("atwater specific", "KCAL"), 2047: ("atwater general", "KCAL"), 1062: ("energy", "KJ"), 1003: ("protein", "G"), 1005: ("carbohydrate, by difference", "G"), 1004: ("total lipid", "G"), 1079: ("fiber, total dietary", "G"), 2000: ("sugars", "G"), 1063: ("sugars", "G"), 1258: ("total saturated", "G"), 1087: ("calcium", "MG"), 1089: ("iron", "MG"), 1090: ("magnesium", "MG"), 1092: ("potassium", "MG"), 1093: ("sodium", "MG"), 1162: ("vitamin c", "MG"), 1114: ("vitamin d", "UG"), 1110: ("vitamin d", "IU"), 1178: ("vitamin b-12", "UG"), 1404: ("18:3 n-3", "G"), 1270: ("18:3", "G"), 1278: ("20:5", "G"), 1280: ("22:5", "G"), 1272: ("22:6", "G"), 1018: ("alcohol", "G"), } USDA_UNDETERMINED_UNIT = "undetermined" USDA_NOT_A_MEASURE = "quantity not specified" BIG = 1 << 62 def _csv_rows(archive, member): with archive.open(member) as raw: yield from csv.DictReader(io.TextIOWrapper(raw, encoding="utf-8-sig", newline="")) def _usda_members(archive, names): found = {} for info in archive.infolist(): base = info.filename.rsplit("/", 1)[-1] if base in names: if base in found: raise TransformError(f"the zip holds {base} twice ({found[base]} and {info.filename})") found[base] = info.filename missing = sorted(set(names) - set(found)) if missing: raise TransformError(f"the zip has no {', '.join(missing)}") return found def check_usda_nutrients(records): """The ids of USDA_NUTRIENTS that nutrient.csv defines. A defined id with another name or unit stops the run.""" present = set() for record in records: try: nutrient_id = int((record.get("id") or "").strip()) except ValueError: continue expected = USDA_NUTRIENTS.get(nutrient_id) if expected is None: continue name = (record.get("name") or "").strip() unit = (record.get("unit_name") or "").strip().upper() if expected[0] not in name.casefold() or unit != expected[1]: raise TransformError(f"nutrient {nutrient_id} is '{name} | {unit}' in nutrient.csv; the method " f"expects a name containing '{expected[0]}' in {expected[1]}") present.add(nutrient_id) return present def usda_nutrient_keys(records): """nutrient.csv → (every id it defines, nutrient_nbr → id). SR Legacy's food_nutrient.csv gives each nutrient by id (1003 for protein); the FNDDS zip's gives it by nutrient_nbr, the older USDA number (203), which read_usda maps back to the id. A number given to two ids maps to None, and read_usda stops if food_nutrient.csv uses it.""" ids, numbers = set(), {} for record in records: nutrient_id = _whole(record.get("id")) if nutrient_id == BIG: continue ids.add(nutrient_id) number = (record.get("nutrient_nbr") or "").strip() if number and numbers.setdefault(number, nutrient_id) != nutrient_id: numbers[number] = None return ids, numbers def _whole(text): try: return int((text or "").strip()) except ValueError: return BIG def usda_portions(records, units, stats): """food_portion.csv rows of one food → [(label, grams)] in sort order (Part B §4.5).""" portions, labels = [], set() for record in sorted(records, key=lambda r: (_whole(r.get("seq_num")), _whole(r.get("id")))): description = clean_text(record.get("portion_description"), rowcheck.MAX_LABEL) if description and description.casefold() == USDA_NOT_A_MEASURE: stats.portions_skipped["quantity not specified"] += 1 continue if description: label = description else: parts = [] amount = num(record.get("amount")) if amount is not None: parts.append(format(amount.normalize(), "f")) unit = clean_text(units.get((record.get("measure_unit_id") or "").strip())) if unit and unit.casefold() != USDA_UNDETERMINED_UNIT: parts.append(unit) modifier = clean_text(record.get("modifier")) if modifier: parts.append(modifier) label = clean_text(" ".join(parts), rowcheck.MAX_LABEL) if not label: stats.portions_skipped["blank label"] += 1 continue grams = q3(num(record.get("gram_weight"))) if grams is None or grams <= 0 or grams > MAX_PORTION: stats.portions_skipped["gram weight outside (0, 10000]"] += 1 continue if label.casefold() in labels: stats.portions_skipped["repeated label"] += 1 continue labels.add(label.casefold()) portions.append((label, grams)) return portions def read_usda(path, source_key, stats): """An FDC CSV zip → rows of one data type. The zip is read member by member, never extracted.""" data_type = USDA_DATA_TYPES[source_key] type_file = f"{data_type}.csv" with zipfile.ZipFile(path) as archive: members = _usda_members(archive, ("food.csv", "nutrient.csv", "food_nutrient.csv", "food_portion.csv", "measure_unit.csv", type_file)) nutrient_records = list(_csv_rows(archive, members["nutrient.csv"])) present = check_usda_nutrients(nutrient_records) defined, by_number = usda_nutrient_keys(nutrient_records) foods = [] for record in _csv_rows(archive, members["food.csv"]): if (record.get("data_type") or "").strip() == data_type: foods.append(((record.get("fdc_id") or "").strip(), record.get("description"))) wanted = {fdc_id for fdc_id, _ in foods} typed = {(record.get("fdc_id") or "").strip() for record in _csv_rows(archive, members[type_file])} if typed != wanted: stats.notes.append(f"{type_file} and food.csv disagree on {len(typed ^ wanted)} fdc_id values") amounts = {} keyed_by = set() # "id" and/or "nutrient_nbr": how this file's food_nutrient.csv names nutrients for record in _csv_rows(archive, members["food_nutrient.csv"]): fdc_id = (record.get("fdc_id") or "").strip() if fdc_id not in wanted: continue raw = (record.get("nutrient_id") or "").strip() nutrient_id = _whole(raw) if nutrient_id in defined: keyed_by.add("id") elif raw in by_number: keyed_by.add("nutrient_nbr") nutrient_id = by_number[raw] if nutrient_id is None: raise TransformError(f"food_nutrient.csv uses nutrient_nbr {raw}, which nutrient.csv gives " "to two ids") if nutrient_id not in USDA_NUTRIENTS: continue if nutrient_id not in present: raise TransformError(f"food_nutrient.csv uses nutrient {nutrient_id}, which nutrient.csv " "does not define") value = num(record.get("amount")) if value is not None: amounts.setdefault(fdc_id, {}).setdefault(nutrient_id, value) if len(keyed_by) > 1: raise TransformError("food_nutrient.csv gives some nutrients by nutrient.csv id and others by " "nutrient_nbr, so they cannot be told apart") if keyed_by == {"nutrient_nbr"}: stats.notes.append("food_nutrient.csv gives nutrients by nutrient_nbr (203 for protein), mapped to " "their nutrient.csv ids (1003)") units = {(record.get("id") or "").strip(): (record.get("name") or "").strip() for record in _csv_rows(archive, members["measure_unit.csv"])} portion_rows = {} for record in _csv_rows(archive, members["food_portion.csv"]): fdc_id = (record.get("fdc_id") or "").strip() if fdc_id in wanted: portion_rows.setdefault(fdc_id, []).append(record) seen = set() for fdc_id, description in foods: stats.read += 1 code = claim_code(stats, fdc_id, seen) if code is None: continue name = clean_text(description, rowcheck.MAX_NAME) if name is None: stats.skip(SKIP_NAME) continue a = amounts.get(fdc_id, {}) energy = a.get(1008) for nutrient_id in (2048, 2047): if energy is None: energy = a.get(nutrient_id) if energy is None: energy = divide(a.get(1062), KJ_PER_KCAL) vitamin_d = a.get(1114) if vitamin_d is None: vitamin_d = divide(a.get(1110), IU_PER_MCG_VITAMIN_D) sugar = a.get(2000) if a.get(2000) is not None else a.get(1063) # ALA is 18:3 n-3 (1404) when given, else 18:3 undifferentiated (1270), which in foods is # almost all ALA. Absent parts are left out; no part at all is NULL. ala = a.get(1404) if a.get(1404) is not None else a.get(1270) parts = [part for part in (ala, a.get(1278), a.get(1280), a.get(1272)) if part is not None] omega3 = sum(parts, Decimal(0)) * THOUSAND if parts else None result = finish_row( stats, source_key=source_key, code=code, name=name, brand=None, countries=["US"], basis="PER_100G", energy=energy, macros={"protein": a.get(1003), "carbohydrates": a.get(1005), "fats": a.get(1004)}, micros={ "fibre_g": a.get(1079), "sugar_g": sugar, "sat_fat_g": a.get(1258), "calcium_mg": a.get(1087), "iron_mg": a.get(1089), "magnesium_mg": a.get(1090), "potassium_mg": a.get(1092), "sodium_mg": a.get(1093), "vit_c_mg": a.get(1162), "vit_d_mcg": vitamin_d, "vit_b12_mcg": a.get(1178), "omega3_mg": omega3, "alcohol_units": divide(a.get(1018), ETHANOL_G_PER_UNIT), }, portions=usda_portions(portion_rows.get(fdc_id, []), units, stats), label_checks=False, ) if result is not None: stats.keep(result) yield result