1"""Assemble reports/phase1_data_audit.md from processed tables (numbers are never typed by hand)."""2import pandas as pd, numpy as np, json3from src.common import ROOT, path, SETTINGS, get_logger4log = get_logger("audit")5P = path("processed")67def md(df, floatfmt="{:,.3f}"):8 df = df.copy()9 for c in df.columns:10 if df[c].dtype.kind == "f": df[c] = df[c].map(lambda x: "" if pd.isna(x) else floatfmt.format(x))11 elif df[c].dtype.kind in "iu": df[c] = df[c].map(lambda x: f"{x:,}")12 return "| " + " | ".join(df.columns) + " |\n|" + "---|" * len(df.columns) + "\n" + "\n".join("| " + " | ".join(map(str, r)) + " |" for r in df.values)1314def main():15 prod = pd.read_parquet(P / "products.parquet"); ret = pd.read_parquet(P / "retailers.parquet")16 feed = pd.read_csv(P / "coverage_by_feed.csv"); cat = pd.read_csv(P / "coverage_by_category.csv"); wk = pd.read_csv(P / "coverage_by_week.csv")17 att = pd.read_csv(P / "attrition_listings.csv"); snaps = pd.read_parquet(P / "snapshots.parquet")18 daily = pd.read_parquet(P / "offers_daily.parquet", columns=["uid", "source", "n_distinct", "n_changes", "rel_range"])19 pairs = pd.read_parquet(P / "product_matches.parquet"); cal = pd.read_csv(P / "match_threshold_calibration.csv")20 val = pd.read_csv(P / "match_validation_summary.csv") if (P / "match_validation_summary.csv").exists() else None21 act = prod[prod.active == 1]22 comp = ret[ret.registry_status == "actif"].groupby("company").agg(banners=("banner", "nunique"), feeds=("feed_id", "size")).sort_values("feeds", ascending=False).reset_index()23 mix = daily.groupby("source").agg(product_days=("uid", "size"), multi_price_share=("n_distinct", lambda s: (s > 1).mean()),24 median_rel_range_when_multi=("rel_range", lambda s: s[s > 0].median())).reset_index()25 mix = mix[mix.product_days > 20000].sort_values("multi_price_share", ascending=False)26 inv = pd.DataFrame([27 ("product name", "confirmed", "all feeds; language FR/EN mixed (IGA/Voilà, Adonis, T&T in English)"),28 ("brand", "confirmed / partly unavailable", f"missing for {100*(act.brand.fillna('')=='').mean():.0f} % of active listings (Walmart, Adonis, T&T, Akhavan, BocoBoco, Costco feeds lack a brand field; brand often embedded in the name)"),29 ("UPC / GTIN", "confirmed (Metro, Super C only)", f"{int(act.gtin_valid.sum()):,} active listings with GS1-valid codes; other feeds expose internal SKUs. Open Food Facts barcodes attached by Food-Ka to {int(act.off_barcode.notna().sum()):,} active listings are derived by name lookup → 'derivable, unreliable', used only as a diagnostic"),30 ("package size / unit", "derivable", f"re-parsed from size_label or name: {100*act.size_qty.notna().mean():.1f} % of active listings (Food-Ka's own parser: {100*act.unit_price.notna().mean():.1f} %); missing mainly for Giant Tiger, Nuvo and circular feeds"),31 ("category", "confirmed (18 canonical)", "Food-Ka mapping from each banner's taxonomy; 'Autres' is a residual (19 % of active listings)"),32 ("retailer / banner / company", "derivable", "54 active feeds → banners → companies (retailers table); ownership basis recorded per feed"),33 ("store", "unavailable", "no store identifier in any feed; online storefronts answered anonymous sessions (store unknown); circulars for postal code H2X 1Y4"),34 ("current price", "confirmed", "event log: first observation + every change (NULL when price disappears)"),35 ("regular / reference price", "current state only; circulars per item-week", "not historised for storefront feeds; `original_price` in Flipp items"),36 ("unit price", "derivable", "recomputed from price and parsed size; per-kg labels handled; multi-buy 'n / x $' labels flagged"),37 ("promotion flag", "current state only", "historical promotion state must be inferred from price paths (storefront) or is implicit (circular items are promotions by construction)"),38 ("observation timestamp", "confirmed (source level)", f"{len(snaps):,} connector passes, median gap {snaps.sort_values('ts').groupby('source').ts.diff().median()/3600:.1f} h; product-level presence between events is inferred (see §3)"),39 ("circular validity", "confirmed", "valid_from / valid_to per Flipp item (weekly, Thu→Wed)"),40 ("availability / stock", "unreliable", f"in_stock NULL for {100*act.in_stock.isna().mean():.0f} % of active listings; current state only"),41 ("geography", "unavailable", "see store"),42 ("quantities / expenditure weights", "unavailable", "listings only; no transactions"),43 ], columns=["variable", "status", "notes"])44 txt = f"""# Phase 1 — Data audit and research-grade panel (WP37)4546Generated by `src/reports/phase1_audit.py` from the processed tables on {pd.Timestamp.utcnow():%Y-%m-%d %H:%M} UTC. Snapshot `{SETTINGS['snapshot']['id']}` (sha256 `{SETTINGS['snapshot']['sha256'][:16]}…`).4748## 1. Source-to-variable inventory4950{md(inv)}5152## 2. Retail hierarchy (feeds → banners → companies)5354Active feeds in the registry: {int((ret.registry_status=='actif').sum())}; with active listings in the snapshot: {act.source.nunique()}. Channel split: {ret[ret.registry_status=='actif'].channel.value_counts().to_dict()}.5556{md(comp)}5758Pharmacy feeds (4) and the SAQ are kept in the retailers table but excluded from grocery analyses; same-banner storefront/circular pairs are 'channel' links, not cross-banner comparisons.5960## 3. Panel reconstruction6162`price_log` is an event log (first observation + each change). A product's price is carried forward between events until `last_seen`; days are local (America/Toronto). Daily statistics per listing: time-weighted mode, min, max, mean, open/close, number of changes and distinct values. Weekly aggregation uses the Quebec circular week (Thursday→Wednesday). Tests (`tests/test_panel.py`) verify bounds, that covered hours equal the observation span, and that single-event products have exactly one distinct price.6364Limitations: (i) a listing that leaves and re-enters a feed is not logged as a gap unless its price changed — intra-span absence is unobserved; (ii) deactivation requires two consecutive misses (≈14 h), so `last_seen` dates removals to within one pass.6566Panel size: {len(daily):,} listing-days over {wk.week.nunique()} circular weeks ({wk.week.min()} → {wk.week.max()}).6768## 4. Store-mixture noise (measurement finding)6970Share of listing-days with more than one observed price value, and the median relative range (max−min)/mode on those days, for feeds with > 20 000 listing-days:7172{md(mix)}7374Interpretation: storefront feeds behind anti-bot protection (Club Entrepôt, Metro, Maxi, Super C, Provigo, Walmart) answer each anonymous session with the price of an unknown store; the resulting intra-day oscillation among 3–5 price levels is not repricing. Single-store online shops (Shopify/WooCommerce) and circulars do not show it. Consequences: weekly modal prices are the primary posted-price measure; within-banner daily range is reported as a store-unidentified lower bound on cross-store dispersion; sub-weekly volatility is not interpreted as price flexibility.7576## 5. Listing-level attrition7778{md(att)}7980## 6. Cross-banner matching8182Tiers: A = exact GS1-valid UPC (Metro ↔ Super C); B = brand + package (quantity and pack count) + identical cleaned token set + identical numeric tokens; C = brand + package + token-sort similarity ≥ 0.90 (not B); D = brand-less feed, package + identical full-name token set + branded side's brand tokens present. Multi-product circular offers ('X ou Y', 'assortis') and size-less listings are excluded from matching.8384Threshold calibration on UPC ground truth (all Metro×Super C candidate pairs within brand×package blocks; positives = same UPC): 8586{md(cal[cal.scorer.isin(['tokens_equal','sort_ratio'])].pivot_table(index=['scorer','thr'], values=['n','precision','recall']).reset_index())}8788Cross-banner pairs found: {pairs[pairs.link_type=='cross_banner'].tier.value_counts().to_dict()}; channel (storefront↔circular, same banner) links: {int((pairs.link_type=='channel').sum()):,}. A first version based on rapidfuzz `token_set_ratio` was rejected after validation (subset names scored 1.0); see `match_validation_sample_v0_tokenset_rejected.csv` and `_v1_round1.csv`.8990Recall against UPC truth (4 413 Metro↔Super C pairs): tier B recovers {pd.read_csv(P/'recall_gtin_truth.csv').recovered_tier.eq('B').mean():.3f}, B+C {pd.read_csv(P/'recall_gtin_truth.csv').recovered_tier.notna().mean():.3f} — an upper bound for cross-company recall because the two banners share a platform and naming conventions. Food-Ka's own `product_links` (2 033 pairs) are recovered at 42.5 %; the remainder are size-less or multi-offer circular links that WP37 excludes by design.9192### 6.1 Validation sample (240 pairs, 60 per tier, stratified random, seed {SETTINGS['seed']})9394{md(val) if val is not None else '*(pending: run src.matching.validation_report after annotation)*'}9596Labels: Claude (claude-opus-5-5) as second annotator; final label = operator review of every pair (research pipeline operator, not the PI); PI adjudication is pending and flagged in the paper. The UPC-based precision for tier B (0.913) is lower than the text-based precision because some listings with identical text carry different UPCs (packaging variants invisible in text) — both numbers are reported.9798**Decision for the primary specification: tiers A + B only; C and D enter sensitivity analyses.**99100## 7. Prospective collection plan (what cannot be done now)101102- **Store geography**: add a store-selection step to Loblaw/Metro/Walmart connectors (store id in the session) for a fixed panel of ~20 stores across Quebec regions, and pull Flipp circulars for ≥ 6 postal codes (Montréal, Québec, Gatineau, Sherbrooke, Saguenay, Trois-Rivières). Both are authorised, polite (one pass per store per day) and reversible changes to Food-Ka; they are **not** implemented in WP37 (no production change).103- **Reference prices**: log `regular_price` and `on_sale` changes in `price_log` (one extra column) so that promotion states become directly observed.104- **Minimum duration**: 26 weeks for promotion recurrence and reference-price adjustment (H3), 52 weeks for seasonal patterns and index validation against StatCan.105"""106 (ROOT / "reports" / "phase1_data_audit.md").write_text(txt)107 log.info("report written (%d chars)", len(txt))108109if __name__ == "__main__":110 main()111