Download src/reference_db/phase_5/nutrition.py from offCanada/reference-db-system: direct link, hf CLI and curl.
- Browser
- Download file 20.1 kB
-
https://huggingface.co/offCanada/reference-db-system/resolve/main/src/reference_db/phase_5/nutrition.py
- Command line
-
hf download hf://offCanada/reference-db-system/src/reference_db/phase_5/nutrition.py
-
curl -L -o nutrition.py https://huggingface.co/offCanada/reference-db-system/resolve/main/src/reference_db/phase_5/nutrition.py
20.1 kB
| import json | |
| import re | |
| from pathlib import Path | |
| from typing import Any | |
| import pandas as pd | |
| from huggingface_hub import hf_hub_download | |
| HF_DATASET = "saraNour/compliments-brand" | |
| NUTRITION_FILE = "raw_data/nutrition.parquet" | |
| PHASE2_FILE = "data/phase_2/standardized_products.parquet" | |
| PHASE3_FILE = "data/phase_3/product_groups.parquet" | |
| PHASE4_FILE = "data/phase_4/product_variant_mapping.parquet" | |
| OUTPUT_DIR = Path("data/phase_5") | |
| NUTRITION_COLUMNS = [ | |
| "calories", | |
| "fat_g", | |
| "saturated_fat_g", | |
| "monounsaturated_fat_g", | |
| "polyunsaturated_fat_g", | |
| "omega6_g", | |
| "omega3_g", | |
| "sugar_alcohols_g", | |
| "carbohydrate_g", | |
| "fibre_g", | |
| "sugars_g", | |
| "protein_g", | |
| "sodium_mg", | |
| "potassium_mg", | |
| "calcium_mg", | |
| "iron_mg", | |
| "cholesterol_mg", | |
| ] | |
| SUSPICIOUS_THRESHOLDS = { | |
| "calories": 1000, | |
| "fat_g": 100, | |
| "protein_g": 100, | |
| } | |
| def load_raw_nutrition() -> pd.DataFrame: | |
| """Load raw nutrition data from Hugging Face.""" | |
| local_path = hf_hub_download( | |
| repo_id=HF_DATASET, | |
| filename=NUTRITION_FILE, | |
| repo_type="dataset", | |
| ) | |
| nutrition = pd.read_parquet(local_path) | |
| nutrition["retailer_product_id"] = ( | |
| nutrition["retailer_product_id"] | |
| .astype(str) | |
| .str.strip() | |
| ) | |
| return nutrition | |
| def load_phase_inputs() -> tuple[pd.DataFrame, pd.DataFrame, pd.DataFrame]: | |
| """Load Phase 2, Phase 3 and Phase 4 outputs.""" | |
| phase2 = pd.read_parquet(PHASE2_FILE) | |
| phase3 = pd.read_parquet(PHASE3_FILE) | |
| phase4 = pd.read_parquet(PHASE4_FILE) | |
| phase2["external_id"] = ( | |
| phase2["external_id"] | |
| .astype(str) | |
| .str.strip() | |
| ) | |
| phase3["external_id"] = ( | |
| phase3["external_id"] | |
| .astype(str) | |
| .str.strip() | |
| ) | |
| phase4["external_id"] = ( | |
| phase4["external_id"] | |
| .astype(str) | |
| .str.strip() | |
| ) | |
| return phase2, phase3, phase4 | |
| def validate_source_schema(nutrition: pd.DataFrame) -> dict[str, Any]: | |
| """Validate that the raw nutrition source contains the expected schema.""" | |
| required_columns = [ | |
| "retailer_product_id", | |
| "product_name", | |
| "brand", | |
| "size", | |
| "serving_size", | |
| "upc", | |
| "scraped_at", | |
| *NUTRITION_COLUMNS, | |
| ] | |
| missing_columns = [ | |
| column | |
| for column in required_columns | |
| if column not in nutrition.columns | |
| ] | |
| duplicate_ids = int( | |
| nutrition["retailer_product_id"].duplicated().sum() | |
| ) | |
| return { | |
| "required_columns": required_columns, | |
| "missing_columns": missing_columns, | |
| "duplicate_retailer_product_ids": duplicate_ids, | |
| "result": ( | |
| "PASS" | |
| if not missing_columns and duplicate_ids == 0 | |
| else "FAIL" | |
| ), | |
| } | |
| def assess_nutrition_quality( | |
| nutrition: pd.DataFrame, | |
| ) -> pd.DataFrame: | |
| """ | |
| Assess nutrition completeness and suspicious values. | |
| Status rules: | |
| - SUSPICIOUS: at least one suspicious nutrition value. | |
| - MISSING_FIELDS: at least one nutrition field is missing. | |
| - VALID: all nutrition fields are present and no suspicious value exists. | |
| """ | |
| result = nutrition.copy() | |
| missing_mask = result[NUTRITION_COLUMNS].isna() | |
| result["missing_field_count"] = missing_mask.sum(axis=1) | |
| result["missing_fields"] = missing_mask.apply( | |
| lambda row: ",".join( | |
| column | |
| for column in NUTRITION_COLUMNS | |
| if row[column] | |
| ), | |
| axis=1, | |
| ) | |
| suspicious_fields = [] | |
| for column, threshold in SUSPICIOUS_THRESHOLDS.items(): | |
| numeric_values = pd.to_numeric( | |
| result[column], | |
| errors="coerce", | |
| ) | |
| suspicious_fields.append( | |
| numeric_values > threshold | |
| ) | |
| if suspicious_fields: | |
| suspicious_mask = suspicious_fields[0].copy() | |
| for mask in suspicious_fields[1:]: | |
| suspicious_mask = suspicious_mask | mask | |
| else: | |
| suspicious_mask = pd.Series( | |
| False, | |
| index=result.index, | |
| ) | |
| result["suspicious"] = suspicious_mask | |
| suspicious_field_names = [] | |
| for index in result.index: | |
| fields = [] | |
| for column, threshold in SUSPICIOUS_THRESHOLDS.items(): | |
| value = pd.to_numeric( | |
| result.at[index, column], | |
| errors="coerce", | |
| ) | |
| if pd.notna(value) and value > threshold: | |
| fields.append(column) | |
| suspicious_field_names.append(",".join(fields)) | |
| result["suspicious_fields"] = suspicious_field_names | |
| result["nutrition_quality_status"] = "VALID" | |
| result.loc[ | |
| result["missing_field_count"] > 0, | |
| "nutrition_quality_status", | |
| ] = "MISSING_FIELDS" | |
| result.loc[ | |
| result["suspicious"], | |
| "nutrition_quality_status", | |
| ] = "SUSPICIOUS" | |
| return result | |
| def parse_serving_size( | |
| value: Any, | |
| ) -> tuple[float | None, str | None, str | None]: | |
| """ | |
| Parse serving size into amount, unit and parsing reason. | |
| Supported: | |
| - 100 g | |
| - 355 mL | |
| - 1 cup (250 ml) | |
| - 2 tsp (10 g) | |
| - kg -> g | |
| - L -> ml | |
| No density-based conversion is performed between mass and volume. | |
| """ | |
| if pd.isna(value): | |
| return None, None, "missing_serving_size" | |
| text = str(value).strip().lower() | |
| if not text: | |
| return None, None, "missing_serving_size" | |
| # Prefer an explicit gram equivalent such as: | |
| # "2 tsp (10 g)" | |
| # "1/8 tsp (0.6 g)" | |
| gram_match = re.search( | |
| r"\(\s*(\d+(?:\.\d+)?)\s*g(?:ram|rams)?\s*\)", | |
| text, | |
| ) | |
| if gram_match: | |
| amount = float(gram_match.group(1)) | |
| return amount, "g", None | |
| # Explicit mass. | |
| mass_match = re.search( | |
| r"(\d+(?:\.\d+)?)\s*(kg|kilograms?|g|grams?)\b", | |
| text, | |
| ) | |
| if mass_match: | |
| amount = float(mass_match.group(1)) | |
| unit = mass_match.group(2) | |
| if unit.startswith("kg"): | |
| amount *= 1000 | |
| return amount, "g", None | |
| # Explicit volume. | |
| volume_match = re.search( | |
| r"(\d+(?:\.\d+)?)\s*(l|litres?|liters?|ml|millilitres?|milliliters?)\b", | |
| text, | |
| ) | |
| if volume_match: | |
| amount = float(volume_match.group(1)) | |
| unit = volume_match.group(2) | |
| if unit in {"l", "litre", "litres", "liter", "liters"}: | |
| amount *= 1000 | |
| return amount, "ml", None | |
| return None, None, "unparseable_serving_size" | |
| def normalize_nutrition( | |
| nutrition: pd.DataFrame, | |
| ) -> pd.DataFrame: | |
| """ | |
| Normalize nutrition values to per-100g or per-100ml. | |
| Mass-based serving sizes produce per_100g values. | |
| Volume-based serving sizes produce per_100ml values. | |
| Mass and volume are intentionally kept separate. | |
| """ | |
| result = nutrition.copy() | |
| parsed = result["serving_size"].apply(parse_serving_size) | |
| result["serving_amount"] = parsed.apply( | |
| lambda value: value[0] | |
| ) | |
| result["serving_unit"] = parsed.apply( | |
| lambda value: value[1] | |
| ) | |
| result["serving_parse_reason"] = parsed.apply( | |
| lambda value: value[2] | |
| ) | |
| result["calories_per_100g"] = pd.NA | |
| result["calories_per_100ml"] = pd.NA | |
| for column in NUTRITION_COLUMNS: | |
| result[f"{column}_per_100g"] = pd.NA | |
| result[f"{column}_per_100ml"] = pd.NA | |
| mass_mask = result["serving_unit"] == "g" | |
| volume_mask = result["serving_unit"] == "ml" | |
| for column in NUTRITION_COLUMNS: | |
| numeric_values = pd.to_numeric( | |
| result[column], | |
| errors="coerce", | |
| ) | |
| per_100_mass = ( | |
| numeric_values / result["serving_amount"] * 100 | |
| ) | |
| per_100_volume = ( | |
| numeric_values / result["serving_amount"] * 100 | |
| ) | |
| result.loc[ | |
| mass_mask, | |
| f"{column}_per_100g", | |
| ] = per_100_mass[mass_mask] | |
| result.loc[ | |
| volume_mask, | |
| f"{column}_per_100ml", | |
| ] = per_100_volume[volume_mask] | |
| return result | |
| def build_product_nutrition( | |
| phase2: pd.DataFrame, | |
| phase3: pd.DataFrame, | |
| phase4: pd.DataFrame, | |
| nutrition: pd.DataFrame, | |
| ) -> pd.DataFrame: | |
| """ | |
| Build the integrated product_nutrition table. | |
| The product table is the driving table, so all 4,440 products | |
| remain present even when nutrition source data is unavailable. | |
| """ | |
| products = phase2[ | |
| [ | |
| "external_id", | |
| "source_retailer", | |
| "title", | |
| "brand", | |
| "barcode", | |
| "size", | |
| "variant_attributes", | |
| ] | |
| ].copy() | |
| groups = phase3[ | |
| [ | |
| "external_id", | |
| "group_id", | |
| ] | |
| ].copy() | |
| variants = phase4[ | |
| [ | |
| "external_id", | |
| "variant_id", | |
| ] | |
| ].copy() | |
| products = products.merge( | |
| groups, | |
| on="external_id", | |
| how="left", | |
| validate="one_to_one", | |
| ) | |
| products = products.merge( | |
| variants, | |
| on="external_id", | |
| how="left", | |
| validate="one_to_one", | |
| ) | |
| products = products.merge( | |
| nutrition, | |
| on="external_id", | |
| how="left", | |
| validate="one_to_one", | |
| suffixes=("", "_nutrition"), | |
| ) | |
| nutrition_present = ( | |
| products["nutrition_quality_status"].notna() | |
| ) | |
| products["match_method"] = pd.NA | |
| products["match_status"] = "NO_SOURCE_DATA" | |
| products.loc[ | |
| nutrition_present, | |
| "match_method", | |
| ] = "exact_external_id" | |
| products.loc[ | |
| nutrition_present, | |
| "match_status", | |
| ] = "MATCHED" | |
| products["nutrition_quality_status"] = ( | |
| products["nutrition_quality_status"] | |
| .fillna("NO_SOURCE_DATA") | |
| ) | |
| products["missing_fields"] = ( | |
| products["missing_fields"] | |
| .fillna("") | |
| ) | |
| products["suspicious_fields"] = ( | |
| products["suspicious_fields"] | |
| .fillna("") | |
| ) | |
| products["missing_field_count"] = ( | |
| products["missing_field_count"] | |
| .fillna(0) | |
| .astype(int) | |
| ) | |
| return products | |
| def build_quality_report( | |
| nutrition: pd.DataFrame, | |
| ) -> pd.DataFrame: | |
| """Build a compact quality report.""" | |
| total = len(nutrition) | |
| status_counts = ( | |
| nutrition["nutrition_quality_status"] | |
| .value_counts() | |
| .to_dict() | |
| ) | |
| rows = [ | |
| { | |
| "metric": "total_records", | |
| "value": total, | |
| }, | |
| { | |
| "metric": "valid_records", | |
| "value": status_counts.get("VALID", 0), | |
| }, | |
| { | |
| "metric": "missing_fields_records", | |
| "value": status_counts.get("MISSING_FIELDS", 0), | |
| }, | |
| { | |
| "metric": "suspicious_records", | |
| "value": status_counts.get("SUSPICIOUS", 0), | |
| }, | |
| { | |
| "metric": "records_with_any_missing_field", | |
| "value": int( | |
| (nutrition["missing_field_count"] > 0).sum() | |
| ), | |
| }, | |
| { | |
| "metric": "records_with_all_fields_missing", | |
| "value": int( | |
| nutrition[NUTRITION_COLUMNS] | |
| .isna() | |
| .all(axis=1) | |
| .sum() | |
| ), | |
| }, | |
| ] | |
| for column in NUTRITION_COLUMNS: | |
| rows.append( | |
| { | |
| "metric": f"missing_{column}", | |
| "value": int( | |
| nutrition[column].isna().sum() | |
| ), | |
| } | |
| ) | |
| for column, threshold in SUSPICIOUS_THRESHOLDS.items(): | |
| numeric_values = pd.to_numeric( | |
| nutrition[column], | |
| errors="coerce", | |
| ) | |
| rows.append( | |
| { | |
| "metric": f"suspicious_{column}", | |
| "value": int( | |
| (numeric_values > threshold).sum() | |
| ), | |
| } | |
| ) | |
| return pd.DataFrame(rows) | |
| def build_statistics( | |
| product_nutrition: pd.DataFrame, | |
| nutrition_normalized: pd.DataFrame, | |
| ) -> dict[str, Any]: | |
| """Build Phase 5 summary statistics.""" | |
| total_products = len(product_nutrition) | |
| matched = int( | |
| ( | |
| product_nutrition["match_status"] | |
| == "MATCHED" | |
| ).sum() | |
| ) | |
| without_nutrition = int( | |
| ( | |
| product_nutrition["match_status"] | |
| == "NO_SOURCE_DATA" | |
| ).sum() | |
| ) | |
| coverage = ( | |
| matched / total_products | |
| if total_products | |
| else 0 | |
| ) | |
| status_counts = ( | |
| nutrition_normalized["nutrition_quality_status"] | |
| .value_counts() | |
| .to_dict() | |
| ) | |
| return { | |
| "phase": 5, | |
| "total_products": total_products, | |
| "nutrition_records": len(nutrition_normalized), | |
| "matched_products": matched, | |
| "products_without_nutrition": without_nutrition, | |
| "nutrition_coverage": round(coverage, 4), | |
| "nutrition_quality": { | |
| "VALID": status_counts.get("VALID", 0), | |
| "MISSING_FIELDS": status_counts.get( | |
| "MISSING_FIELDS", | |
| 0, | |
| ), | |
| "SUSPICIOUS": status_counts.get( | |
| "SUSPICIOUS", | |
| 0, | |
| ), | |
| }, | |
| "serving_units": ( | |
| nutrition_normalized["serving_unit"] | |
| .value_counts(dropna=False) | |
| .to_dict() | |
| ), | |
| } | |
| def validate_phase5( | |
| phase2: pd.DataFrame, | |
| phase3: pd.DataFrame, | |
| phase4: pd.DataFrame, | |
| nutrition_normalized: pd.DataFrame, | |
| product_nutrition: pd.DataFrame, | |
| ) -> dict[str, Any]: | |
| """Run Phase 5 validation checks.""" | |
| checks = {} | |
| checks["product_count"] = { | |
| "expected": len(phase2), | |
| "actual": len(product_nutrition), | |
| "result": ( | |
| "PASS" | |
| if len(product_nutrition) == len(phase2) | |
| else "FAIL" | |
| ), | |
| } | |
| checks["nutrition_count"] = { | |
| "expected": len(nutrition_normalized), | |
| "actual": int( | |
| ( | |
| product_nutrition["match_status"] | |
| == "MATCHED" | |
| ).sum() | |
| ), | |
| "result": ( | |
| "PASS" | |
| if len(nutrition_normalized) | |
| == int( | |
| ( | |
| product_nutrition["match_status"] | |
| == "MATCHED" | |
| ).sum() | |
| ) | |
| else "FAIL" | |
| ), | |
| } | |
| checks["unique_external_id"] = { | |
| "duplicates": int( | |
| product_nutrition["external_id"] | |
| .duplicated() | |
| .sum() | |
| ), | |
| "result": ( | |
| "PASS" | |
| if product_nutrition["external_id"] | |
| .duplicated() | |
| .sum() | |
| == 0 | |
| else "FAIL" | |
| ), | |
| } | |
| checks["group_ids"] = { | |
| "null_count": int( | |
| product_nutrition["group_id"] | |
| .isna() | |
| .sum() | |
| ), | |
| "result": ( | |
| "PASS" | |
| if product_nutrition["group_id"] | |
| .isna() | |
| .sum() | |
| == 0 | |
| else "FAIL" | |
| ), | |
| } | |
| checks["variant_ids"] = { | |
| "null_count": int( | |
| product_nutrition["variant_id"] | |
| .isna() | |
| .sum() | |
| ), | |
| "result": ( | |
| "PASS" | |
| if product_nutrition["variant_id"] | |
| .isna() | |
| .sum() | |
| == 0 | |
| else "FAIL" | |
| ), | |
| } | |
| checks["deterministic_matching"] = { | |
| "unmatched_nutrition": int( | |
| ~nutrition_normalized["external_id"].isin( | |
| phase2["external_id"] | |
| ).sum() | |
| ) | |
| if len(nutrition_normalized) | |
| else 0, | |
| "result": ( | |
| "PASS" | |
| if set( | |
| nutrition_normalized["external_id"] | |
| ).issubset( | |
| set(phase2["external_id"]) | |
| ) | |
| else "FAIL" | |
| ), | |
| } | |
| checks["mass_volume_separated"] = { | |
| "g_records": int( | |
| ( | |
| nutrition_normalized["serving_unit"] | |
| == "g" | |
| ).sum() | |
| ), | |
| "ml_records": int( | |
| ( | |
| nutrition_normalized["serving_unit"] | |
| == "ml" | |
| ).sum() | |
| ), | |
| "result": "PASS", | |
| } | |
| all_passed = all( | |
| check["result"] == "PASS" | |
| for check in checks.values() | |
| ) | |
| return { | |
| "overall": { | |
| "result": "PASS" if all_passed else "FAIL", | |
| }, | |
| "checks": checks, | |
| } | |
| def run_phase5() -> dict[str, Any]: | |
| """Run the complete Phase 5 nutrition pipeline.""" | |
| OUTPUT_DIR.mkdir( | |
| parents=True, | |
| exist_ok=True, | |
| ) | |
| print("Loading raw nutrition...") | |
| raw_nutrition = load_raw_nutrition() | |
| print( | |
| f"Raw nutrition rows: {len(raw_nutrition)}" | |
| ) | |
| schema_validation = validate_source_schema( | |
| raw_nutrition | |
| ) | |
| if schema_validation["result"] != "PASS": | |
| raise ValueError( | |
| "Nutrition source schema validation failed: " | |
| f"{schema_validation}" | |
| ) | |
| print("Loading Phase 2 / 3 / 4 outputs...") | |
| phase2, phase3, phase4 = load_phase_inputs() | |
| nutrition = raw_nutrition.copy() | |
| nutrition["external_id"] = ( | |
| nutrition["retailer_product_id"] | |
| .astype(str) | |
| .str.strip() | |
| ) | |
| print("Assessing nutrition quality...") | |
| nutrition = assess_nutrition_quality( | |
| nutrition | |
| ) | |
| print("Normalizing nutrition...") | |
| nutrition = normalize_nutrition( | |
| nutrition | |
| ) | |
| nutrition_normalized = nutrition.copy() | |
| print("Building product nutrition integration...") | |
| product_nutrition = build_product_nutrition( | |
| phase2=phase2, | |
| phase3=phase3, | |
| phase4=phase4, | |
| nutrition=nutrition_normalized, | |
| ) | |
| print("Building quality report...") | |
| nutrition_quality = build_quality_report( | |
| nutrition_normalized | |
| ) | |
| statistics = build_statistics( | |
| product_nutrition=product_nutrition, | |
| nutrition_normalized=nutrition_normalized, | |
| ) | |
| print("Validating Phase 5...") | |
| validation = validate_phase5( | |
| phase2=phase2, | |
| phase3=phase3, | |
| phase4=phase4, | |
| nutrition_normalized=nutrition_normalized, | |
| product_nutrition=product_nutrition, | |
| ) | |
| nutrition_normalized.to_parquet( | |
| OUTPUT_DIR / "nutrition_normalized.parquet", | |
| index=False, | |
| ) | |
| product_nutrition.to_parquet( | |
| OUTPUT_DIR / "product_nutrition.parquet", | |
| index=False, | |
| ) | |
| nutrition_quality.to_parquet( | |
| OUTPUT_DIR / "nutrition_quality.parquet", | |
| index=False, | |
| ) | |
| with open( | |
| OUTPUT_DIR / "phase5_statistics.json", | |
| "w", | |
| encoding="utf-8", | |
| ) as file: | |
| json.dump( | |
| statistics, | |
| file, | |
| indent=2, | |
| default=str, | |
| ) | |
| with open( | |
| OUTPUT_DIR / "phase5_validation.json", | |
| "w", | |
| encoding="utf-8", | |
| ) as file: | |
| json.dump( | |
| validation, | |
| file, | |
| indent=2, | |
| default=str, | |
| ) | |
| print("\n=== PHASE 5 COMPLETE ===") | |
| print( | |
| f"Products: {statistics['total_products']}" | |
| ) | |
| print( | |
| f"Nutrition records: " | |
| f"{statistics['nutrition_records']}" | |
| ) | |
| print( | |
| f"Matched: " | |
| f"{statistics['matched_products']}" | |
| ) | |
| print( | |
| f"Without nutrition: " | |
| f"{statistics['products_without_nutrition']}" | |
| ) | |
| print( | |
| f"Coverage: " | |
| f"{statistics['nutrition_coverage'] * 100:.1f}%" | |
| ) | |
| print( | |
| "Quality:", | |
| statistics["nutrition_quality"], | |
| ) | |
| print( | |
| f"Validation: " | |
| f"{validation['overall']['result']}" | |
| ) | |
| return { | |
| "nutrition_normalized": nutrition_normalized, | |
| "product_nutrition": product_nutrition, | |
| "nutrition_quality": nutrition_quality, | |
| "statistics": statistics, | |
| "validation": validation, | |
| } | |
| if __name__ == "__main__": | |
| run_phase5() |