KidsReflect_data_pipeline.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Preprocessing of the KidsReflect Dataset\n",
    "\n",
    "This notebook restructures and cleans the raw KidsReflect dataset (comprising 4 CSV files). The main processing steps are:\n",
    "\n",
    "1. **Loading the Dataset:**  \n",
    "   Load the main CSV file and additional CSV files.\n",
    "\n",
    "2. **Reshaping Data:**  \n",
    "   Convert data from wide format to long format.\n",
    "\n",
    "3. **Adding Reference Texts:**  \n",
    "   Integrate reference texts from a separate file.\n",
    "\n",
    "4. **Merging Sessions:**  \n",
    "   Merge sessions belonging to the same subject.\n",
    "\n",
    "5. **Removing Sessions with All NaN Iterations:**  \n",
    "   Filter out sessions where all key columns contain NaN values.\n",
    "\n",
    "6. **Removing Dummy Rows:**  \n",
    "   Eliminate rows flagged as dummy (iterations that were likely skipped in a second session)\n",
    "\n",
    "7. **Removing Fake Subjects:**  \n",
    "   Remove test or pilot subjects based on naming conventions.\n",
    "\n",
    "8. **Removing Gibberish Rows:**  \n",
    "   Filter out rows with unexploitable or gibberish content.\n",
    "\n",
    "9. **Filtering Subjects with Insufficient Iterations:**  \n",
    "   Remove subjects with fewer than 4 iterations\n",
    "\n",
    "10. **Building Sessions Composition:**  \n",
    "    Compile a final session composition for each subject.\n",
    "\n",
    "**Notes:**  \n",
    "- **Mandatory Steps:** Steps 1 to 4 and Step 10 are required—they prepare the dataset for analysis without filtering out any data.  \n",
    "- **Optional Steps:** Steps 5 to 9 are recommended for cleaning but can be individually disabled (by setting their corresponding boolean flags to `False` in Step 0).  \n",
    "- You can run the notebook cell-by-cell for a stepwise execution or run all cells at once once you've set the desired options.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 0: Import libraries and define parameters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import re\n",
    "import json\n",
    "import pandas as pd\n",
    "\n",
    "# Define file paths and parameters\n",
    "DATA_DIR = \"raw_data\"\n",
    "MAIN_FILE = \"talence_end_mc.csv\"\n",
    "ADDITIONAL_FILES = [\"KR_BC_session1.csv\", \"KR_AV_session1.csv\", \"KR_MC_session1.csv\"]\n",
    "REFERENCE_FILE = os.path.join(DATA_DIR, \"reference_data.json\")\n",
    "OUTPUT_DIR = \"processed_data\"\n",
    "OUTPUT_FILE_NAME = \"KR_preprocessed.csv\"\n",
    "OUTPUT_FILE = os.path.join(OUTPUT_DIR, OUTPUT_FILE_NAME)\n",
    "\n",
    "# Define optional step flags\n",
    "RUN_NAN = True         # remove subjects with NaN values on all cycles\n",
    "RUN_DUMMY = True       # remove entries made by the experimenter to skip already done cycles\n",
    "RUN_FAKE = True        # remove fake subjects (test subjects)\n",
    "RUN_GIBBERISH = True   # remove gibberish rows (unexploitables entries by the children)\n",
    "RUN_INSUFFICIENT = True  # remove subjects with insufficient data (more than 50% of missing data, but can be changed in the function)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 1: Data Loading\n",
    "\n",
    "We start by loading the main dataset from the file `talence_end_mc.csv`. However, some session data might be located in other files, so we also load three additional CSV files: `KR_BC_session1.csv`, `KR_AV_session1.csv`, and `KR_MC_session1.csv`. These files share the same structure as the main dataset but don't have headers, so we assign the column names from the main DataFrame.\n",
    "\n",
    "After loading these files, we concatenate all the data into a single DataFrame for analysis.\n",
    "\n",
    "Finally, we check for duplicate `_id` values to ensure data integrity."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Loaded main data: (424, 36)\n",
      "Loaded additional files: (101, 36)\n",
      "Total rows after concatenation: 525\n",
      "Duplicate _id values found: 202\n",
      "IDs with fully identical rows (excluding ['_id', 'origin']): 28\n",
      "IDs with different row values despite sharing the same ID: 73\n"
     ]
    }
   ],
   "source": [
    "def load_data(data_dir: str, main_file: str, verbose: bool = True) -> pd.DataFrame:\n",
    "    \"\"\"\n",
    "    Load the main dataset from a CSV file.\n",
    "    \n",
    "    Parameters:\n",
    "        data_dir (str): Directory containing the data.\n",
    "        main_file (str): Name of the main CSV file.\n",
    "        verbose (bool): Whether to print status messages.\n",
    "    \n",
    "    Returns:\n",
    "        pd.DataFrame: DataFrame with the loaded data and an \"origin\" column set to \"original\".\n",
    "    \"\"\"\n",
    "    os.makedirs(data_dir, exist_ok=True)\n",
    "    data_file_path = os.path.join(data_dir, main_file)\n",
    "    df = pd.read_csv(data_file_path, encoding=\"utf-8-sig\")\n",
    "    df[\"origin\"] = \"original\"\n",
    "    if verbose:\n",
    "        print(\"Loaded main data:\", df.shape)\n",
    "    return df\n",
    "\n",
    "\n",
    "def load_additional_files(data_dir: str, files: list, columns, verbose: bool = True) -> pd.DataFrame:\n",
    "    \"\"\"\n",
    "    Load and concatenate additional CSV files (without headers) from the specified directory.\n",
    "    \n",
    "    Parameters:\n",
    "        data_dir (str): Directory containing the additional files.\n",
    "        files (list): List of additional file names.\n",
    "        columns: Column names to assign to the additional data.\n",
    "        verbose (bool): Whether to print status messages.\n",
    "    \n",
    "    Returns:\n",
    "        pd.DataFrame: Concatenated DataFrame with an \"origin\" column set to \"new\".\n",
    "    \"\"\"\n",
    "    # Remove 'origin' from columns if present, since the additional files don't have it.\n",
    "    if isinstance(columns, (list, tuple)):\n",
    "        cleaned_columns = [col for col in columns if col != \"origin\"]\n",
    "    else:\n",
    "        cleaned_columns = columns.drop(\"origin\", errors=\"ignore\")\n",
    "    \n",
    "    dfs = []\n",
    "    for file in files:\n",
    "        file_path = os.path.join(data_dir, file)\n",
    "        temp_df = pd.read_csv(file_path, header=None, encoding=\"utf-8-sig\", skiprows=1)\n",
    "        # Assign only the cleaned columns\n",
    "        temp_df.columns = cleaned_columns\n",
    "        temp_df[\"origin\"] = \"new\"\n",
    "        dfs.append(temp_df)\n",
    "    result = pd.concat(dfs, ignore_index=True)\n",
    "    if verbose:\n",
    "        print(\"Loaded additional files:\", result.shape)\n",
    "    return result\n",
    "\n",
    "def check_duplicates(df: pd.DataFrame, id_col: str = '_id', exclude_cols: list = None, verbose: bool = True) -> dict:\n",
    "    \"\"\"\n",
    "    Check for duplicate rows based on the specified identifier column and print details on duplicates.\n",
    "    \n",
    "    Parameters:\n",
    "        df (pd.DataFrame): DataFrame to check for duplicate IDs.\n",
    "        id_col (str): The column name used as an identifier (default '_id').\n",
    "        exclude_cols (list): List of columns to exclude from the duplicate comparison \n",
    "                             (default is ['_id', 'origin']).\n",
    "        verbose (bool): Whether to print status messages.\n",
    "    \n",
    "    Returns:\n",
    "        dict: A dictionary with two keys:\n",
    "              - \"identical_duplicates\": list of IDs whose rows (excluding exclude_cols) are identical.\n",
    "              - \"non_identical_duplicates\": list of IDs that have duplicate rows with differences.\n",
    "    \"\"\"\n",
    "    if exclude_cols is None:\n",
    "        exclude_cols = [id_col, \"origin\"]\n",
    "    \n",
    "    # Find duplicate rows based on id_col (keeping all duplicates)\n",
    "    duplicate_ids = df[df[id_col].duplicated(keep=False)]\n",
    "    \n",
    "    if duplicate_ids.empty:\n",
    "        if verbose:\n",
    "            print(f\"No duplicate {id_col} values found!\")\n",
    "        return {\"identical_duplicates\": [], \"non_identical_duplicates\": []}\n",
    "    \n",
    "    if verbose:\n",
    "        print(f\"Duplicate {id_col} values found: {len(duplicate_ids)}\")\n",
    "    \n",
    "    identical_duplicates = []\n",
    "    non_identical_duplicates = []\n",
    "    \n",
    "    # Group duplicates by id_col and compare rows excluding the specified columns\n",
    "    for dup_id, group in duplicate_ids.groupby(id_col):\n",
    "        comp_df = group.drop(columns=exclude_cols, errors=\"ignore\")\n",
    "        # If each column in the group has only one unique value, rows are identical\n",
    "        if comp_df.nunique().sum() == comp_df.shape[1]:\n",
    "            identical_duplicates.append(dup_id)\n",
    "        else:\n",
    "            non_identical_duplicates.append(dup_id)\n",
    "    \n",
    "    if verbose:\n",
    "        print(f\"IDs with fully identical rows (excluding {exclude_cols}): {len(identical_duplicates)}\")\n",
    "        print(f\"IDs with different row values despite sharing the same ID: {len(non_identical_duplicates)}\")\n",
    "    \n",
    "    return {\n",
    "        \"identical_duplicates\": identical_duplicates,\n",
    "        \"non_identical_duplicates\": non_identical_duplicates\n",
    "    }\n",
    "\n",
    "df_main = load_data(DATA_DIR, MAIN_FILE, verbose=True)\n",
    "df_add = load_additional_files(DATA_DIR, ADDITIONAL_FILES, df_main.columns, verbose=True)\n",
    "concat_df = pd.concat([df_main, df_add], ignore_index=True)\n",
    "print(\"Total rows after concatenation:\", concat_df.shape[0])\n",
    "# # Search for duplicate '_id' values between the main and additional datasets (it will be handled during the merging step, step 5)\n",
    "dup_info = check_duplicates(concat_df, id_col='_id', verbose=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 2: Reshaping Data: Wide to Long Format\n",
    "\n",
    "This section of the code transforms our DataFrame (`df`) by cleaning up the data and restructuring it from a wide-format (with multiple cycle-specific columns) into a long-format DataFrame (`df_long`), where 1 row = 1 iteration.\n",
    "\n",
    "#### Initial Cleanup\n",
    "\n",
    "- **Drop the `__v` Column.** This column is unecessary (only 0).\n",
    "  \n",
    "- **Rename `_id` to `ID`:**  \n",
    "\n",
    "#### Process Data for Each Cycle (Iteration 0 to 7)\n",
    "\n",
    "For each cycle (from 0 to 7), the following operations are performed:\n",
    "\n",
    "1. **Define Cycle-Specific Column Names:**\n",
    "   - `data.{cycle}.ctrl1` → Mapped to the `IDENTIFY` column.\n",
    "   - `data.{cycle}.ctrl2` → Mapped to the `ASSESS` column.\n",
    "   - `data.{cycle}.det`   → Mapped to the `GUESS` column.\n",
    "   - `data.{cycle}.exp`   → Mapped to the `SEEK` column.\n",
    "\n",
    "2. **Create a Temporary DataFrame for the Cycle:**\n",
    "   - **Copy Essential Columns:**  \n",
    "     The `ID` and `name` columns are copied into a new DataFrame.\n",
    "   - **Add Cycle-Specific Data:**  \n",
    "     The columns `IDENTIFY`, `ASSESS`, `GUESS`, and `SEEK` are added from the corresponding cycle-specific columns in `df`.  \n",
    "\n",
    "3. **Annotate the Cycle:**  \n",
    "   An `Iteration` column is added to indicate the cycle number (cycle + 1 for human-friendly numbering).\n",
    "\n",
    "4. **Reorder Columns:**  \n",
    "   The DataFrame is rearranged to the following structure:  \n",
    "   `['ID', 'name', 'IDENTIFY', 'GUESS', 'SEEK', 'ASSESS', 'Iteration']`.\n",
    "\n",
    "#### Concatenate Cycle DataFrames\n",
    "\n",
    "- **Merge All Cycles into One DataFrame:**  \n",
    "  The cycle-specific DataFrames stored in `cycle_dfs` are concatenated into a single long-format DataFrame, `df_long`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "After reshaping to long: (4200, 7)\n"
     ]
    }
   ],
   "source": [
    "def reshape_wide_to_long(concat_df: pd.DataFrame, verbose: bool = True) -> pd.DataFrame:\n",
    "    \"\"\"\n",
    "    Reshape data from wide to long format.\n",
    "    \n",
    "    Parameters:\n",
    "        concat_df (pd.DataFrame): DataFrame in wide format.\n",
    "        verbose (bool): Whether to print status messages.\n",
    "    \n",
    "    Returns:\n",
    "        pd.DataFrame: DataFrame in long format with an added \"Iteration\" column.\n",
    "    \"\"\"\n",
    "    if \"__v\" in concat_df.columns:\n",
    "        concat_df = concat_df.drop(columns=[\"__v\"])\n",
    "    concat_df = concat_df.rename(columns={\"_id\": \"ID\"})\n",
    "    cycle_dfs = []\n",
    "    for cycle in range(8):\n",
    "        col_identify = f\"data.{cycle}.ctrl1\"  # Maps to IDENTIFY\n",
    "        col_assess = f\"data.{cycle}.ctrl2\"      # Maps to ASSESS\n",
    "        col_guess = f\"data.{cycle}.det\"         # Maps to GUESS\n",
    "        col_seek = f\"data.{cycle}.exp\"          # Maps to SEEK\n",
    "        if col_identify not in concat_df.columns:\n",
    "            continue\n",
    "        temp_df = concat_df[[\"ID\", \"name\"]].copy()\n",
    "        temp_df[\"IDENTIFY\"] = concat_df[col_identify]\n",
    "        temp_df[\"ASSESS\"] = concat_df[col_assess] if col_assess in concat_df.columns else None\n",
    "        temp_df[\"GUESS\"] = concat_df[col_guess] if col_guess in concat_df.columns else None\n",
    "        temp_df[\"SEEK\"] = concat_df[col_seek] if col_seek in concat_df.columns else None\n",
    "        temp_df[\"Iteration\"] = cycle + 1\n",
    "        temp_df = temp_df[[\"ID\", \"name\", \"IDENTIFY\", \"GUESS\", \"SEEK\", \"ASSESS\", \"Iteration\"]]\n",
    "        cycle_dfs.append(temp_df)\n",
    "    result = pd.concat(cycle_dfs, ignore_index=True)\n",
    "    if verbose:\n",
    "        print(\"After reshaping to long:\", result.shape)\n",
    "    return result\n",
    "\n",
    "long_df = reshape_wide_to_long(concat_df, verbose=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 3: Integrate Reference Texts\n",
    "\n",
    "The reference texts and cues for each step are stored in a separate file. The function `integrate_reference_data` will add the reference texts to the main dataframe, depending on the iteration, and the corresponding cues for each step."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "After integrating reference texts and cues: (4200, 12)\n"
     ]
    }
   ],
   "source": [
    "def extract_reference_data(file_path: str) -> list:\n",
    "    \"\"\"\n",
    "    Extracts a list of dictionaries from the reference file.\n",
    "    Each dictionary contains:\n",
    "      - content: the reference text.\n",
    "      - identify_cues: list of cues for IDENTIFY.\n",
    "      - guess_cues: list of cues for GUESS.\n",
    "      - seek_cues: list of cues for SEEK.\n",
    "      - assess_cues: list of cues for ASSESS.\n",
    "    \n",
    "    Parameters:\n",
    "        file_path (str): Path to the reference file.\n",
    "    \n",
    "    Returns:\n",
    "        list: A list of dictionaries with keys 'content', 'identify_cues', 'guess_cues',\n",
    "              'seek_cues', and 'assess_cues'.\n",
    "    \"\"\"\n",
    "    with open(file_path, \"r\", encoding=\"utf-8\") as f:\n",
    "        data = json.load(f)\n",
    "    \n",
    "    ref_data = []\n",
    "    for item in data:\n",
    "        content = item.get(\"content\", \"\")\n",
    "        bubbles = item.get(\"bubbles\", [])\n",
    "        # Ensure bubbles is a list with exactly 4 elements\n",
    "        while len(bubbles) < 4:\n",
    "            bubbles.append({})\n",
    "        ref_data.append({\n",
    "            \"content\": content,\n",
    "            \"identify_cues\": json.dumps(bubbles[0], ensure_ascii=False, separators=(',', ':')),\n",
    "            \"guess_cues\": json.dumps(bubbles[1], ensure_ascii=False, separators=(',', ':')),\n",
    "            \"seek_cues\": json.dumps(bubbles[2], ensure_ascii=False, separators=(',', ':')),\n",
    "            \"assess_cues\": json.dumps(bubbles[3], ensure_ascii=False, separators=(',', ':')),\n",
    "        })\n",
    "    return ref_data\n",
    "\n",
    "def integrate_reference_data(long_df: pd.DataFrame, reference_file: str, verbose: bool = True) -> pd.DataFrame:\n",
    "    \"\"\"\n",
    "    Integrate reference texts and corresponding cues into the long-format DataFrame based on the iteration number.\n",
    "    It adds the following columns:\n",
    "      - reference\n",
    "      - identify_cues\n",
    "      - guess_cues\n",
    "      - seek_cues\n",
    "      - assess_cues\n",
    "    \n",
    "    Parameters:\n",
    "        long_df (pd.DataFrame): The long-format DataFrame.\n",
    "        reference_file (str): Path to the reference file.\n",
    "        verbose (bool): Whether to print status messages.\n",
    "    \n",
    "    Returns:\n",
    "        pd.DataFrame: The updated DataFrame with new columns.\n",
    "    \"\"\"\n",
    "    ref_data = extract_reference_data(reference_file)\n",
    "    \n",
    "    def get_ref_value(iteration, key):\n",
    "        idx = int(iteration) - 1  # assume iterations are 1-indexed\n",
    "        if 0 <= idx < len(ref_data):\n",
    "            return ref_data[idx].get(key)\n",
    "        else:\n",
    "            return None\n",
    "\n",
    "    long_df = long_df.copy()\n",
    "    long_df[\"reference\"] = long_df[\"Iteration\"].apply(lambda it: get_ref_value(it, \"content\"))\n",
    "    long_df[\"identify_cues\"] = long_df[\"Iteration\"].apply(lambda it: get_ref_value(it, \"identify_cues\"))\n",
    "    long_df[\"guess_cues\"] = long_df[\"Iteration\"].apply(lambda it: get_ref_value(it, \"guess_cues\"))\n",
    "    long_df[\"seek_cues\"] = long_df[\"Iteration\"].apply(lambda it: get_ref_value(it, \"seek_cues\"))\n",
    "    long_df[\"assess_cues\"] = long_df[\"Iteration\"].apply(lambda it: get_ref_value(it, \"assess_cues\"))\n",
    "    \n",
    "    cols = [\"ID\", \"name\", \"reference\", \"IDENTIFY\", \"GUESS\", \"SEEK\", \"ASSESS\", \"Iteration\",\n",
    "            \"identify_cues\", \"guess_cues\", \"seek_cues\", \"assess_cues\"]\n",
    "    result = long_df[cols]\n",
    "    if verbose:\n",
    "        print(\"After integrating reference texts and cues:\", result.shape)\n",
    "    return result\n",
    "\n",
    "long_ref_df = integrate_reference_data(long_df, REFERENCE_FILE, verbose=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 4: Merge Sessions\n",
    "\n",
    "This section of the code merge different sessions of the same subjects together.\n",
    "\n",
    "1. **Canonicalize Subject Names:**  \n",
    "   A function `get_canonical_name` is defined to extract a base name from the subject's name, to find different sessions of a same subject.\n",
    "   - For example, names like `\"Be2\"` or `\"Be2.2\"` become `\"be2\"`, and `\"Be5bis\"` becomes `\"be5\"`.  \n",
    "\n",
    "2. **Mark Dummy Rows:**  \n",
    "   Dummy rows are identified based on the columns `[\"IDENTIFY\", \"GUESS\", \"SEEK\", \"ASSESS\"]`.  \n",
    "   - A row is considered dummy if **all** these columns are either `NaN` or a single-character string.  \n",
    "   A boolean column `is_dummy` is added to mark such rows.\n",
    "\n",
    "3. **Choose the Best Row for Each (Canonical Name, Iteration):**  \n",
    "   Normally, a second session start with dummy rows, and a first avorted session end with `NaN` rows.\n",
    "   The function `choose_best_row` is defined to select the non dummys rows accross sessions of a same subject (it try to reconstrut the complete session):\n",
    "   - It returns the first non-dummy row if available.\n",
    "   - If all rows in the group are dummy, it returns the first row.\n",
    "   The DataFrame is then grouped by `canonical_name` and `Iteration`, and this function is applied to each group. The resulting merged DataFrame is stored in `sessions_merged_df`.\n",
    "\n",
    "4. **Sort and Rename Columns:**  \n",
    "   - The merged DataFrame is sorted by `canonical_name` and `Iteration` for better organization.\n",
    "   - The original `name` column is renamed to `session_name` to preserve the original session identifier."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "STEP 4: Merging the sessions\n",
      "Shape: (2000, 14)\n",
      "Initial number of rows: 4200\n",
      "Final number of rows: 2000\n",
      "Number of rows removed: 2200\n",
      "Initial number of subjects: 321\n",
      "Final number of subjects: 250\n",
      "Number of subjects removed: 71\n"
     ]
    }
   ],
   "source": [
    "def merge_sessions(df: pd.DataFrame, verbose: bool = True) -> pd.DataFrame:\n",
    "    \"\"\"\n",
    "    Merge sessions from the same subject and print a summary of the changes.\n",
    "    \n",
    "    Parameters:\n",
    "        df (pd.DataFrame): DataFrame with subject session data.\n",
    "        verbose (bool): Whether to print detailed status messages.\n",
    "    \n",
    "    Returns:\n",
    "        pd.DataFrame: DataFrame with merged sessions and a new \"canonical_name\" column.\n",
    "    \"\"\"\n",
    "    import re\n",
    "    df = df.copy()\n",
    "    \n",
    "    # Save initial counts for logging\n",
    "    initial_rows = len(df)\n",
    "    initial_subjects = df[\"name\"].nunique()\n",
    "    \n",
    "    # Define a function for canonical subject names\n",
    "    def get_canonical_name(name: str) -> str:\n",
    "        name = str(name).strip().lower()\n",
    "        m = re.search(r\"^([a-zA-Z]+\\d+)\", name)\n",
    "        return m.group(1) if m else name\n",
    "\n",
    "    # Create a new column 'canonical_name'\n",
    "    df[\"canonical_name\"] = df[\"name\"].apply(get_canonical_name)\n",
    "    \n",
    "    # Mark dummy rows (where all key text columns are either NaN or single-character strings)\n",
    "    text_cols = [\"IDENTIFY\", \"GUESS\", \"SEEK\", \"ASSESS\"]\n",
    "    df[\"is_dummy\"] = df[text_cols].apply(\n",
    "        lambda col: col.map(lambda x: pd.isna(x) or (isinstance(x, str) and len(x) == 1))\n",
    "    ).all(axis=1)\n",
    "    \n",
    "    # Function to choose the best row from a group: first non-dummy if available, else the first row\n",
    "    def choose_best_row(group):\n",
    "        non_dummy = group[~group[\"is_dummy\"]]\n",
    "        return non_dummy.iloc[0] if not non_dummy.empty else group.iloc[0]\n",
    "    \n",
    "    # Group by (canonical_name, Iteration) and apply the row selection\n",
    "    merged_df = (\n",
    "        df.groupby([\"canonical_name\", \"Iteration\"], as_index=False)[df.columns]\n",
    "          .apply(choose_best_row)\n",
    "          .reset_index(drop=True)\n",
    "    )\n",
    "    \n",
    "    merged_df = merged_df.sort_values(by=[\"canonical_name\", \"Iteration\"]).reset_index(drop=True)\n",
    "    # Preserve the original session name by renaming 'name'\n",
    "    merged_df = merged_df.rename(columns={\"name\": \"session_name\"})\n",
    "    \n",
    "    # Compute final counts for logging\n",
    "    final_rows = len(merged_df)\n",
    "    final_subjects = merged_df[\"canonical_name\"].nunique()\n",
    "    rows_removed = initial_rows - final_rows\n",
    "    subjects_removed = initial_subjects - final_subjects\n",
    "    \n",
    "    if verbose:\n",
    "        print(\"STEP 4: Merging the sessions\")\n",
    "        print(\"Shape:\", merged_df.shape)\n",
    "        print(\"Initial number of rows:\", initial_rows)\n",
    "        print(\"Final number of rows:\", final_rows)\n",
    "        print(\"Number of rows removed:\", rows_removed)\n",
    "        print(\"Initial number of subjects:\", initial_subjects)\n",
    "        print(\"Final number of subjects:\", final_subjects)\n",
    "        print(\"Number of subjects removed:\", subjects_removed)\n",
    "    \n",
    "    return merged_df\n",
    "\n",
    "merged_df = merge_sessions(long_ref_df, verbose=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 5: Remove Sessions with All NaN Iterations\n",
    "\n",
    "This section of the code filters out sessions (identified by their `ID`) from `df_long` if all values for the columns `IDENTIFY`, `GUESS`, `SEEK`, and `ASSESS` are `NaN` across all iterations. This cleaning step ensures that sessions with no valid data in these columns are removed from further analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "STEP 5: Removing subjects with all NaN values in all iterations\n",
      "Shape: (1891, 14)\n",
      "Initial number of rows: 2000\n",
      "Final number of rows: 1891\n",
      "Number of rows removed: 109\n",
      "Initial number of subjects: 287\n",
      "Final number of subjects: 273\n",
      "Number of subjects removed: 14\n"
     ]
    }
   ],
   "source": [
    "def remove_all_nan_subjects(df: pd.DataFrame, verbose: bool = True) -> pd.DataFrame:\n",
    "    \"\"\"\n",
    "    Remove subjects where all iterations have NaN values in the key text columns.\n",
    "    \n",
    "    Parameters:\n",
    "        df (pd.DataFrame): DataFrame to process.\n",
    "        verbose (bool): Whether to print detailed status messages.\n",
    "        \n",
    "    Returns:\n",
    "        pd.DataFrame: DataFrame with subjects having all NaN iterations removed.\n",
    "    \"\"\"\n",
    "    # Define the columns to check for NaN values\n",
    "    text_cols = [\"IDENTIFY\", \"GUESS\", \"SEEK\", \"ASSESS\"]\n",
    "    \n",
    "    # Record initial counts\n",
    "    initial_rows = len(df)\n",
    "    initial_subjects = df[\"ID\"].nunique()\n",
    "    \n",
    "    # Identify groups where all values in the specified columns are NaN\n",
    "    grouped = df.groupby(\"ID\")[text_cols].apply(lambda group: group.isna().all().all())\n",
    "    \n",
    "    # Get subject IDs where all iterations are NaN\n",
    "    removed_ids = grouped[grouped].index\n",
    "    \n",
    "    # Filter out subjects that are all NaN\n",
    "    df_clean = df[~df[\"ID\"].isin(removed_ids)]\n",
    "    \n",
    "    # Record final counts\n",
    "    final_rows = len(df_clean)\n",
    "    final_subjects = df_clean[\"ID\"].nunique()\n",
    "    \n",
    "    # Compute numbers removed\n",
    "    rows_removed = initial_rows - final_rows\n",
    "    subjects_removed = initial_subjects - final_subjects\n",
    "    \n",
    "    if verbose:\n",
    "        print(\"STEP 5: Removing subjects with all NaN values in all iterations\")\n",
    "        print(\"Shape:\", df_clean.shape)\n",
    "        print(\"Initial number of rows:\", initial_rows)\n",
    "        print(\"Final number of rows:\", final_rows)\n",
    "        print(\"Number of rows removed:\", rows_removed)\n",
    "        print(\"Initial number of subjects:\", initial_subjects)\n",
    "        print(\"Final number of subjects:\", final_subjects)\n",
    "        print(\"Number of subjects removed:\", subjects_removed)\n",
    "    \n",
    "    return df_clean\n",
    "\n",
    "if RUN_NAN:\n",
    "    df_no_nan = remove_all_nan_subjects(merged_df, verbose=True)\n",
    "else:\n",
    "    df_no_nan = merged_df.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 6: Remove Dummy Rows\n",
    "\n",
    "This section removes rows that have been marked as dummy (i.e., rows where `is_dummy` is `True`). This ensures that only valid iterations are retained for further analysis. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "STEP 6: Removing dummy rows\n",
      "Shape: (1258, 14)\n",
      "Initial number of rows: 1891\n",
      "Number of rows removed: 633\n",
      "Length of DataFrame after removal: 1258\n"
     ]
    }
   ],
   "source": [
    "def remove_dummy_rows(df: pd.DataFrame, verbose: bool = True) -> pd.DataFrame:\n",
    "    \"\"\"\n",
    "    Remove rows marked as dummy and print a detailed summary.\n",
    "    \n",
    "    Parameters:\n",
    "        df (pd.DataFrame): DataFrame containing an \"is_dummy\" column.\n",
    "        verbose (bool): Whether to print status messages.\n",
    "    \n",
    "    Returns:\n",
    "        pd.DataFrame: DataFrame with dummy rows removed (keeping the 'is_dummy' column).\n",
    "    \"\"\"\n",
    "    # Record the initial number of rows\n",
    "    initial_len = len(df)\n",
    "    \n",
    "    # Remove rows marked as dummy but keep the 'is_dummy' column intact\n",
    "    df_clean = df[~df[\"is_dummy\"]].copy()\n",
    "    \n",
    "    # Calculate the number of rows removed\n",
    "    final_len = len(df_clean)\n",
    "    rows_removed = initial_len - final_len\n",
    "    \n",
    "    if verbose:\n",
    "        print(\"STEP 6: Removing dummy rows\")\n",
    "        print(\"Shape:\", df_clean.shape)\n",
    "        print(\"Initial number of rows:\", initial_len)\n",
    "        print(\"Number of rows removed:\", rows_removed)\n",
    "        print(\"Length of DataFrame after removal:\", final_len)\n",
    "    \n",
    "    return df_clean\n",
    "\n",
    "if RUN_DUMMY:\n",
    "    df_no_dummy = remove_dummy_rows(df_no_nan, verbose=True)\n",
    "else:\n",
    "    df_no_dummy = df_no_nan.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 7: Remove Fake Subjects\n",
    "\n",
    "This section filters the dataset to remove test or pilot subjects. We identify these subjects manually by checking for names that do not contain a digit, as they are more likely to be non-standard entries. However, some children may have entered their actual names or pseudonyms instead of following the conventional naming format. Therefore, we manually review the list of flagged subjects to distinguish genuine participants from test before removing them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>canonical_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>aac</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ayoub</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>bc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>bn🇹🇳</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>demo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>enplus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>fgy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>hélène</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>jade</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>kayra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>l'insu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>lesqueens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>marion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>mhs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>noémie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>paolo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>snejana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>test</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>test carole</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>tk</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>u</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>victor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>yanis</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   canonical_name\n",
       "0             aac\n",
       "1           ayoub\n",
       "2              bc\n",
       "3            bn🇹🇳\n",
       "4            demo\n",
       "5          enplus\n",
       "6             fgy\n",
       "7          hélène\n",
       "8            jade\n",
       "9           kayra\n",
       "10         l'insu\n",
       "11      lesqueens\n",
       "12         marion\n",
       "13            mhs\n",
       "14         noémie\n",
       "15          paolo\n",
       "16        snejana\n",
       "17           test\n",
       "18    test carole\n",
       "19             tk\n",
       "20              u\n",
       "21         victor\n",
       "22          yanis"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unique_names_df = pd.DataFrame(\n",
    "    df_no_dummy[~df_no_dummy['canonical_name'].str.contains(r'\\d')]['canonical_name'].unique(),\n",
    "    columns=['canonical_name']\n",
    ")\n",
    "\n",
    "unique_names_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "STEP 7: Removing fake subjects\n",
      "Shape: (1173, 14)\n",
      "Initial number of rows: 1258\n",
      "Final number of rows: 1173\n",
      "Number of rows removed: 85\n",
      "Initial number of subjects: 235\n",
      "Final number of subjects: 216\n",
      "Number of subjects removed: 19\n"
     ]
    }
   ],
   "source": [
    "def remove_fake_subjects(df: pd.DataFrame, to_keep: list = [], verbose: bool = True) -> pd.DataFrame:\n",
    "    \"\"\"\n",
    "    Remove fake subjects based on the canonical name.\n",
    "\n",
    "    Parameters:\n",
    "        df (pd.DataFrame): DataFrame with a \"canonical_name\" column.\n",
    "        to_keep (list): List of subject names to keep even if they don't contain a digit.\n",
    "        verbose (bool): Whether to print detailed status messages.\n",
    "\n",
    "    Returns:\n",
    "        pd.DataFrame: DataFrame with fake subjects removed.\n",
    "    \"\"\"\n",
    "    # Count initial unique subjects and rows\n",
    "    initial_subject_count = df[\"canonical_name\"].nunique()\n",
    "    initial_rows = len(df)\n",
    "    \n",
    "    # Keep rows where 'canonical_name' contains at least one digit OR is in the to_keep list\n",
    "    df_clean = df[\n",
    "        df[\"canonical_name\"].str.contains(r\"\\d\") | df[\"canonical_name\"].isin(to_keep)\n",
    "    ].copy()\n",
    "    \n",
    "    # Count final unique subjects and rows\n",
    "    final_subject_count = df_clean[\"canonical_name\"].nunique()\n",
    "    final_rows = len(df_clean)\n",
    "    \n",
    "    # Compute numbers removed\n",
    "    rows_removed = initial_rows - final_rows\n",
    "    subjects_removed = initial_subject_count - final_subject_count\n",
    "    \n",
    "    if verbose:\n",
    "        print(\"STEP 7: Removing fake subjects\")\n",
    "        print(\"Shape:\", df_clean.shape)\n",
    "        print(\"Initial number of rows:\", initial_rows)\n",
    "        print(\"Final number of rows:\", final_rows)\n",
    "        print(\"Number of rows removed:\", rows_removed)\n",
    "        print(\"Initial number of subjects:\", initial_subject_count)\n",
    "        print(\"Final number of subjects:\", final_subject_count)\n",
    "        print(\"Number of subjects removed:\", subjects_removed)\n",
    "    \n",
    "    return df_clean\n",
    "\n",
    "to_keep=[\"paolo\", \"tk\", \"yanis\", \"kayra\"]\n",
    "\n",
    "if RUN_FAKE:\n",
    "    df_no_fake = remove_fake_subjects(df_no_dummy, to_keep=to_keep, verbose=True)\n",
    "else:\n",
    "    df_no_fake = df_no_dummy.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 8: Remove Gibberish Rows\n",
    "\n",
    "This section analyzes text in the data columns to identify and remove rows that are likely to be gibberish and unexploitable. A row is considered gibberish if it meets any of the following criteria:\n",
    "\n",
    "1. **All Columns Are Numeric-Only:**  \n",
    "   If every value in the text columns consists solely of digits, the row is flagged as gibberish.\n",
    "\n",
    "2. **Low Vowel Count:**  \n",
    "   If the total number of vowels (including accented vowels) across the text columns is fewer than 3, the row is flagged as gibberish.\n",
    "\n",
    "3. **Minimal Word Content:**  \n",
    "   If every column contains at most one word, the row is flagged as gibberish."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "STEP 8: Removing gibberish rows\n",
      "Shape: (1054, 14)\n",
      "Initial number of rows: 1173\n",
      "Final number of rows: 1054\n",
      "Number of rows removed: 119\n"
     ]
    }
   ],
   "source": [
    "def remove_gibberish_rows(df: pd.DataFrame, verbose: bool = True) -> pd.DataFrame:\n",
    "    \"\"\"\n",
    "    Remove gibberish rows (optional step) and print a detailed summary.\n",
    "\n",
    "    Parameters:\n",
    "        df (pd.DataFrame): DataFrame to process.\n",
    "        verbose (bool): Whether to print status messages.\n",
    "\n",
    "    Returns:\n",
    "        pd.DataFrame: DataFrame with gibberish rows removed.\n",
    "    \"\"\"\n",
    "    import re\n",
    "    # Define columns for text analysis\n",
    "    TEXT_COLS = [\"IDENTIFY\", \"GUESS\", \"SEEK\", \"ASSESS\"]\n",
    "    VOWEL_PATTERN = re.compile(r\"[aeiouAEIOUàâäéèêëîïôöùûüÿç]\")\n",
    "\n",
    "    def is_numeric_only(text: str) -> bool:\n",
    "        \"\"\"Return True if the text consists only of digits.\"\"\"\n",
    "        return text.strip().isdigit()\n",
    "\n",
    "    def row_is_gibberish(row) -> bool:\n",
    "        \"\"\"\n",
    "        Determine if a row is gibberish based on the following rules:\n",
    "          1) All columns are numeric-only.\n",
    "          2) Total vowels across columns is fewer than 3.\n",
    "          3) Each column has at most one word.\n",
    "        Return True if the row is gibberish; otherwise, False.\n",
    "        \"\"\"\n",
    "        # Get the text values from the specified columns\n",
    "        texts = [str(row.get(col)) if pd.notna(row.get(col)) else \"\" for col in TEXT_COLS]\n",
    "\n",
    "        # Rule 1: Check if all columns are numeric-only\n",
    "        if all(is_numeric_only(txt) for txt in texts):\n",
    "            return True\n",
    "\n",
    "        # Rule 2: Count total vowels\n",
    "        total_vowels = sum(len(VOWEL_PATTERN.findall(txt)) for txt in texts)\n",
    "        if total_vowels < 3:\n",
    "            return True\n",
    "\n",
    "        # Rule 3: Check if each column has at most one word\n",
    "        if all(len(txt.split()) <= 1 for txt in texts):\n",
    "            return True\n",
    "\n",
    "        return False\n",
    "\n",
    "    # Record the initial number of rows\n",
    "    initial_len = len(df)\n",
    "\n",
    "    # Create a boolean column 'is_gibberish'\n",
    "    df = df.copy()\n",
    "    df[\"is_gibberish\"] = df.apply(row_is_gibberish, axis=1)\n",
    "\n",
    "    # Filter out gibberish rows and drop the 'is_gibberish' column\n",
    "    df_clean = df[~df[\"is_gibberish\"]].copy().drop(columns=[\"is_gibberish\"], errors=\"ignore\")\n",
    "\n",
    "    # Record the final number of rows and calculate the number of rows removed\n",
    "    final_len = len(df_clean)\n",
    "    rows_removed = initial_len - final_len\n",
    "\n",
    "    if verbose:\n",
    "        print(\"STEP 8: Removing gibberish rows\")\n",
    "        print(\"Shape:\", df_clean.shape)\n",
    "        print(\"Initial number of rows:\", initial_len)\n",
    "        print(\"Final number of rows:\", final_len)\n",
    "        print(\"Number of rows removed:\", rows_removed)\n",
    "\n",
    "    return df_clean\n",
    "\n",
    "if RUN_GIBBERISH:\n",
    "    df_no_gibberish = remove_gibberish_rows(df_no_fake, verbose=True)\n",
    "else:\n",
    "    df_no_gibberish = df_no_fake.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 9: Remove Subjects with Insufficient Iterations\n",
    "\n",
    "This section ensures that only subjects with a sufficient number of iterations are kept in the dataset. Subjects with fewer than 4 iterations are considered to have insufficient data (50% or less) and are removed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "STEP 9: Removing subjects with insufficient iterations (less than 4)\n",
      "Shape: (929, 14)\n",
      "Initial number of rows: 1054\n",
      "Final number of rows: 929\n",
      "Number of rows removed: 125\n",
      "Initial number of subjects: 210\n",
      "Final number of subjects: 138\n",
      "Number of subjects removed: 72\n"
     ]
    }
   ],
   "source": [
    "def remove_insufficient_data(df: pd.DataFrame, verbose: bool = True) -> pd.DataFrame:\n",
    "    \"\"\"\n",
    "    Remove subjects with insufficient iterations (fewer than 4 iterations)\n",
    "    and print a detailed summary.\n",
    "    \n",
    "    Parameters:\n",
    "        df (pd.DataFrame): DataFrame with a \"canonical_name\" column.\n",
    "        verbose (bool): Whether to print detailed status messages.\n",
    "    \n",
    "    Returns:\n",
    "        pd.DataFrame: DataFrame with subjects having insufficient iterations removed.\n",
    "    \"\"\"\n",
    "    # Count remaining iterations per subject\n",
    "    df_counts = df.groupby(\"canonical_name\")[\"Iteration\"].size().reset_index(name=\"remaining_iters\")\n",
    "    \n",
    "    # Identify subjects with fewer than 4 iterations\n",
    "    subjects_to_drop = df_counts.loc[df_counts[\"remaining_iters\"] < 4, \"canonical_name\"]\n",
    "    \n",
    "    # Record initial statistics\n",
    "    initial_rows = len(df)\n",
    "    initial_subjects = df[\"canonical_name\"].nunique()\n",
    "    \n",
    "    # Count rows that will be removed\n",
    "    rows_removed = df[df[\"canonical_name\"].isin(subjects_to_drop)].shape[0]\n",
    "    \n",
    "    # Drop subjects with insufficient iterations\n",
    "    df_clean = df[~df[\"canonical_name\"].isin(subjects_to_drop)].copy()\n",
    "    \n",
    "    # Record final statistics\n",
    "    final_rows = len(df_clean)\n",
    "    final_subjects = df_clean[\"canonical_name\"].nunique()\n",
    "    \n",
    "    if verbose:\n",
    "        print(\"STEP 9: Removing subjects with insufficient iterations (less than 4)\")\n",
    "        print(\"Shape:\", df_clean.shape)        \n",
    "        print(\"Initial number of rows:\", initial_rows)\n",
    "        print(\"Final number of rows:\", final_rows)\n",
    "        print(\"Number of rows removed:\", rows_removed)\n",
    "        print(\"Initial number of subjects:\", initial_subjects)\n",
    "        print(\"Final number of subjects:\", final_subjects)\n",
    "        print(\"Number of subjects removed:\", len(subjects_to_drop))\n",
    "    \n",
    "    return df_clean\n",
    "\n",
    "if RUN_INSUFFICIENT:\n",
    "    df_sufficient = remove_insufficient_data(df_no_gibberish, verbose=True)\n",
    "else:\n",
    "    df_sufficient = df_no_gibberish.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 10: Build Sessions Composition\n",
    "\n",
    "This section creates a new column that specify the original Id and original name from which the iteration are derivated.\n",
    "\n",
    "1. **Defining a Function to Compile Session Details:**  \n",
    "   The function `get_session_comp_from_merged` processes a group of rows to extract:\n",
    "   - The original session name (from the first occurrence in the group).\n",
    "   - All iteration numbers, extracted using a regular expression.  \n",
    "   It returns a dictionary where each key is a session ID and its value is another dictionary containing the `session_name` and a sorted list of unique iteration numbers.\n",
    "\n",
    "2. **Building a Sessions Composition Dictionary:**  \n",
    "   - The DataFrame `cleaned_df` is grouped by `canonical_name`.\n",
    "   - The function is applied to each group to build a dictionary (`sessions_comp_dict_clean`) that maps each subject (canonical name) to its session composition details.\n",
    "\n",
    "3. **Converting the Dictionary to a DataFrame:**  \n",
    "   - The dictionary is converted into a DataFrame (`sess_comp_df_clean`) with two columns:\n",
    "     - `canonical_name`: The unique subject identifier.\n",
    "     - `sessions_composition`: A dictionary containing session details.\n",
    "\n",
    "4. **Merging Session Composition Back:**  \n",
    "   - The session composition DataFrame is merged back into `cleaned_df` on the `canonical_name` column.\n",
    "\n",
    "5. **Final Adjustments:**  \n",
    "   - The column `canonical_name` is renamed to `name` for consistency.\n",
    "   - The columns are reordered to produce the final DataFrame structure."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Built session composition, final shape: (929, 12)\n"
     ]
    }
   ],
   "source": [
    "def build_session_composition(df: pd.DataFrame, verbose: bool = True) -> pd.DataFrame:\n",
    "    \"\"\"\n",
    "    Build sessions composition from the processed DataFrame.\n",
    "    \n",
    "    Parameters:\n",
    "        df (pd.DataFrame): DataFrame with merged and cleaned session data.\n",
    "        verbose (bool): Whether to print status messages.\n",
    "    \n",
    "    Returns:\n",
    "        pd.DataFrame: Final DataFrame with an added \"sessions_composition\" column.\n",
    "    \"\"\"\n",
    "    def get_session_comp(grp) -> dict:\n",
    "        id_groups = grp.groupby(\"ID\")\n",
    "        result = {}\n",
    "        for session_id, subgrp in id_groups:\n",
    "            iterations = []\n",
    "            for it in subgrp[\"Iteration\"]:\n",
    "                match = re.search(r\"\\d+\", str(it))\n",
    "                if match:\n",
    "                    iterations.append(int(match.group()))\n",
    "            # Trim extra whitespace from session_name\n",
    "            original_name = subgrp[\"session_name\"].iloc[0].strip()\n",
    "            result[session_id] = {\n",
    "                \"session_name\": original_name,\n",
    "                \"iterations\": sorted(set(iterations))\n",
    "            }\n",
    "        return result\n",
    "\n",
    "    sessions_comp = df.groupby(\"canonical_name\").apply(\n",
    "        get_session_comp, include_groups=False\n",
    "    ).to_dict()\n",
    "    sess_comp_df = pd.DataFrame(\n",
    "        list(sessions_comp.items()),\n",
    "        columns=[\"canonical_name\", \"sessions_composition\"]\n",
    "    )\n",
    "    df = df.merge(sess_comp_df, on=\"canonical_name\", how=\"left\")\n",
    "    df = df.rename(columns={\"canonical_name\": \"name\"})\n",
    "    final_cols = [\"name\", \"Iteration\", \"reference\", \"IDENTIFY\", \"GUESS\", \"SEEK\", \"ASSESS\", \n",
    "                  \"identify_cues\", \"guess_cues\", \"seek_cues\", \"assess_cues\", \"sessions_composition\"]\n",
    "    result = df[final_cols]\n",
    "\n",
    "    # Aditionnaly, trim whitespace from other text columns as well\n",
    "    result = df[final_cols].copy()  # explicitly make a copy\n",
    "    text_cols = [\"name\", \"reference\", \"IDENTIFY\", \"GUESS\", \"SEEK\", \"ASSESS\"]\n",
    "    for col in text_cols:\n",
    "        result.loc[:, col] = result[col].astype(str).str.strip()\n",
    "\n",
    "    if verbose:\n",
    "        print(\"Built session composition, final shape:\", result.shape)\n",
    "    return result\n",
    "\n",
    "final_df = build_session_composition(df_sufficient, verbose=True)\n",
    "final_df = final_df.sort_values(by=[\"name\", \"Iteration\"]).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save Final DataFrame to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Final row count: 929\n",
      "Unique subjects: 138\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>Iteration</th>\n",
       "      <th>reference</th>\n",
       "      <th>IDENTIFY</th>\n",
       "      <th>GUESS</th>\n",
       "      <th>SEEK</th>\n",
       "      <th>ASSESS</th>\n",
       "      <th>identify_cues</th>\n",
       "      <th>guess_cues</th>\n",
       "      <th>seek_cues</th>\n",
       "      <th>assess_cues</th>\n",
       "      <th>sessions_composition</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>aac13</td>\n",
       "      <td>3</td>\n",
       "      <td>Toutankhamon était un pharaon, un roi de l'Egy...</td>\n",
       "      <td>L'Egypte antique</td>\n",
       "      <td>C'est un ancien pays de l'Afrique 💸</td>\n",
       "      <td>Qu'est ce que l'Égypte antique</td>\n",
       "      <td>Qui</td>\n",
       "      <td>{\"1\":\"L'Egypte antique\",\"2\":\"Le principe de mo...</td>\n",
       "      <td>{\"1\":\"C'est un ancien pays de l'Afrique\",\"2\":\"...</td>\n",
       "      <td>{\"1\":\"Qu'est ce que l'Egypte Antique ?\",\"2\":\"Q...</td>\n",
       "      <td>{\"1\":\"L'Egypte antique est une ancienne civili...</td>\n",
       "      <td>{'637cc9f3467dbb2195558eb5': {'session_name': ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>aac13</td>\n",
       "      <td>4</td>\n",
       "      <td>La très grande majorité de l’or disponible dan...</td>\n",
       "      <td>Les composants électriques</td>\n",
       "      <td>Les composants électroniques sont par exemple ...</td>\n",
       "      <td>Qu'est-ce qu'une composants électroniques</td>\n",
       "      <td>Non</td>\n",
       "      <td>{\"1\":\"Utilité de l'or pour les couronnes denta...</td>\n",
       "      <td>{\"1\":\"Les couronnes en or sont plus solides et...</td>\n",
       "      <td>{\"1\":\"Pourquoi fait-on des couronnes dentaires...</td>\n",
       "      <td>{\"4\":\"Les couronnes en or sont plus chères\",\"2...</td>\n",
       "      <td>{'637cc9f3467dbb2195558eb5': {'session_name': ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>aac13</td>\n",
       "      <td>5</td>\n",
       "      <td>Des scientifiques ont révélé l’existence de tr...</td>\n",
       "      <td>Des premiers humains l'Australie</td>\n",
       "      <td>C'est un pays du Sud</td>\n",
       "      <td>Où se trouve l'Australie</td>\n",
       "      <td>Oui</td>\n",
       "      <td>{\"1\":\"La formation des traces dans les roches\"...</td>\n",
       "      <td>{\"1\":\"Ces traces se forment automatiquement qu...</td>\n",
       "      <td>{\"1\":\"Comment les êtres vivants laissent-ils d...</td>\n",
       "      <td>{\"4\":\"Seule une toute petite proportion des or...</td>\n",
       "      <td>{'637cc9f3467dbb2195558eb5': {'session_name': ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>aac13</td>\n",
       "      <td>6</td>\n",
       "      <td>La religion de la Grèce antique comprend plusi...</td>\n",
       "      <td>Une mythologie</td>\n",
       "      <td>L'olympe est un endroit en Grèce</td>\n",
       "      <td>Qu'est ce qu'une mythologie</td>\n",
       "      <td>Non</td>\n",
       "      <td>{\"1\":\"Une mythologie\",\"2\":\"Les autres mytholog...</td>\n",
       "      <td>{\"1\":\"Une mythologie est un ensemble de contes...</td>\n",
       "      <td>{\"1\":\"Qu'est ce qu'une mythologie ?\",\"2\":\"Quel...</td>\n",
       "      <td>{\"1\":\"Une mythologie est un ensemble de légend...</td>\n",
       "      <td>{'637cc9f3467dbb2195558eb5': {'session_name': ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>aac24</td>\n",
       "      <td>3</td>\n",
       "      <td>Toutankhamon était un pharaon, un roi de l'Egy...</td>\n",
       "      <td>Toutânkhamon</td>\n",
       "      <td>Roi Pharaon</td>\n",
       "      <td>Quand les Pharaons sont-ils apparus</td>\n",
       "      <td>J'ai trouvé ma réponse</td>\n",
       "      <td>{\"1\":\"L'Egypte antique\",\"2\":\"Le principe de mo...</td>\n",
       "      <td>{\"1\":\"C'est un ancien pays de l'Afrique\",\"2\":\"...</td>\n",
       "      <td>{\"1\":\"Qu'est ce que l'Egypte Antique ?\",\"2\":\"Q...</td>\n",
       "      <td>{\"1\":\"L'Egypte antique est une ancienne civili...</td>\n",
       "      <td>{'637cc949467dbb2195558e9d': {'session_name': ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  Iteration                                          reference  \\\n",
       "0  aac13          3  Toutankhamon était un pharaon, un roi de l'Egy...   \n",
       "1  aac13          4  La très grande majorité de l’or disponible dan...   \n",
       "2  aac13          5  Des scientifiques ont révélé l’existence de tr...   \n",
       "3  aac13          6  La religion de la Grèce antique comprend plusi...   \n",
       "4  aac24          3  Toutankhamon était un pharaon, un roi de l'Egy...   \n",
       "\n",
       "                           IDENTIFY  \\\n",
       "0                  L'Egypte antique   \n",
       "1        Les composants électriques   \n",
       "2  Des premiers humains l'Australie   \n",
       "3                    Une mythologie   \n",
       "4                      Toutânkhamon   \n",
       "\n",
       "                                               GUESS  \\\n",
       "0                C'est un ancien pays de l'Afrique 💸   \n",
       "1  Les composants électroniques sont par exemple ...   \n",
       "2                               C'est un pays du Sud   \n",
       "3                   L'olympe est un endroit en Grèce   \n",
       "4                                        Roi Pharaon   \n",
       "\n",
       "                                        SEEK                  ASSESS  \\\n",
       "0             Qu'est ce que l'Égypte antique                     Qui   \n",
       "1  Qu'est-ce qu'une composants électroniques                     Non   \n",
       "2                   Où se trouve l'Australie                     Oui   \n",
       "3                Qu'est ce qu'une mythologie                     Non   \n",
       "4        Quand les Pharaons sont-ils apparus  J'ai trouvé ma réponse   \n",
       "\n",
       "                                       identify_cues  \\\n",
       "0  {\"1\":\"L'Egypte antique\",\"2\":\"Le principe de mo...   \n",
       "1  {\"1\":\"Utilité de l'or pour les couronnes denta...   \n",
       "2  {\"1\":\"La formation des traces dans les roches\"...   \n",
       "3  {\"1\":\"Une mythologie\",\"2\":\"Les autres mytholog...   \n",
       "4  {\"1\":\"L'Egypte antique\",\"2\":\"Le principe de mo...   \n",
       "\n",
       "                                          guess_cues  \\\n",
       "0  {\"1\":\"C'est un ancien pays de l'Afrique\",\"2\":\"...   \n",
       "1  {\"1\":\"Les couronnes en or sont plus solides et...   \n",
       "2  {\"1\":\"Ces traces se forment automatiquement qu...   \n",
       "3  {\"1\":\"Une mythologie est un ensemble de contes...   \n",
       "4  {\"1\":\"C'est un ancien pays de l'Afrique\",\"2\":\"...   \n",
       "\n",
       "                                           seek_cues  \\\n",
       "0  {\"1\":\"Qu'est ce que l'Egypte Antique ?\",\"2\":\"Q...   \n",
       "1  {\"1\":\"Pourquoi fait-on des couronnes dentaires...   \n",
       "2  {\"1\":\"Comment les êtres vivants laissent-ils d...   \n",
       "3  {\"1\":\"Qu'est ce qu'une mythologie ?\",\"2\":\"Quel...   \n",
       "4  {\"1\":\"Qu'est ce que l'Egypte Antique ?\",\"2\":\"Q...   \n",
       "\n",
       "                                         assess_cues  \\\n",
       "0  {\"1\":\"L'Egypte antique est une ancienne civili...   \n",
       "1  {\"4\":\"Les couronnes en or sont plus chères\",\"2...   \n",
       "2  {\"4\":\"Seule une toute petite proportion des or...   \n",
       "3  {\"1\":\"Une mythologie est un ensemble de légend...   \n",
       "4  {\"1\":\"L'Egypte antique est une ancienne civili...   \n",
       "\n",
       "                                sessions_composition  \n",
       "0  {'637cc9f3467dbb2195558eb5': {'session_name': ...  \n",
       "1  {'637cc9f3467dbb2195558eb5': {'session_name': ...  \n",
       "2  {'637cc9f3467dbb2195558eb5': {'session_name': ...  \n",
       "3  {'637cc9f3467dbb2195558eb5': {'session_name': ...  \n",
       "4  {'637cc949467dbb2195558e9d': {'session_name': ...  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df.to_csv(OUTPUT_FILE, sep=\";\", index=False, encoding=\"utf-8-sig\")\n",
    "print(\"Final row count:\", len(final_df))\n",
    "print(\"Unique subjects:\", final_df[\"name\"].nunique())\n",
    "final_df.head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "gpt_rl",
   "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.11.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}