pilot_test2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Package Pilot Testing\n",
    "\n",
    "The pilot experiment output on real data will not be disclosed due to confidentiality of the data. Below is the output using fake datasets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "🚀Execution time: 0.000000000000000\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import time\n",
    "\n",
    "start_time = time.time()\n",
    "print(\"🚀Execution time: {:.15f}\".format(start_time - time.time()))\n",
    "del start_time"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. Fake Data Pilot Experiment"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.1 Blue Automata With `automata_execution`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "|    | Department   | Dept   | User ID           | Name              | System1   | Cube   |\n",
      "|---:|:-------------|:-------|:------------------|:------------------|:----------|:-------|\n",
      "|  0 | Google       | GOOG   | ewbzm6yv67w33mxy9 | Christopher Bruce | BSE       |        |\n",
      "|  1 | Apple        | AAPL   | wrzzdur66uzwvb0dh | Michael Perez     | BSE       |        |\n",
      "|  2 | Apple        | AAPL   | aurg7k1rxgmnsylsv | Stephanie Carr    | BSE       |        |\n",
      "|  3 | Apple        | AAPL   | np81yvtz4zcum4h39 | Julia Vargas      | BSE       |        |\n",
      "|  4 | Apple        | AAPL   | 99ttcx5t43r1rrwr1 | Michelle Walker   | BSE       |        |\n",
      "\n",
      " Total number of rows:  3464\n",
      "\n",
      "🚀Execution time: 1.093499660491943\n"
     ]
    }
   ],
   "source": [
    "from blueautomata.data_compilation import BlueAutomata\n",
    "\n",
    "start_time = time.time()\n",
    "\n",
    "test = BlueAutomata(\n",
    "    folder_path=r\"C:\\Users\\Khor Kean Teng\\Downloads\\AUP Automata\\data\\fakesystem\",\n",
    "    checklist=\"data/checklist.xlsx\",\n",
    "    staff_data=\"data/fake_hr_data.xlsx\",\n",
    "    name_key=[\"BSE\", \"HKEX\", \"KLSE\", \"LSE1\", \"NASDAQ\", \"NYSE\", \"SGX\", \"SSE\", \"TSE\"],\n",
    "    name_code=[1, 1, 1, 1, 1, 1, 1, 1, 1],\n",
    ")\n",
    "df = test.automata_execution()\n",
    "\n",
    "print(df.head().to_markdown())\n",
    "print(\"\\n Total number of rows: \", df.shape[0])\n",
    "print(\"\\n🚀Execution time: {:.15f}\".format(time.time() - start_time))\n",
    "del start_time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel(\"data/output.xlsx\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.2 AutomataReport With `automata_report_summary`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "|    | System   |   Match IDs |   Not Match IDs |   Total IDs |\n",
      "|---:|:---------|------------:|----------------:|------------:|\n",
      "|  0 | BSE      |          77 |              27 |         104 |\n",
      "|  1 | HKEX     |         404 |              90 |         494 |\n",
      "|  2 | KLSE     |         740 |             162 |         902 |\n",
      "|  3 | LSE1     |         314 |              72 |         386 |\n",
      "|  4 | SHEET1   |         658 |             141 |         799 |\n",
      "|  5 | NYSE     |         565 |             126 |         691 |\n",
      "|  6 | SGX      |         485 |             112 |         597 |\n",
      "|  7 | SSE      |           5 |               0 |           5 |\n",
      "|  8 | TSE      |         216 |              51 |         267 |\n",
      "\n",
      " Total number of matched:  3464\n",
      "\n",
      "🚀Execution time: 1.150006771087646\n"
     ]
    }
   ],
   "source": [
    "from blueautomata.automation_report import AutomataReport\n",
    "\n",
    "start_time = time.time()\n",
    "\n",
    "test = AutomataReport(\n",
    "    folder_path=r\"C:\\Users\\Khor Kean Teng\\Downloads\\AUP Automata\\data\\fakesystem\",\n",
    "    checklist=\"data/checklist.xlsx\",\n",
    "    staff_data=\"data/fake_hr_data.xlsx\",\n",
    "    name_key=[\"BSE\", \"HKEX\", \"KLSE\", \"LSE1\", \"NASDAQ\", \"NYSE\", \"SGX\", \"SSE\", \"TSE\"],\n",
    "    name_code=[1, 1, 1, 1, 1, 1, 1, 1, 1],\n",
    ")\n",
    "df = test.automata_report_summary()\n",
    "\n",
    "print(df.head(n=len(df)).to_markdown())\n",
    "print(\"\\n Total number of matched: \", df[\"Match IDs\"].sum())\n",
    "print(\"\\n🚀Execution time: {:.15f}\".format(time.time() - start_time))\n",
    "del start_time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "|    | User ID           | check   | System1   | Name               |\n",
      "|---:|:------------------|:--------|:----------|:-------------------|\n",
      "|  0 | chgzsprz5pwc0mnx7 | False   | BSE       | Jennifer Sandoval  |\n",
      "|  1 | l3ur5zaf6eefpvslw | False   | BSE       | Jessica Evans      |\n",
      "|  2 | r31p76vr2f5n3x1e8 | False   | BSE       | William Robinson   |\n",
      "|  3 | 0dnjw78m083l0e0p7 | False   | BSE       | Lisa Brown         |\n",
      "|  4 | 84vmzt0j5ev4z4pta | False   | BSE       | Dr. Nichole Thomas |\n",
      "|  5 | g7jvd2nb55b88hdbu | False   | BSE       | Carlos Walls       |\n",
      "|  6 | 3a1wukpcxkxah6d05 | False   | BSE       | William Morgan     |\n",
      "|  7 | 41u8tlyj6nm1scwvx | False   | BSE       | David Wilson       |\n",
      "|  8 | g7h6efjk5z4xlhwzu | False   | BSE       | Maria Watson       |\n",
      "|  9 | 8b2t34bvxburnjc5b | False   | BSE       | Nicholas Burton    |\n",
      "| 10 | 57s8x0bext7v1hhkx | False   | BSE       | Sean Ramirez       |\n",
      "| 11 | jk0g4vfbx83a256lg | False   | BSE       | Alexis Vang        |\n",
      "| 12 | 2zh9mag3x6ph2n5ee | False   | BSE       | Debra Nelson       |\n",
      "| 13 | 53rgf9kh8zddv0kxr | False   | BSE       | Stacy Johnson      |\n",
      "| 14 | kket7v5u14cz618u5 | False   | BSE       | Dillon Aguirre     |\n",
      "\n",
      "🚀Execution time: 1.007138967514038\n"
     ]
    }
   ],
   "source": [
    "from blueautomata.automation_report import AutomataReport\n",
    "\n",
    "start_time = time.time()\n",
    "\n",
    "test = AutomataReport(\n",
    "    folder_path=r\"C:\\Users\\Khor Kean Teng\\Downloads\\AUP Automata\\data\\fakesystem\",\n",
    "    checklist=\"data/checklist.xlsx\",\n",
    "    staff_data=\"data/fake_hr_data.xlsx\",\n",
    "    name_key=[\"BSE\", \"HKEX\", \"KLSE\", \"LSE1\", \"NASDAQ\", \"NYSE\", \"SGX\", \"SSE\", \"TSE\"],\n",
    "    name_code=[1, 1, 1, 1, 1, 1, 1, 1, 1],\n",
    ")\n",
    "df = test.automata_report_unmatch()\n",
    "\n",
    "print(df.head(n=15).to_markdown())\n",
    "print(\"\\n🚀Execution time: {:.15f}\".format(time.time() - start_time))\n",
    "del start_time"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- `fix-inconsistency` will be tested on real dataset only. For fake data example, we will use notebook (see website)\n",
    "- `system-cube-update` will be tested on real dataset only. For fake data example, we will use notebook (see website)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.3 BatchExport With `batch_export`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Export Completed\n",
      "\n",
      "🚀Execution time: 0.585944175720215\n"
     ]
    }
   ],
   "source": [
    "from blueautomata.batch_export import BatchExport\n",
    "\n",
    "start_time = time.time()\n",
    "\n",
    "df = pd.read_excel(\"data/output.xlsx\")\n",
    "\n",
    "temp = BatchExport(\n",
    "    destination=\"data/dept\",\n",
    "    masterlist=df,\n",
    ")\n",
    "\n",
    "temp.batch_export()\n",
    "\n",
    "print(\"\\n🚀Execution time: {:.15f}\".format(time.time() - start_time))\n",
    "del start_time"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.4 AutomateVBA With templatetize"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Execution Completed\n",
      "\n",
      "🚀Execution time: 11.390846729278564\n"
     ]
    }
   ],
   "source": [
    "from blueautomata.to_vba import automate_vba\n",
    "\n",
    "start_time = time.time()\n",
    "\n",
    "temp = automate_vba(\n",
    "    filepath=r\"C:\\\\Users\\\\Khor Kean Teng\\\\Downloads\\\\AUP Automata\\\\vbanew.xlsm\",\n",
    "    macro=\"vbanew.xlsm!Module1.kt_template\",\n",
    ")\n",
    "temp.templatetize()\n",
    "\n",
    "print(\"\\n🚀Execution time: {:.15f}\".format(time.time() - start_time))\n",
    "del start_time"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}