# Wrought food database: how it is built This is the method behind the Wrought food database, in plain words. It is published together with `transform.py` (the code that applies these rules), `rowcheck.py` (the content hash), `off_countries.tsv` (the country table) and each release's `manifest.json` (the input file's URL, date, size and SHA-256, and the git SHAs of the method files). ## Licences - **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 derived from Open Food Facts is licensed under the ODbL 1.0, and its contents under the DbCL 1.0. No Open Food Facts images are used. - **CoFID 2021**: 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. Only the GOV.UK copy is used. - **USDA FoodData Central** (FNDDS 2021-2023 and SR Legacy) is CC0 1.0. U.S. Department of Agriculture, Agricultural Research Service, FoodData Central, fdc.nal.usda.gov. The method is a fixed function of the downloaded file. There are no hand edits, no override lists, and no rows from anywhere else: no lookups and no member data. Any correction is a rule in `transform.py`, so publishing that file publishes the correction. Member data is kept apart: members' own foods stay in their own table, and a member's log only points at a database food. ## What is imported | Source | File | Rows | |---|---|---| | Open Food Facts | the nightly `openfoodfacts-products.jsonl.gz` | every product (branded) | | CoFID 2021 | the GOV.UK `.xlsx` | every food (base), country GB | | USDA FNDDS 2021-2023 | the FoodData Central survey food CSV zip (2024-10-31) | `survey_fndds_food` rows (base), country US | | USDA SR Legacy | the FoodData Central SR Legacy CSV zip (2018-04) | `sr_legacy_food` rows (base), country US | The Open Food Facts file is streamed. Open Food Facts publishes no checksum for it (its `gz-sha256sum` and `sha256sum` lists name only the MongoDB dump), so the import checks the list, records the file's own SHA-256 and says it could not be checked against one; the gzip stream must end cleanly, its own integrity checks included. The CoFID and USDA files must match a SHA-256 pinned in `transform.py`: a different file at the same address stops the import until the pin is updated. ## Rules for every source - **Missing is not zero.** A missing value is stored as unknown. A value the source states as zero stays 0. - **Numbers** are read exactly as decimals and rounded to 3 decimal places, halves away from zero. NaN, infinity and anything that is not a number count as missing. Kilojoules become kcal by dividing by 4.184. - **Text** is normalised to Unicode NFC. Control characters are removed, broken characters are replaced, runs of whitespace become one space, and the ends are trimmed. Names and brands are cut to 255 characters; serving and portion labels to 100. - **Countries** are upper-case ISO 3166 two-letter codes, sorted, without repeats; none when unknown. - **Units.** Values are per 100 g, or per 100 ml for products sold by volume. A serving size and every portion amount is in that same unit (g or ml). - **No personal data** is read. Open Food Facts contributor fields, images, ingredients and packaging are never read. ## Open Food Facts (one product document → one row) - **Code**: `code`, trimmed. It is kept only to match the product on the next import; there is no barcode lookup. - **Name**: the first non-blank of `product_name_en`, `product_name`, then `product_name_` (the product's main language). - **Brand**: the first comma-separated entry of `brands`. - **Countries**: each `countries_tags` entry through `off_countries.tsv`. Tags with no single country (`en:world`) and unknown tags are left out. - **Where the values come from.** Open Food Facts' 2026 nutrition model (product schema 1003 on) keeps a product's nutrition in `nutrition.aggregated_set`, and leaves `nutriments` empty or out; the export holds products in both shapes. The aggregated set is read first, when its `preparation` is `as_sold` and its `per` is `100g` or `100ml`: each nutrient named below is that set's `nutrients..value`, read as the `_100g` value, and the set's `per` gives per 100 g or per 100 ml. Two kinds of value are left out, as the old shape kept both apart from its `_100g` keys: one whose `source` is `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 (`nutrition.input_sets[source_index]`) holds only as `value_computed`. A value whose unit is not the expected one (kcal for `energy-kcal`, kJ for `energy-kj` and `energy`, % vol for `alcohol`, g for the rest) is left out and counted. Otherwise the `nutriments` `_100g` keys are read as below. A product whose aggregated set is for the prepared food, or per serving, is not read from that set; the prepare summary counts these apart. - **Per 100 ml** when `product_quantity_unit` is `ml`; when that unit is missing, when `quantity` ends in ml, cl or l. Otherwise per 100 g. Open Food Facts' `_100g` values are per 100 ml for products labelled by volume, and are used even when the label gives values per serving. - **Energy**: `energy-kcal_100g`; else `energy-kj_100g` ÷ 4.184; else `energy_100g` (kJ) ÷ 4.184. - **Protein, carbohydrate, fat**: `proteins_100g`, `carbohydrates_100g`, `fat_100g`. Carbohydrate is as labelled: UK and EU labels exclude fibre, US labels include it. - **Fibre, sugars, saturated fat**: `fiber_100g`, `sugars_100g`, `saturated-fat_100g` (g). - **Iron, calcium, vitamin C, magnesium, potassium**: the `_100g` value (g) × 1000 → mg. - **Vitamin D, vitamin B12**: the `_100g` value (g) × 1,000,000 → µg. - **Omega-3**: `omega-3-fat_100g` × 1000 → mg. Its parts are not added up. - **Sodium**: `sodium_100g` × 1000 → mg. Only when sodium is missing: `salt_100g` × 400 (1 g of salt holds 400 mg of sodium). - **Alcohol**: `alcohol_100g` is % by volume; units per 100 = % × 0.1 (one UK unit is 10 ml of ethanol). For a per-100 g product, 100 g is taken as about 100 ml. - **Serving**: `serving_quantity` when it is over 0 and at most 10,000, and its unit (`serving_quantity_unit`, when given) matches g or ml as above; the label is the `serving_size` text. Both are kept or neither is. - **Portions**: none. ## CoFID 2021 (one food → one row) - **Code**: Food Code. **Name**: Food Name. **Brand**: none. **Country**: GB. - **Per 100 ml** only for alcoholic beverages, food group Q (every Group code starting with Q), as the file's 1.1 Notes sheet states. Every other food, milk, juices and soft drinks included, is per 100 g, as CoFID gives it. - **Energy**: KCALS; else KJ ÷ 4.184. - **Protein, carbohydrate, fat**: PROT, CHO (available carbohydrate as monosaccharide equivalents, kept as given), FAT. - **Fibre**: AOACFIB only. NSP fibre is a different method and is never used in its place. - **Sugars, saturated fat**: TOTSUG, SATFOD. **Iron, calcium, vitamin C, magnesium, potassium, sodium**: FE, CA, VITC, MG, K, NA (mg). **Vitamin D, B12**: VITD, VITB12 (µg). - **Omega-3**: TOTn3PFOD × 1000 → mg. - **Alcohol**: ALCO ÷ 7.89 (7.89 g of ethanol is 10 ml, one UK unit), so 45% ABV gives 4.5 units. - **Cell codes**: a number is used as given; `Tr` (trace) is 0; `N` (no reliable figure) and blank are missing; any other text is missing and counted. - Each code is found by scanning the header rows of every sheet that has a Food Code column, and sheets are joined on Food Code. A code found in no sheet, or in two, stops the import. - The 1.4 Inorganics sheet has a blank Food Code header. A sheet like that is joined on the Food Code column of the sheet holding protein when its Food Name and Group headers sit in the same columns. Its food codes must then be exactly the same as that sheet's, or the import stops. ## USDA FNDDS and SR Legacy (one food → one row) - **Code**: `fdc_id`. **Name**: `description`. **Brand**: none. **Country**: US. Per 100 g (drinks too). - Values by FoodData Central nutrient id. Before use, each id's unit and name in `nutrient.csv` are checked; a mismatch stops the import. - The FNDDS file's `food_nutrient.csv` names each nutrient by its older USDA number (`nutrient_nbr`, 203 for protein) instead of its id (1003), so those numbers are mapped back to ids through `nutrient.csv`. A file that uses both, or a number `nutrient.csv` gives to two ids, stops the import. - **Energy**: 1008 (kcal); else 2048 (Atwater specific factors); else 2047 (Atwater general factors); else 1062 (kJ) ÷ 4.184. - **Protein, carbohydrate, fat**: 1003, 1005 (by difference, which includes fibre; kept as given), 1004. - **Fibre, sugars, saturated fat**: 1079; 2000, else 1063; 1258. - **Calcium, iron, magnesium, potassium, sodium, vitamin C**: 1087, 1089, 1090, 1092, 1093, 1162 (mg). - **Vitamin D**: 1114 (µg); else 1110 (IU) ÷ 40. **Vitamin B12**: 1178 (µg). - **Omega-3**: (ALA + EPA 1278 + DPA 1280 + DHA 1272) × 1000 → mg, where ALA is 1404 when given, else 1270. Missing parts are left out; with no part at all, omega-3 is missing. - **Alcohol**: 1018 (g) ÷ 7.89. - **Portions** (household measures): the label is `portion_description` when given; otherwise the amount (without trailing zeros), the measure unit unless it is "undetermined", and the modifier. The amount is `gram_weight`. Portions are ordered by `seq_num`, then by id. A portion is left out when its weight is 0 or less or over 10,000 g, its label is blank, it is "Quantity not specified", or its label repeats an earlier one for the same food. ## Rows that are left out Every skip is counted by reason in the run's summary. | # | Rule | Applies to | |---|---|---| | 1 | No usable name | all | | 2 | No energy in any energy field | all | | 3 | Protein, carbohydrate or fat missing | all | | 4 | Energy or a macro negative | all | | 5 | Over 1,000 kcal per 100 g or ml (usually kJ typed as kcal) | all | | 6 | Code missing or over 64 characters | all | | 7 | A code seen earlier in the same file (the first claims it) | all | | 8 | Marked obsolete (withdrawn from sale) | Open Food Facts | | 9 | Protein, carbohydrate or fat over 100 g per 100 g (150 per 100 ml) | Open Food Facts | | 10 | Protein + carbohydrate + fat over 105 g per 100 g (157.5 per 100 ml) | Open Food Facts | | 11 | Over 13 kcal and more than 30% + 5 kcal away from 4 × protein + 4 × carbohydrate + 9 × fat + 2 × fibre + 7 × alcohol (g) | Open Food Facts | | 12 | A line that is not a JSON object | Open Food Facts | Base foods (CoFID and USDA) get rules 1-7 only: they are laboratory data, and the label rules 9-11 would drop valid rows. How many base rows would fail 9-11 is reported for review only. **Values set to missing (the row is kept):** - a negative fibre, sugar, saturated fat, mineral, vitamin, omega-3 or alcohol value; - a value no food can hold: grams over 100 per 100 g (150 per 100 ml), mg over 100,000, µg over 9,999,999.999, alcohol over 12.7 units (pure ethanol); - Open Food Facts only: sugars more than carbohydrate + 0.5 g, or saturated fat more than fat + 0.5 g; - a serving outside (0, 10,000] or in the other unit (both serving fields). An Open Food Facts run also stops when more than 0.1% of lines are not JSON, or fewer than 25% of products give a row, since either means the export was cut short or changed shape. ## Versions - Each row has a **content hash**: SHA-256 of a compact JSON array of `"v1"`, the source, code, kind, name, brand, countries, basis, every value as its stored 3-decimal text (missing as null), the serving, and the portions. Zero and missing hash differently. - **Unchanged** products (same hash as the current row) are not written again. - **Changed** products get a new row, and the old row is marked retired at the same moment. Rows are never changed or removed otherwise, so anything that refers to the old row keeps its values. - **New** products get a row. A product that comes back after being retired gets a new row. - **Missing** products (no longer in the file, or now left out by a rule) are marked retired. - A source is refused unless the operator confirms it when more than 10% of its current rows are missing from the file, which usually means a cut-short file, or more than 50% changed, which usually means a change of file shape or units. Fewer changes are routine: products are edited at the source all the time. A change to these rules or to the hash version changes values, so it correctly gives every affected product a new row and needs that confirmation.