Eight 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(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", "--"), show_col_types = FALSE)

# 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",
    `EN_YEAR` >= 2015
  )

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

# ============================================================
# 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_length=100_000, 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 without accents (PÉRIODE -> periode,
# referenceNumber -> reference_number, as janitor does in R), trimmed text,
# empty strings as missing.

data = data.rename(
    {
        column: 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
    }
)
data = data.with_columns(pl.col(pl.Utf8).str.strip_chars().replace("", None))

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_length=100_000, null_values=['--'])
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")

* 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
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

# ============================================================
# 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 DataFrames
using Downloads
using TidierData
using TidierFiles

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 = read_csv("data/raw/ODP-PR-PT_CMA.csv", delim = "\t", 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"]) && row["EN_YEAR"] >= 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,
)

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.txt; the downloaded file
# ============================================================

# 0. Setup ----

library(httr2)

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.txt")

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.txt; the downloaded file
# ============================================================

# %% 0. Setup

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.txt"

# %% 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)

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.txt; the downloaded file
* ============================================================

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 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.txt"
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)
end

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.txt; the downloaded file
# ============================================================

# 0. Setup

using Downloads

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.txt",
)

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

# ============================================================
# 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(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.

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

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 without accents (PÉRIODE -> periode,
# referenceNumber -> reference_number, as janitor does in R), trimmed text,
# empty strings as missing.

data = data.rename(
    {
        column: 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
    }
)
data = data.with_columns(pl.col(pl.Utf8).str.strip_chars().replace("", None))

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")

* 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
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: 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 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))

# 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

# 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,
)