{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "cc5226a5",
   "metadata": {},
   "source": [
    "# TP — Crimes à Chicago : pandas vs Spark\n",
    "\n",
    "Données : *Crimes - 2001 to Present* (Chicago Police Department), 22 colonnes.\n",
    "Export officiel : `https://data.cityofchicago.org/api/views/ijzp-q8t2/rows.csv?accessType=DOWNLOAD`\n",
    "(~8,07 M de lignes, ~1,9 Go).\n",
    "\n",
    "Fichier utilisé pour ces mesures : `crimes_clean_4m.csv`, 1,13 Go et 4 778 681 lignes, extrait du\n",
    "même export avec les mêmes colonnes. Le code ne change pas pour l'export complet, il suffit de\n",
    "pointer `PATH` dessus.\n",
    "\n",
    "Machine : Windows 11, 14 cœurs, 31,5 Go de RAM, Java 17, PySpark 4.3.0, pandas 3.x, Python 3.12.\n",
    "\n",
    "Un point de méthode avant de commencer. Spark est paresseux : `spark.read.csv()` seul ne prend que\n",
    "quelques millisecondes, parce qu'il ne lit rien. Chaque « temps de lecture » ci-dessous est donc\n",
    "lecture + première action (`count()`)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c7c1b82b",
   "metadata": {},
   "source": [
    "## 0. Session Spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "e7ce3def",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-11-09T12:46:47.429782Z",
     "iopub.status.busy": "2026-11-09T12:46:47.428783Z",
     "iopub.status.idle": "2026-11-09T12:47:12.912992Z",
     "shell.execute_reply": "2026-11-09T12:47:12.911982Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Y.CHEHBOUB\\PERSONAL__DO_NOT_TOUCH\\OpenDataModule\\crimes_clean_4m.csv : 1.13 Go\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Y.CHEHBOUB\\PERSONAL__DO_NOT_TOUCH\\OpenDataModule\\.venv\\Lib\\site-packages\\pyspark\\testing\\utils.py:127: FutureWarning: PySpark does not yet fully support pandas >= 3.0.0. Some features may not work correctly. It is recommended to use pandas < 3.0.0 for now.\n",
      "  require_minimum_pandas_version()\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Spark 4.3.0 | UI: http://FBL-LT-0607.insead.org:4040\n"
     ]
    }
   ],
   "source": [
    "import os, time, json, urllib.request\n",
    "import pyspark.sql.functions as F\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.types import (StructType, StructField, StringType, IntegerType,\n",
    "                               LongType, DoubleType, BooleanType, TimestampType)\n",
    "\n",
    "PATH = os.environ.get(\"CRIMES_CSV\", \"crimes.csv\")\n",
    "assert os.path.exists(PATH), f\"fichier introuvable : {PATH}\"\n",
    "print(f\"{PATH} : {os.path.getsize(PATH)/1e9:.2f} Go\")\n",
    "\n",
    "spark = (SparkSession.builder\n",
    "         .appName(\"TP Crimes Chicago\")\n",
    "         .master(\"local[*]\")\n",
    "         .config(\"spark.driver.memory\", \"4g\")\n",
    "         .config(\"spark.sql.shuffle.partitions\", \"16\")\n",
    "         .config(\"spark.driver.maxResultSize\", \"512m\")\n",
    "         .config(\"spark.ui.enabled\", \"true\")\n",
    "         .getOrCreate())\n",
    "spark.sparkContext.setLogLevel(\"ERROR\")\n",
    "print(\"Spark\", spark.version, \"| UI:\", spark.sparkContext.uiWebUrl)\n",
    "\n",
    "RESULTS = {}\n",
    "\n",
    "def taches_consommees():\n",
    "    # tasks cumulees de l'application, lues sur l'API REST du UI Spark\n",
    "    base = spark.sparkContext.uiWebUrl\n",
    "    app = spark.sparkContext.applicationId\n",
    "    with urllib.request.urlopen(f\"{base}/api/v1/applications/{app}/stages\", timeout=10) as r:\n",
    "        return sum(s[\"numTasks\"] for s in json.load(r))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee4efa97",
   "metadata": {},
   "source": [
    "## 1. Lecture avec pandas\n",
    "\n",
    "`pd.read_csv()` charge **tout** le fichier dans la RAM du driver : aucun parallélisme, aucune\n",
    "distribution, un `DataFrame` de plusieurs millions de lignes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "fcd8adfa",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-11-09T12:47:12.915499Z",
     "iopub.status.busy": "2026-11-09T12:47:12.915499Z",
     "iopub.status.idle": "2026-11-09T12:47:21.380237Z",
     "shell.execute_reply": "2026-11-09T12:47:21.380237Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "pandas : 1.66 s\n",
      "shape  : 4,778,681 lignes x 22 colonnes\n",
      "RAM pic: 2.78 Go\n",
      "memoire du DataFrame (deep) : 1.36 Go\n",
      "str        10\n",
      "float64     7\n",
      "int64       3\n",
      "bool        2\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "pandas moteur C (engine='c') : 6.38 s  |  RAM pic process : 2.78 Go\n",
      "RAM du process apres liberation : 0.13 Go\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd, psutil, threading, gc\n",
    "\n",
    "proc = psutil.Process()\n",
    "pic = [proc.memory_info().rss]; stop = [False]\n",
    "def _echantillonne():\n",
    "    while not stop[0]:\n",
    "        pic[0] = max(pic[0], proc.memory_info().rss); time.sleep(0.2)\n",
    "threading.Thread(target=_echantillonne, daemon=True).start()\n",
    "\n",
    "t0 = time.perf_counter()\n",
    "pdf = pd.read_csv(PATH)\n",
    "t_pandas = time.perf_counter() - t0\n",
    "stop[0] = True\n",
    "RESULTS[\"1_pandas\"] = round(t_pandas, 2)\n",
    "\n",
    "print(f\"pandas : {t_pandas:.2f} s\")\n",
    "print(f\"shape  : {pdf.shape[0]:,} lignes x {pdf.shape[1]} colonnes\")\n",
    "print(f\"RAM pic: {pic[0]/2**30:.2f} Go\")\n",
    "print(f\"memoire du DataFrame (deep) : {pdf.memory_usage(deep=True).sum()/2**30:.2f} Go\")\n",
    "print(pdf.dtypes.value_counts().to_string())\n",
    "\n",
    "del pdf; gc.collect()\n",
    "\n",
    "# meme lecture avec le moteur C historique (pandas 2.x) pour comparaison\n",
    "t0 = time.perf_counter()\n",
    "pdf_c = pd.read_csv(PATH, engine=\"c\", low_memory=False)\n",
    "t_c = time.perf_counter() - t0\n",
    "RESULTS[\"1_pandas_engine_c\"] = round(t_c, 2)\n",
    "print(f\"\\npandas moteur C (engine='c') : {t_c:.2f} s  |  RAM pic process : {pic[0]/2**30:.2f} Go\")\n",
    "del pdf_c; gc.collect()\n",
    "print(f\"RAM du process apres liberation : {proc.memory_info().rss/2**30:.2f} Go\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2dfff7ba",
   "metadata": {},
   "source": [
    "### Conclusion Q1\n",
    "\n",
    "Le fichier entier est matérialisé dans un seul processus : 2,78 Go de RAM pour un DataFrame de\n",
    "1,36 Go, soit 2,5 fois la taille du fichier. La lecture elle-même est rapide (1,66 s avec le moteur\n",
    "par défaut de pandas 3, 6,38 s avec le moteur C).\n",
    "\n",
    "Ce qui coince n'est pas le `read_csv`, c'est la suite : chaque filtre, `groupby` ou jointure tourne\n",
    "sur un seul thread, et il faut recharger le fichier à chaque exécution. Sur 8 M de lignes et 1,9 Go,\n",
    "ça devient un choix de moteur plutôt qu'une préférence."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "249bae4c",
   "metadata": {},
   "source": [
    "## 2. Lecture avec Spark — options par défaut\n",
    "\n",
    "Sans aucune option : la première ligne du fichier est traitée comme une donnée, les colonnes\n",
    "s'appellent `_c0` … `_c21`, tout est en `string`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "150cf840",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-11-09T12:47:21.383778Z",
     "iopub.status.busy": "2026-11-09T12:47:21.383778Z",
     "iopub.status.idle": "2026-11-09T12:47:28.101959Z",
     "shell.execute_reply": "2026-11-09T12:47:28.100435Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "spark.read.csv(PATH).count() : 1.93 s -> 4,778,682 lignes\n",
      "colonnes : ['_c0', '_c1', '_c2', '_c3', '_c4', '_c5', '_c6', '_c7', '_c8', '_c9', '_c10', '_c11', '_c12', '_c13', '_c14', '_c15', '_c16', '_c17', '_c18', '_c19', '_c20', '_c21']\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+-----------+--------------------+--------------------+----+--------------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+--------+---------+--------+\n",
      "|     _c0|        _c1|                 _c2|                 _c3| _c4|                 _c5|                 _c6|                 _c7|   _c8|     _c9|_c10|    _c11|_c12|          _c13|    _c14|        _c15|        _c16|_c17|                _c18|    _c19|     _c20|    _c21|\n",
      "+--------+-----------+--------------------+--------------------+----+--------------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+--------+---------+--------+\n",
      "|      ID|Case Number|                Date|               Block|IUCR|        Primary Type|         Description|Location Description|Arrest|Domestic|Beat|District|Ward|Community Area|FBI Code|X Coordinate|Y Coordinate|Year|          Updated On|Latitude|Longitude|Location|\n",
      "|13311263|   JG503434|07/29/2022 03:39:...|     023XX S TROY ST|1582|OFFENSE INVOLVING...|   CHILD PORNOGRAPHY|           RESIDENCE|  true|   false|1033|     010|  25|            30|      17|        NULL|        NULL|2022|04/18/2024 03:40:...|    NULL|     NULL|    NULL|\n",
      "|13053066|   JG103252|01/03/2023 04:44:...|039XX W WASHINGTO...|2017|           NARCOTICS|MANUFACTURE / DEL...|            SIDEWALK|  true|   false|1122|     011|  28|            26|      18|        NULL|        NULL|2023|01/20/2024 03:41:...|    NULL|     NULL|    NULL|\n",
      "+--------+-----------+--------------------+--------------------+----+--------------------+--------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+--------------------+--------+---------+--------+\n",
      "only showing top 3 rows\n"
     ]
    }
   ],
   "source": [
    "df_brut = spark.read.csv(PATH)\n",
    "t0 = time.perf_counter(); n_brut = df_brut.count(); t_brut = time.perf_counter() - t0\n",
    "RESULTS[\"2_spark_defaut_count\"] = round(t_brut, 2)\n",
    "print(f\"spark.read.csv(PATH).count() : {t_brut:.2f} s -> {n_brut:,} lignes\")\n",
    "print(\"colonnes :\", df_brut.columns)\n",
    "df_brut.show(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8de3b89a",
   "metadata": {},
   "source": [
    "### 2 bis. Avec `header=True`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "70b927a7",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-11-09T12:47:28.106183Z",
     "iopub.status.busy": "2026-11-09T12:47:28.105184Z",
     "iopub.status.idle": "2026-11-09T12:47:29.401021Z",
     "shell.execute_reply": "2026-11-09T12:47:29.399007Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "spark.read.csv(PATH, header=True).count() : 0.99 s -> 4,778,681 lignes\n",
      "colonnes : ['ID', 'Case Number', 'Date', 'Block', 'IUCR', 'Primary Type', 'Description', 'Location Description', 'Arrest', 'Domestic', 'Beat', 'District', 'Ward', 'Community Area', 'FBI Code', 'X Coordinate', 'Y Coordinate', 'Year', 'Updated On', 'Latitude', 'Longitude', 'Location']\n",
      "root\n",
      " |-- ID: string (nullable = true)\n",
      " |-- Case Number: string (nullable = true)\n",
      " |-- Date: string (nullable = true)\n",
      " |-- Block: string (nullable = true)\n",
      " |-- IUCR: string (nullable = true)\n",
      " |-- Primary Type: string (nullable = true)\n",
      " |-- Description: string (nullable = true)\n",
      " |-- Location Description: string (nullable = true)\n",
      " |-- Arrest: string (nullable = true)\n",
      " |-- Domestic: string (nullable = true)\n",
      " |-- Beat: string (nullable = true)\n",
      " |-- District: string (nullable = true)\n",
      " |-- Ward: string (nullable = true)\n",
      " |-- Community Area: string (nullable = true)\n",
      " |-- FBI Code: string (nullable = true)\n",
      " |-- X Coordinate: string (nullable = true)\n",
      " |-- Y Coordinate: string (nullable = true)\n",
      " |-- Year: string (nullable = true)\n",
      " |-- Updated On: string (nullable = true)\n",
      " |-- Latitude: string (nullable = true)\n",
      " |-- Longitude: string (nullable = true)\n",
      " |-- Location: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_str = spark.read.csv(PATH, header=True)\n",
    "t0 = time.perf_counter(); n = df_str.count(); t_header = time.perf_counter() - t0\n",
    "RESULTS[\"2_spark_header_count\"] = round(t_header, 2)\n",
    "print(f\"spark.read.csv(PATH, header=True).count() : {t_header:.2f} s -> {n:,} lignes\")\n",
    "print(\"colonnes :\", df_str.columns)\n",
    "df_str.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d43b18f",
   "metadata": {},
   "source": [
    "### Conclusion Q2\n",
    "\n",
    "Sans option, Spark prend la première ligne pour une donnée : 4 778 682 lignes au lieu de 4 778 681,\n",
    "colonnes `_c0 … _c21`, tout en `string`. `header=True` règle les deux, sans coût supplémentaire.\n",
    "\n",
    "Face à pandas, le temps est du même ordre sur ce volume. La différence est ailleurs : Spark lit par\n",
    "partitions de 128 Mo, en parallèle, et ne fait jamais transiter le fichier par le driver."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c3730cde",
   "metadata": {},
   "source": [
    "## 3. Partitions, colonnes, lignes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "716d3a09",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-11-09T12:47:29.404358Z",
     "iopub.status.busy": "2026-11-09T12:47:29.404358Z",
     "iopub.status.idle": "2026-11-09T12:47:29.487985Z",
     "shell.execute_reply": "2026-11-09T12:47:29.485964Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "partitions : 14  (= découpage du fichier en blocs de 128 Mo, 1 partition = 1 task)\n",
      "nombre de colonnes : 22\n",
      "colonnes : ['ID', 'Case Number', 'Date', 'Block', 'IUCR', 'Primary Type', 'Description', 'Location Description', 'Arrest', 'Domestic', 'Beat', 'District', 'Ward', 'Community Area', 'FBI Code', 'X Coordinate', 'Y Coordinate', 'Year', 'Updated On', 'Latitude', 'Longitude', 'Location']\n",
      "lignes : 4,778,681\n"
     ]
    }
   ],
   "source": [
    "n_part = df_str.rdd.getNumPartitions()\n",
    "print(\"partitions :\", n_part, \" (= découpage du fichier en blocs de 128 Mo, 1 partition = 1 task)\")\n",
    "print(\"nombre de colonnes :\", len(df_str.columns))\n",
    "print(\"colonnes :\", df_str.columns)\n",
    "RESULTS[\"3_partitions\"] = n_part\n",
    "RESULTS[\"3_colonnes\"] = len(df_str.columns)\n",
    "print(f\"lignes : {n:,}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0b95fe0",
   "metadata": {},
   "source": [
    "## 4. Affichage « comme dans un notebook pandas »\n",
    "\n",
    "Évaluer `df` dans une cellule n'affiche qu'un `DataFrame[...]`. Le réglage est\n",
    "**`spark.sql.repl.eagerEval.enabled = true`** : Spark rend alors un tableau HTML, comme pandas,\n",
    "borné par `eagerEval.maxNumRows`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "41a9b504",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-11-09T12:47:29.491987Z",
     "iopub.status.busy": "2026-11-09T12:47:29.491987Z",
     "iopub.status.idle": "2026-11-09T12:47:29.902480Z",
     "shell.execute_reply": "2026-11-09T12:47:29.900964Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border='1'>\n",
       "<tr><th>ID</th><th>Case Number</th><th>Date</th><th>Block</th><th>IUCR</th><th>Primary Type</th><th>Description</th><th>Location Description</th><th>Arrest</th><th>Domestic</th><th>Beat</th><th>District</th><th>Ward</th><th>Community Area</th><th>FBI Code</th><th>X Coordinate</th><th>Y Coordinate</th><th>Year</th><th>Updated On</th><th>Latitude</th><th>Longitude</th><th>Location</th></tr>\n",
       "<tr><td>13311263</td><td>JG503434</td><td>07/29/2022 03:39:00 AM</td><td>023XX S TROY ST</td><td>1582</td><td>OFFENSE INVOLVING CHIL...</td><td>CHILD PORNOGRAPHY</td><td>RESIDENCE</td><td>true</td><td>false</td><td>1033</td><td>010</td><td>25</td><td>30</td><td>17</td><td>NULL</td><td>NULL</td><td>2022</td><td>04/18/2024 03:40:59 PM</td><td>NULL</td><td>NULL</td><td>NULL</td></tr>\n",
       "<tr><td>13053066</td><td>JG103252</td><td>01/03/2023 04:44:00 PM</td><td>039XX W WASHINGTON BLVD</td><td>2017</td><td>NARCOTICS</td><td>MANUFACTURE / DELIVER ...</td><td>SIDEWALK</td><td>true</td><td>false</td><td>1122</td><td>011</td><td>28</td><td>26</td><td>18</td><td>NULL</td><td>NULL</td><td>2023</td><td>01/20/2024 03:41:12 PM</td><td>NULL</td><td>NULL</td><td>NULL</td></tr>\n",
       "<tr><td>11227634</td><td>JB147599</td><td>08/26/2017 10:00:00 AM</td><td>001XX W RANDOLPH ST</td><td>0281</td><td>CRIM SEXUAL ASSAULT</td><td>NON-AGGRAVATED</td><td>HOTEL/MOTEL</td><td>false</td><td>false</td><td>0122</td><td>001</td><td>42</td><td>32</td><td>02</td><td>NULL</td><td>NULL</td><td>2017</td><td>02/11/2018 03:57:41 PM</td><td>NULL</td><td>NULL</td><td>NULL</td></tr>\n",
       "<tr><td>12416972</td><td>JE293535</td><td>10/01/2020 12:01:00 AM</td><td>031XX W 53RD PL</td><td>1753</td><td>OFFENSE INVOLVING CHIL...</td><td>SEXUAL ASSAULT OF CHIL...</td><td>RESIDENCE</td><td>false</td><td>true</td><td>0923</td><td>009</td><td>14</td><td>63</td><td>02</td><td>NULL</td><td>NULL</td><td>2020</td><td>09/14/2023 03:41:59 PM</td><td>NULL</td><td>NULL</td><td>NULL</td></tr>\n",
       "<tr><td>12536164</td><td>JE439378</td><td>09/24/2015 12:00:00 AM</td><td>031XX W 53RD PL</td><td>1753</td><td>OFFENSE INVOLVING CHIL...</td><td>SEXUAL ASSAULT OF CHIL...</td><td>APARTMENT</td><td>false</td><td>true</td><td>0923</td><td>009</td><td>14</td><td>63</td><td>02</td><td>NULL</td><td>NULL</td><td>2015</td><td>09/14/2023 03:41:59 PM</td><td>NULL</td><td>NULL</td><td>NULL</td></tr>\n",
       "</table>\n",
       "only showing top 5 rows\n"
      ],
      "text/plain": [
       "+--------+-----------+----------------------+-----------------------+----+-------------------------+-------------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+----------------------+--------+---------+--------+\n",
       "|      ID|Case Number|                  Date|                  Block|IUCR|             Primary Type|              Description|Location Description|Arrest|Domestic|Beat|District|Ward|Community Area|FBI Code|X Coordinate|Y Coordinate|Year|            Updated On|Latitude|Longitude|Location|\n",
       "+--------+-----------+----------------------+-----------------------+----+-------------------------+-------------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+----------------------+--------+---------+--------+\n",
       "|13311263|   JG503434|07/29/2022 03:39:00 AM|        023XX S TROY ST|1582|OFFENSE INVOLVING CHIL...|        CHILD PORNOGRAPHY|           RESIDENCE|  true|   false|1033|     010|  25|            30|      17|        NULL|        NULL|2022|04/18/2024 03:40:59 PM|    NULL|     NULL|    NULL|\n",
       "|13053066|   JG103252|01/03/2023 04:44:00 PM|039XX W WASHINGTON BLVD|2017|                NARCOTICS|MANUFACTURE / DELIVER ...|            SIDEWALK|  true|   false|1122|     011|  28|            26|      18|        NULL|        NULL|2023|01/20/2024 03:41:12 PM|    NULL|     NULL|    NULL|\n",
       "|11227634|   JB147599|08/26/2017 10:00:00 AM|    001XX W RANDOLPH ST|0281|      CRIM SEXUAL ASSAULT|           NON-AGGRAVATED|         HOTEL/MOTEL| false|   false|0122|     001|  42|            32|      02|        NULL|        NULL|2017|02/11/2018 03:57:41 PM|    NULL|     NULL|    NULL|\n",
       "|12416972|   JE293535|10/01/2020 12:01:00 AM|        031XX W 53RD PL|1753|OFFENSE INVOLVING CHIL...|SEXUAL ASSAULT OF CHIL...|           RESIDENCE| false|    true|0923|     009|  14|            63|      02|        NULL|        NULL|2020|09/14/2023 03:41:59 PM|    NULL|     NULL|    NULL|\n",
       "|12536164|   JE439378|09/24/2015 12:00:00 AM|        031XX W 53RD PL|1753|OFFENSE INVOLVING CHIL...|SEXUAL ASSAULT OF CHIL...|           APARTMENT| false|    true|0923|     009|  14|            63|      02|        NULL|        NULL|2015|09/14/2023 03:41:59 PM|    NULL|     NULL|    NULL|\n",
       "+--------+-----------+----------------------+-----------------------+----+-------------------------+-------------------------+--------------------+------+--------+----+--------+----+--------------+--------+------------+------------+----+----------------------+--------+---------+--------+\n",
       "only showing top 5 rows"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark.conf.set(\"spark.sql.repl.eagerEval.enabled\", True)\n",
    "spark.conf.set(\"spark.sql.repl.eagerEval.maxNumRows\", 5)\n",
    "spark.conf.set(\"spark.sql.repl.eagerEval.truncate\", 25)\n",
    "df_str     # rendu type pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2429a0bf",
   "metadata": {},
   "source": [
    "## 5. `inferSchema=True` : pourquoi ≥ 30 s ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "b66cdac7",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-11-09T12:47:29.906992Z",
     "iopub.status.busy": "2026-11-09T12:47:29.905478Z",
     "iopub.status.idle": "2026-11-09T12:47:36.828016Z",
     "shell.execute_reply": "2026-11-09T12:47:36.826999Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "inferSchema=True -> count() : 6.91 s (4,778,681 lignes)\n",
      "root\n",
      " |-- ID: integer (nullable = true)\n",
      " |-- Case Number: string (nullable = true)\n",
      " |-- Date: string (nullable = true)\n",
      " |-- Block: string (nullable = true)\n",
      " |-- IUCR: string (nullable = true)\n",
      " |-- Primary Type: string (nullable = true)\n",
      " |-- Description: string (nullable = true)\n",
      " |-- Location Description: string (nullable = true)\n",
      " |-- Arrest: boolean (nullable = true)\n",
      " |-- Domestic: boolean (nullable = true)\n",
      " |-- Beat: integer (nullable = true)\n",
      " |-- District: integer (nullable = true)\n",
      " |-- Ward: integer (nullable = true)\n",
      " |-- Community Area: integer (nullable = true)\n",
      " |-- FBI Code: string (nullable = true)\n",
      " |-- X Coordinate: integer (nullable = true)\n",
      " |-- Y Coordinate: integer (nullable = true)\n",
      " |-- Year: integer (nullable = true)\n",
      " |-- Updated On: string (nullable = true)\n",
      " |-- Latitude: double (nullable = true)\n",
      " |-- Longitude: double (nullable = true)\n",
      " |-- Location: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "t0 = time.perf_counter()\n",
    "df_infer = spark.read.csv(PATH, header=True, inferSchema=True)\n",
    "n_infer = df_infer.count()      # c'est ici que l'inference est reellement executee\n",
    "t_infer = time.perf_counter() - t0\n",
    "RESULTS[\"5_inferSchema_count\"] = round(t_infer, 2)\n",
    "print(f\"inferSchema=True -> count() : {t_infer:.2f} s ({n_infer:,} lignes)\")\n",
    "df_infer.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34ca5719",
   "metadata": {},
   "source": [
    "### Conclusion Q5\n",
    "\n",
    "6,91 s contre 0,99 s sans inférence, environ 7 fois plus. La cause est simple : Spark doit parcourir\n",
    "le fichier une seconde fois, uniquement pour deviner le type de chaque colonne, avant la vraie\n",
    "lecture. Deux passes complètes.\n",
    "\n",
    "Sur l'export de 1,9 Go, c'est le surcoût de plusieurs dizaines de secondes dont parle l'énoncé.\n",
    "Quand on connaît les colonnes, c'est du temps perdu, d'où le schéma explicite en Q8."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4edff1d",
   "metadata": {},
   "source": [
    "## 6. Types, sélection, ajout / suppression de colonnes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "ff58584d",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-11-09T12:47:36.833012Z",
     "iopub.status.busy": "2026-11-09T12:47:36.833012Z",
     "iopub.status.idle": "2026-11-09T12:47:36.843673Z",
     "shell.execute_reply": "2026-11-09T12:47:36.842654Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Types inférés :\n",
      "  ID                       int\n",
      "  Case Number              string\n",
      "  Date                     string\n",
      "  Block                    string\n",
      "  IUCR                     string\n",
      "  Primary Type             string\n",
      "  Description              string\n",
      "  Location Description     string\n",
      "  Arrest                   boolean\n",
      "  Domestic                 boolean\n",
      "  Beat                     int\n",
      "  District                 int\n",
      "  Ward                     int\n",
      "  Community Area           int\n",
      "  FBI Code                 string\n",
      "  X Coordinate             int\n",
      "  Y Coordinate             int\n",
      "  Year                     int\n",
      "  Updated On               string\n",
      "  Latitude                 double\n",
      "  Longitude                double\n",
      "  Location                 string\n"
     ]
    }
   ],
   "source": [
    "print(\"Types inférés :\")\n",
    "for c, t in df_infer.dtypes:\n",
    "    print(f\"  {c:<24} {t}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "18a28008",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-11-09T12:47:36.850214Z",
     "iopub.status.busy": "2026-11-09T12:47:36.850214Z",
     "iopub.status.idle": "2026-11-09T12:47:37.104865Z",
     "shell.execute_reply": "2026-11-09T12:47:37.103346Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+-----------+\n",
      "|District|Case Number|\n",
      "+--------+-----------+\n",
      "|      10|   JG503434|\n",
      "|      11|   JG103252|\n",
      "|       1|   JB147599|\n",
      "|       9|   JE293535|\n",
      "|       9|   JE439378|\n",
      "+--------+-----------+\n",
      "only showing top 5 rows\n"
     ]
    }
   ],
   "source": [
    "# acces aux colonnes District et Case Number\n",
    "df_infer.select(\"District\", \"Case Number\").show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ceaf978f",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-11-09T12:47:37.109393Z",
     "iopub.status.busy": "2026-11-09T12:47:37.109393Z",
     "iopub.status.idle": "2026-11-09T12:47:37.580653Z",
     "shell.execute_reply": "2026-11-09T12:47:37.579436Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "colonnes de df2 : ['District', 'Case Number']\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+-----------+---+\n",
      "|District|Case Number| un|\n",
      "+--------+-----------+---+\n",
      "|      10|   JG503434|  1|\n",
      "|      11|   JG103252|  1|\n",
      "|       1|   JB147599|  1|\n",
      "|       9|   JE293535|  1|\n",
      "|       9|   JE439378|  1|\n",
      "+--------+-----------+---+\n",
      "only showing top 5 rows\n",
      "apres withColumn('un', lit(1)) : ['District', 'Case Number', 'un']\n",
      "apres drop('un') : ['District', 'Case Number']\n",
      "apres drop('ID','Case Number','Block') : ['Date', 'IUCR', 'Primary Type', 'Description', 'Location Description', 'Arrest', 'Domestic', 'Beat', 'District', 'Ward', 'Community Area', 'FBI Code', 'X Coordinate', 'Y Coordinate', 'Year', 'Updated On', 'Latitude', 'Longitude', 'Location']\n"
     ]
    }
   ],
   "source": [
    "# DataFrame restreint a ces 2 colonnes\n",
    "df2 = df_infer.select(\"District\", \"Case Number\")\n",
    "print(\"colonnes de df2 :\", df2.columns)\n",
    "\n",
    "# ajout d'une colonne constante = 1\n",
    "df2 = df2.withColumn(\"un\", F.lit(1))\n",
    "df2.show(5)\n",
    "print(\"apres withColumn('un', lit(1)) :\", df2.columns)\n",
    "\n",
    "# suppression de cette colonne\n",
    "df2 = df2.drop(\"un\")\n",
    "print(\"apres drop('un') :\", df2.columns)\n",
    "\n",
    "# suppression de ID, Case Number, Block\n",
    "df_sans = df_infer.drop(\"ID\", \"Case Number\", \"Block\")\n",
    "print(\"apres drop('ID','Case Number','Block') :\", df_sans.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a310917",
   "metadata": {},
   "source": [
    "## 7. Affichages ciblés\n",
    "\n",
    "5 premières valeurs de `IUCR` ; 5 premières lignes de `Case Number`, `Date`, `Arrest`\n",
    "**simultanément** ; 2 enregistrements **en mode vertical** et **sans troncature**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "9ec92e35",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-11-09T12:47:37.586182Z",
     "iopub.status.busy": "2026-11-09T12:47:37.586182Z",
     "iopub.status.idle": "2026-11-09T12:47:37.743090Z",
     "shell.execute_reply": "2026-11-09T12:47:37.741101Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+\n",
      "|IUCR|\n",
      "+----+\n",
      "|1582|\n",
      "|2017|\n",
      "|0281|\n",
      "|1753|\n",
      "|1753|\n",
      "+----+\n",
      "only showing top 5 rows"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "df_infer.select(\"IUCR\").show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "6fd70a41",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-11-09T12:47:37.747092Z",
     "iopub.status.busy": "2026-11-09T12:47:37.746092Z",
     "iopub.status.idle": "2026-11-09T12:47:37.941060Z",
     "shell.execute_reply": "2026-11-09T12:47:37.939523Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+--------------------+------+\n",
      "|Case Number|                Date|Arrest|\n",
      "+-----------+--------------------+------+\n",
      "|   JG503434|07/29/2022 03:39:...|  true|\n",
      "|   JG103252|01/03/2023 04:44:...|  true|\n",
      "|   JB147599|08/26/2017 10:00:...| false|\n",
      "|   JE293535|10/01/2020 12:01:...| false|\n",
      "|   JE439378|09/24/2015 12:00:...| false|\n",
      "+-----------+--------------------+------+\n",
      "only showing top 5 rows"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "df_infer.select(\"Case Number\", \"Date\", \"Arrest\").show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "509d9911",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-11-09T12:47:37.945596Z",
     "iopub.status.busy": "2026-11-09T12:47:37.944597Z",
     "iopub.status.idle": "2026-11-09T12:47:38.156880Z",
     "shell.execute_reply": "2026-11-09T12:47:38.154872Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-RECORD 0-------------------------------------\n",
      " Case Number  | JG503434                      \n",
      " Date         | 07/29/2022 03:39:00 AM        \n",
      " Arrest       | true                          \n",
      " Primary Type | OFFENSE INVOLVING CHILDREN    \n",
      " Block        | 023XX S TROY ST               \n",
      " Description  | CHILD PORNOGRAPHY             \n",
      "-RECORD 1-------------------------------------\n",
      " Case Number  | JG103252                      \n",
      " Date         | 01/03/2023 04:44:00 PM        \n",
      " Arrest       | true                          \n",
      " Primary Type | NARCOTICS                     \n",
      " Block        | 039XX W WASHINGTON BLVD       \n",
      " Description  | MANUFACTURE / DELIVER - CRACK \n",
      "only showing top 2 rows\n"
     ]
    }
   ],
   "source": [
    "df_infer.select(\"Case Number\", \"Date\", \"Arrest\", \"Primary Type\", \"Block\", \"Description\") \\\n",
    "        .show(2, truncate=False, vertical=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff89dd36",
   "metadata": {},
   "source": [
    "## 8. Schéma manuel (au lieu de `inferSchema`)\n",
    "\n",
    "On déclare les types une fois pour toutes : Spark ne devine plus rien."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "935259a4",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-11-09T12:47:38.163964Z",
     "iopub.status.busy": "2026-11-09T12:47:38.163964Z",
     "iopub.status.idle": "2026-11-09T12:47:39.030925Z",
     "shell.execute_reply": "2026-11-09T12:47:39.029909Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "schéma manuel -> count() : 0.85 s (4,778,681 lignes)\n",
      "vs inferSchema : 8.1 x plus rapide\n",
      "root\n",
      " |-- ID: long (nullable = true)\n",
      " |-- Case Number: string (nullable = true)\n",
      " |-- Date: timestamp (nullable = true)\n",
      " |-- Block: string (nullable = true)\n",
      " |-- IUCR: string (nullable = true)\n",
      " |-- Primary Type: string (nullable = true)\n",
      " |-- Description: string (nullable = true)\n",
      " |-- Location Description: string (nullable = true)\n",
      " |-- Arrest: boolean (nullable = true)\n",
      " |-- Domestic: boolean (nullable = true)\n",
      " |-- Beat: integer (nullable = true)\n",
      " |-- District: integer (nullable = true)\n",
      " |-- Ward: integer (nullable = true)\n",
      " |-- Community Area: integer (nullable = true)\n",
      " |-- FBI Code: string (nullable = true)\n",
      " |-- X Coordinate: double (nullable = true)\n",
      " |-- Y Coordinate: double (nullable = true)\n",
      " |-- Year: integer (nullable = true)\n",
      " |-- Updated On: timestamp (nullable = true)\n",
      " |-- Latitude: double (nullable = true)\n",
      " |-- Longitude: double (nullable = true)\n",
      " |-- Location: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "schema = StructType([\n",
    "    StructField(\"ID\", LongType(), True),\n",
    "    StructField(\"Case Number\", StringType(), True),\n",
    "    StructField(\"Date\", StringType(), True),       # converti en timestamp juste apres\n",
    "    StructField(\"Block\", StringType(), True),\n",
    "    StructField(\"IUCR\", StringType(), True),\n",
    "    StructField(\"Primary Type\", StringType(), True),\n",
    "    StructField(\"Description\", StringType(), True),\n",
    "    StructField(\"Location Description\", StringType(), True),\n",
    "    StructField(\"Arrest\", BooleanType(), True),\n",
    "    StructField(\"Domestic\", BooleanType(), True),\n",
    "    StructField(\"Beat\", IntegerType(), True),\n",
    "    StructField(\"District\", IntegerType(), True),\n",
    "    StructField(\"Ward\", IntegerType(), True),\n",
    "    StructField(\"Community Area\", IntegerType(), True),\n",
    "    StructField(\"FBI Code\", StringType(), True),\n",
    "    StructField(\"X Coordinate\", DoubleType(), True),\n",
    "    StructField(\"Y Coordinate\", DoubleType(), True),\n",
    "    StructField(\"Year\", IntegerType(), True),\n",
    "    StructField(\"Updated On\", StringType(), True),\n",
    "    StructField(\"Latitude\", DoubleType(), True),\n",
    "    StructField(\"Longitude\", DoubleType(), True),\n",
    "    StructField(\"Location\", StringType(), True),\n",
    "])\n",
    "\n",
    "t0 = time.perf_counter()\n",
    "df = spark.read.csv(PATH, header=True, schema=schema)\n",
    "# les dates arrivent en texte : ISO (cet export) ou MM/DD/YYYY hh:mm:ss AM/PM (export officiel)\n",
    "for c in (\"Date\", \"Updated On\"):\n",
    "    df = df.withColumn(c, F.coalesce(\n",
    "        F.try_to_timestamp(c, F.lit(\"yyyy-MM-dd'T'HH:mm:ss.SSS\")),   # export reconstruit (ISO)\n",
    "        F.try_to_timestamp(c, F.lit(\"MM/dd/yyyy hh:mm:ss a\"))))      # export officiel\n",
    "n = df.count()\n",
    "t_schema = time.perf_counter() - t0\n",
    "RESULTS[\"8_schema_manuel_count\"] = round(t_schema, 2)\n",
    "print(f\"schéma manuel -> count() : {t_schema:.2f} s ({n:,} lignes)\")\n",
    "print(f\"vs inferSchema : {RESULTS['5_inferSchema_count'] / t_schema:.1f} x plus rapide\")\n",
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11a8fa48",
   "metadata": {},
   "source": [
    "## 9. `df.limit()`, `df.head()`, `df.take()`, `df.collect()`\n",
    "\n",
    "Exécutés **exactement tels qu'écrits** dans l'énoncé, puis avec un argument quand la signature\n",
    "l'exige."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "4ddd4e8a",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-11-09T12:47:39.034929Z",
     "iopub.status.busy": "2026-11-09T12:47:39.034929Z",
     "iopub.status.idle": "2026-11-09T12:47:39.042598Z",
     "shell.execute_reply": "2026-11-09T12:47:39.041083Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "df.limit() sans argument -> TypeError : DataFrame.limit() missing 1 required positional argument: 'num'\n",
      "  (0.0006 s)\n"
     ]
    }
   ],
   "source": [
    "# df.limit() sans argument\n",
    "t0 = time.perf_counter()\n",
    "try:\n",
    "    df.limit()\n",
    "except Exception as e:\n",
    "    print(f\"df.limit() sans argument -> {type(e).__name__} : {e}\")\n",
    "print(f\"  ({time.perf_counter()-t0:.4f} s)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "97884ada",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-11-09T12:47:39.045614Z",
     "iopub.status.busy": "2026-11-09T12:47:39.045614Z",
     "iopub.status.idle": "2026-11-09T12:47:39.053219Z",
     "shell.execute_reply": "2026-11-09T12:47:39.052199Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "df.take() sans argument -> TypeError : DataFrame.take() missing 1 required positional argument: 'num'\n"
     ]
    }
   ],
   "source": [
    "# df.take() sans argument\n",
    "try:\n",
    "    df.take()\n",
    "except Exception as e:\n",
    "    print(f\"df.take() sans argument -> {type(e).__name__} : {e}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "939b288b",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-11-09T12:47:39.057731Z",
     "iopub.status.busy": "2026-11-09T12:47:39.057731Z",
     "iopub.status.idle": "2026-11-09T12:47:39.458566Z",
     "shell.execute_reply": "2026-11-09T12:47:39.457176Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "df.limit(5) : 6.4 ms -> DataFrame (transformation paresseuse, 0 task)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "df.head()   : 0.26 s -> Row (1 seul enregistrement) Row(ID=13311263, Case Number='JG503434', Date=datetime.datetime(2022, 7, 29, 3, 39), Block='023XX S TROY ST', IUCR='1582...\n",
      "df.take(5)  : 0.12 s -> list de 5 Rows\n"
     ]
    }
   ],
   "source": [
    "# les versions correctes, et ce qu'elles declenchent\n",
    "t0 = time.perf_counter(); df_lim = df.limit(5); t = time.perf_counter()-t0\n",
    "print(f\"df.limit(5) : {t*1000:.1f} ms -> {type(df_lim).__name__} (transformation paresseuse, 0 task)\")\n",
    "\n",
    "t0 = time.perf_counter(); h = df.head(); t_head = time.perf_counter()-t0\n",
    "print(f\"df.head()   : {t_head:.2f} s -> {type(h).__name__} (1 seul enregistrement) {str(h)[:120]}...\")\n",
    "\n",
    "t0 = time.perf_counter(); tk = df.take(5); t_take = time.perf_counter()-t0\n",
    "print(f\"df.take(5)  : {t_take:.2f} s -> {type(tk).__name__} de {len(tk)} Rows\")\n",
    "RESULTS[\"9_limit_ms\"] = round(t*1000, 1); RESULTS[\"9_head\"] = round(t_head, 2); RESULTS[\"9_take\"] = round(t_take, 2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "173083e0",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-11-09T12:47:39.464261Z",
     "iopub.status.busy": "2026-11-09T12:47:39.463259Z",
     "iopub.status.idle": "2026-11-09T12:48:01.613567Z",
     "shell.execute_reply": "2026-11-09T12:48:01.612555Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "df.collect() a échoué après 22 s : Py4JJavaError\n",
      ": org.apache.spark.SparkException: Job aborted due to stage failure: Total size of serialized results of 11 tasks (541.9 MiB) is bigger than spark.driver.maxResultSize (512.0 MiB)\n"
     ]
    }
   ],
   "source": [
    "# df.collect() : rapatrie TOUTES les lignes dans le driver\n",
    "t0 = time.perf_counter()\n",
    "try:\n",
    "    lignes = df.collect()\n",
    "    t_collect = time.perf_counter() - t0\n",
    "    print(f\"df.collect() : {t_collect:.2f} s -> {len(lignes):,} Rows dans le driver\")\n",
    "    print(f\"RAM du process driver : {psutil.Process().memory_info().rss/2**30:.2f} Go\")\n",
    "    del lignes\n",
    "except Exception as e:\n",
    "    t_collect = time.perf_counter() - t0\n",
    "    print(f\"df.collect() a échoué après {t_collect:.0f} s : {type(e).__name__}\")\n",
    "    lignes_err = [l.strip() for l in str(e).splitlines() if \"maxResultSize\" in l or \"serialized results\" in l]\n",
    "    print(lignes_err[0][:300] if lignes_err else str(e).splitlines()[0][:300])\n",
    "    RESULTS[\"9_collect_echec_s\"] = round(t_collect)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "093f1691",
   "metadata": {},
   "source": [
    "### Conclusion Q9\n",
    "\n",
    "| appel | signature | retour | coût |\n",
    "|---|---|---|---|\n",
    "| `df.limit()` | invalide, il faut `limit(n)` | — | `TypeError` immédiat |\n",
    "| `df.take()` | invalide, il faut `take(n)` | — | `TypeError` immédiat |\n",
    "| `df.limit(5)` | valide | un DataFrame | 6 ms, paresseux, 0 task |\n",
    "| `df.head()` | valide | 1 `Row` | un job, une seule ligne remonte |\n",
    "| `df.take(5)` | valide | liste de 5 `Row` | un job, 5 lignes remontent |\n",
    "| `df.collect()` | valide | toutes les `Row` | échec en 22 s, 541,9 Mo pour 512 Mo autorisés |\n",
    "\n",
    "`limit()` et `take()` sans argument ne compilent pas. `limit(n)` ne déclenche aucun job. `head()` et\n",
    "`take(n)` rapatrient n lignes, ce qui reste borné.\n",
    "\n",
    "`collect()` est l'appel qui casse : il ramène toutes les lignes dans le driver. On agrège côté\n",
    "Spark et on ne collecte que du réduit."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f57fd6b5",
   "metadata": {},
   "source": [
    "## 10. Mise en cache"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "9344ee57",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-11-09T12:48:01.617075Z",
     "iopub.status.busy": "2026-11-09T12:48:01.617075Z",
     "iopub.status.idle": "2026-11-09T12:48:35.216639Z",
     "shell.execute_reply": "2026-11-09T12:48:35.214624Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "df.cache() + 1er count() (matérialisation) : 33.20 s\n",
      "storageLevel : Disk Memory Deserialized 1x Replicated\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2e count() sur le df caché : 0.39 s -> gain x85.9\n"
     ]
    }
   ],
   "source": [
    "t0 = time.perf_counter(); df.cache(); df.count(); t1 = time.perf_counter() - t0\n",
    "print(f\"df.cache() + 1er count() (matérialisation) : {t1:.2f} s\")\n",
    "print(\"storageLevel :\", df.storageLevel)\n",
    "\n",
    "t0 = time.perf_counter(); df.count(); t2 = time.perf_counter() - t0\n",
    "print(f\"2e count() sur le df caché : {t2:.2f} s -> gain x{t1/t2:.1f}\")\n",
    "RESULTS[\"10_cache_avant\"] = round(t1, 2); RESULTS[\"10_cache_apres\"] = round(t2, 2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "210c6018",
   "metadata": {},
   "source": [
    "## 11. `describe()` vs `summary()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "34270c70",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-11-09T12:48:35.222553Z",
     "iopub.status.busy": "2026-11-09T12:48:35.221543Z",
     "iopub.status.idle": "2026-11-09T12:49:16.819037Z",
     "shell.execute_reply": "2026-11-09T12:49:16.818025Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+------------------+-----------+--------------+------------------+-----------------+---------------+--------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+-------------------+-------------------+-----------------------------+\n",
      "|summary|ID                |Case Number|Block         |IUCR              |Primary Type     |Description    |Location Description|Beat              |District          |Ward              |Community Area    |FBI Code          |X Coordinate      |Y Coordinate      |Year              |Latitude           |Longitude          |Location                     |\n",
      "+-------+------------------+-----------+--------------+------------------+-----------------+---------------+--------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+-------------------+-------------------+-----------------------------+\n",
      "|count  |4778681           |4778681    |4778681       |4778681           |4778681          |4778681        |4765346             |4778681           |4778671           |4762934           |4762260           |4778681           |4639516           |4639516           |4778681           |4639516            |4639516            |4639516                      |\n",
      "|mean   |8295168.073901983 |121135.0   |NULL          |1118.6681095117915|NULL             |NULL           |NULL                |1178.4850708804374|11.312354208942192|22.82153101428657 |37.375004934631875|11.963148123480636|1164594.948358622 |1885727.6083119446|2011.9394960659647|41.842031892881316 |-87.67151672354201 |NULL                         |\n",
      "|stddev |2928305.7089460925|NULL       |NULL          |808.6209370467046 |NULL             |NULL           |NULL                |710.3029838153512 |7.040792170888419 |13.853425820754138|21.519802798047042|7.28935695889752  |16995.721801510103|32411.83042989041 |5.333368053449232 |0.08917910470156476|0.06157710810517837|NULL                         |\n",
      "|min    |670               |03J493690  |0000X E 100 ST|0110              |ARSON            |$300 AND UNDER |\"CTA \"\"L\"\" TRAIN\"   |111               |1                 |0                 |0                 |01A               |0.0               |0.0               |2001              |36.619446395       |-91.686565684      |(36.619446395, -91.686565684)|\n",
      "|max    |14382985          |ZZ740108   |XX S          |5132              |WEAPONS VIOLATION|WIREROOM/SPORTS|YARD                |6100              |61                |50                |77                |27                |1205119.0         |1951573.0         |2026              |42.022709624       |-87.524529378      |(42.022709624, -87.665704214)|\n",
      "+-------+------------------+-----------+--------------+------------------+-----------------+---------------+--------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+-------------------+-------------------+-----------------------------+\n",
      "\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "describe() : 39.29 s | tasks cumulées : 258\n"
     ]
    }
   ],
   "source": [
    "t0 = time.perf_counter(); df.describe().show(truncate=False); t_desc = time.perf_counter() - t0\n",
    "RESULTS[\"11_describe\"] = round(t_desc, 2)\n",
    "print(f\"describe() : {t_desc:.2f} s | tasks cumulées : {taches_consommees():,}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "6791129c",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-11-09T12:49:16.822567Z",
     "iopub.status.busy": "2026-11-09T12:49:16.822567Z",
     "iopub.status.idle": "2026-11-09T12:52:40.153228Z",
     "shell.execute_reply": "2026-11-09T12:52:40.151706Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+------------------+-----------+--------------+------------------+-----------------+---------------+--------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+-------------------+-------------------+-----------------------------+\n",
      "|summary|ID                |Case Number|Block         |IUCR              |Primary Type     |Description    |Location Description|Beat              |District          |Ward              |Community Area    |FBI Code          |X Coordinate      |Y Coordinate      |Year              |Latitude           |Longitude          |Location                     |\n",
      "+-------+------------------+-----------+--------------+------------------+-----------------+---------------+--------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+-------------------+-------------------+-----------------------------+\n",
      "|count  |4778681           |4778681    |4778681       |4778681           |4778681          |4778681        |4765346             |4778681           |4778671           |4762934           |4762260           |4778681           |4639516           |4639516           |4778681           |4639516            |4639516            |4639516                      |\n",
      "|mean   |8295168.073901983 |121135.0   |NULL          |1118.6681095117915|NULL             |NULL           |NULL                |1178.4850708804374|11.312354208942192|22.82153101428657 |37.375004934631875|11.963148123480636|1164594.948358622 |1885727.6083119446|2011.9394960659647|41.842031892881316 |-87.67151672354201 |NULL                         |\n",
      "|stddev |2928305.7089460925|NULL       |NULL          |808.6209370467046 |NULL             |NULL           |NULL                |710.3029838153512 |7.040792170888419 |13.853425820754138|21.519802798047042|7.28935695889752  |16995.721801510103|32411.83042989041 |5.333368053449232 |0.08917910470156476|0.06157710810517837|NULL                         |\n",
      "|min    |670               |03J493690  |0000X E 100 ST|0110              |ARSON            |$300 AND UNDER |\"CTA \"\"L\"\" TRAIN\"   |111               |1                 |0                 |0                 |01A               |0.0               |0.0               |2001              |36.619446395       |-91.686565684      |(36.619446395, -91.686565684)|\n",
      "|25%    |6881454           |121135.0   |NULL          |560.0             |NULL             |NULL           |NULL                |621               |6                 |10                |23                |6.0               |1152932.0         |1858954.0         |2009              |41.768382919       |-87.713900069      |NULL                         |\n",
      "|50%    |8703988           |121135.0   |NULL          |860.0             |NULL             |NULL           |NULL                |1033              |10                |23                |32                |11.0              |1166144.0         |1891266.0         |2012              |41.857181658       |-87.665741123      |NULL                         |\n",
      "|75%    |10652677          |121135.0   |NULL          |1320.0            |NULL             |NULL           |NULL                |1724              |17                |34                |56                |18.0              |1176372.0         |1909055.0         |2016              |41.906287777       |-87.628102401      |NULL                         |\n",
      "|max    |14382985          |ZZ740108   |XX S          |5132              |WEAPONS VIOLATION|WIREROOM/SPORTS|YARD                |6100              |61                |50                |77                |27                |1205119.0         |1951573.0         |2026              |42.022709624       |-87.524529378      |(42.022709624, -87.665704214)|\n",
      "+-------+------------------+-----------+--------------+------------------+-----------------+---------------+--------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------+-------------------+-------------------+-----------------------------+\n",
      "\n",
      "summary() : 203.28 s | tasks cumulées : 287\n"
     ]
    }
   ],
   "source": [
    "t0 = time.perf_counter(); df.summary().show(truncate=False); t_sum = time.perf_counter() - t0\n",
    "RESULTS[\"11_summary\"] = round(t_sum, 2)\n",
    "print(f\"summary() : {t_sum:.2f} s | tasks cumulées : {taches_consommees():,}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4595508b",
   "metadata": {},
   "source": [
    "### Conclusion Q11\n",
    "\n",
    "`describe()` renvoie `count`, `mean`, `stddev`, `min` et `max`. `summary()` ajoute les quantiles\n",
    "25, 50 et 75 % (`percentile_approx`), ce qui explique l'écart entre 39 s et 203 s.\n",
    "\n",
    "Les deux relancent un scan complet, donc les enchaîner après un `cache()` est la seule façon de ne\n",
    "pas payer deux fois."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39279e46",
   "metadata": {},
   "source": [
    "## 12. Valeurs manquantes, valeurs uniques, effectifs par type de crime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "ee3d19b7",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-11-09T12:52:40.158759Z",
     "iopub.status.busy": "2026-11-09T12:52:40.157244Z",
     "iopub.status.idle": "2026-11-09T12:53:05.410652Z",
     "shell.execute_reply": "2026-11-09T12:53:05.409638Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "valeurs manquantes : 5.30 s (1 passe, 0 shuffle)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "valeurs uniques   : 19.94 s (4 lots de countDistinct)\n",
      "\n",
      "colonne                   manquantes       %     uniques\n",
      "ID                                 0   0.00%   4,695,426\n",
      "Case Number                        0   0.00%   4,695,409\n",
      "Date                               0   0.00%   1,950,543\n",
      "Block                              0   0.00%      42,382\n",
      "IUCR                               0   0.00%         417\n",
      "Primary Type                       0   0.00%          35\n",
      "Description                        0   0.00%         554\n",
      "Location Description          13,335   0.28%         168\n",
      "Arrest                             0   0.00%           2\n",
      "Domestic                           0   0.00%           2\n",
      "Beat                               0   0.00%         306\n",
      "District                          10   0.00%          25\n",
      "Ward                          15,747   0.33%          51\n",
      "Community Area                16,421   0.34%          78\n",
      "FBI Code                           0   0.00%          27\n",
      "X Coordinate                 139,165   2.91%      73,928\n",
      "Y Coordinate                 139,165   2.91%     123,536\n",
      "Year                               0   0.00%          26\n",
      "Updated On                         0   0.00%       7,471\n",
      "Latitude                     139,165   2.91%     594,464\n",
      "Longitude                    139,165   2.91%     594,164\n",
      "Location                     139,165   2.91%     594,998\n"
     ]
    }
   ],
   "source": [
    "# 1) valeurs manquantes : une seule passe, sans shuffle\n",
    "t0 = time.perf_counter()\n",
    "r_null = df.select([F.count(F.when(F.col(c).isNull(), 1)).alias(c) for c in df.columns]).first()\n",
    "t_null = time.perf_counter() - t0\n",
    "RESULTS[\"12_manquantes\"] = round(t_null, 2)\n",
    "print(f\"valeurs manquantes : {t_null:.2f} s (1 passe, 0 shuffle)\")\n",
    "\n",
    "# 2) valeurs uniques : countDistinct = un shuffle par colonne -> par lots de 5\n",
    "t0 = time.perf_counter()\n",
    "uniq = {}\n",
    "for i in range(0, len(df.columns), 5):\n",
    "    lot = df.columns[i:i+5]\n",
    "    row = df.select([F.countDistinct(F.col(c)).alias(c) for c in lot]).first()\n",
    "    uniq.update({c: row[c] for c in lot})\n",
    "t_uniq = time.perf_counter() - t0\n",
    "RESULTS[\"12_uniques\"] = round(t_uniq, 2)\n",
    "print(f\"valeurs uniques   : {t_uniq:.2f} s (4 lots de countDistinct)\\n\")\n",
    "\n",
    "print(f\"{'colonne':<24}{'manquantes':>12}{'%':>8}{'uniques':>12}\")\n",
    "for c in df.columns:\n",
    "    m, u = r_null[c], uniq[c]\n",
    "    print(f\"{c:<24}{m:>12,}{100*m/n:>7.2f}%{u:>12,}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "ba8fba5b",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-11-09T12:53:05.415015Z",
     "iopub.status.busy": "2026-11-09T12:53:05.415015Z",
     "iopub.status.idle": "2026-11-09T12:53:06.517212Z",
     "shell.execute_reply": "2026-11-09T12:53:06.515836Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------------------------+-------+-----------+\n",
      "|Primary Type                     |count  |pourcentage|\n",
      "+---------------------------------+-------+-----------+\n",
      "|THEFT                            |1044026|21.85      |\n",
      "|BATTERY                          |856076 |17.91      |\n",
      "|CRIMINAL DAMAGE                  |521203 |10.91      |\n",
      "|NARCOTICS                        |450421 |9.43       |\n",
      "|ASSAULT                          |304841 |6.38       |\n",
      "|OTHER OFFENSE                    |295000 |6.17       |\n",
      "|BURGLARY                         |269230 |5.63       |\n",
      "|DECEPTIVE PRACTICE               |246498 |5.16       |\n",
      "|MOTOR VEHICLE THEFT              |213950 |4.48       |\n",
      "|ROBBERY                          |178236 |3.73       |\n",
      "|CRIMINAL TRESPASS                |128985 |2.7        |\n",
      "|WEAPONS VIOLATION                |63478  |1.33       |\n",
      "|OFFENSE INVOLVING CHILDREN       |41131  |0.86       |\n",
      "|PUBLIC PEACE VIOLATION           |35097  |0.73       |\n",
      "|PROSTITUTION                     |34995  |0.73       |\n",
      "|SEX OFFENSE                      |20457  |0.43       |\n",
      "|CRIM SEXUAL ASSAULT              |20269  |0.42       |\n",
      "|INTERFERENCE WITH PUBLIC OFFICER |14820  |0.31       |\n",
      "|GAMBLING                         |7833   |0.16       |\n",
      "|ARSON                            |7481   |0.16       |\n",
      "|LIQUOR LAW VIOLATION             |7363   |0.15       |\n",
      "|CRIMINAL SEXUAL ASSAULT          |5317   |0.11       |\n",
      "|KIDNAPPING                       |3754   |0.08       |\n",
      "|STALKING                         |3023   |0.06       |\n",
      "|INTIMIDATION                     |2751   |0.06       |\n",
      "|CONCEALED CARRY LICENSE VIOLATION|748    |0.02       |\n",
      "|OBSCENITY                        |674    |0.01       |\n",
      "|HOMICIDE                         |429    |0.01       |\n",
      "|NON-CRIMINAL                     |171    |0.0        |\n",
      "|PUBLIC INDECENCY                 |148    |0.0        |\n",
      "|HUMAN TRAFFICKING                |129    |0.0        |\n",
      "|OTHER NARCOTIC VIOLATION         |96     |0.0        |\n",
      "|NON - CRIMINAL                   |38     |0.0        |\n",
      "|NON-CRIMINAL (SUBJECT SPECIFIED) |9      |0.0        |\n",
      "|RITUALISM                        |4      |0.0        |\n",
      "+---------------------------------+-------+-----------+\n",
      "\n",
      "groupBy Primary Type : 1.09 s | total = 4,778,681 crimes\n"
     ]
    }
   ],
   "source": [
    "t0 = time.perf_counter()\n",
    "crimes = (df.groupBy(\"Primary Type\").count()\n",
    "            .withColumn(\"pourcentage\", F.round(F.col(\"count\") / F.lit(n) * 100, 2))\n",
    "            .orderBy(F.desc(\"count\")))\n",
    "crimes.show(50, truncate=False)\n",
    "t_crimes = time.perf_counter() - t0\n",
    "RESULTS[\"12_par_type\"] = round(t_crimes, 2)\n",
    "print(f\"groupBy Primary Type : {t_crimes:.2f} s | total = {n:,} crimes\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "951945d8",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-11-09T12:53:06.521248Z",
     "iopub.status.busy": "2026-11-09T12:53:06.520247Z",
     "iopub.status.idle": "2026-11-09T12:53:07.428002Z",
     "shell.execute_reply": "2026-11-09T12:53:07.426988Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\n",
      "  \"1_pandas\": 1.66,\n",
      "  \"1_pandas_engine_c\": 6.38,\n",
      "  \"2_spark_defaut_count\": 1.93,\n",
      "  \"2_spark_header_count\": 0.99,\n",
      "  \"3_partitions\": 14,\n",
      "  \"3_colonnes\": 22,\n",
      "  \"5_inferSchema_count\": 6.91,\n",
      "  \"8_schema_manuel_count\": 0.85,\n",
      "  \"9_limit_ms\": 6.4,\n",
      "  \"9_head\": 0.26,\n",
      "  \"9_take\": 0.12,\n",
      "  \"9_collect_echec_s\": 22,\n",
      "  \"10_cache_avant\": 33.2,\n",
      "  \"10_cache_apres\": 0.39,\n",
      "  \"11_describe\": 39.29,\n",
      "  \"11_summary\": 203.28,\n",
      "  \"12_manquantes\": 5.3,\n",
      "  \"12_uniques\": 19.94,\n",
      "  \"12_par_type\": 1.09\n",
      "}\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Session Spark arrêtée.\n"
     ]
    }
   ],
   "source": [
    "print(json.dumps(RESULTS, indent=2, ensure_ascii=False))\n",
    "open(\"resultats.json\", \"w\", encoding=\"utf-8\").write(json.dumps(RESULTS, indent=2, ensure_ascii=False))\n",
    "\n",
    "df.unpersist()\n",
    "spark.stop()\n",
    "print(\"\\nSession Spark arrêtée.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "96b6124c",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "### Note sur la variabilité\n",
    "\n",
    "Les temps ci-dessus sont pris avec le fichier en cache disque. À froid, sur une première lecture\n",
    "réelle, c'est 4 à 7 fois plus lent, et c'est ce que voit un poste qui ouvre le fichier pour la\n",
    "première fois :\n",
    "\n",
    "| cellule | à froid | en cache |\n",
    "|---|---|---|\n",
    "| `pd.read_csv` | 7,7 s | 1,7 s |\n",
    "| `spark.read.csv(..., header=True).count()` | 6,7 s | 1,0 s |\n",
    "| `inferSchema=True` + `count()` | 21,5 s | 6,9 s |\n",
    "\n",
    "Sur l'export complet (1,9 Go, ~8,07 M lignes), comptez environ 1,7 fois ces valeurs. L'ordre de\n",
    "grandeur et les conclusions ne changent pas."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}