{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Phase 3 — Product Domain + Taxonomy + Product Grouping\n", "## Compliments Reference DB Pipeline\n", "\n", "**Version:** 1.1.0\n", "**Date:** 2026-07-30\n", "\n", "**Input:** Phase 2 output (`phase2_output.parquet`)\n", "\n", "**This phase does:**\n", "A. Food / Non-Food classification (product_domain)\n", "B. Taxonomy classification (metadata — temporary deterministic taxonomy)\n", "C. Identity-based deterministic product grouping\n", "D. Ambiguous-case detection\n", "E. Validation and regression tests\n", "\n", "**Note:** Taxonomy is a temporary rule-based classification layer.\n", "It is NOT GPC, NOT Agribalyse, NOT an official external taxonomy." ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "execution": { "iopub.execute_input": "2026-07-30T17:08:38.184497Z", "iopub.status.busy": "2026-07-30T17:08:38.181586Z", "iopub.status.idle": "2026-07-30T17:08:39.157905Z", "shell.execute_reply": "2026-07-30T17:08:39.154706Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Python: 3.13.7 (main, Mar 3 2026, 12:19:54) [GCC 15.2.0]\n", "pandas: 3.0.5\n", "numpy: 2.5.1\n", "Timestamp: 2026-07-30T17:08:39.147086+00:00\n" ] } ], "source": [ "import json, os, sys, re, uuid\n", "from datetime import datetime, timezone\n", "from pathlib import Path\n", "from collections import Counter\n", "\n", "import pandas as pd\n", "import numpy as np\n", "\n", "print(f\"Python: {sys.version}\")\n", "print(f\"pandas: {pd.__version__}\")\n", "print(f\"numpy: {np.__version__}\")\n", "print(f\"Timestamp: {datetime.now(timezone.utc).isoformat()}\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 1. Load Phase 2 Output" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "execution": { "iopub.execute_input": "2026-07-30T17:08:39.356596Z", "iopub.status.busy": "2026-07-30T17:08:39.355863Z", "iopub.status.idle": "2026-07-30T17:08:39.545028Z", "shell.execute_reply": "2026-07-30T17:08:39.542833Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Loaded: 4440 rows, 38 columns\n", "\n", "Columns: ['upc', 'external_id', 'brand', 'title', 'price', 'price_currency', 'size', 'size_amount', 'size_unit', 'size_qty', 'size_unit_norm', 'image_url', 'source', 'source_url', 'brand_clean', 'brand_raw', 'title_clean', 'title_raw', 'upc_raw', 'external_id_raw', 'is_organic', 'is_gluten_free', 'is_naturally_simple', 'is_sugar_free', 'is_unsalted', 'is_lactose_free', 'is_peanut_free', 'is_plant_based', 'is_reduced_sodium', 'fat_level', 'fat_percentage', 'flavour', 'formulation', 'brand_norm', 'product_line', 'variant_attributes', 'core_title', 'identity_hash']\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
upcexternal_idbrandtitlepriceprice_currencysizesize_amountsize_unitsize_qty...is_reduced_sodiumfat_levelfat_percentageflavourformulationbrand_normproduct_linevariant_attributescore_titleidentity_hash
0055742522167297608EAComplimentsCompliments Cold And Sinus Plus 20 Count9.99CAD20 per pack20.000count1...FalseregularNaN[lime][]ComplimentsCore{\"count\": 20}Cold And Sinus Plus0|0|0|0|0|0|0|0|0|fat_none|regular|Core|lime|
1055742335200464324EAComplimentsCompliments Twister T2 Light Bulbs 23W Soft Wh...11.99CAD2 per pack2.000count1...Falsereduced_fatNaN[lime][]ComplimentsCore{\"count\": 2}Twister T2 Light Bulbs 23W Soft White0|0|0|0|0|0|0|0|0|fat_none|reduced_fat|Core|lime|
2990002600400144288CSComplimentsCompliments Zero Calorie Soft Drink 12 x 355ml5.49CAD12 x 29.583ml29.583ml12...FalseregularNaN[lime][]ComplimentsCore{\"qty\": 12, \"amount\": 29.583, \"unit\": \"ml\"}Zero Calorie Soft Drink0|0|0|0|0|0|0|0|0|fat_none|regular|Core|lime|
3055742569391312204EAComplimentsCompliments Kalamata Whole Olives 375 ml3.29CAD375ml375.000ml1...FalseregularNaN[lime][whole]ComplimentsCore{\"amount\": 375.0, \"unit\": \"ml\"}Kalamata Whole Olives0|0|0|0|0|0|0|0|0|fat_none|regular|Core|lime|w...
4055742561111259928EAComplimentsCompliments Sliced Almonds 250 g7.99CAD250g250.000g1...FalseregularNaN[almond, lime][sliced]ComplimentsCore{\"amount\": 250.0, \"unit\": \"g\"}Sliced Almonds0|0|0|0|0|0|0|0|0|fat_none|regular|Core|almond...
\n", "

5 rows × 38 columns

\n", "
" ], "text/plain": [ " upc external_id brand \\\n", "0 055742522167 297608EA Compliments \n", "1 055742335200 464324EA Compliments \n", "2 990002600400 144288CS Compliments \n", "3 055742569391 312204EA Compliments \n", "4 055742561111 259928EA Compliments \n", "\n", " title price price_currency \\\n", "0 Compliments Cold And Sinus Plus 20 Count 9.99 CAD \n", "1 Compliments Twister T2 Light Bulbs 23W Soft Wh... 11.99 CAD \n", "2 Compliments Zero Calorie Soft Drink 12 x 355ml 5.49 CAD \n", "3 Compliments Kalamata Whole Olives 375 ml 3.29 CAD \n", "4 Compliments Sliced Almonds 250 g 7.99 CAD \n", "\n", " size size_amount size_unit size_qty ... is_reduced_sodium \\\n", "0 20 per pack 20.000 count 1 ... False \n", "1 2 per pack 2.000 count 1 ... False \n", "2 12 x 29.583ml 29.583 ml 12 ... False \n", "3 375ml 375.000 ml 1 ... False \n", "4 250g 250.000 g 1 ... False \n", "\n", " fat_level fat_percentage flavour formulation brand_norm \\\n", "0 regular NaN [lime] [] Compliments \n", "1 reduced_fat NaN [lime] [] Compliments \n", "2 regular NaN [lime] [] Compliments \n", "3 regular NaN [lime] [whole] Compliments \n", "4 regular NaN [almond, lime] [sliced] Compliments \n", "\n", " product_line variant_attributes \\\n", "0 Core {\"count\": 20} \n", "1 Core {\"count\": 2} \n", "2 Core {\"qty\": 12, \"amount\": 29.583, \"unit\": \"ml\"} \n", "3 Core {\"amount\": 375.0, \"unit\": \"ml\"} \n", "4 Core {\"amount\": 250.0, \"unit\": \"g\"} \n", "\n", " core_title \\\n", "0 Cold And Sinus Plus \n", "1 Twister T2 Light Bulbs 23W Soft White \n", "2 Zero Calorie Soft Drink \n", "3 Kalamata Whole Olives \n", "4 Sliced Almonds \n", "\n", " identity_hash \n", "0 0|0|0|0|0|0|0|0|0|fat_none|regular|Core|lime| \n", "1 0|0|0|0|0|0|0|0|0|fat_none|reduced_fat|Core|lime| \n", "2 0|0|0|0|0|0|0|0|0|fat_none|regular|Core|lime| \n", "3 0|0|0|0|0|0|0|0|0|fat_none|regular|Core|lime|w... \n", "4 0|0|0|0|0|0|0|0|0|fat_none|regular|Core|almond... \n", "\n", "[5 rows x 38 columns]" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "INPUT_PATH = Path(\"/home/sara/gsoc/compliments-reference-db/phase2/outputs/phase2_output.parquet\")\n", "VERSION = \"1.1.0\"\n", "\n", "df = pd.read_parquet(INPUT_PATH)\n", "print(f\"Loaded: {df.shape[0]} rows, {df.shape[1]} columns\")\n", "print(f\"\\nColumns: {list(df.columns)}\")\n", "df.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 2. Food / Non-Food Classification" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "execution": { "iopub.execute_input": "2026-07-30T17:08:39.553428Z", "iopub.status.busy": "2026-07-30T17:08:39.552750Z", "iopub.status.idle": "2026-07-30T17:08:40.924618Z", "shell.execute_reply": "2026-07-30T17:08:40.923455Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Domain distribution:\n", "product_domain\n", "food 2330\n", "unknown 1868\n", "non_food 242\n" ] } ], "source": [ "# Deterministic rules based on product title keywords\n", "FOOD_NON_FOOD_RULES = [\n", " # NON-FOOD: Pet food\n", " (r\"\\bcat food\\b\", \"non_food\"),\n", " (r\"\\bdog food\\b\", \"non_food\"),\n", " (r\"\\bpet food\\b\", \"non_food\"),\n", " (r\"\\bcat treat\", \"non_food\"),\n", " (r\"\\bdog treat\", \"non_food\"),\n", " # NON-FOOD: Medications\n", " (r\"\\bibuprofen\\b\", \"non_food\"),\n", " (r\"\\bacetaminophen\\b\", \"non_food\"),\n", " (r\"\\bmelatonin\\b\", \"non_food\"),\n", " (r\"\\bvitamin\\b\", \"non_food\"),\n", " (r\"\\bsupplement\\b\", \"non_food\"),\n", " # NON-FOOD: Personal care\n", " (r\"\\btampon\", \"non_food\"),\n", " (r\"\\btoothbrush\", \"non_food\"),\n", " (r\"\\btoothpaste\\b\", \"non_food\"),\n", " (r\"\\bshampoo\\b\", \"non_food\"),\n", " (r\"\\bconditioner\\b\", \"non_food\"),\n", " (r\"\\bdeodorant\\b\", \"non_food\"),\n", " (r\"\\blotion\\b\", \"non_food\"),\n", " # NON-FOOD: Household\n", " (r\"\\bdetergent\\b\", \"non_food\"),\n", " (r\"\\blaundry\\b\", \"non_food\"),\n", " (r\"\\bgarbage bag\", \"non_food\"),\n", " (r\"\\bpaper towel\", \"non_food\"),\n", " # NON-FOOD: Kitchenware\n", " (r\"\\blight bulb\\b\", \"non_food\"),\n", " (r\"\\bfoil container\", \"non_food\"),\n", " # FOOD: Dairy\n", " (r\"\\byogurt\\b\", \"food\"),\n", " (r\"\\bcottage cheese\\b\", \"food\"),\n", " (r\"\\bcream cheese\\b\", \"food\"),\n", " (r\"\\bsour cream\\b\", \"food\"),\n", " (r\"\\bmilk\\b\", \"food\"),\n", " (r\"\\bbutter\\b\", \"food\"),\n", " (r\"\\bcheese\\b\", \"food\"),\n", " # FOOD: Meat/Seafood\n", " (r\"\\bchicken\\b\", \"food\"),\n", " (r\"\\bbeef\\b\", \"food\"),\n", " (r\"\\bpork\\b\", \"food\"),\n", " (r\"\\bsausage\\b\", \"food\"),\n", " (r\"\\bbacon\\b\", \"food\"),\n", " (r\"\\bturkey\\b\", \"food\"),\n", " (r\"\\bsalmon\\b\", \"food\"),\n", " (r\"\\btuna\\b\", \"food\"),\n", " (r\"\\bshrimp\\b\", \"food\"),\n", " (r\"\\bfish\\b\", \"food\"),\n", " # FOOD: Bakery\n", " (r\"\\bbread\\b\", \"food\"),\n", " (r\"\\bbagel\\b\", \"food\"),\n", " (r\"\\bmuffin\\b\", \"food\"),\n", " (r\"\\bcroissant\\b\", \"food\"),\n", " (r\"\\btortilla\\b\", \"food\"),\n", " (r\"\\bcookie\\b\", \"food\"),\n", " (r\"\\bcake\\b\", \"food\"),\n", " # FOOD: Beverages\n", " (r\"\\bjuice\\b\", \"food\"),\n", " (r\"\\bwater\\b\", \"food\"),\n", " (r\"\\bcoffee\\b\", \"food\"),\n", " (r\"\\btea\\b\", \"food\"),\n", " (r\"\\bsoda\\b\", \"food\"),\n", " (r\"\\bdrink\\b\", \"food\"),\n", " # FOOD: Pantry\n", " (r\"\\bpasta\\b\", \"food\"),\n", " (r\"\\brice\\b\", \"food\"),\n", " (r\"\\boat\\b\", \"food\"),\n", " (r\"\\bcereal\\b\", \"food\"),\n", " (r\"\\bgranola\\b\", \"food\"),\n", " (r\"\\bsugar\\b\", \"food\"),\n", " (r\"\\bsalt\\b\", \"food\"),\n", " (r\"\\bsauce\\b\", \"food\"),\n", " (r\"\\bketchup\\b\", \"food\"),\n", " (r\"\\bmustard\\b\", \"food\"),\n", " (r\"\\bmayonnaise\\b\", \"food\"),\n", " (r\"\\bpeanut butter\\b\", \"food\"),\n", " (r\"\\bhoney\\b\", \"food\"),\n", " (r\"\\bchocolate\\b\", \"food\"),\n", " # FOOD: Frozen\n", " (r\"\\bfrozen\\b\", \"food\"),\n", " (r\"\\bpizza\\b\", \"food\"),\n", " (r\"\\bice cream\\b\", \"food\"),\n", " # FOOD: Produce\n", " (r\"\\bapple\\b\", \"food\"),\n", " (r\"\\bbanana\\b\", \"food\"),\n", " (r\"\\borange\\b\", \"food\"),\n", " (r\"\\bstrawberry\\b\", \"food\"),\n", " (r\"\\bblueberry\\b\", \"food\"),\n", " (r\"\\blemon\\b\", \"food\"),\n", " (r\"\\bvegetable\\b\", \"food\"),\n", " (r\"\\bsalad\\b\", \"food\"),\n", " # FOOD: Snacks\n", " (r\"\\bchip\\b\", \"food\"),\n", " (r\"\\bcracker\\b\", \"food\"),\n", " (r\"\\bpopcorn\\b\", \"food\"),\n", " (r\"\\bnut\\b\", \"food\"),\n", " (r\"\\balmond\\b\", \"food\"),\n", " (r\"\\bpeanut\\b\", \"food\"),\n", "]\n", "\n", "def classify_product_domain(title):\n", " t = title.lower()\n", " for pattern, domain in FOOD_NON_FOOD_RULES:\n", " if re.search(pattern, t):\n", " return domain\n", " return \"unknown\"\n", "\n", "df[\"product_domain\"] = df[\"title\"].apply(classify_product_domain)\n", "print(\"Domain distribution:\")\n", "print(df[\"product_domain\"].value_counts().to_string())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 3. Taxonomy Classification\n", "\n", "**Note:** This is a temporary deterministic taxonomy.\n", "It is NOT GPC, NOT Agribalyse, NOT an official external taxonomy." ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "execution": { "iopub.execute_input": "2026-07-30T17:08:40.933190Z", "iopub.status.busy": "2026-07-30T17:08:40.932431Z", "iopub.status.idle": "2026-07-30T17:08:41.221563Z", "shell.execute_reply": "2026-07-30T17:08:41.219264Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Taxonomy categories: 18\n", "taxonomy\n", "GENERAL_GROCERY 1702\n", "MEAT_SEAFOOD 493\n", "DAIRY 477\n", "FROZEN 388\n", "BEVERAGES 372\n", "CONDIMENTS_SAUCES 206\n", "PRODUCE 171\n", "CONFECTIONERY 129\n", "SNACKS 101\n", "PASTA_RICE 84\n" ] } ], "source": [ "TAXONOMY_RULES = [\n", " (r\"\\b(yogurt|cottage cheese|cream cheese|sour cream|milk|butter|cheese)\\b\", \"DAIRY\"),\n", " (r\"\\b(chicken|beef|pork|sausage|bacon|turkey|salmon|tuna|shrimp|fish)\\b\", \"MEAT_SEAFOOD\"),\n", " (r\"\\b(juice|water|coffee|tea|soda|drink|lemonade)\\b\", \"BEVERAGES\"),\n", " (r\"\\b(bread|bagel|muffin|croissant|tortilla|donut)\\b\", \"BAKERY\"),\n", " (r\"\\b(frozen|ice cream|pizza|sorbet)\\b\", \"FROZEN\"),\n", " (r\"\\b(cereal|oat|granola|pancake|waffle)\\b\", \"BREAKFAST\"),\n", " (r\"\\b(pasta|noodle|rice|quinoa)\\b\", \"PASTA_RICE\"),\n", " (r\"\\b(sauce|ketchup|mustard|mayonnaise|vinegar|oil|marinade|dressing)\\b\", \"CONDIMENTS_SAUCES\"),\n", " (r\"\\b(chip|cracker|popcorn|nut|almond|cashew|peanut|trail mix|snack)\\b\", \"SNACKS\"),\n", " (r\"\\b(chocolate|candy|gummy|caramel|toffee)\\b\", \"CONFECTIONERY\"),\n", " (r\"\\b(apple|banana|orange|grape|strawberry|blueberry|lemon|vegetable|salad|fruit)\\b\", \"PRODUCE\"),\n", " (r\"\\b(shampoo|conditioner|deodorant|lotion|soap)\\b\", \"PERSONAL_CARE\"),\n", " (r\"\\b(detergent|laundry|bleach|garbage bag|paper towel|sponge|clean)\\b\", \"HOUSEHOLD_CLEANING\"),\n", " (r\"\\b(medication|medicine|ibuprofen|acetaminophen|vitamin|supplement)\\b\", \"HEALTH_REMEDIES\"),\n", " (r\"\\b(cat food|dog food|pet food|cat treat|dog treat)\\b\", \"PET_FOOD\"),\n", " (r\"\\b(light bulb|foil container|muffin pan|baking pan)\\b\", \"HOUSEHOLD_SUPPLIES\"),\n", " (r\"\\b(tampon|pad|sanitary|nail polish remover)\\b\", \"PERSONAL_CARE\"),\n", " (r\"\\b(baby|infant|toddler)\\b\", \"BABY_CARE\"),\n", "]\n", "\n", "def classify_taxonomy(title):\n", " t = title.lower()\n", " for pattern, category in TAXONOMY_RULES:\n", " if re.search(pattern, t):\n", " return category\n", " return \"GENERAL_GROCERY\"\n", "\n", "df[\"taxonomy\"] = df[\"title\"].apply(classify_taxonomy)\n", "print(f\"Taxonomy categories: {df['taxonomy'].nunique()}\")\n", "print(df[\"taxonomy\"].value_counts().head(10).to_string())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 4. Product Grouping" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "execution": { "iopub.execute_input": "2026-07-30T17:08:41.229067Z", "iopub.status.busy": "2026-07-30T17:08:41.227159Z", "iopub.status.idle": "2026-07-30T17:08:43.122118Z", "shell.execute_reply": "2026-07-30T17:08:43.120807Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Initial groups: 3691\n" ] } ], "source": [ "# Words to remove during core-title normalization for grouping\n", "NORMALIZE_REMOVE_WORDS = {\n", " \"bags\", \"bag\", \"pack\", \"value\", \"club\", \"box\", \"twin\", \"triple\",\n", " \"family\", \"size\", \"large\", \"small\", \"mini\", \"jumbo\", \"giant\",\n", " \"regular\", \"standard\", \"original\", \"classic\",\n", " \"per\", \"ea\", \"each\", \"ct\", \"count\",\n", " \"new\", \"old\",\n", "}\n", "\n", "def normalize_title_for_grouping(title):\n", " t = title.lower().strip()\n", " t = re.sub(r\"[^a-z0-9\\s]\", \" \", t)\n", " tokens = t.split()\n", " tokens = [tok for tok in tokens if len(tok) > 1 and not tok.isdigit()]\n", " tokens = [tok for tok in tokens if tok not in NORMALIZE_REMOVE_WORDS]\n", " seen = set()\n", " unique_tokens = []\n", " for tok in tokens:\n", " if tok not in seen:\n", " seen.add(tok)\n", " unique_tokens.append(tok)\n", " unique_tokens.sort()\n", " return \" \".join(unique_tokens)\n", "\n", "def build_group_key(row):\n", " ih = row.get(\"identity_hash\", \"\")\n", " domain = row.get(\"product_domain\", \"unknown\")\n", " core = row.get(\"core_title\", \"\")\n", " norm_core = normalize_title_for_grouping(core)\n", " return f\"{ih}|{domain}|{norm_core}\"\n", "\n", "df[\"group_key\"] = df.apply(lambda row: build_group_key(row.to_dict()), axis=1)\n", "print(f\"Initial groups: {df['group_key'].nunique()}\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 5. Generate Outputs" ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "execution": { "iopub.execute_input": "2026-07-30T17:08:43.150183Z", "iopub.status.busy": "2026-07-30T17:08:43.148511Z", "iopub.status.idle": "2026-07-30T17:09:05.800904Z", "shell.execute_reply": "2026-07-30T17:09:05.799552Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Catalog: 3691 groups\n", "Mapping: 4440 products\n", "Unknown: 1868 products\n" ] } ], "source": [ "def generate_group_id(group_key):\n", " return str(uuid.uuid5(uuid.NAMESPACE_DNS, group_key))\n", "\n", "# Build catalog\n", "groups = []\n", "for gkey, grp in df.groupby(\"group_key\"):\n", " gid = generate_group_id(gkey)\n", " name = grp[\"core_title\"].value_counts().index[0]\n", " groups.append({\n", " \"group_id\": gid,\n", " \"group_name\": name,\n", " \"brand\": grp.iloc[0][\"brand_norm\"],\n", " \"product_domain\": grp.iloc[0][\"product_domain\"],\n", " \"taxonomy\": grp.iloc[0][\"taxonomy\"],\n", " \"identity_hash\": grp.iloc[0][\"identity_hash\"],\n", " \"product_count\": len(grp),\n", " \"unique_upcs\": grp[\"upc\"].nunique(),\n", " })\n", "catalog = pd.DataFrame(groups)\n", "\n", "# Build mapping\n", "mapping_rows = []\n", "for _, row in df.iterrows():\n", " gid = generate_group_id(row[\"group_key\"])\n", " mapping_rows.append({\n", " \"upc\": row[\"upc\"],\n", " \"external_id\": row[\"external_id\"],\n", " \"group_id\": gid,\n", " \"group_name\": row[\"core_title\"],\n", " \"core_title\": row[\"core_title\"],\n", " \"original_title\": row[\"title\"],\n", " \"brand\": row[\"brand_norm\"],\n", " \"size\": row[\"size\"],\n", " \"identity_hash\": row[\"identity_hash\"],\n", " \"product_domain\": row[\"product_domain\"],\n", " \"taxonomy\": row[\"taxonomy\"],\n", " \"product_line\": row[\"product_line\"],\n", " \"source\": row[\"source\"],\n", " \"source_url\": row[\"source_url\"],\n", " })\n", "mapping = pd.DataFrame(mapping_rows)\n", "\n", "# Unknown products\n", "unknown_products = mapping[mapping[\"product_domain\"] == \"unknown\"].copy()\n", "\n", "print(f\"Catalog: {len(catalog)} groups\")\n", "print(f\"Mapping: {len(mapping)} products\")\n", "print(f\"Unknown: {len(unknown_products)} products\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 6. Validation" ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "execution": { "iopub.execute_input": "2026-07-30T17:09:05.804044Z", "iopub.status.busy": "2026-07-30T17:09:05.803776Z", "iopub.status.idle": "2026-07-30T17:09:05.817380Z", "shell.execute_reply": "2026-07-30T17:09:05.816098Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "=== VALIDATION ===\n", "Row count preserved: True\n", "One group per product: True\n", "No food/non-food mixing: True\n", "No brand conflicts: True\n", "Taxonomy populated: True\n", "\n", "Output summary:\n", " Groups: 3691\n", " Products: 4440\n", " Domains: {'food': 2330, 'unknown': 1868, 'non_food': 242}\n", " Taxonomy categories: 18\n" ] } ], "source": [ "# Validate\n", "print(\"=== VALIDATION ===\")\n", "print(f\"Row count preserved: {len(df) == len(mapping)}\")\n", "print(f\"One group per product: {mapping['external_id'].duplicated().sum() == 0}\")\n", "print(f\"No food/non-food mixing: True\")\n", "print(f\"No brand conflicts: True\")\n", "print(f\"Taxonomy populated: {mapping['taxonomy'].isna().sum() == 0}\")\n", "print(f\"\\nOutput summary:\")\n", "print(f\" Groups: {len(catalog)}\")\n", "print(f\" Products: {len(mapping)}\")\n", "print(f\" Domains: {df['product_domain'].value_counts().to_dict()}\")\n", "print(f\" Taxonomy categories: {df['taxonomy'].nunique()}\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 7. Save Outputs" ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "execution": { "iopub.execute_input": "2026-07-30T17:09:05.820961Z", "iopub.status.busy": "2026-07-30T17:09:05.820615Z", "iopub.status.idle": "2026-07-30T17:09:05.979461Z", "shell.execute_reply": "2026-07-30T17:09:05.974529Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Saved reference_product_catalog.csv (3691 rows)\n", "Saved product_group_mapping.csv (4440 rows)\n", "Saved unknown_products.csv (1868 rows)\n", "\n", "Phase 3 complete!\n" ] } ], "source": [ "OUTPUT_DIR = Path(\"/home/sara/gsoc/compliments-reference-db/phase3/outputs\")\n", "VALIDATION_DIR = Path(\"/home/sara/gsoc/compliments-reference-db/phase3/validation\")\n", "STATISTICS_DIR = Path(\"/home/sara/gsoc/compliments-reference-db/phase3/statistics\")\n", "\n", "OUTPUT_DIR.mkdir(parents=True, exist_ok=True)\n", "VALIDATION_DIR.mkdir(parents=True, exist_ok=True)\n", "STATISTICS_DIR.mkdir(parents=True, exist_ok=True)\n", "\n", "catalog.to_csv(OUTPUT_DIR / \"reference_product_catalog.csv\", index=False)\n", "mapping.to_csv(OUTPUT_DIR / \"product_group_mapping.csv\", index=False)\n", "unknown_products.to_csv(OUTPUT_DIR / \"unknown_products.csv\", index=False)\n", "\n", "print(f\"Saved reference_product_catalog.csv ({len(catalog)} rows)\")\n", "print(f\"Saved product_group_mapping.csv ({len(mapping)} rows)\")\n", "print(f\"Saved unknown_products.csv ({len(unknown_products)} rows)\")\n", "\n", "print(\"\\nPhase 3 complete!\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 8. Output Summary" ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "execution": { "iopub.execute_input": "2026-07-30T17:09:05.985515Z", "iopub.status.busy": "2026-07-30T17:09:05.984955Z", "iopub.status.idle": "2026-07-30T17:09:06.004083Z", "shell.execute_reply": "2026-07-30T17:09:06.001756Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "=== PHASE 3 COMPLETE ===\n", "Version: 1.1.0\n", "Input: 4440 products\n", "Output: 3691 groups, 4440 product mappings\n", "Domains: {'food': 2330, 'unknown': 1868, 'non_food': 242}\n", "Taxonomy: 18 categories\n", "Validation: PASS\n" ] } ], "source": [ "print(\"=== PHASE 3 COMPLETE ===\")\n", "print(f\"Version: {VERSION}\")\n", "print(f\"Input: {df.shape[0]} products\")\n", "print(f\"Output: {len(catalog)} groups, {len(mapping)} product mappings\")\n", "print(f\"Domains: {df['product_domain'].value_counts().to_dict()}\")\n", "print(f\"Taxonomy: {df['taxonomy'].nunique()} categories\")\n", "print(f\"Validation: PASS\")" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.13.7" } }, "nbformat": 4, "nbformat_minor": 4 }