Repository Public
1#!/usr/bin/env python32# UQO Éval — Quantity Survey : construit data/rsmeans.db à partir des extractions brutes RSMeans Online (raw/<LOC>_<BOOK>_<LABOR>_<DIV>_p<N>.json).3#4# Méthode (voir docs/construction/RSMEANS.md) :5# - lignes de coûts unitaires (MasterFormat 2018, version 2026 T4) de 4 livres : 12 Commercial New Construction (STD/OPN), 14 Facilities and6# Commercial Renovation (STD/OPN), 3 Residential New Construction (RES), 4 Facilities Maintenance and Repair (FMR) ;7# - 5 localisations EXTRAITES EN ENTIER (National Average, Montréal, Québec, Gatineau, Hull) → table rsm_prices ;8# - 13 autres villes du Québec échantillonnées (≤ 300 lignes par division) → facteurs de localisation par division et nature de coût9# (matériau / main-d'œuvre / équipement) = médiane des ratios ville / national (lignes ≥ 1,00 $ pour éviter le bruit d'arrondi) → table rsm_factors ;10# - VALIDATION : les mêmes facteurs sont recalculés pour les 4 villes extraites en entier et confrontés ligne à ligne aux prix réels11# (erreur absolue médiane / p90) → rsm_meta.validation_json ; la plateforme n'invente aucun prix : un prix « par facteur » est étiqueté comme tel.12import glob, json, math, os, re, sqlite3, statistics, sys, time13from collections import defaultdict1415RAW = sys.argv[1] if len(sys.argv) > 1 else "/tmp/rsmeans/raw"16OUT = sys.argv[2] if len(sys.argv) > 2 else "data/rsmeans.db"17SPEC = json.load(open(sys.argv[3] if len(sys.argv) > 3 else os.path.join(os.path.dirname(RAW), "spec.json")))18COLS = ["Favorite", "UnitLineNumber", "LineNumber", "bGreenLine", "LineSource", "LineSourceValue", "Description", "QTipDescription", "UnitOfMeasureCode", "CrewCode", "DailyOutput", "Frequency", "LaborHours", "MaterialCost", "MaterialCostOP", "InstallCostOP", "LaborCost", "EquipmentCost", "Total", "AssemblyTotal", "TotalInHouse", "TotalOP", "ReferenceLinkPath", "GraphicLinkPath", "CostLineTypeCode", "FormatCode", "PercentLine", "MF04", "MF95", "EquipmentCostOP", "LaborCostOP", "InstallCost", "ShortDescription", "HourlyOperCost", "RentPerDay", "RentPerWeek", "RentPerMonth", "CrewEquipCost"]19NUM = ["DailyOutput", "LaborHours", "MaterialCost", "MaterialCostOP", "InstallCostOP", "LaborCost", "EquipmentCost", "Total", "TotalInHouse", "TotalOP", "EquipmentCostOP", "LaborCostOP", "InstallCost", "HourlyOperCost", "RentPerDay", "RentPerWeek", "RentPerMonth", "CrewEquipCost"]20BOOKS = {"12": "Commercial New Construction", "14": "Facilities and Commercial Renovation", "3": "Residential New Construction", "4": "Facilities Maintenance and Repair"}21LOCS = {l["key"]: l for l in SPEC["locations"]}22# coordonnées (approx.) pour la carte / le choix automatique de la ville la plus proche23COORDS = {"NA": (None, None), "MONTREAL": (45.5019, -73.5674), "QUEBEC": (46.8139, -71.2080), "GATINEAU": (45.4765, -75.7013), "HULL": (45.4287, -75.7175), "CAP-DE-LA-MADELEINE": (46.3833, -72.5167), "CHARLESBOURG": (46.8617, -71.2628), "CHICOUTIMI": (48.4280, -71.0580), "GRANBY": (45.4000, -72.7333), "JOLIETTE": (46.0167, -73.4333), "LAVAL": (45.6066, -73.7124), "RIMOUSKI": (48.4489, -68.5236), "ROUYN-NORANDA": (48.2366, -79.0231), "SAINT-HYACINTHE": (45.6307, -72.9570), "SHERBROOKE": (45.4042, -71.8929), "SOREL": (46.0433, -73.1134), "ST-JEROME": (45.7806, -74.0036), "TROIS-RIVIERES": (46.3432, -72.5477)}2425def num(s):26 if s is None: return None27 s = str(s).replace(",", "").replace("%", "").strip()28 if s == "": return None29 try: return float(s)30 except ValueError: return None3132def strip_html(s):33 return re.sub(r"\s+", " ", re.sub(r"<[^>]+>", "", s or "")).strip()3435def refs(s):36 return sorted(set(re.findall(r"REFPDF\\\\(R[0-9A-Z]+)\.pdf", s or "")))3738t0 = time.time()39files = sorted(glob.glob(os.path.join(RAW, "*.json")))40print(f"{len(files)} fichiers bruts")41lines = {} # line → dict42books_of = defaultdict(set)43prices = {} # (line, loc_id, labor) → dict (localisations complètes)44samples = defaultdict(list) # (loc_key, labor, division) → [(line, mat, lab, eq)]45nat = {} # (line, labor) → (mat, lab, eq)46counts = defaultdict(int)47for f in files:48 name = os.path.basename(f)[:-5]49 m = re.match(r"^(.+)_(\d+)_([A-Z]+)_(\d\d)_p(\d+)$", name)50 if not m: continue51 lk, book, labor, div, page = m.groups(); loc = LOCS[lk]52 try: j = json.load(open(f))53 except Exception as e: print("JSON invalide", name, e); continue54 for r in j["rows"]:55 c = dict(zip(COLS, r["cell"]))56 ln = c["UnitLineNumber"] or c["MF04"]57 if not ln: continue58 books_of[ln].add(book)59 if ln not in lines or (not lines[ln]["unit"] and c["UnitOfMeasureCode"]):60 lines[ln] = {"line": ln, "mf95": c["MF95"] or None, "short": strip_html(c["ShortDescription"]) or strip_html(c["Description"]), "full": strip_html(c["QTipDescription"]), "unit": c["UnitOfMeasureCode"] or None, "crew": c["CrewCode"] or None, "daily_output": num(c["DailyOutput"]), "labor_hours": num(c["LaborHours"]), "format": c["FormatCode"] or None, "percent": 1 if str(c["PercentLine"]).lower() == "true" else 0, "green": 1 if "leaf" in (c["bGreenLine"] or "") else 0, "division": ln[:2], "indent": (re.search(r"ind(\d)", c["Description"] or "") or [None, "0"])[1], "refs": ",".join(refs(c["ReferenceLinkPath"])) or None}61 vals = {k: num(c[k]) for k in NUM}62 if loc["full"]:63 key = (ln, loc["id"], labor)64 if key in prices and prices[key]["book"] != book:65 p = prices[key]66 if any((p[k] or 0) != (vals[k] or 0) for k in ("MaterialCost", "LaborCost", "EquipmentCost", "TotalOP")): counts["conflit_prix_entre_livres"] += 167 continue68 prices[key] = {"book": book, **vals}69 if lk == "NA": nat[(ln, labor)] = (vals["MaterialCost"], vals["LaborCost"], vals["EquipmentCost"])70 samples[(lk, labor, div)].append((ln, vals["MaterialCost"], vals["LaborCost"], vals["EquipmentCost"]))71 counts["lignes_lues"] += 172print(f"{len(lines)} lignes distinctes, {len(prices)} prix localisés complets, conflits entre livres : {counts['conflit_prix_entre_livres']}")7374# ---- facteurs de localisation par (ville, type de MO, division à 2 chiffres ET section à 4 chiffres) : médiane des ratios ville/national (valeurs ≥ 1,00 $)75def factors_for(lk, labor, div):76 rows = [r for r in samples.get((lk, labor, div[:2]), []) if r[0].startswith(div)]77 out = {}78 for idx, key in ((1, "material"), (2, "labor"), (3, "equipment")):79 ratios = []80 for ln, *v in rows:81 n = nat.get((ln, labor))82 if not n: continue83 a, b = v[idx - 1], n[idx - 1]84 if a is not None and b is not None and a >= 1.0 and b >= 1.0: ratios.append(a / b)85 if len(ratios) >= 3:86 med = statistics.median(ratios); sd = statistics.pstdev(ratios) if len(ratios) > 1 else 0.087 out[key] = (round(med, 4), len(ratios), round(sd, 4))88 else: out[key] = (None, len(ratios), None)89 return out9091factors = [] # (loc_id, labor, division|section, mat_f, lab_f, eq_f, n…, sd…, method)92for lk, loc in LOCS.items():93 if lk == "NA": continue94 for labor in sorted({k[1] for k in samples if k[0] == lk}):95 for div in sorted({k[2] for k in samples if k[0] == lk and k[1] == labor}):96 keys = [div] + sorted({r[0][:4] for r in samples[(lk, labor, div)]})97 for key in keys:98 fct = factors_for(lk, labor, key)99 if key != div and fct["material"][1] < 5 and fct["labor"][1] < 5: continue # section trop peu échantillonnée100 factors.append((loc["id"], labor, key, fct["material"][0], fct["labor"][0], fct["equipment"][0], fct["material"][1], fct["labor"][1], fct["equipment"][1], fct["material"][2], fct["labor"][2], fct["equipment"][2], "full-validation" if loc["full"] else "sample"))101print(f"{len(factors)} facteurs (ville × MO × division/section)")102103# ---- validation : pour les villes complètes, prix prédit = national × facteur vs prix réel (toutes les lignes, pas seulement l'échantillon)104validation = {}105fmap = {(f[0], f[1], f[2]): f for f in factors}106for lk, loc in LOCS.items():107 if not loc["full"] or lk == "NA": continue108 errs = defaultdict(list)109 for (ln, lid, labor), p in prices.items():110 if lid != loc["id"]: continue111 n = nat.get((ln, labor))112 if not n: continue113 for level in ("section", "division"):114 f = fmap.get((lid, labor, ln[:4] if level == "section" else ln[:2]))115 if not f: continue116 for idx, key, fi in ((0, "material", 3), (1, "labor", 4), (2, "equipment", 5)):117 actual, base, fac = p[["MaterialCost", "LaborCost", "EquipmentCost"][idx]], n[idx], f[fi]118 if actual and base and fac and actual >= 1.0: errs[(level, key)].append(abs(base * fac - actual) / actual)119 validation[lk] = {f"{level}.{k}": {"n": len(v), "mdape_pct": round(100 * statistics.median(v), 2), "p90_pct": round(100 * sorted(v)[int(0.9 * len(v))], 2), "share_over_5pct": round(100 * sum(1 for x in v if x > 0.05) / len(v), 1)} for (level, k), v in errs.items() if v}120print("validation", json.dumps(validation))121122# ---- écriture SQLite123if os.path.exists(OUT): os.remove(OUT)124db = sqlite3.connect(OUT); db.executescript("""125PRAGMA journal_mode=WAL; PRAGMA synchronous=OFF;126CREATE TABLE rsm_meta(key TEXT PRIMARY KEY, value TEXT NOT NULL);127CREATE TABLE rsm_books(id TEXT PRIMARY KEY, name TEXT NOT NULL, labor_types TEXT NOT NULL);128CREATE TABLE rsm_locations(id INTEGER PRIMARY KEY, key TEXT NOT NULL UNIQUE, name TEXT NOT NULL, cci_code TEXT, country TEXT NOT NULL, state TEXT NOT NULL, full INTEGER NOT NULL, lat REAL, lng REAL);129CREATE TABLE rsm_lines(line TEXT PRIMARY KEY, mf95 TEXT, division TEXT NOT NULL, short_desc TEXT NOT NULL, full_desc TEXT NOT NULL, unit TEXT, crew TEXT, daily_output REAL, labor_hours REAL, format TEXT, percent_line INTEGER NOT NULL DEFAULT 0, green INTEGER NOT NULL DEFAULT 0, indent INTEGER NOT NULL DEFAULT 0, books TEXT NOT NULL, refs TEXT);130CREATE INDEX idx_lines_div ON rsm_lines(division);131CREATE TABLE rsm_prices(line TEXT NOT NULL, location_id INTEGER NOT NULL, labor TEXT NOT NULL, book TEXT NOT NULL, material REAL, material_op REAL, labor_cost REAL, labor_op REAL, equipment REAL, equipment_op REAL, total REAL, total_op REAL, total_inhouse REAL, install REAL, install_op REAL, hourly_oper REAL, rent_day REAL, rent_week REAL, rent_month REAL, crew_equip REAL, PRIMARY KEY(line, location_id, labor));132CREATE INDEX idx_prices_loc ON rsm_prices(location_id, labor);133CREATE TABLE rsm_factors(location_id INTEGER NOT NULL, labor TEXT NOT NULL, division TEXT NOT NULL, material_f REAL, labor_f REAL, equipment_f REAL, n_material INTEGER, n_labor INTEGER, n_equipment INTEGER, sd_material REAL, sd_labor REAL, sd_equipment REAL, method TEXT NOT NULL, PRIMARY KEY(location_id, labor, division));134CREATE VIRTUAL TABLE rsm_fts USING fts5(line UNINDEXED, text, tokenize='unicode61 remove_diacritics 2');135""")136db.executemany("INSERT INTO rsm_books VALUES (?,?,?)", [("12", BOOKS["12"], "STD,OPN"), ("14", BOOKS["14"], "STD,OPN"), ("3", BOOKS["3"], "RES"), ("4", BOOKS["4"], "FMR")])137db.executemany("INSERT INTO rsm_locations VALUES (?,?,?,?,?,?,?,?,?)", [(l["id"], l["key"], l["name"], (re.search(r"\((C\d+)\)", l["name"]) or [None, None])[1], l["cc"], l["sc"], 1 if l["full"] else 0, COORDS.get(l["key"], (None, None))[0], COORDS.get(l["key"], (None, None))[1]) for l in SPEC["locations"]])138db.executemany("INSERT INTO rsm_lines VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", [(l["line"], l["mf95"], l["division"], l["short"], l["full"], l["unit"], l["crew"], l["daily_output"], l["labor_hours"], l["format"], l["percent"], l["green"], int(l["indent"] or 0), ",".join(sorted(books_of[l["line"]], key=int)), l["refs"]) for l in lines.values()])139db.executemany("INSERT INTO rsm_prices VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", [(ln, lid, labor, p["book"], p["MaterialCost"], p["MaterialCostOP"], p["LaborCost"], p["LaborCostOP"], p["EquipmentCost"], p["EquipmentCostOP"], p["Total"], p["TotalOP"], p["TotalInHouse"], p["InstallCost"], p["InstallCostOP"], p["HourlyOperCost"], p["RentPerDay"], p["RentPerWeek"], p["RentPerMonth"], p["CrewEquipCost"]) for (ln, lid, labor), p in prices.items()])140db.executemany("INSERT INTO rsm_factors VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)", factors)141db.executemany("INSERT INTO rsm_fts VALUES (?,?)", [(l["line"], f"{l['line']} {l['short']} {l['full']}") for l in lines.values()])142meta = {"source": "RSMeans Online (Gordian) — rsmeansonline.com, compte UQO", "release": SPEC["release"]["name"], "release_id": SPEC["release"]["id"], "format": "MasterFormat 2018", "measurement": "US Standard", "extracted_at": time.strftime("%Y-%m-%d"), "books": BOOKS, "full_locations": [l["key"] for l in SPEC["locations"] if l["full"]], "sample_locations": [l["key"] for l in SPEC["locations"] if not l["full"]], "lines": len(lines), "prices": len(prices), "factors": len(factors), "raw_files": len(files), "conflicts_between_books": counts["conflit_prix_entre_livres"], "validation": validation, "licence": "Données sous licence RSMeans/Gordian (abonnement UQO) : usage pédagogique interne ; pas de redistribution brute ; voir docs/construction/RSMEANS.md"}143db.executemany("INSERT INTO rsm_meta VALUES (?,?)", [(k, json.dumps(v, ensure_ascii=False)) for k, v in meta.items()])144db.commit(); db.execute("PRAGMA journal_mode=DELETE"); db.execute("VACUUM"); db.close()145print(f"OK → {OUT} ({os.path.getsize(OUT) / 1e6:.1f} Mo) en {time.time() - t0:.0f} s")146