TeX 55.1%
Python 44.2%
1# Author: Simon-Pierre Boucher — simon-pierre.boucher@uqo.ca2"""Transaction standardisation, mutually exclusive property classification and exclusion ledger.34Layers5------6standardized : one row per raw transaction, typed, joined to the roll unit and geography7cleaned : + classification + every flag; nothing deleted, `exclude_reason` carries the8 first (highest-priority) reason, `in_headline_sample` the verdict9analytic : headline sample only, with the modelling columns1011Classification precedence (first match wins):12 R1 built_after_sale roll building year > sale year (lot or unfinished structure at sale)13 R2 land roll type 'terrain' or CUBF land/road codes14 R3 non_residential CUBF outside the residential group {1000, 1100, 1211}15 R4 class from roll 1000 & n_units==1 & Intégré -> condo ; 1000 & n_units==1 & detached/semi/row -> house ;16 1000 & 2<=n_units<=5 -> plex ; n_units>=6 -> multi ; 1100 -> cottage ; 1211 -> mobile17 R5 provider fallback when the roll is missing or incomplete: provider label in {unifamilial, condo, plex}18 conflicts between a complete roll record and the provider label are kept as `type_conflict`19 (headline keeps the roll class; sensitivity drops conflicts).20"""21from __future__ import annotations2223import polars as pl2425HOUSE_LINKS = {"Détaché", "Jumelé", "En rangée 1 côté", "En rangée plus de 1 côté"}26PROVIDER_MAP = {"unifamilial": "house", "condo": "condo", "plex": "plex"}27LAND_CUBF_PREFIX = ("9", "4") # 9xxx vacant land / forest ; 4xxx transport-utility (roads)28RESIDENTIAL_CUBF = {1000, 1100, 1211}29MAX_PARCEL_SALES = 10 # parcels with more sales in 2021-2026 are shared / developer parcels30RATIO_LOG_BAND = 1.3862943611198906 # log(4): assessment-ratio deviation from the year×type×region median31PRICE_CAP = 10_000_000.0323334def standardize(tx: pl.DataFrame, units: pl.DataFrame, geo: pl.DataFrame) -> pl.DataFrame:35 """Join raw transactions to the roll unit and the geography layer; typed columns only."""36 u = units.select(37 "id_provinc",38 pl.col("cubf"), pl.col("cubf_libelle"), pl.col("type_prop").alias("roll_type"),39 pl.col("annee_construction").alias("roll_year_built"), pl.col("annee_estimee").alias("roll_year_estimated"),40 pl.col("aire_etages_m2").alias("roll_floor_area_m2"), pl.col("superficie_terrain_m2").alias("roll_land_area_m2"),41 pl.col("front_terrain_m").alias("roll_frontage_m"), pl.col("nb_etages").alias("roll_storeys"),42 pl.col("nb_logements").alias("roll_n_units"), pl.col("nb_locaux_non_resid").alias("roll_n_nonres"),43 pl.col("lien_physique").alias("roll_link"), pl.col("genre_construction").alias("roll_genre"),44 pl.col("valeur_terrain").alias("roll_land_value"), pl.col("valeur_batiment").alias("roll_building_value"),45 pl.col("valeur_role").alias("roll_total_value"), pl.col("valeur_anterieure").alias("roll_previous_value"),46 pl.col("dat_cond_marche").alias("roll_reference_date"), pl.col("n_adresses").alias("roll_n_addresses"),47 pl.lit(True).alias("roll_matched"),48 )49 g = geo.select(["id", "mun_code", "mun_name", "mun_design", "mrc_code", "mrc_name", "region_code", "region_name", "comet_code", "comet_name",50 "borough_code", "borough_name", "mamh_population", "x_m", "y_m", "grid500", "grid1k", "grid2k", "grid5k", "roll_mun_agrees"])51 df = (52 tx.rename({"id": "tx_id", "id_provinc": "parcel_id", "amount": "price", "property_type": "provider_type",53 "year_built": "provider_year_built", "floor_area": "provider_floor_area", "building_type": "provider_building_type",54 "land_area": "provider_land_area", "valeur_role": "provider_assessment"})55 .with_columns(pl.when(pl.col("parcel_id") == "").then(None).otherwise(pl.col("parcel_id")).alias("parcel_id"))56 .join(u, left_on="parcel_id", right_on="id_provinc", how="left")57 .join(g, left_on="tx_id", right_on="id", how="left")58 .with_columns(59 pl.col("date").dt.strftime("%Y-%m").alias("month"), pl.col("date").dt.year().alias("year"),60 pl.col("price").log().alias("log_price"),61 pl.col("roll_matched").fill_null(False),62 )63 )64 return df656667def classify(df: pl.DataFrame) -> pl.DataFrame:68 roll_complete = pl.col("cubf").is_not_null() & pl.col("roll_n_units").is_not_null()69 cubf_s = pl.col("cubf").cast(pl.Utf8)70 is_land = (pl.col("roll_type") == "terrain") | cubf_s.str.starts_with("9") | cubf_s.str.starts_with("4")71 roll_class = (72 pl.when(pl.col("cubf") == 1100).then(pl.lit("cottage"))73 .when(pl.col("cubf") == 1211).then(pl.lit("mobile"))74 .when((pl.col("cubf") == 1000) & (pl.col("roll_n_units") >= 6)).then(pl.lit("multi"))75 .when((pl.col("cubf") == 1000) & (pl.col("roll_n_units").is_between(2, 5))).then(pl.lit("plex"))76 .when((pl.col("cubf") == 1000) & (pl.col("roll_n_units") <= 1) & (pl.col("roll_link") == "Intégré")).then(pl.lit("condo"))77 .when((pl.col("cubf") == 1000) & (pl.col("roll_n_units") <= 1) & pl.col("roll_link").is_in(list(HOUSE_LINKS))).then(pl.lit("house"))78 .otherwise(None)79 )80 provider_class = pl.col("provider_type").replace_strict(PROVIDER_MAP, default=None)81 df = df.with_columns(82 roll_class.alias("roll_class"), provider_class.alias("provider_class"),83 (pl.col("roll_year_built") > pl.col("year")).alias("built_after_sale"),84 (pl.col("roll_year_built") == pl.col("year")).alias("new_construction"),85 is_land.alias("roll_is_land"),86 (pl.col("cubf").is_not_null() & ~pl.col("cubf").is_in(list(RESIDENTIAL_CUBF)) & ~is_land).alias("roll_non_residential"),87 )88 df = df.with_columns(89 pl.when(pl.col("roll_class").is_not_null()).then(pl.col("roll_class")).otherwise(pl.col("provider_class")).alias("property_class"),90 pl.when(pl.col("roll_class").is_not_null()).then(pl.lit("roll")).when(pl.col("provider_class").is_not_null()).then(pl.lit("provider")).otherwise(pl.lit("none")).alias("class_source"),91 (pl.col("roll_class").is_not_null() & pl.col("provider_class").is_not_null() & (pl.col("roll_class") != pl.col("provider_class"))).alias("type_conflict"),92 pl.when(pl.col("roll_class") == "house").then(93 pl.when(pl.col("roll_link") == "Détaché").then(pl.lit("detached")).when(pl.col("roll_link") == "Jumelé").then(pl.lit("semi")).otherwise(pl.lit("row"))94 ).when(pl.col("roll_class") == "condo").then(pl.lit("integrated")).otherwise(None).alias("subtype"),95 )96 return df979899def add_flags(df: pl.DataFrame) -> pl.DataFrame:100 # duplicates and multi-parcel deeds101 df = df.with_columns(102 pl.col("street").str.replace(r"^\s*\d+[A-Za-z]?(?:-\d+)?[,\s]+", "").str.to_lowercase().alias("_street_name"),103 (pl.col("lat") * 100).round(0).alias("_lat_c"), (pl.col("lng") * 100).round(0).alias("_lng_c"),104 )105 df = df.with_columns(106 (pl.len().over(["parcel_id", "date", "price"]) > 1).alias("_dup_exact_grp"),107 (pl.int_range(pl.len()).over(["parcel_id", "date", "price"]) > 0).alias("_dup_exact_later"),108 (pl.col("parcel_id").is_not_null() & (pl.col("price").n_unique().over(["parcel_id", "date"]) > 1)).alias("same_parcel_same_date_diff_price"),109 (pl.len().over(["date", "price", "city", "_street_name"]) > 1).alias("_mp_street"),110 (pl.len().over(["date", "price", "_lat_c", "_lng_c"]) > 1).alias("_mp_cell"),111 pl.len().over("parcel_id").alias("parcel_sales_2021_2026"),112 ).with_columns(113 (pl.col("parcel_id").is_not_null() & pl.col("_dup_exact_grp") & pl.col("_dup_exact_later")).alias("exact_duplicate"),114 (pl.col("_mp_street") | pl.col("_mp_cell")).alias("multi_parcel_candidate"),115 (pl.col("parcel_id").is_not_null() & (pl.col("parcel_sales_2021_2026") > MAX_PARCEL_SALES)).alias("shared_parcel"),116 pl.when(pl.col("parcel_id").is_null()).then(None).otherwise(pl.col("parcel_sales_2021_2026")).alias("parcel_sales_2021_2026"),117 )118 # assessment-ratio deviation from the year × class × region median. Basis = the provider's119 # `valeur_role` (assessment in force at the sale date, see results/cleaning/provider_vs_roll_fields.csv),120 # falling back to the current roll value.121 df = df.with_columns(pl.coalesce(pl.col("provider_assessment"), pl.col("roll_total_value")).alias("assessment_at_sale"))122 df = df.with_columns((pl.col("price") / pl.col("assessment_at_sale")).log().alias("_lr"))123 df = df.with_columns(pl.col("_lr").median().over(["year", "property_class", "region_code"]).alias("_lr_med"))124 df = df.with_columns(125 pl.when(pl.col("assessment_at_sale") > 0).then((pl.col("_lr") - pl.col("_lr_med")).abs() > RATIO_LOG_BAND).otherwise(False).alias("assessment_ratio_outlier"),126 (pl.col("price") > PRICE_CAP).alias("price_above_cap"),127 (pl.col("roll_floor_area_m2").is_null() | (pl.col("roll_floor_area_m2") <= 0)).alias("floor_area_missing"),128 pl.col("roll_year_built").is_null().alias("year_built_missing"),129 )130 reasons = [131 ("exact_duplicate", "exact_duplicate"),132 ("same_parcel_same_date_diff_price", "same_parcel_same_date_diff_price"),133 ("built_after_sale", "built_after_sale"),134 ("land", "roll_is_land"),135 ("non_residential", "roll_non_residential"),136 ("shared_parcel", "shared_parcel"),137 ("multi_parcel_candidate", "multi_parcel_candidate"),138 ("price_above_cap", "price_above_cap"),139 ("assessment_ratio_outlier", "assessment_ratio_outlier"),140 ]141 expr = pl.lit(None, dtype=pl.Utf8)142 for label, col in reversed(reasons):143 expr = pl.when(pl.col(col).fill_null(False)).then(pl.lit(label)).otherwise(expr)144 df = df.with_columns(expr.alias("exclude_reason"))145 df = df.with_columns(146 pl.when(pl.col("exclude_reason").is_not_null()).then(pl.col("exclude_reason"))147 .when(~pl.col("property_class").fill_null("none").is_in(["house", "condo", "plex"])).then(pl.lit("class_not_in_headline"))148 .when(~pl.col("roll_matched")).then(pl.lit("no_roll_characteristics"))149 .otherwise(None).alias("exclude_reason_headline")150 ).with_columns(pl.col("exclude_reason_headline").is_null().alias("in_headline_sample"))151 return df.drop([c for c in df.columns if c.startswith("_")])152153154def analytic(df: pl.DataFrame) -> pl.DataFrame:155 """Headline analytic layer with modelling columns (ages, logs, imputation inputs)."""156 a = df.filter(pl.col("in_headline_sample")).with_columns(157 (pl.col("year") - pl.col("roll_year_built")).clip(0, None).alias("age"),158 pl.col("roll_floor_area_m2").alias("floor_area_m2"),159 pl.col("roll_land_area_m2").alias("land_area_m2"),160 pl.col("roll_storeys").alias("storeys"), pl.col("roll_n_units").alias("n_units"),161 pl.col("roll_genre").fill_null("unknown").alias("genre"),162 )163 return a164