Nine crafts, one connection

R

# ============================================================
# StatCan public use microdata file
# Purpose: Fetch the data behind MapleStats MCP's statcan_pumf_tabulate
#          (exact: the PUMF ZIP)
# Inputs:  https://www150.statcan.gc.ca/n1/pub/98m0001x/2023001/cen21_ind_98m0001x_part_rec21.zip
# Outputs: data/raw/cen21_ind_98m0001x_part_rec21.zip; the unzipped files
# ============================================================

# 0. Setup ----

dir.create("data/raw", recursive = TRUE, showWarnings = FALSE)

# 1. Read inputs ----

download.file("https://www150.statcan.gc.ca/n1/pub/98m0001x/2023001/cen21_ind_98m0001x_part_rec21.zip", "data/raw/cen21_ind_98m0001x_part_rec21.zip", mode = "wb")
zip::unzip("data/raw/cen21_ind_98m0001x_part_rec21.zip", exdir = "data/raw/cen21_ind_98m0001x_part_rec21")
file.remove("data/raw/cen21_ind_98m0001x_part_rec21.zip")

data_files <- list.files("data/raw/cen21_ind_98m0001x_part_rec21", full.names = TRUE, recursive = TRUE)

Python

# ============================================================
# StatCan public use microdata file
# Purpose: Fetch the data behind MapleStats MCP's statcan_pumf_tabulate
#          (exact: the PUMF ZIP)
# Inputs:  https://www150.statcan.gc.ca/n1/pub/98m0001x/2023001/cen21_ind_98m0001x_part_rec21.zip
# Outputs: data/raw/cen21_ind_98m0001x_part_rec21.zip; the unzipped files
# ============================================================

# %% 0. Setup

import zipfile
from pathlib import Path

import httpx
import polars as pl

RAW_DIR = Path("data/raw")
RAW_DIR.mkdir(parents=True, exist_ok=True)
raw_path = RAW_DIR / "cen21_ind_98m0001x_part_rec21.zip"

# %% 1. Read inputs

with httpx.Client(
    http2=True,
    follow_redirects=True,
    timeout=300,
    headers={"User-Agent": "Mozilla/5.0 (compatible; research script)"},
) as client:
    response = client.get('https://www150.statcan.gc.ca/n1/pub/98m0001x/2023001/cen21_ind_98m0001x_part_rec21.zip')
response.raise_for_status()
raw_path.write_bytes(response.content)

with zipfile.ZipFile(raw_path) as archive:
    archive.extractall(RAW_DIR / raw_path.stem)

Stata

* ============================================================
* StatCan public use microdata file
* Purpose: Fetch the data behind MapleStats MCP's statcan_pumf_tabulate
*          (exact: the PUMF ZIP)
* Inputs:  https://www150.statcan.gc.ca/n1/pub/98m0001x/2023001/cen21_ind_98m0001x_part_rec21.zip
* Outputs: data/raw/cen21_ind_98m0001x_part_rec21.zip; the unzipped files
* ============================================================

version 18
clear all
set more off

* 0. Setup

capture mkdir "logs"
capture log close
log using "logs/statcan_pumf_tabulate.log", replace
capture mkdir "data"
capture mkdir "data/raw"

* 1. Read inputs

* Stata reads no JSON or HTML and truncates long column names, so its
* built-in Python (Stata 16+) fetches, filters and writes a CSV. Point
* Stata at a Python with these packages first: python set exec <path>.

python:
import json
import zipfile
from pathlib import Path
import httpx
import polars as pl
RAW_DIR = Path("data/raw")
RAW_DIR.mkdir(parents=True, exist_ok=True)
raw_path = RAW_DIR / "cen21_ind_98m0001x_part_rec21.zip"
with httpx.Client(http2=True, follow_redirects=True, timeout=300, headers={"User-Agent": "Mozilla/5.0 (compatible; research script)"}) as client: response = client.get('https://www150.statcan.gc.ca/n1/pub/98m0001x/2023001/cen21_ind_98m0001x_part_rec21.zip')
response.raise_for_status()
raw_path.write_bytes(response.content)
with zipfile.ZipFile(raw_path) as archive: archive.extractall(RAW_DIR / raw_path.stem)
end

log close

Julia

# ============================================================
# StatCan public use microdata file
# Purpose: Fetch the data behind MapleStats MCP's statcan_pumf_tabulate
#          (exact: the PUMF ZIP)
# Inputs:  https://www150.statcan.gc.ca/n1/pub/98m0001x/2023001/cen21_ind_98m0001x_part_rec21.zip
# Outputs: data/raw/cen21_ind_98m0001x_part_rec21.zip; the unzipped files
# ============================================================

# 0. Setup

using Downloads

mkpath("data/raw")

# 1. Read inputs

Downloads.download("https://www150.statcan.gc.ca/n1/pub/98m0001x/2023001/cen21_ind_98m0001x_part_rec21.zip", "data/raw/cen21_ind_98m0001x_part_rec21.zip")

R

# ============================================================
# IRCC monthly update: Canada - Permanent Residents by Province/Territory and CMA
# Purpose: Fetch the data behind MapleStats MCP's ircc_monthly_query
#          (exact: the source file, then the tool's filters)
# Inputs:  https://www.ircc.canada.ca/opendata-donneesouvertes/data/ODP-PR-PT_CMA.csv
# Outputs: data/raw/ODP-PR-PT_CMA.csv; the prepared table as `data`
# ============================================================

# 0. Setup ----

library(dplyr)
library(janitor)
library(purrr)
library(readr)
library(stringr)

dir.create("data/raw", recursive = TRUE, showWarnings = FALSE)

# 1. Read inputs ----

download.file("https://www.ircc.canada.ca/opendata-donneesouvertes/data/ODP-PR-PT_CMA.csv", "data/raw/ODP-PR-PT_CMA.csv", mode = "wb")

data <- read_delim("data/raw/ODP-PR-PT_CMA.csv", delim = "\t", na = c("", "NA", "--"),
  col_types = cols(.default = col_character()))

# 2. Check inputs ----

stopifnot("https://www.ircc.canada.ca/opendata-donneesouvertes/data/ODP-PR-PT_CMA.csv returned no rows" = nrow(data) > 0)

# 3. Prepare data ----

# Keep the rows the MapleStats tool kept.

data <- data |>
  filter(
    str_to_lower(str_trim(`EN_CENSUS_METROPOLITAN_AREA`)) == "edmonton",
    suppressWarnings(as.numeric(`EN_YEAR`)) >= 2015
  )

data <- data |>
  clean_names()

# Standard cleaning: trimmed text, empty strings as missing, and numbers
# stored as text converted to numbers. Codes stay text as the source
# wrote them: columns named like one (coordinate, vector_id, dguid, NAICS,
# postal code) and columns with a leading zero (01, 000000001).

code_columns <- "^(coordinate|vector|vector_?id|product_?id|pid|dguid|[a-z0-9_]*_dguid|postal_?code|[a-z0-9_]*_postal_code|fsa|[a-z0-9_]*naics[a-z0-9_]*|noc|noc_[a-z0-9_]*|[a-z0-9_]*_noc|sgc|sgc_[a-z0-9_]*|[a-z0-9_]*_sgc)$"
data <- data |>
  mutate(across(where(is.character), \(x) na_if(str_trim(x), "")))
keep_text <- names(data)[
  str_detect(names(data), code_columns) |
    map_lgl(
      data,
      \(column) is.character(column) && any(str_detect(column, "^-?0[0-9]"), na.rm = TRUE)
    )
]
data <- data |>
  mutate(
    across(any_of(keep_text), \(x) if (is.list(x)) x else as.character(x)),
    across(where(is.character) & !any_of(keep_text), parse_guess)
  )

Python

# ============================================================
# IRCC monthly update: Canada - Permanent Residents by Province/Territory and CMA
# Purpose: Fetch the data behind MapleStats MCP's ircc_monthly_query
#          (exact: the source file, then the tool's filters)
# Inputs:  https://www.ircc.canada.ca/opendata-donneesouvertes/data/ODP-PR-PT_CMA.csv
# Outputs: data/raw/ODP-PR-PT_CMA.csv; the prepared table as `data`
# ============================================================

# %% 0. Setup

import re
import unicodedata
from pathlib import Path

import httpx
import polars as pl

RAW_DIR = Path("data/raw")
RAW_DIR.mkdir(parents=True, exist_ok=True)
raw_path = RAW_DIR / "ODP-PR-PT_CMA.csv"

# %% 1. Read inputs

with httpx.Client(
    http2=True,
    follow_redirects=True,
    timeout=300,
    headers={"User-Agent": "Mozilla/5.0 (compatible; research script)"},
) as client:
    response = client.get('https://www.ircc.canada.ca/opendata-donneesouvertes/data/ODP-PR-PT_CMA.csv')
response.raise_for_status()
raw_path.write_bytes(response.content)

data = pl.read_csv(raw_path, separator='\t', infer_schema=False, null_values=['--'])

# %% 2. Check inputs

assert data.height > 0, "https://www.ircc.canada.ca/opendata-donneesouvertes/data/ODP-PR-PT_CMA.csv returned no rows"

# %% 3. Prepare data

# Keep the rows the MapleStats tool kept.

data = data.filter(
    pl.col('EN_CENSUS_METROPOLITAN_AREA').cast(pl.Utf8).str.strip_chars().str.to_lowercase() == 'edmonton',
    pl.col('EN_YEAR').cast(pl.Float64, strict=False) >= 2015,
)

# Standard cleaning: snake_case names as janitor's clean_names() gives them
# in R (PÉRIODE -> periode, referenceNumber -> reference_number, # -> number,
# % -> percent, apostrophes dropped, a leading digit prefixed with x),
# trimmed text, empty strings as missing. Names that clean alike are
# numbered as janitor numbers them (Indicator, indicator -> indicator,
# indicator_2).

clean_names = [
    re.sub(
        r"[^0-9a-z]+",
        "_",
        re.sub(
            r"([a-z0-9])([A-Z])",
            r"\1_\2",
            "".join(
                " " if unicodedata.category(ch)[0] in "PS" and not ch.isascii() else ch
                for ch in unicodedata.normalize("NFKD", column.replace("'", ""))
            )
            .replace("#", " number ")
            .replace("%", " percent ")
            .encode("ascii", "ignore")
            .decode(),
        ).lower(),
    ).strip("_")
    or "x"
    for column in data.columns
]
clean_names = [f"x{name}" if name[0].isdigit() else name for name in clean_names]
while len(set(clean_names)) < len(clean_names):
    name_counts = {}
    numbered = []
    for name in clean_names:
        name_counts[name] = name_counts.get(name, 0) + 1
        count = name_counts[name]
        numbered.append(name if count == 1 else f"{name}_{count}")
    clean_names = numbered
data = data.rename(dict(zip(data.columns, clean_names)))
data = data.with_columns(pl.col(pl.Utf8).str.strip_chars().replace("", None))

# Numbers stored as text become numbers. Codes stay text as the source
# wrote them: columns named like one (coordinate, vector_id, dguid, NAICS,
# postal code) and columns with a leading zero (01, 000000001).

code_columns = re.compile(r"^(coordinate|vector|vector_?id|product_?id|pid|dguid|[a-z0-9_]*_dguid|postal_?code|[a-z0-9_]*_postal_code|fsa|[a-z0-9_]*naics[a-z0-9_]*|noc|noc_[a-z0-9_]*|[a-z0-9_]*_noc|sgc|sgc_[a-z0-9_]*|[a-z0-9_]*_sgc)$")
for column in data.columns:
    dtype = data.schema[column]
    if code_columns.search(column) and not dtype.is_nested():
        data = data.with_columns(pl.col(column).cast(pl.Utf8))
        continue
    if dtype != pl.Utf8 or data[column].null_count() == data.height:
        continue
    numbers = data[column].cast(pl.Float64, strict=False)
    leading_zero = data[column].str.contains(r"^-?0[0-9]").any()
    if numbers.null_count() > data[column].null_count() or leading_zero:
        continue
    whole = data[column].cast(pl.Int64, strict=False)
    data = data.with_columns(whole if whole.null_count() == numbers.null_count() else numbers)

Stata

* ============================================================
* IRCC monthly update: Canada - Permanent Residents by Province/Territory and CMA
* Purpose: Fetch the data behind MapleStats MCP's ircc_monthly_query
*          (exact: the source file, then the tool's filters)
* Inputs:  https://www.ircc.canada.ca/opendata-donneesouvertes/data/ODP-PR-PT_CMA.csv
* Outputs: data/raw/ODP-PR-PT_CMA.csv; the prepared table as `data`
* ============================================================

version 18
clear all
set more off

* 0. Setup

capture mkdir "logs"
capture log close
log using "logs/ircc_monthly_query.log", replace
capture mkdir "data"
capture mkdir "data/raw"

* 1. Read inputs

* Stata reads no JSON or HTML and truncates long column names, so its
* built-in Python (Stata 16+) fetches, filters and writes a CSV. Point
* Stata at a Python with these packages first: python set exec <path>.

python:
import json
from pathlib import Path
import httpx
import polars as pl
RAW_DIR = Path("data/raw")
RAW_DIR.mkdir(parents=True, exist_ok=True)
raw_path = RAW_DIR / "ODP-PR-PT_CMA.csv"
with httpx.Client(http2=True, follow_redirects=True, timeout=300, headers={"User-Agent": "Mozilla/5.0 (compatible; research script)"}) as client: response = client.get('https://www.ircc.canada.ca/opendata-donneesouvertes/data/ODP-PR-PT_CMA.csv')
response.raise_for_status()
raw_path.write_bytes(response.content)
data = pl.read_csv(raw_path, separator='\t', infer_schema=False, null_values=['--'])
assert data.height > 0, "https://www.ircc.canada.ca/opendata-donneesouvertes/data/ODP-PR-PT_CMA.csv returned no rows"
data = data.filter(pl.col('EN_CENSUS_METROPOLITAN_AREA').cast(pl.Utf8).str.strip_chars().str.to_lowercase() == 'edmonton', pl.col('EN_YEAR').cast(pl.Float64, strict=False) >= 2015)
nested = [name for name, dtype in data.schema.items() if dtype.is_nested()]
data = data.with_columns(pl.col(name).map_elements(lambda value: json.dumps(value.to_list() if isinstance(value, pl.Series) else value, default=str), return_dtype=pl.Utf8) for name in nested)
data.write_csv(RAW_DIR / "ODP-PR-PT_CMA_prepared.csv")
end

import delimited "data/raw/ODP-PR-PT_CMA_prepared.csv", clear varnames(1) encoding("utf-8") stringcols(_all) bindquote(strict) maxquotedrows(unlimited)

* 2. Check inputs

* The Python block checked the download for rows before its filters and
* steps, which may leave none (the tool then returns an empty table).

* 3. Prepare data

* Standard cleaning: lower-case names, trimmed text, and numbers stored as
* text converted (destring leaves genuinely non-numeric text alone). Codes
* stay text as the source wrote them: variables named like one (coordinate,
* vectorid, dguid, NAICS, postal code) and ones with a leading zero (01).
* rename *, lower stops at a clash (Indicator next to indicator), so names
* that lower-case alike are numbered as janitor numbers them (indicator,
* indicator_2), then renamed in one group rename, which allows swaps.

local names
foreach var of varlist _all {
    local names `names' `=strlower("`var'")'
}
local dups : list dups names
while "`dups'" != "" {
    local numbered
    local before
    foreach name of local names {
        local count 1
        foreach earlier of local before {
            if "`earlier'" == "`name'" local ++count
        }
        local before `before' `name'
        if `count' > 1 {
            local name = substr("`name'", 1, 32 - strlen("_`count'")) + "_`count'"
        }
        local numbered `numbered' `name'
    }
    local names `numbered'
    local dups : list dups names
}
local old_names
local new_names
local i 0
foreach var of varlist _all {
    local ++i
    local name : word `i' of `names'
    if "`name'" != "`var'" {
        local old_names `old_names' `var'
        local new_names `new_names' `name'
    }
}
if "`old_names'" != "" {
    rename (`old_names') (`new_names')
}
quietly ds, has(type string)
local text_vars `r(varlist)'
foreach var of local text_vars {
    replace `var' = strtrim(`var')
    if regexm("`var'|", "^(coordinate|vector|vector_?id|product_?id|pid|dguid|[a-z0-9_]*_dguid|postal_?code|[a-z0-9_]*_postal_code|fsa|[a-z0-9_]*naics[a-z0-9_]*|noc|noc_[a-z0-9_]*|[a-z0-9_]*_noc|sgc|sgc_[a-z0-9_]*|[a-z0-9_]*_sgc)[|]") continue
    quietly count if regexm(`var', "^-?0[0-9]")
    if r(N) > 0 continue
    destring `var', replace
}

log close

Julia

# ============================================================
# IRCC monthly update: Canada - Permanent Residents by Province/Territory and CMA
# Purpose: Fetch the data behind MapleStats MCP's ircc_monthly_query
#          (exact: the source file, then the tool's filters)
# Inputs:  https://www.ircc.canada.ca/opendata-donneesouvertes/data/ODP-PR-PT_CMA.csv
# Outputs: data/raw/ODP-PR-PT_CMA.csv; the prepared table as `data`
# ============================================================

# 0. Setup

using CSV
using DataFrames
using Downloads
using TidierData

mkpath("data/raw")

# 1. Read inputs

Downloads.download("https://www.ircc.canada.ca/opendata-donneesouvertes/data/ODP-PR-PT_CMA.csv", "data/raw/ODP-PR-PT_CMA.csv")
data = CSV.read("data/raw/ODP-PR-PT_CMA.csv", DataFrame, delim = "\t", types = String, missingstring = ["", "--"])

# 2. Check inputs

@assert nrow(data) > 0 "https://www.ircc.canada.ca/opendata-donneesouvertes/data/ODP-PR-PT_CMA.csv returned no rows"

# 3. Prepare data

# Keep the rows the MapleStats tool kept.

data = filter(row -> begin
        (lowercase(strip(coalesce(string(row["EN_CENSUS_METROPOLITAN_AREA"]), ""))) == "edmonton") &&
        (!ismissing(row["EN_YEAR"]) && something(tryparse(Float64, string(row["EN_YEAR"])), NaN) >= 2015)
    end, data)

# Standard cleaning: snake_case names, trimmed text, empty strings as missing.

data = @chain data begin
    @clean_names
end
data = mapcols(
    col -> eltype(col) <: Union{Missing, AbstractString} ?
        [ismissing(x) || isempty(strip(x)) ? missing : strip(x) for x in col] : col,
    data,
)

# Numbers stored as text become numbers. Codes stay text as the source
# wrote them: columns named like one (coordinate, vector_id, dguid, NAICS,
# postal code) and columns with a leading zero (01, 000000001).

code_columns = r"^(coordinate|vector|vector_?id|product_?id|pid|dguid|[a-z0-9_]*_dguid|postal_?code|[a-z0-9_]*_postal_code|fsa|[a-z0-9_]*naics[a-z0-9_]*|noc|noc_[a-z0-9_]*|[a-z0-9_]*_noc|sgc|sgc_[a-z0-9_]*|[a-z0-9_]*_sgc)$"
for name in names(data)
    column = data[!, name]
    if occursin(code_columns, name)
        data[!, name] = [ismissing(x) ? missing : string(x) for x in column]
        continue
    end
    eltype(column) <: Union{Missing, AbstractString} || continue
    values = collect(skipmissing(column))
    (isempty(values) || any(v -> occursin(r"^-?0[0-9]", v), values)) && continue
    any(v -> isnothing(tryparse(Float64, v)), values) && continue
    whole = all(v -> !isnothing(tryparse(Int, v)), values)
    data[!, name] = [
        ismissing(x) ? missing : parse(whole ? Int : Float64, x) for x in column
    ]
end

Excel (Power Query)

// ============================================================
// IRCC monthly update: Canada - Permanent Residents by Province/Territory and CMA
// Purpose: Fetch the data behind MapleStats MCP's ircc_monthly_query
//          (exact: the source file, then the tool's filters)
// Inputs:  https://www.ircc.canada.ca/opendata-donneesouvertes/data/ODP-PR-PT_CMA.csv
// Outputs: the prepared table, loaded to a worksheet or the Data Model
// How to run: Excel > Data > Get Data > From Other Sources > Blank Query >
//   Advanced Editor; paste this query, Done, then Close & Load. Answer the
//   first prompt with Anonymous access; Data > Refresh All fetches it again.
//   A worksheet holds 1,048,576 rows: load a larger table (a full StatCan
//   table can be) with Close & Load To > Only Create Connection > Add this
//   data to the Data Model.
// ============================================================
let
    // 0. Setup
    // Cell values as text or number, null-safe, for the filters and checks.
    AsText = (value as any) as text =>
        if value = null then "" else (try Text.From(value) otherwise ""),
    AsNumber = (value as any) as nullable number =>
        if value = null then null else (try Number.From(value, "en-US") otherwise null),
    // Records to a table with every field any record has, not only the first's.
    ToTable = (records as list) as table =>
        Table.FromRecords(
            records,
            List.Distinct(List.Combine(List.Transform(records, Record.FieldNames))),
            MissingField.UseNull
        ),
    // 1. Read inputs
    Raw = Web.Contents("https://www.ircc.canada.ca/opendata-donneesouvertes/data/ODP-PR-PT_CMA.csv"),
    Csv = Csv.Document(Raw, [Delimiter = "#(0009)", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
    Loaded = Table.PromoteHeaders(Csv, [PromoteAllScalars = true]),
    // 2. Check inputs
    // Stop with a clear message instead of loading an empty table.
    Checked = if Table.IsEmpty(Loaded)
        then error Error.Record("MapleStats", "https://www.ircc.canada.ca/opendata-donneesouvertes/data/ODP-PR-PT_CMA.csv returned no rows")
        else Loaded,
    // 3. Prepare data
    Headers = Table.TransformColumnNames(
        Checked,
        (column) =>
            let
                name = Text.Trim(column, {Character.FromNumber(65279)})
            in
                name
    ),
    // The source writes missing cells as --.
    Missing = Table.TransformColumns(
        Headers,
        List.Transform(
            Table.ColumnNames(Headers),
            (name) =>
                {name, (value) => if List.Contains({"--"}, value) then null else value}
        )
    ),
    // Keep the rows the MapleStats tool kept.
    Filtered = Table.SelectRows(
        Missing,
        each Text.Lower(Text.Trim(AsText(Record.Field(_, "EN_CENSUS_METROPOLITAN_AREA")))) = "edmonton"
            and (let n = AsNumber(Record.Field(_, "EN_YEAR")) in n <> null and n >= 2015)
    ),
    // Standard cleaning, as the R script's clean_names() and type_convert():
    // snake_case names without accents (PÉRIODE -> periode, referenceNumber ->
    // reference_number), names that clean alike numbered (indicator,
    // indicator_2), trimmed text, empty text as null, then columns whose
    // every value is a number or an ISO date (YYYY-MM-DD) converted.
    Accents = [#"à" = "a", #"â" = "a", #"ä" = "a", #"á" = "a", #"ç" = "c", #"é" = "e", #"è" = "e", #"ê" = "e", #"ë" = "e", #"î" = "i", #"ï" = "i", #"í" = "i", #"ô" = "o", #"ö" = "o", #"ó" = "o", #"ù" = "u", #"û" = "u", #"ü" = "u", #"ú" = "u", #"ÿ" = "y", #"ñ" = "n", #"œ" = "oe", #"æ" = "ae", #"À" = "A", #"Â" = "A", #"Ä" = "A", #"Á" = "A", #"Ç" = "C", #"É" = "E", #"È" = "E", #"Ê" = "E", #"Ë" = "E", #"Î" = "I", #"Ï" = "I", #"Í" = "I", #"Ô" = "O", #"Ö" = "O", #"Ó" = "O", #"Ù" = "U", #"Û" = "U", #"Ü" = "U", #"Ú" = "U", #"Ÿ" = "Y", #"Ñ" = "N", #"Œ" = "OE", #"Æ" = "AE"],
    CleanName = (name as text) as text =>
        let
            Plain = Text.Combine(
                List.Transform(Text.ToList(name), (ch) => Record.FieldOrDefault(Accents, ch, ch))
            ),
            Words = Splitter.SplitTextByCharacterTransition({"a".."z", "0".."9"}, {"A".."Z"})(
                Plain
            ),
            Lower = Text.Lower(Text.Combine(Words, "_")),
            Spaced = Text.Combine(
                List.Transform(
                    Text.ToList(Lower), (ch) => if List.Contains(NameCharacters, ch) then ch else " "
                )
            ),
            Snake = Text.Combine(List.Select(Text.Split(Spaced, " "), (part) => part <> ""), "_")
        in
            if Snake = "" then "x" else Snake,
    NameCharacters = {"a".."z", "0".."9"},
    OldNames = Table.ColumnNames(Filtered),
    CleanNames = List.Transform(OldNames, CleanName),
    NewNames = List.Transform(
        List.Positions(CleanNames),
        (i) =>
            let
                Earlier = List.Count(
                    List.Select(List.FirstN(CleanNames, i), (name) => name = CleanNames{i})
                )
            in
                if Earlier = 0 then CleanNames{i} else CleanNames{i} & "_" & Text.From(Earlier + 1)
    ),
    Renamed = Table.RenameColumns(
        Filtered,
        List.Select(List.Zip({OldNames, NewNames}), (pair) => pair{0} <> pair{1})
    ),
    Trimmed = Table.TransformColumns(
        Renamed,
        List.Transform(
            Table.ColumnNames(Renamed),
            (name) =>
                {
                    name,
                    (value) =>
                        if value is text
                        then (if Text.Trim(value) = "" then null else Text.Trim(value))
                        else value
                }
        )
    ),
    IsNumberText = (value as any) as logical =>
        value is number
        or (value is text and (try Number.FromText(value, "en-US") otherwise null) <> null),
    IsIsoDate = (value as any) as logical =>
        value is text
        and Text.Length(value) = 10
        and Text.Middle(value, 4, 1) = "-"
        and Text.Middle(value, 7, 1) = "-"
        and (try Date.FromText(value, "en-US") otherwise null) <> null,
    ColumnsWhere = (table as table, test as function) as list =>
        List.Select(
            Table.ColumnNames(table),
            (name) =>
                let
                    Values = List.RemoveNulls(Table.Column(table, name))
                in
                    not List.IsEmpty(Values) and List.AllTrue(List.Transform(Values, test))
        ),
    // Codes stay text as the source wrote them: columns named like one
    // (coordinate, vector_id, dguid, NAICS, postal code; StatCan's "2.1" and
    // "2.10" are different coordinates) and columns with a leading zero (01).
    CodeName = (name as text) as logical =>
        List.Contains(
            {"coordinate", "vector", "vector_id", "vectorid", "product_id", "productid", "pid",
              "dguid", "postal_code", "postalcode", "fsa", "noc", "sgc"},
            name
        )
        or Text.EndsWith(name, "_dguid")
        or Text.EndsWith(name, "_postal_code")
        or Text.Contains(name, "naics")
        or Text.StartsWith(name, "noc_")
        or Text.EndsWith(name, "_noc")
        or Text.StartsWith(name, "sgc_")
        or Text.EndsWith(name, "_sgc"),
    HasLeadingZero = (value as any) as logical =>
        value is text
        and (
            let
                Digits = if Text.StartsWith(value, "-") then Text.Middle(value, 1) else value
            in
                Text.Length(Digits) > 1
                and Text.Start(Digits, 1) = "0"
                and List.Contains({"0".."9"}, Text.Middle(Digits, 1, 1))
        ),
    CodeColumns = List.Select(
        Table.ColumnNames(Trimmed),
        (name) => CodeName(name) or List.AnyTrue(List.Transform(Table.Column(Trimmed, name), HasLeadingZero))
    ),
    Codes = Table.TransformColumns(
        Trimmed,
        List.Transform(
            CodeColumns,
            (name) =>
                {
                    name,
                    (value) =>
                        if value = null then null
                        else if value is number then Number.ToText(value, "G", "en-US")
                        else Text.From(value),
                    type text
                }
        )
    ),
    NumberColumns = List.Difference(ColumnsWhere(Codes, IsNumberText), CodeColumns),
    Numbers = Table.TransformColumnTypes(
        Codes, List.Transform(NumberColumns, (name) => {name, type number}), "en-US"
    ),
    DateColumns = ColumnsWhere(Numbers, IsIsoDate),
    Cleaned = Table.TransformColumns(
        Numbers,
        List.Transform(
            DateColumns,
            (name) => {name, (value) => if value = null then null else Date.FromText(value, "en-US"), type date}
        )
    ),
    Data = Cleaned
in
    Data

R

# ============================================================
# Reproduce cmhc_get_table_data
# Purpose: Fetch the data behind MapleStats MCP's cmhc_get_table_data
#          (exact: the request the tool made, recorded while it ran)
# Inputs:  https://www03.cmhc-schl.gc.ca/hmip-pimh/en/TableMapChart/ExportTable
# Outputs: data/raw/ExportTable.csv; the prepared table as `data`
# ============================================================

# 0. Setup ----

library(dplyr)
library(httr2)
library(janitor)
library(purrr)
library(readr)
library(stringr)

dir.create("data/raw", recursive = TRUE, showWarnings = FALSE)

# 1. Read inputs ----

request("https://www03.cmhc-schl.gc.ca/hmip-pimh/en/TableMapChart/ExportTable") |>
  req_body_form(!!!list(`TableId` = "5.6.1", `GeographyId` = "1", `GeographyTypeId` = "1", `exportType` = "csv")) |>
  req_perform(path = "data/raw/ExportTable.csv")

# The file opens with 2 title line(s) above its header; read the table between.

lines <- read_lines("data/raw/ExportTable.csv", skip = 2)
data <- read_delim(I(str_c(lines, collapse = "\n")), delim = ",",
  col_types = cols(.default = col_character()))

# 2. Check inputs ----

stopifnot("https://www03.cmhc-schl.gc.ca/hmip-pimh/en/TableMapChart/ExportTable returned no rows" = nrow(data) > 0)

# 3. Prepare data ----

data <- data |>
  clean_names()

# Standard cleaning: trimmed text, empty strings as missing, and numbers
# stored as text converted to numbers. Codes stay text as the source
# wrote them: columns named like one (coordinate, vector_id, dguid, NAICS,
# postal code) and columns with a leading zero (01, 000000001).

code_columns <- "^(coordinate|vector|vector_?id|product_?id|pid|dguid|[a-z0-9_]*_dguid|postal_?code|[a-z0-9_]*_postal_code|fsa|[a-z0-9_]*naics[a-z0-9_]*|noc|noc_[a-z0-9_]*|[a-z0-9_]*_noc|sgc|sgc_[a-z0-9_]*|[a-z0-9_]*_sgc)$"
data <- data |>
  mutate(across(where(is.character), \(x) na_if(str_trim(x), "")))
keep_text <- names(data)[
  str_detect(names(data), code_columns) |
    map_lgl(
      data,
      \(column) is.character(column) && any(str_detect(column, "^-?0[0-9]"), na.rm = TRUE)
    )
]
data <- data |>
  mutate(
    across(any_of(keep_text), \(x) if (is.list(x)) x else as.character(x)),
    across(where(is.character) & !any_of(keep_text), parse_guess)
  )

Python

# ============================================================
# Reproduce cmhc_get_table_data
# Purpose: Fetch the data behind MapleStats MCP's cmhc_get_table_data
#          (exact: the request the tool made, recorded while it ran)
# Inputs:  https://www03.cmhc-schl.gc.ca/hmip-pimh/en/TableMapChart/ExportTable
# Outputs: data/raw/ExportTable.csv; the prepared table as `data`
# ============================================================

# %% 0. Setup

import io
import re
import unicodedata
from pathlib import Path

import httpx
import polars as pl

RAW_DIR = Path("data/raw")
RAW_DIR.mkdir(parents=True, exist_ok=True)
raw_path = RAW_DIR / "ExportTable.csv"

# %% 1. Read inputs

with httpx.Client(
    http2=True,
    follow_redirects=True,
    timeout=300,
    headers={"User-Agent": "Mozilla/5.0 (compatible; research script)"},
) as client:
    response = client.post('https://www03.cmhc-schl.gc.ca/hmip-pimh/en/TableMapChart/ExportTable', data={'TableId': '5.6.1', 'GeographyId': '1', 'GeographyTypeId': '1', 'exportType': 'csv'})
response.raise_for_status()
raw_path.write_bytes(response.content)

# The file opens with 2 title line(s) above its header; read the table between.

lines = raw_path.read_text(encoding="utf-8", errors="replace").splitlines()[2:]
data = pl.read_csv(io.StringIO("\n".join(lines)), separator=',', infer_schema=False)

# %% 2. Check inputs

assert data.height > 0, "https://www03.cmhc-schl.gc.ca/hmip-pimh/en/TableMapChart/ExportTable returned no rows"

# %% 3. Prepare data

# Standard cleaning: snake_case names as janitor's clean_names() gives them
# in R (PÉRIODE -> periode, referenceNumber -> reference_number, # -> number,
# % -> percent, apostrophes dropped, a leading digit prefixed with x),
# trimmed text, empty strings as missing. Names that clean alike are
# numbered as janitor numbers them (Indicator, indicator -> indicator,
# indicator_2).

clean_names = [
    re.sub(
        r"[^0-9a-z]+",
        "_",
        re.sub(
            r"([a-z0-9])([A-Z])",
            r"\1_\2",
            "".join(
                " " if unicodedata.category(ch)[0] in "PS" and not ch.isascii() else ch
                for ch in unicodedata.normalize("NFKD", column.replace("'", ""))
            )
            .replace("#", " number ")
            .replace("%", " percent ")
            .encode("ascii", "ignore")
            .decode(),
        ).lower(),
    ).strip("_")
    or "x"
    for column in data.columns
]
clean_names = [f"x{name}" if name[0].isdigit() else name for name in clean_names]
while len(set(clean_names)) < len(clean_names):
    name_counts = {}
    numbered = []
    for name in clean_names:
        name_counts[name] = name_counts.get(name, 0) + 1
        count = name_counts[name]
        numbered.append(name if count == 1 else f"{name}_{count}")
    clean_names = numbered
data = data.rename(dict(zip(data.columns, clean_names)))
data = data.with_columns(pl.col(pl.Utf8).str.strip_chars().replace("", None))

# Numbers stored as text become numbers. Codes stay text as the source
# wrote them: columns named like one (coordinate, vector_id, dguid, NAICS,
# postal code) and columns with a leading zero (01, 000000001).

code_columns = re.compile(r"^(coordinate|vector|vector_?id|product_?id|pid|dguid|[a-z0-9_]*_dguid|postal_?code|[a-z0-9_]*_postal_code|fsa|[a-z0-9_]*naics[a-z0-9_]*|noc|noc_[a-z0-9_]*|[a-z0-9_]*_noc|sgc|sgc_[a-z0-9_]*|[a-z0-9_]*_sgc)$")
for column in data.columns:
    dtype = data.schema[column]
    if code_columns.search(column) and not dtype.is_nested():
        data = data.with_columns(pl.col(column).cast(pl.Utf8))
        continue
    if dtype != pl.Utf8 or data[column].null_count() == data.height:
        continue
    numbers = data[column].cast(pl.Float64, strict=False)
    leading_zero = data[column].str.contains(r"^-?0[0-9]").any()
    if numbers.null_count() > data[column].null_count() or leading_zero:
        continue
    whole = data[column].cast(pl.Int64, strict=False)
    data = data.with_columns(whole if whole.null_count() == numbers.null_count() else numbers)

Stata

* ============================================================
* Reproduce cmhc_get_table_data
* Purpose: Fetch the data behind MapleStats MCP's cmhc_get_table_data
*          (exact: the request the tool made, recorded while it ran)
* Inputs:  https://www03.cmhc-schl.gc.ca/hmip-pimh/en/TableMapChart/ExportTable
* Outputs: data/raw/ExportTable.csv; the prepared table as `data`
* ============================================================

version 18
clear all
set more off

* 0. Setup

capture mkdir "logs"
capture log close
log using "logs/cmhc_get_table_data.log", replace
capture mkdir "data"
capture mkdir "data/raw"

* 1. Read inputs

* Stata reads no JSON or HTML and truncates long column names, so its
* built-in Python (Stata 16+) fetches, filters and writes a CSV. Point
* Stata at a Python with these packages first: python set exec <path>.

python:
import io
import json
from pathlib import Path
import httpx
import polars as pl
RAW_DIR = Path("data/raw")
RAW_DIR.mkdir(parents=True, exist_ok=True)
raw_path = RAW_DIR / "ExportTable.csv"
with httpx.Client(http2=True, follow_redirects=True, timeout=300, headers={"User-Agent": "Mozilla/5.0 (compatible; research script)"}) as client: response = client.post('https://www03.cmhc-schl.gc.ca/hmip-pimh/en/TableMapChart/ExportTable', data={'TableId': '5.6.1', 'GeographyId': '1', 'GeographyTypeId': '1', 'exportType': 'csv'})
response.raise_for_status()
raw_path.write_bytes(response.content)
lines = raw_path.read_text(encoding="utf-8", errors="replace").splitlines()[2:]
data = pl.read_csv(io.StringIO("\n".join(lines)), separator=',', infer_schema=False)
nested = [name for name, dtype in data.schema.items() if dtype.is_nested()]
data = data.with_columns(pl.col(name).map_elements(lambda value: json.dumps(value.to_list() if isinstance(value, pl.Series) else value, default=str), return_dtype=pl.Utf8) for name in nested)
data.write_csv(RAW_DIR / "ExportTable_prepared.csv")
end

import delimited "data/raw/ExportTable_prepared.csv", clear varnames(1) encoding("utf-8") stringcols(_all) bindquote(strict) maxquotedrows(unlimited)

* 2. Check inputs

assert _N > 0

* 3. Prepare data

* Standard cleaning: lower-case names, trimmed text, and numbers stored as
* text converted (destring leaves genuinely non-numeric text alone). Codes
* stay text as the source wrote them: variables named like one (coordinate,
* vectorid, dguid, NAICS, postal code) and ones with a leading zero (01).
* rename *, lower stops at a clash (Indicator next to indicator), so names
* that lower-case alike are numbered as janitor numbers them (indicator,
* indicator_2), then renamed in one group rename, which allows swaps.

local names
foreach var of varlist _all {
    local names `names' `=strlower("`var'")'
}
local dups : list dups names
while "`dups'" != "" {
    local numbered
    local before
    foreach name of local names {
        local count 1
        foreach earlier of local before {
            if "`earlier'" == "`name'" local ++count
        }
        local before `before' `name'
        if `count' > 1 {
            local name = substr("`name'", 1, 32 - strlen("_`count'")) + "_`count'"
        }
        local numbered `numbered' `name'
    }
    local names `numbered'
    local dups : list dups names
}
local old_names
local new_names
local i 0
foreach var of varlist _all {
    local ++i
    local name : word `i' of `names'
    if "`name'" != "`var'" {
        local old_names `old_names' `var'
        local new_names `new_names' `name'
    }
}
if "`old_names'" != "" {
    rename (`old_names') (`new_names')
}
quietly ds, has(type string)
local text_vars `r(varlist)'
foreach var of local text_vars {
    replace `var' = strtrim(`var')
    if regexm("`var'|", "^(coordinate|vector|vector_?id|product_?id|pid|dguid|[a-z0-9_]*_dguid|postal_?code|[a-z0-9_]*_postal_code|fsa|[a-z0-9_]*naics[a-z0-9_]*|noc|noc_[a-z0-9_]*|[a-z0-9_]*_noc|sgc|sgc_[a-z0-9_]*|[a-z0-9_]*_sgc)[|]") continue
    quietly count if regexm(`var', "^-?0[0-9]")
    if r(N) > 0 continue
    destring `var', replace
}

log close

Julia

# ============================================================
# Reproduce cmhc_get_table_data
# Purpose: Fetch the data behind MapleStats MCP's cmhc_get_table_data
#          (exact: the request the tool made, recorded while it ran)
# Inputs:  https://www03.cmhc-schl.gc.ca/hmip-pimh/en/TableMapChart/ExportTable
# Outputs: data/raw/ExportTable.csv; the prepared table as `data`
# ============================================================

# 0. Setup

using CSV
using DataFrames
using Downloads
using TidierData

mkpath("data/raw")

# 1. Read inputs

Downloads.request(
    "https://www03.cmhc-schl.gc.ca/hmip-pimh/en/TableMapChart/ExportTable";
    headers = ["Content-Type" => "application/x-www-form-urlencoded"], method = "POST", input = IOBuffer("TableId=5.6.1&GeographyId=1&GeographyTypeId=1&exportType=csv"),
    output = "data/raw/ExportTable.csv",
)

# The file opens with 2 title line(s) above its header; read the table between.

lines = readlines("data/raw/ExportTable.csv")[(2 + 1):end]
data = CSV.read(IOBuffer(join(lines, "\n")), DataFrame, types = String)

# 2. Check inputs

@assert nrow(data) > 0 "https://www03.cmhc-schl.gc.ca/hmip-pimh/en/TableMapChart/ExportTable returned no rows"

# 3. Prepare data

# Standard cleaning: snake_case names, trimmed text, empty strings as missing.

data = @chain data begin
    @clean_names
end
data = mapcols(
    col -> eltype(col) <: Union{Missing, AbstractString} ?
        [ismissing(x) || isempty(strip(x)) ? missing : strip(x) for x in col] : col,
    data,
)

# Numbers stored as text become numbers. Codes stay text as the source
# wrote them: columns named like one (coordinate, vector_id, dguid, NAICS,
# postal code) and columns with a leading zero (01, 000000001).

code_columns = r"^(coordinate|vector|vector_?id|product_?id|pid|dguid|[a-z0-9_]*_dguid|postal_?code|[a-z0-9_]*_postal_code|fsa|[a-z0-9_]*naics[a-z0-9_]*|noc|noc_[a-z0-9_]*|[a-z0-9_]*_noc|sgc|sgc_[a-z0-9_]*|[a-z0-9_]*_sgc)$"
for name in names(data)
    column = data[!, name]
    if occursin(code_columns, name)
        data[!, name] = [ismissing(x) ? missing : string(x) for x in column]
        continue
    end
    eltype(column) <: Union{Missing, AbstractString} || continue
    values = collect(skipmissing(column))
    (isempty(values) || any(v -> occursin(r"^-?0[0-9]", v), values)) && continue
    any(v -> isnothing(tryparse(Float64, v)), values) && continue
    whole = all(v -> !isnothing(tryparse(Int, v)), values)
    data[!, name] = [
        ismissing(x) ? missing : parse(whole ? Int : Float64, x) for x in column
    ]
end

Excel (Power Query)

// ============================================================
// Reproduce cmhc_get_table_data
// Purpose: Fetch the data behind MapleStats MCP's cmhc_get_table_data
//          (exact: the request the tool made, recorded while it ran)
// Inputs:  https://www03.cmhc-schl.gc.ca/hmip-pimh/en/TableMapChart/ExportTable
// Outputs: the prepared table, loaded to a worksheet or the Data Model
// How to run: Excel > Data > Get Data > From Other Sources > Blank Query >
//   Advanced Editor; paste this query, Done, then Close & Load. Answer the
//   first prompt with Anonymous access; Data > Refresh All fetches it again.
//   A worksheet holds 1,048,576 rows: load a larger table (a full StatCan
//   table can be) with Close & Load To > Only Create Connection > Add this
//   data to the Data Model.
// ============================================================
let
    // 0. Setup
    // Cell values as text or number, null-safe, for the filters and checks.
    AsText = (value as any) as text =>
        if value = null then "" else (try Text.From(value) otherwise ""),
    AsNumber = (value as any) as nullable number =>
        if value = null then null else (try Number.From(value, "en-US") otherwise null),
    // Records to a table with every field any record has, not only the first's.
    ToTable = (records as list) as table =>
        Table.FromRecords(
            records,
            List.Distinct(List.Combine(List.Transform(records, Record.FieldNames))),
            MissingField.UseNull
        ),
    // 1. Read inputs
    Raw = Web.Contents("https://www03.cmhc-schl.gc.ca/hmip-pimh/en/TableMapChart/ExportTable", [Headers = [#"Content-Type" = "application/x-www-form-urlencoded"], Content = Text.ToBinary(Uri.BuildQueryString([#"TableId" = "5.6.1", #"GeographyId" = "1", #"GeographyTypeId" = "1", #"exportType" = "csv"]))]),
    // The file opens with 2 title line(s) above its header; read the table between.
    Body = List.Skip(Lines.FromBinary(Raw, null, null, 65001), 2),
    Csv = Csv.Document(Text.Combine(Body, "#(cr,lf)"), [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
    Loaded = Table.PromoteHeaders(Csv, [PromoteAllScalars = true]),
    // 2. Check inputs
    // Stop with a clear message instead of loading an empty table.
    Checked = if Table.IsEmpty(Loaded)
        then error Error.Record("MapleStats", "https://www03.cmhc-schl.gc.ca/hmip-pimh/en/TableMapChart/ExportTable returned no rows")
        else Loaded,
    // 3. Prepare data
    Headers = Table.TransformColumnNames(
        Checked,
        (column) =>
            let
                name = Text.Trim(column, {Character.FromNumber(65279)})
            in
                name
    ),
    // Standard cleaning, as the R script's clean_names() and type_convert():
    // snake_case names without accents (PÉRIODE -> periode, referenceNumber ->
    // reference_number), names that clean alike numbered (indicator,
    // indicator_2), trimmed text, empty text as null, then columns whose
    // every value is a number or an ISO date (YYYY-MM-DD) converted.
    Accents = [#"à" = "a", #"â" = "a", #"ä" = "a", #"á" = "a", #"ç" = "c", #"é" = "e", #"è" = "e", #"ê" = "e", #"ë" = "e", #"î" = "i", #"ï" = "i", #"í" = "i", #"ô" = "o", #"ö" = "o", #"ó" = "o", #"ù" = "u", #"û" = "u", #"ü" = "u", #"ú" = "u", #"ÿ" = "y", #"ñ" = "n", #"œ" = "oe", #"æ" = "ae", #"À" = "A", #"Â" = "A", #"Ä" = "A", #"Á" = "A", #"Ç" = "C", #"É" = "E", #"È" = "E", #"Ê" = "E", #"Ë" = "E", #"Î" = "I", #"Ï" = "I", #"Í" = "I", #"Ô" = "O", #"Ö" = "O", #"Ó" = "O", #"Ù" = "U", #"Û" = "U", #"Ü" = "U", #"Ú" = "U", #"Ÿ" = "Y", #"Ñ" = "N", #"Œ" = "OE", #"Æ" = "AE"],
    CleanName = (name as text) as text =>
        let
            Plain = Text.Combine(
                List.Transform(Text.ToList(name), (ch) => Record.FieldOrDefault(Accents, ch, ch))
            ),
            Words = Splitter.SplitTextByCharacterTransition({"a".."z", "0".."9"}, {"A".."Z"})(
                Plain
            ),
            Lower = Text.Lower(Text.Combine(Words, "_")),
            Spaced = Text.Combine(
                List.Transform(
                    Text.ToList(Lower), (ch) => if List.Contains(NameCharacters, ch) then ch else " "
                )
            ),
            Snake = Text.Combine(List.Select(Text.Split(Spaced, " "), (part) => part <> ""), "_")
        in
            if Snake = "" then "x" else Snake,
    NameCharacters = {"a".."z", "0".."9"},
    OldNames = Table.ColumnNames(Headers),
    CleanNames = List.Transform(OldNames, CleanName),
    NewNames = List.Transform(
        List.Positions(CleanNames),
        (i) =>
            let
                Earlier = List.Count(
                    List.Select(List.FirstN(CleanNames, i), (name) => name = CleanNames{i})
                )
            in
                if Earlier = 0 then CleanNames{i} else CleanNames{i} & "_" & Text.From(Earlier + 1)
    ),
    Renamed = Table.RenameColumns(
        Headers,
        List.Select(List.Zip({OldNames, NewNames}), (pair) => pair{0} <> pair{1})
    ),
    Trimmed = Table.TransformColumns(
        Renamed,
        List.Transform(
            Table.ColumnNames(Renamed),
            (name) =>
                {
                    name,
                    (value) =>
                        if value is text
                        then (if Text.Trim(value) = "" then null else Text.Trim(value))
                        else value
                }
        )
    ),
    IsNumberText = (value as any) as logical =>
        value is number
        or (value is text and (try Number.FromText(value, "en-US") otherwise null) <> null),
    IsIsoDate = (value as any) as logical =>
        value is text
        and Text.Length(value) = 10
        and Text.Middle(value, 4, 1) = "-"
        and Text.Middle(value, 7, 1) = "-"
        and (try Date.FromText(value, "en-US") otherwise null) <> null,
    ColumnsWhere = (table as table, test as function) as list =>
        List.Select(
            Table.ColumnNames(table),
            (name) =>
                let
                    Values = List.RemoveNulls(Table.Column(table, name))
                in
                    not List.IsEmpty(Values) and List.AllTrue(List.Transform(Values, test))
        ),
    // Codes stay text as the source wrote them: columns named like one
    // (coordinate, vector_id, dguid, NAICS, postal code; StatCan's "2.1" and
    // "2.10" are different coordinates) and columns with a leading zero (01).
    CodeName = (name as text) as logical =>
        List.Contains(
            {"coordinate", "vector", "vector_id", "vectorid", "product_id", "productid", "pid",
              "dguid", "postal_code", "postalcode", "fsa", "noc", "sgc"},
            name
        )
        or Text.EndsWith(name, "_dguid")
        or Text.EndsWith(name, "_postal_code")
        or Text.Contains(name, "naics")
        or Text.StartsWith(name, "noc_")
        or Text.EndsWith(name, "_noc")
        or Text.StartsWith(name, "sgc_")
        or Text.EndsWith(name, "_sgc"),
    HasLeadingZero = (value as any) as logical =>
        value is text
        and (
            let
                Digits = if Text.StartsWith(value, "-") then Text.Middle(value, 1) else value
            in
                Text.Length(Digits) > 1
                and Text.Start(Digits, 1) = "0"
                and List.Contains({"0".."9"}, Text.Middle(Digits, 1, 1))
        ),
    CodeColumns = List.Select(
        Table.ColumnNames(Trimmed),
        (name) => CodeName(name) or List.AnyTrue(List.Transform(Table.Column(Trimmed, name), HasLeadingZero))
    ),
    Codes = Table.TransformColumns(
        Trimmed,
        List.Transform(
            CodeColumns,
            (name) =>
                {
                    name,
                    (value) =>
                        if value = null then null
                        else if value is number then Number.ToText(value, "G", "en-US")
                        else Text.From(value),
                    type text
                }
        )
    ),
    NumberColumns = List.Difference(ColumnsWhere(Codes, IsNumberText), CodeColumns),
    Numbers = Table.TransformColumnTypes(
        Codes, List.Transform(NumberColumns, (name) => {name, type number}), "en-US"
    ),
    DateColumns = ColumnsWhere(Numbers, IsIsoDate),
    Cleaned = Table.TransformColumns(
        Numbers,
        List.Transform(
            DateColumns,
            (name) => {name, (value) => if value = null then null else Date.FromText(value, "en-US"), type date}
        )
    ),
    Data = Cleaned
in
    Data

R

# ============================================================
# StatCan public use microdata file
# Purpose: Fetch the data behind MapleStats MCP's statcan_pumf_tabulate
#          (exact: the PUMF ZIP)
# Inputs:  https://www150.statcan.gc.ca/n1/pub/98m0001x/2023001/cen21_ind_98m0001x_part_rec21.zip
# Outputs: data/raw/cen21_ind_98m0001x_part_rec21.zip; the unzipped files
# ============================================================

# 0. Setup ----

dir.create("data/raw", recursive = TRUE, showWarnings = FALSE)

# 1. Read inputs ----

download.file("https://www150.statcan.gc.ca/n1/pub/98m0001x/2023001/cen21_ind_98m0001x_part_rec21.zip", "data/raw/cen21_ind_98m0001x_part_rec21.zip", mode = "wb")
zip::unzip("data/raw/cen21_ind_98m0001x_part_rec21.zip", exdir = "data/raw/cen21_ind_98m0001x_part_rec21")
file.remove("data/raw/cen21_ind_98m0001x_part_rec21.zip")

data_files <- list.files("data/raw/cen21_ind_98m0001x_part_rec21", full.names = TRUE, recursive = TRUE)

Python

# ============================================================
# StatCan public use microdata file
# Purpose: Fetch the data behind MapleStats MCP's statcan_pumf_tabulate
#          (exact: the PUMF ZIP)
# Inputs:  https://www150.statcan.gc.ca/n1/pub/98m0001x/2023001/cen21_ind_98m0001x_part_rec21.zip
# Outputs: data/raw/cen21_ind_98m0001x_part_rec21.zip; the unzipped files
# ============================================================

# %% 0. Setup

import zipfile
from pathlib import Path

import httpx
import polars as pl

RAW_DIR = Path("data/raw")
RAW_DIR.mkdir(parents=True, exist_ok=True)
raw_path = RAW_DIR / "cen21_ind_98m0001x_part_rec21.zip"

# %% 1. Read inputs

with httpx.Client(
    http2=True,
    follow_redirects=True,
    timeout=300,
    headers={"User-Agent": "Mozilla/5.0 (compatible; research script)"},
) as client:
    response = client.get('https://www150.statcan.gc.ca/n1/pub/98m0001x/2023001/cen21_ind_98m0001x_part_rec21.zip')
response.raise_for_status()
raw_path.write_bytes(response.content)

with zipfile.ZipFile(raw_path) as archive:
    archive.extractall(RAW_DIR / raw_path.stem)

Stata

* ============================================================
* StatCan public use microdata file
* Purpose: Fetch the data behind MapleStats MCP's statcan_pumf_tabulate
*          (exact: the PUMF ZIP)
* Inputs:  https://www150.statcan.gc.ca/n1/pub/98m0001x/2023001/cen21_ind_98m0001x_part_rec21.zip
* Outputs: data/raw/cen21_ind_98m0001x_part_rec21.zip; the unzipped files
* ============================================================

version 18
clear all
set more off

* 0. Setup

capture mkdir "logs"
capture log close
log using "logs/statcan_pumf_tabulate.log", replace
capture mkdir "data"
capture mkdir "data/raw"

* 1. Read inputs

* Stata reads no JSON or HTML and truncates long column names, so its
* built-in Python (Stata 16+) fetches, filters and writes a CSV. Point
* Stata at a Python with these packages first: python set exec <path>.

python:
import json
import zipfile
from pathlib import Path
import httpx
import polars as pl
RAW_DIR = Path("data/raw")
RAW_DIR.mkdir(parents=True, exist_ok=True)
raw_path = RAW_DIR / "cen21_ind_98m0001x_part_rec21.zip"
with httpx.Client(http2=True, follow_redirects=True, timeout=300, headers={"User-Agent": "Mozilla/5.0 (compatible; research script)"}) as client: response = client.get('https://www150.statcan.gc.ca/n1/pub/98m0001x/2023001/cen21_ind_98m0001x_part_rec21.zip')
response.raise_for_status()
raw_path.write_bytes(response.content)
with zipfile.ZipFile(raw_path) as archive: archive.extractall(RAW_DIR / raw_path.stem)
end

log close

Julia

# ============================================================
# StatCan public use microdata file
# Purpose: Fetch the data behind MapleStats MCP's statcan_pumf_tabulate
#          (exact: the PUMF ZIP)
# Inputs:  https://www150.statcan.gc.ca/n1/pub/98m0001x/2023001/cen21_ind_98m0001x_part_rec21.zip
# Outputs: data/raw/cen21_ind_98m0001x_part_rec21.zip; the unzipped files
# ============================================================

# 0. Setup

using Downloads

mkpath("data/raw")

# 1. Read inputs

Downloads.download("https://www150.statcan.gc.ca/n1/pub/98m0001x/2023001/cen21_ind_98m0001x_part_rec21.zip", "data/raw/cen21_ind_98m0001x_part_rec21.zip")

R

# ============================================================
# StatCan table 11-10-0044-01
# Purpose: Fetch the data behind MapleStats MCP's wds_get_data_from_cube_coord
#          (exact: full table from the product id)
# Inputs:  https://www150.statcan.gc.ca/n1/tbl/csv/11100044-eng.zip
# Outputs: data/raw/11100044-eng.zip; the prepared table as `data`
# ============================================================

# 0. Setup ----

library(cansim)
library(dplyr)
library(janitor)
library(readr)
library(stringr)

dir.create("data/raw", recursive = TRUE, showWarnings = FALSE)

# 1. Read inputs ----

data <- get_cansim("11-10-0044-01")

# 2. Check inputs ----

stopifnot("https://www150.statcan.gc.ca/n1/tbl/csv/11100044-eng.zip returned no rows" = nrow(data) > 0)

# 3. Prepare data ----

data <- data |>
  clean_names()

# cansim adds val_norm (VALUE times its scalar factor) and a Date column;
# keep STATUS and SYMBOL, which flag estimates to use with caution.

data <- data |>
  filter(!is.na(val_norm))

# Standard cleaning: trimmed text, empty strings as missing, and numbers
# stored as text converted to numbers.

data <- data |>
  mutate(across(where(is.character), \(x) na_if(str_trim(x), ""))) |>
  type_convert()

Python

# ============================================================
# StatCan table 11-10-0044-01
# Purpose: Fetch the data behind MapleStats MCP's wds_get_data_from_cube_coord
#          (exact: full table from the product id)
# Inputs:  https://www150.statcan.gc.ca/n1/tbl/csv/11100044-eng.zip
# Outputs: data/raw/11100044-eng.zip; the prepared table as `data`
# ============================================================

# %% 0. Setup

import re
import unicodedata
import zipfile
from pathlib import Path

import httpx
import polars as pl

RAW_DIR = Path("data/raw")
RAW_DIR.mkdir(parents=True, exist_ok=True)
raw_path = RAW_DIR / "11100044-eng.zip"

# %% 1. Read inputs

with httpx.Client(
    http2=True,
    follow_redirects=True,
    timeout=300,
    headers={"User-Agent": "Mozilla/5.0 (compatible; research script)"},
) as client:
    response = client.get('https://www150.statcan.gc.ca/n1/tbl/csv/11100044-eng.zip')
response.raise_for_status()
raw_path.write_bytes(response.content)

# The ZIP may also hold a metadata file; read the data file.

with zipfile.ZipFile(raw_path) as archive:
    data_name = next(n for n in archive.namelist() if n.lower().endswith((".csv", ".txt")) and "metadata" not in n.lower())
    archive.extract(data_name, RAW_DIR)

data = pl.read_csv(RAW_DIR / data_name, separator=',', infer_schema_length=100_000)

# %% 2. Check inputs

assert data.height > 0, "https://www150.statcan.gc.ca/n1/tbl/csv/11100044-eng.zip returned no rows"

# %% 3. Prepare data

# VALUE is in the unit named by SCALAR_FACTOR; SCALAR_ID is its power of ten.

data = data.with_columns(
    (pl.col("VALUE") * 10 ** pl.col("SCALAR_ID").cast(pl.Int64)).alias(
        "VALUE_NORMALIZED"
    )
)

# Standard cleaning: snake_case names without accents (PÉRIODE -> periode,
# referenceNumber -> reference_number, as janitor does in R), trimmed text,
# empty strings as missing. Names that clean alike are numbered as janitor
# numbers them (Indicator, indicator -> indicator, indicator_2).

clean_names = [
    re.sub(
        r"[^0-9a-z]+",
        "_",
        re.sub(
            r"([a-z0-9])([A-Z])",
            r"\1_\2",
            unicodedata.normalize("NFKD", column).encode("ascii", "ignore").decode(),
        ).lower(),
    ).strip("_")
    for column in data.columns
]
while len(set(clean_names)) < len(clean_names):
    name_counts = {}
    numbered = []
    for name in clean_names:
        name_counts[name] = name_counts.get(name, 0) + 1
        count = name_counts[name]
        numbered.append(name if count == 1 else f"{name}_{count}")
    clean_names = numbered
data = data.rename(dict(zip(data.columns, clean_names)))
data = data.with_columns(pl.col(pl.Utf8).str.strip_chars().replace("", None))

Stata

* ============================================================
* StatCan table 11-10-0044-01
* Purpose: Fetch the data behind MapleStats MCP's wds_get_data_from_cube_coord
*          (exact: full table from the product id)
* Inputs:  https://www150.statcan.gc.ca/n1/tbl/csv/11100044-eng.zip
* Outputs: data/raw/11100044-eng.zip; the prepared table as `data`
* ============================================================

version 18
clear all
set more off

* 0. Setup

capture mkdir "logs"
capture log close
log using "logs/wds_get_data_from_cube_coord.log", replace
capture mkdir "data"
capture mkdir "data/raw"

* 1. Read inputs

* Stata reads no JSON or HTML and truncates long column names, so its
* built-in Python (Stata 16+) fetches, filters and writes a CSV. Point
* Stata at a Python with these packages first: python set exec <path>.

python:
import json
import zipfile
from pathlib import Path
import httpx
import polars as pl
RAW_DIR = Path("data/raw")
RAW_DIR.mkdir(parents=True, exist_ok=True)
raw_path = RAW_DIR / "11100044-eng.zip"
with httpx.Client(http2=True, follow_redirects=True, timeout=300, headers={"User-Agent": "Mozilla/5.0 (compatible; research script)"}) as client: response = client.get('https://www150.statcan.gc.ca/n1/tbl/csv/11100044-eng.zip')
response.raise_for_status()
raw_path.write_bytes(response.content)
with zipfile.ZipFile(raw_path) as archive: data_name = next(n for n in archive.namelist() if n.lower().endswith((".csv", ".txt")) and "metadata" not in n.lower()); archive.extract(data_name, RAW_DIR)
data = pl.read_csv(RAW_DIR / data_name, separator=',', infer_schema_length=100_000)
nested = [name for name, dtype in data.schema.items() if dtype.is_nested()]
data = data.with_columns(pl.col(name).map_elements(lambda value: json.dumps(value.to_list() if isinstance(value, pl.Series) else value, default=str), return_dtype=pl.Utf8) for name in nested)
data.write_csv(RAW_DIR / "11100044-eng_prepared.csv")
end

import delimited "data/raw/11100044-eng_prepared.csv", clear varnames(1) encoding("utf-8")

* 2. Check inputs

assert _N > 0

* 3. Prepare data

* value is in the unit named by scalar_factor; scalar_id is its power of ten.

generate double value_normalized = value * 10^scalar_id

* Standard cleaning: lower-case names, trimmed text, and numbers stored as
* text converted (destring leaves genuinely non-numeric text alone).
* rename *, lower stops at a clash (Indicator next to indicator), so names
* that lower-case alike are numbered as janitor numbers them (indicator,
* indicator_2), then renamed in one group rename, which allows swaps.

local names
foreach var of varlist _all {
    local names `names' `=strlower("`var'")'
}
local dups : list dups names
while "`dups'" != "" {
    local numbered
    local before
    foreach name of local names {
        local count 1
        foreach earlier of local before {
            if "`earlier'" == "`name'" local ++count
        }
        local before `before' `name'
        if `count' > 1 {
            local name = substr("`name'", 1, 32 - strlen("_`count'")) + "_`count'"
        }
        local numbered `numbered' `name'
    }
    local names `numbered'
    local dups : list dups names
}
local old_names
local new_names
local i 0
foreach var of varlist _all {
    local ++i
    local name : word `i' of `names'
    if "`name'" != "`var'" {
        local old_names `old_names' `var'
        local new_names `new_names' `name'
    }
}
if "`old_names'" != "" {
    rename (`old_names') (`new_names')
}
quietly ds, has(type string)
local text_vars `r(varlist)'
foreach var of local text_vars {
    replace `var' = strtrim(`var')
}
destring, replace

log close

Julia

# ============================================================
# StatCan table 11-10-0044-01
# Purpose: Fetch the data behind MapleStats MCP's wds_get_data_from_cube_coord
#          (exact: full table from the product id)
# Inputs:  https://www150.statcan.gc.ca/n1/tbl/csv/11100044-eng.zip
# Outputs: data/raw/11100044-eng.zip; the prepared table as `data`
# ============================================================

# 0. Setup

using Downloads
using TidierData
using TidierFiles
using ZipFile

mkpath("data/raw")

# 1. Read inputs

Downloads.download("https://www150.statcan.gc.ca/n1/tbl/csv/11100044-eng.zip", "data/raw/11100044-eng.zip")
archive = ZipFile.Reader("data/raw/11100044-eng.zip")

# The ZIP may also hold a metadata file; read the data file.

data_file = first(f for f in archive.files if (endswith(lowercase(f.name), ".csv") || endswith(lowercase(f.name), ".txt")) &&
    !occursin("metadata", lowercase(f.name)))
write("data/raw/table.csv", read(data_file))
close(archive)
data = read_csv("data/raw/table.csv")

# 2. Check inputs

@assert nrow(data) > 0 "https://www150.statcan.gc.ca/n1/tbl/csv/11100044-eng.zip returned no rows"

# 3. Prepare data

# VALUE is in the unit named by SCALAR_FACTOR; SCALAR_ID is its power of ten.

data.VALUE_NORMALIZED = data.VALUE .* 10.0 .^ data.SCALAR_ID

# Standard cleaning: snake_case names, trimmed text, empty strings as missing.

data = @chain data begin
    @clean_names
end
data = mapcols(
    col -> eltype(col) <: Union{Missing, AbstractString} ?
        [ismissing(x) || isempty(strip(x)) ? missing : strip(x) for x in col] : col,
    data,
)

R

# ============================================================
# Bank of Canada Valet: BD.CDN.2YR.DQ.YLD,BD.CDN.10YR.DQ.YLD
# Purpose: Fetch the data behind MapleStats MCP's boc_get_observations
#          (exact: Valet request rebuilt from the tool's arguments)
# Inputs:  https://www.bankofcanada.ca/valet/observations/BD.CDN.2YR.DQ.YLD,BD.CDN.10YR.DQ.YLD/json?start_date=2001-01-01
# Outputs: data/raw/valet_observations.json; the prepared table as `data`
# ============================================================

# 0. Setup ----

library(dplyr)
library(httr2)
library(janitor)
library(jsonlite)
library(lubridate)
library(readr)
library(stringr)
library(tibble)
library(tidyr)

dir.create("data/raw", recursive = TRUE, showWarnings = FALSE)

# 1. Read inputs ----

response <- request("https://www.bankofcanada.ca/valet/observations/BD.CDN.2YR.DQ.YLD,BD.CDN.10YR.DQ.YLD/json?start_date=2001-01-01") |>
  req_perform()

writeLines(resp_body_string(response), "data/raw/valet_observations.json")

payload <- fromJSON("data/raw/valet_observations.json", flatten = TRUE)
data <- as_tibble(payload[["observations"]])

# 2. Check inputs ----

stopifnot("https://www.bankofcanada.ca/valet/observations/BD.CDN.2YR.DQ.YLD,BD.CDN.10YR.DQ.YLD/json?start_date=2001-01-01 returned no rows" = nrow(data) > 0)

# 3. Prepare data ----

data <- data |>
  clean_names()

# Valet nests each series as <series>.v; make one row per date and series.

data <- data |>
  pivot_longer(-d, names_to = "series", values_to = "value") |>
  mutate(
    series = str_remove(series, "_v$") |> str_to_upper(),
    value = as.numeric(value),
    date = ymd(d)
  ) |>
  select(date, series, value)

# Standard cleaning: trimmed text, empty strings as missing, and numbers
# stored as text converted to numbers.

data <- data |>
  mutate(across(where(is.character), \(x) na_if(str_trim(x), ""))) |>
  type_convert()

Python

# ============================================================
# Bank of Canada Valet: BD.CDN.2YR.DQ.YLD,BD.CDN.10YR.DQ.YLD
# Purpose: Fetch the data behind MapleStats MCP's boc_get_observations
#          (exact: Valet request rebuilt from the tool's arguments)
# Inputs:  https://www.bankofcanada.ca/valet/observations/BD.CDN.2YR.DQ.YLD,BD.CDN.10YR.DQ.YLD/json?start_date=2001-01-01
# Outputs: data/raw/valet_observations.json; the prepared table as `data`
# ============================================================

# %% 0. Setup

import json
import re
import unicodedata
from pathlib import Path

import httpx
import polars as pl

RAW_DIR = Path("data/raw")
RAW_DIR.mkdir(parents=True, exist_ok=True)
raw_path = RAW_DIR / "valet_observations.json"

# %% 1. Read inputs

with httpx.Client(
    http2=True,
    follow_redirects=True,
    timeout=300,
    headers={"User-Agent": "Mozilla/5.0 (compatible; research script)"},
) as client:
    response = client.get('https://www.bankofcanada.ca/valet/observations/BD.CDN.2YR.DQ.YLD,BD.CDN.10YR.DQ.YLD/json?start_date=2001-01-01')
response.raise_for_status()
raw_path.write_bytes(response.content)

payload = json.loads(raw_path.read_text(encoding="utf-8"))
records = payload['observations']
data = pl.json_normalize(records, strict=False)

# %% 2. Check inputs

assert data.height > 0, "https://www.bankofcanada.ca/valet/observations/BD.CDN.2YR.DQ.YLD,BD.CDN.10YR.DQ.YLD/json?start_date=2001-01-01 returned no rows"

# %% 3. Prepare data

# Valet nests each series as <series>.v; make one row per date and series.

data = data.unpivot(index="d", variable_name="series", value_name="value")
data = data.with_columns(
    pl.col("d").str.to_date().alias("date"),
    pl.col("series").str.replace(r"\.v$", ""),
    pl.col("value").cast(pl.Float64, strict=False),
).select("date", "series", "value")

# Standard cleaning: snake_case names without accents (PÉRIODE -> periode,
# referenceNumber -> reference_number, as janitor does in R), trimmed text,
# empty strings as missing. Names that clean alike are numbered as janitor
# numbers them (Indicator, indicator -> indicator, indicator_2).

clean_names = [
    re.sub(
        r"[^0-9a-z]+",
        "_",
        re.sub(
            r"([a-z0-9])([A-Z])",
            r"\1_\2",
            unicodedata.normalize("NFKD", column).encode("ascii", "ignore").decode(),
        ).lower(),
    ).strip("_")
    for column in data.columns
]
while len(set(clean_names)) < len(clean_names):
    name_counts = {}
    numbered = []
    for name in clean_names:
        name_counts[name] = name_counts.get(name, 0) + 1
        count = name_counts[name]
        numbered.append(name if count == 1 else f"{name}_{count}")
    clean_names = numbered
data = data.rename(dict(zip(data.columns, clean_names)))
data = data.with_columns(pl.col(pl.Utf8).str.strip_chars().replace("", None))

Stata

* ============================================================
* Bank of Canada Valet: BD.CDN.2YR.DQ.YLD,BD.CDN.10YR.DQ.YLD
* Purpose: Fetch the data behind MapleStats MCP's boc_get_observations
*          (exact: Valet request rebuilt from the tool's arguments)
* Inputs:  https://www.bankofcanada.ca/valet/observations/BD.CDN.2YR.DQ.YLD,BD.CDN.10YR.DQ.YLD/json?start_date=2001-01-01
* Outputs: data/raw/valet_observations.json; the prepared table as `data`
* ============================================================

version 18
clear all
set more off

* 0. Setup

capture mkdir "logs"
capture log close
log using "logs/boc_get_observations.log", replace
capture mkdir "data"
capture mkdir "data/raw"

* 1. Read inputs

* Stata reads no JSON or HTML and truncates long column names, so its
* built-in Python (Stata 16+) fetches, filters and writes a CSV. Point
* Stata at a Python with these packages first: python set exec <path>.

python:
import json
from pathlib import Path
import httpx
import polars as pl
RAW_DIR = Path("data/raw")
RAW_DIR.mkdir(parents=True, exist_ok=True)
raw_path = RAW_DIR / "valet_observations.json"
with httpx.Client(http2=True, follow_redirects=True, timeout=300, headers={"User-Agent": "Mozilla/5.0 (compatible; research script)"}) as client: response = client.get('https://www.bankofcanada.ca/valet/observations/BD.CDN.2YR.DQ.YLD,BD.CDN.10YR.DQ.YLD/json?start_date=2001-01-01')
response.raise_for_status()
raw_path.write_bytes(response.content)
payload = json.loads(raw_path.read_text(encoding="utf-8"))
records = payload['observations']
data = pl.json_normalize(records, strict=False)
nested = [name for name, dtype in data.schema.items() if dtype.is_nested()]
data = data.with_columns(pl.col(name).map_elements(lambda value: json.dumps(value.to_list() if isinstance(value, pl.Series) else value, default=str), return_dtype=pl.Utf8) for name in nested)
data.write_csv(RAW_DIR / "valet_observations_prepared.csv")
end

import delimited "data/raw/valet_observations_prepared.csv", clear varnames(1) encoding("utf-8")

* 2. Check inputs

assert _N > 0

* 3. Prepare data

* One column per series (<series>_v), one row per date.

generate date = date(d, "YMD")
format date %td
drop d

* Standard cleaning: lower-case names, trimmed text, and numbers stored as
* text converted (destring leaves genuinely non-numeric text alone).
* rename *, lower stops at a clash (Indicator next to indicator), so names
* that lower-case alike are numbered as janitor numbers them (indicator,
* indicator_2), then renamed in one group rename, which allows swaps.

local names
foreach var of varlist _all {
    local names `names' `=strlower("`var'")'
}
local dups : list dups names
while "`dups'" != "" {
    local numbered
    local before
    foreach name of local names {
        local count 1
        foreach earlier of local before {
            if "`earlier'" == "`name'" local ++count
        }
        local before `before' `name'
        if `count' > 1 {
            local name = substr("`name'", 1, 32 - strlen("_`count'")) + "_`count'"
        }
        local numbered `numbered' `name'
    }
    local names `numbered'
    local dups : list dups names
}
local old_names
local new_names
local i 0
foreach var of varlist _all {
    local ++i
    local name : word `i' of `names'
    if "`name'" != "`var'" {
        local old_names `old_names' `var'
        local new_names `new_names' `name'
    }
}
if "`old_names'" != "" {
    rename (`old_names') (`new_names')
}
quietly ds, has(type string)
local text_vars `r(varlist)'
foreach var of local text_vars {
    replace `var' = strtrim(`var')
}
destring, replace

log close

Julia

# ============================================================
# Bank of Canada Valet: BD.CDN.2YR.DQ.YLD,BD.CDN.10YR.DQ.YLD
# Purpose: Fetch the data behind MapleStats MCP's boc_get_observations
#          (exact: Valet request rebuilt from the tool's arguments)
# Inputs:  https://www.bankofcanada.ca/valet/observations/BD.CDN.2YR.DQ.YLD,BD.CDN.10YR.DQ.YLD/json?start_date=2001-01-01
# Outputs: data/raw/valet_observations.json; the prepared table as `data`
# ============================================================

# 0. Setup

using DataFrames
using Downloads
using JSON3
using Tables
using TidierData

mkpath("data/raw")

# 1. Read inputs

Downloads.download("https://www.bankofcanada.ca/valet/observations/BD.CDN.2YR.DQ.YLD,BD.CDN.10YR.DQ.YLD/json?start_date=2001-01-01", "data/raw/valet_observations.json")
payload = JSON3.read(read("data/raw/valet_observations.json", String))
records = payload["observations"]
data = DataFrame(Tables.dictrowtable(records))

# 2. Check inputs

@assert nrow(data) > 0 "https://www.bankofcanada.ca/valet/observations/BD.CDN.2YR.DQ.YLD,BD.CDN.10YR.DQ.YLD/json?start_date=2001-01-01 returned no rows"

# 3. Prepare data

# Standard cleaning: snake_case names, trimmed text, empty strings as missing.

data = @chain data begin
    @clean_names
end
data = mapcols(
    col -> eltype(col) <: Union{Missing, AbstractString} ?
        [ismissing(x) || isempty(strip(x)) ? missing : strip(x) for x in col] : col,
    data,
)

R

# ============================================================
# Canadian patent search (CIPO IP Horizons)
# Purpose: Fetch the data behind MapleStats MCP's ised_ip_horizons_search_patents
#          (exact: the IP Horizons tables the search used, then the tool's filters)
# Inputs:  https://opic-cipo.ca/cipo/client_downloads/Patent_CSV_2024_10_11/PT_main_1_to_2000000_2024-10-11.zip
# Outputs: data/raw/PT_main_1_to_2000000_2024-10-11.zip; the prepared table as `data`
# ============================================================

# 0. Setup ----

library(dplyr)
library(janitor)
library(purrr)
library(readr)
library(stringr)

dir.create("data/raw", recursive = TRUE, showWarnings = FALSE)

# 1. Read inputs ----

files <- c(
  main_1 = "https://opic-cipo.ca/cipo/client_downloads/Patent_CSV_2024_10_11/PT_main_1_to_2000000_2024-10-11.zip",
  main_2 = "https://opic-cipo.ca/cipo/client_downloads/Patent_CSV_2024_10_11/PT_main_2000001_to_4000000_2024-10-11.zip"
)

walk2(
  files,
  names(files),
  \(url, name) download.file(url, str_c("data/raw/", name, ".zip"), mode = "wb")
)

# Every column as text; headers are bilingual ("Patent Number - Numéro du
# brevet"), so keep the English half in snake_case.

tables <- names(files) |>
  map(
    \(name) unzip(str_c("data/raw/", name, ".zip"), exdir = "data/raw") |>
      read_delim(
        delim = "|",
        quote = "",
        na = c("", "NULL"),
        col_types = cols(.default = col_character()),
        trim_ws = TRUE
      ) |>
      rename_with(\(x) str_split_i(x, " - ", 1) |> make_clean_names())
  ) |>
  set_names(names(files))

patents <- bind_rows(tables[c("main_1", "main_2")]) |>
  filter(str_detect(filing_date, "^\\d{4}-\\d{2}-\\d{2}$"), filing_date >= "1800-01-01")
data <- patents |>
  arrange(desc(filing_date))

# 2. Check inputs ----

stopifnot("https://opic-cipo.ca/cipo/client_downloads/Patent_CSV_2024_10_11/PT_main_1_to_2000000_2024-10-11.zip returned no rows" = nrow(data) > 0)

# 3. Prepare data ----

data <- data |>
  clean_names()

# Standard cleaning: trimmed text, empty strings as missing, and numbers
# stored as text converted to numbers.

data <- data |>
  mutate(across(where(is.character), \(x) na_if(str_trim(x), ""))) |>
  type_convert()

Python

# ============================================================
# Canadian patent search (CIPO IP Horizons)
# Purpose: Fetch the data behind MapleStats MCP's ised_ip_horizons_search_patents
#          (exact: the IP Horizons tables the search used, then the tool's filters)
# Inputs:  https://opic-cipo.ca/cipo/client_downloads/Patent_CSV_2024_10_11/PT_main_1_to_2000000_2024-10-11.zip
# Outputs: data/raw/PT_main_1_to_2000000_2024-10-11.zip; the prepared table as `data`
# ============================================================

# %% 0. Setup

import re
import ssl
import unicodedata
import zipfile
from pathlib import Path

import certifi
import httpx
import polars as pl

RAW_DIR = Path("data/raw")
RAW_DIR.mkdir(parents=True, exist_ok=True)
raw_path = RAW_DIR / "PT_main_1_to_2000000_2024-10-11.zip"

# %% 1. Read inputs

# CIPO's server omits its RapidSSL intermediate certificate; add it from the
# issuer on top of certifi's roots, so verification still anchors at a root.

intermediate = httpx.get("http://cacerts.rapidssl.com/RapidSSLTLSRSACAG1.crt").content
tls = ssl.create_default_context(cafile=certifi.where())
tls.load_verify_locations(cadata=ssl.DER_cert_to_PEM_cert(intermediate))

files = {
    'main_1': 'https://opic-cipo.ca/cipo/client_downloads/Patent_CSV_2024_10_11/PT_main_1_to_2000000_2024-10-11.zip',
    'main_2': 'https://opic-cipo.ca/cipo/client_downloads/Patent_CSV_2024_10_11/PT_main_2000001_to_4000000_2024-10-11.zip',
}
tables = {}
with httpx.Client(verify=tls, timeout=900, follow_redirects=True) as client:
    for name, url in files.items():
        zip_path = RAW_DIR / f"{name}.zip"
        with client.stream("GET", url) as response, zip_path.open("wb") as handle:
            response.raise_for_status()
            for chunk in response.iter_bytes(1 << 20):
                handle.write(chunk)
        with zipfile.ZipFile(zip_path) as archive:
            member = archive.namelist()[0]
            archive.extract(member, RAW_DIR)

        # Every column as text; headers are bilingual ("Patent Number - Numéro
        # du brevet"), so keep the English half in snake_case.

        tables[name] = (
            pl.scan_csv(
                RAW_DIR / member,
                separator="|",
                quote_char=None,
                null_values=["NULL"],
                infer_schema=False,
            )
            .rename(
                lambda column: re.sub(
                    r"[^a-z0-9]+", "_", column.split(" - ")[0].strip().lower()
                ).strip("_")
            )
            .with_columns(pl.all().str.strip_chars())
        )

patents = pl.concat([tables[name] for name in ['main_1', 'main_2']]).filter(pl.col("filing_date").str.contains(r"^\d{4}-\d{2}-\d{2}$"), pl.col("filing_date") >= "1800-01-01")
data = patents.sort("filing_date", descending=True, nulls_last=True).collect()

# %% 2. Check inputs

assert data.height > 0, "https://opic-cipo.ca/cipo/client_downloads/Patent_CSV_2024_10_11/PT_main_1_to_2000000_2024-10-11.zip returned no rows"

# %% 3. Prepare data

# Standard cleaning: snake_case names without accents (PÉRIODE -> periode,
# referenceNumber -> reference_number, as janitor does in R), trimmed text,
# empty strings as missing. Names that clean alike are numbered as janitor
# numbers them (Indicator, indicator -> indicator, indicator_2).

clean_names = [
    re.sub(
        r"[^0-9a-z]+",
        "_",
        re.sub(
            r"([a-z0-9])([A-Z])",
            r"\1_\2",
            unicodedata.normalize("NFKD", column).encode("ascii", "ignore").decode(),
        ).lower(),
    ).strip("_")
    for column in data.columns
]
while len(set(clean_names)) < len(clean_names):
    name_counts = {}
    numbered = []
    for name in clean_names:
        name_counts[name] = name_counts.get(name, 0) + 1
        count = name_counts[name]
        numbered.append(name if count == 1 else f"{name}_{count}")
    clean_names = numbered
data = data.rename(dict(zip(data.columns, clean_names)))
data = data.with_columns(pl.col(pl.Utf8).str.strip_chars().replace("", None))

Stata

* ============================================================
* Canadian patent search (CIPO IP Horizons)
* Purpose: Fetch the data behind MapleStats MCP's ised_ip_horizons_search_patents
*          (exact: the IP Horizons tables the search used, then the tool's filters)
* Inputs:  https://opic-cipo.ca/cipo/client_downloads/Patent_CSV_2024_10_11/PT_main_1_to_2000000_2024-10-11.zip
* Outputs: data/raw/PT_main_1_to_2000000_2024-10-11.zip; the prepared table as `data`
* ============================================================

version 18
clear all
set more off

* 0. Setup

capture mkdir "logs"
capture log close
log using "logs/ised_ip_horizons_search_patents.log", replace
capture mkdir "data"
capture mkdir "data/raw"

* 1. Read inputs

* Stata reads no JSON or HTML and truncates long column names, so its
* built-in Python (Stata 16+) fetches, filters and writes a CSV. Point
* Stata at a Python with these packages first: python set exec <path>.

python:
import json
import re
import ssl
import zipfile
from pathlib import Path
import certifi
import httpx
import polars as pl
RAW_DIR = Path("data/raw")
RAW_DIR.mkdir(parents=True, exist_ok=True)
raw_path = RAW_DIR / "PT_main_1_to_2000000_2024-10-11.zip"
intermediate = httpx.get("http://cacerts.rapidssl.com/RapidSSLTLSRSACAG1.crt").content
tls = ssl.create_default_context(cafile=certifi.where())
tls.load_verify_locations(cadata=ssl.DER_cert_to_PEM_cert(intermediate))
files = {'main_1': 'https://opic-cipo.ca/cipo/client_downloads/Patent_CSV_2024_10_11/PT_main_1_to_2000000_2024-10-11.zip', 'main_2': 'https://opic-cipo.ca/cipo/client_downloads/Patent_CSV_2024_10_11/PT_main_2000001_to_4000000_2024-10-11.zip'}
tables = {}
exec('with httpx.Client(verify=tls, timeout=900, follow_redirects=True) as client:\n    for name, url in files.items():\n        zip_path = RAW_DIR / f"{name}.zip"\n        with client.stream("GET", url) as response, zip_path.open("wb") as handle:\n            response.raise_for_status()\n            for chunk in response.iter_bytes(1 << 20):\n                handle.write(chunk)\n        with zipfile.ZipFile(zip_path) as archive:\n            member = archive.namelist()[0]\n            archive.extract(member, RAW_DIR)\n        tables[name] = (pl.scan_csv(RAW_DIR / member, separator="|", quote_char=None, null_values=["NULL"], infer_schema=False) .rename(lambda column: re.sub(r"[^a-z0-9]+", "_", column.split(" - ")[0].strip().lower()).strip("_")) .with_columns(pl.all().str.strip_chars()))\n')
patents = pl.concat([tables[name] for name in ['main_1', 'main_2']]).filter(pl.col("filing_date").str.contains('^\\d{4}-\\d{2}-\\d{2}\x24'), pl.col("filing_date") >= "1800-01-01")
data = patents.sort("filing_date", descending=True, nulls_last=True).collect()
nested = [name for name, dtype in data.schema.items() if dtype.is_nested()]
data = data.with_columns(pl.col(name).map_elements(lambda value: json.dumps(value.to_list() if isinstance(value, pl.Series) else value, default=str), return_dtype=pl.Utf8) for name in nested)
data.write_csv(RAW_DIR / "PT_main_1_to_2000000_202_prepared.csv")
end

import delimited "data/raw/PT_main_1_to_2000000_202_prepared.csv", clear varnames(1) encoding("utf-8")

* 2. Check inputs

assert _N > 0

* 3. Prepare data

* Standard cleaning: lower-case names, trimmed text, and numbers stored as
* text converted (destring leaves genuinely non-numeric text alone).
* rename *, lower stops at a clash (Indicator next to indicator), so names
* that lower-case alike are numbered as janitor numbers them (indicator,
* indicator_2), then renamed in one group rename, which allows swaps.

local names
foreach var of varlist _all {
    local names `names' `=strlower("`var'")'
}
local dups : list dups names
while "`dups'" != "" {
    local numbered
    local before
    foreach name of local names {
        local count 1
        foreach earlier of local before {
            if "`earlier'" == "`name'" local ++count
        }
        local before `before' `name'
        if `count' > 1 {
            local name = substr("`name'", 1, 32 - strlen("_`count'")) + "_`count'"
        }
        local numbered `numbered' `name'
    }
    local names `numbered'
    local dups : list dups names
}
local old_names
local new_names
local i 0
foreach var of varlist _all {
    local ++i
    local name : word `i' of `names'
    if "`name'" != "`var'" {
        local old_names `old_names' `var'
        local new_names `new_names' `name'
    }
}
if "`old_names'" != "" {
    rename (`old_names') (`new_names')
}
quietly ds, has(type string)
local text_vars `r(varlist)'
foreach var of local text_vars {
    replace `var' = strtrim(`var')
}
destring, replace

log close

Julia

# ============================================================
# Canadian patent search (CIPO IP Horizons)
# Purpose: Fetch the data behind MapleStats MCP's ised_ip_horizons_search_patents
#          (exact: the IP Horizons tables the search used, then the tool's filters)
# Inputs:  https://opic-cipo.ca/cipo/client_downloads/Patent_CSV_2024_10_11/PT_main_1_to_2000000_2024-10-11.zip
# Outputs: data/raw/PT_main_1_to_2000000_2024-10-11.zip; the prepared table as `data`
# ============================================================

# 0. Setup

using CSV
using DataFrames
using Downloads
using TidierData
using ZipFile

mkpath("data/raw")

# 1. Read inputs

files = Dict(
    "main_1" => "https://opic-cipo.ca/cipo/client_downloads/Patent_CSV_2024_10_11/PT_main_1_to_2000000_2024-10-11.zip",
    "main_2" => "https://opic-cipo.ca/cipo/client_downloads/Patent_CSV_2024_10_11/PT_main_2000001_to_4000000_2024-10-11.zip",
)
tables = Dict{String, DataFrame}()
for (name, url) in files
    zip_path = "data/raw/$(name).zip"
    Downloads.download(url, zip_path)
    archive = ZipFile.Reader(zip_path)
    member = archive.files[1]
    csv_path = "data/raw/$(member.name)"
    write(csv_path, read(member))
    close(archive)

    # Every column as text; keep the English half of each bilingual header.

    table = CSV.read(csv_path, DataFrame; delim = '|', quoted = false, missingstring = "NULL", types = String)
    rename!(table, [lowercase(replace(strip(split(n, " - ")[1]), r"[^A-Za-z0-9]+" => "_")) for n in names(table)])
    tables[name] = mapcols(col -> [ismissing(x) ? missing : strip(x) for x in col], table)
end

patents = vcat([tables[n] for n in ["main_1", "main_2"]]...)
patents = filter(row -> (!ismissing(row.filing_date) && occursin(r"^\d{4}-\d{2}-\d{2}$", row.filing_date) && row.filing_date >= "1800-01-01"), patents)
data = sort(patents, "filing_date"; rev = true)

# 2. Check inputs

@assert nrow(data) > 0 "https://opic-cipo.ca/cipo/client_downloads/Patent_CSV_2024_10_11/PT_main_1_to_2000000_2024-10-11.zip returned no rows"

# 3. Prepare data

# Standard cleaning: snake_case names, trimmed text, empty strings as missing.

data = @chain data begin
    @clean_names
end
data = mapcols(
    col -> eltype(col) <: Union{Missing, AbstractString} ?
        [ismissing(x) || isempty(strip(x)) ? missing : strip(x) for x in col] : col,
    data,
)

R

# ============================================================
# Bank of Canada Valet: V39079
# Purpose: Fetch the data behind MapleStats MCP's boc_get_observations
#          (exact: Valet request rebuilt from the tool's arguments)
# Inputs:  https://www.bankofcanada.ca/valet/observations/V39079/json?start_date=2015-01-01
# Outputs: data/raw/valet_observations.json; the prepared table as `data`
# ============================================================

# 0. Setup ----

library(dplyr)
library(httr2)
library(janitor)
library(jsonlite)
library(lubridate)
library(purrr)
library(readr)
library(stringr)
library(tibble)
library(tidyr)

dir.create("data/raw", recursive = TRUE, showWarnings = FALSE)

# 1. Read inputs ----

response <- request("https://www.bankofcanada.ca/valet/observations/V39079/json?start_date=2015-01-01") |>
  req_perform()

writeLines(resp_body_string(response), "data/raw/valet_observations.json")

payload <- fromJSON("data/raw/valet_observations.json", flatten = TRUE)
data <- as_tibble(payload[["observations"]])

# 2. Check inputs ----

stopifnot("https://www.bankofcanada.ca/valet/observations/V39079/json?start_date=2015-01-01 returned no rows" = nrow(data) > 0)

# 3. Prepare data ----

data <- data |>
  clean_names()

# Valet nests each series as <series>.v; make one row per date and series.

data <- data |>
  pivot_longer(-d, names_to = "series", values_to = "value") |>
  mutate(
    series = str_remove(series, "_v$") |> str_to_upper(),
    value = as.numeric(value),
    date = ymd(d)
  ) |>
  select(date, series, value)

# Standard cleaning: trimmed text, empty strings as missing, and numbers
# stored as text converted to numbers. Codes stay text as the source
# wrote them: columns named like one (coordinate, vector_id, dguid, NAICS,
# postal code) and columns with a leading zero (01, 000000001).

code_columns <- "^(coordinate|vector|vector_?id|product_?id|pid|dguid|[a-z0-9_]*_dguid|postal_?code|[a-z0-9_]*_postal_code|fsa|[a-z0-9_]*naics[a-z0-9_]*|noc|noc_[a-z0-9_]*|[a-z0-9_]*_noc|sgc|sgc_[a-z0-9_]*|[a-z0-9_]*_sgc)$"
data <- data |>
  mutate(across(where(is.character), \(x) na_if(str_trim(x), "")))
keep_text <- names(data)[
  str_detect(names(data), code_columns) |
    map_lgl(
      data,
      \(column) is.character(column) && any(str_detect(column, "^-?0[0-9]"), na.rm = TRUE)
    )
]
data <- data |>
  mutate(
    across(any_of(keep_text), \(x) if (is.list(x)) x else as.character(x)),
    across(where(is.character) & !any_of(keep_text), parse_guess)
  )

Python

# ============================================================
# Bank of Canada Valet: V39079
# Purpose: Fetch the data behind MapleStats MCP's boc_get_observations
#          (exact: Valet request rebuilt from the tool's arguments)
# Inputs:  https://www.bankofcanada.ca/valet/observations/V39079/json?start_date=2015-01-01
# Outputs: data/raw/valet_observations.json; the prepared table as `data`
# ============================================================

# %% 0. Setup

import json
import re
import unicodedata
from pathlib import Path

import httpx
import polars as pl

RAW_DIR = Path("data/raw")
RAW_DIR.mkdir(parents=True, exist_ok=True)
raw_path = RAW_DIR / "valet_observations.json"

# %% 1. Read inputs

with httpx.Client(
    http2=True,
    follow_redirects=True,
    timeout=300,
    headers={"User-Agent": "Mozilla/5.0 (compatible; research script)"},
) as client:
    response = client.get('https://www.bankofcanada.ca/valet/observations/V39079/json?start_date=2015-01-01')
response.raise_for_status()
raw_path.write_bytes(response.content)

payload = json.loads(raw_path.read_text(encoding="utf-8"))
records = payload['observations']
data = pl.json_normalize(records, strict=False)

# %% 2. Check inputs

assert data.height > 0, "https://www.bankofcanada.ca/valet/observations/V39079/json?start_date=2015-01-01 returned no rows"

# %% 3. Prepare data

# Valet nests each series as <series>.v; make one row per date and series.

data = data.unpivot(index="d", variable_name="series", value_name="value")
data = data.with_columns(
    pl.col("d").str.to_date().alias("date"),
    pl.col("series").str.replace(r"\.v$", ""),
    pl.col("value").cast(pl.Float64, strict=False),
).select("date", "series", "value")

# Standard cleaning: snake_case names as janitor's clean_names() gives them
# in R (PÉRIODE -> periode, referenceNumber -> reference_number, # -> number,
# % -> percent, apostrophes dropped, a leading digit prefixed with x),
# trimmed text, empty strings as missing. Names that clean alike are
# numbered as janitor numbers them (Indicator, indicator -> indicator,
# indicator_2).

clean_names = [
    re.sub(
        r"[^0-9a-z]+",
        "_",
        re.sub(
            r"([a-z0-9])([A-Z])",
            r"\1_\2",
            "".join(
                " " if unicodedata.category(ch)[0] in "PS" and not ch.isascii() else ch
                for ch in unicodedata.normalize("NFKD", column.replace("'", ""))
            )
            .replace("#", " number ")
            .replace("%", " percent ")
            .encode("ascii", "ignore")
            .decode(),
        ).lower(),
    ).strip("_")
    or "x"
    for column in data.columns
]
clean_names = [f"x{name}" if name[0].isdigit() else name for name in clean_names]
while len(set(clean_names)) < len(clean_names):
    name_counts = {}
    numbered = []
    for name in clean_names:
        name_counts[name] = name_counts.get(name, 0) + 1
        count = name_counts[name]
        numbered.append(name if count == 1 else f"{name}_{count}")
    clean_names = numbered
data = data.rename(dict(zip(data.columns, clean_names)))
data = data.with_columns(pl.col(pl.Utf8).str.strip_chars().replace("", None))

# Numbers stored as text become numbers. Codes stay text as the source
# wrote them: columns named like one (coordinate, vector_id, dguid, NAICS,
# postal code) and columns with a leading zero (01, 000000001).

code_columns = re.compile(r"^(coordinate|vector|vector_?id|product_?id|pid|dguid|[a-z0-9_]*_dguid|postal_?code|[a-z0-9_]*_postal_code|fsa|[a-z0-9_]*naics[a-z0-9_]*|noc|noc_[a-z0-9_]*|[a-z0-9_]*_noc|sgc|sgc_[a-z0-9_]*|[a-z0-9_]*_sgc)$")
for column in data.columns:
    dtype = data.schema[column]
    if code_columns.search(column) and not dtype.is_nested():
        data = data.with_columns(pl.col(column).cast(pl.Utf8))
        continue
    if dtype != pl.Utf8 or data[column].null_count() == data.height:
        continue
    numbers = data[column].cast(pl.Float64, strict=False)
    leading_zero = data[column].str.contains(r"^-?0[0-9]").any()
    if numbers.null_count() > data[column].null_count() or leading_zero:
        continue
    whole = data[column].cast(pl.Int64, strict=False)
    data = data.with_columns(whole if whole.null_count() == numbers.null_count() else numbers)

Stata

* ============================================================
* Bank of Canada Valet: V39079
* Purpose: Fetch the data behind MapleStats MCP's boc_get_observations
*          (exact: Valet request rebuilt from the tool's arguments)
* Inputs:  https://www.bankofcanada.ca/valet/observations/V39079/json?start_date=2015-01-01
* Outputs: data/raw/valet_observations.json; the prepared table as `data`
* ============================================================

version 18
clear all
set more off

* 0. Setup

capture mkdir "logs"
capture log close
log using "logs/boc_get_observations.log", replace
capture mkdir "data"
capture mkdir "data/raw"

* 1. Read inputs

* Stata reads no JSON or HTML and truncates long column names, so its
* built-in Python (Stata 16+) fetches, filters and writes a CSV. Point
* Stata at a Python with these packages first: python set exec <path>.

python:
import json
from pathlib import Path
import httpx
import polars as pl
RAW_DIR = Path("data/raw")
RAW_DIR.mkdir(parents=True, exist_ok=True)
raw_path = RAW_DIR / "valet_observations.json"
with httpx.Client(http2=True, follow_redirects=True, timeout=300, headers={"User-Agent": "Mozilla/5.0 (compatible; research script)"}) as client: response = client.get('https://www.bankofcanada.ca/valet/observations/V39079/json?start_date=2015-01-01')
response.raise_for_status()
raw_path.write_bytes(response.content)
payload = json.loads(raw_path.read_text(encoding="utf-8"))
records = payload['observations']
data = pl.json_normalize(records, strict=False)
nested = [name for name, dtype in data.schema.items() if dtype.is_nested()]
data = data.with_columns(pl.col(name).map_elements(lambda value: json.dumps(value.to_list() if isinstance(value, pl.Series) else value, default=str), return_dtype=pl.Utf8) for name in nested)
data.write_csv(RAW_DIR / "valet_observations_prepared.csv")
end

import delimited "data/raw/valet_observations_prepared.csv", clear varnames(1) encoding("utf-8") stringcols(_all) bindquote(strict) maxquotedrows(unlimited)

* 2. Check inputs

assert _N > 0

* 3. Prepare data

* One column per series (<series>_v), one row per date.

generate date = date(d, "YMD")
format date %td
drop d

* Standard cleaning: lower-case names, trimmed text, and numbers stored as
* text converted (destring leaves genuinely non-numeric text alone). Codes
* stay text as the source wrote them: variables named like one (coordinate,
* vectorid, dguid, NAICS, postal code) and ones with a leading zero (01).
* rename *, lower stops at a clash (Indicator next to indicator), so names
* that lower-case alike are numbered as janitor numbers them (indicator,
* indicator_2), then renamed in one group rename, which allows swaps.

local names
foreach var of varlist _all {
    local names `names' `=strlower("`var'")'
}
local dups : list dups names
while "`dups'" != "" {
    local numbered
    local before
    foreach name of local names {
        local count 1
        foreach earlier of local before {
            if "`earlier'" == "`name'" local ++count
        }
        local before `before' `name'
        if `count' > 1 {
            local name = substr("`name'", 1, 32 - strlen("_`count'")) + "_`count'"
        }
        local numbered `numbered' `name'
    }
    local names `numbered'
    local dups : list dups names
}
local old_names
local new_names
local i 0
foreach var of varlist _all {
    local ++i
    local name : word `i' of `names'
    if "`name'" != "`var'" {
        local old_names `old_names' `var'
        local new_names `new_names' `name'
    }
}
if "`old_names'" != "" {
    rename (`old_names') (`new_names')
}
quietly ds, has(type string)
local text_vars `r(varlist)'
foreach var of local text_vars {
    replace `var' = strtrim(`var')
    if regexm("`var'|", "^(coordinate|vector|vector_?id|product_?id|pid|dguid|[a-z0-9_]*_dguid|postal_?code|[a-z0-9_]*_postal_code|fsa|[a-z0-9_]*naics[a-z0-9_]*|noc|noc_[a-z0-9_]*|[a-z0-9_]*_noc|sgc|sgc_[a-z0-9_]*|[a-z0-9_]*_sgc)[|]") continue
    quietly count if regexm(`var', "^-?0[0-9]")
    if r(N) > 0 continue
    destring `var', replace
}

log close

Julia

# ============================================================
# Bank of Canada Valet: V39079
# Purpose: Fetch the data behind MapleStats MCP's boc_get_observations
#          (exact: Valet request rebuilt from the tool's arguments)
# Inputs:  https://www.bankofcanada.ca/valet/observations/V39079/json?start_date=2015-01-01
# Outputs: data/raw/valet_observations.json; the prepared table as `data`
# ============================================================

# 0. Setup

using DataFrames
using Dates
using Downloads
using JSON3
using Tables
using TidierData

mkpath("data/raw")

# 1. Read inputs

Downloads.download("https://www.bankofcanada.ca/valet/observations/V39079/json?start_date=2015-01-01", "data/raw/valet_observations.json")
payload = JSON3.read(read("data/raw/valet_observations.json", String))
records = payload["observations"]
data = DataFrame(Tables.dictrowtable(records))
data = mapcols(col -> [x === nothing ? missing : x for x in col], data)

# 2. Check inputs

@assert nrow(data) > 0 "https://www.bankofcanada.ca/valet/observations/V39079/json?start_date=2015-01-01 returned no rows"

# 3. Prepare data

# Valet nests each series as {v: value}; make one row per date and series.

data = stack(data, Not("d"); variable_name = "series", value_name = "cell")
data.value = [
    ismissing(cell) ? missing :
        something(tryparse(Float64, string(get(cell, :v, ""))), missing)
    for cell in data.cell
]
data.date = Date.(data.d)
data = select(data, "date", "series", "value")

# Standard cleaning: snake_case names, trimmed text, empty strings as missing.

data = @chain data begin
    @clean_names
end
data = mapcols(
    col -> eltype(col) <: Union{Missing, AbstractString} ?
        [ismissing(x) || isempty(strip(x)) ? missing : strip(x) for x in col] : col,
    data,
)

# Numbers stored as text become numbers. Codes stay text as the source
# wrote them: columns named like one (coordinate, vector_id, dguid, NAICS,
# postal code) and columns with a leading zero (01, 000000001).

code_columns = r"^(coordinate|vector|vector_?id|product_?id|pid|dguid|[a-z0-9_]*_dguid|postal_?code|[a-z0-9_]*_postal_code|fsa|[a-z0-9_]*naics[a-z0-9_]*|noc|noc_[a-z0-9_]*|[a-z0-9_]*_noc|sgc|sgc_[a-z0-9_]*|[a-z0-9_]*_sgc)$"
for name in names(data)
    column = data[!, name]
    if occursin(code_columns, name)
        data[!, name] = [ismissing(x) ? missing : string(x) for x in column]
        continue
    end
    eltype(column) <: Union{Missing, AbstractString} || continue
    values = collect(skipmissing(column))
    (isempty(values) || any(v -> occursin(r"^-?0[0-9]", v), values)) && continue
    any(v -> isnothing(tryparse(Float64, v)), values) && continue
    whole = all(v -> !isnothing(tryparse(Int, v)), values)
    data[!, name] = [
        ismissing(x) ? missing : parse(whole ? Int : Float64, x) for x in column
    ]
end

Excel (Power Query)

// ============================================================
// Bank of Canada Valet: V39079
// Purpose: Fetch the data behind MapleStats MCP's boc_get_observations
//          (exact: Valet request rebuilt from the tool's arguments)
// Inputs:  https://www.bankofcanada.ca/valet/observations/V39079/json?start_date=2015-01-01
// Outputs: the prepared table, loaded to a worksheet or the Data Model
// How to run: Excel > Data > Get Data > From Other Sources > Blank Query >
//   Advanced Editor; paste this query, Done, then Close & Load. Answer the
//   first prompt with Anonymous access; Data > Refresh All fetches it again.
//   A worksheet holds 1,048,576 rows: load a larger table (a full StatCan
//   table can be) with Close & Load To > Only Create Connection > Add this
//   data to the Data Model.
// ============================================================
let
    // 0. Setup
    // Cell values as text or number, null-safe, for the filters and checks.
    AsText = (value as any) as text =>
        if value = null then "" else (try Text.From(value) otherwise ""),
    AsNumber = (value as any) as nullable number =>
        if value = null then null else (try Number.From(value, "en-US") otherwise null),
    // Records to a table with every field any record has, not only the first's.
    ToTable = (records as list) as table =>
        Table.FromRecords(
            records,
            List.Distinct(List.Combine(List.Transform(records, Record.FieldNames))),
            MissingField.UseNull
        ),
    // 1. Read inputs
    Raw = Web.Contents("https://www.bankofcanada.ca/valet/observations/V39079/json?start_date=2015-01-01"),
    Payload = Json.Document(Raw),
    Loaded = ToTable(Record.Field(Payload, "observations")),
    // 2. Check inputs
    // Stop with a clear message instead of loading an empty table.
    Checked = if Table.IsEmpty(Loaded)
        then error Error.Record("MapleStats", "https://www.bankofcanada.ca/valet/observations/V39079/json?start_date=2015-01-01 returned no rows")
        else Loaded,
    // 3. Prepare data
    // Valet nests each series as <series>[v]; make one row per date and series.
    Long = Table.UnpivotOtherColumns(Checked, {"d"}, "series", "cell"),
    Valued = Table.AddColumn(
        Long, "value", each try Number.From([cell][v], "en-US") otherwise null, type number
    ),
    Tidy = Table.RenameColumns(Table.SelectColumns(Valued, {"d", "series", "value"}), {{"d", "date"}}),
    // Standard cleaning, as the R script's clean_names() and type_convert():
    // snake_case names without accents (PÉRIODE -> periode, referenceNumber ->
    // reference_number), names that clean alike numbered (indicator,
    // indicator_2), trimmed text, empty text as null, then columns whose
    // every value is a number or an ISO date (YYYY-MM-DD) converted.
    Accents = [#"à" = "a", #"â" = "a", #"ä" = "a", #"á" = "a", #"ç" = "c", #"é" = "e", #"è" = "e", #"ê" = "e", #"ë" = "e", #"î" = "i", #"ï" = "i", #"í" = "i", #"ô" = "o", #"ö" = "o", #"ó" = "o", #"ù" = "u", #"û" = "u", #"ü" = "u", #"ú" = "u", #"ÿ" = "y", #"ñ" = "n", #"œ" = "oe", #"æ" = "ae", #"À" = "A", #"Â" = "A", #"Ä" = "A", #"Á" = "A", #"Ç" = "C", #"É" = "E", #"È" = "E", #"Ê" = "E", #"Ë" = "E", #"Î" = "I", #"Ï" = "I", #"Í" = "I", #"Ô" = "O", #"Ö" = "O", #"Ó" = "O", #"Ù" = "U", #"Û" = "U", #"Ü" = "U", #"Ú" = "U", #"Ÿ" = "Y", #"Ñ" = "N", #"Œ" = "OE", #"Æ" = "AE"],
    CleanName = (name as text) as text =>
        let
            Plain = Text.Combine(
                List.Transform(Text.ToList(name), (ch) => Record.FieldOrDefault(Accents, ch, ch))
            ),
            Words = Splitter.SplitTextByCharacterTransition({"a".."z", "0".."9"}, {"A".."Z"})(
                Plain
            ),
            Lower = Text.Lower(Text.Combine(Words, "_")),
            Spaced = Text.Combine(
                List.Transform(
                    Text.ToList(Lower), (ch) => if List.Contains(NameCharacters, ch) then ch else " "
                )
            ),
            Snake = Text.Combine(List.Select(Text.Split(Spaced, " "), (part) => part <> ""), "_")
        in
            if Snake = "" then "x" else Snake,
    NameCharacters = {"a".."z", "0".."9"},
    OldNames = Table.ColumnNames(Tidy),
    CleanNames = List.Transform(OldNames, CleanName),
    NewNames = List.Transform(
        List.Positions(CleanNames),
        (i) =>
            let
                Earlier = List.Count(
                    List.Select(List.FirstN(CleanNames, i), (name) => name = CleanNames{i})
                )
            in
                if Earlier = 0 then CleanNames{i} else CleanNames{i} & "_" & Text.From(Earlier + 1)
    ),
    Renamed = Table.RenameColumns(
        Tidy,
        List.Select(List.Zip({OldNames, NewNames}), (pair) => pair{0} <> pair{1})
    ),
    Trimmed = Table.TransformColumns(
        Renamed,
        List.Transform(
            Table.ColumnNames(Renamed),
            (name) =>
                {
                    name,
                    (value) =>
                        if value is text
                        then (if Text.Trim(value) = "" then null else Text.Trim(value))
                        else value
                }
        )
    ),
    IsNumberText = (value as any) as logical =>
        value is number
        or (value is text and (try Number.FromText(value, "en-US") otherwise null) <> null),
    IsIsoDate = (value as any) as logical =>
        value is text
        and Text.Length(value) = 10
        and Text.Middle(value, 4, 1) = "-"
        and Text.Middle(value, 7, 1) = "-"
        and (try Date.FromText(value, "en-US") otherwise null) <> null,
    ColumnsWhere = (table as table, test as function) as list =>
        List.Select(
            Table.ColumnNames(table),
            (name) =>
                let
                    Values = List.RemoveNulls(Table.Column(table, name))
                in
                    not List.IsEmpty(Values) and List.AllTrue(List.Transform(Values, test))
        ),
    // Codes stay text as the source wrote them: columns named like one
    // (coordinate, vector_id, dguid, NAICS, postal code; StatCan's "2.1" and
    // "2.10" are different coordinates) and columns with a leading zero (01).
    CodeName = (name as text) as logical =>
        List.Contains(
            {"coordinate", "vector", "vector_id", "vectorid", "product_id", "productid", "pid",
              "dguid", "postal_code", "postalcode", "fsa", "noc", "sgc"},
            name
        )
        or Text.EndsWith(name, "_dguid")
        or Text.EndsWith(name, "_postal_code")
        or Text.Contains(name, "naics")
        or Text.StartsWith(name, "noc_")
        or Text.EndsWith(name, "_noc")
        or Text.StartsWith(name, "sgc_")
        or Text.EndsWith(name, "_sgc"),
    HasLeadingZero = (value as any) as logical =>
        value is text
        and (
            let
                Digits = if Text.StartsWith(value, "-") then Text.Middle(value, 1) else value
            in
                Text.Length(Digits) > 1
                and Text.Start(Digits, 1) = "0"
                and List.Contains({"0".."9"}, Text.Middle(Digits, 1, 1))
        ),
    CodeColumns = List.Select(
        Table.ColumnNames(Trimmed),
        (name) => CodeName(name) or List.AnyTrue(List.Transform(Table.Column(Trimmed, name), HasLeadingZero))
    ),
    Codes = Table.TransformColumns(
        Trimmed,
        List.Transform(
            CodeColumns,
            (name) =>
                {
                    name,
                    (value) =>
                        if value = null then null
                        else if value is number then Number.ToText(value, "G", "en-US")
                        else Text.From(value),
                    type text
                }
        )
    ),
    NumberColumns = List.Difference(ColumnsWhere(Codes, IsNumberText), CodeColumns),
    Numbers = Table.TransformColumnTypes(
        Codes, List.Transform(NumberColumns, (name) => {name, type number}), "en-US"
    ),
    DateColumns = ColumnsWhere(Numbers, IsIsoDate),
    Cleaned = Table.TransformColumns(
        Numbers,
        List.Transform(
            DateColumns,
            (name) => {name, (value) => if value = null then null else Date.FromText(value, "en-US"), type date}
        )
    ),
    Data = Cleaned
in
    Data