{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13a40f32-d068-43e5-a0a3-9b53f0cea5fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "import mercury as mr \n",
    "from IPython.display import clear_output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8079f627-82d5-4897-b8c7-186c108d0f84",
   "metadata": {},
   "outputs": [],
   "source": [
    "file = mr.UploadFile(label=\"Upload your Excel file\", accept=\".xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "643bfef6-90ad-4070-b3ef-fbb3c62a3cee",
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "import unicodedata\n",
    "\n",
    "def normalize_column_name(column_name):\n",
    "    # Convert to string\n",
    "    column_name = str(column_name)\n",
    "\n",
    "    # Remove accents\n",
    "    column_name = unicodedata.normalize(\"NFKD\", column_name)\n",
    "    column_name = column_name.encode(\"ascii\", \"ignore\").decode(\"ascii\")\n",
    "\n",
    "    # Convert to lowercase\n",
    "    column_name = column_name.lower()\n",
    "\n",
    "    # Remove extra spaces\n",
    "    column_name = column_name.strip()\n",
    "\n",
    "    # Replace special characters with _\n",
    "    column_name = re.sub(r\"[^a-z0-9]+\", \"_\", column_name)\n",
    "\n",
    "    # Remove _ from start and end\n",
    "    column_name = column_name.strip(\"_\")\n",
    "\n",
    "    return column_name\n",
    "\n",
    "def normalize_column_names(df):\n",
    "    df = df.copy()\n",
    "    df.columns = [normalize_column_name(col) for col in df.columns]\n",
    "    return df\n",
    "\n",
    "def remove_duplicate_rows(df):\n",
    "    # Make a copy\n",
    "    df_clean = df.copy()\n",
    "\n",
    "    # Remove duplicate rows\n",
    "    df_clean = df_clean.drop_duplicates()\n",
    "\n",
    "    return df_clean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4d4793ab-0a7e-4f74-a381-bc93d990fda8",
   "metadata": {},
   "outputs": [],
   "source": [
    "if file.name is not None:\n",
    "    import pandas as pd\n",
    "    from io import BytesIO\n",
    "    data = BytesIO(file.value)\n",
    "    excel = pd.ExcelFile(data)\n",
    "    sheet_names = excel.sheet_names\n",
    "    sheets_info = []\n",
    "    df_list = []\n",
    "    ready_df = []\n",
    "    \n",
    "    for sheet_name in excel.sheet_names:\n",
    "        df = pd.read_excel(data, sheet_name=sheet_name)\n",
    "        df_list.append(df)\n",
    "        ready_df.append(df)\n",
    "        rows = df.shape[0]\n",
    "        columns = df.shape[1]\n",
    "        empty_cells = df.isna().sum().sum()\n",
    "        duplicate_rows = df.duplicated().sum()\n",
    "    \n",
    "        sheets_info.append({\n",
    "            \"Sheet Name\": sheet_name,\n",
    "            \"Rows\": rows,\n",
    "            \"Columns\": columns,\n",
    "            \"Empty Cells\": empty_cells,\n",
    "            \"Duplicate Rows\": duplicate_rows\n",
    "        })\n",
    "    \n",
    "    sheets_info_df = pd.DataFrame(sheets_info)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "75889854-7d9e-41ac-9132-4cd9a6981a86",
   "metadata": {},
   "outputs": [],
   "source": [
    "if file.name is None:\n",
    "    mr.Markdown(\"# Upload the Excel file to start\") \n",
    "else:\n",
    "    mr.Markdown(f\"# Uploaded file: {file.name}\", key='title_md')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "53ab7e24-bf30-48d6-b107-b961d10c3d51",
   "metadata": {},
   "outputs": [],
   "source": [
    "if file.name is not None:\n",
    "    sheets_info_table = mr.Table(sheets_info_df, page_size=20, key='sheets-info')\n",
    "else:\n",
    "    clear_output()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e53c76a0-2faa-4e83-a3cd-c12f5141ea38",
   "metadata": {},
   "outputs": [],
   "source": [
    "if file.name is not None: \n",
    "    sheet_select = mr.Select(label=\"Choose sheet\", choices=sheet_names)\n",
    "else: \n",
    "    clear_output()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c3d864e6-407e-4257-931c-72deeea8fe7a",
   "metadata": {},
   "outputs": [],
   "source": [
    "if file.name is not None: \n",
    "    mr.Markdown(f\"## Sheet: {sheet_select.value}\", key='sheet_md')\n",
    "else:\n",
    "    clear_output()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "902788d4-770b-4aa8-8165-7f13f5c92dc1",
   "metadata": {},
   "outputs": [],
   "source": [
    "if file.name is not None:\n",
    "    oryginal_data_table = mr.Table(df_list[sheet_names.index(sheet_select.value)], page_size=20, key=f\"oryginal-df-{sheet_select.value}\")\n",
    "else: \n",
    "    clear_output()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "84892d8f-79fd-45c9-b623-dd8d744d7a0f",
   "metadata": {},
   "outputs": [],
   "source": [
    "if file.name is not None: \n",
    "    mr.Markdown(\"### Choose operation\", position=\"sidebar\", key='checkboxes')\n",
    "    normalize_col_names_checkbox = mr.CheckBox(label=\"Normalize column names\", appearance=\"box\", key=f\"normalize_col_names_checkbox-{sheet_select.value}\")\n",
    "    remove_duplicate_rows_checkbox = mr.CheckBox(label=\"Remove duplicate rows\", appearance=\"box\", key=f\"remove_duplicate_rows_checkbox-{sheet_select.value}\")\n",
    "else: \n",
    "    clear_output()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "46b910a1-ee3e-47d6-8ab4-a8b82f539f5b",
   "metadata": {},
   "outputs": [],
   "source": [
    "if file.name is not None:\n",
    "    if normalize_col_names_checkbox.value:\n",
    "        normalize_col_names_checkbox.disabled = True\n",
    "        ready_df[sheet_names.index(sheet_select.value)] = normalize_column_names(ready_df[sheet_names.index(sheet_select.value)])\n",
    "\n",
    "    if remove_duplicate_rows_checkbox.value:\n",
    "        remove_duplicate_rows_checkbox.disabled = True\n",
    "        ready_df[sheet_names.index(sheet_select.value)] = remove_duplicate_rows(ready_df[sheet_names.index(sheet_select.value)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5144a641-f7fd-4ed6-867a-1f9b927fb950",
   "metadata": {},
   "outputs": [],
   "source": [
    "if file.name is not None: \n",
    "    if normalize_col_names_checkbox.value or remove_duplicate_rows_checkbox.value: \n",
    "        mr.Markdown(\"## Results\", key='results')\n",
    "        edited_data_table = mr.Table(ready_df[sheet_names.index(sheet_select.value)], page_size=20, key=f\"results-table-{sheet_select.value}\")\n",
    "    else:\n",
    "        clear_output()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca3df87e-31d7-42dd-8fe4-e3cbd287a17c",
   "metadata": {},
   "outputs": [],
   "source": [
    "if file.name is not None:\n",
    "    csv_data = ready_df[sheet_names.index(sheet_select.value)].to_csv(index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f856113c-00af-4be9-aa79-8a16faab39eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "if file.name is not None:\n",
    "    if normalize_col_names_checkbox.value or remove_duplicate_rows_checkbox.value:\n",
    "        mr.Markdown(\"### Download edited sheet\", position=\"sidebar\", key='download')\n",
    "        mr.Download(\n",
    "            data=csv_data,\n",
    "            filename=f\"{normalize_column_name(sheet_select.value)}-edited.csv\",\n",
    "            mime=\"text/csv\",\n",
    "            label=\"Download as CSV\",\n",
    "            key=f\"csv-download-{sheet_select.value}-{normalize_col_names_checkbox.value}-{remove_duplicate_rows_checkbox.value}\"\n",
    "        )\n",
    "    else: \n",
    "        clear_output()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de2ecc2c-4e99-4800-a486-93fb0e49c7f7",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "mvenv",
   "language": "python",
   "name": "mvenv"
  },
  "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.10.12"
  },
  "mercury": {
   "title": "Excel Cleaner"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}