{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Lab 02 · Task 3 — Independent EDA and Cleaning · SOLUTIONS\n",
    "\n",
    "**Dataset:** `dataset_D_git_classroom_activity.csv`\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part 1 — Load and Inspect"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sweetviz as sv\n",
    "import dtale\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Shape: (10000, 23)\n",
      "\n",
      "Column types:\n",
      "event_id                  object\n",
      "user_id                   object\n",
      "repo_id                   object\n",
      "timestamp                 object\n",
      "event_type                object\n",
      "lines_added                int64\n",
      "lines_deleted              int64\n",
      "files_changed              int64\n",
      "dominant_language         object\n",
      "ci_status                 object\n",
      "coverage_percent          object\n",
      "editor                    object\n",
      "os                        object\n",
      "time_to_ci_minutes       float64\n",
      "build_duration_s         float64\n",
      "tests_run                  int64\n",
      "tests_failed               int64\n",
      "is_weekend                object\n",
      "pr_merge_time_hours      float64\n",
      "label_is_high_quality     object\n",
      "exam_period               object\n",
      "commit_message_length    float64\n",
      "is_bot_user               object\n",
      "dtype: object\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>event_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>repo_id</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>event_type</th>\n",
       "      <th>lines_added</th>\n",
       "      <th>lines_deleted</th>\n",
       "      <th>files_changed</th>\n",
       "      <th>dominant_language</th>\n",
       "      <th>ci_status</th>\n",
       "      <th>...</th>\n",
       "      <th>time_to_ci_minutes</th>\n",
       "      <th>build_duration_s</th>\n",
       "      <th>tests_run</th>\n",
       "      <th>tests_failed</th>\n",
       "      <th>is_weekend</th>\n",
       "      <th>pr_merge_time_hours</th>\n",
       "      <th>label_is_high_quality</th>\n",
       "      <th>exam_period</th>\n",
       "      <th>commit_message_length</th>\n",
       "      <th>is_bot_user</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>evt_d351e59b15fd</td>\n",
       "      <td>user_2432</td>\n",
       "      <td>repo_575</td>\n",
       "      <td>29/03/2025 17:26</td>\n",
       "      <td>pr_opened</td>\n",
       "      <td>40</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>Python</td>\n",
       "      <td>SUCCESS</td>\n",
       "      <td>...</td>\n",
       "      <td>13.38</td>\n",
       "      <td>493.98</td>\n",
       "      <td>115</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>54.6</td>\n",
       "      <td>0</td>\n",
       "      <td>true</td>\n",
       "      <td>39.0</td>\n",
       "      <td>HUMAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>evt_435c1b33622f</td>\n",
       "      <td>user_2017</td>\n",
       "      <td>repo_1112</td>\n",
       "      <td>01/07/2025 12:20</td>\n",
       "      <td>Commit</td>\n",
       "      <td>3</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>GO</td>\n",
       "      <td>FAILED</td>\n",
       "      <td>...</td>\n",
       "      <td>16.86</td>\n",
       "      <td>107.57</td>\n",
       "      <td>90</td>\n",
       "      <td>14</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>No</td>\n",
       "      <td>65.0</td>\n",
       "      <td>Bot</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>evt_758099c90286</td>\n",
       "      <td>user_930</td>\n",
       "      <td>repo_103</td>\n",
       "      <td>2025-01-30 02:26:34+00:00</td>\n",
       "      <td>pr_merged</td>\n",
       "      <td>13</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>Rust</td>\n",
       "      <td>failed</td>\n",
       "      <td>...</td>\n",
       "      <td>448.32</td>\n",
       "      <td>193.38</td>\n",
       "      <td>92</td>\n",
       "      <td>6</td>\n",
       "      <td>No</td>\n",
       "      <td>68.6</td>\n",
       "      <td>False</td>\n",
       "      <td>false</td>\n",
       "      <td>79.0</td>\n",
       "      <td>Human</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>evt_312809052420</td>\n",
       "      <td>user_1892</td>\n",
       "      <td>repo_988</td>\n",
       "      <td>2025-03-21 08:01:25-05:00</td>\n",
       "      <td>pr_opened</td>\n",
       "      <td>28</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>C++</td>\n",
       "      <td>SUCCESS</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>498.92</td>\n",
       "      <td>177</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>50.6</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BOT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>evt_0b2d75d29ec3</td>\n",
       "      <td>user_2793</td>\n",
       "      <td>repo_419</td>\n",
       "      <td>2025-02-28 18:22:51-05:00</td>\n",
       "      <td>Review_comment</td>\n",
       "      <td>79</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>C++</td>\n",
       "      <td>failure</td>\n",
       "      <td>...</td>\n",
       "      <td>1.14</td>\n",
       "      <td>162.55</td>\n",
       "      <td>113</td>\n",
       "      <td>9</td>\n",
       "      <td>false</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>false</td>\n",
       "      <td>48.0</td>\n",
       "      <td>human</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           event_id    user_id    repo_id                  timestamp  \\\n",
       "0  evt_d351e59b15fd  user_2432   repo_575           29/03/2025 17:26   \n",
       "1  evt_435c1b33622f  user_2017  repo_1112           01/07/2025 12:20   \n",
       "2  evt_758099c90286   user_930   repo_103  2025-01-30 02:26:34+00:00   \n",
       "3  evt_312809052420  user_1892   repo_988  2025-03-21 08:01:25-05:00   \n",
       "4  evt_0b2d75d29ec3  user_2793   repo_419  2025-02-28 18:22:51-05:00   \n",
       "\n",
       "         event_type  lines_added  lines_deleted  files_changed  \\\n",
       "0         pr_opened           40              2              3   \n",
       "1            Commit            3             24              2   \n",
       "2         pr_merged           13             12             11   \n",
       "3         pr_opened           28              6              3   \n",
       "4   Review_comment            79              2              3   \n",
       "\n",
       "  dominant_language ci_status  ... time_to_ci_minutes build_duration_s  \\\n",
       "0            Python   SUCCESS  ...              13.38           493.98   \n",
       "1                GO    FAILED  ...              16.86           107.57   \n",
       "2              Rust    failed  ...             448.32           193.38   \n",
       "3              C++    SUCCESS  ...                NaN           498.92   \n",
       "4               C++   failure  ...               1.14           162.55   \n",
       "\n",
       "  tests_run  tests_failed  is_weekend  pr_merge_time_hours  \\\n",
       "0       115             8           1                 54.6   \n",
       "1        90            14       False                  NaN   \n",
       "2        92             6          No                 68.6   \n",
       "3       177            12           0                 50.6   \n",
       "4       113             9       false                  NaN   \n",
       "\n",
       "   label_is_high_quality exam_period  commit_message_length is_bot_user  \n",
       "0                      0        true                   39.0      HUMAN   \n",
       "1                      0          No                   65.0        Bot   \n",
       "2                  False       false                   79.0       Human  \n",
       "3                     No          No                    NaN         BOT  \n",
       "4                  False       false                   48.0       human  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('dataset_D_git_classroom_activity.csv')\n",
    "\n",
    "print(f'Shape: {df.shape}')\n",
    "print('\\nColumn types:')\n",
    "print(df.dtypes)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> **What to note:**\n",
    "> - `coverage_percent` should be numeric but is `object` — formatting problem in raw values\n",
    "> - `is_weekend`, `label_is_high_quality`, `exam_period` should be boolean but are `object`\n",
    "> - `commit_message_length` is `float64` rather than `int` — a sign that missing values forced a float type\n",
    "\n",
    "---\n",
    "\n",
    "## Part 2 — Automated Profiling with SweetViz"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "6f266d5ddd784391837b6e25c61dc1ea",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "                                             |          | [  0%]   00:00 -> (? left)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Report sweetviz_git_raw.html was generated.\n",
      "Report saved. Open sweetviz_git_raw.html in your browser.\n"
     ]
    }
   ],
   "source": [
    "report = sv.analyze(df)\n",
    "report.show_html('sweetviz_git_raw.html', open_browser=False)\n",
    "print('Report saved. Open sweetviz_git_raw.html in your browser.')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Triage checklist — answers\n",
    "\n",
    "| Question | Finding |\n",
    "|---|---|\n",
    "| Which columns have missing values? Which has the most? | `pr_merge_time_hours` (71.7%), `commit_message_length` (7%), `build_duration_s` (2.1%), `time_to_ci_minutes` (2%) |\n",
    "| Which columns should be boolean? | `is_weekend`, `label_is_high_quality`, `exam_period` |\n",
    "| Which columns should be numeric? | `coverage_percent` — shown as TEXT due to comma decimal separators |\n",
    "| `event_type` distinct count | ~42 — should be 7; case/whitespace variants |\n",
    "| What is unusual about `ci_status`? | Besides case/whitespace variants, `FAILED` and `FAILURE` are synonyms that need merging |\n",
    "| Suspicious numeric ranges | `lines_added` max 5000, `time_to_ci_minutes` max 1578, `pr_merge_time_hours` has negative values |\n",
    "\n",
    "---\n",
    "\n",
    "## Part 3 — Navigate and Inspect with D-Tale"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "D-Tale running at: http://127.0.0.1:40001\n"
     ]
    }
   ],
   "source": [
    "d = dtale.show(df, host='127.0.0.1', subprocess=True, open_browser=True)\n",
    "print('D-Tale running at:', d._url)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## Part 4 — Clean with Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_clean = df.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "### 4.1 — Boolean columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['0', '1', 'False', 'No', 'True', 'Yes', 'false', 'true']\n"
     ]
    }
   ],
   "source": [
    "# Inspect\n",
    "print(sorted(df_clean['is_weekend'].dropna().unique().tolist()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Boolean keys included so the mapping is safe to re-run\n",
    "bool_map = {\n",
    "    'True': True,  'true': True,  '1': True,  'Yes': True,  True: True,\n",
    "    'False': False, 'false': False, '0': False, 'No': False, False: False\n",
    "}\n",
    "\n",
    "for col in ['is_weekend', 'label_is_high_quality', 'exam_period']:\n",
    "    df_clean[col] = df_clean[col].map(bool_map)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "is_weekend: {False: 7137, True: 2863} | nulls: 0\n",
      "label_is_high_quality: {False: 9260, True: 740} | nulls: 0\n",
      "exam_period: {False: 8092, True: 1908} | nulls: 0\n"
     ]
    }
   ],
   "source": [
    "# Verify\n",
    "for col in ['is_weekend', 'label_is_high_quality', 'exam_period']:\n",
    "    print(f\"{col}: {df_clean[col].value_counts().to_dict()} | nulls: {df_clean[col].isna().sum()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "### 4.2 — `is_bot_user`: case and whitespace"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "is_bot_user\n",
      "human      3209\n",
      "Human      3132\n",
      " HUMAN     3075\n",
      "bot         209\n",
      "BOT         200\n",
      " Bot        175\n"
     ]
    }
   ],
   "source": [
    "# Inspect\n",
    "print(df_clean['is_bot_user'].value_counts().to_string())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_clean['is_bot_user'] = df_clean['is_bot_user'].str.strip().str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "is_bot_user\n",
      "human    9416\n",
      "bot       584\n",
      "Name: count, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Verify\n",
    "print(df_clean['is_bot_user'].value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "### 4.3 — Categorical columns: case and whitespace"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "dominant_language unique before: 36\n"
     ]
    }
   ],
   "source": [
    "# Inspect\n",
    "print(f'dominant_language unique before: {df_clean[\"dominant_language\"].nunique()}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Strip and lowercase for columns with pure case/whitespace variance\n",
    "for col in ['dominant_language', 'editor', 'event_type']:\n",
    "    df_clean[col] = df_clean[col].str.strip().str.lower()\n",
    "\n",
    "# os: strip and lowercase, then merge win → windows\n",
    "df_clean['os'] = (\n",
    "    df_clean['os']\n",
    "    .str.strip()\n",
    "    .str.lower()\n",
    "    .replace({'win': 'windows'})\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "dominant_language (6 unique): ['c++', 'go', 'java', 'javascript', 'python', 'rust']\n",
      "editor (5 unique): ['intellij', 'nano', 'sublime', 'vim', 'vscode']\n",
      "os (3 unique): ['linux', 'macos', 'windows']\n",
      "event_type (7 unique): ['ci_run', 'commit', 'pr_merged', 'pr_opened', 'push', 'review_comment', 'test_run']\n"
     ]
    }
   ],
   "source": [
    "# Verify\n",
    "for col in ['dominant_language', 'editor', 'os', 'event_type']:\n",
    "    print(f\"{col} ({df_clean[col].nunique()} unique): {sorted(df_clean[col].dropna().unique().tolist())}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "### 4.4 — `ci_status`: case, whitespace, and synonym merging"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ci_status\n",
      "SUCCESS        1586\n",
      "success        1505\n",
      "failure        1189\n",
      "FAILED         1168\n",
      "cancelled      1142\n",
      "Success         507\n",
      "CANCELLED       383\n",
      "FAILURE         367\n",
      "failed          359\n",
      "Failed          281\n",
      "Failure         263\n",
      "Cancelled       238\n",
      " SUCCESS        178\n",
      " success        167\n",
      " FAILED         141\n",
      " failure        133\n",
      " cancelled      129\n",
      " Success         55\n",
      " failed          45\n",
      " CANCELLED       42\n",
      " FAILURE         37\n",
      " Failed          33\n",
      " Cancelled       32\n",
      " Failure         20\n"
     ]
    }
   ],
   "source": [
    "# Inspect\n",
    "print(df_clean['ci_status'].value_counts().to_string())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Step 1: strip and lowercase\n",
    "# Step 2: merge 'failure' into 'failed'\n",
    "# Rationale: both indicate the CI pipeline did not complete successfully.\n",
    "# 'failed' is the more common and explicit term in CI tooling (GitHub Actions, Jenkins).\n",
    "df_clean['ci_status'] = (\n",
    "    df_clean['ci_status']\n",
    "    .str.strip()\n",
    "    .str.lower()\n",
    "    .replace({'failure': 'failed'})\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ci_status\n",
      "failed       4036\n",
      "success      3998\n",
      "cancelled    1966\n",
      "Name: count, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Verify\n",
    "print(df_clean['ci_status'].value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> **Decision:** `failure` → `failed`. Both mean the CI pipeline did not complete successfully. `failed` is the canonical term used by major CI tools (GitHub Actions, Jenkins, GitLab CI) and is more explicit.\n",
    "\n",
    "---\n",
    "\n",
    "### 4.5 — `coverage_percent`: comma decimal separator, type conversion, and outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "dtype: object\n",
      "Rows with comma: 710\n"
     ]
    }
   ],
   "source": [
    "# Inspect\n",
    "print(f'dtype: {df_clean[\"coverage_percent\"].dtype}')\n",
    "comma_rows = df_clean['coverage_percent'].astype(str).str.contains(',', na=False)\n",
    "print(f'Rows with comma: {comma_rows.sum()}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fix: replace comma, convert to float\n",
    "df_clean['coverage_percent'] = (\n",
    "    df_clean['coverage_percent']\n",
    "    .astype(str)\n",
    "    .str.replace(',', '.', regex=False)\n",
    "    .replace('nan', float('nan'))\n",
    "    .astype(float)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "dtype: float64\n",
      "count    10000.00\n",
      "mean        74.33\n",
      "std         17.49\n",
      "min        -19.67\n",
      "25%         64.70\n",
      "50%         75.10\n",
      "75%         85.50\n",
      "max        130.73\n",
      "Name: coverage_percent, dtype: float64\n",
      "\n",
      "Values < 0:   105 rows\n",
      "Values > 100: 97 rows\n"
     ]
    }
   ],
   "source": [
    "# Verify — also check for values outside the valid 0-100 range\n",
    "print(f'dtype: {df_clean[\"coverage_percent\"].dtype}')\n",
    "print(df_clean['coverage_percent'].describe().round(2))\n",
    "print(f'\\nValues < 0:   {(df_clean[\"coverage_percent\"] < 0).sum()} rows')\n",
    "print(f'Values > 100: {(df_clean[\"coverage_percent\"] > 100).sum()} rows')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Invalid coverage values set to NaN: 202\n",
      "Range after: 14.2 – 100.0\n"
     ]
    }
   ],
   "source": [
    "# coverage_percent must be in [0, 100] — values outside this range are logging errors\n",
    "invalid_cov = (df_clean['coverage_percent'] < 0) | (df_clean['coverage_percent'] > 100)\n",
    "df_clean.loc[invalid_cov, 'coverage_percent'] = float('nan')\n",
    "print(f'Invalid coverage values set to NaN: {invalid_cov.sum()}')\n",
    "print(f'Range after: {df_clean[\"coverage_percent\"].min():.1f} – {df_clean[\"coverage_percent\"].max():.1f}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "### 4.6 — Missing values: decisions and strategy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>missing</th>\n",
       "      <th>%</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>coverage_percent</th>\n",
       "      <td>202</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time_to_ci_minutes</th>\n",
       "      <td>202</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>build_duration_s</th>\n",
       "      <td>214</td>\n",
       "      <td>2.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pr_merge_time_hours</th>\n",
       "      <td>7168</td>\n",
       "      <td>71.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>commit_message_length</th>\n",
       "      <td>696</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       missing     %\n",
       "coverage_percent           202   2.0\n",
       "time_to_ci_minutes         202   2.0\n",
       "build_duration_s           214   2.1\n",
       "pr_merge_time_hours       7168  71.7\n",
       "commit_message_length      696   7.0"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Inspect missing counts\n",
    "missing = df_clean.isnull().sum()\n",
    "pct = (missing / len(df_clean) * 100).round(1)\n",
    "pd.DataFrame({'missing': missing, '%': pct})[missing > 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "event_type\n",
      "pr_merged    1418\n",
      "pr_opened    1414\n",
      "Name: count, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Investigate pr_merge_time_hours — which event types have non-null values?\n",
    "print(df_clean.loc[df_clean['pr_merge_time_hours'].notna(), 'event_type'].value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> **Finding:** `pr_merge_time_hours` is only non-null for `pr_merged` and `pr_opened` events — exactly the rows where a merge time is meaningful. This is **structural missingness (MNAR — Missing Not At Random)**, not a data quality problem. Imputing or dropping these rows would destroy valid analytical signal. Keep as NaN.\n",
    "\n",
    "| Column | Decision | Rationale |\n",
    "|---|---|---|\n",
    "| `pr_merge_time_hours` | Keep NaN | Structural: only meaningful for PR events |\n",
    "| `commit_message_length` | Keep NaN | Unclear cause — may be bot commits or merge commits without messages |\n",
    "| `build_duration_s` | Keep NaN | Sporadic; likely CI jobs that did not reach the build phase |\n",
    "| `time_to_ci_minutes` | Keep NaN | Sporadic; likely events that did not trigger CI |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Missing value strategy: all four columns kept as NaN.\n",
      "No rows dropped.\n"
     ]
    }
   ],
   "source": [
    "# All four columns: leave as NaN — no action needed\n",
    "# (Documented above)\n",
    "print('Missing value strategy: all four columns kept as NaN.')\n",
    "print('No rows dropped.')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "### 4.7 — Outliers and impossible values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Negative pr_merge_time_hours: 38\n",
      "     event_type  pr_merge_time_hours\n",
      "46    pr_merged            -9.027020\n",
      "124   pr_opened            -8.308229\n",
      "895   pr_merged            -3.894638\n",
      "924   pr_opened            -1.362144\n",
      "1384  pr_merged            -3.945483\n"
     ]
    }
   ],
   "source": [
    "# A — Negative pr_merge_time_hours\n",
    "neg_mask = df_clean['pr_merge_time_hours'] < 0\n",
    "print(f'Negative pr_merge_time_hours: {neg_mask.sum()}')\n",
    "print(df_clean.loc[neg_mask, ['event_type', 'pr_merge_time_hours']].head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Negative values set to NaN: 38\n",
      "New min: 0.00\n"
     ]
    }
   ],
   "source": [
    "# Fix A\n",
    "df_clean.loc[neg_mask, 'pr_merge_time_hours'] = float('nan')\n",
    "print(f'Negative values set to NaN: {neg_mask.sum()}')\n",
    "print(f'New min: {df_clean[\"pr_merge_time_hours\"].min():.2f}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Rows where tests_failed > tests_run: 231\n",
      "       tests_run  tests_failed\n",
      "count      231.0         231.0\n",
      "mean       100.3         115.0\n",
      "std         54.4          57.5\n",
      "min          0.0           2.0\n",
      "25%         74.5          84.0\n",
      "50%        111.0         126.0\n",
      "75%        138.0         152.5\n",
      "max        216.0         245.0\n"
     ]
    }
   ],
   "source": [
    "# B — tests_failed > tests_run (cross-column logical check)\n",
    "impossible_mask = df_clean['tests_failed'] > df_clean['tests_run']\n",
    "print(f'Rows where tests_failed > tests_run: {impossible_mask.sum()}')\n",
    "print(df_clean.loc[impossible_mask, ['tests_run', 'tests_failed']].describe().round(1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "tests_failed set to NaN: 231\n",
      "Remaining impossible rows: 0\n"
     ]
    }
   ],
   "source": [
    "# Fix B — set tests_failed to NaN for impossible rows\n",
    "# We do not touch tests_run — it may be correct; tests_failed is the unreliable value\n",
    "df_clean.loc[impossible_mask, 'tests_failed'] = float('nan')\n",
    "print(f'tests_failed set to NaN: {impossible_mask.sum()}')\n",
    "# Verify: no remaining impossible rows\n",
    "remaining = df_clean['tests_failed'] > df_clean['tests_run']\n",
    "print(f'Remaining impossible rows: {remaining.sum()}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "lines_added distribution:\n",
      "count    10000.0\n",
      "mean        47.0\n",
      "std        368.9\n",
      "min          0.0\n",
      "25%          5.0\n",
      "50%         13.0\n",
      "75%         27.0\n",
      "max       5000.0\n",
      "Name: lines_added, dtype: float64\n",
      "\n",
      "Rows > 1000 lines added: 55\n",
      "    event_type  lines_added  lines_deleted dominant_language\n",
      "319  pr_opened         5000              5                go\n",
      "364  pr_opened         5000             10                go\n",
      "487     commit         5000              4              java\n",
      "511  pr_merged         5000             20              java\n",
      "657  pr_merged         5000              1            python\n",
      "744  pr_merged         5000              7            python\n",
      "790  pr_opened         5000              7              rust\n",
      "870  pr_opened         5000              2                go\n"
     ]
    }
   ],
   "source": [
    "# C — lines_added and lines_deleted outliers\n",
    "print('lines_added distribution:')\n",
    "print(df_clean['lines_added'].describe().round(1))\n",
    "print(f'\\nRows > 1000 lines added: {(df_clean[\"lines_added\"] > 1000).sum()}')\n",
    "print(df_clean.loc[df_clean['lines_added'] > 1000,\n",
    "                   ['event_type', 'lines_added', 'lines_deleted', 'dominant_language']].head(8).to_string())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "lines_added outliers set to NaN: 55\n",
      "lines_deleted outliers set to NaN: 64\n",
      "\n",
      "lines_added max after: 196.0\n",
      "lines_deleted max after: 138.0\n"
     ]
    }
   ],
   "source": [
    "# Decision: commits adding or deleting > 1000 lines are flagged as outliers.\n",
    "# While large commits can be legitimate (adding a framework, vendoring dependencies),\n",
    "# values of 5000 lines are extreme for a classroom context and likely logging errors.\n",
    "# We set them to NaN rather than dropping — other columns in these rows remain valid.\n",
    "threshold = 1000\n",
    "large_add = df_clean['lines_added'] > threshold\n",
    "large_del = df_clean['lines_deleted'] > threshold\n",
    "\n",
    "df_clean.loc[large_add, 'lines_added'] = float('nan')\n",
    "df_clean.loc[large_del, 'lines_deleted'] = float('nan')\n",
    "\n",
    "print(f'lines_added outliers set to NaN: {large_add.sum()}')\n",
    "print(f'lines_deleted outliers set to NaN: {large_del.sum()}')\n",
    "print(f'\\nlines_added max after: {df_clean[\"lines_added\"].max()}')\n",
    "print(f'lines_deleted max after: {df_clean[\"lines_deleted\"].max()}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "### 4.8 — `timestamp`: mixed datetime formats *(optional)*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "timestamp dtype: datetime64[ns, UTC]\n",
      "Unparsed (NaT) after first pass: 6838\n"
     ]
    }
   ],
   "source": [
    "# First pass — handles ISO 8601 formats\n",
    "df_clean['timestamp'] = pd.to_datetime(df_clean['timestamp'], utc=True, errors='coerce')\n",
    "print(f'timestamp dtype: {df_clean[\"timestamp\"].dtype}')\n",
    "print(f'Unparsed (NaT) after first pass: {df_clean[\"timestamp\"].isna().sum()}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Resolved in second pass: 1816\n",
      "Still NaT (truly ambiguous): 5022\n"
     ]
    }
   ],
   "source": [
    "# The remaining NaTs are DD/MM/YYYY format rows — apply a second pass\n",
    "# using the systematic try_formats approach from Task 2\n",
    "\n",
    "def try_formats(series, formats):\n",
    "    result = pd.Series(pd.NaT, index=series.index, dtype='datetime64[ns, UTC]')\n",
    "    remaining = series.copy()\n",
    "    for fmt in formats:\n",
    "        parsed = pd.to_datetime(remaining, format=fmt, errors='coerce', utc=True)\n",
    "        resolved_idx = parsed.index[parsed.notna()]\n",
    "        result.loc[resolved_idx] = parsed.loc[resolved_idx]\n",
    "        remaining = remaining.drop(index=resolved_idx)\n",
    "    return result\n",
    "\n",
    "candidate_formats = [\n",
    "    '%d/%m/%Y %H:%M',\n",
    "    '%m/%d/%Y %H:%M',\n",
    "    '%d/%m/%Y',\n",
    "    '%m/%d/%Y',\n",
    "]\n",
    "\n",
    "unparsed_idx = df_clean.index[df_clean['timestamp'].isna()]\n",
    "raw_unparsed = df.loc[unparsed_idx, 'timestamp']\n",
    "resolved = try_formats(raw_unparsed, candidate_formats)\n",
    "df_clean.loc[unparsed_idx, 'timestamp'] = resolved\n",
    "\n",
    "print(f'Resolved in second pass: {resolved.notna().sum()}')\n",
    "print(f'Still NaT (truly ambiguous): {df_clean[\"timestamp\"].isna().sum()}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## Part 5 — Verify with D-Tale"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2026-02-24 10:21:11,629 - INFO     - Shutdown complete\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "D-Tale (cleaned) running at: http://127.0.0.1:40001\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2026-02-24 10:21:14,521 - ERROR    - Exception occurred while processing request: object of type 'NoneType' has no len()\n",
      "Traceback (most recent call last):\n",
      "  File \"d:\\Repositories\\VI_Lab_01_EDA\\.venv\\Lib\\site-packages\\dtale\\views.py\", line 121, in _handle_exceptions\n",
      "    return func(*args, **kwargs)\n",
      "           ^^^^^^^^^^^^^^^^^^^^^\n",
      "  File \"d:\\Repositories\\VI_Lab_01_EDA\\.venv\\Lib\\site-packages\\dtale\\views.py\", line 1603, in get_processes\n",
      "    [_load_process(data_id) for data_id in global_state.keys()],\n",
      "    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n",
      "  File \"d:\\Repositories\\VI_Lab_01_EDA\\.venv\\Lib\\site-packages\\dtale\\views.py\", line 1603, in <listcomp>\n",
      "    [_load_process(data_id) for data_id in global_state.keys()],\n",
      "     ^^^^^^^^^^^^^^^^^^^^^^\n",
      "  File \"d:\\Repositories\\VI_Lab_01_EDA\\.venv\\Lib\\site-packages\\dtale\\views.py\", line 1588, in _load_process\n",
      "    rows=len(data),\n",
      "         ^^^^^^^^^\n",
      "TypeError: object of type 'NoneType' has no len()\n"
     ]
    }
   ],
   "source": [
    "d.kill()\n",
    "d_clean = dtale.show(df_clean, host='127.0.0.1', subprocess=True, open_browser=True)\n",
    "print('D-Tale (cleaned) running at:', d_clean._url)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## Part 6 — Before vs After with SweetViz"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "72f89f10eb614acba449eae1e2567d36",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "                                             |          | [  0%]   00:00 -> (? left)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Report sweetviz_git_comparison.html was generated.\n",
      "Comparison report saved.\n"
     ]
    }
   ],
   "source": [
    "# Exclude timestamp — SweetViz cannot compare string vs datetime64\n",
    "exclude = ['timestamp']\n",
    "compare = sv.compare(\n",
    "    [df.drop(columns=exclude), 'Raw'],\n",
    "    [df_clean.drop(columns=exclude).reset_index(drop=True), 'Cleaned']\n",
    ")\n",
    "compare.show_html('sweetviz_git_comparison.html', open_browser=False)\n",
    "print('Comparison report saved.')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## Part 7 — Save"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Saved: 10000 rows, 23 columns\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2026-02-24 11:18:57,558 - INFO     - Executing shutdown due to inactivity...\n",
      "2026-02-24 11:18:57,708 - INFO     - Executing shutdown...\n",
      "2026-02-24 11:18:57,713 - INFO     - Not running with the Werkzeug Server, exiting by searching gc for BaseWSGIServer\n",
      "2026-02-24 11:18:58,185 - ERROR    - weakly-referenced object no longer exists\n",
      "2026-02-24 11:20:06,895 - INFO     - Executing shutdown due to inactivity...\n"
     ]
    }
   ],
   "source": [
    "df_clean.to_csv('dataset_D_git_classroom_activity_clean.csv', index=False)\n",
    "print(f'Saved: {len(df_clean)} rows, {len(df_clean.columns)} columns')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## Reflection — Suggested Answers\n",
    "\n",
    "**1. `pr_merge_time_hours` missing 71.7% — is this a data quality problem?**  \n",
    "No. The missingness is structural: only `pr_merged` and `pr_opened` events have a merge time by definition. Every other event type (commit, push, CI run, etc.) has no merge time to record. This is MNAR — Missing Not At Random — and the pattern itself carries meaning. Imputing or dropping these rows would be wrong.\n",
    "\n",
    "**2. What does the cross-column check reveal that single-column inspection misses?**  \n",
    "Single-column inspection of `tests_failed` shows values ranging from 0 to 245 — nothing obviously wrong. Single-column inspection of `tests_run` also looks normal. Only by comparing the two together does the logical impossibility appear: you cannot fail more tests than you ran. This is a category of data quality issue that automated profiling tools like SweetViz do not detect.\n",
    "\n",
    "**3. What knowledge beyond the data was needed for `ci_status`?**  \n",
    "Domain knowledge about CI systems: that `failed` and `failure` refer to the same pipeline outcome, and that `failed` is the conventional term in tools like GitHub Actions and Jenkins. Without this knowledge, a purely statistical analysis would treat them as two separate categories and silently undercount CI failures.\n",
    "\n",
    "**4. What was the same as Task 2? What was new?**  \n",
    "Same: boolean encoding chaos, case/whitespace inconsistency, comma decimal separator, structural missingness reasoning, negative value treatment, D-Tale navigation, SweetViz before/after.  \n",
    "New: synonym merging (`ci_status`), cross-column logical consistency check (`tests_failed > tests_run`), out-of-range numeric check (`coverage_percent` outside 0–100).  \n",
    "Takeaway: the core cleaning patterns transfer across domains. What changes is the domain knowledge needed to make the decisions — which canonical form to use, what physical constraints apply to each variable, what constitutes a structurally justified missing value."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "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.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
