sthoumyre-ensae/Project_Carmignac
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
branche_sarah
Project_Carmignac/brouillon/repair.ipynb

4322 lines
358 KiB
Plaintext
Raw Permalink Normal View History

global clustering
2026-04-05 17:52:42 +02:00
{
"cells": [
{
"cell_type": "code",
"execution_count": 1,
"id": "29f7e620-7b04-45f6-ac87-f17505f140c3",
"metadata": {},
"outputs": [],
"source": [
"import pandas as pd\n",
"import numpy as np\n",
"import plotly.graph_objects as go\n",
"import matplotlib.pyplot as plt"
]
},
{
"cell_type": "code",
"execution_count": 6,
"id": "a48ad016-e4f2-40d9-a607-344a316f5f02",
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"\n",
"KeyboardInterrupt\n",
"\n"
]
}
],
"source": [
"stocks = pd.read_csv(\"stocks.csv\")\n",
"flows = pd.read_csv(\"flows.csv\")\n",
"\n",
"stocks[\"Centralisation Date\"] = pd.to_datetime(stocks[\"Centralisation Date\"])\n",
"flows[\"Centralisation Date\"] = pd.to_datetime(flows[\"Centralisation Date\"])"
]
},
{
"cell_type": "code",
"execution_count": 11,
"id": "221a4c7b-0f50-431a-875b-ad40bed7f0ac",
"metadata": {},
"outputs": [],
"source": [
"import os\n",
"import s3fs\n",
"os.environ[\"AWS_ACCESS_KEY_ID\"] = 'N0C5PK75FDX2WXI8OVP1'\n",
"os.environ[\"AWS_SECRET_ACCESS_KEY\"] = 'nZvC2urUkG7EvhDsFDyaOslqr160aoWMs+5MP3Ft'\n",
"os.environ[\"AWS_SESSION_TOKEN\"] = 'eyJhbGciOiJIUzUxMiIsInR5cCI6IkpXVCJ9.eyJhY2Nlc3NLZXkiOiJOMEM1UEs3NUZEWDJXWEk4T1ZQMSIsImFjciI6IjAiLCJhbGxvd2VkLW9yaWdpbnMiOlsiKiJdLCJhdWQiOlsibWluaW8iLCJhY2NvdW50Il0sImF1dGhfdGltZSI6MTc3MzIyNzI3OCwiYXpwIjoib255eGlhLW1pbmlvIiwiZW1haWwiOiJzYXJhaC50aG91bXlyZUBlbnNhZS5mciIsImVtYWlsX3ZlcmlmaWVkIjp0cnVlLCJleHAiOjE3NzQ0MzY4OTksImZhbWlseV9uYW1lIjoiVEhPVU1ZUkUiLCJnaXZlbl9uYW1lIjoiU2FyYWgiLCJncm91cHMiOlsiYmRjLWRhdGEiLCJiZGMtY2FybWlnbmFjLWczIl0sImlhdCI6MTc3MzIyNzI5OSwiaXNzIjoiaHR0cHM6Ly9hdXRoLmdyb3VwZS1nZW5lcy5mci9yZWFsbXMvZ2VuZXMiLCJqdGkiOiI5Mjc0ODgyMy04OTgzLTQzYjktYTZhNy0xYjhlNDdiOTRjNTUiLCJuYW1lIjoiU2FyYWggVEhPVU1ZUkUiLCJwb2xpY3kiOiJzdHNvbmx5IiwicHJlZmVycmVkX3VzZXJuYW1lIjoic3Rob3VteXJlLWVuc2FlIiwicmVhbG1fYWNjZXNzIjp7InJvbGVzIjpbIm9mZmxpbmVfYWNjZXNzIiwiZGVmYXVsdC1yb2xlcy1nZW5lcyIsInVtYV9hdXRob3JpemF0aW9uIl19LCJyZXNvdXJjZV9hY2Nlc3MiOnsiYWNjb3VudCI6eyJyb2xlcyI6WyJtYW5hZ2UtYWNjb3VudCIsIm1hbmFnZS1hY2NvdW50LWxpbmtzIiwidmlldy1wcm9maWxlIl19fSwic2NvcGUiOiJvcGVuaWQgcHJvZmlsZSBlbWFpbCIsInNpZCI6IjRkODM3NWVmLTQwY2QtNDYyMi05NzIyLTI4YjhjZTQ2MWQ5YyIsInN1YiI6ImVhYWVkN2QyLWM4MjYtNGIxNC05MzczLTYwYjNhODhlMWFiNiIsInR5cCI6IkJlYXJlciJ9.hl_SekvaH9A22PMb3W0VQBSNO67LnaneIuLC-X5XBnzOO5GLV61aocDRfYC6hvVVhdzyewSTtD2kvdyJdeu6qA'\n",
"os.environ[\"AWS_DEFAULT_REGION\"] = 'us-east-1'\n",
"fs = s3fs.S3FileSystem(\n",
" client_kwargs={'endpoint_url': 'https://'+'minio-simple.lab.groupe-genes.fr'},\n",
" key = os.environ[\"AWS_ACCESS_KEY_ID\"], \n",
" secret = os.environ[\"AWS_SECRET_ACCESS_KEY\"], \n",
" token = os.environ[\"AWS_SESSION_TOKEN\"])\n"
]
},
{
"cell_type": "code",
"execution_count": 9,
"id": "87505949-ecd8-4fad-a19b-d29130be587e",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Index(['Registrar Account - ID', 'Product - Isin', 'Centralisation Date',\n",
" 'Quantity - AUM', 'corrected_aum', 'repair_flag'],\n",
" dtype='object')\n"
]
}
],
"source": [
"print(stocks.columns)"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "3c6d9d05-b203-49ae-869f-7f85ead2c69e",
"metadata": {},
"outputs": [],
"source": [
"keys = [\n",
" \"Registrar Account - ID\",\n",
" \"Product - Isin\",\n",
" \"Centralisation Date\"\n",
"]\n",
"\n",
"stocks = stocks[keys + [\"Quantity - AUM\"]]\n",
"\n",
"flows = flows[keys + [\"Quantity - NetFlows\"]]\n",
"\n",
"flows = (\n",
" flows\n",
" .groupby(keys, as_index=False)\n",
" .sum()\n",
")"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "d30c2235-281b-41a6-828b-abb6fcfc4183",
"metadata": {},
"outputs": [],
"source": [
"df = stocks.merge(flows, on=keys, how=\"left\")\n",
"\n",
"df[\"Quantity - NetFlows\"] = df[\"Quantity - NetFlows\"].fillna(0)\n",
"\n",
"df = df.sort_values(keys)\n",
"\n",
"\n",
"\n",
"# REBUILD ACCOUNTING IDENTITY\n",
"\n",
"\n",
"df[\"prev_aum\"] = df.groupby(\n",
" [\"Registrar Account - ID\",\"Product - Isin\"]\n",
")[\"Quantity - AUM\"].shift(1)\n",
"\n",
"df[\"prev_flow\"] = df.groupby(\n",
" [\"Registrar Account - ID\",\"Product - Isin\"]\n",
")[\"Quantity - NetFlows\"].shift(1).fillna(0)\n",
"\n",
"df[\"expected_aum\"] = df[\"prev_aum\"] + df[\"prev_flow\"]\n",
"\n",
"\n",
"\n",
"# GAP ANALYSIS\n",
"\n",
"\n",
"df[\"gap\"] = df[\"Quantity - AUM\"] - df[\"expected_aum\"]\n",
"df[\"gap_abs\"] = df[\"gap\"].abs()\n",
"\n",
"EPS = 10\n",
"\n",
"df[\"rupture_flag\"] = (\n",
" df[\"prev_aum\"].notna()\n",
" & (df[\"gap_abs\"] > EPS)\n",
")\n",
"\n",
"\n",
"\n",
"# PARAMETERS\n",
"\n",
"\n",
"GAP_TOL = 1e-6\n",
"REL_GAP_THR = 0.05\n",
"MIN_PERSISTENCE = 3\n",
"\n",
"\n",
"\n",
"# SORT DATA\n",
"\n",
"\n",
"df = df.sort_values(\n",
" [\"Registrar Account - ID\", \"Product - Isin\", \"Centralisation Date\"]\n",
")\n",
"\n",
"df[\"corrected_aum\"] = df[\"Quantity - AUM\"]\n",
"df[\"repair_flag\"] = False\n",
"\n",
"\n",
"# REBUILD EXPECTED AUM BEFORE REPAIR\n",
"\n",
"\n",
"df = df.sort_values(\n",
" [\"Registrar Account - ID\", \"Product - Isin\", \"Centralisation Date\"]\n",
")\n",
"\n",
"df[\"prev_aum\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - AUM\"]\n",
" .shift(1)\n",
")\n",
"\n",
"df[\"prev_flow\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - NetFlows\"]\n",
" .shift(1)\n",
" .fillna(0)\n",
")\n",
"\n",
"df[\"expected_stock\"] = df[\"prev_aum\"] + df[\"prev_flow\"]\n",
"\n",
"#delete negative AUM\n",
"df = df[df[\"Quantity - AUM\"] >= 0]"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "efd374d0-6393-45f2-926e-2c29249cd078",
"metadata": {},
"outputs": [],
"source": [
"def repair_group(g):\n",
"\n",
" g = g.copy()\n",
"\n",
" obs = g[\"Quantity - AUM\"].values\n",
" flows = g[\"Quantity - NetFlows\"].values\n",
"\n",
" corrected = obs.copy()\n",
"\n",
" \n",
" # Build expected AUM path\n",
" \n",
"\n",
" expected = np.empty_like(obs)\n",
" expected[0] = np.nan\n",
"\n",
" for t in range(1, len(obs)):\n",
" expected[t] = corrected[t-1] + flows[t-1]\n",
"\n",
" gap = obs - expected\n",
"\n",
" rel_gap = np.abs(gap) / np.maximum(np.abs(expected), 1.0)\n",
"\n",
" idx = None\n",
"\n",
" \n",
" # Detect persistent shift\n",
" \n",
"\n",
" for i in range(1, len(obs) - MIN_PERSISTENCE):\n",
"\n",
" if (\n",
" rel_gap[i] > REL_GAP_THR\n",
" and np.all(np.abs(gap[i:i+MIN_PERSISTENCE] - gap[i]) < GAP_TOL)\n",
" and np.all(np.abs(np.diff(flows[i:i+MIN_PERSISTENCE])) < GAP_TOL)\n",
" ):\n",
" idx = i\n",
" break\n",
"\n",
" if idx is None:\n",
" return g\n",
"\n",
" \n",
" # Compute shift\n",
" \n",
"\n",
" shift = gap[idx]\n",
"\n",
" candidate = obs[idx:] - shift\n",
"\n",
" \n",
" # SAFETY CHECKS\n",
" \n",
"\n",
" # 1. do not allow negative AUM\n",
" # refuse repair if it creates NEW negative AUM\n",
" if ((candidate < 0) & (obs[idx:] >= 0)).any():\n",
" return g\n",
"\n",
" # 2. avoid extreme corrections\n",
" if abs(shift) > 2 * np.nanmax(np.abs(obs)):\n",
" return g\n",
"\n",
" \n",
" # Apply correction\n",
" \n",
"\n",
" corrected[idx:] = candidate\n",
"\n",
" g.loc[g.index[idx]:, \"repair_flag\"] = True\n",
"\n",
" \n",
" # Rebuild expected path after repair\n",
" \n",
"\n",
" expected_corr = np.empty_like(obs)\n",
" expected_corr[0] = np.nan\n",
"\n",
" for t in range(1, len(obs)):\n",
" expected_corr[t] = corrected[t-1] + flows[t-1]\n",
"\n",
" g[\"corrected_aum\"] = corrected\n",
" g[\"expected_stock_corr\"] = expected_corr\n",
"\n",
" return g"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "fe1f869c-0a00-47e0-9355-3705b23561c7",
"metadata": {},
"outputs": [],
"source": [
"def repair_group(g):\n",
"\n",
" g = g.copy()\n",
"\n",
" obs = g[\"Quantity - AUM\"].values\n",
" flows = g[\"Quantity - NetFlows\"].values\n",
"\n",
" corrected = obs.copy()\n",
"\n",
" # Build expected AUM path\n",
" expected = np.empty_like(obs)\n",
" expected[0] = np.nan\n",
"\n",
" for t in range(1, len(obs)):\n",
" expected[t] = corrected[t-1] + flows[t-1]\n",
"\n",
" gap = obs - expected\n",
" rel_gap = np.abs(gap) / np.maximum(np.abs(expected), 1.0)\n",
"\n",
" idx = None\n",
" shift = None\n",
"\n",
" for i in range(1, len(obs) - MIN_PERSISTENCE - 1):\n",
"\n",
" # ------------------------------------------------\n",
" # CASE 1 — standard persistent shift (original algo)\n",
" # ------------------------------------------------\n",
" if (\n",
" rel_gap[i] > REL_GAP_THR\n",
" and np.all(np.abs(gap[i:i+MIN_PERSISTENCE] - gap[i]) < GAP_TOL)\n",
" and np.all(np.abs(np.diff(flows[i:i+MIN_PERSISTENCE])) < GAP_TOL)\n",
" ):\n",
" idx = i\n",
" shift = gap[i]\n",
" break\n",
"\n",
" # ------------------------------------------------\n",
" # CASE 2 — double shift\n",
" # ------------------------------------------------\n",
" if (\n",
" rel_gap[i] > REL_GAP_THR\n",
" and rel_gap[i+1] > REL_GAP_THR\n",
" and np.all(np.abs(gap[i+1:i+1+MIN_PERSISTENCE] - gap[i+1]) < GAP_TOL)\n",
" and np.all(np.abs(np.diff(flows[i+1:i+1+MIN_PERSISTENCE])) < GAP_TOL)\n",
" ):\n",
" idx = i\n",
" shift = gap[i+1]\n",
" break\n",
"\n",
" if idx is None:\n",
" return g\n",
"\n",
" # Apply shift\n",
" candidate = obs[idx:] - shift\n",
"\n",
" # Safety checks\n",
"\n",
" # avoid creating new negative AUM\n",
" if ((candidate < 0) & (obs[idx:] >= 0)).any():\n",
" return g\n",
"\n",
" # avoid extreme corrections\n",
" if abs(shift) > 2 * np.nanmax(np.abs(obs)):\n",
" return g\n",
"\n",
" corrected[idx:] = candidate\n",
"\n",
" g.loc[g.index[idx]:, \"repair_flag\"] = True\n",
"\n",
" # rebuild expected path after repair\n",
" expected_corr = np.empty_like(obs)\n",
" expected_corr[0] = np.nan\n",
"\n",
" for t in range(1, len(obs)):\n",
" expected_corr[t] = corrected[t-1] + flows[t-1]\n",
"\n",
" g[\"corrected_aum\"] = corrected\n",
" g[\"expected_stock_corr\"] = expected_corr\n",
"\n",
" return g"
]
},
{
"cell_type": "code",
"execution_count": 49,
"id": "1ca2a5ab-354f-49af-b1aa-75c93d48de06",
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"/tmp/ipykernel_5465/3637915452.py:8: DtypeWarning:\n",
"\n",
"Columns (0,1,2,3) have mixed types. Specify dtype option on import or set low_memory=False.\n",
"\n",
"/tmp/ipykernel_5465/3637915452.py:14: DtypeWarning:\n",
"\n",
"Columns (0,1,2,3) have mixed types. Specify dtype option on import or set low_memory=False.\n",
"\n",
"/tmp/ipykernel_5465/3637915452.py:125: FutureWarning:\n",
"\n",
"DataFrameGroupBy.apply operated on the grouping columns. This behavior is deprecated, and in a future version of pandas the grouping columns will be excluded from the operation. Either pass `include_groups=False` to exclude the groupings or explicitly select the grouping columns after groupby to silence this warning.\n",
"\n"
]
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"stock_repaired.csv successfully created\n"
]
}
],
"source": [
"#FULL STOCK REPAIR\n",
"\n",
"# ============================================================\n",
"# LOAD DATA\n",
"# ============================================================\n",
"\n",
"with fs.open('projet-bdc-data/carmignac/AUM ENSAE V2 -20251105.csv', 'rb') as f:\n",
" stocks = pd.read_csv(f, sep=\";\")\n",
"\n",
"with fs.open(\n",
" \"projet-bdc-data/carmignac/Flows ENSAE V2 -20251105.csv\",\n",
" \"rb\"\n",
") as f:\n",
" flows = pd.read_csv(f, sep=\";\")\n",
"\n",
"stocks[\"Centralisation Date\"] = pd.to_datetime(stocks[\"Centralisation Date\"])\n",
"flows[\"Centralisation Date\"] = pd.to_datetime(flows[\"Centralisation Date\"])\n",
"\n",
"# ============================================================\n",
"# MERGE FLOWS\n",
"# ============================================================\n",
"\n",
"df = stocks.merge(\n",
" flows,\n",
" on=[\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"],\n",
" how=\"left\"\n",
")\n",
"\n",
"df[\"Quantity - NetFlows\"] = df[\"Quantity - NetFlows\"].fillna(0)\n",
"\n",
"df = df.sort_values(\n",
" [\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"]\n",
")\n",
"\n",
"# ============================================================\n",
"# PARAMETERS\n",
"# ============================================================\n",
"\n",
"REL_GAP_THR = 0.05\n",
"MIN_PERSISTENCE = 3\n",
"GAP_TOL = 1e-6\n",
"\n",
"# ============================================================\n",
"# REPAIR FUNCTION\n",
"# ============================================================\n",
"\n",
"def repair_group(g):\n",
"\n",
" g = g.copy()\n",
"\n",
" obs = g[\"Quantity - AUM\"].values\n",
" flows = g[\"Quantity - NetFlows\"].values\n",
"\n",
" corrected = obs.copy()\n",
"\n",
" expected = np.empty_like(obs)\n",
" expected[0] = np.nan\n",
"\n",
" for t in range(1, len(obs)):\n",
" expected[t] = corrected[t-1] + flows[t-1]\n",
"\n",
" gap = obs - expected\n",
" rel_gap = np.abs(gap) / np.maximum(np.abs(expected), 1.0)\n",
"\n",
" idx = None\n",
" shift = None\n",
"\n",
" for i in range(1, len(obs) - MIN_PERSISTENCE - 1):\n",
"\n",
" # CASE 1 — persistent shift\n",
" if (\n",
" rel_gap[i] > REL_GAP_THR\n",
" and np.all(np.abs(gap[i:i+MIN_PERSISTENCE] - gap[i]) < GAP_TOL)\n",
" and np.all(np.abs(np.diff(flows[i:i+MIN_PERSISTENCE])) < GAP_TOL)\n",
" ):\n",
" idx = i\n",
" shift = gap[i]\n",
" break\n",
"\n",
" # CASE 2 — double shift\n",
" if (\n",
" rel_gap[i] > REL_GAP_THR\n",
" and rel_gap[i+1] > REL_GAP_THR\n",
" and np.all(np.abs(gap[i+1:i+1+MIN_PERSISTENCE] - gap[i+1]) < GAP_TOL)\n",
" and np.all(np.abs(np.diff(flows[i+1:i+1+MIN_PERSISTENCE])) < GAP_TOL)\n",
" ):\n",
" idx = i\n",
" shift = gap[i+1]\n",
" break\n",
"\n",
" if idx is None:\n",
" return g\n",
"\n",
" candidate = obs[idx:] - shift\n",
"\n",
" if ((candidate < 0) & (obs[idx:] >= 0)).any():\n",
" return g\n",
"\n",
" if abs(shift) > 2 * np.nanmax(np.abs(obs)):\n",
" return g\n",
"\n",
" corrected[idx:] = candidate\n",
"\n",
" g.loc[g.index[idx]:, \"repair_flag\"] = True\n",
"\n",
" expected_corr = np.empty_like(obs)\n",
" expected_corr[0] = np.nan\n",
"\n",
" for t in range(1, len(obs)):\n",
" expected_corr[t] = corrected[t-1] + flows[t-1]\n",
"\n",
" g[\"corrected_aum\"] = corrected\n",
"\n",
" return g\n",
"\n",
"# ============================================================\n",
"# APPLY REPAIR\n",
"# ============================================================\n",
"\n",
"df_repaired = (\n",
" df.groupby(\n",
" [\"Registrar Account - ID\",\"Product - Isin\"],\n",
" group_keys=False\n",
" )\n",
" .apply(repair_group)\n",
")\n",
"\n",
"# ============================================================\n",
"# REBUILD STOCK FILE\n",
"# ============================================================\n",
"\n",
"stocks_repaired = stocks.copy()\n",
"\n",
"stocks_repaired[\"Quantity - AUM\"] = df_repaired[\"corrected_aum\"].fillna(\n",
" stocks[\"Quantity - AUM\"]\n",
")\n",
"\n",
"# ============================================================\n",
"# SAVE WITH ORIGINAL FORMAT\n",
"# ============================================================\n",
"\n",
"stocks_repaired.to_csv(\n",
" \"stock_repaired.csv\",\n",
" sep=\";\",\n",
" index=False\n",
")\n",
"\n",
"print(\"stock_repaired.csv successfully created\")"
]
},
{
"cell_type": "code",
"execution_count": 15,
"id": "f94f07b4-e053-4828-bbb1-3697f9a11751",
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"/tmp/ipykernel_5465/3656779442.py:4: FutureWarning: DataFrameGroupBy.apply operated on the grouping columns. This behavior is deprecated, and in a future version of pandas the grouping columns will be excluded from the operation. Either pass `include_groups=False` to exclude the groupings or explicitly select the grouping columns after groupby to silence this warning.\n",
" .apply(repair_group)\n"
]
},
{
"ename": "KeyError",
"evalue": "'expected_stock_corr'",
"output_type": "error",
"traceback": [
"\u001b[31m---------------------------------------------------------------------------\u001b[39m",
"\u001b[31mKeyError\u001b[39m Traceback (most recent call last)",
"\u001b[36mFile \u001b[39m\u001b[32m/opt/python/lib/python3.13/site-packages/pandas/core/indexes/base.py:3812\u001b[39m, in \u001b[36mIndex.get_loc\u001b[39m\u001b[34m(self, key)\u001b[39m\n\u001b[32m 3811\u001b[39m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[32m-> \u001b[39m\u001b[32m3812\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[43m.\u001b[49m\u001b[43m_engine\u001b[49m\u001b[43m.\u001b[49m\u001b[43mget_loc\u001b[49m\u001b[43m(\u001b[49m\u001b[43mcasted_key\u001b[49m\u001b[43m)\u001b[49m\n\u001b[32m 3813\u001b[39m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m err:\n",
"\u001b[36mFile \u001b[39m\u001b[32mpandas/_libs/index.pyx:167\u001b[39m, in \u001b[36mpandas._libs.index.IndexEngine.get_loc\u001b[39m\u001b[34m()\u001b[39m\n",
"\u001b[36mFile \u001b[39m\u001b[32mpandas/_libs/index.pyx:196\u001b[39m, in \u001b[36mpandas._libs.index.IndexEngine.get_loc\u001b[39m\u001b[34m()\u001b[39m\n",
"\u001b[36mFile \u001b[39m\u001b[32mpandas/_libs/hashtable_class_helper.pxi:7088\u001b[39m, in \u001b[36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[39m\u001b[34m()\u001b[39m\n",
"\u001b[36mFile \u001b[39m\u001b[32mpandas/_libs/hashtable_class_helper.pxi:7096\u001b[39m, in \u001b[36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[39m\u001b[34m()\u001b[39m\n",
"\u001b[31mKeyError\u001b[39m: 'expected_stock_corr'",
"\nThe above exception was the direct cause of the following exception:\n",
"\u001b[31mKeyError\u001b[39m Traceback (most recent call last)",
"\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[15]\u001b[39m\u001b[32m, line 25\u001b[39m\n\u001b[32m 22\u001b[39m df[\u001b[33m\"\u001b[39m\u001b[33mexpected_stock\u001b[39m\u001b[33m\"\u001b[39m] = df[\u001b[33m\"\u001b[39m\u001b[33mprev_aum\u001b[39m\u001b[33m\"\u001b[39m] + df[\u001b[33m\"\u001b[39m\u001b[33mprev_flow\u001b[39m\u001b[33m\"\u001b[39m]\n\u001b[32m 23\u001b[39m df[\u001b[33m\"\u001b[39m\u001b[33mgap_before\u001b[39m\u001b[33m\"\u001b[39m] = df[\u001b[33m\"\u001b[39m\u001b[33mQuantity - AUM\u001b[39m\u001b[33m\"\u001b[39m] - df[\u001b[33m\"\u001b[39m\u001b[33mexpected_stock\u001b[39m\u001b[33m\"\u001b[39m]\n\u001b[32m---> \u001b[39m\u001b[32m25\u001b[39m df[\u001b[33m\"\u001b[39m\u001b[33mgap_after\u001b[39m\u001b[33m\"\u001b[39m] = df[\u001b[33m\"\u001b[39m\u001b[33mcorrected_aum\u001b[39m\u001b[33m\"\u001b[39m] - \u001b[43mdf\u001b[49m\u001b[43m[\u001b[49m\u001b[33;43m\"\u001b[39;49m\u001b[33;43mexpected_stock_corr\u001b[39;49m\u001b[33;43m\"\u001b[39;49m\u001b[43m]\u001b[49m\n\u001b[32m 27\u001b[39m df[\u001b[33m\"\u001b[39m\u001b[33mrupture_before\u001b[39m\u001b[33m\"\u001b[39m] = df[\u001b[33m\"\u001b[39m\u001b[33mgap_before\u001b[39m\u001b[33m\"\u001b[39m].abs() > GAP_TOL\n\u001b[32m 29\u001b[39m df[\u001b[33m\"\u001b[39m\u001b[33mrupture_after\u001b[39m\u001b[33m\"\u001b[39m] = df[\u001b[33m\"\u001b[39m\u001b[33mgap_after\u001b[39m\u001b[33m\"\u001b[39m].abs() > GAP_TOL\n",
"\u001b[36mFile \u001b[39m\u001b[32m/opt/python/lib/python3.13/site-packages/pandas/core/frame.py:4113\u001b[39m, in \u001b[36mDataFrame.__getitem__\u001b[39m\u001b[34m(self, key)\u001b[39m\n\u001b[32m 4111\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m.columns.nlevels > \u001b[32m1\u001b[39m:\n\u001b[32m 4112\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m._getitem_multilevel(key)\n\u001b[32m-> \u001b[39m\u001b[32m4113\u001b[39m indexer = \u001b[38;5;28;43mself\u001b[39;49m\u001b[43m.\u001b[49m\u001b[43mcolumns\u001b[49m\u001b[43m.\u001b[49m\u001b[43mget_loc\u001b[49m\u001b[43m(\u001b[49m\u001b[43mkey\u001b[49m\u001b[43m)\u001b[49m\n\u001b[32m 4114\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m is_integer(indexer):\n\u001b[32m 4115\u001b[39m indexer = [indexer]\n",
"\u001b[36mFile \u001b[39m\u001b[32m/opt/python/lib/python3.13/site-packages/pandas/core/indexes/base.py:3819\u001b[39m, in \u001b[36mIndex.get_loc\u001b[39m\u001b[34m(self, key)\u001b[39m\n\u001b[32m 3814\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(casted_key, \u001b[38;5;28mslice\u001b[39m) \u001b[38;5;129;01mor\u001b[39;00m (\n\u001b[32m 3815\u001b[39m \u001b[38;5;28misinstance\u001b[39m(casted_key, abc.Iterable)\n\u001b[32m 3816\u001b[39m \u001b[38;5;129;01mand\u001b[39;00m \u001b[38;5;28many\u001b[39m(\u001b[38;5;28misinstance\u001b[39m(x, \u001b[38;5;28mslice\u001b[39m) \u001b[38;5;28;01mfor\u001b[39;00m x \u001b[38;5;129;01min\u001b[39;00m casted_key)\n\u001b[32m 3817\u001b[39m ):\n\u001b[32m 3818\u001b[39m \u001b[38;5;28;01mraise\u001b[39;00m InvalidIndexError(key)\n\u001b[32m-> \u001b[39m\u001b[32m3819\u001b[39m \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m(key) \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34;01merr\u001b[39;00m\n\u001b[32m 3820\u001b[39m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mTypeError\u001b[39;00m:\n\u001b[32m 3821\u001b[39m \u001b[38;5;66;03m# If we have a listlike key, _check_indexing_error will raise\u001b[39;00m\n\u001b[32m 3822\u001b[39m \u001b[38;5;66;03m# InvalidIndexError. Otherwise we fall through and re-raise\u001b[39;00m\n\u001b[32m 3823\u001b[39m \u001b[38;5;66;03m# the TypeError.\u001b[39;00m\n\u001b[32m 3824\u001b[39m \u001b[38;5;28mself\u001b[39m._check_indexing_error(key)\n",
"\u001b[31mKeyError\u001b[39m: 'expected_stock_corr'"
]
}
],
"source": [
"df = (\n",
" df\n",
" .groupby([\"Registrar Account - ID\", \"Product - Isin\"], group_keys=False)\n",
" .apply(repair_group)\n",
")\n",
"\n",
"# VALIDATION BEFORE / AFTER\n",
"\n",
"df[\"prev_aum\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - AUM\"]\n",
" .shift(1)\n",
")\n",
"\n",
"df[\"prev_flow\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - NetFlows\"]\n",
" .shift(1)\n",
" .fillna(0)\n",
")\n",
"\n",
"df[\"expected_stock\"] = df[\"prev_aum\"] + df[\"prev_flow\"]\n",
"df[\"gap_before\"] = df[\"Quantity - AUM\"] - df[\"expected_stock\"]\n",
"\n",
"df[\"gap_after\"] = df[\"corrected_aum\"] - df[\"expected_stock_corr\"]\n",
"\n",
"df[\"rupture_before\"] = df[\"gap_before\"].abs() > GAP_TOL\n",
"\n",
"df[\"rupture_after\"] = df[\"gap_after\"].abs() > GAP_TOL\n",
"\n",
"\n",
"\n",
"# SUMMARY\n",
"\n",
"\n",
"summary = pd.DataFrame({\n",
" \"Before repair\": [df[\"rupture_before\"].sum()],\n",
" \"After repair\": [df[\"rupture_after\"].sum()],\n",
" \"Repaired points\": [df[\"repair_flag\"].sum()]\n",
"})\n",
"\n",
"print(summary)\n",
"\n",
"\n",
"\n",
"# BUILD REPAIRED DATASET\n",
"\n",
"\n",
"stocks_repaired = stocks.copy()\n",
"\n",
"repair_map = df[[\n",
" \"Registrar Account - ID\",\n",
" \"Product - Isin\",\n",
" \"Centralisation Date\",\n",
" \"corrected_aum\",\n",
" \"repair_flag\"\n",
"]]\n",
"\n",
"stocks_repaired = stocks_repaired.merge(\n",
" repair_map,\n",
" on=keys,\n",
" how=\"left\"\n",
")\n",
"\n",
"stocks_repaired[\"Quantity - AUM\"] = np.where(\n",
" stocks_repaired[\"repair_flag\"] == True,\n",
" stocks_repaired[\"corrected_aum\"],\n",
" stocks_repaired[\"Quantity - AUM\"]\n",
")\n",
"\n",
"stocks_repaired.to_csv(\"stock_repaired.csv\", index=False)\n",
"\n",
"\n",
"\n",
"# COMPARISON RAW VS REPAIRED\n",
"\n",
"\n",
"df_compare = stocks.merge(\n",
" stocks_repaired,\n",
" on=keys,\n",
" how=\"inner\",\n",
" suffixes=(\"_raw\",\"_repaired\")\n",
")\n",
"\n",
"df_compare[\"aum_diff\"] = (\n",
" df_compare[\"Quantity - AUM_repaired\"]\n",
" - df_compare[\"Quantity - AUM_raw\"]\n",
")\n",
"\n",
"print(\"\\nNUMBER OF MODIFIED OBSERVATIONS:\",\n",
" (df_compare[\"aum_diff\"] != 0).sum())\n",
"\n",
"print(\"Share modified:\",\n",
" round((df_compare[\"aum_diff\"] != 0).mean()*100,2), \"%\")\n",
"\n",
"print(\"\\nTOTAL AUM\")\n",
"\n",
"print(\"Raw total :\", df_compare[\"Quantity - AUM_raw\"].sum())\n",
"print(\"Repaired total :\", df_compare[\"Quantity - AUM_repaired\"].sum())\n",
"\n",
"\n",
"\n",
"# RUPTURE DISTRIBUTION BEFORE / AFTER\n",
"\n",
"\n",
"def rupture_distribution(df, flag):\n",
"\n",
" rupture_summary = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" .agg(\n",
" n_ruptures=(flag,\"sum\"),\n",
" total_obs=(flag,\"count\"),\n",
" rupture_ratio=(flag,\"mean\")\n",
" )\n",
" .reset_index()\n",
" )\n",
"\n",
" rs = rupture_summary.copy()\n",
"\n",
" bins = [0,0.01,0.10,0.30,1.01]\n",
"\n",
" labels = [\n",
" \"Clean / quasi-clean (≤1%)\",\n",
" \"Moderate (1–10%)\",\n",
" \"High (10–30%)\",\n",
" \"Severe (>30%)\"\n",
" ]\n",
"\n",
" rs[\"rupture_class\"] = pd.cut(\n",
" rs[\"rupture_ratio\"],\n",
" bins=bins,\n",
" labels=labels,\n",
" include_lowest=True\n",
" )\n",
"\n",
" dist = (\n",
" rs[\"rupture_class\"]\n",
" .value_counts(normalize=True)\n",
" .sort_index()\n",
" * 100\n",
" ).round(1)\n",
"\n",
" return dist\n",
"\n",
"\n",
"dist_before = rupture_distribution(df,\"rupture_before\")\n",
"dist_after = rupture_distribution(df,\"rupture_after\")"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "54491736-58b3-4ef7-b6c4-5534ec796bce",
"metadata": {},
"outputs": [],
"source": [
"# DONUT CHART BEFORE / AFTER\n",
"\n",
"fig = go.Figure()\n",
"\n",
"fig.add_trace(go.Pie(\n",
" labels=dist_before.index,\n",
" values=dist_before.values,\n",
" hole=0.45,\n",
" name=\"Before repair\",\n",
" domain=dict(x=[0,0.48]),\n",
" textinfo=\"percent\"\n",
"))\n",
"\n",
"fig.add_trace(go.Pie(\n",
" labels=dist_after.index,\n",
" values=dist_after.values,\n",
" hole=0.45,\n",
" name=\"After repair\",\n",
" domain=dict(x=[0.52,1]),\n",
" textinfo=\"percent\"\n",
"))\n",
"\n",
"fig.update_layout(\n",
" title=\"Rupture intensity distribution (Before vs After repair)\",\n",
" annotations=[\n",
" dict(text=\"Before repair\", x=0.22, y=0.5, showarrow=False),\n",
" dict(text=\"After repair\", x=0.78, y=0.5, showarrow=False)\n",
" ]\n",
")\n",
"\n",
"fig.show()"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "d844f6f0-c0f4-4f71-8280-1fd39ced83b7",
"metadata": {},
"outputs": [],
"source": [
"# LOAD DATA\n",
"\n",
"aum = pd.read_csv(\"stock_repaired.csv\")\n",
"\n",
"aum[\"Centralisation Date\"] = pd.to_datetime(aum[\"Centralisation Date\"])\n",
"\n",
"\n",
"# KEEP USEFUL COLUMNS\n",
"\n",
"aum = aum[[\n",
" \"Registrar Account - ID\",\n",
" \"Product - Isin\",\n",
" \"Centralisation Date\",\n",
" \"Quantity - AUM\",\n",
" \"repair_flag\"\n",
"]]\n",
"\n",
"flows = flows[[\n",
" \"Registrar Account - ID\",\n",
" \"Product - Isin\",\n",
" \"Centralisation Date\",\n",
" \"Quantity - NetFlows\"\n",
"]]\n",
"\n",
"\n",
"\n",
"# AGGREGATE FLOWS\n",
"\n",
"flows = (\n",
" flows\n",
" .groupby(\n",
" [\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"],\n",
" as_index=False\n",
" )[\"Quantity - NetFlows\"]\n",
" .sum()\n",
")\n",
"\n",
"\n",
"\n",
"# MERGE DATASETS\n",
"\n",
"df = aum.merge(\n",
" flows,\n",
" on=[\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"],\n",
" how=\"left\"\n",
")\n",
"\n",
"df[\"Quantity - NetFlows\"] = df[\"Quantity - NetFlows\"].fillna(0)\n",
"\n",
"print(\"Dataset size:\", df.shape)\n",
"\n",
"\n",
"\n",
"# SORT DATA\n",
"\n",
"df = df.sort_values(\n",
" [\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"]\n",
")\n",
"\n",
"\n",
"\n",
"# REBUILD ACCOUNTING IDENTITY\n",
"\n",
"df[\"prev_aum\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - AUM\"]\n",
" .shift(1)\n",
")\n",
"\n",
"df[\"prev_flow\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - NetFlows\"]\n",
" .shift(1)\n",
" .fillna(0)\n",
")\n",
"\n",
"df[\"expected_aum\"] = df[\"prev_aum\"] + df[\"prev_flow\"]\n",
"\n",
"\n",
"\n",
"# GAPS\n",
"\n",
"df[\"gap\"] = df[\"Quantity - AUM\"] - df[\"expected_aum\"]\n",
"\n",
"df[\"gap_abs\"] = df[\"gap\"].abs()\n",
"\n",
"df[\"gap_rel\"] = (\n",
" df[\"gap_abs\"] /\n",
" df[\"expected_aum\"].abs().clip(lower=1)\n",
")\n",
"\n",
"\n",
"\n",
"# ACCOUNTING CONSISTENCY\n",
"\n",
"print(\"\\nACCOUNTING GAP DISTRIBUTION\")\n",
"\n",
"print(df[\"gap_abs\"].describe())\n",
"\n",
"print(\"\\nRelative gap quantiles\")\n",
"\n",
"print(df[\"gap_rel\"].quantile([0.90,0.95,0.99]))\n",
"\n",
"\n",
"\n",
"# NEGATIVE AUM\n",
"\n",
"neg = (df[\"Quantity - AUM\"] < 0).sum()\n",
"\n",
"print(\"\\nNEGATIVE AUM:\", neg)\n",
"\n",
"\n",
"\n",
"# REPAIR RATE\n",
"\n",
"print(\"\\nREPAIR RATE\")\n",
"\n",
"print(df[\"repair_flag\"].mean())\n",
"\n",
"\n",
"\n",
"# AUM JUMPS\n",
"\n",
"\n",
"df[\"prev_obs\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - AUM\"]\n",
" .shift(1)\n",
")\n",
"\n",
"df[\"aum_jump\"] = (\n",
" df[\"Quantity - AUM\"] /\n",
" df[\"prev_obs\"].replace(0,np.nan)\n",
")\n",
"\n",
"print(\"\\nAUM JUMP QUANTILES\")\n",
"\n",
"print(df[\"aum_jump\"].quantile([0.90,0.95,0.99]))\n",
"\n",
"\n",
"\n",
"# VISUAL CHECK\n",
"\n",
"\n",
"def plot_series(account, isin):\n",
"\n",
" sub = df[\n",
" (df[\"Registrar Account - ID\"] == account) &\n",
" (df[\"Product - Isin\"] == isin)\n",
" ]\n",
"\n",
" plt.figure(figsize=(8,3))\n",
"\n",
" plt.plot(\n",
" sub[\"Centralisation Date\"],\n",
" sub[\"Quantity - AUM\"],\n",
" label=\"AUM\"\n",
" )\n",
"\n",
" plt.plot(\n",
" sub[\"Centralisation Date\"],\n",
" sub[\"expected_aum\"],\n",
" linestyle=\"--\",\n",
" label=\"Expected AUM\"\n",
" )\n",
"\n",
" plt.legend()\n",
" plt.title(f\"Account {account} — ISIN {isin}\")\n",
"\n",
" plt.show()"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "f7d759f7-64be-4d82-a79c-98cda407cfec",
"metadata": {
"scrolled": true
},
"outputs": [],
"source": [
"# COMPUTE AUM CHANGE\n",
"\n",
"df[\"prev_aum\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - AUM\"]\n",
" .shift(1)\n",
")\n",
"\n",
"df[\"delta_aum\"] = df[\"Quantity - AUM\"] - df[\"prev_aum\"]\n",
"\n",
"df[\"flow_lag\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - NetFlows\"]\n",
" .shift(1)\n",
")\n",
"\n",
"\n",
"\n",
"# FILTER VALID OBSERVATIONS\n",
"\n",
"\n",
"diag = df[\n",
" df[\"prev_aum\"].notna() &\n",
" df[\"flow_lag\"].notna()\n",
"]\n",
"\n",
"\n",
"\n",
"# SAMPLE FOR PLOTTING (dataset is large)\n",
"\n",
"\n",
"sample = diag.sample(20000, random_state=1)\n",
"\n",
"\n",
"\n",
"# SCATTER PLOT\n",
"\n",
"\n",
"plt.figure(figsize=(7,7))\n",
"\n",
"plt.scatter(\n",
" sample[\"flow_lag\"],\n",
" sample[\"delta_aum\"],\n",
" alpha=0.3,\n",
" s=5\n",
")\n",
"\n",
"# perfect accounting identity\n",
"x = np.linspace(\n",
" sample[\"flow_lag\"].min(),\n",
" sample[\"flow_lag\"].max(),\n",
" 100\n",
")\n",
"\n",
"plt.plot(x, x, color=\"red\", label=\"Perfect identity\")\n",
"\n",
"plt.xlabel(\"Flow (t-1)\")\n",
"plt.ylabel(\"Δ AUM\")\n",
"\n",
"plt.title(\"AUM / Flow Accounting Diagnostic\")\n",
"\n",
"plt.legend()\n",
"\n",
"plt.show()"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "d0a959c9-cfff-44cb-a1df-6c7275ec5b43",
"metadata": {},
"outputs": [],
"source": [
"df[\"implied_return\"] = (\n",
" df[\"Quantity - AUM\"] - df[\"prev_aum\"] - df[\"flow_lag\"]\n",
") / df[\"prev_aum\"].replace(0, np.nan)\n",
"\n",
"print(df[\"implied_return\"].quantile([0.5,0.9,0.95,0.99]))"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "15111e8a-7d87-4c37-8122-daafe90a1ad5",
"metadata": {
"scrolled": true
},
"outputs": [],
"source": [
"# ============================================================\n",
"# ADDITIONAL DATASET VALIDATION CHECKS (ROBUST VERSION)\n",
"# ============================================================\n",
"\n",
"import numpy as np\n",
"import pandas as pd\n",
"\n",
"print(\"\\n==============================\")\n",
"print(\"ADDITIONAL DATA QUALITY CHECKS\")\n",
"print(\"==============================\")\n",
"\n",
"# ------------------------------------------------------------\n",
"# RECOMPUTE KEY VARIABLES IF NEEDED\n",
"# ------------------------------------------------------------\n",
"\n",
"df = df.sort_values([\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"])\n",
"\n",
"df[\"prev_aum\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - AUM\"]\n",
" .shift(1)\n",
")\n",
"\n",
"df[\"flow_lag\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - NetFlows\"]\n",
" .shift(1)\n",
")\n",
"\n",
"df[\"flow_lag\"] = df[\"flow_lag\"].fillna(0)\n",
"\n",
"df[\"expected_aum\"] = df[\"prev_aum\"] + df[\"flow_lag\"]\n",
"\n",
"df[\"gap\"] = df[\"Quantity - AUM\"] - df[\"expected_aum\"]\n",
"df[\"gap_abs\"] = df[\"gap\"].abs()\n",
"\n",
"df[\"delta_aum\"] = df[\"Quantity - AUM\"] - df[\"prev_aum\"]\n",
"\n",
"df[\"implied_return\"] = (\n",
" df[\"Quantity - AUM\"] - df[\"prev_aum\"] - df[\"flow_lag\"]\n",
") / df[\"prev_aum\"].replace(0,np.nan)\n",
"\n",
"df[\"aum_jump\"] = (\n",
" df[\"Quantity - AUM\"] /\n",
" df[\"prev_aum\"].replace(0,np.nan)\n",
")\n",
"\n",
"\n",
"# ------------------------------------------------------------\n",
"# 1. CHECK SERIES WHERE GAP IS STILL LARGE\n",
"# ------------------------------------------------------------\n",
"\n",
"remaining_gaps = df[df[\"gap_abs\"] > 10]\n",
"\n",
"series_remaining = (\n",
" remaining_gaps\n",
" .groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" .size()\n",
")\n",
"\n",
"print(\"\\nSERIES STILL WITH LARGE ACCOUNTING GAPS:\", len(series_remaining))\n",
"\n",
"\n",
"# ------------------------------------------------------------\n",
"# 2. GAP DISTRIBUTION\n",
"# ------------------------------------------------------------\n",
"\n",
"print(\"\\nACCOUNTING GAP DISTRIBUTION\")\n",
"print(df[\"gap_abs\"].quantile([0.5,0.9,0.95,0.99]))\n",
"\n",
"\n",
"# ------------------------------------------------------------\n",
"# 3. IMPLIED RETURNS DISTRIBUTION\n",
"# ------------------------------------------------------------\n",
"\n",
"print(\"\\nIMPLIED RETURN DISTRIBUTION\")\n",
"print(df[\"implied_return\"].quantile([0.5,0.9,0.95,0.99]))\n",
"\n",
"\n",
"# ------------------------------------------------------------\n",
"# 4. EXTREME RETURNS\n",
"# ------------------------------------------------------------\n",
"\n",
"extreme_returns = df[df[\"implied_return\"].abs() > 2]\n",
"\n",
"print(\"\\nOBSERVATIONS WITH EXTREME IMPLIED RETURNS (>200%):\",\n",
" len(extreme_returns))\n",
"\n",
"\n",
"# ------------------------------------------------------------\n",
"# 5. FLOW / AUM ACCOUNTING CORRELATION\n",
"# ------------------------------------------------------------\n",
"\n",
"valid = df[\n",
" df[\"prev_aum\"].notna() &\n",
" df[\"flow_lag\"].notna()\n",
"]\n",
"\n",
"corr = valid[\"delta_aum\"].corr(valid[\"flow_lag\"])\n",
"\n",
"print(\"\\nCORRELATION ΔAUM vs FLOW:\", corr)\n",
"\n",
"\n",
"# ------------------------------------------------------------\n",
"# 6. LARGE AUM JUMPS\n",
"# ------------------------------------------------------------\n",
"\n",
"large_jumps = df[df[\"aum_jump\"].abs() > 5]\n",
"\n",
"print(\"\\nLARGE AUM JUMPS (>5x):\", len(large_jumps))\n",
"\n",
"\n",
"# ------------------------------------------------------------\n",
"# 7. SERIES WITH HIGH GAP RATE\n",
"# ------------------------------------------------------------\n",
"\n",
"series_gap_rate = (\n",
" (df[\"gap_abs\"] > 10)\n",
" .groupby([df[\"Registrar Account - ID\"], df[\"Product - Isin\"]])\n",
" .mean()\n",
")\n",
"\n",
"problem_series = series_gap_rate[series_gap_rate > 0.2]\n",
"\n",
"print(\"\\nSERIES WITH >20% ACCOUNTING GAPS:\", len(problem_series))\n",
"\n",
"\n",
"# ------------------------------------------------------------\n",
"# 8. TOTAL AUM STABILITY\n",
"# ------------------------------------------------------------\n",
"\n",
"if \"df_compare\" in globals():\n",
"\n",
" raw_total = df_compare[\"Quantity - AUM_raw\"].sum()\n",
" repaired_total = df_compare[\"Quantity - AUM_repaired\"].sum()\n",
"\n",
" print(\"\\nTOTAL AUM RAW:\", raw_total)\n",
" print(\"TOTAL AUM REPAIRED:\", repaired_total)\n",
"\n",
" print(\"RELATIVE CHANGE:\",\n",
" (repaired_total - raw_total) / raw_total)\n",
"\n",
"\n",
"# ------------------------------------------------------------\n",
"# 9. PROPORTION OF SERIES REPAIRED\n",
"# ------------------------------------------------------------\n",
"\n",
"if \"repair_flag\" in df.columns:\n",
"\n",
" series_repaired = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"repair_flag\"]\n",
" .max()\n",
" )\n",
"\n",
" print(\"\\nSERIES WITH AT LEAST ONE REPAIR:\",\n",
" series_repaired.mean())\n",
"\n",
"\n",
"# ------------------------------------------------------------\n",
"# 10. WORST SERIES (MANUAL CHECK)\n",
"# ------------------------------------------------------------\n",
"\n",
"worst_series = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"gap_abs\"]\n",
" .max()\n",
" .sort_values(ascending=False)\n",
" .head(10)\n",
")\n",
"\n",
"print(\"\\nWORST SERIES AFTER REPAIR\")\n",
"print(worst_series)"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "f9a4fd91-bb8b-4172-a267-cbe7f2e4fae7",
"metadata": {},
"outputs": [],
"source": [
"print(\"RUPTURES BEFORE:\", summary[\"Before repair\"].iloc[0])\n",
"print(\"RUPTURES AFTER :\", summary[\"After repair\"].iloc[0])\n",
"print(\"REDUCTION RATE :\", 1 - summary[\"After repair\"].iloc[0] /\n",
" summary[\"Before repair\"].iloc[0])"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "68596521-a10a-479a-a6cd-36b6be3c55b9",
"metadata": {},
"outputs": [],
"source": [
"# CHECK IF REPAIR CREATED NEW NEGATIVE AUM\n",
"\n",
"df_compare = stocks.merge(\n",
" stocks_repaired,\n",
" on=[\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"],\n",
" how=\"inner\",\n",
" suffixes=(\"_raw\",\"_repaired\")\n",
")\n",
"\n",
"neg_raw = (\n",
" df_compare[df_compare[\"Quantity - AUM_raw\"] < 0]\n",
" .groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" .size()\n",
")\n",
"\n",
"neg_rep = (\n",
" df_compare[df_compare[\"Quantity - AUM_repaired\"] < 0]\n",
" .groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" .size()\n",
")\n",
"\n",
"print(\"Negative series BEFORE repair:\", len(neg_raw))\n",
"print(\"Negative series AFTER repair:\", len(neg_rep))\n",
"\n",
"created_neg = set(neg_rep.index) - set(neg_raw.index)\n",
"\n",
"print(\"\\nNumber of series where repair created negatives:\", len(created_neg))\n",
"\n",
"if len(created_neg) > 0:\n",
" print(\"\\nSeries concerned:\")\n",
" print(created_neg)\n",
"\n",
"\n",
"\n",
"for acc, isin in created_neg:\n",
"\n",
" sub = df_compare[\n",
" (df_compare[\"Registrar Account - ID\"] == acc) &\n",
" (df_compare[\"Product - Isin\"] == isin)\n",
" ].sort_values(\"Centralisation Date\").reset_index(drop=True)\n",
"\n",
" # indices where repaired AUM becomes negative\n",
" neg_idx = sub.index[sub[\"Quantity - AUM_repaired\"] < 0]\n",
"\n",
" print(\"\\n======================================\")\n",
" print(\"Account:\", acc, \"ISIN:\", isin)\n",
"\n",
" for i in neg_idx:\n",
"\n",
" start = max(0, i-3)\n",
" end = min(len(sub), i+3)\n",
"\n",
" print(\"\\nContext around created negative:\")\n",
" print(\n",
" sub.loc[start:end, [\n",
" \"Centralisation Date\",\n",
" \"Quantity - AUM_raw\",\n",
" \"Quantity - AUM_repaired\"\n",
" ]]\n",
" )"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "203797d1-c380-406d-ac6e-78c4e1228966",
"metadata": {},
"outputs": [],
"source": [
"# top N worst series\n",
"N = 20\n",
"\n",
"worst_series = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])[\"gap_abs\"]\n",
" .max()\n",
" .sort_values(ascending=False)\n",
" .head(N)\n",
")\n",
"\n",
"print(worst_series)"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "17c6be03-3b76-41e7-906f-d86472bda274",
"metadata": {},
"outputs": [],
"source": [
"def plot_account_series(account, isin):\n",
"\n",
" sub = df[\n",
" (df[\"Registrar Account - ID\"] == account) &\n",
" (df[\"Product - Isin\"] == isin)\n",
" ].sort_values(\"Centralisation Date\")\n",
"\n",
" plt.figure(figsize=(10,4))\n",
"\n",
" plt.plot(\n",
" sub[\"Centralisation Date\"],\n",
" sub[\"Quantity - AUM\"],\n",
" label=\"AUM\",\n",
" linewidth=2\n",
" )\n",
"\n",
" plt.plot(\n",
" sub[\"Centralisation Date\"],\n",
" sub[\"expected_aum\"],\n",
" linestyle=\"--\",\n",
" label=\"Expected AUM\"\n",
" )\n",
"\n",
" # highlight large gaps\n",
" ruptures = sub[sub[\"gap_abs\"] > 10]\n",
"\n",
" plt.scatter(\n",
" ruptures[\"Centralisation Date\"],\n",
" ruptures[\"Quantity - AUM\"],\n",
" color=\"red\",\n",
" label=\"Rupture\",\n",
" s=40\n",
" )\n",
"\n",
" plt.title(f\"Account {account} | ISIN {isin}\")\n",
" plt.legend()\n",
" plt.grid(alpha=0.3)\n",
"\n",
" plt.show()"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "02a22e9a-a71c-4212-8f0a-f33c18e4b530",
"metadata": {},
"outputs": [],
"source": [
"for acc, isin in worst_series.index:\n",
" plot_account_series(acc, isin)"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "c72e2dc2-c35d-4608-a3da-57a37acf64c7",
"metadata": {},
"outputs": [],
"source": [
"def run_data_challenge(stocks, flows):\n",
"\n",
" # conversion dates\n",
" stocks[\"Centralisation Date\"] = pd.to_datetime(stocks[\"Centralisation Date\"])\n",
" flows[\"Centralisation Date\"] = pd.to_datetime(flows[\"Centralisation Date\"])\n",
"\n",
" # merge datasets\n",
" df = stocks.merge(\n",
" flows,\n",
" on=[\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"],\n",
" how=\"left\"\n",
" )\n",
"\n",
" df[\"Quantity - NetFlows\"] = df[\"Quantity - NetFlows\"].fillna(0)\n",
"\n",
" # sort\n",
" df = df.sort_values(\n",
" [\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"]\n",
" )\n",
"\n",
" # previous values\n",
" df[\"prev_aum\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - AUM\"]\n",
" .shift(1)\n",
" )\n",
"\n",
" df[\"flow_lag\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - NetFlows\"]\n",
" .shift(1)\n",
" ).fillna(0)\n",
"\n",
" # expected AUM\n",
" df[\"expected_aum\"] = df[\"prev_aum\"] + df[\"flow_lag\"]\n",
"\n",
" # gap\n",
" df[\"gap\"] = df[\"Quantity - AUM\"] - df[\"expected_aum\"]\n",
"\n",
" # build score\n",
" df[\"score\"] = np.exp(-np.abs(df[\"gap\"]) / (df[\"expected_aum\"].abs()+1))\n",
"\n",
" # score timeline\n",
" score_timeline = (\n",
" df.groupby(\"Centralisation Date\")[\"score\"]\n",
" .sum()\n",
" .reset_index()\n",
" .rename(columns={\"Centralisation Date\":\"date\",\n",
" \"score\":\"sum_scores\"})\n",
" .sort_values(\"date\")\n",
" )\n",
"\n",
" code_changes = pd.DataFrame() # placeholder\n",
"\n",
" return score_timeline, code_changes"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "67787ce5-39ae-4a3a-ba22-e9d38bcaf8d3",
"metadata": {},
"outputs": [],
"source": [
"# ============================================================\n",
"# RUN DATA CHALLENGE ON RAW AND CLEAN DATASETS\n",
"# ============================================================\n",
"\n",
"DATASETS = {\n",
" \"raw\": \"stocks.csv\",\n",
" \"clean\": \"stock_repaired.csv\"\n",
"}\n",
"\n",
"results = {}\n",
"\n",
"for name, file in DATASETS.items():\n",
"\n",
" print(\"\\n====================================\")\n",
" print(\"RUNNING DATA CHALLENGE ON:\", name)\n",
" print(\"====================================\")\n",
"\n",
" # load datasets\n",
" stocks = pd.read_csv(file, low_memory=False)\n",
" flows = pd.read_csv(\"flows.csv\", low_memory=False)\n",
"\n",
" # run the full algorithm\n",
" score_timeline, code_changes = run_data_challenge(stocks, flows)\n",
"\n",
" # store results\n",
" results[name] = score_timeline.copy()\n",
"\n",
"\n",
"# ============================================================\n",
"# BUILD COMPARISON TABLE\n",
"# ============================================================\n",
"\n",
"comparison = (\n",
" results[\"raw\"][[\"date\", \"sum_scores\"]]\n",
" .rename(columns={\"sum_scores\": \"raw_scores\"})\n",
" .merge(\n",
" results[\"clean\"][[\"date\", \"sum_scores\"]]\n",
" .rename(columns={\"sum_scores\": \"clean_scores\"}),\n",
" on=\"date\",\n",
" how=\"outer\"\n",
" )\n",
" .sort_values(\"date\")\n",
")\n",
"\n",
"# improvement from cleaning\n",
"comparison[\"improvement\"] = (\n",
" comparison[\"clean_scores\"] - comparison[\"raw_scores\"]\n",
")\n",
"\n",
"# relative improvement\n",
"comparison[\"relative_improvement\"] = (\n",
" comparison[\"improvement\"] /\n",
" comparison[\"raw_scores\"].replace(0, np.nan)\n",
")\n",
"\n",
"print(\"\\n==============================\")\n",
"print(\"RAW VS CLEAN SCORE COMPARISON\")\n",
"print(\"==============================\")\n",
"\n",
"print(comparison.head())\n",
"print(comparison.tail())"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "5bdb367f-3764-400a-a20d-793f8d004d82",
"metadata": {},
"outputs": [],
"source": [
"# ============================================================\n",
"# PARAMETERS\n",
"# ============================================================\n",
"\n",
"TARGET_DATE = pd.Timestamp(\"2025-10-31\")\n",
"AUM_THRESHOLD = 5_000_000\n",
"EXCLUDED = [\"OFF DISTRIBUTION\", \"PRIVATE CLIENTS\"]\n",
"\n",
"ALPHA = 5 # penalty strength for accounting error\n",
"\n",
"stocks[\"Centralisation Date\"] = pd.to_datetime(stocks[\"Centralisation Date\"])\n",
"flows[\"Centralisation Date\"] = pd.to_datetime(flows[\"Centralisation Date\"])\n",
"\n",
"stocks[\"Registrar Account - ID\"] = stocks[\"Registrar Account - ID\"].astype(str).str.strip()\n",
"flows[\"Registrar Account - ID\"] = flows[\"Registrar Account - ID\"].astype(str).str.strip()\n",
"\n",
"stocks[\"Product - Isin\"] = stocks[\"Product - Isin\"].astype(str)\n",
"flows[\"Product - Isin\"] = flows[\"Product - Isin\"].astype(str)\n",
"\n",
"# ============================================================\n",
"# REMOVE EXCLUDED ACCOUNTS\n",
"# ============================================================\n",
"\n",
"stocks = stocks[~stocks[\"Registrar Account - ID\"].str.upper().isin(EXCLUDED)]\n",
"flows = flows[~flows[\"Registrar Account - ID\"].str.upper().isin(EXCLUDED)]"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "b10832d6-988a-42cd-a6b6-4dfb690315b7",
"metadata": {},
"outputs": [],
"source": [
"# ============================================================\n",
"# SELECT UNIVERSE AT TARGET DATE\n",
"# ============================================================\n",
"\n",
"latest = stocks[stocks[\"Centralisation Date\"] == TARGET_DATE]\n",
"\n",
"account_aum = (\n",
" latest.groupby(\"Registrar Account - ID\")[\"Quantity - AUM\"]\n",
" .sum()\n",
" .sort_values(ascending=False)\n",
")\n",
"\n",
"# remove negative values if any\n",
"account_aum = account_aum[account_aum > 0]\n",
"\n",
"# cumulative coverage\n",
"cum_aum = account_aum.cumsum()\n",
"total_aum = account_aum.sum()\n",
"\n",
"coverage = cum_aum / total_aum\n",
"\n",
"# select accounts covering 97% of AUM\n",
"selected_accounts = account_aum[coverage <= 0.97]\n",
"\n",
"# ensure at least one more account\n",
"selected_accounts = account_aum.iloc[:len(selected_accounts)+1]\n",
"\n",
"# weights at t0\n",
"weights = selected_accounts / selected_accounts.sum()\n",
"\n",
"print(\"\\nUNIVERSE SELECTION\")\n",
"print(\"------------------\")\n",
"print(\"Number of selected accounts:\", len(selected_accounts))\n",
"print(\"Coverage:\", selected_accounts.sum() / total_aum)\n",
"\n",
"print(\"\\nTop accounts:\")\n",
"print(selected_accounts.head(10))\n",
"\n",
"# ============================================================\n",
"# BUILD DATASET\n",
"# ============================================================\n",
"\n",
"df = stocks.merge(\n",
" flows,\n",
" on=[\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"],\n",
" how=\"left\"\n",
")\n",
"\n",
"df[\"Quantity - NetFlows\"] = df[\"Quantity - NetFlows\"].fillna(0)\n",
"\n",
"df = df.sort_values(\n",
" [\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"]\n",
")\n",
"\n",
"# ============================================================\n",
"# COMPUTE PREVIOUS VALUES\n",
"# ============================================================\n",
"\n",
"df[\"prev_aum\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - AUM\"]\n",
" .shift(1)\n",
")\n",
"\n",
"df[\"flow_lag\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - NetFlows\"]\n",
" .shift(1)\n",
").fillna(0)\n",
"\n",
"df[\"expected_aum\"] = df[\"prev_aum\"] + df[\"flow_lag\"]\n",
"\n",
"df[\"gap\"] = df[\"Quantity - AUM\"] - df[\"expected_aum\"]\n",
"\n",
"df[\"rel_error\"] = (\n",
" df[\"gap\"].abs() /\n",
" df[\"expected_aum\"].abs().clip(lower=1)\n",
")\n",
"\n",
"# ============================================================\n",
"# ACCOUNT LEVEL ERROR\n",
"# ============================================================\n",
"\n",
"account_error = (\n",
" df.groupby([\"Centralisation Date\",\"Registrar Account - ID\"])\n",
" [\"rel_error\"]\n",
" .mean()\n",
" .reset_index()\n",
")\n",
"\n",
"# ============================================================\n",
"# FAST SCORE BACKWARD PROPAGATION\n",
"# ============================================================\n",
"\n",
"# pivot errors into matrix\n",
"error_matrix = (\n",
" account_error\n",
" .pivot(\n",
" index=\"Centralisation Date\",\n",
" columns=\"Registrar Account - ID\",\n",
" values=\"rel_error\"\n",
" )\n",
")\n",
"\n",
"# keep only selected accounts\n",
"error_matrix = error_matrix[selected_accounts.index]\n",
"\n",
"# fill missing errors\n",
"error_matrix = error_matrix.fillna(0)\n",
"\n",
"# keep only dates <= target\n",
"error_matrix = error_matrix.loc[error_matrix.index <= TARGET_DATE]\n",
"\n",
"# sort dates\n",
"error_matrix = error_matrix.sort_index()\n",
"\n",
"dates = error_matrix.index.values\n",
"\n",
"# convert to numpy for speed\n",
"errors = error_matrix.values\n",
"\n",
"# initial scores\n",
"scores = weights.loc[selected_accounts.index].values\n",
"\n",
"score_history = []\n",
"\n",
"# backward propagation\n",
"for i in range(len(dates)-1, -1, -1):\n",
"\n",
" err = errors[i]\n",
"\n",
" quality = np.exp(-ALPHA * err)\n",
"\n",
" scores = scores * quality\n",
"\n",
" score_history.append({\n",
" \"date\": dates[i],\n",
" \"sum_scores\": scores.sum()\n",
" })\n",
"\n",
"score_timeline = (\n",
" pd.DataFrame(score_history)\n",
" .sort_values(\"date\")\n",
")\n",
"\n",
"# ============================================================\n",
"# NORMALISE\n",
"# ============================================================\n",
"\n",
"initial_score = score_timeline[\"sum_scores\"].iloc[-1]\n",
"\n",
"score_timeline[\"score_retention\"] = (\n",
" score_timeline[\"sum_scores\"] / initial_score\n",
")\n",
"\n",
"# ============================================================\n",
"# RESULTS\n",
"# ============================================================\n",
"\n",
"print(\"\\nSCORE TIMELINE\")\n",
"print(score_timeline.head())\n",
"print(score_timeline.tail())\n",
"\n",
"# ============================================================\n",
"# PLOT\n",
"# ============================================================\n",
"\n",
"plt.figure(figsize=(8,4))\n",
"\n",
"plt.plot(\n",
" score_timeline[\"date\"],\n",
" score_timeline[\"score_retention\"]\n",
")\n",
"\n",
"plt.title(\"Score retention when moving backward in time\")\n",
"plt.xlabel(\"Date\")\n",
"plt.ylabel(\"Σ Scores / Σ Scores (t0)\")\n",
"plt.grid(alpha=0.3)\n",
"\n",
"plt.show()\n",
"\n",
"# ============================================================\n",
"# SAVE\n",
"# ============================================================\n",
"\n",
"score_timeline.to_csv(\n",
" \"data_challenge_score_timeline.csv\",\n",
" index=False\n",
")"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "86d82213-d155-4feb-817e-ef1df50578e8",
"metadata": {},
"outputs": [],
"source": [
"# ============================================================\n",
"# PRECOMPUTE PORTFOLIOS BY DATE\n",
"# ============================================================\n",
"\n",
"portfolio_by_date = {\n",
" d: g.droplevel(0)\n",
" for d, g in portfolio.groupby(level=0)\n",
"}\n",
"\n",
"flows_by_date = {\n",
" d: g.droplevel(0)\n",
" for d, g in flows_matrix.groupby(level=0)\n",
"}\n",
"\n",
"# ============================================================\n",
"# CODE SURGERY SEARCH (FAST + ROBUST)\n",
"# ============================================================\n",
"\n",
"code_changes = []\n",
"\n",
"for row in ruptures.itertuples():\n",
"\n",
" date = row._1\n",
" acc = row._2\n",
"\n",
" # find previous date\n",
" prev_date = score_timeline.loc[\n",
" score_timeline[\"date\"] < date,\n",
" \"date\"\n",
" ].max()\n",
"\n",
" if pd.isna(prev_date):\n",
" continue\n",
"\n",
" if date not in portfolio_by_date:\n",
" continue\n",
"\n",
" if prev_date not in portfolio_by_date:\n",
" continue\n",
"\n",
" # portfolio at t\n",
" port_today = portfolio_by_date[date]\n",
"\n",
" if acc not in port_today.index:\n",
" continue\n",
"\n",
" port_t = port_today.loc[acc]\n",
"\n",
" # portfolio at t-1\n",
" prev_port = portfolio_by_date[prev_date]\n",
"\n",
" # flows at t-1\n",
" prev_flow = flows_by_date.get(prev_date)\n",
"\n",
" # align flows to portfolio\n",
" if prev_flow is not None:\n",
"\n",
" prev_flow = (\n",
" prev_flow\n",
" .reindex(index=prev_port.index, columns=prev_port.columns)\n",
" .fillna(0)\n",
" )\n",
"\n",
" else:\n",
"\n",
" prev_flow = pd.DataFrame(\n",
" 0,\n",
" index=prev_port.index,\n",
" columns=prev_port.columns\n",
" )\n",
"\n",
" # convert to numpy\n",
" prev_port_mat = prev_port.values\n",
" prev_flow_mat = prev_flow.values\n",
"\n",
" # predicted portfolio\n",
" predicted = prev_port_mat + prev_flow_mat\n",
"\n",
" port_t_vec = port_t.reindex(prev_port.columns).fillna(0).values\n",
"\n",
" # compute error vectorised\n",
" diff = np.abs(predicted - port_t_vec)\n",
"\n",
" errors = diff.sum(axis=1) / (np.abs(port_t_vec).sum() + 1)\n",
"\n",
" if len(errors) == 0:\n",
" continue\n",
"\n",
" best_idx = errors.argmin()\n",
"\n",
" if best_idx >= len(prev_port.index):\n",
" continue\n",
"\n",
" best_code = prev_port.index[best_idx]\n",
" best_error = errors[best_idx]\n",
"\n",
" if best_code != acc and best_error < 0.3:\n",
"\n",
" code_changes.append({\n",
" \"date\": date,\n",
" \"old_code\": acc,\n",
" \"new_code\": best_code,\n",
" \"portfolio_error\": best_error\n",
" })\n",
"\n",
"\n",
"# ============================================================\n",
"# RESULTS\n",
"# ============================================================\n",
"\n",
"code_changes = pd.DataFrame(code_changes)\n",
"\n",
"print(\"\\nDetected distributor code changes:\")\n",
"print(code_changes.head())\n",
"\n",
"code_changes.to_csv(\"detected_code_changes.csv\", index=False)"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "452b8321-26c5-4229-9992-43c38eb5253f",
"metadata": {},
"outputs": [],
"source": [
"def detect_code_changes(portfolio, flows_matrix, ruptures, score_timeline):\n",
"\n",
" # ============================================================\n",
" # PRECOMPUTE PORTFOLIOS BY DATE\n",
" # ============================================================\n",
"\n",
" portfolio_by_date = {\n",
" d: g.droplevel(0)\n",
" for d, g in portfolio.groupby(level=0)\n",
" }\n",
"\n",
" flows_by_date = {\n",
" d: g.droplevel(0)\n",
" for d, g in flows_matrix.groupby(level=0)\n",
" }\n",
"\n",
" # ============================================================\n",
" # CODE SURGERY SEARCH\n",
" # ============================================================\n",
"\n",
" code_changes = []\n",
"\n",
" for row in ruptures.itertuples():\n",
"\n",
" date = row._1\n",
" acc = row._2\n",
"\n",
" # find previous date\n",
" prev_date = score_timeline.loc[\n",
" score_timeline[\"date\"] < date,\n",
" \"date\"\n",
" ].max()\n",
"\n",
" if pd.isna(prev_date):\n",
" continue\n",
"\n",
" if date not in portfolio_by_date:\n",
" continue\n",
"\n",
" if prev_date not in portfolio_by_date:\n",
" continue\n",
"\n",
" # portfolio at t\n",
" port_today = portfolio_by_date[date]\n",
"\n",
" if acc not in port_today.index:\n",
" continue\n",
"\n",
" port_t = port_today.loc[acc]\n",
"\n",
" # portfolio at t-1\n",
" prev_port = portfolio_by_date[prev_date]\n",
"\n",
" # flows at t-1\n",
" prev_flow = flows_by_date.get(prev_date)\n",
"\n",
" # align flows to portfolio\n",
" if prev_flow is not None:\n",
"\n",
" prev_flow = (\n",
" prev_flow\n",
" .reindex(index=prev_port.index, columns=prev_port.columns)\n",
" .fillna(0)\n",
" )\n",
"\n",
" else:\n",
"\n",
" prev_flow = pd.DataFrame(\n",
" 0,\n",
" index=prev_port.index,\n",
" columns=prev_port.columns\n",
" )\n",
"\n",
" # convert to numpy\n",
" prev_port_mat = prev_port.values\n",
" prev_flow_mat = prev_flow.values\n",
"\n",
" # predicted portfolio\n",
" predicted = prev_port_mat + prev_flow_mat\n",
"\n",
" port_t_vec = port_t.reindex(prev_port.columns).fillna(0).values\n",
"\n",
" # compute error vectorised\n",
" diff = np.abs(predicted - port_t_vec)\n",
"\n",
" errors = diff.sum(axis=1) / (np.abs(port_t_vec).sum() + 1)\n",
"\n",
" if len(errors) == 0:\n",
" continue\n",
"\n",
" best_idx = errors.argmin()\n",
"\n",
" if best_idx >= len(prev_port.index):\n",
" continue\n",
"\n",
" best_code = prev_port.index[best_idx]\n",
" best_error = errors[best_idx]\n",
"\n",
" if best_code != acc and best_error < 0.3:\n",
"\n",
" code_changes.append({\n",
" \"date\": date,\n",
" \"old_code\": acc,\n",
" \"new_code\": best_code,\n",
" \"portfolio_error\": best_error\n",
" })\n",
"\n",
" # ============================================================\n",
" # RESULTS\n",
" # ============================================================\n",
"\n",
" code_changes = pd.DataFrame(code_changes)\n",
"\n",
" print(\"\\nDetected distributor code changes:\")\n",
" print(code_changes.head())\n",
"\n",
" # nouveau nom de fichier\n",
" code_changes.to_csv(\"detected_code_changes_filtered.csv\", index=False)\n",
"\n",
" return code_changes\n",
"\n",
"code_changes = detect_code_changes(\n",
" portfolio,\n",
" flows_matrix,\n",
" ruptures,\n",
" score_timeline\n",
")"
]
},
{
"cell_type": "code",
"execution_count": 4,
"id": "068da1e3-9de7-49d1-bda4-663b02f6d76a",
"metadata": {},
"outputs": [],
"source": [
"def detect_code_changes_fast(portfolio, flows_matrix, ruptures, score_timeline):\n",
"\n",
" # ============================================================\n",
" # PRECOMPUTE PORTFOLIOS BY DATE\n",
" # ============================================================\n",
"\n",
" portfolio_by_date = {\n",
" d: g.droplevel(0)\n",
" for d, g in portfolio.groupby(level=0)\n",
" }\n",
"\n",
" flows_by_date = {\n",
" d: g.droplevel(0)\n",
" for d, g in flows_matrix.groupby(level=0)\n",
" }\n",
"\n",
" # ============================================================\n",
" # PRECOMPUTE PREVIOUS DATES\n",
" # ============================================================\n",
"\n",
" dates = sorted(score_timeline[\"date\"].unique())\n",
"\n",
" prev_date_map = {\n",
" dates[i]: dates[i - 1] if i > 0 else None\n",
" for i in range(len(dates))\n",
" }\n",
"\n",
" # ============================================================\n",
" # CODE SURGERY SEARCH\n",
" # ============================================================\n",
"\n",
" code_changes = []\n",
"\n",
" from tqdm import tqdm\n",
" \n",
" for row in tqdm(ruptures.itertuples(), total=len(ruptures)):\n",
" date = row._1\n",
" acc = row._2\n",
"\n",
" prev_date = prev_date_map.get(date)\n",
"\n",
" if prev_date is None:\n",
" continue\n",
"\n",
" if date not in portfolio_by_date:\n",
" continue\n",
"\n",
" if prev_date not in portfolio_by_date:\n",
" continue\n",
"\n",
" port_today = portfolio_by_date[date]\n",
"\n",
" if acc not in port_today.index:\n",
" continue\n",
"\n",
" port_t = port_today.loc[acc]\n",
"\n",
" prev_port = portfolio_by_date[prev_date]\n",
"\n",
" # ========================================================\n",
" # LIMIT CANDIDATES (shared ISINs)\n",
" # ========================================================\n",
"\n",
" held_isins = port_t[port_t > 0].index\n",
"\n",
" if len(held_isins) == 0:\n",
" continue\n",
"\n",
" candidate_mask = prev_port[held_isins].sum(axis=1) > 0\n",
"\n",
" candidates = prev_port.index[candidate_mask]\n",
"\n",
" if len(candidates) == 0:\n",
" continue\n",
"\n",
" prev_port = prev_port.loc[candidates]\n",
"\n",
" prev_flow = flows_by_date.get(prev_date)\n",
"\n",
" if prev_flow is not None:\n",
"\n",
" prev_flow = prev_flow.reindex(\n",
" index=candidates,\n",
" columns=prev_port.columns\n",
" ).fillna(0)\n",
"\n",
" else:\n",
"\n",
" prev_flow = pd.DataFrame(\n",
" 0,\n",
" index=candidates,\n",
" columns=prev_port.columns\n",
" )\n",
"\n",
" # ========================================================\n",
" # VECTORISED ERROR COMPUTATION\n",
" # ========================================================\n",
"\n",
" prev_port_mat = prev_port.values\n",
" prev_flow_mat = prev_flow.values\n",
"\n",
" predicted = prev_port_mat + prev_flow_mat\n",
"\n",
" port_t_vec = port_t.reindex(prev_port.columns).fillna(0).values\n",
"\n",
" diff = np.abs(predicted - port_t_vec)\n",
"\n",
" errors = diff.sum(axis=1) / (np.abs(port_t_vec).sum() + 1)\n",
"\n",
" if len(errors) == 0:\n",
" continue\n",
"\n",
" best_idx = errors.argmin()\n",
"\n",
" best_code = prev_port.index[best_idx]\n",
" best_error = errors[best_idx]\n",
"\n",
" if best_code != acc and best_error < 0.3:\n",
"\n",
" code_changes.append({\n",
" \"date\": date,\n",
" \"old_code\": acc,\n",
" \"new_code\": best_code,\n",
" \"portfolio_error\": best_error\n",
" })\n",
"\n",
" # ============================================================\n",
" # RESULTS\n",
" # ============================================================\n",
"\n",
" code_changes = pd.DataFrame(code_changes)\n",
"\n",
" print(\"\\nDetected distributor code changes:\")\n",
" print(code_changes.head())\n",
"\n",
" code_changes.to_csv(\"detected_code_changes_fast.csv\", index=False)\n",
"\n",
" return code_changes"
]
},
{
"cell_type": "code",
"execution_count": 3,
"id": "2b332049-db18-470a-9249-248f01e8ca36",
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
" 11%|█ | 2068/18582 [00:24<03:14, 85.09it/s] \n"
]
},
{
"ename": "KeyboardInterrupt",
"evalue": "",
"output_type": "error",
"traceback": [
"\u001b[31m---------------------------------------------------------------------------\u001b[39m",
"\u001b[31mKeyboardInterrupt\u001b[39m Traceback (most recent call last)",
"\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[3]\u001b[39m\u001b[32m, line 145\u001b[39m\n\u001b[32m 138\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m code_changes\n\u001b[32m 141\u001b[39m \u001b[38;5;66;03m# ============================================================\u001b[39;00m\n\u001b[32m 142\u001b[39m \u001b[38;5;66;03m# RUN\u001b[39;00m\n\u001b[32m 143\u001b[39m \u001b[38;5;66;03m# ============================================================\u001b[39;00m\n\u001b[32m--> \u001b[39m\u001b[32m145\u001b[39m code_changes = \u001b[43mdetect_code_changes_fast\u001b[49m\u001b[43m(\u001b[49m\n\u001b[32m 146\u001b[39m \u001b[43m \u001b[49m\u001b[43mportfolio\u001b[49m\u001b[43m,\u001b[49m\n\u001b[32m 147\u001b[39m \u001b[43m \u001b[49m\u001b[43mflows_matrix\u001b[49m\u001b[43m,\u001b[49m\n\u001b[32m 148\u001b[39m \u001b[43m \u001b[49m\u001b[43mruptures\u001b[49m\u001b[43m,\u001b[49m\n\u001b[32m 149\u001b[39m \u001b[43m \u001b[49m\u001b[43mscore_timeline\u001b[49m\n\u001b[32m 150\u001b[39m \u001b[43m)\u001b[49m\n",
"\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[3]\u001b[39m\u001b[32m, line 69\u001b[39m, in \u001b[36mdetect_code_changes_fast\u001b[39m\u001b[34m(portfolio, flows_matrix, ruptures, score_timeline)\u001b[39m\n\u001b[32m 66\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mlen\u001b[39m(held_isins) == \u001b[32m0\u001b[39m:\n\u001b[32m 67\u001b[39m \u001b[38;5;28;01mcontinue\u001b[39;00m\n\u001b[32m---> \u001b[39m\u001b[32m69\u001b[39m candidate_mask = \u001b[43mprev_port\u001b[49m\u001b[43m[\u001b[49m\u001b[43mheld_isins\u001b[49m\u001b[43m]\u001b[49m\u001b[43m.\u001b[49m\u001b[43msum\u001b[49m\u001b[43m(\u001b[49m\u001b[43maxis\u001b[49m\u001b[43m=\u001b[49m\u001b[32;43m1\u001b[39;49m\u001b[43m)\u001b[49m > \u001b[32m0\u001b[39m\n\u001b[32m 71\u001b[39m candidates = prev_port.index[candidate_mask]\n\u001b[32m 73\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mlen\u001b[39m(candidates) == \u001b[32m0\u001b[39m:\n",
"\u001b[36mFile \u001b[39m\u001b[32m/opt/python/lib/python3.13/site-packages/pandas/core/frame.py:11697\u001b[39m, in \u001b[36mDataFrame.sum\u001b[39m\u001b[34m(self, axis, skipna, numeric_only, min_count, **kwargs)\u001b[39m\n\u001b[32m 11688\u001b[39m \u001b[38;5;129m@doc\u001b[39m(make_doc(\u001b[33m\"\u001b[39m\u001b[33msum\u001b[39m\u001b[33m\"\u001b[39m, ndim=\u001b[32m2\u001b[39m))\n\u001b[32m 11689\u001b[39m \u001b[38;5;28;01mdef\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34msum\u001b[39m(\n\u001b[32m 11690\u001b[39m \u001b[38;5;28mself\u001b[39m,\n\u001b[32m (...)\u001b[39m\u001b[32m 11695\u001b[39m **kwargs,\n\u001b[32m 11696\u001b[39m ):\n\u001b[32m> \u001b[39m\u001b[32m11697\u001b[39m result = \u001b[38;5;28;43msuper\u001b[39;49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\u001b[43m.\u001b[49m\u001b[43msum\u001b[49m\u001b[43m(\u001b[49m\u001b[43maxis\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mskipna\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mnumeric_only\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mmin_count\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43m*\u001b[49m\u001b[43m*\u001b[49m\u001b[43mkwargs\u001b[49m\u001b[43m)\u001b[49m\n\u001b[32m 11698\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m result.__finalize__(\u001b[38;5;28mself\u001b[39m, method=\u001b[33m\"\u001b[39m\u001b[33msum\u001b[39m\u001b[33m\"\u001b[39m)\n",
"\u001b[36mFile \u001b[39m\u001b[32m/opt/python/lib/python3.13/site-packages/pandas/core/generic.py:12571\u001b[39m, in \u001b[36mNDFrame.sum\u001b[39m\u001b[34m(self, axis, skipna, numeric_only, min_count, **kwargs)\u001b[39m\n\u001b[32m 12563\u001b[39m \u001b[38;5;28;01mdef\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34msum\u001b[39m(\n\u001b[32m 12564\u001b[39m \u001b[38;5;28mself\u001b[39m,\n\u001b[32m 12565\u001b[39m axis: Axis | \u001b[38;5;28;01mNone\u001b[39;00m = \u001b[32m0\u001b[39m,\n\u001b[32m (...)\u001b[39m\u001b[32m 12569\u001b[39m **kwargs,\n\u001b[32m 12570\u001b[39m ):\n\u001b[32m> \u001b[39m\u001b[32m12571\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[43m.\u001b[49m\u001b[43m_min_count_stat_function\u001b[49m\u001b[43m(\u001b[49m\n\u001b[32m 12572\u001b[39m \u001b[43m \u001b[49m\u001b[33;43m\"\u001b[39;49m\u001b[33;43msum\u001b[39;49m\u001b[33;43m\"\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mnanops\u001b[49m\u001b[43m.\u001b[49m\u001b[43mnansum\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43maxis\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mskipna\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mnumeric_only\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mmin_count\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43m*\u001b[49m\u001b[43m*\u001b[49m\u001b[43mkwargs\u001b[49m\n\u001b[32m 12573\u001b[39m \u001b[43m \u001b[49m\u001b[43m)\u001b[49m\n",
"\u001b[36mFile \u001b[39m\u001b[32m/opt/python/lib/python3.13/site-packages/pandas/core/generic.py:12554\u001b[39m, in \u001b[36mNDFrame._min_count_stat_function\u001b[39m\u001b[34m(self, name, func, axis, skipna, numeric_only, min_count, **kwargs)\u001b[39m\n\u001b[32m 12551\u001b[39m \u001b[38;5;28;01melif\u001b[39;00m axis \u001b[38;5;129;01mis\u001b[39;00m lib.no_default:\n\u001b[32m 12552\u001b[39m axis = \u001b[32m0\u001b[39m\n\u001b[32m> \u001b[39m\u001b[32m12554\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[43m.\u001b[49m\u001b[43m_reduce\u001b[49m\u001b[43m(\u001b[49m\n\u001b[32m 12555\u001b[39m \u001b[43m \u001b[49m\u001b[43mfunc\u001b[49m\u001b[43m,\u001b[49m\n\u001b[32m 12556\u001b[39m \u001b[43m \u001b[49m\u001b[43mname\u001b[49m\u001b[43m=\u001b[49m\u001b[43mname\u001b[49m\u001b[43m,\u001b[49m\n\u001b[32m 12557\u001b[39m \u001b[43m \u001b[49m\u001b[43maxis\u001b[49m\u001b[43m=\u001b[49m\u001b[43maxis\u001b[49m\u001b[43m,\u001b[49m\n\u001b[32m 12558\u001b[39m \u001b[43m \u001b[49m\u001b[43mskipna\u001b[49m\u001b[43m=\u001b[49m\u001b[43mskipna\u001b[49m\u001b[43m,\u001b[49m\n\u001b[32m 12559\u001b[39m \u001b[43m \u001b[49m\u001b[43mnumeric_only\u001b[49m\u001b[43m=\u001b[49m\u001b[43mnumeric_only\u001b[49m\u001b[43m,\u001b[49m\n\u001b[32m 12560\u001b[39m \u001b[43m \u001b[49m\u001b[43mmin_count\u001b[49m\u001b[43m=\u001b[49m\u001b[43mmin_count\u001b[49m\u001b[43m,\u001b[49m\n\u001b[32m 12561\u001b[39m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n",
"\u001b[36mFile \u001b[39m\u001b[32m/opt/python/lib/python3.13/site-packages/pandas/core/frame.py:11593\u001b[39m, in \u001b[36mDataFrame._reduce\u001b[39m\u001b[34m(self, op, name, axis, skipna, numeric_only, filter_type, **kwds)\u001b[39m\n\u001b[32m 11591\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m out_dtype \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m \u001b[38;5;129;01mand\u001b[39;00m out.dtype != \u001b[33m\"\u001b[39m\u001b[33mboolean\u001b[39m\u001b[33m\"\u001b[39m:\n\u001b[32m 11592\u001b[39m out = out.astype(out_dtype)\n\u001b[32m> \u001b[39m\u001b[32m11593\u001b[39m \u001b[38;5;28;01melif\u001b[39;00m (\u001b[43mdf\u001b[49m\u001b[43m.\u001b[49m\u001b[43m_mgr\u001b[49m\u001b[43m.\u001b[49m\u001b[43mget_dtypes\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m == \u001b[38;5;28mobject\u001b[39m).any() \u001b[38;5;129;01mand\u001b[39;00m name \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;129;01min\u001b[39;00m [\u001b[33m\"\u001b[39m\u001b[33many\u001b[39m\u001b[33m\"\u001b[39m, \u001b[33m\"\u001b[39m\u001b[33mall\u001b[39m\u001b[33m\"\u001b[39m]:\n\u001b[32m 11594\u001b[39m out = out.astype(\u001b[38;5;28mobject\u001b[39m)\n\u001b[32m 11595\u001b[39m \u001b[38;5;28;01melif\u001b[39;00m \u001b[38;5;28mlen\u001b[39m(\u001b[38;5;28mself\u001b[39m) == \u001b[32m0\u001b[39m \u001b[38;5;129;01mand\u001b[39;00m out.dtype == \u001b[38;5;28mobject\u001b[39m \u001b[38;5;129;01mand\u001b[39;00m name \u001b[38;5;129;01min\u001b[39;00m (\u001b[33m\"\u001b[39m\u001b[33msum\u001b[39m\u001b[33m\"\u001b[39m, \u001b[33m\"\u001b[39m\u001b[33mprod\u001b[39m\u001b[33m\"\u001b[39m):\n\u001b[32m 11596\u001b[39m \u001b[38;5;66;03m# Even if we are object dtype, follow numpy and return\u001b[39;00m\n\u001b[32m 11597\u001b[39m \u001b[38;5;66;03m# float64, see test_apply_funcs_over_empty\u001b[39;00m\n",
"\u001b[36mFile \u001b[39m\u001b[32m/opt/python/lib/python3.13/site-packages/pandas/core/internals/managers.py:289\u001b[39m, in \u001b[36mBaseBlockManager.get_dtypes\u001b[39m\u001b[34m(self)\u001b[39m\n\u001b[32m 287\u001b[39m \u001b[38;5;28;01mdef\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34mget_dtypes\u001b[39m(\u001b[38;5;28mself\u001b[39m) -> npt.NDArray[np.object_]:\n\u001b[32m 288\u001b[39m dtypes = np.array([blk.dtype \u001b[38;5;28;01mfor\u001b[39;00m blk \u001b[38;5;129;01min\u001b[39;00m \u001b[38;5;28mself\u001b[39m.blocks], dtype=\u001b[38;5;28mobject\u001b[39m)\n\u001b[32m--> \u001b[39m\u001b[32m289\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m dtypes.take(\u001b[38;5;28;43mself\u001b[39;49m\u001b[43m.\u001b[49m\u001b[43mblknos\u001b[49m)\n",
"\u001b[36mFile \u001b[39m\u001b[32m/opt/python/lib/python3.13/site-packages/pandas/core/internals/managers.py:192\u001b[39m, in \u001b[36mBaseBlockManager.blknos\u001b[39m\u001b[34m(self)\u001b[39m\n\u001b[32m 182\u001b[39m \u001b[38;5;250m\u001b[39m\u001b[33;03m\"\"\"\u001b[39;00m\n\u001b[32m 183\u001b[39m \u001b[33;03mSuppose we want to find the array corresponding to our i'th column.\u001b[39;00m\n\u001b[32m 184\u001b[39m \n\u001b[32m (...)\u001b[39m\u001b[32m 188\u001b[39m \u001b[33;03mself.blocks[self.blknos[i]]\u001b[39;00m\n\u001b[32m 189\u001b[39m \u001b[33;03m\"\"\"\u001b[39;00m\n\u001b[32m 190\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m._blknos \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[32m 191\u001b[39m \u001b[38;5;66;03m# Note: these can be altered by other BlockManager methods.\u001b[39;00m\n\u001b[32m--> \u001b[39m\u001b[32m192\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[43m.\u001b[49m\u001b[43m_rebuild_blknos_and_blklocs\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[32m 194\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m._blknos\n",
"\u001b[31mKeyboardInterrupt\u001b[39m: "
]
}
],
"source": [
"# ============================================================\n",
"# RUN\n",
"# ============================================================\n",
"\n",
"code_changes = detect_code_changes_fast(\n",
" portfolio,\n",
" flows_matrix,\n",
" ruptures,\n",
" score_timeline\n",
")"
]
},
{
"cell_type": "code",
"execution_count": 5,
"id": "3054206e-13b8-4931-a1d8-e3cbb97eab7a",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\n",
"====================================\n",
"RUNNING DATA CHALLENGE ON: raw\n",
"====================================\n"
]
},
{
"name": "stderr",
"output_type": "stream",
"text": [
"100%|██████████| 18582/18582 [02:27<00:00, 126.15it/s]\n"
]
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"\n",
"Detected distributor code changes:\n",
" date old_code new_code portfolio_error\n",
"0 2015-02-28 200001285 200001992 0.090028\n",
"1 2015-02-28 200001771 420304 0.049979\n",
"2 2015-02-28 200001894 366541 0.215146\n",
"3 2015-02-28 200002064 412736 0.210827\n",
"4 2015-02-28 200002109 406337 0.173276\n",
"\n",
"====================================\n",
"RUNNING DATA CHALLENGE ON: clean\n",
"====================================\n"
]
},
{
"name": "stderr",
"output_type": "stream",
"text": [
"100%|██████████| 18521/18521 [02:34<00:00, 119.82it/s]\n"
]
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"\n",
"Detected distributor code changes:\n",
" date old_code new_code portfolio_error\n",
"0 2015-02-28 200001285 200001992 0.090028\n",
"1 2015-02-28 200001771 420304 0.049979\n",
"2 2015-02-28 200001894 366541 0.215146\n",
"3 2015-02-28 200002064 412736 0.210827\n",
"4 2015-02-28 200002109 406337 0.173276\n",
"\n",
"==============================\n",
"RAW VS CLEAN SCORE COMPARISON\n",
"==============================\n",
" date raw_scores clean_scores improvement relative_improvement\n",
"0 2015-01-31 5639.521248 5639.524696 0.003448 6.113170e-07\n",
"1 2015-02-28 5639.521248 5639.524696 0.003448 6.113170e-07\n",
"2 2015-03-31 5656.707028 5656.722266 0.015238 2.693820e-06\n",
"3 2015-04-30 5701.063923 5701.084181 0.020258 3.553334e-06\n",
"4 2015-05-31 5728.963044 5728.994764 0.031720 5.536759e-06\n",
" date raw_scores clean_scores improvement relative_improvement\n",
"125 2025-06-30 11703.280129 11703.402856 0.122727 0.000010\n",
"126 2025-07-31 11855.613232 11855.796565 0.183333 0.000015\n",
"127 2025-08-31 11962.226141 11962.553334 0.327193 0.000027\n",
"128 2025-09-30 12046.634770 12046.966956 0.332185 0.000028\n",
"129 2025-10-31 12189.843400 12190.351082 0.507683 0.000042\n"
]
}
],
"source": [
"# ============================================================\n",
"# RUN DATA CHALLENGE ON RAW AND CLEAN DATASETS\n",
"# ============================================================\n",
"\n",
"DATASETS = {\n",
" \"raw\": \"stocks.csv\",\n",
" \"clean\": \"stock_repaired.csv\"\n",
"}\n",
"\n",
"results = {}\n",
"code_changes_results = {}\n",
"\n",
"for name, file in DATASETS.items():\n",
"\n",
" print(\"\\n====================================\")\n",
" print(\"RUNNING DATA CHALLENGE ON:\", name)\n",
" print(\"====================================\")\n",
"\n",
" # ============================================================\n",
" # LOAD DATA\n",
" # ============================================================\n",
"\n",
" stocks = pd.read_csv(file, low_memory=False)\n",
" flows = pd.read_csv(\"flows.csv\", low_memory=False)\n",
"\n",
" stocks[\"Centralisation Date\"] = pd.to_datetime(stocks[\"Centralisation Date\"])\n",
" flows[\"Centralisation Date\"] = pd.to_datetime(flows[\"Centralisation Date\"])\n",
"\n",
" stocks[\"Registrar Account - ID\"] = stocks[\"Registrar Account - ID\"].astype(str).str.strip()\n",
" flows[\"Registrar Account - ID\"] = flows[\"Registrar Account - ID\"].astype(str).str.strip()\n",
"\n",
" stocks[\"Product - Isin\"] = stocks[\"Product - Isin\"].astype(str)\n",
" flows[\"Product - Isin\"] = flows[\"Product - Isin\"].astype(str)\n",
"\n",
" # ============================================================\n",
" # BUILD DATASET\n",
" # ============================================================\n",
"\n",
" df = stocks.merge(\n",
" flows,\n",
" on=[\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"],\n",
" how=\"left\"\n",
" )\n",
"\n",
" df[\"Quantity - NetFlows\"] = df[\"Quantity - NetFlows\"].fillna(0)\n",
"\n",
" df = df.sort_values(\n",
" [\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"]\n",
" )\n",
"\n",
" # ============================================================\n",
" # ACCOUNTING IDENTITY\n",
" # ============================================================\n",
"\n",
" df[\"prev_aum\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - AUM\"]\n",
" .shift(1)\n",
" )\n",
"\n",
" df[\"flow_lag\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - NetFlows\"]\n",
" .shift(1)\n",
" ).fillna(0)\n",
"\n",
" df[\"expected_aum\"] = df[\"prev_aum\"] + df[\"flow_lag\"]\n",
"\n",
" df[\"gap\"] = df[\"Quantity - AUM\"] - df[\"expected_aum\"]\n",
"\n",
" df[\"rel_error\"] = (\n",
" df[\"gap\"].abs() /\n",
" df[\"expected_aum\"].abs().clip(lower=1)\n",
" )\n",
"\n",
" # ============================================================\n",
" # ACCOUNT LEVEL ERROR\n",
" # ============================================================\n",
"\n",
" account_error = (\n",
" df.groupby([\"Centralisation Date\",\"Registrar Account - ID\"])\n",
" [\"rel_error\"]\n",
" .mean()\n",
" .reset_index()\n",
" )\n",
"\n",
" RUPTURE_THRESHOLD = 0.5\n",
"\n",
" ruptures = account_error[\n",
" account_error[\"rel_error\"] > RUPTURE_THRESHOLD\n",
" ]\n",
"\n",
" # ============================================================\n",
" # BUILD PORTFOLIO MATRICES\n",
" # ============================================================\n",
"\n",
" portfolio = (\n",
" stocks\n",
" .pivot_table(\n",
" index=[\"Centralisation Date\",\"Registrar Account - ID\"],\n",
" columns=\"Product - Isin\",\n",
" values=\"Quantity - AUM\",\n",
" aggfunc=\"sum\"\n",
" )\n",
" .fillna(0)\n",
" )\n",
"\n",
" flows_matrix = (\n",
" flows\n",
" .pivot_table(\n",
" index=[\"Centralisation Date\",\"Registrar Account - ID\"],\n",
" columns=\"Product - Isin\",\n",
" values=\"Quantity - NetFlows\",\n",
" aggfunc=\"sum\"\n",
" )\n",
" .fillna(0)\n",
" )\n",
"\n",
" # ============================================================\n",
" # SCORE COMPUTATION\n",
" # ============================================================\n",
"\n",
" error_matrix = (\n",
" account_error\n",
" .pivot(\n",
" index=\"Centralisation Date\",\n",
" columns=\"Registrar Account - ID\",\n",
" values=\"rel_error\"\n",
" )\n",
" .fillna(0)\n",
" )\n",
"\n",
" dates = error_matrix.index.values\n",
" errors = error_matrix.values\n",
"\n",
" scores = np.ones(errors.shape[1])\n",
"\n",
" score_history = []\n",
"\n",
" for i in range(len(dates)-1, -1, -1):\n",
"\n",
" quality = np.exp(-5 * errors[i])\n",
"\n",
" scores = scores * quality\n",
"\n",
" score_history.append({\n",
" \"date\": dates[i],\n",
" \"sum_scores\": scores.sum()\n",
" })\n",
"\n",
" score_timeline = (\n",
" pd.DataFrame(score_history)\n",
" .sort_values(\"date\")\n",
" )\n",
"\n",
" initial_score = score_timeline[\"sum_scores\"].iloc[-1]\n",
"\n",
" score_timeline[\"score_retention\"] = (\n",
" score_timeline[\"sum_scores\"] / initial_score\n",
" )\n",
"\n",
" results[name] = score_timeline.copy()\n",
"\n",
" # ============================================================\n",
" # CODE SURGERY\n",
" # ============================================================\n",
"\n",
" code_changes = detect_code_changes_fast(\n",
" portfolio,\n",
" flows_matrix,\n",
" ruptures,\n",
" score_timeline\n",
" )\n",
"\n",
" code_changes_results[name] = code_changes\n",
"\n",
"\n",
"# ============================================================\n",
"# BUILD COMPARISON TABLE\n",
"# ============================================================\n",
"\n",
"comparison = (\n",
" results[\"raw\"][[\"date\", \"sum_scores\"]]\n",
" .rename(columns={\"sum_scores\": \"raw_scores\"})\n",
" .merge(\n",
" results[\"clean\"][[\"date\", \"sum_scores\"]]\n",
" .rename(columns={\"sum_scores\": \"clean_scores\"}),\n",
" on=\"date\",\n",
" how=\"outer\"\n",
" )\n",
" .sort_values(\"date\")\n",
")\n",
"\n",
"comparison[\"improvement\"] = (\n",
" comparison[\"clean_scores\"] - comparison[\"raw_scores\"]\n",
")\n",
"\n",
"comparison[\"relative_improvement\"] = (\n",
" comparison[\"improvement\"] /\n",
" comparison[\"raw_scores\"].replace(0, np.nan)\n",
")\n",
"\n",
"print(\"\\n==============================\")\n",
"print(\"RAW VS CLEAN SCORE COMPARISON\")\n",
"print(\"==============================\")\n",
"\n",
"print(comparison.head())\n",
"print(comparison.tail())"
]
},
{
"cell_type": "code",
"execution_count": 7,
"id": "15c60063-b8e5-43f0-a662-67a2f5601408",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"TOTAL RAW SCORE : 1102753.964620689\n",
"TOTAL CLEAN SCORE : 1102778.1794964853\n",
"ABSOLUTE IMPROVEMENT : 24.21487579634413\n",
"RELATIVE IMPROVEMENT : 2.1958547938363796e-05\n"
]
}
],
"source": [
"total_raw = comparison[\"raw_scores\"].sum()\n",
"total_clean = comparison[\"clean_scores\"].sum()\n",
"\n",
"absolute_gain = total_clean - total_raw\n",
"relative_gain = absolute_gain / total_raw\n",
"\n",
"print(\"TOTAL RAW SCORE :\", total_raw)\n",
"print(\"TOTAL CLEAN SCORE :\", total_clean)\n",
"\n",
"print(\"ABSOLUTE IMPROVEMENT :\", absolute_gain)\n",
"print(\"RELATIVE IMPROVEMENT :\", relative_gain)"
]
},
{
"cell_type": "code",
"execution_count": 8,
"id": "28f02823-483c-4ed8-bf24-912c5ca7f7d3",
"metadata": {},
"outputs": [
{
"data": {
"image/png": "iVBORw0KGgoAAAANSUhEUgAAA1AAAAHDCAYAAAAqdvv1AAAAOnRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjEwLjgsIGh0dHBzOi8vbWF0cGxvdGxpYi5vcmcvwVt1zgAAAAlwSFlzAAAPYQAAD2EBqD+naQAAf1BJREFUeJzs3Xd4FXXaxvHvnHQCSQikQgihJ7RAKAm9BCJNUCwoqwgoroKKWLGgWBfWio3XXQVUUMFVVFQggvRI772EThIgpELqmfePmCMxgEETTsr9uS6va8/M78x5ZvKcbG5m5jeGaZomIiIiIiIi8qcs9i5ARERERESkolCAEhERERERKSEFKBERERERkRJSgBIRERERESkhBSgREREREZESUoASEREREREpIQUoERERERGRElKAEhERERERKSEFKBERERERkRJSgBIRkVJx1113Ub9+fXuXIX9Tjx496NGjh73LKBWHDx/GMAxmzpz5l94/c+ZMDMPg8OHDpVqXiFRsClAiYnfbt2/npptuIjg4GFdXV+rUqUOfPn1455137F2aiMhfMmfOHN566y17lyEiZUABSkTsas2aNbRr146tW7dyzz338O6773L33XdjsVh4++237V2eXIX//Oc/7N27195liJQLClAilZejvQsQkart5ZdfxtPTk/Xr1+Pl5VVkXVJS0jWt5fz581SrVu2afmZlkJmZibu7O05OTvYupVSoD0RE5Ep0BkpE7OrgwYM0b968WHgC8PX1Lbbss88+o0OHDlSrVo2aNWvSrVs3Fi9eXGTM+++/T/PmzXFxcSEwMJCxY8eSkpJSZEyPHj1o0aIFGzdupFu3blSrVo2nnnoKgOzsbJ577jkaNWqEi4sLQUFBPP7442RnZ5don9auXUv//v2pWbMm7u7utGrVqtjZtKVLl9K1a1fc3d3x8vJi8ODB7N69u8iY559/HsMw2LdvH//4xz/w9PTEx8eHZ599FtM0OXbsGIMHD8bDwwN/f39ef/31Iu9ftmwZhmHw5Zdf8tRTT+Hv74+7uzvXX389x44dKzJ25cqV3HzzzdSrV8+2zw8//DAXLlwoMu6uu+6ievXqHDx4kP79+1OjRg2GDx9uW/fHe6C++OILIiIiqFGjBh4eHrRs2bLYsTh06BA333wz3t7eVKtWjcjISH744YdL7svcuXN5+eWXqVu3Lq6urvTu3ZsDBw4UGXv+/Hn27NnDmTNnrvBTKnClPvj2228ZMGAAgYGBuLi40LBhQ1588UXy8/Nt7582bRoODg5F+uv111/HMAwmTJhgW5afn0+NGjV44oknLlvLwIEDadCgwSXXRUVF0a5dO9vr2NhYunTpgpeXF9WrV6dp06a2uv9MSb5Df1TS78SMGTPo1asXvr6+uLi4EBYWxgcffFBse/Xr12fgwIGsWrWKDh064OrqSoMGDfjkk0+KjU1JSWH8+PEEBQXh4uJCo0aNmDJlClartdi4u+66C09PT7y8vBgxYkSx7/2V7Ny5k169euHm5kbdunV56aWXin0GlKwvevTowQ8//MCRI0cwDAPDMGzfjZycHCZNmkRERASenp64u7vTtWtXfvnllxLXKiL2pTNQImJXwcHBxMXFsWPHDlq0aHHFsZMnT+b555+nU6dOvPDCCzg7O7N27VqWLl1K3759gYLQMXnyZKKjo7nvvvvYu3cvH3zwAevXr2f16tVFzpKcPXuWfv36MWzYMP7xj3/g5+eH1Wrl+uuvZ9WqVYwZM4bQ0FC2b9/Om2++yb59+5g/f/4Va4yNjWXgwIEEBATw0EMP4e/vz+7du1mwYAEPPfQQAD///DP9+vWjQYMGPP/881y4cIF33nmHzp07s2nTpmIh5NZbbyU0NJR//etf/PDDD7z00kt4e3vzf//3f/Tq1YspU6Ywe/ZsHn30Udq3b0+3bt2KvP/ll1/GMAyeeOIJkpKSeOutt4iOjmbLli24ubkBMG/ePM6fP899991HrVq1WLduHe+88w7Hjx9n3rx5RbaXl5dHTEwMXbp04bXXXrvs2ZrY2Fhuu+02evfuzZQpUwDYvXs3q1evth2LxMREOnXqxPnz53nwwQepVasWs2bN4vrrr+err77ihhtuKLLNf/3rX1gsFh599FFSU1OZOnUqw4cPZ+3atbYx69ato2fPnjz33HM8//zzV/x5waX7AAomEKhevToTJkygevXqLF26lEmTJpGWlsa///1vALp27YrVamXVqlUMHDgQKAijFouFlStX2j5j8+bNZGRkFPvZXOzWW2/lzjvvZP369bRv3962/MiRI/z666+2z9y5cycDBw6kVatWvPDCC7i4uHDgwAFWr179p/taku/QH13Nd+KDDz6gefPmXH/99Tg6OvL9999z//33Y7VaGTt2bJHtHjhwgJtuuonRo0czYsQIPv74Y+666y4iIiJo3rw5UBCGu3fvzokTJ7j33nupV68ea9asYeLEiZw6dcp2iZxpmgwePJhVq1bxz3/+k9DQUL755htGjBjxp8cEICEhgZ49e5KXl8eTTz6Ju7s7H374oe37cbGS9MXTTz9Namoqx48f58033wSgevXqAKSlpfHf//6X2267jXvuuYf09HQ++ugjYmJiWLduHeHh4SWqWUTsyBQRsaPFixebDg4OpoODgxkVFWU+/vjj5qJFi8ycnJwi4/bv329aLBbzhhtuMPPz84uss1qtpmmaZlJSkuns7Gz27du3yJh3333XBMyPP/7Ytqx79+4mYE6fPr3Itj799FPTYrGYK1euLLJ8+vTpJmCuXr36svuSl5dnhoSEmMHBwea5c+cuWaNpmmZ4eLjp6+trnj171rZs69atpsViMe+8807bsueee84EzDFjxhT5jLp165qGYZj/+te/bMvPnTtnurm5mSNGjLAt++WXX0zArFOnjpmWlmZbPnfuXBMw3377bduy8+fPF9ufV1991TQMwzxy5Iht2YgRI0zAfPLJJ4uNHzFihBkcHGx7/dBDD5keHh5mXl5esbGFxo8fbwJFjnd6eroZEhJi1q9f3/ZzLNyX0NBQMzs72zb27bffNgFz+/btxfb7ueeeu+znFrpcH5jmpY/Jvffea1arVs3MysoyTdM08/PzTQ8PD/Pxxx83TbPg51yrVi3z5ptvNh0cHMz09HTTNE3zjTfeMC0WS7G+uFhqaqrp4uJiPvLII0WWT506tcjP4c033zQB8/Tp03+6fxcryXfINAuOSffu3W2vr+Y7caljFhMTYzZo0KDIsuDgYBMwV6xYYVuWlJRUbP9ffPFF093d3dy3b1+R9z/55JOmg4ODefToUdM0TXP+/PkmYE6dOtU2Ji8vz+zatasJmDNmzLjcYTFN8/c+XLt2bZF6PD09TcCMj4+/4j7+sS9M0zQHDBhQ5PtwcV0X97BpFnx//fz8zFGjRl2xThEpH3QJn4jYVZ8+fYiLi+P6669n69atTJ06lZiYGOrUqcN3331nGzd//nysViuTJk3CYin6q8swDKDgzE5OTg7jx48vMuaee+7Bw8Oj2GVhLi4ujBw5ssiyefPmERoaSrNmzThz5oztv169egFc8TKbzZs3Ex8fz/jx44tdklhY46lTp9iyZQt33XUX3t7etvWtWrWiT58+/Pjjj8W2e/fdd9v+t4ODA+3atcM0TUaPHm1b7uXlRdOmTTl06FCx9995553UqFHD9vqmm24iICCgyGdd/C/tmZmZnDlzhk6dOmGaJps3by62zfvuu++yx+HimjIzM4mNjb3smB9//JEOHTrQpUsX27Lq1aszZswYDh8+zK5du4qMHzlyJM7OzrbXXbt2BSiy3z169MA0zRKdfYJL9wEUPSbp6emcOXOGrl272i4RBLBYLHTq1IkVK1YABWfYzp49y5NPPolpmsTFxQEFZ6VatGhxyUtVC3l4eNCvXz/mzp2LaZq25V9++SWRkZHUq1cPwLaNb7/99pKXmF1OSb5Dl3I134mLj1lqaipnzpyhe/fuHDp0iNTU1CLbDQsLs/38AHx8fIr18Lx58+jatSs1a9Ys8tnR0dHk5+fbjvuPP/6Io6Njkb5
"text/plain": [
"<Figure size 1000x500 with 1 Axes>"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"plt.figure(figsize=(10,5))\n",
"\n",
"plt.plot(comparison[\"date\"], comparison[\"raw_scores\"], label=\"Raw\")\n",
"plt.plot(comparison[\"date\"], comparison[\"clean_scores\"], label=\"Clean\")\n",
"\n",
"plt.legend()\n",
"plt.title(\"Score comparison: raw vs cleaned data\")\n",
"plt.grid(alpha=0.3)\n",
"plt.show()"
]
},
{
"cell_type": "code",
"execution_count": 14,
"id": "a002234f-deec-4283-8245-ffac74f1930b",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"[None]"
]
},
"execution_count": 14,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"import os\n",
"import s3fs\n",
"os.environ[\"AWS_ACCESS_KEY_ID\"] = 'N0C5PK75FDX2WXI8OVP1'\n",
"os.environ[\"AWS_SECRET_ACCESS_KEY\"] = 'nZvC2urUkG7EvhDsFDyaOslqr160aoWMs+5MP3Ft'\n",
"os.environ[\"AWS_SESSION_TOKEN\"] = 'eyJhbGciOiJIUzUxMiIsInR5cCI6IkpXVCJ9.eyJhY2Nlc3NLZXkiOiJOMEM1UEs3NUZEWDJXWEk4T1ZQMSIsImFjciI6IjAiLCJhbGxvd2VkLW9yaWdpbnMiOlsiKiJdLCJhdWQiOlsibWluaW8iLCJhY2NvdW50Il0sImF1dGhfdGltZSI6MTc3MzIyNzI3OCwiYXpwIjoib255eGlhLW1pbmlvIiwiZW1haWwiOiJzYXJhaC50aG91bXlyZUBlbnNhZS5mciIsImVtYWlsX3ZlcmlmaWVkIjp0cnVlLCJleHAiOjE3NzQ0MzY4OTksImZhbWlseV9uYW1lIjoiVEhPVU1ZUkUiLCJnaXZlbl9uYW1lIjoiU2FyYWgiLCJncm91cHMiOlsiYmRjLWRhdGEiLCJiZGMtY2FybWlnbmFjLWczIl0sImlhdCI6MTc3MzIyNzI5OSwiaXNzIjoiaHR0cHM6Ly9hdXRoLmdyb3VwZS1nZW5lcy5mci9yZWFsbXMvZ2VuZXMiLCJqdGkiOiI5Mjc0ODgyMy04OTgzLTQzYjktYTZhNy0xYjhlNDdiOTRjNTUiLCJuYW1lIjoiU2FyYWggVEhPVU1ZUkUiLCJwb2xpY3kiOiJzdHNvbmx5IiwicHJlZmVycmVkX3VzZXJuYW1lIjoic3Rob3VteXJlLWVuc2FlIiwicmVhbG1fYWNjZXNzIjp7InJvbGVzIjpbIm9mZmxpbmVfYWNjZXNzIiwiZGVmYXVsdC1yb2xlcy1nZW5lcyIsInVtYV9hdXRob3JpemF0aW9uIl19LCJyZXNvdXJjZV9hY2Nlc3MiOnsiYWNjb3VudCI6eyJyb2xlcyI6WyJtYW5hZ2UtYWNjb3VudCIsIm1hbmFnZS1hY2NvdW50LWxpbmtzIiwidmlldy1wcm9maWxlIl19fSwic2NvcGUiOiJvcGVuaWQgcHJvZmlsZSBlbWFpbCIsInNpZCI6IjRkODM3NWVmLTQwY2QtNDYyMi05NzIyLTI4YjhjZTQ2MWQ5YyIsInN1YiI6ImVhYWVkN2QyLWM4MjYtNGIxNC05MzczLTYwYjNhODhlMWFiNiIsInR5cCI6IkJlYXJlciJ9.hl_SekvaH9A22PMb3W0VQBSNO67LnaneIuLC-X5XBnzOO5GLV61aocDRfYC6hvVVhdzyewSTtD2kvdyJdeu6qA'\n",
"os.environ[\"AWS_DEFAULT_REGION\"] = 'us-east-1'\n",
"fs = s3fs.S3FileSystem(\n",
" client_kwargs={'endpoint_url': 'https://'+'minio-simple.lab.groupe-genes.fr'},\n",
" key = os.environ[\"AWS_ACCESS_KEY_ID\"], \n",
" secret = os.environ[\"AWS_SECRET_ACCESS_KEY\"], \n",
" token = os.environ[\"AWS_SESSION_TOKEN\"])\n",
"\n",
"# 3️⃣ Upload du CSV\n",
"local_file = \"stock_repaired.csv\"\n",
"s3_path = \"projet-bdc-carmignac-g3\"\n",
"\n",
"fs.put(local_file, s3_path)"
]
},
{
"cell_type": "code",
"execution_count": 16,
"id": "eeb8f32c-c717-4d48-85c5-248661b5a945",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\n",
"====================================\n",
"RUNNING COHERENCE SCORE ON: raw\n",
"====================================\n",
"GLOBAL SCORE: 0.756699778421513\n",
"\n",
"====================================\n",
"RUNNING COHERENCE SCORE ON: clean\n",
"====================================\n"
]
},
{
"ename": "KeyError",
"evalue": "'Centralisation Date'",
"output_type": "error",
"traceback": [
"\u001b[31m---------------------------------------------------------------------------\u001b[39m",
"\u001b[31mKeyError\u001b[39m Traceback (most recent call last)",
"\u001b[36mFile \u001b[39m\u001b[32m/opt/python/lib/python3.13/site-packages/pandas/core/indexes/base.py:3812\u001b[39m, in \u001b[36mIndex.get_loc\u001b[39m\u001b[34m(self, key)\u001b[39m\n\u001b[32m 3811\u001b[39m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[32m-> \u001b[39m\u001b[32m3812\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[43m.\u001b[49m\u001b[43m_engine\u001b[49m\u001b[43m.\u001b[49m\u001b[43mget_loc\u001b[49m\u001b[43m(\u001b[49m\u001b[43mcasted_key\u001b[49m\u001b[43m)\u001b[49m\n\u001b[32m 3813\u001b[39m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m err:\n",
"\u001b[36mFile \u001b[39m\u001b[32mpandas/_libs/index.pyx:167\u001b[39m, in \u001b[36mpandas._libs.index.IndexEngine.get_loc\u001b[39m\u001b[34m()\u001b[39m\n",
"\u001b[36mFile \u001b[39m\u001b[32mpandas/_libs/index.pyx:196\u001b[39m, in \u001b[36mpandas._libs.index.IndexEngine.get_loc\u001b[39m\u001b[34m()\u001b[39m\n",
"\u001b[36mFile \u001b[39m\u001b[32mpandas/_libs/hashtable_class_helper.pxi:7088\u001b[39m, in \u001b[36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[39m\u001b[34m()\u001b[39m\n",
"\u001b[36mFile \u001b[39m\u001b[32mpandas/_libs/hashtable_class_helper.pxi:7096\u001b[39m, in \u001b[36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[39m\u001b[34m()\u001b[39m\n",
"\u001b[31mKeyError\u001b[39m: 'Centralisation Date'",
"\nThe above exception was the direct cause of the following exception:\n",
"\u001b[31mKeyError\u001b[39m Traceback (most recent call last)",
"\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[16]\u001b[39m\u001b[32m, line 45\u001b[39m\n\u001b[32m 39\u001b[39m \u001b[38;5;66;03m# --------------------------------------------------------\u001b[39;00m\n\u001b[32m 40\u001b[39m \u001b[38;5;66;03m# LOAD STOCKS\u001b[39;00m\n\u001b[32m 41\u001b[39m \u001b[38;5;66;03m# --------------------------------------------------------\u001b[39;00m\n\u001b[32m 43\u001b[39m stocks = pd.read_csv(stock_file, low_memory=\u001b[38;5;28;01mFalse\u001b[39;00m)\n\u001b[32m---> \u001b[39m\u001b[32m45\u001b[39m stocks[\u001b[33m\"\u001b[39m\u001b[33mCentralisation Date\u001b[39m\u001b[33m\"\u001b[39m] = pd.to_datetime(\u001b[43mstocks\u001b[49m\u001b[43m[\u001b[49m\u001b[33;43m\"\u001b[39;49m\u001b[33;43mCentralisation Date\u001b[39;49m\u001b[33;43m\"\u001b[39;49m\u001b[43m]\u001b[49m)\n\u001b[32m 46\u001b[39m stocks[\u001b[33m\"\u001b[39m\u001b[33mRegistrar Account - ID\u001b[39m\u001b[33m\"\u001b[39m] = stocks[\u001b[33m\"\u001b[39m\u001b[33mRegistrar Account - ID\u001b[39m\u001b[33m\"\u001b[39m].astype(\u001b[38;5;28mstr\u001b[39m).str.strip()\n\u001b[32m 47\u001b[39m stocks[\u001b[33m\"\u001b[39m\u001b[33mProduct - Isin\u001b[39m\u001b[33m\"\u001b[39m] = stocks[\u001b[33m\"\u001b[39m\u001b[33mProduct - Isin\u001b[39m\u001b[33m\"\u001b[39m].astype(\u001b[38;5;28mstr\u001b[39m)\n",
"\u001b[36mFile \u001b[39m\u001b[32m/opt/python/lib/python3.13/site-packages/pandas/core/frame.py:4113\u001b[39m, in \u001b[36mDataFrame.__getitem__\u001b[39m\u001b[34m(self, key)\u001b[39m\n\u001b[32m 4111\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m.columns.nlevels > \u001b[32m1\u001b[39m:\n\u001b[32m 4112\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m._getitem_multilevel(key)\n\u001b[32m-> \u001b[39m\u001b[32m4113\u001b[39m indexer = \u001b[38;5;28;43mself\u001b[39;49m\u001b[43m.\u001b[49m\u001b[43mcolumns\u001b[49m\u001b[43m.\u001b[49m\u001b[43mget_loc\u001b[49m\u001b[43m(\u001b[49m\u001b[43mkey\u001b[49m\u001b[43m)\u001b[49m\n\u001b[32m 4114\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m is_integer(indexer):\n\u001b[32m 4115\u001b[39m indexer = [indexer]\n",
"\u001b[36mFile \u001b[39m\u001b[32m/opt/python/lib/python3.13/site-packages/pandas/core/indexes/base.py:3819\u001b[39m, in \u001b[36mIndex.get_loc\u001b[39m\u001b[34m(self, key)\u001b[39m\n\u001b[32m 3814\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(casted_key, \u001b[38;5;28mslice\u001b[39m) \u001b[38;5;129;01mor\u001b[39;00m (\n\u001b[32m 3815\u001b[39m \u001b[38;5;28misinstance\u001b[39m(casted_key, abc.Iterable)\n\u001b[32m 3816\u001b[39m \u001b[38;5;129;01mand\u001b[39;00m \u001b[38;5;28many\u001b[39m(\u001b[38;5;28misinstance\u001b[39m(x, \u001b[38;5;28mslice\u001b[39m) \u001b[38;5;28;01mfor\u001b[39;00m x \u001b[38;5;129;01min\u001b[39;00m casted_key)\n\u001b[32m 3817\u001b[39m ):\n\u001b[32m 3818\u001b[39m \u001b[38;5;28;01mraise\u001b[39;00m InvalidIndexError(key)\n\u001b[32m-> \u001b[39m\u001b[32m3819\u001b[39m \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m(key) \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34;01merr\u001b[39;00m\n\u001b[32m 3820\u001b[39m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mTypeError\u001b[39;00m:\n\u001b[32m 3821\u001b[39m \u001b[38;5;66;03m# If we have a listlike key, _check_indexing_error will raise\u001b[39;00m\n\u001b[32m 3822\u001b[39m \u001b[38;5;66;03m# InvalidIndexError. Otherwise we fall through and re-raise\u001b[39;00m\n\u001b[32m 3823\u001b[39m \u001b[38;5;66;03m# the TypeError.\u001b[39;00m\n\u001b[32m 3824\u001b[39m \u001b[38;5;28mself\u001b[39m._check_indexing_error(key)\n",
"\u001b[31mKeyError\u001b[39m: 'Centralisation Date'"
]
}
],
"source": [
"# ============================================================\n",
"# DATASETS\n",
"# ============================================================\n",
"\n",
"DATASETS = {\n",
" \"raw\": \"stocks.csv\",\n",
"}\n",
"\n",
"flows_file = \"flows.csv\"\n",
"\n",
"results = {}\n",
"\n",
"# ============================================================\n",
"# LOAD FLOWS\n",
"# ============================================================\n",
"\n",
"flows = pd.read_csv(flows_file, low_memory=False)\n",
"\n",
"flows[\"Centralisation Date\"] = pd.to_datetime(flows[\"Centralisation Date\"])\n",
"flows[\"Registrar Account - ID\"] = flows[\"Registrar Account - ID\"].astype(str).str.strip()\n",
"flows[\"Product - Isin\"] = flows[\"Product - Isin\"].astype(str)\n",
"\n",
"flows[\"Quantity - NetFlows\"] = flows[\"Quantity - NetFlows\"].fillna(0)\n",
"\n",
"# ============================================================\n",
"# LOOP OVER DATASETS\n",
"# ============================================================\n",
"\n",
"for name, stock_file in DATASETS.items():\n",
"\n",
" print(\"\\n====================================\")\n",
" print(\"RUNNING COHERENCE SCORE ON:\", name)\n",
" print(\"====================================\")\n",
"\n",
" # --------------------------------------------------------\n",
" # LOAD STOCKS\n",
" # --------------------------------------------------------\n",
"\n",
" stocks = pd.read_csv(stock_file, low_memory=False)\n",
"\n",
" stocks[\"Centralisation Date\"] = pd.to_datetime(stocks[\"Centralisation Date\"])\n",
" stocks[\"Registrar Account - ID\"] = stocks[\"Registrar Account - ID\"].astype(str).str.strip()\n",
" stocks[\"Product - Isin\"] = stocks[\"Product - Isin\"].astype(str)\n",
"\n",
" # --------------------------------------------------------\n",
" # MERGE FLOWS\n",
" # --------------------------------------------------------\n",
"\n",
" df = stocks.merge(\n",
" flows,\n",
" on=[\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"],\n",
" how=\"left\"\n",
" )\n",
"\n",
" df[\"Quantity - NetFlows\"] = df[\"Quantity - NetFlows\"].fillna(0)\n",
"\n",
" df = df.sort_values(\n",
" [\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"]\n",
" )\n",
"\n",
" # --------------------------------------------------------\n",
" # ACCOUNTING RELATION\n",
" # --------------------------------------------------------\n",
"\n",
" df[\"prev_aum\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - AUM\"]\n",
" .shift(1)\n",
" )\n",
"\n",
" df[\"flow_lag\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - NetFlows\"]\n",
" .shift(1)\n",
" ).fillna(0)\n",
"\n",
" df[\"expected_aum\"] = df[\"prev_aum\"] + df[\"flow_lag\"]\n",
"\n",
" df[\"error\"] = df[\"Quantity - AUM\"] - df[\"expected_aum\"]\n",
"\n",
" # --------------------------------------------------------\n",
" # NORMALIZED ERROR\n",
" # --------------------------------------------------------\n",
"\n",
" df[\"scale\"] = df[\"prev_aum\"].abs().clip(lower=1)\n",
"\n",
" df[\"normalized_error\"] = df[\"error\"].abs() / df[\"scale\"]\n",
"\n",
" # --------------------------------------------------------\n",
" # OBSERVATION SCORE\n",
" # --------------------------------------------------------\n",
"\n",
" df[\"score_obs\"] = np.exp(-5 * df[\"normalized_error\"])\n",
"\n",
" # --------------------------------------------------------\n",
" # ACCOUNT SCORE\n",
" # --------------------------------------------------------\n",
"\n",
" account_score = (\n",
" df.groupby(\"Registrar Account - ID\")[\"score_obs\"]\n",
" .mean()\n",
" )\n",
"\n",
" # --------------------------------------------------------\n",
" # ACCOUNT WEIGHTS (31/10/2025)\n",
" # --------------------------------------------------------\n",
"\n",
" last_date = stocks[\"Centralisation Date\"].max()\n",
"\n",
" aum_last = (\n",
" stocks[stocks[\"Centralisation Date\"] == last_date]\n",
" .groupby(\"Registrar Account - ID\")[\"Quantity - AUM\"]\n",
" .sum()\n",
" )\n",
"\n",
" weights = aum_last / aum_last.sum()\n",
"\n",
" # --------------------------------------------------------\n",
" # ALIGN\n",
" # --------------------------------------------------------\n",
"\n",
" combined = pd.concat([account_score, weights], axis=1)\n",
" combined.columns = [\"score\", \"weight\"]\n",
"\n",
" combined = combined.fillna(0)\n",
"\n",
" # --------------------------------------------------------\n",
" # GLOBAL SCORE\n",
" # --------------------------------------------------------\n",
"\n",
" combined[\"weighted_score\"] = combined[\"score\"] * combined[\"weight\"]\n",
"\n",
" global_score = combined[\"weighted_score\"].sum()\n",
"\n",
" print(\"GLOBAL SCORE:\", global_score)\n",
"\n",
" results[name] = {\n",
" \"score\": global_score,\n",
" \"details\": combined\n",
" }\n",
"\n",
"# ============================================================\n",
"# COMPARISON\n",
"# ============================================================\n",
"\n",
"print(\"\\n====================================\")\n",
"print(\"RAW VS CLEAN COMPARISON\")\n",
"print(\"====================================\")\n",
"\n",
"raw_score = results[\"raw\"][\"score\"]\n"
]
},
{
"cell_type": "code",
"execution_count": 22,
"id": "ca380a04-155e-4fea-bfae-cfbfa9401245",
"metadata": {},
"outputs": [],
"source": [
"flows = pd.read_csv(flows_file, sep=None, engine=\"python\")\n",
"stocks = pd.read_csv(stock_file, sep=None, engine=\"python\")"
]
},
{
"cell_type": "code",
"execution_count": 23,
"id": "f515fcd8-872b-40c5-8a5b-4eacf5e51097",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Index(['Unnamed: 0', 'Agreement - Code', 'Company - Id',\n",
" 'Company - Ultimate Parent Id', 'Registrar Account - ID',\n",
" 'Registrar Account - Region', 'RegistrarAccount - Country',\n",
" 'Product - Asset Type', 'Product - Strategy', 'Product - Legal Status',\n",
" 'Product - Is Dedie ?', 'Product - Fund', 'Product - Shareclass Type',\n",
" 'Product - Shareclass Currency', 'Product - Isin',\n",
" 'Centralisation Date', 'Quantity - Subscription',\n",
" 'Quantity - Redemption', 'Quantity - NetFlows',\n",
" 'Value Ccy - Subscription', 'Value Ccy - Redemption',\n",
" 'Value Ccy - NetFlows', 'Value € - Subscription',\n",
" 'Value € - Redemption', 'Value € - NetFlows'],\n",
" dtype='object')\n",
"Index(['Unnamed: 0', 'Agreement - Code', 'Company - Id',\n",
" 'Company - Ultimate Parent Id', 'Registrar Account - ID',\n",
" 'Registrar Account - Region', 'RegistrarAccount - Country',\n",
" 'Product - Asset Type', 'Product - Strategy', 'Product - Legal Status',\n",
" 'Product - Is Dedie ?', 'Product - Fund', 'Product - Shareclass Type',\n",
" 'Product - Shareclass Currency', 'Product - Isin',\n",
" 'Centralisation Date', 'Quantity - AUM', 'Value - AUM CCY',\n",
" 'Value - AUM €'],\n",
" dtype='object')\n"
]
}
],
"source": [
"print(flows.columns)\n",
"print(stocks.columns)"
]
},
{
"cell_type": "code",
"execution_count": 24,
"id": "4ce0bfea-7714-4fd7-ba29-17ce8d976ab6",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\n",
"====================================\n",
"RUNNING COHERENCE SCORE ON: raw\n",
"====================================\n",
"GLOBAL SCORE: 0.756699778421513\n",
"\n",
"====================================\n",
"RUNNING COHERENCE SCORE ON: clean\n",
"====================================\n",
"GLOBAL SCORE: 0.756699778421513\n",
"\n",
"====================================\n",
"RAW VS CLEAN COMPARISON\n",
"====================================\n",
"RAW SCORE : 0.756699778421513\n",
"CLEAN SCORE : 0.756699778421513\n",
"IMPROVEMENT : 0.0\n",
"RELATIVE IMPROVEMENT : 0.0\n"
]
}
],
"source": [
"import pandas as pd\n",
"import numpy as np\n",
"\n",
"# ============================================================\n",
"# DATASETS\n",
"# ============================================================\n",
"\n",
"DATASETS = {\n",
" \"raw\": \"stocks.csv\", \n",
" \"clean\": \"stock_repaired.csv\"\n",
"}\n",
"\n",
"flows_file = \"flows.csv\"\n",
"\n",
"results = {}\n",
"\n",
"# ============================================================\n",
"# LOAD FLOWS\n",
"# ============================================================\n",
"\n",
"flows[\"Centralisation Date\"] = pd.to_datetime(flows[\"Centralisation Date\"])\n",
"flows[\"Registrar Account - ID\"] = flows[\"Registrar Account - ID\"].astype(str).str.strip()\n",
"flows[\"Product - Isin\"] = flows[\"Product - Isin\"].astype(str)\n",
"\n",
"flows[\"Quantity - NetFlows\"] = flows[\"Quantity - NetFlows\"].fillna(0)\n",
"\n",
"# ============================================================\n",
"# LOOP OVER DATASETS\n",
"# ============================================================\n",
"\n",
"for name, stock_file in DATASETS.items():\n",
"\n",
" print(\"\\n====================================\")\n",
" print(\"RUNNING COHERENCE SCORE ON:\", name)\n",
" print(\"====================================\")\n",
"\n",
" # --------------------------------------------------------\n",
" # LOAD STOCKS\n",
" # --------------------------------------------------------\n",
"\n",
" stocks[\"Centralisation Date\"] = pd.to_datetime(stocks[\"Centralisation Date\"])\n",
" stocks[\"Registrar Account - ID\"] = stocks[\"Registrar Account - ID\"].astype(str).str.strip()\n",
" stocks[\"Product - Isin\"] = stocks[\"Product - Isin\"].astype(str)\n",
"\n",
" # --------------------------------------------------------\n",
" # MERGE FLOWS\n",
" # --------------------------------------------------------\n",
"\n",
" df = stocks.merge(\n",
" flows,\n",
" on=[\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"],\n",
" how=\"left\"\n",
" )\n",
"\n",
" df[\"Quantity - NetFlows\"] = df[\"Quantity - NetFlows\"].fillna(0)\n",
"\n",
" df = df.sort_values(\n",
" [\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"]\n",
" )\n",
"\n",
" # --------------------------------------------------------\n",
" # ACCOUNTING RELATION\n",
" # --------------------------------------------------------\n",
"\n",
" df[\"prev_aum\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - AUM\"]\n",
" .shift(1)\n",
" )\n",
"\n",
" df[\"flow_lag\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - NetFlows\"]\n",
" .shift(1)\n",
" ).fillna(0)\n",
"\n",
" df[\"expected_aum\"] = df[\"prev_aum\"] + df[\"flow_lag\"]\n",
"\n",
" df[\"error\"] = df[\"Quantity - AUM\"] - df[\"expected_aum\"]\n",
"\n",
" # --------------------------------------------------------\n",
" # NORMALIZED ERROR\n",
" # --------------------------------------------------------\n",
"\n",
" df[\"scale\"] = df[\"prev_aum\"].abs().clip(lower=1)\n",
"\n",
" df[\"normalized_error\"] = df[\"error\"].abs() / df[\"scale\"]\n",
"\n",
" # --------------------------------------------------------\n",
" # OBSERVATION SCORE\n",
" # --------------------------------------------------------\n",
"\n",
" df[\"score_obs\"] = np.exp(-5 * df[\"normalized_error\"])\n",
"\n",
" # --------------------------------------------------------\n",
" # ACCOUNT SCORE\n",
" # --------------------------------------------------------\n",
"\n",
" account_score = (\n",
" df.groupby(\"Registrar Account - ID\")[\"score_obs\"]\n",
" .mean()\n",
" )\n",
"\n",
" # --------------------------------------------------------\n",
" # ACCOUNT WEIGHTS (31/10/2025)\n",
" # --------------------------------------------------------\n",
"\n",
" last_date = stocks[\"Centralisation Date\"].max()\n",
"\n",
" aum_last = (\n",
" stocks[stocks[\"Centralisation Date\"] == last_date]\n",
" .groupby(\"Registrar Account - ID\")[\"Quantity - AUM\"]\n",
" .sum()\n",
" )\n",
"\n",
" weights = aum_last / aum_last.sum()\n",
"\n",
" combined = pd.concat([account_score, weights], axis=1)\n",
" combined.columns = [\"score\", \"weight\"]\n",
" combined = combined.fillna(0)\n",
"\n",
" combined[\"weighted_score\"] = combined[\"score\"] * combined[\"weight\"]\n",
"\n",
" global_score = combined[\"weighted_score\"].sum()\n",
"\n",
" print(\"GLOBAL SCORE:\", global_score)\n",
"\n",
" results[name] = global_score\n",
"\n",
"# ============================================================\n",
"# COMPARISON\n",
"# ============================================================\n",
"\n",
"print(\"\\n====================================\")\n",
"print(\"RAW VS CLEAN COMPARISON\")\n",
"print(\"====================================\")\n",
"\n",
"raw_score = results[\"raw\"]\n",
"clean_score = results[\"clean\"]\n",
"\n",
"improvement = clean_score - raw_score\n",
"relative = improvement / raw_score\n",
"\n",
"print(\"RAW SCORE :\", raw_score)\n",
"print(\"CLEAN SCORE :\", clean_score)\n",
"print(\"IMPROVEMENT :\", improvement)\n",
"print(\"RELATIVE IMPROVEMENT :\", relative)"
]
},
{
"cell_type": "code",
"execution_count": 26,
"id": "d5d2a309-f843-47ff-b256-66e7d4843c0b",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"TRAJECTORY STABILITY SCORE : 0.7577041546254711\n"
]
}
],
"source": [
"# ============================================================\n",
"# TRAJECTORY STABILITY SCORE\n",
"# ============================================================\n",
"\n",
"# merge stocks + flows\n",
"df = stocks.merge(\n",
" flows,\n",
" on=[\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"],\n",
" how=\"left\"\n",
")\n",
"\n",
"df[\"Quantity - NetFlows\"] = df[\"Quantity - NetFlows\"].fillna(0)\n",
"\n",
"df = df.sort_values(\n",
" [\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"]\n",
")\n",
"\n",
"# ------------------------------------------------------------\n",
"# COMPUTE DELTA AUM\n",
"# ------------------------------------------------------------\n",
"\n",
"df[\"prev_aum\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - AUM\"]\n",
" .shift(1)\n",
")\n",
"\n",
"df[\"delta_aum\"] = df[\"Quantity - AUM\"] - df[\"prev_aum\"]\n",
"\n",
"# ------------------------------------------------------------\n",
"# TRAJECTORY ERROR\n",
"# ------------------------------------------------------------\n",
"\n",
"df[\"trajectory_error\"] = df[\"delta_aum\"] - df[\"Quantity - NetFlows\"]\n",
"\n",
"# normalize relative to portfolio size\n",
"df[\"scale\"] = df[\"prev_aum\"].abs().clip(lower=1)\n",
"\n",
"df[\"normalized_error\"] = df[\"trajectory_error\"].abs() / df[\"scale\"]\n",
"\n",
"# ------------------------------------------------------------\n",
"# SCORE PER OBSERVATION\n",
"# ------------------------------------------------------------\n",
"\n",
"df[\"score_obs\"] = np.exp(-5 * df[\"normalized_error\"])\n",
"\n",
"# ------------------------------------------------------------\n",
"# SCORE PER DISTRIBUTOR\n",
"# ------------------------------------------------------------\n",
"\n",
"account_score = (\n",
" df.groupby(\"Registrar Account - ID\")[\"score_obs\"]\n",
" .mean()\n",
")\n",
"\n",
"# ------------------------------------------------------------\n",
"# WEIGHTS (AUM LAST DATE)\n",
"# ------------------------------------------------------------\n",
"\n",
"last_date = stocks[\"Centralisation Date\"].max()\n",
"\n",
"aum_last = (\n",
" stocks[stocks[\"Centralisation Date\"] == last_date]\n",
" .groupby(\"Registrar Account - ID\")[\"Quantity - AUM\"]\n",
" .sum()\n",
")\n",
"\n",
"weights = aum_last / aum_last.sum()\n",
"\n",
"# ------------------------------------------------------------\n",
"# GLOBAL SCORE\n",
"# ------------------------------------------------------------\n",
"\n",
"combined = pd.concat([account_score, weights], axis=1)\n",
"combined.columns = [\"score\", \"weight\"]\n",
"combined = combined.fillna(0)\n",
"\n",
"combined[\"weighted_score\"] = combined[\"score\"] * combined[\"weight\"]\n",
"\n",
"trajectory_score = combined[\"weighted_score\"].sum()\n",
"\n",
"print(\"TRAJECTORY STABILITY SCORE :\", trajectory_score)"
]
},
{
"cell_type": "code",
"execution_count": 27,
"id": "af46b28d-957c-44d8-b46c-fc3e8a76fdd0",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"MEAN |ΔAUM − Flow| RAW : 1279.1238370039505\n",
"MEAN |ΔAUM − Flow| CLEAN : 1324.7974438780602\n",
"ABSOLUTE IMPROVEMENT : -45.67360687410974\n",
"RELATIVE IMPROVEMENT : -0.03570694685910124\n"
]
}
],
"source": [
"# ============================================================\n",
"# MEAN ABSOLUTE TRAJECTORY ERROR\n",
"# ============================================================\n",
"\n",
"def compute_mean_error(stocks_df):\n",
"\n",
" df = stocks_df.merge(\n",
" flows,\n",
" on=[\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"],\n",
" how=\"left\"\n",
" )\n",
"\n",
" df[\"Quantity - NetFlows\"] = df[\"Quantity - NetFlows\"].fillna(0)\n",
"\n",
" df = df.sort_values(\n",
" [\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"]\n",
" )\n",
"\n",
" # previous AUM\n",
" df[\"prev_aum\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - AUM\"]\n",
" .shift(1)\n",
" )\n",
"\n",
" # delta AUM\n",
" df[\"delta_aum\"] = df[\"Quantity - AUM\"] - df[\"prev_aum\"]\n",
"\n",
" # trajectory error\n",
" df[\"trajectory_error\"] = df[\"delta_aum\"] - df[\"Quantity - NetFlows\"]\n",
"\n",
" # absolute error\n",
" df[\"abs_error\"] = df[\"trajectory_error\"].abs()\n",
"\n",
" return df[\"abs_error\"].mean()\n",
"\n",
"\n",
"# ------------------------------------------------------------\n",
"# COMPUTE RAW VS CLEAN\n",
"# ------------------------------------------------------------\n",
"\n",
"raw_error = compute_mean_error(stocks)\n",
"clean_error = compute_mean_error(stocks_repaired)\n",
"\n",
"print(\"MEAN |ΔAUM − Flow| RAW :\", raw_error)\n",
"print(\"MEAN |ΔAUM − Flow| CLEAN :\", clean_error)\n",
"\n",
"improvement = raw_error - clean_error\n",
"relative = improvement / raw_error\n",
"\n",
"print(\"ABSOLUTE IMPROVEMENT :\", improvement)\n",
"print(\"RELATIVE IMPROVEMENT :\", relative)"
]
},
{
"cell_type": "code",
"execution_count": 30,
"id": "70938ac0-f9c3-47d2-b4d7-cdefbee72add",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"MEAN RELATIVE |ΔAUM| RAW : 0.382395981437048\n",
"MEAN RELATIVE |ΔAUM| CLEAN : 0.3956454388801965\n",
"VAR RELATIVE ΔAUM RAW : 55.51684717820779\n",
"VAR RELATIVE ΔAUM CLEAN : 55.8099150101412\n",
"\n",
"IMPROVEMENTS\n",
"MEAN IMPROVEMENT : -0.013249457443148482\n",
"VAR IMPROVEMENT : -0.29306783193340635\n"
]
}
],
"source": [
"# ============================================================\n",
"# NORMALIZED AUM STABILITY (ROBUST VERSION)\n",
"# ============================================================\n",
"\n",
"def compute_aum_stability_filtered(stocks_df, min_aum=1000):\n",
"\n",
" df = stocks_df.sort_values(\n",
" [\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"]\n",
" ).copy()\n",
"\n",
" df[\"prev_aum\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - AUM\"]\n",
" .shift(1)\n",
" )\n",
"\n",
" df[\"delta_aum\"] = df[\"Quantity - AUM\"] - df[\"prev_aum\"]\n",
"\n",
" # filter small portfolios\n",
" df = df[df[\"prev_aum\"].abs() > min_aum]\n",
"\n",
" df[\"normalized_delta\"] = df[\"delta_aum\"].abs() / df[\"prev_aum\"].abs()\n",
"\n",
" mean_norm_delta = df[\"normalized_delta\"].mean()\n",
" var_norm_delta = df[\"normalized_delta\"].var()\n",
"\n",
" return mean_norm_delta, var_norm_delta\n",
"\n",
"\n",
"# ------------------------------------------------------------\n",
"# RAW\n",
"# ------------------------------------------------------------\n",
"\n",
"raw_mean, raw_var = compute_aum_stability_filtered(stocks)\n",
"\n",
"# ------------------------------------------------------------\n",
"# CLEAN\n",
"# ------------------------------------------------------------\n",
"\n",
"clean_mean, clean_var = compute_aum_stability_filtered(stocks_repaired)\n",
"\n",
"# ------------------------------------------------------------\n",
"# RESULTS\n",
"# ------------------------------------------------------------\n",
"\n",
"print(\"MEAN RELATIVE |ΔAUM| RAW :\", raw_mean)\n",
"print(\"MEAN RELATIVE |ΔAUM| CLEAN :\", clean_mean)\n",
"\n",
"print(\"VAR RELATIVE ΔAUM RAW :\", raw_var)\n",
"print(\"VAR RELATIVE ΔAUM CLEAN :\", clean_var)\n",
"\n",
"print(\"\\nIMPROVEMENTS\")\n",
"\n",
"print(\"MEAN IMPROVEMENT :\", raw_mean - clean_mean)\n",
"print(\"VAR IMPROVEMENT :\", raw_var - clean_var)"
]
},
{
"cell_type": "code",
"execution_count": 34,
"id": "9d89bfeb-db8c-4c3c-af6e-fee7ea524dbd",
"metadata": {
"scrolled": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Number of large accounts: 433\n",
"\n",
"RAW DATA\n",
"mean_jump : 224994.9664587711\n",
"median_jump : 0.01670204997155296\n",
"jump>50% : 0.13255873973605017\n",
"jump>100% : 0.06362709256786046\n",
"jump>200% : 0.0557404201046879\n",
"\n",
"CLEAN DATA\n",
"mean_jump : 93408.81311850502\n",
"median_jump : 0.026516611646358403\n",
"jump>50% : 0.15272455736996207\n",
"jump>100% : 0.06512212855430446\n",
"jump>200% : 0.05549332861718643\n",
"\n",
"IMPROVEMENTS\n",
"mean_jump : 131586.1533402661\n",
"median_jump : -0.009814561674805444\n",
"jump>50% : -0.020165817633911898\n",
"jump>100% : -0.0014950359864440016\n",
"jump>200% : 0.0002470914875014746\n"
]
}
],
"source": [
"# ============================================================\n",
"# CLEANING QUALITY TEST\n",
"# ============================================================\n",
"\n",
"# ------------------------------------------------------------\n",
"# STEP 1 — SELECT LARGE ACCOUNTS (>5M€ at last date)\n",
"# ------------------------------------------------------------\n",
"\n",
"last_date = stocks[\"Centralisation Date\"].max()\n",
"\n",
"aum_last = (\n",
" stocks[stocks[\"Centralisation Date\"] == last_date]\n",
" .groupby(\"Registrar Account - ID\")[\"Value - AUM €\"]\n",
" .sum()\n",
")\n",
"\n",
"large_accounts = aum_last[aum_last >= 5_000_000].index\n",
"\n",
"print(\"Number of large accounts:\", len(large_accounts))\n",
"\n",
"stocks_large = stocks[\n",
" stocks[\"Registrar Account - ID\"].isin(large_accounts)\n",
"]\n",
"\n",
"stocks_repaired_large = stocks_repaired[\n",
" stocks_repaired[\"Registrar Account - ID\"].isin(large_accounts)\n",
"]\n",
"\n",
"\n",
"# ------------------------------------------------------------\n",
"# STEP 2 — COMPUTE RELATIVE AUM JUMPS\n",
"# ------------------------------------------------------------\n",
"\n",
"def compute_aum_jumps(df):\n",
"\n",
" df = df.sort_values(\n",
" [\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"]\n",
" ).copy()\n",
"\n",
" df[\"prev_aum\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Value - AUM €\"]\n",
" .shift(1)\n",
" )\n",
"\n",
" df[\"relative_jump\"] = (\n",
" (df[\"Value - AUM €\"] - df[\"prev_aum\"]).abs() /\n",
" df[\"prev_aum\"].abs().clip(lower=1)\n",
" )\n",
"\n",
" return df\n",
"\n",
"\n",
"raw_df = compute_aum_jumps(stocks_large)\n",
"clean_df = compute_aum_jumps(stocks_repaired_large)\n",
"\n",
"\n",
"# ------------------------------------------------------------\n",
"# STEP 3 — METRICS\n",
"# ------------------------------------------------------------\n",
"\n",
"def summarize_jumps(df):\n",
"\n",
" jumps = df[\"relative_jump\"].dropna()\n",
"\n",
" results = {\n",
" \"mean_jump\": jumps.mean(),\n",
" \"median_jump\": jumps.median(),\n",
" \"jump>50%\": (jumps > 0.5).mean(),\n",
" \"jump>100%\": (jumps > 1).mean(),\n",
" \"jump>200%\": (jumps > 2).mean()\n",
" }\n",
"\n",
" return results\n",
"\n",
"\n",
"raw_stats = summarize_jumps(raw_df)\n",
"clean_stats = summarize_jumps(clean_df)\n",
"\n",
"\n",
"# ------------------------------------------------------------\n",
"# STEP 4 — PRINT RESULTS\n",
"# ------------------------------------------------------------\n",
"\n",
"print(\"\\nRAW DATA\")\n",
"for k, v in raw_stats.items():\n",
" print(k, \":\", v)\n",
"\n",
"print(\"\\nCLEAN DATA\")\n",
"for k, v in clean_stats.items():\n",
" print(k, \":\", v)\n",
"\n",
"print(\"\\nIMPROVEMENTS\")\n",
"\n",
"for k in raw_stats:\n",
" print(k, \":\", raw_stats[k] - clean_stats[k])"
]
},
{
"cell_type": "code",
"execution_count": 35,
"id": "c6a4042e-85e4-476c-8fdb-daac82caa896",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"OBSERVATIONS CORRECTED : 32537\n",
"SERIES CORRECTED : 297\n",
"TOTAL AUM ADJUSTMENT : 390742193.9398935\n",
"MEAN ADJUSTMENT (corrected points) : 12009.164764418769\n"
]
}
],
"source": [
"# ============================================================\n",
"# CLEANING IMPACT METRICS\n",
"# ============================================================\n",
"\n",
"# merge raw vs cleaned\n",
"df_compare = stocks.merge(\n",
" stocks_repaired,\n",
" on=[\n",
" \"Registrar Account - ID\",\n",
" \"Product - Isin\",\n",
" \"Centralisation Date\"\n",
" ],\n",
" suffixes=(\"_raw\", \"_clean\")\n",
")\n",
"\n",
"# difference in AUM\n",
"df_compare[\"aum_diff\"] = (\n",
" df_compare[\"Quantity - AUM_clean\"] -\n",
" df_compare[\"Quantity - AUM_raw\"]\n",
")\n",
"\n",
"# ------------------------------------------------------------\n",
"# number of observations corrected\n",
"# ------------------------------------------------------------\n",
"\n",
"obs_corrected = (df_compare[\"aum_diff\"] != 0).sum()\n",
"\n",
"# ------------------------------------------------------------\n",
"# number of series corrected\n",
"# ------------------------------------------------------------\n",
"\n",
"series_corrected = (\n",
" df_compare[df_compare[\"aum_diff\"] != 0]\n",
" .groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" .ngroups\n",
")\n",
"\n",
"# ------------------------------------------------------------\n",
"# magnitude of corrections\n",
"# ------------------------------------------------------------\n",
"\n",
"total_adjustment = df_compare[\"aum_diff\"].abs().sum()\n",
"\n",
"mean_adjustment = (\n",
" df_compare.loc[df_compare[\"aum_diff\"] != 0, \"aum_diff\"]\n",
" .abs()\n",
" .mean()\n",
")\n",
"\n",
"# ------------------------------------------------------------\n",
"# results\n",
"# ------------------------------------------------------------\n",
"\n",
"print(\"OBSERVATIONS CORRECTED :\", obs_corrected)\n",
"print(\"SERIES CORRECTED :\", series_corrected)\n",
"\n",
"print(\"TOTAL AUM ADJUSTMENT :\", total_adjustment)\n",
"print(\"MEAN ADJUSTMENT (corrected points) :\", mean_adjustment)"
]
},
{
"cell_type": "code",
"execution_count": 42,
"id": "3bd29760-baa3-4916-a0d1-40ea6c898fb8",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"RAW SCORE : 8.867398273073611e-11\n",
"CLEAN SCORE : 1.9555471695728966e-10\n",
"IMPROVEMENT : 1.0688073422655355e-10\n",
"2.205322361025593\n"
]
}
],
"source": [
"# ============================================================\n",
"# SCORE 2 — TRAJECTORY STABILITY\n",
"# ============================================================\n",
"\n",
"def compute_stability_score(df):\n",
"\n",
" df = df.sort_values(\n",
" [\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"]\n",
" ).copy()\n",
"\n",
" df[\"prev_aum\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - AUM\"]\n",
" .shift(1)\n",
" )\n",
"\n",
" df[\"delta\"] = df[\"Quantity - AUM\"] - df[\"prev_aum\"]\n",
"\n",
" score = 1 / (1 + df[\"delta\"].var())\n",
"\n",
" return score\n",
"\n",
"raw_score2 = compute_stability_score(stocks_large)\n",
"clean_score2 = compute_stability_score(stocks_repaired_large)\n",
"\n",
"print(\"RAW SCORE :\", raw_score2)\n",
"print(\"CLEAN SCORE :\", clean_score2)\n",
"print(\"IMPROVEMENT :\", clean_score2 - raw_score2)\n",
"\n",
"improvement = clean_score2 / raw_score2\n",
"print(improvement)"
]
},
{
"cell_type": "code",
"execution_count": 39,
"id": "ba38f855-7b3a-4438-84e8-96b0c37892ef",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Number of large accounts: 433\n",
"stocks_large rows: 810442\n",
"flows_large rows: 1529403\n"
]
}
],
"source": [
"# ============================================================\n",
"# CREATE FLOWS_LARGE (same filter as stocks_large)\n",
"# ============================================================\n",
"\n",
"# last date\n",
"last_date = stocks[\"Centralisation Date\"].max()\n",
"\n",
"# AUM per account at last date (in euros)\n",
"aum_last = (\n",
" stocks[stocks[\"Centralisation Date\"] == last_date]\n",
" .groupby(\"Registrar Account - ID\")[\"Value - AUM €\"]\n",
" .sum()\n",
")\n",
"\n",
"# accounts > 5M€\n",
"large_accounts = aum_last[aum_last >= 5_000_000].index\n",
"\n",
"print(\"Number of large accounts:\", len(large_accounts))\n",
"\n",
"# filter datasets\n",
"stocks_large = stocks[\n",
" stocks[\"Registrar Account - ID\"].isin(large_accounts)\n",
"]\n",
"\n",
"stocks_repaired_large = stocks_repaired[\n",
" stocks_repaired[\"Registrar Account - ID\"].isin(large_accounts)\n",
"]\n",
"\n",
"flows_large = flows[\n",
" flows[\"Registrar Account - ID\"].isin(large_accounts)\n",
"]\n",
"\n",
"print(\"stocks_large rows:\", len(stocks_large))\n",
"print(\"flows_large rows:\", len(flows_large))"
]
},
{
"cell_type": "code",
"execution_count": 40,
"id": "5bb68d76-33c2-4167-a392-53474ee60816",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"RAW SCORE : 0.00017577210434760302\n",
"CLEAN SCORE : 0.0003594192235483644\n",
"IMPROVEMENT : 0.0001836471192007614\n"
]
}
],
"source": [
"# ============================================================\n",
"# FLOW CONSISTENCY SCORE\n",
"# ============================================================\n",
"\n",
"def compute_flow_consistency(df):\n",
"\n",
" df = df.merge(\n",
" flows_large,\n",
" on=[\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"],\n",
" how=\"left\"\n",
" )\n",
"\n",
" df[\"Quantity - NetFlows\"] = df[\"Quantity - NetFlows\"].fillna(0)\n",
"\n",
" df = df.sort_values(\n",
" [\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"]\n",
" )\n",
"\n",
" df[\"prev_aum\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - AUM\"]\n",
" .shift(1)\n",
" )\n",
"\n",
" df[\"delta\"] = df[\"Quantity - AUM\"] - df[\"prev_aum\"]\n",
"\n",
" df[\"error\"] = (df[\"delta\"] - df[\"Quantity - NetFlows\"]).abs()\n",
"\n",
" score = 1 / (1 + df[\"error\"].mean())\n",
"\n",
" return score\n",
"\n",
"raw_score3 = compute_flow_consistency(stocks_large)\n",
"clean_score3 = compute_flow_consistency(stocks_repaired_large)\n",
"\n",
"print(\"RAW SCORE :\", raw_score3)\n",
"print(\"CLEAN SCORE :\", clean_score3)\n",
"print(\"IMPROVEMENT :\", clean_score3 - raw_score3)"
]
},
{
"cell_type": "code",
"execution_count": 43,
"id": "64829ff4-46ee-4497-b6a2-87c835631062",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"RAW SCORE : 0.5623318126772077\n",
"CLEAN SCORE : 0.6635861577517481\n",
"IMPROVEMENT : 0.10125434507454045\n"
]
}
],
"source": [
"# PERSISTENT ACCOUNTING GAP SCORE\n",
"\n",
"def compute_persistent_gap_score(stocks_df, flows_df, persistence=3):\n",
"\n",
" df = stocks_df.merge(\n",
" flows_df,\n",
" on=[\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"],\n",
" how=\"left\"\n",
" )\n",
"\n",
" df[\"Quantity - NetFlows\"] = df[\"Quantity - NetFlows\"].fillna(0)\n",
"\n",
" df = df.sort_values(\n",
" [\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"]\n",
" ).copy()\n",
"\n",
" # previous AUM\n",
" df[\"prev_aum\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - AUM\"]\n",
" .shift(1)\n",
" )\n",
"\n",
" # accounting gap\n",
" df[\"gap\"] = (\n",
" df[\"Quantity - AUM\"] -\n",
" (df[\"prev_aum\"] + df[\"Quantity - NetFlows\"])\n",
" )\n",
"\n",
" persistent_count = 0\n",
" total_count = 0\n",
"\n",
" for (_, _), g in df.groupby([\"Registrar Account - ID\",\"Product - Isin\"]):\n",
"\n",
" gaps = g[\"gap\"].values\n",
"\n",
" for i in range(len(gaps) - persistence):\n",
"\n",
" window = gaps[i:i+persistence]\n",
"\n",
" if np.all(np.isfinite(window)):\n",
"\n",
" total_count += 1\n",
"\n",
" if np.all(np.abs(window - window[0]) < 1e-6):\n",
"\n",
" persistent_count += 1\n",
"\n",
" if total_count == 0:\n",
" return np.nan\n",
"\n",
" score = 1 - persistent_count / total_count\n",
"\n",
" return score\n",
"\n",
"\n",
"# ============================================================\n",
"# COMPUTE SCORES\n",
"# ============================================================\n",
"\n",
"raw_score = compute_persistent_gap_score(stocks_large, flows_large)\n",
"clean_score = compute_persistent_gap_score(stocks_repaired_large, flows_large)\n",
"\n",
"print(\"RAW SCORE :\", raw_score)\n",
"print(\"CLEAN SCORE :\", clean_score)\n",
"print(\"IMPROVEMENT :\", clean_score - raw_score)"
]
},
{
"cell_type": "code",
"execution_count": 44,
"id": "3a1365ae-6376-4d5a-8213-2846cafb6fd6",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"RAW SCORE : 0.09429052631578948\n",
"CLEAN SCORE : 0.14027954256670902\n",
"IMPROVEMENT : 0.04598901625091954\n",
"RELATIVE IMPROVEMENT : 48.77374010714206 %\n"
]
}
],
"source": [
"# ============================================================\n",
"# GAP HALF-LIFE SCORE\n",
"# ============================================================\n",
"\n",
"import numpy as np\n",
"\n",
"def compute_gap_halflife_score(stocks_df, flows_df):\n",
"\n",
" df = stocks_df.merge(\n",
" flows_df,\n",
" on=[\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"],\n",
" how=\"left\"\n",
" )\n",
"\n",
" df[\"Quantity - NetFlows\"] = df[\"Quantity - NetFlows\"].fillna(0)\n",
"\n",
" df = df.sort_values(\n",
" [\"Registrar Account - ID\",\"Product - Isin\",\"Centralisation Date\"]\n",
" ).copy()\n",
"\n",
" df[\"prev_aum\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - AUM\"]\n",
" .shift(1)\n",
" )\n",
"\n",
" df[\"gap\"] = (\n",
" df[\"Quantity - AUM\"] -\n",
" (df[\"prev_aum\"] + df[\"Quantity - NetFlows\"])\n",
" )\n",
"\n",
" persistence_lengths = []\n",
"\n",
" for (_, _), g in df.groupby([\"Registrar Account - ID\",\"Product - Isin\"]):\n",
"\n",
" gaps = g[\"gap\"].values\n",
"\n",
" run_length = 1\n",
"\n",
" for i in range(1, len(gaps)):\n",
"\n",
" if np.isfinite(gaps[i]) and np.isfinite(gaps[i-1]) and abs(gaps[i] - gaps[i-1]) < 1e-6:\n",
" run_length += 1\n",
" else:\n",
" if run_length > 1:\n",
" persistence_lengths.append(run_length)\n",
" run_length = 1\n",
"\n",
" if run_length > 1:\n",
" persistence_lengths.append(run_length)\n",
"\n",
" if len(persistence_lengths) == 0:\n",
" return np.nan\n",
"\n",
" mean_persistence = np.mean(persistence_lengths)\n",
"\n",
" score = 1 / (1 + mean_persistence)\n",
"\n",
" return score\n",
"\n",
"\n",
"# ============================================================\n",
"# COMPUTE SCORES\n",
"# ============================================================\n",
"\n",
"raw_score = compute_gap_halflife_score(stocks_large, flows_large)\n",
"clean_score = compute_gap_halflife_score(stocks_repaired_large, flows_large)\n",
"\n",
"print(\"RAW SCORE :\", raw_score)\n",
"print(\"CLEAN SCORE :\", clean_score)\n",
"print(\"IMPROVEMENT :\", clean_score - raw_score)\n",
"print(\"RELATIVE IMPROVEMENT :\", (clean_score/raw_score - 1) * 100, \"%\")"
]
},
{
"cell_type": "code",
"execution_count": 47,
"id": "f431e22e-abd2-4d78-9684-4612dea12df7",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"['Value - AUM €']"
]
},
"execution_count": 47,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"[\"Value - AUM €\"]"
]
},
{
"cell_type": "code",
"execution_count": 53,
"id": "fb3bc701-2f09-4876-8549-fd16b40f30e0",
"metadata": {},
"outputs": [
{
"data": {
"image/png": "iVBORw0KGgoAAAANSUhEUgAAAhYAAAIjCAYAAABf8FLNAAAAOnRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjEwLjgsIGh0dHBzOi8vbWF0cGxvdGxpYi5vcmcvwVt1zgAAAAlwSFlzAAAPYQAAD2EBqD+naQAARulJREFUeJzt3Xl8VPW9//H3mSQzJBmykZAQ9h1EAhEkQkVAUAErolap0gpWae1Fa+tyFX+9LN4qUDeqdSva4FZxuS5c5FosJYBIVRCUHcSwb8GEhISQSWa+vz8iU8YkZAgnTCZ5PR+PeUDOfOfM55yZybzz/X7POZYxxggAAMAGjlAXAAAAGg+CBQAAsA3BAgAA2IZgAQAAbEOwAAAAtiFYAAAA2xAsAACAbQgWAADANgQLAABgG4IFgJDJycmRZVnKyckJdSkAbEKwABqBefPmybIs/y0yMlKtW7fWxIkTtW/fvlCXVy8WLVqk6dOnh7oMv0ceeUTvv/9+qMsAQo5gATQiDz30kF599VU9//zzGjVqlF577TUNGTJEJ06cCHVptlu0aJFmzJgR6jL8CBZApchQFwDAPqNGjVL//v0lSbfddpuSk5M1e/ZsLViwQDfccEOIqwPQFNBjATRigwcPliTt2LHDv8zj8Wjq1Knq16+f4uPjFRsbq8GDB2vp0qUBj73gggt07bXXBizr3bu3LMvS119/7V/25ptvyrIsbd68+bS17N27V2PHjlVsbKxatmyp3/3udyorK6vSbsWKFbr++uvVrl07uVwutW3bVr/73e9UWlrqbzNx4kQ988wzkhQwBHTSY489pkGDBqlFixaKjo5Wv3799M4771R5ro8//lgXX3yxEhIS5Ha71b17dz344IMBbcrKyjRt2jR16dLFX89//ud/BtRuWZZKSkr08ssv+2uZOHHiafcH0FjRYwE0Yjt37pQkJSYm+pcVFRXpxRdf1I033qhJkybp2LFjeumll3TFFVfo888/V9++fSVVhpI33njD/7j8/Hxt3LhRDodDK1asUEZGhqTKIJCSkqKePXvWWEdpaamGDx+u3bt36ze/+Y3S09P16quv6p///GeVtm+//baOHz+uX//612rRooU+//xzPf3009q7d6/efvttSdKvfvUr7d+/Xx9//LFeffXVKuv405/+pDFjxmj8+PHyeDyaP3++rr/+ei1cuFBXXnmlJGnjxo368Y9/rIyMDD300ENyuVz65ptvtHLlSv96fD6fxowZo08++US//OUv1bNnT61fv15PPvmktm3b5h/6ePXVV3XbbbdpwIAB+uUvfylJ6ty5c20vD9A4GQBhLzs720gy//jHP0xeXp7Zs2ePeeedd0xKSopxuVxmz549/rYVFRWmrKws4PEFBQUmNTXV/OIXv/Ave/vtt40ks2nTJmOMMQsWLDAul8uMGTPGjBs3zt8uIyPDXHPNNaetb86cOUaSeeutt/zLSkpKTJcuXYwks3TpUv/y48ePV3n8zJkzjWVZZteuXf5lkydPNjX9CvvhOjwejzn//PPNpZde6l/25JNPGkkmLy+vxrpfffVV43A4zIoVKwKWP//880aSWblypX9ZbGysmTBhQo3rApoKhkKARmTEiBFKSUlR27Zt9ZOf/ESxsbFasGCB2rRp428TEREhp9MpqfIv8vz8fFVUVKh///768ssv/e1ODqMsX75cUmXPxIUXXqjLLrtMK1askCQdPXpUGzZs8LetyaJFi9SqVSv95Cc/8S+LiYnx/3V/qujoaP//S0pKdOTIEQ0aNEjGGK1duzao/XDqOgoKClRYWKjBgwcHbF9CQoIk6YMPPpDP56t2PW+//bZ69uypHj166MiRI/7bpZdeKklVho8ANNE5FsuXL9dVV12l9PR0WZZVp5ncxhg99thj6tatm1wul1q3bq2HH37Y/mKBM/DMM8/o448/1jvvvKPRo0fryJEjcrlcVdq9/PLLysjIULNmzdSiRQulpKToww8/VGFhob9Namqqunbt6g8RK1as0ODBg3XJJZdo//79+vbbb7Vy5Ur5fL5ag8WuXbvUpUuXgHkQktS9e/cqbXfv3q2JEycqKSlJbrdbKSkpGjJkiCQF1Hc6Cxcu1EUXXaRmzZopKSlJKSkpeu655wIeP27cOP3oRz/SbbfdptTUVP30pz/VW2+9FRAytm/fro0bNyolJSXg1q1bN0nS4cOHg6oHaEqa5ByLkpIS9enTR7/4xS+qTE4L1l133aXFixfrscceU+/evZWfn6/8/HybKwXOzIABA/xHhYwdO1YXX3yxbrrpJm3dulVut1uS9Nprr2nixIkaO3as7rvvPrVs2VIRERGaOXNmwCRPSbr44ou1ZMkSlZaWas2aNZo6darOP/98JSQkaMWKFdq8ebPcbrcyMzNtqd/r9eqyyy5Tfn6+7r//fvXo0UOxsbHat2+fJk6cWGPPwqlWrFihMWPG6JJLLtGzzz6rVq1aKSoqStnZ2frb3/7mbxcdHa3ly5dr6dKl+vDDD/XRRx/pzTff1KWXXqrFixcrIiJCPp9PvXv31hNPPFHtc7Vt29aW7QYalVCPxYSaJPPee+8FLDtx4oS55557THp6uomJiTEDBgwIGAPetGmTiYyMNFu2bDm3xQI1ODnH4osvvghYvnTpUiPJzJw507/s6quvNp06dTI+ny+g7aBBg0z79u0Dlv31r381ksxf//pX43A4zNGjR40xxvz4xz82t956qxk0aJC5/PLLa63v8ssvN+np6VWe849//GPAHIu1a9caSebll18OaLd48WIjyWRnZ/uX3XHHHdXOsbjrrrtMdHS0OXHiRMDym266qcY5GSc9/PDDRpL5+OOPjTHGjB492rRu3bpK3dVxu93MsQAMcyyqdccdd2jVqlWaP3++vv76a11//fUaOXKktm/fLkn63//9X3Xq1EkLFy5Ux44d1aFDB9122230WKDBGTp0qAYMGKA5c+b4T5IVEREhqXI476TPPvtMq1atqvL4k0Mcs2fPVkZGhuLj4/3LlyxZotWrV9c6DCJJo0eP1v79+wMO+Tx+/Lj+8pe/BLSrrjZjjP70pz9VWWdsbKykynkeP1yHZVnyer3+ZTt37qwy5Fnd5/XkETEnDyW94YYbtG/fPs2dO7dK29LSUpWUlATU88NagCYpxMEm5PSDHotdu3aZiIgIs2/fvoB2w4cPN1OmTDHGGPOrX/3KuFwuk5WVZZYvX26WLl1q+vbta4YNG3YuSwf8auqxMObfR3c899xzxph/90KMGTPGvPDCC+aBBx4wCQkJplevXlV6LIwxJi0tzUgyd955p3/ZqlWrjCQjyeTk5NRa38kjQJo1a2buv/9+M2fOHNOvXz+TkZER0GPh8XhM586dTXJysnn44YfN008/bYYOHWr69OlTpcfirbfeMpLMz3/+c/Paa6+ZN954wxhjzJIlS4wkM3jwYPPcc8+ZGTNmmJYtW/qf66S77rrLZGZmmt///vdm7ty55uGHHzatW7c2bdq08ffMeL1eM3r0aGNZlvnpT39qnn76aTNnzhxz++23m6SkpID9PXr0aBMbG2sef/xx88Ybb5h//etfte4XoDEiWPwgWCxcuNBIMrGxsQG3yMhIc8MNNxhjjJk0aZKRZLZu3ep/3Jo1a4wkhkcQEqcLFl6v13Tu3Nl07tzZVFRUGJ/PZx555BHTvn1743K5TGZmplm4cKGZMGFCtcHi+uuvN5LMm2++6V/m8XhMTEyMcTqdprS0NKgad+3aZcaMGWNiYmJMcnKyueuuu8xHH31U5XDTTZs2mREjRhi3222Sk5PNpEmTzFdffVUlWFRUVJg777zTpKSkGMuyAkLDSy+9ZLp27WpcLpfp0aOHyc7ONtOmTQtos2TJEnP11Veb9PR043Q6TXp6urnxxhvNtm3bAur2eDxm9uzZplevXsblcpnExETTr18/M2PGDFNYWOhvt2XLFnPJJZeY6OhoI4lhETRZljGn9Dk2QZZl6b333tPYsWMlVZ5FcPz48dq4caO/W/Ykt9uttLQ0TZs2TY888ojKy8v995WWliomJkaLFy/
"text/plain": [
"<Figure size 600x600 with 1 Axes>"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"image/png": "iVBORw0KGgoAAAANSUhEUgAAAhYAAAIjCAYAAABf8FLNAAAAOnRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjEwLjgsIGh0dHBzOi8vbWF0cGxvdGxpYi5vcmcvwVt1zgAAAAlwSFlzAAAPYQAAD2EBqD+naQAARoBJREFUeJzt3Xl8FPX9x/H3bJLdXOQigSSc4UbOFDSCIiCoIOJZRUsLWKXaotYqVrG/n4CtAtXifVX9BbUqivXgh/wQiwQQqQqKch8hEG6ChIQEyLH7/f2BbLMmkE2YsNnk9Xw89gE7+92Zz8xOsu985zszljHGCAAAwAaOQBcAAAAaDoIFAACwDcECAADYhmABAABsQ7AAAAC2IVgAAADbECwAAIBtCBYAAMA2BAsAAGAbggXQgLVt21bjxo0LdBk1lpWVJcuylJWVFehSANQQwQIIQtnZ2brtttvUrl07hYeHKyYmRhdccIGeeuopHTt2LNDlBdT8+fM1ZcqUQJfh9eijj+rDDz8MdBnAWUOwAILMxx9/rB49eujdd9/VyJEj9cwzz2jatGlq3bq17rvvPv3+978PdIkBNX/+fE2dOjXQZXgRLNDYhAa6AAD+y8nJ0Y033qg2bdros88+U0pKive1CRMmaOvWrfr4448DWCGAxo4eCyCI/PWvf1VRUZFeffVVn1BxUocOHartsTh8+LDuvvtutWrVSi6XSx06dNCMGTPk8Xh82j3++OPq37+/mjZtqoiICPXp00fvvfdepflZlqU77rhDH374obp37y6Xy6Vu3bppwYIFfq3Trl27dPXVVysqKkrNmjXTH/7wB5WUlFRqt2zZMl1//fVq3bq1XC6XWrVqpT/84Q8+h37GjRun5557zlvXyUdN1+nTTz/VhRdeqLi4OEVHR6tz58568MEHfdqUlJRo8uTJ6tChg7eeP/7xjz61W5al4uJivfbaa95agnHMC1AT9FgAQeR///d/1a5dO/Xv379W7z969KgGDhyo3bt367bbblPr1q31xRdfaNKkSdq7d6+efPJJb9unnnpKV155pUaPHq3S0lLNnj1b119/vebNm6cRI0b4zPfzzz/X+++/r9/97ndq0qSJnn76aV133XXKzc1V06ZNT1nPsWPHNGTIEOXm5uquu+5Samqq3njjDX322WeV2s6ZM0dHjx7Vb3/7WzVt2lRfffWVnnnmGe3atUtz5syRJN12223as2ePPv30U73xxhuV5uHPOq1bt05XXHGFevbsqYcfflgul0tbt27V8uXLvfPxeDy68sor9fnnn+s3v/mNunbtqjVr1uiJJ57Q5s2bvYc+3njjDd16660677zz9Jvf/EaS1L59e/8+LCBYGQBBoaCgwEgyV111ld/vadOmjRk7dqz3+Z///GcTFRVlNm/e7NPugQceMCEhISY3N9c77ejRoz5tSktLTffu3c3FF1/sM12ScTqdZuvWrd5p3333nZFknnnmmdPW9+STTxpJ5t133/VOKy4uNh06dDCSzOLFi09ZjzHGTJs2zViWZXbs2OGdNmHCBHOqX23+rNMTTzxhJJm8vLxT1v3GG28Yh8Nhli1b5jP9xRdfNJLM8uXLvdOioqJ8PgOgoeNQCBAkCgsLJUlNmjSp9TzmzJmjAQMGKD4+XgcPHvQ+hg4dKrfbraVLl3rbRkREeP+fn5+vgoICDRgwQN98802l+Q4dOtTnL/GePXsqJiZG27ZtO2098+fPV0pKin7+8597p0VGRnr/uq+oYj3FxcU6ePCg+vfvL2OMvv32W7/W3591iouLkyR99NFHlQ4PnTRnzhx17dpVXbp08dmOF198sSRp8eLFftUDNESNMlgsXbpUI0eOVGpqqizLqtWIbWOMHn/8cXXq1Ekul0stWrTQI488Yn+xwI9iYmIkSUeOHKn1PLZs2aIFCxYoKSnJ5zF06FBJ0oEDB7xt582bp/PPP1/h4eFKSEhQUlKSXnjhBRUUFFSab+vWrStNi4+PV35+/mnr2bFjhzp06OAzDkKSOnfuXKltbm6uxo0bp4SEBEVHRyspKUkDBw6UpCprqoo/6zRq1ChdcMEFuvXWW9W8eXPdeOONevfdd31CxpYtW7Ru3bpK27FTp06SfLcj0Ng0yjEWxcXF6tWrl37961/r2muvrdU8fv/732vhwoV6/PHH1aNHDx06dEiHDh2yuVLgP2JiYpSamqq1a9fWeh4ej0eXXHKJ/vjHP1b5+skvxmXLlunKK6/URRddpOeff14pKSkKCwtTZmam3nrrrUrvCwkJqXJ+xpha11qR2+3WJZdcokOHDun+++9Xly5dFBUVpd27d2vcuHGn7FmoyN91ioiI0NKlS7V48WJ9/PHHWrBggd555x1dfPHFWrhwoUJCQuTxeNSjRw/NnDmzymW1atXKlvUGglGjDBbDhw/X8OHDT/l6SUmJ/vSnP+ntt9/W4cOH1b17d82YMUODBg2SJG3YsEEvvPCC1q5d6/3LKi0t7WyUjkbuiiuu0N///netWLFC/fr1q/H727dvr6KiIm8Pxan885//VHh4uD755BO5XC7v9MzMzBov83TatGmjtWvXyhjj02uxadMmn3Zr1qzR5s2b9dprr2nMmDHe6Z9++mmlef609+OkmqyTw+HQkCFDNGTIEM2cOVOPPvqo/vSnP2nx4sXewz7fffedhgwZcsrlVVcP0FA1ykMh1bnjjju0YsUKzZ49W99//72uv/56DRs2TFu2bJH0n5H58+bNU1pamtq2batbb72VHgvUuT/+8Y+KiorSrbfeqv3791d6PTs7W0899dQp33/DDTdoxYoV+uSTTyq9dvjwYZWXl0s60QNhWZbcbrf39e3bt9t+oafLL79ce/bs8Tnl8+jRo/r73//u0+5kj0jFHhBjTJXrGhUVJenE+vx0Hv6sU1U/x71795Yk76mkN9xwg3bv3q2XX365Uttjx46puLjYp56f1gI0ZI2yx+J0cnNzlZmZqdzcXKWmpkqSJk6cqAULFigzM1OPPvqotm3bph07dmjOnDl6/fXX5Xa79Yc//EE///nPqzxNDrBL+/bt9dZbb2nUqFHq2rWrxowZo+7du6u0tFRffPGF5syZc9rrJNx3332aO3eurrjiCo0bN059+vRRcXGx1qxZo/fee0/bt29XYmKiRowYoZkzZ2rYsGH6xS9+oQMHDui5555Thw4d9P3339u2PuPHj9ezzz6rMWPGaNWqVUpJSdEbb7yhyMhIn3ZdunRR+/btNXHiRO3evVsxMTH65z//WeUYjj59+kiS7rrrLl122WUKCQnRjTfe6Pc6Pfzww1q6dKlGjBihNm3a6MCBA3r++efVsmVLXXjhhZKkX/3qV3r33Xd1++23a/Hixbrgggvkdru1ceNGvfvuu/rkk0/Ut29fbz3/+te/NHPmTKWmpiotLU0ZGRm2bUOg3gnkKSn1gSTzwQcfeJ/PmzfPSDJRUVE+j9DQUHPDDTcYY4wZP368kWQ2bdrkfd+qVauMJLNx48azvQpohDZv3mzGjx9v2rZta5xOp2nSpIm54IILzDPPPGOOHz/ubffT002NMebIkSNm0qRJpkOHDsbpdJrExETTv39/8/jjj5vS0lJvu1dffdV07NjRuFwu06VLF5OZmWkmT55c6VROSWbChAmVaqxq2VXZsWOHufLKK01kZKRJTEw0v//9782CBQsqnW66fv16M3ToUBMdHW0SExPN+PHjvae1ZmZmetuVl5ebO++80yQlJRnLsnzq9WedFi1aZK666iqTmppqnE6nSU1NNTfddFOlU3RLS0vNjBkzTLdu3YzL5TLx8fGmT58+ZurUqaagoMDbbuPGjeaiiy4yERERRhKnnqLBs4yxaXRVkLIsSx988IGuvvpqSdI777yj0aNHa926dZUGpEVHRys5OVmTJ0/Wo48+qrKyMu9rx44dU2RkpBYuXKhLLrnkbK4CAAD1BodCfiI9PV1ut1sHDhzQgAEDqmxzwQUXqLy8XNnZ2d5z9zdv3izpxGA0AAAaq0bZY1FUVKStW7dKOhEkZs6cqcGDByshIUGtW7fWL3/5Sy1
"text/plain": [
"<Figure size 600x600 with 1 Axes>"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"# ============================================================\n",
"# BUILD RAW AND CLEAN DATASETS FOR DIAGNOSTIC\n",
"# ============================================================\n",
"\n",
"def build_accounting_df(stocks_df, flows_df):\n",
"\n",
" keys = [\n",
" \"Registrar Account - ID\",\n",
" \"Product - Isin\",\n",
" \"Centralisation Date\"\n",
" ]\n",
"\n",
" df = stocks_df.merge(flows_df, on=keys, how=\"left\")\n",
"\n",
" df[\"Quantity - NetFlows\"] = df[\"Quantity - NetFlows\"].fillna(0)\n",
"\n",
" df = df.sort_values(keys)\n",
"\n",
" df[\"prev_aum\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - AUM\"]\n",
" .shift(1)\n",
" )\n",
"\n",
" df[\"flow_lag\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - NetFlows\"]\n",
" .shift(1)\n",
" )\n",
"\n",
" df[\"delta_aum\"] = df[\"Quantity - AUM\"] - df[\"prev_aum\"]\n",
"\n",
" return df\n",
"\n",
"\n",
"# RAW DATASET\n",
"df_raw = build_accounting_df(stocks, flows)\n",
"\n",
"# CLEAN DATASET\n",
"df_clean = build_accounting_df(stocks_repaired, flows)\n",
"\n",
"\n",
"# ============================================================\n",
"# SCATTER FUNCTION\n",
"# ============================================================\n",
"\n",
"import matplotlib.pyplot as plt\n",
"import numpy as np\n",
"\n",
"def accounting_scatter(df, title):\n",
"\n",
" diag = df[\n",
" df[\"prev_aum\"].notna() &\n",
" df[\"flow_lag\"].notna()\n",
" ]\n",
"\n",
" sample = diag.sample(20000, random_state=1)\n",
"\n",
" plt.figure(figsize=(6,6))\n",
"\n",
" plt.scatter(\n",
" sample[\"flow_lag\"],\n",
" sample[\"delta_aum\"],\n",
" alpha=0.3,\n",
" s=5\n",
" )\n",
"\n",
" x = np.linspace(\n",
" sample[\"flow_lag\"].min(),\n",
" sample[\"flow_lag\"].max(),\n",
" 100\n",
" )\n",
"\n",
" plt.plot(x, x, color=\"red\", label=\"Perfect identity\")\n",
"\n",
" plt.xlabel(\"Flow (t-1,t)\")\n",
" plt.ylabel(\"Δ AUM\")\n",
"\n",
" plt.title(title)\n",
"\n",
" plt.legend()\n",
"\n",
" plt.show()\n",
"\n",
"\n",
"# ============================================================\n",
"# PLOTS\n",
"# ============================================================\n",
"\n",
"accounting_scatter(df_raw, \"Raw dataset\")\n",
"accounting_scatter(df_clean, \"Clean dataset\")"
]
},
{
"cell_type": "code",
"execution_count": 56,
"id": "dc3ba2ce-42ca-490a-98b0-83fd14457dd8",
"metadata": {},
"outputs": [
{
"data": {
"image/png": "iVBORw0KGgoAAAANSUhEUgAAArMAAAF2CAYAAAB9KhCBAAAAOnRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjEwLjgsIGh0dHBzOi8vbWF0cGxvdGxpYi5vcmcvwVt1zgAAAAlwSFlzAAAPYQAAD2EBqD+naQAATuNJREFUeJzt3XdUFNffBvBnKbuAdFGaCBYUSxR7bEEjEdRoLIn1ZyGWGDVqMBZiFMTe0WjsijFGMSaWEGNU7EpiA7FgR1ERbEixgLD3/cPDvK4UdxFYR5/POZzj3rlz5zvjoA+XKQohhAARERERkQwZ6LsAIiIiIqLCYpglIiIiItlimCUiIiIi2WKYJSIiIiLZYpglIiIiItlimCUiIiIi2WKYJSIiIiLZYpglIiIiItlimCUiIiIi2WKYJSpBQUFBUCgUJbKtFi1aoEWLFtLn/fv3Q6FQYPPmzSWy/X79+sHNza1EtlVY6enpGDBgABwcHKBQKDBy5Eh9l/TWc3NzQ79+/fRdBhGRhGGWqJBCQ0OhUCikLxMTEzg5OcHHxwcLFy5EWlpakWwnISEBQUFBiI6OLpLxitLbXJs2pk2bhtDQUHz99ddYt24devfure+S3gpHjx5FUFAQHj16pO9S9Ebu5/brvOv7R+8XI30XQCR3wcHBqFChAp4/f47ExETs378fI0eOxLx587B9+3bUqlVL6vvDDz9g3LhxOo2fkJCASZMmwc3NDZ6enlqvt2vXLp22UxgF1bZixQqo1epir+FN7N27Fx9++CECAwP1Xcpb5ejRo5g0aRL69esHa2trjWUXL16EgcG7Pw9S2O87uXjX94/eLwyzRG+oTZs2qF+/vvQ5ICAAe/fuxaeffooOHTogNjYWpqamAAAjIyMYGRXvt92TJ09gZmYGpVJZrNt5HWNjY71uXxt3795F9erV9V2GrKhUKn2XIMnKyoJardb7ua6LZ8+eQalUFvkPBMU1LpEsCCIqlDVr1ggA4vjx43kunzZtmgAgli9fLrUFBgaKV7/tdu3aJZo2bSqsrKxEqVKlRJUqVURAQIAQQoh9+/YJALm+1qxZI4QQwsvLS9SoUUOcOHFCNG/eXJiamooRI0ZIy7y8vKTt5Iy1ceNGERAQIOzt7YWZmZlo3769iI+P16jJ1dVV9O3bN9c+vTzm62rr27evcHV11Vg/PT1d+Pv7i3LlygmlUimqVKkiZs+eLdRqtUY/AGLo0KFiy5YtokaNGkKpVIrq1auLv//+O89j/aqkpCTx5ZdfirJlywqVSiVq1aolQkNDcx2LV7/i4uLyHXP16tWiZcuWokyZMkKpVIpq1aqJn376Kc++O3bsEB999JEwNzcXFhYWon79+mL9+vUaff7991/Rpk0bYW1tLczMzMQHH3wgQkJCNPpERESIZs2aCTMzM2FlZSU6dOggzp8/r9Enr+MsRN7nmjbHNWe9/I7Nq+dGzvfB4cOHxbfffivs7OyEmZmZ6Nixo7h7967G9rOzs0VgYKBwdHQUpqamokWLFuLcuXP5nm8vi4uLEwDE7Nmzxfz580XFihWFgYGBiIqKkmp49e8v5+953759UtvL3zONGzcWJiYmws3NTSxZsiTXevmd29p8f7w8zoYNG8T48eOFk5OTUCgUIjk5WQjx4hzw8fERlpaWwtTUVHz00Ufi8OHDBR6H14374MEDMWrUKFGzZk1RqlQpYWFhIXx9fUV0dLTW+6dtbampqWLEiBHC1dVVKJVKUaZMGeHt7S1Onjz52n0gKkqcmSUqJr1798b333+PXbt2YeDAgXn2OXfuHD799FPUqlULwcHBUKlUuHLlCo4cOQIAqFatGoKDgzFx4kQMGjQIzZs3BwA0adJEGuPBgwdo06YNunfvjv/973+wt7cvsK6pU6dCoVBg7NixuHv3LkJCQuDt7Y3o6GhpBlkb2tT2MiEEOnTogH379qF///7w9PTEP//8g9GjR+P27duYP3++Rv/Dhw/jjz/+wJAhQ2BhYYGFCxeiS5cuiI+PR+nSpfOt6+nTp2jRogWuXLmCYcOGoUKFCvjtt9/Qr18/PHr0CCNGjEC1atWwbt06fPvttyhXrhxGjRoFAChTpky+4y5ZsgQ1atRAhw4dYGRkhD///BNDhgyBWq3G0KFDpX6hoaH48ssvUaNGDQQEBMDa2hpRUVHYuXMnevbsCQDYvXs3Pv30Uzg6OmLEiBFwcHBAbGwswsPDMWLECADAnj170KZNG1SsWBFBQUF4+vQpfvzxRzRt2hSnTp0q9M11rzuunTt3xqVLl7BhwwbMnz8fdnZ2rz02APDNN9/AxsYGgYGBuH79OkJCQjBs2DCEhYVJfQICAjBr1iy0b98ePj4+OH36NHx8fPDs2TOt61+zZg2ePXuGQYMGQaVSwdbWVudjkJycjLZt26Jr167o0aMHNm3ahK+//hpKpRJffvmlzuf260yePBlKpRLfffcdMjIyoFQqsXfvXrRp0wb16tVDYGAgDAwMsGbNGnz88cc4dOgQGjZsWKhxz58/j61bt+KLL75AhQoVkJSUhGXLlsHLywvnz5+Hk5PTa/dP29oGDx6MzZs3Y9iwYahevToePHiAw4cPIzY2FnXr1i3UsSIqFH2naSK5et3MrBBCWFlZiTp16kifX50tmz9/vgAg7t27l+8Yx48fzzVrksPLy0sAEEuXLs1zWV6zRM7OziI1NVVq37RpkwAgFixYILVpO/NUUG2vzhhu3bpVABBTpkzR6Pf5558LhUIhrly5IrUBEEqlUqPt9OnTAoD48ccfc23rZSEhIQKA+OWXX6S2zMxM0bhxY2Fubq6x766urqJdu3YFjpfjyZMnudp8fHxExYoVpc+PHj0SFhYWolGjRuLp06cafXNmn7OyskSFChWEq6urNEP3ah8hhPD09BRly5YVDx48kNpOnz4tDAwMRJ8+faQ2XWdmtTmus2fPznemOr+ZWW9vb436v/32W2FoaCgePXokhBAiMTFRGBkZiY4dO2qMFxQUJABoPTNraWmZa8ZX15lZAGLu3LlSW0ZGhnS8MzMzhRAFn9u6zsxWrFhR4/xRq9XC3d1d+Pj4aByzJ0+eiAoVKohPPvmkwGOR37hCCPHs2TORnZ2t0RYXFydUKpUIDg6W2vLbP11qs7KyEkOHDi2wVqKSwItriIqRubl5gU81yLm5Ztu2bYW+WUqlUsHPz0/r/n369IGFhYX0+fPPP4ejoyN27NhRqO1ra8eOHTA0NMTw4cM12keNGgUhBP7++2+Ndm9vb1SqVEn6XKtWLVhaWuLatWuv3Y6DgwN69OghtRkbG2P48OFIT0/HgQMHClX/y7PWKSkpuH//Pry8vHDt2jWkpKQAeDHjmpaWhnHjxsHExERj/ZxHskVFRSEuLg4jR47MdXNVTp87d+4gOjoa/fr105h5rFWrFj755JM3+rsq7HF9nUGDBmk8dq558+bIzs7GjRs3AAARERHIysrCkCFDNNb75ptvdNpOly5dXjtL/DpGRkb46quvpM9KpRJfffUV7t69i5MnT77R2Hnp27evxvkTHR2Ny5cvo2fPnnjw4AHu37+P+/fv4/Hjx2jVqhUOHjyo1b8Hr44LvPj3IOe62ezsbDx48ADm5uaoWrUqTp069doxdanN2toa//33HxISEnQ5HERFjmGWqBilp6drBMdXdevWDU2bNsWAAQNgb2+P7t27Y9OmTToFW2dnZ51ugHF3d9f4rFAoULlyZVy/fl3rMQrjxo0bcHJyynU8qlWrJi1/Wfny5XONYWNjg+Tk5Ndux93dPdeNMPltR1tHjhyBt7c3SpUqBWtra5QpUwbff/89AEhh9urVqwCAmjVr5juONn1yaqxatWquZdWqVZPCRWEU9rjqOq6NjQ0ASOPm7FPlypU1+tna2kp9tVGhQoU3KRMA4OTkhFKlSmm0ValSBQCK5fvg1ZovX74M4EUYLVOmjMbXypUrkZGRIZ1TuowLAGq1GvPnz4e7uztUKhXs7OxQpkwZxMTEaDWmLrXNmjULZ8+ehYuLCxo2bIigoKA3/qGIqDB4zSxRMbl16xZSUlJy/ef
"text/plain": [
"<Figure size 800x400 with 1 Axes>"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"# ============================================================\n",
"# BUILD RAW AND CLEAN ACCOUNTING DATASETS\n",
"# ============================================================\n",
"\n",
"def build_gap_df(stocks_df, flows_df):\n",
"\n",
" keys = [\n",
" \"Registrar Account - ID\",\n",
" \"Product - Isin\",\n",
" \"Centralisation Date\"\n",
" ]\n",
"\n",
" df = stocks_df.merge(flows_df, on=keys, how=\"left\")\n",
"\n",
" df[\"Quantity - NetFlows\"] = df[\"Quantity - NetFlows\"].fillna(0)\n",
"\n",
" df = df.sort_values(keys)\n",
"\n",
" df[\"prev_aum\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - AUM\"]\n",
" .shift(1)\n",
" )\n",
"\n",
" df[\"flow_lag\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - NetFlows\"]\n",
" .shift(1)\n",
" .fillna(0)\n",
" )\n",
"\n",
" df[\"expected_aum\"] = df[\"prev_aum\"] + df[\"flow_lag\"]\n",
"\n",
" df[\"gap\"] = df[\"Quantity - AUM\"] - df[\"expected_aum\"]\n",
"\n",
" df[\"rupture\"] = df[\"gap\"].abs() > 10\n",
"\n",
" return df\n",
"\n",
"\n",
"# RAW DATA\n",
"df_raw = build_gap_df(stocks, flows)\n",
"\n",
"# CLEAN DATA\n",
"df_clean = build_gap_df(stocks_repaired, flows)\n",
"\n",
"\n",
"# ============================================================\n",
"# DISTRIBUTION OF RUPTURE RATES\n",
"# ============================================================\n",
"\n",
"def rupture_distribution(df):\n",
"\n",
" series = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" .agg(\n",
" n_ruptures=(\"rupture\",\"sum\"),\n",
" total_obs=(\"rupture\",\"count\")\n",
" )\n",
" .reset_index()\n",
" )\n",
"\n",
" series[\"rupture_rate\"] = series[\"n_ruptures\"] / series[\"total_obs\"]\n",
"\n",
" bins = [0,0.01,0.10,0.30,1]\n",
" labels = [\"≤1%\", \"1–10%\", \"10–30%\", \">30%\"]\n",
"\n",
" series[\"class\"] = pd.cut(\n",
" series[\"rupture_rate\"],\n",
" bins=bins,\n",
" labels=labels,\n",
" include_lowest=True\n",
" )\n",
"\n",
" dist = (\n",
" series[\"class\"]\n",
" .value_counts(normalize=True)\n",
" .sort_index()\n",
" )\n",
"\n",
" return dist\n",
"\n",
"\n",
"dist_before = rupture_distribution(df_raw)\n",
"dist_after = rupture_distribution(df_clean)\n",
"\n",
"\n",
"# ============================================================\n",
"# PLOT\n",
"# ============================================================\n",
"\n",
"import matplotlib.pyplot as plt\n",
"import numpy as np\n",
"\n",
"x = np.arange(len(dist_before))\n",
"\n",
"plt.figure(figsize=(8,4))\n",
"\n",
"plt.bar(x-0.2, dist_before.values, width=0.4, label=\"Before cleaning\")\n",
"plt.bar(x+0.2, dist_after.values, width=0.4, label=\"After cleaning\")\n",
"\n",
"plt.xticks(x, dist_before.index)\n",
"\n",
"plt.ylabel(\"Share of series\")\n",
"\n",
"plt.title(\"Distribution of accounting rupture rates\")\n",
"\n",
"plt.legend()\n",
"\n",
"plt.show()"
]
},
{
"cell_type": "code",
"execution_count": 59,
"id": "8a52078b-5d56-447a-aabe-712e2c74cdae",
"metadata": {},
"outputs": [
{
"data": {
"image/png": "iVBORw0KGgoAAAANSUhEUgAAArMAAAHWCAYAAABkNgFvAAAAOnRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjEwLjgsIGh0dHBzOi8vbWF0cGxvdGxpYi5vcmcvwVt1zgAAAAlwSFlzAAAPYQAAD2EBqD+naQAAZPJJREFUeJzt3XdUFNf/PvBnQXbpIIogiIBij4JCQDSKBcUSu9EYo0jUJAZbiIkhRezEWIK9K0ZjJLHmq8aGYMUGYouiYsECKBYQVED2/v7wx3xcKQIiy+jzOmfPYe/eufPenRl5HO7MKoQQAkREREREMqSj7QKIiIiIiEqKYZaIiIiIZIthloiIiIhki2GWiIiIiGSLYZaIiIiIZIthloiIiIhki2GWiIiIiGSLYZaIiIiIZIthloiIiIhki2GWqJSMHz8eCoWiTNbVqlUrtGrVSnoeGRkJhUKB9evXl8n6Bw0aBAcHhzJZV0mlp6djyJAhsLa2hkKhwOjRo7VdUpGV5b5EZefatWtQKBQIDQ3VdilEbxWGWaJ8hIaGQqFQSA99fX3Y2NjAx8cHc+bMwaNHj0plPbdv38b48eMRGxtbKuOVpvJcW1FMnToVoaGhGDZsGFavXo0BAwZou6Q3qqy319SpU7F58+YyWZfcrF27FiEhIdoug+idoRBCCG0XQVTehIaGws/PDxMnToSjoyOys7ORlJSEyMhI7N69G9WrV8c///yDRo0aScs8e/YMz549g76+fpHXc+LECbz//vtYuXIlBg0aVOTlsrKyAABKpRLA8zOzrVu3xt9//43evXsXeZyS1padnQ21Wg2VSlUq63oTmjZtigoVKuDgwYPaLqXYynJfKiljY2P07t2bZxnz8eGHH+Ls2bO4du2aRrsQApmZmdDT04Ourq52iiN6C1XQdgFE5VnHjh3h5uYmPQ8MDMTevXvx4YcfomvXrjh//jwMDAwAABUqVECFCm/2kHr8+DEMDQ2lEKstenp6Wl1/Udy5cwf169fXdhmSp0+fQqlUQkfn1X8QK4t9icpe7l95iKh0cZoBUTG1adMGP//8M65fv441a9ZI7fnNc9y9ezc++OADmJubw9jYGHXq1MEPP/wA4PnZ1Pfffx8A4OfnJ01pyD3T1apVK7z33nuIjo5Gy5YtYWhoKC378pzZXDk5Ofjhhx9gbW0NIyMjdO3aFTdu3NDo4+DgkO+ZuxfHfFVt+c2ZzcjIwDfffAM7OzuoVCrUqVMHM2bMwMt//FEoFBg+fDg2b96M9957DyqVCg0aNMCOHTvy/8BfcufOHQwePBhWVlbQ19eHs7MzVq1aJb2eO3/46tWr2LZtm1T7y2fJ8qvpjz/+QJ06daCvrw9XV1fs378/T99bt27hs88+g5WVlVT7ihUrNPrk1rBu3Tr89NNPsLW1haGhIdLS0pCdnY0JEyagVq1a0NfXR6VKlfDBBx9g9+7d0vKlvS8BwNGjR9GhQweYmZnB0NAQXl5eOHTokMY6ctd7+fJlDBo0CObm5jAzM4Ofnx8eP36s8XllZGRg1apV0roKOxuclZWFcePGwdXVFWZmZjAyMkKLFi0QERGRp69arcbs2bPRsGFD6Ovrw9LSEh06dMCJEyc0+q1Zswbu7u4wNDRExYoV0bJlS+zatUujz4IFC9CgQQOoVCrY2NjA398fDx8+1OhTlOMh9zNWKBT466+/MGXKFFSrVg36+vpo27YtLl++rLHctm3bcP36demzyT1W8pszO2jQIBgbG+PWrVvo3r07jI2NYWlpiTFjxiAnJ0ejpnv37mHAgAEwNTWFubk5fH19cerUqSLPwz19+jS8vLxgYGCAatWqYfLkyVi5cmWe42PLli3o3LkzbGxsoFKpULNmTUyaNClPPS/+G9WsWTMYGBjA0dERixYtyrPuuXPnokGDBtL2cnNzw9q1a19ZM1FR8L/+RCUwYMAA/PDDD9i1axeGDh2ab59z587hww8/RKNGjTBx4kSoVCpcvnxZChD16tXDxIkTMW7cOHz++edo0aIFAKBZs2bSGPfu3UPHjh3x8ccf49NPP4WVlVWhdU2ZMgUKhQJjx47FnTt3EBISAm9vb8TGxkpnkIuiKLW9SAiBrl27IiIiAoMHD4aLiwt27tyJb7/9Frdu3cJvv/2m0f/gwYPYuHEjvvrqK5iYmGDOnDno1asXEhISUKlSpQLrevLkCVq1aoXLly9j+PDhcHR0xN9//41Bgwbh4cOHGDVqFOrVq4fVq1fj66+/RrVq1fDNN98AACwtLQt9z/v27UNYWBhGjhwJlUqFBQsWoEOHDjh27Bjee+89AEBycjKaNm0qhV9LS0v8+++/GDx4MNLS0vJcZDZp0iQolUqMGTMGmZmZUCqVGD9+PIKDgzFkyBC4u7sjLS0NJ06cQExMDNq1a5dvba+7L+3duxcdO3aEq6srgoKCoKOjg5UrV6JNmzY4cOAA3N3dNdbXp08fODo6Ijg4GDExMVi2bBmqVKmCadOmAQBWr14t1f/5558DAGrWrFngZ5uWloZly5ahX79+GDp0KB49eoTly5fDx8cHx44dg4uLi9R38ODBCA0NRceOHTFkyBA8e/YMBw4cwJEjR6S/kkyYMAHjx49Hs2bNMHHiRCiVShw9ehR79+5F+/btATwP5hMmTIC3tzeGDRuGuLg4LFy4EMePH8ehQ4dK/NeFX375BTo6OhgzZgxSU1Px66+/on///jh69CgA4Mcff0Rqaipu3rwp7ffGxsaFjpmTkwMfHx94eHhgxowZ2LNnD2bOnImaNWti2LBhAJ6H/C5duuDYsWMYNmwY6tatiy1btsDX17dIdd+6dQutW7eGQqFAYGAgjIyMsGzZsnynCoWGhsLY2BgBAQEwNjbG3r17MW7cOKSlpWH69OkafR88eIBOnTqhT58+6NevH/766y8MGzYMSqUSn332GQBg6dKlGDlyJHr37o1Ro0bh6dOnOH36NI4ePYpPPvmkSPUTFUoQUR4rV64UAMTx48cL7GNmZiYaN24sPQ8KChIvHlK//fabACDu3r1b4BjHjx8XAMTKlSvzvObl5SUAiEWLFuX7mpeXl/Q8IiJCABC2trYiLS1Nav/rr78EADF79mypzd7eXvj6+r5yzMJq8/X1Ffb29tLzzZs3CwBi8uTJGv169+4tFAqFuHz5stQGQCiVSo22U6dOCQBi7ty5edb1opCQEAFArFmzRmrLysoSnp6ewtjYWOO929vbi86dOxc63os1ARAnTpyQ2q5fvy709fVFjx49pLbBgweLqlWripSUFI3lP/74Y2FmZiYeP34shPjf9qhRo4bUlsvZ2fmVdZXmvqRWq0WtWrWEj4+PUKvVUvvjx4+Fo6OjaNeuXZ71fvbZZxpj9OjRQ1SqVEmjzcjIKN/9KD/Pnj0TmZmZGm0PHjwQVlZWGuvau3evACBGjhyZZ4zc2i9duiR0dHREjx49RE5OTr597ty5I5RKpWjfvr1Gn3nz5gkAYsWKFVJbUY+H3G1ar149jfcye/ZsAUCcOXNGauvcubPG8ZHr6tWrebaRr6+vACAmTpyo0bdx48bC1dVVer5hwwYBQISEhEhtOTk5ok2bNgUepy8aMWKEUCgU4uTJk1LbvXv3hIWFhQAgrl69KrW/vM8KIcQXX3whDA0NxdOnT6W23H+jZs6cKbVlZmYKFxcXUaVKFZGVlSWEEKJbt26iQYMGhdZH9Do4zYCohIyNjQu9q4G5uTmA53+yU6vVJVqHSqWCn59fkfsPHDgQJiYm0vPevXujatWq2L59e4nWX1Tbt2+Hrq4uRo4cqdH+zTffQAiBf//9V6Pd29tb40xeo0aNYGpqiitXrrxyPdbW1ujXr5/Upqenh5EjRyI9PR379u0r8Xvw9PSEq6ur9Lx69ero1q0bdu7ciZycHAghsGHDBnTp0gVCCKSkpEgPHx8fpKamIiYmRmNMX1/fPGfEzc3Nce7cOVy6dKnItb3OvhQbG4tLly7hk08+wb1796SaMzIy0LZtW+zfvz/PmF9++aXG8xYtWuDevXtIS0sr1rpz6erqSvO81Wo17t+/j2fPnsH
"text/plain": [
"<Figure size 800x500 with 1 Axes>"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"# ============================================================\n",
"# BUILD GAP DATASETS\n",
"# ============================================================\n",
"\n",
"def build_gap_df(stocks_df, flows_df):\n",
"\n",
" keys = [\n",
" \"Registrar Account - ID\",\n",
" \"Product - Isin\",\n",
" \"Centralisation Date\"\n",
" ]\n",
"\n",
" df = stocks_df.merge(flows_df, on=keys, how=\"left\")\n",
"\n",
" df[\"Quantity - NetFlows\"] = df[\"Quantity - NetFlows\"].fillna(0)\n",
"\n",
" df = df.sort_values(keys)\n",
"\n",
" df[\"prev_aum\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - AUM\"]\n",
" .shift(1)\n",
" )\n",
"\n",
" df[\"flow_lag\"] = (\n",
" df.groupby([\"Registrar Account - ID\",\"Product - Isin\"])\n",
" [\"Quantity - NetFlows\"]\n",
" .shift(1)\n",
" .fillna(0)\n",
" )\n",
"\n",
" df[\"expected_aum\"] = df[\"prev_aum\"] + df[\"flow_lag\"]\n",
"\n",
" df[\"gap\"] = df[\"Quantity - AUM\"] - df[\"expected_aum\"]\n",
"\n",
" return df\n",
"\n",
"\n",
"df_raw = build_gap_df(stocks, flows)\n",
"df_clean = build_gap_df(stocks_repaired, flows)\n",
"\n",
"\n",
"# ============================================================\n",
"# COMPUTE GAP PERSISTENCE LENGTH\n",
"# ============================================================\n",
"\n",
"import numpy as np\n",
"\n",
"def compute_gap_sequences(df, tol=1e-6):\n",
"\n",
" lengths = []\n",
"\n",
" for (_, _), g in df.groupby([\"Registrar Account - ID\",\"Product - Isin\"]):\n",
"\n",
" gaps = g[\"gap\"].values\n",
"\n",
" current_len = 1\n",
"\n",
" for i in range(1, len(gaps)):\n",
"\n",
" if np.isfinite(gaps[i]) and np.isfinite(gaps[i-1]) and abs(gaps[i] - gaps[i-1]) < tol:\n",
"\n",
" current_len += 1\n",
"\n",
" else:\n",
"\n",
" lengths.append(current_len)\n",
" current_len = 1\n",
"\n",
" lengths.append(current_len)\n",
"\n",
" return np.array(lengths)\n",
"\n",
"\n",
"raw_lengths = compute_gap_sequences(df_raw)\n",
"clean_lengths = compute_gap_sequences(df_clean)\n",
"\n",
"\n",
"# ============================================================\n",
"# PLOT DISTRIBUTION\n",
"# ============================================================\n",
"\n",
"import matplotlib.pyplot as plt\n",
"\n",
"plt.figure(figsize=(8,5))\n",
"\n",
"bins = np.arange(1,30)\n",
"\n",
"plt.hist(\n",
" raw_lengths,\n",
" bins=bins,\n",
" alpha=0.5,\n",
" label=\"Raw dataset\",\n",
" density=True\n",
")\n",
"\n",
"plt.hist(\n",
" clean_lengths,\n",
" bins=bins,\n",
" alpha=0.5,\n",
" label=\"Clean dataset\",\n",
" density=True\n",
")\n",
"\n",
"plt.xlabel(\"Gap persistence length (number of periods)\")\n",
"plt.ylabel(\"Density\")\n",
"\n",
"plt.title(\"Distribution of persistent accounting gaps\")\n",
"\n",
"plt.legend()\n",
"\n",
"plt.show()"
]
},
{
"cell_type": "code",
"execution_count": 63,
"id": "b7dfc5a3-e2b0-4fbf-894a-a66226f38165",
"metadata": {},
"outputs": [
{
"data": {
"image/png": "iVBORw0KGgoAAAANSUhEUgAAArMAAAGJCAYAAACZ7rtNAAAAOnRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjEwLjgsIGh0dHBzOi8vbWF0cGxvdGxpYi5vcmcvwVt1zgAAAAlwSFlzAAAPYQAAD2EBqD+naQAAjZ1JREFUeJzs3Xd8U9X7B/DPTdKke28olF32KLYURFAKRRRZX0VFGSIqgqh18lMZDsoQ5AsiKMpwfGUooAKyKkU2yBIQyixltaV7N21yfn+kiYSmI5A2Tft5v155QW7OvefJ7W375PTc80hCCAEiIiIiIhsks3YARERERER3i8ksEREREdksJrNEREREZLOYzBIRERGRzWIyS0REREQ2i8ksEREREdksJrNEREREZLOYzBIRERGRzWIyS0REREQ2i8ksEVULSZIwbdo0a4dhM7777juEhITAzs4O7u7u1g6n1ps2bRokSbJ2GERUCzCZJaojVqxYAUmSDA97e3u0bNkSEydORHJysrXDq5L//e9/mD9/vrXDqHFnz57F6NGj0axZMyxduhRfffWVtUOqFfLz8zFt2jTExcVZOxQiqsUkIYSwdhBEdO9WrFiBMWPG4MMPP0STJk1QWFiIPXv24LvvvkPjxo1x6tQpODo61lg8hYWFUCgUUCgUVd7n0UcfxalTp5CQkFB9gdVCS5Yswfjx43H+/Hk0b97c2uHUGqmpqfDx8cHUqVPLjPKXlJSgpKQE9vb21gmOiGqNqv+WISKb8PDDD6Nr164AgOeffx5eXl6YN28efvnlFzz11FP3dOz8/PwqJ8RMMqouJSUFADi9wAzmflAiorqL0wyI6riHHnoIAHD58mXDtu+//x6hoaFwcHCAp6cnnnzySVy9etVov969e6Ndu3Y4cuQIHnjgATg6OuL//u//AAB//fUXoqKi4O3tDQcHBzRp0gTPPfec0f53zpnNycnBa6+9huDgYKhUKvj6+qJv3744evSoob9NmzbhypUrhqkSwcHBhv2LioowdepUNG/eHCqVCkFBQXj77bdRVFRUpt+JEydiw4YNaNeuHVQqFdq2bYstW7aUOTfXr1/H2LFjERgYCJVKhSZNmmD8+PFQq9WGNpmZmXjttdcQFBQElUqF5s2bY9asWdBqtVU6/1988QXatm0LlUqFwMBATJgwAZmZmYbXg4ODMXXqVACAj49PpXON//77b4wePRpNmzaFvb09/P398dxzzyEtLe2u39/rr79u+Lo0bNgQI0eORGpqqqFNSkoKxo4dCz8/P9jb26Njx45YuXKlUV9xcXGQJKnMlICEhARIkoQVK1YYto0ePRrOzs64fv06Bg8eDGdnZ/j4+ODNN9+ERqMx7Ofj4wMAmD59uuGa0J8bU3Nmzfnax8XFoWvXrrC3t0ezZs3w5ZdfmjUPd9GiRWjatCkcHBwQFhaG3bt3o3fv3ujdu7ehjVqtxpQpUxAaGgo3Nzc4OTmhZ8+e2Llzp8lz9Omnn+Kzzz5D48aN4eDggF69euHUqVNGbZOSkjBmzBg0bNgQKpUKAQEBGDRoUL37awbR7fixlqiOu3jxIgDAy8sLAPDJJ5/ggw8+wBNPPIHnn38et27dwsKFC/HAAw/g2LFjRqODaWlpePjhh/Hkk0/imWeegZ+fH1JSUtCvXz/4+Pjg3Xffhbu7OxISErBu3boK43jppZfw008/YeLEiWjTpg3S0tKwZ88enDlzBl26dMF7772HrKwsXLt2DZ999hkAwNnZGQCg1Wrx2GOPYc+ePXjhhRfQunVrnDx5Ep999hnOnTuHDRs2GPW1Z88erFu3Di+//DJcXFywYMECDBs2DImJiYbzcOPGDYSFhSEzMxMvvPACQkJCcP36dfz000/Iz8+HUqlEfn4+evXqhevXr+PFF19Eo0aNsG/fPkyePBk3b96sdH7vtGnTMH36dERGRmL8+PGIj4/H4sWLcfjwYezduxd2dnaYP38+vv32W6xfvx6LFy+Gs7MzOnToUO4xt2/fjkuXLmHMmDHw9/fH6dOn8dVXX+H06dM4cOCAIRmryvvLzc1Fz549cebMGTz33HPo0qULUlNT8euvv+LatWvw9vZGQUEBevfujQsXLmDixIlo0qQJ1q5di9GjRyMzMxOvvvpqheegPBqNBlFRUQgPD8enn36KHTt2YO7cuWjWrBnGjx8PHx8fLF68GOPHj8eQIUMwdOhQAKjw3ABV+9ofO3YM/fv3R0BAAKZPnw6NRoMPP/zQkDxXZvHixZg4cSJ69uyJ119/HQkJCRg8eDA8PDzQsGFDQ7vs7Gx8/fXXeOqppzBu3Djk5OTgm2++QVRUFA4dOoROnToZHffbb79FTk4OJkyYgMLCQvz3v//FQw89hJMnT8LPzw8AMGzYMJw+fRqvvPIKgoODkZKSgu3btyMxMdHowx9RvSKIqE5Yvny5ACB27Nghbt26Ja5evSpWrVolvLy8hIODg7h27ZpISEgQcrlcfPLJJ0b7njx5UigUCqPtvXr1EgDEkiVLjNquX79eABCHDx+uMB4AYurUqYbnbm5uYsKECRXu88gjj4jGjRuX2f7dd98JmUwmdu/ebbR9yZIlAoDYu3evUb9KpVJcuHDBsO3EiRMCgFi4cKFh28iRI4VMJjP5PrRarRBCiI8++kg4OTmJc+fOGb3+7rvvCrlcLhITE8t9LykpKUKpVIp+/foJjUZj2P75558LAGLZsmWGbVOnThUAxK1bt8o9nl5+fn6ZbT/++KMAIP7880+z3t+UKVMEALFu3bpy28yfP18AEN9//73hNbVaLSIiIoSzs7PIzs4WQgixc+dOAUDs3LnT6DiXL18WAMTy5csN20aNGiUAiA8//NCobefOnUVoaKjh+a1bt8pcR3r6c3a7qn7tBw4cKBwdHcX169cN286fPy8UCkWZY96pqKhIeHl5ifvuu08UFxcbtq9YsUIAEL169TJsKykpEUVFRUb7Z2RkCD8/P/Hcc88ZtunPkf77VO/gwYMCgHj99dcN+wIQc+bMqTBGovqG0wyI6pjIyEj4+PggKCgITz75JJydnbF+/Xo0aNAA69atg1arxRNPPIHU1FTDw9/fHy1atCjz50+VSoUxY8YYbdOP3G7cuBHFxcVVjsvd3R0HDx7EjRs3zH5Pa9euRevWrRESEmIUt34KxZ1xR0ZGolmzZobnHTp0gKurKy5dugRAN9K7YcMGDBw40DC/+Hb60c21a9eiZ8+e8PDwMOo3MjISGo0Gf/75Z7kx79ixA2q1Gq+99hpksn9/1I4bNw6urq7YtGmT2ecBABwcHAz/LywsRGpqKrp16wYAhikbVX1/P//8Mzp27IghQ4aU22bz5s3w9/c3mm9tZ2eHSZMmITc3F7t27bqr9wHoRutv17NnT8PX6G5V9rXXaDTYsWMHBg8ejMDAQEO75s2b4+GHH670+H/99RfS0tIwbtw4ozm7I0aMgIeHh1FbuVwOpVIJQPc1SU9PR0lJCbp27Wr4Wt1u8ODBaNCggeF5WFgYwsPDsXnzZgC6r71SqURcXBwyMjKqcjqI6gVOMyCqYxYtWoSWLVtCoVDAz88PrVq1MiRT58+fhxACLVq0MLmvnZ2d0fMGDRoYfhnr9erVC8OGDcP06dPx2WefoXfv3hg8eDCefvppqFSqcuOaPXs2Ro0ahaCgIISGhmLAgAEYOXIkmjZtWul7On/+PM6cOVPun4H1N1DpNWrUqEwbDw8PQwJw69YtZGdno127dpX2+/fff1e539tduXIFANCqVSuj7UqlEk2bNjW8bq709HRMnz4dq1atKtN/VlYWgKq/v4sXL2LYsGEVtrly5QpatGhhlJADQOvWrQ2v3w17e/sy5/X2r9Hdquxrn5KSgoKCApOrRlRlJQn9+72zrUKhMPln/pUrV2Lu3Lk4e/as0Ye/Jk2alGlr6vuyZcuWWLNmDQDdh8tZs2bhjTfegJ+fH7p164ZHH30UI0eOhL+/f6WxE9VVTGaJ6piwsDCTo3GAbnRIkiT8/vvvkMvlZV7Xz1HVu30UUE+SJPz00084cOAAfvvtN2zduhXPPfc
"text/plain": [
"<Figure size 800x400 with 1 Axes>"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"import numpy as np\n",
"import matplotlib.pyplot as plt\n",
"\n",
"max_len = 20\n",
"\n",
"# clip lengths\n",
"raw_clipped = np.minimum(raw_lengths, max_len)\n",
"clean_clipped = np.minimum(clean_lengths, max_len)\n",
"\n",
"# compute histogram\n",
"raw_hist = np.bincount(raw_clipped, minlength=max_len+1)\n",
"clean_hist = np.bincount(clean_clipped, minlength=max_len+1)\n",
"\n",
"# normalize\n",
"raw_hist = raw_hist / raw_hist.sum()\n",
"clean_hist = clean_hist / clean_hist.sum()\n",
"\n",
"x = np.arange(max_len+1)\n",
"\n",
"plt.figure(figsize=(8,4))\n",
"\n",
"plt.plot(x, raw_hist, marker=\"o\", label=\"Raw dataset\")\n",
"plt.plot(x, clean_hist, marker=\"o\", label=\"Clean dataset\")\n",
"\n",
"plt.xlabel(\"Gap persistence length (20 = 20+)\")\n",
"plt.ylabel(\"Share of sequences\")\n",
"\n",
"plt.title(\"Persistence of accounting gaps\")\n",
"\n",
"plt.legend()\n",
"\n",
"plt.show()"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "5a9d6cd8-e92b-4975-969d-cb3d6d9e4bce",
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3 (ipykernel)",
"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.13.11"
}
},
"nbformat": 4,
"nbformat_minor": 5
}
Reference in New Issue Copy Permalink