#!/usr/bin/env python3 """Normalize UK CoFID 2021 (McCance and Widdowson's Composition of Foods Integrated Dataset) -> FoodIndex/normalized/extra_U.jsonl Source: Public Health England (2021), "McCance and Widdowson's The Composition of Foods Integrated Dataset 2021", https://www.gov.uk/government/publications/composition-of-foods-integrated-dataset-cofid Licence: Open Government Licence v3.0 (GOV.UK: "All content is available under the Open Government Licence v3.0, except where otherwise stated"; the files state no exception; (c) Crown copyright 2021). Rules: * Energy = column KCALS ("Energy (kcal)") of sheet "1.3 Proximates", per 100 g of food, except alcoholic beverages (food group Q*), which CoFID gives per 100 ml (user guide p.7 / sheet 1.1 Notes) -> unit "ml". * "N" (no reliable value) or empty -> record dropped. "Tr" (trace) -> 0.0 kcal. * CoFID has no portion sizes -> portions []. * Category = CoFID sub-group name from Appendix B of the user guide. * Display-name clarifications (documented, data unchanged): UK "Potato chips" are French fries, so those names become "Potato chips (fries), ..."; "HOMEMADE" is lower-cased. Two name collisions are resolved: 19-607 (recipe dish) gets "..., with onions, flour and stock (recipe)"; 16-127 is dropped because its name says "weighed with bones and skin" while 16-128 (calculated from it, about half the energy) carries the same name, so 16-127 must really be flesh only - the name/value pairing is doubtful. Usage: python3 -I normalize_extra_U.py """ import json import os import re import sys import unicodedata from collections import Counter sys.path.insert(0, os.path.dirname(os.path.abspath(__file__))) from xlsxlite import Workbook # noqa: E402 SRC = "U" DATASET_DATE = "2021-03-19" # GOV.UK publication "Last updated: 19 March 2021" (2021 dataset + user guide) # Appendix B "Food sub-group codes" of the CoFID 2021 user guide. GROUPS = { 'A': 'Cereals and cereal products', 'AA': 'Flours, grains and starches', 'AB': 'Sandwiches', 'AC': 'Rice', 'AD': 'Pasta', 'AE': 'Pizzas', 'AF': 'Breads', 'AG': 'Rolls', 'AI': 'Breakfast cereals', 'AK': 'Infant cereal foods', 'AM': 'Biscuits', 'AN': 'Cakes', 'AO': 'Pastry', 'AP': 'Buns and pastries', 'AS': 'Puddings', 'AT': 'Savouries', 'B': 'Milk and milk products', 'BA': 'Cows milk', 'BAB': 'Breakfast milk', 'BAE': 'Skimmed milk', 'BAH': 'Semi-skimmed milk', 'BAK': 'Whole milk', 'BAN': 'Channel Island milk', 'BAR': 'Processed milks', 'BC': 'Other milks', 'BF': 'Infant formulas', 'BFD': 'Whey-based modified milks', 'BFG': 'Non-whey-based modified milks', 'BFJ': 'Soya-based modified milks', 'BFP': 'Follow-on formulas', 'BH': 'Milk-based drinks', 'BJ': 'Creams', 'BJC': 'Fresh creams (pasteurised)', 'BJF': 'Frozen creams (pasteurised)', 'BJL': 'Sterilised creams', 'BJP': 'UHT creams', 'BJS': 'Imitation creams', 'BL': 'Cheeses', 'BN': 'Yogurts', 'BNE': 'Whole milk yogurts', 'BNH': 'Low fat yogurts', 'BNS': 'Other yogurts', 'BP': 'Ice creams', 'BR': 'Puddings and chilled desserts', 'BV': 'Savoury dishes and sauces', 'C': 'Eggs', 'CA': 'Eggs', 'CD': 'Egg dishes', 'CDE': 'Savoury egg dishes', 'CDH': 'Sweet egg dishes', 'D': 'Vegetables', 'DA': 'Potatoes', 'DAE': 'Early potatoes', 'DAM': 'Main crop potatoes', 'DAP': 'Chipped old potatoes', 'DAR': 'Potato products', 'DB': 'Beans and lentils', 'DF': 'Peas', 'DG': 'Vegetables, general', 'DI': 'Vegetables, dried', 'DR': 'Vegetable dishes', 'F': 'Fruit', 'FA': 'Fruit, general', 'FC': 'Fruit juices', 'G': 'Nuts and seeds', 'GA': 'Nuts and seeds, general', 'H': 'Herbs and spices', 'J': 'Fish and fish products', 'JA': 'White fish', 'JC': 'Fatty fish', 'JK': 'Crustacea', 'JM': 'Molluscs', 'JR': 'Fish products and dishes', 'M': 'Meat and meat products', 'MA': 'Meat', 'MAA': 'Bacon', 'MAC': 'Beef', 'MAE': 'Lamb', 'MAG': 'Pork', 'MAI': 'Veal', 'MC': 'Poultry', 'MCA': 'Chicken', 'MCC': 'Duck', 'MCE': 'Goose', 'MCG': 'Grouse', 'MCI': 'Partridge', 'MCK': 'Pheasant', 'MCM': 'Pigeon', 'MCO': 'Turkey', 'ME': 'Game', 'MEA': 'Hare', 'MEC': 'Rabbit', 'MEE': 'Venison', 'MG': 'Offal', 'MBG': 'Burgers and grillsteaks', 'MI': 'Meat products', 'MIG': 'Other meat products', 'MR': 'Meat dishes', 'O': 'Fats and oils', 'OA': 'Spreading fats', 'OB': 'Animal fats', 'OC': 'Oils', 'OE': 'Non-animal fats', 'OF': 'Cooking fats', 'P': 'Beverages', 'PA': 'Powdered drinks, essences and infusions', 'PAA': 'Powdered drinks and essences', 'PAC': 'Infusions', 'PC': 'Soft drinks', 'PCA': 'Carbonated drinks', 'PCC': 'Squash and cordials', 'PE': 'Juices', 'Q': 'Alcoholic beverages', 'QA': 'Beers', 'QC': 'Ciders', 'QE': 'Wines', 'QF': 'Fortified wines', 'QG': 'Vermouths', 'QI': 'Liqueurs', 'QK': 'Spirits', 'S': 'Sugars, preserves and snacks', 'SC': 'Sugars, syrups and preserves', 'SE': 'Confectionery', 'SEA': 'Chocolate confectionery', 'SEC': 'Non-chocolate confectionery', 'SN': 'Savoury snacks', 'SNA': 'Potato-based snacks', 'SNB': 'Potato and mixed cereal snacks', 'SNC': 'Non-potato snacks', 'W': 'Soups, sauces and miscellaneous foods', 'WA': 'Soups', 'WAA': 'Homemade soups', 'WAC': 'Canned soups', 'WAE': 'Packet soups', 'WC': 'Sauces', 'WCD': 'Dairy sauces', 'WCG': 'Salad sauces, dressings and pickles', 'WCN': 'Non-salad sauces', 'WE': 'Pickles and chutneys', 'WY': 'Miscellaneous foods', } NAME_OVERRIDES = {"19-607": "Beef, mince, stewed, with onions, flour and stock (recipe)"} DROP_CODES = {"16-127": "name_contradicts_value(see docstring)"} def clean_name(s: str) -> str: s = unicodedata.normalize("NFC", s or "") s = re.sub(r"\s+", " ", s).strip() s = s.replace("HOMEMADE", "homemade") s = re.sub(r"^Potato chips,", "Potato chips (fries),", s) return s def main() -> None: src, out_path = sys.argv[1], sys.argv[2] wb = Workbook(src) rows = list(wb.rows("1.3 Proximates")) codes_row = rows[1] ki = codes_row.index("KCALS") drops = Counter() out = [] seen = set() for r in rows[3:]: if not r or not r[0] or not str(r[0]).strip(): continue code = str(r[0]).strip() if code in seen: # CoFID 2021 reuses 13-669 for two different foods (aubergine, roasted / watercress, raw): # keep both, the later one with a stable "-2" suffix. k = 2 while f"{code}-{k}" in seen: k += 1 code = f"{code}-{k}" drops["(kept) duplicate_source_code_suffixed"] += 1 seen.add(code) if code in DROP_CODES: drops[DROP_CODES[code]] += 1 continue name = NAME_OVERRIDES.get(code) or clean_name(r[1] if len(r) > 1 else "") group = (str(r[3]).strip() if len(r) > 3 and r[3] is not None else "") v = r[ki] if len(r) > ki else None if isinstance(v, str): vs = v.strip() if vs == "Tr": kcal = 0.0 drops["(kept) trace_energy_as_0"] += 1 elif vs in ("N", ""): drops["energy_N_or_missing"] += 1 continue else: try: kcal = float(vs) except ValueError: drops["energy_not_numeric"] += 1 continue elif isinstance(v, float): kcal = v else: drops["energy_N_or_missing"] += 1 continue if not (0.0 <= kcal <= 900.0): drops["energy_out_of_range"] += 1 continue if not (2 <= len(name) <= 120): drops["bad_name_length"] += 1 continue unit = "ml" if group.startswith("Q") else "g" cat = GROUPS.get(group) or GROUPS.get(group[:2]) or GROUPS.get(group[:1]) or "" out.append({ "id": f"{SRC}:{code}", "name": name, "brand": "", "kcal": round(kcal, 1), "unit": unit, "portions": [], "barcode": "", "src": SRC, "generic": True, "pop": None, "date": DATASET_DATE, "countries": ["gb"], "lang": "en", "category": cat, }) names = Counter(x["name"].lower() for x in out) dup = [n for n, c in names.items() if c > 1] if dup: raise SystemExit("duplicate names left: %r" % dup) out.sort(key=lambda x: x["id"]) with open(out_path, "w", encoding="utf-8") as f: for rec in out: f.write(json.dumps(rec, ensure_ascii=False, separators=(",", ":")) + "\n") print("foods in sheet:", len(seen)) print("written:", len(out), "->", out_path) print("units:", dict(Counter(x["unit"] for x in out))) print("dropped/notes:", dict(drops)) if __name__ == "__main__": main()