{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 02 - Data Cleaning\n",
    "\n",
    "Create `data/processed/jira_issues_cleaned.csv` from the raw Jira export. This keeps only fields known before resolution, plus dates needed by the next notebook to create duration_days target variable.."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "529aa861",
   "metadata": {},
   "source": [
    "## 02-01 Import cleaning tools\n",
    "\n",
    "Load the small set of Python tools needed for this notebook. pathlib keeps file paths portable, while pandas handles the tabular data manipulation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "8d4c682c",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pathlib import Path\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "922e97e3",
   "metadata": {},
   "source": [
    "## 02-02 Load selected raw fields\n",
    "\n",
    "Set the project paths, create the processed-data folder, and load only the Jira columns needed for the first duration model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4f719c50",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Raw rows loaded: 1,149,323\n",
      "Raw columns loaded: 13\n"
     ]
    }
   ],
   "source": [
    "PROJECT_ROOT = Path.cwd().parent if Path.cwd().name == \"notebooks\" else Path.cwd()\n",
    "RAW_PATH = PROJECT_ROOT / \"jira_ticket_dataset.csv\"\n",
    "OUTPUT_DIR = PROJECT_ROOT / \"data\" / \"processed\"\n",
    "OUTPUT_DIR.mkdir(parents=True, exist_ok=True)\n",
    "\n",
    "RAW_COLUMNS = [\n",
    "    \"summary\",\n",
    "    \"description\",\n",
    "    \"priority.name\",\n",
    "    \"issuetype.name\",\n",
    "    \"project.key\",\n",
    "    \"projectCategory.name\",\n",
    "    \"votes.votes\",\n",
    "    \"watches.watchCount\",\n",
    "    \"labels\",\n",
    "    \"assignee\",\n",
    "    \"statusCategory.name\",\n",
    "    \"created\",\n",
    "    \"resolutiondate\",\n",
    "]\n",
    "\n",
    "ticket_df = pd.read_csv(RAW_PATH, usecols=RAW_COLUMNS)\n",
    "\n",
    "print(f\"Raw rows loaded: {ticket_df.shape[0]:,}\")\n",
    "print(f\"Raw columns loaded: {ticket_df.shape[1]:,}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d7d5ec3",
   "metadata": {},
   "source": [
    "## 02-03 Filter usable completed issues\n",
    "\n",
    "Keep issues marked as done with valid created and resolution timestamps. Rows with missing dates or negative durations cannot produce a reliable duration target."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4b995c42",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Rows before completed issue filtering: 1,149,323\n",
      "Rows after completed issue filtering: 945,103\n",
      "Rows removed: 204,220\n"
     ]
    }
   ],
   "source": [
    "created_dates = pd.to_datetime(ticket_df[\"created\"], errors=\"coerce\")\n",
    "resolution_dates = pd.to_datetime(ticket_df[\"resolutiondate\"], errors=\"coerce\")\n",
    "\n",
    "completed_issue_mask = (\n",
    "    ticket_df[\"statusCategory.name\"].eq(\"Done\")\n",
    "    & created_dates.notna()\n",
    "    & resolution_dates.notna()\n",
    "    & (resolution_dates >= created_dates)\n",
    ")\n",
    "\n",
    "rows_before = len(ticket_df)\n",
    "clean_df = ticket_df.loc[completed_issue_mask].copy()\n",
    "\n",
    "print(f\"Rows before completed issue filtering: {rows_before:,}\")\n",
    "print(f\"Rows after completed issue filtering: {len(clean_df):,}\")\n",
    "print(f\"Rows removed: {rows_before - len(clean_df):,}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b589b6e9",
   "metadata": {},
   "source": [
    "## 02-04 Normalize fields and create simple features\n",
    "\n",
    "Clean text and categorical fields, convert empty values into explicit defaults, and derive basic counts and flags from summaries, descriptions, labels, assignments, votes, and watches."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "70bf0c48",
   "metadata": {},
   "outputs": [],
   "source": [
    "text_columns = [\"summary\", \"description\"]\n",
    "\n",
    "for column in text_columns:\n",
    "    clean_df[column] = (\n",
    "        clean_df[column]\n",
    "        .fillna(\"\")\n",
    "        .astype(str)\n",
    "        .str.replace(r\"\\s+\", \" \", regex=True)\n",
    "        .str.strip()\n",
    "    )\n",
    "\n",
    "categorical_mappings = {\n",
    "    \"priority.name\": \"priority_name\",\n",
    "    \"issuetype.name\": \"issuetype_name\",\n",
    "    \"project.key\": \"project_key\",\n",
    "    \"projectCategory.name\": \"project_category_name\",\n",
    "}\n",
    "\n",
    "for source_column, clean_column in categorical_mappings.items():\n",
    "    clean_df[clean_column] = (\n",
    "        clean_df[source_column]\n",
    "        .fillna(\"Unknown\")\n",
    "        .astype(str)\n",
    "        .str.replace(r\"\\s+\", \" \", regex=True)\n",
    "        .str.strip()\n",
    "        .replace(\"\", \"Unknown\")\n",
    "    )\n",
    "\n",
    "clean_df[\"summary_char_count\"] = clean_df[\"summary\"].str.len()\n",
    "clean_df[\"summary_word_count\"] = clean_df[\"summary\"].str.split().str.len()\n",
    "clean_df[\"description_char_count\"] = clean_df[\"description\"].str.len()\n",
    "clean_df[\"description_word_count\"] = clean_df[\"description\"].str.split().str.len()\n",
    "clean_df[\"has_description\"] = (clean_df[\"description_word_count\"] > 0).astype(int)\n",
    "\n",
    "labels_text = clean_df[\"labels\"].fillna(\"[]\").astype(str).str.strip()\n",
    "clean_df[\"labels_count\"] = labels_text.str.count(\",\") + labels_text.ne(\"[]\").astype(int)\n",
    "clean_df[\"has_assignee\"] = clean_df[\"assignee\"].notna().astype(int)\n",
    "clean_df[\"votes_votes\"] = pd.to_numeric(clean_df[\"votes.votes\"], errors=\"coerce\").fillna(0).clip(lower=0)\n",
    "clean_df[\"watches_watch_count\"] = pd.to_numeric(clean_df[\"watches.watchCount\"], errors=\"coerce\").fillna(0).clip(lower=0)\n",
    "\n",
    "for column in [\"created\", \"resolutiondate\"]:\n",
    "    clean_df[column] = pd.to_datetime(clean_df[column], errors=\"coerce\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d467fb88",
   "metadata": {},
   "source": [
    "## 02-05 Select model columns and check quality\n",
    "\n",
    "Limit the cleaned dataframe to model-ready columns, remove duplicate records, and confirm that the final feature set has no remaining missing values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "09e996a9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Rows before duplicate removal: 945,103\n",
      "Rows after duplicate removal: 937,203\n",
      "Duplicate rows removed: 7,900\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>missing_count</th>\n",
       "      <th>missing_percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>summary</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>description</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>priority_name</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>issuetype_name</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>project_key</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>project_category_name</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>summary_char_count</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>summary_word_count</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>description_char_count</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>description_word_count</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>has_description</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>labels_count</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>has_assignee</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>votes_votes</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>watches_watch_count</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>created</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>resolutiondate</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        missing_count  missing_percent\n",
       "summary                             0              0.0\n",
       "description                         0              0.0\n",
       "priority_name                       0              0.0\n",
       "issuetype_name                      0              0.0\n",
       "project_key                         0              0.0\n",
       "project_category_name               0              0.0\n",
       "summary_char_count                  0              0.0\n",
       "summary_word_count                  0              0.0\n",
       "description_char_count              0              0.0\n",
       "description_word_count              0              0.0\n",
       "has_description                     0              0.0\n",
       "labels_count                        0              0.0\n",
       "has_assignee                        0              0.0\n",
       "votes_votes                         0              0.0\n",
       "watches_watch_count                 0              0.0\n",
       "created                             0              0.0\n",
       "resolutiondate                      0              0.0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_model_columns = [\n",
    "    \"summary\",\n",
    "    \"description\",\n",
    "    \"priority_name\",\n",
    "    \"issuetype_name\",\n",
    "    \"project_key\",\n",
    "    \"project_category_name\",\n",
    "    \"summary_char_count\",\n",
    "    \"summary_word_count\",\n",
    "    \"description_char_count\",\n",
    "    \"description_word_count\",\n",
    "    \"has_description\",\n",
    "    \"labels_count\",\n",
    "    \"has_assignee\",\n",
    "    \"votes_votes\",\n",
    "    \"watches_watch_count\",\n",
    "    \"created\",\n",
    "    \"resolutiondate\",\n",
    "]\n",
    "\n",
    "rows_before = len(clean_df)\n",
    "model_df = clean_df[cleaned_model_columns].drop_duplicates().copy()\n",
    "\n",
    "print(f\"Rows before duplicate removal: {rows_before:,}\")\n",
    "print(f\"Rows after duplicate removal: {len(model_df):,}\")\n",
    "print(f\"Duplicate rows removed: {rows_before - len(model_df):,}\")\n",
    "\n",
    "missing_summary = pd.DataFrame({\n",
    "    \"missing_count\": model_df.isna().sum(),\n",
    "    \"missing_percent\": model_df.isna().mean().mul(100).round(2),\n",
    "})\n",
    "missing_summary.sort_values(\"missing_percent\", ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf065a80",
   "metadata": {},
   "source": [
    "## 02-06 Save cleaned datasets\n",
    "\n",
    "Write the full cleaned dataset and a small reproducible sample to `data/processed` so later notebooks and the app can reuse the same prepared data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4a351ea3",
   "metadata": {},
   "outputs": [
    {
     "ename": "",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31mThe Kernel crashed while executing code in the current cell or a previous cell. \n",
      "\u001b[1;31mPlease review the code in the cell(s) to identify a possible cause of the failure. \n",
      "\u001b[1;31mClick <a href='https://aka.ms/vscodeJupyterKernelCrash'>here</a> for more info. \n",
      "\u001b[1;31mView Jupyter <a href='command:jupyter.viewOutput'>log</a> for further details."
     ]
    }
   ],
   "source": [
    "csv_path = OUTPUT_DIR / \"jira_issues_cleaned.csv\"\n",
    "sample_path = OUTPUT_DIR / \"jira_issues_cleaned_sample.csv\"\n",
    "\n",
    "model_df.to_csv(csv_path, index=False)\n",
    "model_df.sample(n=min(100, len(model_df)), random_state=42).to_csv(sample_path, index=False)\n",
    "\n",
    "print(f\"Saved cleaned CSV file to: {csv_path}\")\n",
    "print(f\"Saved sample CSV file to: {sample_path}\")\n",
    "print(f\"Final cleaned rows: {model_df.shape[0]:,}\")\n",
    "print(f\"Final cleaned columns: {model_df.shape[1]:,}\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}