{ "cells": [ { "cell_type": "markdown", "id": "2338d785", "metadata": {}, "source": [ "# TrimAndJitterV3 — the anonymisation algorithm, in one notebook\n", "\n", "**Appendix to the presentation (slide 6).** Everything the algorithm does, written out in\n", "plain pandas so it can be read top to bottom, then checked bit-for-bit against the\n", "pipeline implementation (`src/dsh/m1/transform.py`, configuration id\n", "`c15_window_k5_noise5`) and scored with the pipeline's privacy tests.\n", "\n", "The name says what it does: **Trim** — cut the columns that name or locate a person and\n", "the rows that stand alone; **Jitter** — nudge every measurement by a few percent.\n", "**V3** because it is the third generation: V1 removed identifiers only, V2 coarsened time\n", "and categories, V3 keeps the detail and protects the numbers instead.\n", "\n", "The algorithm has **four steps** and nothing else:\n", "\n", "| step | what | why |\n", "|---|---|---|\n", "| 1 | **Remove** the three direct identifiers (`msisdn`, `imsi`, `imei`) and the base-station id (`enb_id`) | direct identifiers name a subscriber; the station id is a quasi-identifier too fine to keep in a row release |\n", "| 2 | **Noise**: multiply every measurement (speeds, latencies, volumes — 14 columns) by e^Laplace(0, 0.05), ≈ ±5 % | exact measurements fingerprint a row almost as well as a name; ±5 % breaks the exact match while keeping every distribution |\n", "| 3 | **Keep** the 10-minute time window, the province, the application category and the radio type as they are | these are the columns the business questions need |\n", "| 4 | **Suppress**: drop every row whose (window, province, app, radio type) cell has fewer than 5 rows | a row alone in its cell is singled out; 5 is the smallest crowd we accept |\n", "\n", "The dataset used throughout is the one Elisa supplied for the challenge. It is fabricated\n", "(no real subscribers, randomised locations, invented dates), so its rows can be shown\n", "directly — the whole file (1,099,340 rows) is transformed and used throughout." ] }, { "cell_type": "code", "execution_count": 1, "id": "aca7f627", "metadata": { "execution": { "iopub.execute_input": "2026-09-20T09:19:32.889880Z", "iopub.status.busy": "2026-09-20T09:19:32.889751Z", "iopub.status.idle": "2026-09-20T09:19:33.129202Z", "shell.execute_reply": "2026-09-20T09:19:33.128427Z" } }, "outputs": [], "source": [ "from __future__ import annotations\n", "\n", "import json, sys, os, hashlib\n", "from datetime import UTC, datetime\n", "from pathlib import Path\n", "\n", "import numpy as np\n", "import pandas as pd\n", "import pyarrow as pa\n", "import pyarrow.parquet as pq\n", "\n", "ROOT = Path.cwd() if (Path.cwd() / \"src\" / \"dsh\").exists() else Path.cwd().parent\n", "sys.path.insert(0, str(ROOT / \"src\"))\n", "PARQUET = ROOT / \"data\" / \"elisa_aaltoai_hackathon_2026_mock.parquet\"\n", "SEED = 42\n", "pd.set_option(\"display.width\", 160, \"display.max_columns\", 30, \"display.float_format\", \"{:.4g}\".format)\n", "\n", "# Default: show the saved answer without credentials or network access.\n", "RUN_AI = os.environ.get(\"RUN_AI\", \"0\") == \"1\"\n", "sys.path.insert(0, str(ROOT / \"notebooks\"))" ] }, { "cell_type": "markdown", "id": "c49368fb", "metadata": {}, "source": [ "## 1 · The algorithm — stand-alone\n", "\n", "Three constants and one function. `trim_and_jitter_v3()` is the whole algorithm." ] }, { "cell_type": "code", "execution_count": 2, "id": "44172dbf", "metadata": { "execution": { "iopub.execute_input": "2026-09-20T09:19:33.130505Z", "iopub.status.busy": "2026-09-20T09:19:33.130343Z", "iopub.status.idle": "2026-09-20T09:19:33.134600Z", "shell.execute_reply": "2026-09-20T09:19:33.134222Z" } }, "outputs": [], "source": [ "IDENTIFIERS = (\"msisdn\", \"imsi\", \"imei\") # direct identifiers: always removed\n", "STATION = \"enb_id\" # quasi-identifier removed in this configuration\n", "QUASI = (\"time_start\", \"province\", \"application_category\", \"radio_access_type\") # kept, used as the k-cell\n", "MEASUREMENTS = ( # the 14 supplied measurement columns: noised\n", " \"tp_dl_avg\", \"tp_ul_avg\", \"tp_dl_filtered_avg\",\n", " \"cont_rtt_radio_avg\", \"cont_rtt_internet_avg\", \"initial_rtt_radio_avg\",\n", " \"tcp_retrans_byte_ratio_downlink_avg\", \"tcp_retrans_byte_ratio_uplink_avg\",\n", " \"http_response_time_avg\", \"http_sr_avg\",\n", " \"data_GB_sum\", \"im_video_GB_sum\", \"im_audio_GB_sum\", \"tethering_data_GB_dl_sum\",\n", ")\n", "NOISE_SCALE = 0.05 # Laplace scale of the log-multiplier: ≈ ±5 % typical change\n", "K = 5 # minimum rows per (window, province, app, radio type) cell\n", "\n", "\n", "def trim_and_jitter_v3(raw: pd.DataFrame, seed: int = SEED) -> tuple[pd.DataFrame, np.ndarray]:\n", " \"TrimAndJitterV3 (pipeline id c15_window_k5_noise5). Returns (released rows, mask of the raw rows kept).\"\n", " out = raw.copy()\n", "\n", " # TRIM 1 — remove identifiers and the station id\n", " out = out.drop(columns=[c for c in (*IDENTIFIERS, STATION) if c in out.columns])\n", "\n", " # JITTER — multiplicative noise on every measurement column\n", " # one generator for the whole release, seeded from the run seed (+1 keeps it\n", " # independent of the pseudonym generator used by other configurations)\n", " rng = np.random.default_rng(seed + 1)\n", " for col in MEASUREMENTS:\n", " if col not in out.columns:\n", " continue\n", " v = out[col].astype(float)\n", " factor = np.exp(rng.laplace(0.0, NOISE_SCALE, size=len(v)))\n", " noisy = (v * factor).clip(lower=0.0, upper=float(v.max())) # never below 0, never above the observed max\n", " noisy[v.isna()] = np.nan # a missing value stays missing\n", " out[col] = noisy # (an exact 0 stays 0: 0 × anything = 0)\n", "\n", " # KEEP — time window, province, app category and radio type are unchanged\n", "\n", " # TRIM 2 — k-suppression on the quasi-identifier cell\n", " cell = [c for c in QUASI if c in out.columns]\n", " size = out.groupby(cell, dropna=False)[cell[0]].transform(\"size\").to_numpy()\n", " keep = size >= K\n", " return out[keep].reset_index(drop=True), keep" ] }, { "cell_type": "markdown", "id": "1a0a316d", "metadata": {}, "source": [ "## 2 · Before and after, on the full supplied dataset\n", "\n", "`trim_and_jitter_v3()` is run once on all 1,099,340 rows of Elisa's file. Shown below: the\n", "first rows before, the same rows after, and the first row the k = 5 rule withholds. Watch\n", "three things: the identifier and station columns disappear, every measurement moves by a\n", "few percent, and the withheld row is gone. (For readability only three of the 14\n", "measurement columns are displayed; the function transforms all of them.)" ] }, { "cell_type": "code", "execution_count": 3, "id": "768a3b31", "metadata": { "execution": { "iopub.execute_input": "2026-09-20T09:19:33.135639Z", "iopub.status.busy": "2026-09-20T09:19:33.135521Z", "iopub.status.idle": "2026-09-20T09:19:34.040305Z", "shell.execute_reply": "2026-09-20T09:19:34.039894Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "BEFORE — first 8 of 1,099,340 rows of the supplied dataset\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
msisdnimsiimeienb_idtime_startprovinceapplication_categoryradio_access_typetp_dl_avgcont_rtt_radio_avgdata_GB_sum
03586000911692440710000911691000000000911698511711553761809100800UusimaaP2P2G0NaN1.212e-05
13586001147952440710001147951000000001147956911610133631809100800Etelä-PohjanmaaRemote access5GNaNNaN0.0004451
23586001173892440710001173891000000001173898511711589521809102000UusimaaAI5G0.4935172.30.002388
33586000542402440710000542401000000000542408511711574931809100800UusimaaChat5GNaNNaN2.041e-05
43586001130672440710001130671000000001130678010511451011809101400PirkanmaaP2P2G0NaN4.971e-07
53586000616832440710000616831000000000616838511711539321809101400UusimaaAI4G0.0868259.364.238e-05
63586000013292440710000013291000000000013298022810526031809100800Päijät-HämeP2P4G0NaN1.866e-05
73586000182792440710000182791000000000182798511711520201809103800UusimaaP2P2G0NaN1.459e-07
\n", "
" ], "text/plain": [ " msisdn imsi imei enb_id time_start province application_category radio_access_type tp_dl_avg \\\n", "0 358600091169 244071000091169 100000000091169 851171155376 1809100800 Uusimaa P2P 2G 0 \n", "1 358600114795 244071000114795 100000000114795 691161013363 1809100800 Etelä-Pohjanmaa Remote access 5G NaN \n", "2 358600117389 244071000117389 100000000117389 851171158952 1809102000 Uusimaa AI 5G 0.4935 \n", "3 358600054240 244071000054240 100000000054240 851171157493 1809100800 Uusimaa Chat 5G NaN \n", "4 358600113067 244071000113067 100000000113067 801051145101 1809101400 Pirkanmaa P2P 2G 0 \n", "5 358600061683 244071000061683 100000000061683 851171153932 1809101400 Uusimaa AI 4G 0.08682 \n", "6 358600001329 244071000001329 100000000001329 802281052603 1809100800 Päijät-Häme P2P 4G 0 \n", "7 358600018279 244071000018279 100000000018279 851171152020 1809103800 Uusimaa P2P 2G 0 \n", "\n", " cont_rtt_radio_avg data_GB_sum \n", "0 NaN 1.212e-05 \n", "1 NaN 0.0004451 \n", "2 172.3 0.002388 \n", "3 NaN 2.041e-05 \n", "4 NaN 4.971e-07 \n", "5 59.36 4.238e-05 \n", "6 NaN 1.866e-05 \n", "7 NaN 1.459e-07 " ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "AFTER — the same rows in the released table (1,097,135 rows kept, 2,205 withheld)\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
time_startprovinceapplication_categoryradio_access_typetp_dl_avgcont_rtt_radio_avgdata_GB_sum
01809100800UusimaaP2P2G0NaN1.207e-05
11809100800Etelä-PohjanmaaRemote access5GNaNNaN0.0004177
21809102000UusimaaAI5G0.4202190.60.002342
31809100800UusimaaChat5GNaNNaN1.987e-05
41809101400PirkanmaaP2P2G0NaN5.289e-07
51809101400UusimaaAI4G0.0834159.253.927e-05
61809100800Päijät-HämeP2P4G0NaN1.875e-05
71809103800UusimaaP2P2G0NaN1.36e-07
\n", "
" ], "text/plain": [ " time_start province application_category radio_access_type tp_dl_avg cont_rtt_radio_avg data_GB_sum\n", "0 1809100800 Uusimaa P2P 2G 0 NaN 1.207e-05\n", "1 1809100800 Etelä-Pohjanmaa Remote access 5G NaN NaN 0.0004177\n", "2 1809102000 Uusimaa AI 5G 0.4202 190.6 0.002342\n", "3 1809100800 Uusimaa Chat 5G NaN NaN 1.987e-05\n", "4 1809101400 Pirkanmaa P2P 2G 0 NaN 5.289e-07\n", "5 1809101400 Uusimaa AI 4G 0.08341 59.25 3.927e-05\n", "6 1809100800 Päijät-Häme P2P 4G 0 NaN 1.875e-05\n", "7 1809103800 Uusimaa P2P 2G 0 NaN 1.36e-07" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "\n", "First withheld row in the file (raw row 20): alone or nearly alone in its time_start / province / application_category / radio_access_type cell\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
msisdnimsiimeienb_idtime_startprovinceapplication_categoryradio_access_typetp_dl_avgcont_rtt_radio_avgdata_GB_sum
20358600060669244071000060669100000000060669759711090501809100800Kanta-HämeChat5GNaNNaN1.22e-05
\n", "
" ], "text/plain": [ " msisdn imsi imei enb_id time_start province application_category radio_access_type tp_dl_avg cont_rtt_radio_avg \\\n", "20 358600060669 244071000060669 100000000060669 75971109050 1809100800 Kanta-Häme Chat 5G NaN NaN \n", "\n", " data_GB_sum \n", "20 1.22e-05 " ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "from dsh.m1.loader import load\n", "\n", "have_parquet = PARQUET.exists()\n", "if not have_parquet:\n", " print(f\"{PARQUET.name} not found — this and every later cell need the supplied file.\")\n", "else:\n", " source = load(PARQUET, seed=SEED, code_version=\"notebook\", config_version=\"notebook\")\n", " raw = source.rows.to_pandas()\n", " ours, ours_mask = trim_and_jitter_v3(raw) # the whole file, once\n", "\n", " SHOW = [*IDENTIFIERS, STATION, *QUASI, \"tp_dl_avg\", \"cont_rtt_radio_avg\", \"data_GB_sum\"]\n", " N = 8\n", " print(f\"BEFORE — first {N} of {len(raw):,} rows of the supplied dataset\"); display(raw[SHOW].head(N))\n", " pos = np.cumsum(ours_mask) - 1 # released position of each kept raw row\n", " kept_first = [i for i in range(N) if ours_mask[i]]\n", " print(f\"AFTER — the same rows in the released table ({len(ours):,} rows kept, {int((~ours_mask).sum()):,} withheld)\")\n", " display(ours.iloc[[pos[i] for i in kept_first]][[c for c in SHOW if c in ours.columns]].set_axis(kept_first))\n", " withheld = [i for i in range(N) if not ours_mask[i]]\n", " if withheld:\n", " print(f\"rows {withheld} of the first {N} were withheld (fewer than {K} rows in their cell)\")\n", " first_gone = int(np.flatnonzero(~ours_mask)[0])\n", " print(f\"\\nFirst withheld row in the file (raw row {first_gone:,}): alone or nearly alone in its {' / '.join(QUASI)} cell\")\n", " display(raw[SHOW].iloc[[first_gone]])" ] }, { "cell_type": "markdown", "id": "12d0aa3a", "metadata": {}, "source": [ "## 3 · The same algorithm, checked against the pipeline\n", "\n", "The pipeline's `transform()` is what produced every number on the comparison page. The\n", "stand-alone function above must give the *same released table* — same rows, same noise,\n", "same suppression — or this notebook would be describing something else." ] }, { "cell_type": "code", "execution_count": 4, "id": "588cf648", "metadata": { "execution": { "iopub.execute_input": "2026-09-20T09:19:34.041450Z", "iopub.status.busy": "2026-09-20T09:19:34.041324Z", "iopub.status.idle": "2026-09-20T09:19:35.429043Z", "shell.execute_reply": "2026-09-20T09:19:35.428315Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "identical: 1,097,135 released rows × 18 columns; 2,205 rows suppressed; checksum sha256:36be13cea8c2…\n" ] } ], "source": [ "from dsh.m1.transform import transform_with_mask\n", "\n", "CONFIG = \"c15_window_k5_noise5\" # TrimAndJitterV3's id inside the pipeline\n", "if have_parquet:\n", " pipeline, pipeline_mask = transform_with_mask(source, algorithm_id=\"m1\", config_id=CONFIG, parameters={})\n", " theirs = pipeline.rows.to_pandas()\n", "\n", " assert list(ours.columns) == list(theirs.columns), (list(ours.columns), list(theirs.columns))\n", " assert (ours_mask == np.asarray(pipeline_mask)).all()\n", " pd.testing.assert_frame_equal(ours, theirs, check_dtype=False, check_exact=False, rtol=0, atol=1e-12)\n", " print(f\"identical: {len(ours):,} released rows × {ours.shape[1]} columns; \"\n", " f\"{int((~ours_mask).sum()):,} rows suppressed; \"\n", " f\"checksum {source.provenance.dataset_checksum[:19]}…\")" ] }, { "cell_type": "markdown", "id": "29a81347", "metadata": {}, "source": [ "## 4 · What changed, on the real file — aggregates only\n", "\n", "Column by column: which columns were removed, how far the noised numbers moved, and how\n", "many rows the k = 5 rule removed." ] }, { "cell_type": "code", "execution_count": 5, "id": "471b89d5", "metadata": { "execution": { "iopub.execute_input": "2026-09-20T09:19:35.430109Z", "iopub.status.busy": "2026-09-20T09:19:35.430005Z", "iopub.status.idle": "2026-09-20T09:19:36.442245Z", "shell.execute_reply": "2026-09-20T09:19:36.441705Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "removed columns: ['msisdn', 'imsi', 'imei', 'enb_id']\n", "kept unchanged : ['time_start', 'radio_access_type', 'province', 'application_category']\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
non-nullzeros kept as zeromedian |change|p90 |change|mean beforemean after
column
tp_dl_avg886158True3.5%11.5%0.34620.347
tp_ul_avg839572True3.5%11.5%0.041740.04185
tp_dl_filtered_avg119108True3.5%11.6%7.2467.264
cont_rtt_radio_avg589609True3.5%11.5%116.9117.2
cont_rtt_internet_avg624183True3.5%11.5%103.7104
initial_rtt_radio_avg752659True3.5%11.5%46.946.97
tcp_retrans_byte_ratio_downlink_avg1048147True3.4%11.5%0.019940.01985
tcp_retrans_byte_ratio_uplink_avg1096747True3.5%11.5%0.018230.01813
http_response_time_avg1097135True3.5%11.4%10.6610.65
http_sr_avg63001True3.5%11.4%0.89060.893
data_GB_sum1097135True3.5%11.5%0.010320.01036
im_video_GB_sum1097135True3.5%11.3%0.0001410.0001416
im_audio_GB_sum1097135True3.4%11.5%7.955e-058.005e-05
tethering_data_GB_dl_sum1097135True3.5%11.6%0.0048970.004889
\n", "
" ], "text/plain": [ " non-null zeros kept as zero median |change| p90 |change| mean before mean after\n", "column \n", "tp_dl_avg 886158 True 3.5% 11.5% 0.3462 0.347\n", "tp_ul_avg 839572 True 3.5% 11.5% 0.04174 0.04185\n", "tp_dl_filtered_avg 119108 True 3.5% 11.6% 7.246 7.264\n", "cont_rtt_radio_avg 589609 True 3.5% 11.5% 116.9 117.2\n", "cont_rtt_internet_avg 624183 True 3.5% 11.5% 103.7 104\n", "initial_rtt_radio_avg 752659 True 3.5% 11.5% 46.9 46.97\n", "tcp_retrans_byte_ratio_downlink_avg 1048147 True 3.4% 11.5% 0.01994 0.01985\n", "tcp_retrans_byte_ratio_uplink_avg 1096747 True 3.5% 11.5% 0.01823 0.01813\n", "http_response_time_avg 1097135 True 3.5% 11.4% 10.66 10.65\n", "http_sr_avg 63001 True 3.5% 11.4% 0.8906 0.893\n", "data_GB_sum 1097135 True 3.5% 11.5% 0.01032 0.01036\n", "im_video_GB_sum 1097135 True 3.5% 11.3% 0.000141 0.0001416\n", "im_audio_GB_sum 1097135 True 3.4% 11.5% 7.955e-05 8.005e-05\n", "tethering_data_GB_dl_sum 1097135 True 3.5% 11.6% 0.004897 0.004889" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "rows: 1,099,340 in → 1,097,135 out (2,205 = 0.20 % suppressed by the k = 5 rule)\n" ] } ], "source": [ "if have_parquet:\n", " removed = [c for c in raw.columns if c not in ours.columns]\n", " print(\"removed columns:\", removed)\n", " print(\"kept unchanged :\", [c for c in ours.columns if c not in MEASUREMENTS])\n", " kept_raw = raw[ours_mask].reset_index(drop=True)\n", " rows = []\n", " for col in MEASUREMENTS:\n", " a, b = kept_raw[col].astype(float), ours[col].astype(float)\n", " both = a.notna() & b.notna() & (a > 0)\n", " rel = ((b[both] - a[both]).abs() / a[both])\n", " rows.append({\n", " \"column\": col,\n", " \"non-null\": int(a.notna().sum()),\n", " \"zeros kept as zero\": bool(((a == 0) == (b == 0)).all()),\n", " \"median |change|\": float(rel.median()) if len(rel) else np.nan,\n", " \"p90 |change|\": float(rel.quantile(0.9)) if len(rel) else np.nan,\n", " \"mean before\": float(a.mean()), \"mean after\": float(b.mean()),\n", " })\n", " table = pd.DataFrame(rows).set_index(\"column\")\n", " for c in (\"median |change|\", \"p90 |change|\"):\n", " table[c] = table[c].map(lambda x: f\"{x:.1%}\" if pd.notna(x) else \"—\")\n", " display(table)\n", " print(f\"rows: {len(raw):,} in → {len(ours):,} out ({int((~ours_mask).sum()):,} = {100*(~ours_mask).mean():.2f} % suppressed by the k = {K} rule)\")" ] }, { "cell_type": "markdown", "id": "558e4247", "metadata": {}, "source": [ "## 5 · The privacy benchmarks\n", "\n", "The pipeline's own tests, run on this release: the three EDPB principles (isolation,\n", "linkage, inference) and our fourth, the numeric fingerprint. All four count subscribers or\n", "probes; none says \"anonymous\".\n", "\n", "| benchmark | question it answers | attacker |\n", "|---|---|---|\n", "| Isolation | how many subscribers have a released row that is alone in its (window, province, app, radio) cell? | sees the release, knows a subscriber's categorical values |\n", "| Linkage across releases | if two windows are released separately, how many subscribers can be matched between them? | knows a subscriber's values in release A |\n", "| Inference | for how many subscribers does the cell they fall in give away their app category (or heavy use) at ≥ 90 %? | knows the categorical values, not the attribute |\n", "| Numeric fingerprint | how many known observations (categoricals **and** exact measurements) find their own released row within ±1 %? | knows one raw observation of the subscriber |" ] }, { "cell_type": "code", "execution_count": 6, "id": "310ee5a1", "metadata": { "execution": { "iopub.execute_input": "2026-09-20T09:19:36.443492Z", "iopub.status.busy": "2026-09-20T09:19:36.443380Z", "iopub.status.idle": "2026-09-20T09:20:03.781426Z", "shell.execute_reply": "2026-09-20T09:20:03.780944Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
valueunit
benchmarkmetric
1 · isolationsubscribers singled out0of 199,195 subscribers
2 · linkagesubscribers linkable across two releases0windows 1–3 vs 4–6
3 · inferenceapp category inferable at ≥ 90 %1198subscribers
heavy use inferable at ≥ 90 %1subscribers
4 · fingerprintprobes finding their own row (±1 %)0.2% of 1,500 probes
probes with no candidate at all99.07% of 1,500 probes
\n", "
" ], "text/plain": [ " value unit\n", "benchmark metric \n", "1 · isolation subscribers singled out 0 of 199,195 subscribers\n", "2 · linkage subscribers linkable across two releases 0 windows 1–3 vs 4–6\n", "3 · inference app category inferable at ≥ 90 % 1198 subscribers\n", " heavy use inferable at ≥ 90 % 1 subscribers\n", "4 · fingerprint probes finding their own row (±1 %) 0.2 % of 1,500 probes\n", " probes with no candidate at all 99.07 % of 1,500 probes" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "if have_parquet:\n", " from dsh.m1 import privacy as m1_privacy\n", " from dsh.m1.run import cross_release_linkage\n", " from dsh.schema.interfaces import EvaluatorTruth\n", "\n", " truth = EvaluatorTruth(rows=source.rows)\n", " results = m1_privacy.evaluate_all(pipeline, truth=truth, seed=SEED, retained_mask=np.asarray(pipeline_mask))\n", " results.append(cross_release_linkage(source, config_id=CONFIG, parameters={}))\n", " by_id = {r.test_id: r for r in results}\n", "\n", " def metric(test, mid):\n", " r = by_id.get(test)\n", " if r is None or r.status != \"ok\":\n", " return f\"{r.status if r else 'absent'}: {r.reason if r else ''}\"\n", " return next(m.value for m in r.metrics if m.metric_id == mid)\n", "\n", " n_sub = metric(\"singling_out_k_anonymity\", \"subscribers_in_source\")\n", " summary = pd.DataFrame([\n", " [\"1 · isolation\", \"subscribers singled out\", metric(\"singling_out_k_anonymity\", \"subscribers_any_row_singled_out\"), f\"of {n_sub:,.0f} subscribers\"],\n", " [\"2 · linkage\", \"subscribers linkable across two releases\", metric(\"linkage_cross_release\", \"subscribers_linkable_across_releases\"), \"windows 1–3 vs 4–6\"],\n", " [\"3 · inference\", \"app category inferable at ≥ 90 %\", metric(\"inference_application_category\", \"subscribers_inferable\"), \"subscribers\"],\n", " [\"3 · inference\", \"heavy use inferable at ≥ 90 %\", metric(\"inference_heavy_user\", \"subscribers_inferable\"), \"subscribers\"],\n", " [\"4 · fingerprint\", \"probes finding their own row (±1 %)\", metric(\"numeric_fingerprint\", \"probes_correctly_matched_pct\"), \"% of 1,500 probes\"],\n", " [\"4 · fingerprint\", \"probes with no candidate at all\", metric(\"numeric_fingerprint\", \"probes_no_candidate_pct\"), \"% of 1,500 probes\"],\n", " ], columns=[\"benchmark\", \"metric\", \"value\", \"unit\"])\n", " display(summary.set_index([\"benchmark\", \"metric\"]))" ] }, { "cell_type": "markdown", "id": "1fafcf95", "metadata": {}, "source": [ "## 6 · Elina's three questions, answered from the released table\n", "\n", "The presentation opens with three concrete questions. Here each one is computed **from the\n", "released rows only** (`ours`), and — where it can be answered at all — the same statistic\n", "is computed from the raw rows to show that TrimAndJitterV3 did not change the answer. All\n", "outputs are aggregates.\n", "\n", "1. Where should Elisa focus product development: gaming or streaming?\n", "2. Which application categories should Elisa prioritise for network-quality improvements to reduce customer complaints?\n", "3. Where should Elisa invest in base-station capacity to improve service quality?" ] }, { "cell_type": "code", "execution_count": 7, "id": "0e3bf359", "metadata": { "execution": { "iopub.execute_input": "2026-09-20T09:20:03.782758Z", "iopub.status.busy": "2026-09-20T09:20:03.782645Z", "iopub.status.idle": "2026-09-20T09:20:03.973955Z", "shell.execute_reply": "2026-09-20T09:20:03.973554Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
application_categoryGamingStreaming
rawrecords1.217e+048.957e+04
share of all records0.011070.08147
traffic (GB)8013431
share of all traffic0.070590.3024
video traffic (GB)08.484
5G share of records0.34180.4139
median download (Mbit/s)0.072350.1048
median radio RTT (ms)93.1270.72
share slow (<p10 speed)0.024240.01557
share laggy (>p90 RTT)0.063360.04541
released (TrimAndJitterV3)records1.213e+048.954e+04
share of all records0.011060.08161
traffic (GB)803.93452
share of all traffic0.070760.3038
video traffic (GB)08.489
5G share of records0.34070.414
median download (Mbit/s)0.07230.1051
median radio RTT (ms)92.6671.03
share slow (<p10 speed)0.024070.01626
share laggy (>p90 RTT)0.062910.04531
\n", "
" ], "text/plain": [ "application_category Gaming Streaming\n", "raw records 1.217e+04 8.957e+04\n", " share of all records 0.01107 0.08147\n", " traffic (GB) 801 3431\n", " share of all traffic 0.07059 0.3024\n", " video traffic (GB) 0 8.484\n", " 5G share of records 0.3418 0.4139\n", " median download (Mbit/s) 0.07235 0.1048\n", " median radio RTT (ms) 93.12 70.72\n", " share slow (p90 RTT) 0.06336 0.04541\n", "released (TrimAndJitterV3) records 1.213e+04 8.954e+04\n", " share of all records 0.01106 0.08161\n", " traffic (GB) 803.9 3452\n", " share of all traffic 0.07076 0.3038\n", " video traffic (GB) 0 8.489\n", " 5G share of records 0.3407 0.414\n", " median download (Mbit/s) 0.0723 0.1051\n", " median radio RTT (ms) 92.66 71.03\n", " share slow (p90 RTT) 0.06291 0.04531" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "Q1 → Focus on Gaming: it is the worse-served experience (median radio RTT 93 vs 71 ms, lower download speed), so that is where product work moves the needle. Streaming is the larger audience (4× the traffic, 30.4% vs 7.1% of all volume) and already served well. Same reading from the raw table.\n" ] } ], "source": [ "if have_parquet:\n", " TP, RTT, APP, VOL = \"tp_dl_avg\", \"cont_rtt_radio_avg\", \"application_category\", \"data_GB_sum\"\n", "\n", " def product_view(df: pd.DataFrame, cats=(\"Gaming\", \"Streaming\")) -> pd.DataFrame:\n", " \"Demand and experience per category: how much is used, by how many observations, and how well it works.\"\n", " slow, laggy = df[TP].quantile(0.10), df[RTT].quantile(0.90)\n", " g = df[df[APP].isin(cats)].groupby(APP)\n", " return pd.DataFrame({\n", " \"records\": g.size(),\n", " \"share of all records\": g.size() / len(df),\n", " \"traffic (GB)\": g[VOL].sum(),\n", " \"share of all traffic\": g[VOL].sum() / df[VOL].sum(),\n", " \"video traffic (GB)\": g[\"im_video_GB_sum\"].sum(),\n", " \"5G share of records\": g[\"radio_access_type\"].apply(lambda x: (x == \"5G\").mean()),\n", " \"median download (Mbit/s)\": g[TP].median(),\n", " \"median radio RTT (ms)\": g[RTT].median(),\n", " \"share slow (p90 RTT)\": g[RTT].apply(lambda x: (x > laggy).mean()),\n", " }).loc[list(cats)]\n", "\n", " q1 = pd.concat({\"raw\": product_view(raw), \"released (TrimAndJitterV3)\": product_view(ours)}, axis=1)\n", " display(q1.T)\n", " r = product_view(ours)\n", " ratio = r.loc[\"Streaming\", \"traffic (GB)\"] / r.loc[\"Gaming\", \"traffic (GB)\"]\n", " print(f\"Q1 → Focus on Gaming: it is the worse-served experience (median radio RTT {r.loc['Gaming','median radio RTT (ms)']:.0f} vs \"\n", " f\"{r.loc['Streaming','median radio RTT (ms)']:.0f} ms, lower download speed), so that is where product work moves the needle. \"\n", " f\"Streaming is the larger audience ({ratio:.0f}× the traffic, {r.loc['Streaming','share of all traffic']:.1%} vs \"\n", " f\"{r.loc['Gaming','share of all traffic']:.1%} of all volume) and already served well. Same reading from the raw table.\")" ] }, { "cell_type": "code", "execution_count": 8, "id": "e06c88ea", "metadata": { "execution": { "iopub.execute_input": "2026-09-20T09:20:03.975048Z", "iopub.status.busy": "2026-09-20T09:20:03.974937Z", "iopub.status.idle": "2026-09-20T09:20:04.508628Z", "shell.execute_reply": "2026-09-20T09:20:04.508166Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
recordspoor-experience recordsshare poorshare of all poor recordsrank rawrank released
application_category
IM110883395330.35650.275511
VPN79409295420.3720.205922
Other80412176060.21890.122733
Cloud services10964094140.085860.065644
Social10836282440.076080.0574555
CDN5672566310.11690.0462166
\n", "
" ], "text/plain": [ " records poor-experience records share poor share of all poor records rank raw rank released\n", "application_category \n", "IM 110883 39533 0.3565 0.2755 1 1\n", "VPN 79409 29542 0.372 0.2059 2 2\n", "Other 80412 17606 0.2189 0.1227 3 3\n", "Cloud services 109640 9414 0.08586 0.0656 4 4\n", "Social 108362 8244 0.07608 0.05745 5 5\n", "CDN 56725 6631 0.1169 0.04621 6 6" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "Q2 → prioritise by *number* of poor-experience records, because complaints scale with affected sessions, not with a category's failure rate: IM, VPN, Other account for 60% of all poor-experience records. (P2P fails almost every time but is tiny.) Released vs raw: top-6 identical order; counts differ by at most 148 records.\n" ] } ], "source": [ "if have_parquet:\n", " def complaint_view(df: pd.DataFrame) -> pd.DataFrame:\n", " \"Per category: how many observations are unusually slow or laggy (a proxy for complaint volume) and what share that is.\"\n", " slow, laggy = df[TP].quantile(0.10), df[RTT].quantile(0.90)\n", " g = df.groupby(APP)\n", " out = pd.DataFrame({\n", " \"records\": g.size(),\n", " \"poor-experience records\": g.apply(lambda x: int(((x[TP] < slow) | (x[RTT] > laggy)).sum())),\n", " })\n", " out[\"share poor\"] = out[\"poor-experience records\"] / out[\"records\"]\n", " out[\"share of all poor records\"] = out[\"poor-experience records\"] / out[\"poor-experience records\"].sum()\n", " return out.sort_values(\"poor-experience records\", ascending=False)\n", "\n", " q2_raw, q2_rel = complaint_view(raw), complaint_view(ours)\n", " top = q2_rel.head(6).copy()\n", " top[\"rank raw\"] = [int(q2_raw.index.get_loc(c)) + 1 for c in top.index]\n", " top[\"rank released\"] = range(1, len(top) + 1)\n", " display(top)\n", " same = list(q2_raw.head(6).index) == list(q2_rel.head(6).index)\n", " lead = top.index[0]\n", " print(f\"Q2 → prioritise by *number* of poor-experience records, because complaints scale with affected sessions, not with a category's failure rate: \"\n", " f\"{', '.join(top.index[:3])} account for {top['share of all poor records'].head(3).sum():.0%} of all poor-experience records. \"\n", " f\"(P2P fails almost every time but is tiny.) Released vs raw: top-6 {'identical order' if same else 'same set, order differs'}; \"\n", " f\"counts differ by at most {int((q2_rel['poor-experience records'] - q2_raw['poor-experience records']).abs().max()):,} records.\")" ] }, { "cell_type": "code", "execution_count": 9, "id": "16caa8a0", "metadata": { "execution": { "iopub.execute_input": "2026-09-20T09:20:04.509790Z", "iopub.status.busy": "2026-09-20T09:20:04.509665Z", "iopub.status.idle": "2026-09-20T09:20:04.703378Z", "shell.execute_reply": "2026-09-20T09:20:04.702908Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Q3 → cannot be answered from the released table at the level the question needs:\n", " 'enb_id' released: False\n", " the raw file could rank stations (its 50 busiest carry 26% of recorded volume); the release cannot — by design, the station id is a quasi-identifier we chose to remove.\n", " What survives is the province level — traffic and poor-experience share per province and radio type — which says which region, not which mast.\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
radio_access_type2G4G5G
province
Etelä-Karjala016.2190.2
Etelä-Pohjanmaa025.9336.7
Etelä-Savo012.5138.3
Kainuu07.8122.2
Kanta-Häme028.5323.4
Keski-Pohjanmaa07.190.1
Keski-Suomi035.2433.9
Kymenlaakso019.7243.7
Lappi015.7228.1
Pirkanmaa077.61418
Pohjanmaa023.1326.2
Pohjois-Karjala021.9231
Pohjois-Pohjanmaa044.3579.4
Pohjois-Savo026.7330.1
Päijät-Häme021.4275.1
Satakunta019.7237.5
UnavailableNaN0.22
Undefined03.237.7
Uusimaa0.1322.94259
Varsinais-Suomi065.6762.8
\n", "
" ], "text/plain": [ "radio_access_type 2G 4G 5G\n", "province \n", "Etelä-Karjala 0 16.2 190.2\n", "Etelä-Pohjanmaa 0 25.9 336.7\n", "Etelä-Savo 0 12.5 138.3\n", "Kainuu 0 7.8 122.2\n", "Kanta-Häme 0 28.5 323.4\n", "Keski-Pohjanmaa 0 7.1 90.1\n", "Keski-Suomi 0 35.2 433.9\n", "Kymenlaakso 0 19.7 243.7\n", "Lappi 0 15.7 228.1\n", "Pirkanmaa 0 77.6 1418\n", "Pohjanmaa 0 23.1 326.2\n", "Pohjois-Karjala 0 21.9 231\n", "Pohjois-Pohjanmaa 0 44.3 579.4\n", "Pohjois-Savo 0 26.7 330.1\n", "Päijät-Häme 0 21.4 275.1\n", "Satakunta 0 19.7 237.5\n", "Unavailable NaN 0.2 2\n", "Undefined 0 3.2 37.7\n", "Uusimaa 0.1 322.9 4259\n", "Varsinais-Suomi 0 65.6 762.8" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
radio_access_type2G4G5G
province
Etelä-Karjala0.8750.1420.1
Etelä-Pohjanmaa0.8980.1390.097
Etelä-Savo0.8330.1340.093
Kainuu10.1360.089
Kanta-Häme0.9220.1420.094
Keski-Pohjanmaa0.6670.1360.099
Keski-Suomi0.8740.140.098
Kymenlaakso10.1410.106
Lappi0.9820.1430.091
Pirkanmaa0.8810.1390.098
Pohjanmaa0.8950.1420.09
Pohjois-Karjala0.8960.1360.1
Pohjois-Pohjanmaa0.9170.1370.094
Pohjois-Savo0.9440.1370.1
Päijät-Häme0.9190.1380.102
Satakunta10.1350.097
UnavailableNaN0.1890.048
Undefined10.1350.086
Uusimaa0.8530.140.099
Varsinais-Suomi0.8710.1390.099
\n", "
" ], "text/plain": [ "radio_access_type 2G 4G 5G\n", "province \n", "Etelä-Karjala 0.875 0.142 0.1\n", "Etelä-Pohjanmaa 0.898 0.139 0.097\n", "Etelä-Savo 0.833 0.134 0.093\n", "Kainuu 1 0.136 0.089\n", "Kanta-Häme 0.922 0.142 0.094\n", "Keski-Pohjanmaa 0.667 0.136 0.099\n", "Keski-Suomi 0.874 0.14 0.098\n", "Kymenlaakso 1 0.141 0.106\n", "Lappi 0.982 0.143 0.091\n", "Pirkanmaa 0.881 0.139 0.098\n", "Pohjanmaa 0.895 0.142 0.09\n", "Pohjois-Karjala 0.896 0.136 0.1\n", "Pohjois-Pohjanmaa 0.917 0.137 0.094\n", "Pohjois-Savo 0.944 0.137 0.1\n", "Päijät-Häme 0.919 0.138 0.102\n", "Satakunta 1 0.135 0.097\n", "Unavailable NaN 0.189 0.048\n", "Undefined 1 0.135 0.086\n", "Uusimaa 0.853 0.14 0.099\n", "Varsinais-Suomi 0.871 0.139 0.099" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "if have_parquet:\n", " print(\"Q3 → cannot be answered from the released table at the level the question needs:\")\n", " print(f\" 'enb_id' released: {'enb_id' in ours.columns}\")\n", " raw_station = raw.groupby(\"enb_id\")[VOL].sum().sort_values(ascending=False)\n", " top50 = raw_station.head(50).sum() / raw_station.sum()\n", " print(f\" the raw file could rank stations (its 50 busiest carry {top50:.0%} of recorded volume); the release cannot — \"\n", " \"by design, the station id is a quasi-identifier we chose to remove.\")\n", " print(\" What survives is the province level — traffic and poor-experience share per province and radio type — \"\n", " \"which says which region, not which mast.\")\n", " slow, laggy = ours[TP].quantile(0.10), ours[RTT].quantile(0.90)\n", " prov = ours.groupby([\"province\", \"radio_access_type\"]).agg(\n", " traffic_GB=(VOL, \"sum\"),\n", " share_poor=(TP, lambda x: float(((x < slow) | (ours.loc[x.index, RTT] > laggy)).mean())),\n", " )\n", " display(prov[\"traffic_GB\"].unstack().round(1))\n", " display(prov[\"share_poor\"].unstack().round(3))" ] }, { "cell_type": "markdown", "id": "d067b793", "metadata": {}, "source": [ "### The answers, one sentence each — computed on the base dataset" ] }, { "cell_type": "code", "execution_count": 10, "id": "6ceec058", "metadata": { "execution": { "iopub.execute_input": "2026-09-20T09:20:04.704762Z", "iopub.status.busy": "2026-09-20T09:20:04.704622Z", "iopub.status.idle": "2026-09-20T09:20:04.979148Z", "shell.execute_reply": "2026-09-20T09:20:04.978683Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "1. Gaming — it is the worse-served experience (median radio RTT 93 ms vs 71 ms for Streaming, median download 0.072 vs 0.105 Mbit/s), so it is where product development can improve the most; Streaming is already the larger audience (30.2% vs 7.1% of all traffic) and served well.\n", "2. IM, VPN and Other — together they account for 60% of all poor-experience observations (86,863 of 143,723), with IM alone at 39,612 (36% of its own records).\n", "3. On the base dataset the 143 busiest of 1,983 base stations carry half of all recorded traffic, so those are where capacity investment would go — but the release cannot name them, because TrimAndJitterV3 removes the station id.\n" ] } ], "source": [ "if have_parquet:\n", " b = product_view(raw)\n", " c = complaint_view(raw)\n", " top3 = c.head(3)\n", " st = raw.groupby(\"enb_id\")[VOL].sum().sort_values(ascending=False)\n", " n_top = int((st.cumsum() / st.sum() < 0.5).sum()) + 1\n", " print(f\"1. Gaming — it is the worse-served experience (median radio RTT {b.loc['Gaming','median radio RTT (ms)']:.0f} ms vs \"\n", " f\"{b.loc['Streaming','median radio RTT (ms)']:.0f} ms for Streaming, median download {b.loc['Gaming','median download (Mbit/s)']:.3f} vs \"\n", " f\"{b.loc['Streaming','median download (Mbit/s)']:.3f} Mbit/s), so it is where product development can improve the most; Streaming is already \"\n", " f\"the larger audience ({b.loc['Streaming','share of all traffic']:.1%} vs {b.loc['Gaming','share of all traffic']:.1%} of all traffic) and served well.\")\n", " print(f\"2. {top3.index[0]}, {top3.index[1]} and {top3.index[2]} — together they account for {top3['share of all poor records'].sum():.0%} of all \"\n", " f\"poor-experience observations ({int(top3['poor-experience records'].sum()):,} of {int(c['poor-experience records'].sum()):,}), \"\n", " f\"with {top3.index[0]} alone at {int(top3.iloc[0]['poor-experience records']):,} ({top3.iloc[0]['share poor']:.0%} of its own records).\")\n", " print(f\"3. On the base dataset the {n_top} busiest of {len(st):,} base stations carry half of all recorded traffic, so those are where capacity \"\n", " f\"investment would go — but the release cannot name them, because TrimAndJitterV3 removes the station id.\")" ] }, { "cell_type": "markdown", "id": "8310e243", "metadata": {}, "source": [ "**What this shows.** Questions 1 and 2 are comparisons *between categories* — the release\n", "answers them with the same ranking as the raw file, because ±5 % jitter moves every record\n", "a little and no category systematically, and the 0.2 % of trimmed rows are spread across\n", "categories. Question 3 needs the base-station id, which TrimAndJitterV3 removes; that is\n", "the deliberate cost, and the honest answer is \"which province, not which mast\" — a\n", "station-level product would need its own minimum-cell rule." ] }, { "cell_type": "markdown", "id": "4f7fb3f1", "metadata": {}, "source": [ "## 7 · Why these four steps and not others\n", "\n", "Read alongside the comparison page (`out/m1/comparison.html`) and the decision brief. Pipeline id: `c15_window_k5_noise5`.\n", "\n", "- **Removing identifiers alone** leaves 72,841 subscribers singled out on their categorical values and ≈99 % findable by their exact measurements. Necessary, nowhere near sufficient.\n", "- **Coarsening time to the hour** (c3) drops isolation to 66 but loses the 10-minute resolution that the time-dependent business questions need. c15 keeps the window and pays for it with suppression instead.\n", "- **Suppression at k = 5** on the (window, province, app, radio) cell takes isolation to 0. It removes ~2,200 rows (0.2 %) — mostly the mixed, rare cells — which is why the *inference* count rises: the survivors are more homogeneous. That is the one open number on this configuration and it is stated, not hidden.\n", "- **±5 % multiplicative noise** closes the numeric fingerprint (≈99 % → well under 1 %). Additive noise scaled to the standard deviation was tried first and destroyed utility (the columns are zero-inflated and heavy-tailed); larger scales (±10 %, ±30 %) do not improve the fingerprint further and cost 4–11× the distortion.\n", "- **No pseudonym.** Session linkage is what a pseudonym buys, and it is measured separately (c16) at 78.8 % three-observation linkability; it belongs to a controlled tier, not the default release.\n", "\n", "What this is **not**: differential privacy. There is no sensitivity bound and no clipping of a subscriber's total contribution, so the numbers above say what a specific, transformation-aware attacker achieves on this file — not what any attacker could." ] }, { "cell_type": "markdown", "id": "b3ed42f1", "metadata": {}, "source": [ "## Reproduce\n", "\n", "```sh\n", "PYTHONPATH=src python scripts/m1_sweep.py --parquet data/elisa_aaltoai_hackathon_2026_mock.parquet --seed 42 --only c15_window_k5_noise5\n", "PYTHONPATH=src python scripts/compare_results.py out/m1/*.json --html out/m1/comparison.html --md out/m1/comparison.md\n", "```\n", "\n", "Same seed, same Parquet checksum, same numbers." ] }, { "cell_type": "markdown", "id": "e51d579e", "metadata": { "tags": [ "llm-appendix" ] }, "source": [ "# 8 · From measured results to business hypotheses\n", "\n", "Experimental appendix. Python computes the numbers; Mistral interprets aggregate application-category summaries. The original notebook above retains its saved outputs. This is AI-assisted interpretation, not evidence of business impact or an anonymity certification.\n", "\n", "The following JSON is the **complete data payload** sent to the endpoint. No source rows or identifiers are included. The response requires human review. The Mistral client and system instructions are in `llm/analyst.py`; credentials are loaded from private environment variables." ] }, { "cell_type": "code", "execution_count": 11, "id": "2237d82e", "metadata": { "execution": { "iopub.execute_input": "2026-09-20T09:20:04.980476Z", "iopub.status.busy": "2026-09-20T09:20:04.980367Z", "iopub.status.idle": "2026-09-20T09:20:04.985225Z", "shell.execute_reply": "2026-09-20T09:20:04.984670Z" }, "tags": [ "llm-appendix" ] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "{\n", " \"source\": \"Elisa hackathon mock dataset, transformed with c15_window_k5_noise5, seed 42\",\n", " \"released_records\": 1097135,\n", " \"definitions\": {\n", " \"quality_flag\": \"Download below the released-table p10 OR radio RTT above its p90; relative screening proxy, not an SLA breach or observed complaint.\",\n", " \"records\": \"Observations, not unique subscribers or customers.\",\n", " \"measurement_units\": \"Original supplied measurement scales; physical units are not independently verified.\",\n", " \"scope\": \"Six short time buckets; fabricated timestamps. No subscriber identifiers, aliases, station IDs or individual rows are sent.\",\n", " \"business_limits\": \"No revenue, price, willingness-to-pay, complaint, churn, causal or representative-population evidence.\"\n", " },\n", " \"categories\": [\n", " {\n", " \"application_category\": \"AI\",\n", " \"records\": 3626,\n", " \"traffic_GB\": 15.431066,\n", " \"median_download\": 0.053602,\n", " \"median_radio_RTT\": 97.578677,\n", " \"quality_flagged_records\": 387,\n", " \"traffic_share_percent\": 0.135826,\n", " \"quality_flagged_percent\": 10.672918,\n", " \"traffic_GB_rank\": 18,\n", " \"quality_flagged_records_rank\": 19,\n", " \"quality_flagged_percent_rank\": 8\n", " },\n", " {\n", " \"application_category\": \"Ads\",\n", " \"records\": 29723,\n", " \"traffic_GB\": 74.430801,\n", " \"median_download\": 0.086811,\n", " \"median_radio_RTT\": 88.241837,\n", " \"quality_flagged_records\": 1637,\n", " \"traffic_share_percent\": 0.655146,\n", " \"quality_flagged_percent\": 5.507519,\n", " \"traffic_GB_rank\": 12,\n", " \"quality_flagged_records_rank\": 14,\n", " \"quality_flagged_percent_rank\": 20\n", " },\n", " {\n", " \"application_category\": \"Analytics\",\n", " \"records\": 37683,\n", " \"traffic_GB\": 32.383585,\n", " \"median_download\": 0.060764,\n", " \"median_radio_RTT\": 68.395662,\n", " \"quality_flagged_records\": 1404,\n", " \"traffic_share_percent\": 0.285043,\n", " \"quality_flagged_percent\": 3.725818,\n", " \"traffic_GB_rank\": 16,\n", " \"quality_flagged_records_rank\": 15,\n", " \"quality_flagged_percent_rank\": 22\n", " },\n", " {\n", " \"application_category\": \"CDN\",\n", " \"records\": 56725,\n", " \"traffic_GB\": 1272.432857,\n", " \"median_download\": 0.080664,\n", " \"median_radio_RTT\": 106.574512,\n", " \"quality_flagged_records\": 6631,\n", " \"traffic_share_percent\": 11.200061,\n", " \"quality_flagged_percent\": 11.689731,\n", " \"traffic_GB_rank\": 4,\n", " \"quality_flagged_records_rank\": 6,\n", " \"quality_flagged_percent_rank\": 6\n", " },\n", " {\n", " \"application_category\": \"Chat\",\n", " \"records\": 967,\n", " \"traffic_GB\": 0.036769,\n", " \"median_download\": 0.041371,\n", " \"median_radio_RTT\": 71.3581,\n", " \"quality_flagged_records\": 101,\n", " \"traffic_share_percent\": 0.000324,\n", " \"quality_flagged_percent\": 10.444674,\n", " \"traffic_GB_rank\": 25,\n", " \"quality_flagged_records_rank\": 21,\n", " \"quality_flagged_percent_rank\": 10\n", " },\n", " {\n", " \"application_category\": \"Cloud services\",\n", " \"records\": 109640,\n", " \"traffic_GB\": 86.215456,\n", " \"median_download\": 0.048069,\n", " \"median_radio_RTT\": 65.960008,\n", " \"quality_flagged_records\": 9414,\n", " \"traffic_share_percent\": 0.758876,\n", " \"quality_flagged_percent\": 8.586282,\n", " \"traffic_GB_rank\": 11,\n", " \"quality_flagged_records_rank\": 4,\n", " \"quality_flagged_percent_rank\": 12\n", " },\n", " {\n", " \"application_category\": \"Email\",\n", " \"records\": 39215,\n", " \"traffic_GB\": 33.797939,\n", " \"median_download\": 0.076001,\n", " \"median_radio_RTT\": 66.353398,\n", " \"quality_flagged_records\": 1839,\n", " \"traffic_share_percent\": 0.297492,\n", " \"quality_flagged_percent\": 4.689532,\n", " \"traffic_GB_rank\": 15,\n", " \"quality_flagged_records_rank\": 13,\n", " \"quality_flagged_percent_rank\": 21\n", " },\n", " {\n", " \"application_category\": \"File mgmt\",\n", " \"records\": 25837,\n", " \"traffic_GB\": 98.710818,\n", " \"median_download\": 0.056874,\n", " \"median_radio_RTT\": 90.585769,\n", " \"quality_flagged_records\": 1848,\n", " \"traffic_share_percent\": 0.868861,\n", " \"quality_flagged_percent\": 7.152533,\n", " \"traffic_GB_rank\": 10,\n", " \"quality_flagged_records_rank\": 11,\n", " \"quality_flagged_percent_rank\": 17\n", " },\n", " {\n", " \"application_category\": \"File transfer\",\n", " \"records\": 104,\n", " \"traffic_GB\": 0.137517,\n", " \"median_download\": 0.057644,\n", " \"median_radio_RTT\": 81.214227,\n", " \"quality_flagged_records\": 2,\n", " \"traffic_share_percent\": 0.00121,\n", " \"quality_flagged_percent\": 1.923077,\n", " \"traffic_GB_rank\": 23,\n", " \"quality_flagged_records_rank\": 25,\n", " \"quality_flagged_percent_rank\": 24\n", " },\n", " {\n", " \"application_category\": \"Gaming\",\n", " \"records\": 12129,\n", " \"traffic_GB\": 803.878685,\n", " \"median_download\": 0.072299,\n", " \"median_radio_RTT\": 92.657085,\n", " \"quality_flagged_records\": 1036,\n", " \"traffic_share_percent\": 7.075808,\n", " \"quality_flagged_percent\": 8.541512,\n", " \"traffic_GB_rank\": 5,\n", " \"quality_flagged_records_rank\": 17,\n", " \"quality_flagged_percent_rank\": 13\n", " },\n", " {\n", " \"application_category\": \"IM\",\n", " \"records\": 110883,\n", " \"traffic_GB\": 314.289314,\n", " \"median_download\": 0.014675,\n", " \"median_radio_RTT\": 86.315341,\n", " \"quality_flagged_records\": 39533,\n", " \"traffic_share_percent\": 2.766401,\n", " \"quality_flagged_percent\": 35.652895,\n", " \"traffic_GB_rank\": 7,\n", " \"quality_flagged_records_rank\": 1,\n", " \"quality_flagged_percent_rank\": 3\n", " },\n", " {\n", " \"application_category\": \"IoT\",\n", " \"records\": 588,\n", " \"traffic_GB\": 0.098273,\n", " \"median_download\": 0.013444,\n", " \"median_radio_RTT\": 140.774753,\n", " \"quality_flagged_records\": 62,\n", " \"traffic_share_percent\": 0.000865,\n", " \"quality_flagged_percent\": 10.544218,\n", " \"traffic_GB_rank\": 24,\n", " \"quality_flagged_records_rank\": 22,\n", " \"quality_flagged_percent_rank\": 9\n", " },\n", " {\n", " \"application_category\": \"Networking\",\n", " \"records\": 139543,\n", " \"traffic_GB\": 40.024916,\n", " \"median_download\": 0.037369,\n", " \"median_radio_RTT\": 75.164611,\n", " \"quality_flagged_records\": 695,\n", " \"traffic_share_percent\": 0.352303,\n", " \"quality_flagged_percent\": 0.498054,\n", " \"traffic_GB_rank\": 14,\n", " \"quality_flagged_records_rank\": 18,\n", " \"quality_flagged_percent_rank\": 25\n", " },\n", " {\n", " \"application_category\": \"News\",\n", " \"records\": 16521,\n", " \"traffic_GB\": 15.561019,\n", " \"median_download\": 0.152315,\n", " \"median_radio_RTT\": 82.268747,\n", " \"quality_flagged_records\": 1277,\n", " \"traffic_share_percent\": 0.136969,\n", " \"quality_flagged_percent\": 7.729556,\n", " \"traffic_GB_rank\": 17,\n", " \"quality_flagged_records_rank\": 16,\n", " \"quality_flagged_percent_rank\": 15\n", " },\n", " {\n", " \"application_category\": \"Other\",\n", " \"records\": 80412,\n", " \"traffic_GB\": 262.133511,\n", " \"median_download\": 0.047881,\n", " \"median_radio_RTT\": 131.952641,\n", " \"quality_flagged_records\": 17606,\n", " \"traffic_share_percent\": 2.307321,\n", " \"quality_flagged_percent\": 21.894742,\n", " \"traffic_GB_rank\": 8,\n", " \"quality_flagged_records_rank\": 3,\n", " \"quality_flagged_percent_rank\": 4\n", " },\n", " {\n", " \"application_category\": \"P2P\",\n", " \"records\": 4700,\n", " \"traffic_GB\": 1.609552,\n", " \"median_download\": 0.0,\n", " \"median_radio_RTT\": 97.234571,\n", " \"quality_flagged_records\": 4668,\n", " \"traffic_share_percent\": 0.014167,\n", " \"quality_flagged_percent\": 99.319149,\n", " \"traffic_GB_rank\": 21,\n", " \"quality_flagged_records_rank\": 9,\n", " \"quality_flagged_percent_rank\": 1\n", " },\n", " {\n", " \"application_category\": \"Remote access\",\n", " \"records\": 976,\n", " \"traffic_GB\": 10.58673,\n", " \"median_download\": 0.048467,\n", " \"median_radio_RTT\": 200.671654,\n", " \"quality_flagged_records\": 105,\n", " \"traffic_share_percent\": 0.093185,\n", " \"quality_flagged_percent\": 10.758197,\n", " \"traffic_GB_rank\": 19,\n", " \"quality_flagged_records_rank\": 20,\n", " \"quality_flagged_percent_rank\": 7\n", " },\n", " {\n", " \"application_category\": \"Social\",\n", " \"records\": 108362,\n", " \"traffic_GB\": 2413.738688,\n", " \"median_download\": 0.088749,\n", " \"median_radio_RTT\": 87.306744,\n", " \"quality_flagged_records\": 8244,\n", " \"traffic_share_percent\": 21.245931,\n", " \"quality_flagged_percent\": 7.607833,\n", " \"traffic_GB_rank\": 2,\n", " \"quality_flagged_records_rank\": 5,\n", " \"quality_flagged_percent_rank\": 16\n", " },\n", " {\n", " \"application_category\": \"Speedtest\",\n", " \"records\": 413,\n", " \"traffic_GB\": 4.725507,\n", " \"median_download\": 0.081314,\n", " \"median_radio_RTT\": 52.614473,\n", " \"quality_flagged_records\": 12,\n", " \"traffic_share_percent\": 0.041594,\n", " \"quality_flagged_percent\": 2.905569,\n", " \"traffic_GB_rank\": 20,\n", " \"quality_flagged_records_rank\": 24,\n", " \"quality_flagged_percent_rank\": 23\n", " },\n", " {\n", " \"application_category\": \"Streaming\",\n", " \"records\": 89541,\n", " \"traffic_GB\": 3451.94495,\n", " \"median_download\": 0.105112,\n", " \"median_radio_RTT\": 71.03449,\n", " \"quality_flagged_records\": 5489,\n", " \"traffic_share_percent\": 30.38431,\n", " \"quality_flagged_percent\": 6.130153,\n", " \"traffic_GB_rank\": 1,\n", " \"quality_flagged_records_rank\": 8,\n", " \"quality_flagged_percent_rank\": 19\n", " },\n", " {\n", " \"application_category\": \"Updates\",\n", " \"records\": 19618,\n", " \"traffic_GB\": 470.969755,\n", " \"median_download\": 0.059138,\n", " \"median_radio_RTT\": 92.249095,\n", " \"quality_flagged_records\": 1848,\n", " \"traffic_share_percent\": 4.145515,\n", " \"quality_flagged_percent\": 9.41992,\n", " \"traffic_GB_rank\": 6,\n", " \"quality_flagged_records_rank\": 11,\n", " \"quality_flagged_percent_rank\": 11\n", " },\n", " {\n", " \"application_category\": \"VPN\",\n", " \"records\": 79409,\n", " \"traffic_GB\": 147.486764,\n", " \"median_download\": 0.015305,\n", " \"median_radio_RTT\": 63.776121,\n", " \"quality_flagged_records\": 29542,\n", " \"traffic_share_percent\": 1.298191,\n", " \"quality_flagged_percent\": 37.202332,\n", " \"traffic_GB_rank\": 9,\n", " \"quality_flagged_records_rank\": 2,\n", " \"quality_flagged_percent_rank\": 2\n", " },\n", " {\n", " \"application_category\": \"VoIP\",\n", " \"records\": 130,\n", " \"traffic_GB\": 0.655848,\n", " \"median_download\": 0.077561,\n", " \"median_radio_RTT\": 101.024459,\n", " \"quality_flagged_records\": 19,\n", " \"traffic_share_percent\": 0.005773,\n", " \"quality_flagged_percent\": 14.615385,\n", " \"traffic_GB_rank\": 22,\n", " \"quality_flagged_records_rank\": 23,\n", " \"quality_flagged_percent_rank\": 5\n", " },\n", " {\n", " \"application_category\": \"Web\",\n", " \"records\": 76125,\n", " \"traffic_GB\": 1765.984884,\n", " \"median_download\": 0.098835,\n", " \"median_radio_RTT\": 99.108064,\n", " \"quality_flagged_records\": 6424,\n", " \"traffic_share_percent\": 15.544347,\n", " \"quality_flagged_percent\": 8.438752,\n", " \"traffic_GB_rank\": 3,\n", " \"quality_flagged_records_rank\": 7,\n", " \"quality_flagged_percent_rank\": 14\n", " },\n", " {\n", " \"application_category\": \"Web search\",\n", " \"records\": 54265,\n", " \"traffic_GB\": 43.680559,\n", " \"median_download\": 0.076282,\n", " \"median_radio_RTT\": 55.036266,\n", " \"quality_flagged_records\": 3686,\n", " \"traffic_share_percent\": 0.38448,\n", " \"quality_flagged_percent\": 6.792592,\n", " \"traffic_GB_rank\": 13,\n", " \"quality_flagged_records_rank\": 10,\n", " \"quality_flagged_percent_rank\": 18\n", " }\n", " ]\n", "}\n" ] } ], "source": [ "payload = json.loads('{\"source\": \"Elisa hackathon mock dataset, transformed with c15_window_k5_noise5, seed 42\", \"released_records\": 1097135, \"definitions\": {\"quality_flag\": \"Download below the released-table p10 OR radio RTT above its p90; relative screening proxy, not an SLA breach or observed complaint.\", \"records\": \"Observations, not unique subscribers or customers.\", \"measurement_units\": \"Original supplied measurement scales; physical units are not independently verified.\", \"scope\": \"Six short time buckets; fabricated timestamps. No subscriber identifiers, aliases, station IDs or individual rows are sent.\", \"business_limits\": \"No revenue, price, willingness-to-pay, complaint, churn, causal or representative-population evidence.\"}, \"categories\": [{\"application_category\": \"AI\", \"records\": 3626, \"traffic_GB\": 15.431066, \"median_download\": 0.053602, \"median_radio_RTT\": 97.578677, \"quality_flagged_records\": 387, \"traffic_share_percent\": 0.135826, \"quality_flagged_percent\": 10.672918, \"traffic_GB_rank\": 18, \"quality_flagged_records_rank\": 19, \"quality_flagged_percent_rank\": 8}, {\"application_category\": \"Ads\", \"records\": 29723, \"traffic_GB\": 74.430801, \"median_download\": 0.086811, \"median_radio_RTT\": 88.241837, \"quality_flagged_records\": 1637, \"traffic_share_percent\": 0.655146, \"quality_flagged_percent\": 5.507519, \"traffic_GB_rank\": 12, \"quality_flagged_records_rank\": 14, \"quality_flagged_percent_rank\": 20}, {\"application_category\": \"Analytics\", \"records\": 37683, \"traffic_GB\": 32.383585, \"median_download\": 0.060764, \"median_radio_RTT\": 68.395662, \"quality_flagged_records\": 1404, \"traffic_share_percent\": 0.285043, \"quality_flagged_percent\": 3.725818, \"traffic_GB_rank\": 16, \"quality_flagged_records_rank\": 15, \"quality_flagged_percent_rank\": 22}, {\"application_category\": \"CDN\", \"records\": 56725, \"traffic_GB\": 1272.432857, \"median_download\": 0.080664, \"median_radio_RTT\": 106.574512, \"quality_flagged_records\": 6631, \"traffic_share_percent\": 11.200061, \"quality_flagged_percent\": 11.689731, \"traffic_GB_rank\": 4, \"quality_flagged_records_rank\": 6, \"quality_flagged_percent_rank\": 6}, {\"application_category\": \"Chat\", \"records\": 967, \"traffic_GB\": 0.036769, \"median_download\": 0.041371, \"median_radio_RTT\": 71.3581, \"quality_flagged_records\": 101, \"traffic_share_percent\": 0.000324, \"quality_flagged_percent\": 10.444674, \"traffic_GB_rank\": 25, \"quality_flagged_records_rank\": 21, \"quality_flagged_percent_rank\": 10}, {\"application_category\": \"Cloud services\", \"records\": 109640, \"traffic_GB\": 86.215456, \"median_download\": 0.048069, \"median_radio_RTT\": 65.960008, \"quality_flagged_records\": 9414, \"traffic_share_percent\": 0.758876, \"quality_flagged_percent\": 8.586282, \"traffic_GB_rank\": 11, \"quality_flagged_records_rank\": 4, \"quality_flagged_percent_rank\": 12}, {\"application_category\": \"Email\", \"records\": 39215, \"traffic_GB\": 33.797939, \"median_download\": 0.076001, \"median_radio_RTT\": 66.353398, \"quality_flagged_records\": 1839, \"traffic_share_percent\": 0.297492, \"quality_flagged_percent\": 4.689532, \"traffic_GB_rank\": 15, \"quality_flagged_records_rank\": 13, \"quality_flagged_percent_rank\": 21}, {\"application_category\": \"File mgmt\", \"records\": 25837, \"traffic_GB\": 98.710818, \"median_download\": 0.056874, \"median_radio_RTT\": 90.585769, \"quality_flagged_records\": 1848, \"traffic_share_percent\": 0.868861, \"quality_flagged_percent\": 7.152533, \"traffic_GB_rank\": 10, \"quality_flagged_records_rank\": 11, \"quality_flagged_percent_rank\": 17}, {\"application_category\": \"File transfer\", \"records\": 104, \"traffic_GB\": 0.137517, \"median_download\": 0.057644, \"median_radio_RTT\": 81.214227, \"quality_flagged_records\": 2, \"traffic_share_percent\": 0.00121, \"quality_flagged_percent\": 1.923077, \"traffic_GB_rank\": 23, \"quality_flagged_records_rank\": 25, \"quality_flagged_percent_rank\": 24}, {\"application_category\": \"Gaming\", \"records\": 12129, \"traffic_GB\": 803.878685, \"median_download\": 0.072299, \"median_radio_RTT\": 92.657085, \"quality_flagged_records\": 1036, \"traffic_share_percent\": 7.075808, \"quality_flagged_percent\": 8.541512, \"traffic_GB_rank\": 5, \"quality_flagged_records_rank\": 17, \"quality_flagged_percent_rank\": 13}, {\"application_category\": \"IM\", \"records\": 110883, \"traffic_GB\": 314.289314, \"median_download\": 0.014675, \"median_radio_RTT\": 86.315341, \"quality_flagged_records\": 39533, \"traffic_share_percent\": 2.766401, \"quality_flagged_percent\": 35.652895, \"traffic_GB_rank\": 7, \"quality_flagged_records_rank\": 1, \"quality_flagged_percent_rank\": 3}, {\"application_category\": \"IoT\", \"records\": 588, \"traffic_GB\": 0.098273, \"median_download\": 0.013444, \"median_radio_RTT\": 140.774753, \"quality_flagged_records\": 62, \"traffic_share_percent\": 0.000865, \"quality_flagged_percent\": 10.544218, \"traffic_GB_rank\": 24, \"quality_flagged_records_rank\": 22, \"quality_flagged_percent_rank\": 9}, {\"application_category\": \"Networking\", \"records\": 139543, \"traffic_GB\": 40.024916, \"median_download\": 0.037369, \"median_radio_RTT\": 75.164611, \"quality_flagged_records\": 695, \"traffic_share_percent\": 0.352303, \"quality_flagged_percent\": 0.498054, \"traffic_GB_rank\": 14, \"quality_flagged_records_rank\": 18, \"quality_flagged_percent_rank\": 25}, {\"application_category\": \"News\", \"records\": 16521, \"traffic_GB\": 15.561019, \"median_download\": 0.152315, \"median_radio_RTT\": 82.268747, \"quality_flagged_records\": 1277, \"traffic_share_percent\": 0.136969, \"quality_flagged_percent\": 7.729556, \"traffic_GB_rank\": 17, \"quality_flagged_records_rank\": 16, \"quality_flagged_percent_rank\": 15}, {\"application_category\": \"Other\", \"records\": 80412, \"traffic_GB\": 262.133511, \"median_download\": 0.047881, \"median_radio_RTT\": 131.952641, \"quality_flagged_records\": 17606, \"traffic_share_percent\": 2.307321, \"quality_flagged_percent\": 21.894742, \"traffic_GB_rank\": 8, \"quality_flagged_records_rank\": 3, \"quality_flagged_percent_rank\": 4}, {\"application_category\": \"P2P\", \"records\": 4700, \"traffic_GB\": 1.609552, \"median_download\": 0.0, \"median_radio_RTT\": 97.234571, \"quality_flagged_records\": 4668, \"traffic_share_percent\": 0.014167, \"quality_flagged_percent\": 99.319149, \"traffic_GB_rank\": 21, \"quality_flagged_records_rank\": 9, \"quality_flagged_percent_rank\": 1}, {\"application_category\": \"Remote access\", \"records\": 976, \"traffic_GB\": 10.58673, \"median_download\": 0.048467, \"median_radio_RTT\": 200.671654, \"quality_flagged_records\": 105, \"traffic_share_percent\": 0.093185, \"quality_flagged_percent\": 10.758197, \"traffic_GB_rank\": 19, \"quality_flagged_records_rank\": 20, \"quality_flagged_percent_rank\": 7}, {\"application_category\": \"Social\", \"records\": 108362, \"traffic_GB\": 2413.738688, \"median_download\": 0.088749, \"median_radio_RTT\": 87.306744, \"quality_flagged_records\": 8244, \"traffic_share_percent\": 21.245931, \"quality_flagged_percent\": 7.607833, \"traffic_GB_rank\": 2, \"quality_flagged_records_rank\": 5, \"quality_flagged_percent_rank\": 16}, {\"application_category\": \"Speedtest\", \"records\": 413, \"traffic_GB\": 4.725507, \"median_download\": 0.081314, \"median_radio_RTT\": 52.614473, \"quality_flagged_records\": 12, \"traffic_share_percent\": 0.041594, \"quality_flagged_percent\": 2.905569, \"traffic_GB_rank\": 20, \"quality_flagged_records_rank\": 24, \"quality_flagged_percent_rank\": 23}, {\"application_category\": \"Streaming\", \"records\": 89541, \"traffic_GB\": 3451.94495, \"median_download\": 0.105112, \"median_radio_RTT\": 71.03449, \"quality_flagged_records\": 5489, \"traffic_share_percent\": 30.38431, \"quality_flagged_percent\": 6.130153, \"traffic_GB_rank\": 1, \"quality_flagged_records_rank\": 8, \"quality_flagged_percent_rank\": 19}, {\"application_category\": \"Updates\", \"records\": 19618, \"traffic_GB\": 470.969755, \"median_download\": 0.059138, \"median_radio_RTT\": 92.249095, \"quality_flagged_records\": 1848, \"traffic_share_percent\": 4.145515, \"quality_flagged_percent\": 9.41992, \"traffic_GB_rank\": 6, \"quality_flagged_records_rank\": 11, \"quality_flagged_percent_rank\": 11}, {\"application_category\": \"VPN\", \"records\": 79409, \"traffic_GB\": 147.486764, \"median_download\": 0.015305, \"median_radio_RTT\": 63.776121, \"quality_flagged_records\": 29542, \"traffic_share_percent\": 1.298191, \"quality_flagged_percent\": 37.202332, \"traffic_GB_rank\": 9, \"quality_flagged_records_rank\": 2, \"quality_flagged_percent_rank\": 2}, {\"application_category\": \"VoIP\", \"records\": 130, \"traffic_GB\": 0.655848, \"median_download\": 0.077561, \"median_radio_RTT\": 101.024459, \"quality_flagged_records\": 19, \"traffic_share_percent\": 0.005773, \"quality_flagged_percent\": 14.615385, \"traffic_GB_rank\": 22, \"quality_flagged_records_rank\": 23, \"quality_flagged_percent_rank\": 5}, {\"application_category\": \"Web\", \"records\": 76125, \"traffic_GB\": 1765.984884, \"median_download\": 0.098835, \"median_radio_RTT\": 99.108064, \"quality_flagged_records\": 6424, \"traffic_share_percent\": 15.544347, \"quality_flagged_percent\": 8.438752, \"traffic_GB_rank\": 3, \"quality_flagged_records_rank\": 7, \"quality_flagged_percent_rank\": 14}, {\"application_category\": \"Web search\", \"records\": 54265, \"traffic_GB\": 43.680559, \"median_download\": 0.076282, \"median_radio_RTT\": 55.036266, \"quality_flagged_records\": 3686, \"traffic_share_percent\": 0.38448, \"quality_flagged_percent\": 6.792592, \"traffic_GB_rank\": 13, \"quality_flagged_records_rank\": 10, \"quality_flagged_percent_rank\": 18}]}')\n", "print(json.dumps(payload, indent=2))" ] }, { "cell_type": "markdown", "id": "c60da2eb", "metadata": { "tags": [ "llm-appendix" ] }, "source": [ "### Elina’s question\n", "\n", "> Using the attached data, where should Elisa focus gaming or streaming product development and app-quality improvements, and why?" ] }, { "cell_type": "code", "execution_count": 12, "id": "fff6b0de", "metadata": { "execution": { "iopub.execute_input": "2026-09-20T09:20:04.986237Z", "iopub.status.busy": "2026-09-20T09:20:04.986125Z", "iopub.status.idle": "2026-09-20T09:20:04.988033Z", "shell.execute_reply": "2026-09-20T09:20:04.987669Z" }, "tags": [ "llm-appendix" ] }, "outputs": [], "source": [ "prompt = 'Using the attached data, where should Elisa focus gaming or streaming product development and app-quality improvements, and why?'" ] }, { "cell_type": "code", "execution_count": 13, "id": "3a5532b1", "metadata": { "execution": { "iopub.execute_input": "2026-09-20T09:20:04.989049Z", "iopub.status.busy": "2026-09-20T09:20:04.988940Z", "iopub.status.idle": "2026-09-20T09:20:08.502076Z", "shell.execute_reply": "2026-09-20T09:20:08.501456Z" }, "tags": [ "llm-appendix" ] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Fresh Mistral response (RUN_AI enabled):\n", "**1. Gaming versus streaming product exploration**\n", "Evidence: Streaming has 30.4% traffic share (rank 1) and 6.1% flagged rate (rank 19); Gaming has 7.1% traffic share (rank 5) and 8.5% flagged rate (rank 13).\n", "Possible value: Validating a gaming-focused product bundle could differentiate Elisa’s offering.\n", "Next check: Establish customer demand for gaming-specific features and normal workload behaviour.\n", "\n", "**2. Application-quality investigation: Instant Messaging (IM)**\n", "Evidence: IM has 35.7% flagged records (rank 3) and 39,533 flagged observations (rank 1).\n", "Possible value: Prioritising IM service-improvement spending may enhance user experience.\n", "Next check: Identify IM-specific quality criteria and implementation costs.\n" ] } ], "source": [ "cache_file = ROOT / \"notebooks\" / \"llm\" / \"saved_answer.json\"\n", "payload_hash = hashlib.sha256(json.dumps(payload, sort_keys=True).encode()).hexdigest()\n", "if RUN_AI:\n", " from llm.analyst import interpret\n", "\n", " answer, generation = interpret(payload, prompt)\n", " generation.update(\n", " generated_at_utc=datetime.now(UTC).isoformat(),\n", " payload_sha256=payload_hash,\n", " )\n", " cache_file.write_text(json.dumps({\n", " \"question\": prompt, \"answer\": answer, \"generation\": generation,\n", " }, indent=2) + \"\\n\")\n", " print(\"Fresh Mistral response (RUN_AI enabled):\")\n", "else:\n", " saved = json.loads(cache_file.read_text())\n", " if saved[\"question\"] != prompt or saved[\"generation\"][\"payload_sha256\"] != payload_hash:\n", " raise ValueError(\"Saved answer does not match this question/data; enable RUN_AI to refresh\")\n", " answer, generation = saved[\"answer\"], saved[\"generation\"]\n", " print(\"Saved Mistral response (RUN_AI disabled; no API call):\")\n", "print(answer)\n" ] }, { "cell_type": "code", "execution_count": 14, "id": "27d9eea4", "metadata": { "execution": { "iopub.execute_input": "2026-09-20T09:20:08.503698Z", "iopub.status.busy": "2026-09-20T09:20:08.503583Z", "iopub.status.idle": "2026-09-20T09:20:08.505654Z", "shell.execute_reply": "2026-09-20T09:20:08.505274Z" }, "tags": [ "llm-appendix" ] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "{\n", " \"model\": \"mistralai/Mistral-Large-3-675B-Instruct-2512-NVFP4\",\n", " \"usage\": {\n", " \"completion_tokens\": 182,\n", " \"prompt_tokens\": 4293,\n", " \"total_tokens\": 4475,\n", " \"completion_tokens_details\": null,\n", " \"prompt_tokens_details\": null\n", " },\n", " \"finish_reason\": \"stop\",\n", " \"generated_at_utc\": \"2026-09-20T09:20:08.499155+00:00\",\n", " \"payload_sha256\": \"c09c0399f3f27fe0a8f7b27bc6c9ab7e751784b53341a9927f199098a3728cfc\"\n", "}\n" ] } ], "source": [ "print(json.dumps(generation, indent=2))" ] }, { "cell_type": "markdown", "id": "3064e5a4", "metadata": { "tags": [ "llm-appendix" ] }, "source": [ "**Interpretation needs review.** Counts are observations, not customers; relative flags do not establish service faults. Product and revenue suggestions are hypotheses to validate." ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.14.6" } }, "nbformat": 4, "nbformat_minor": 5 }