data_acquisition.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "66aff925",
   "metadata": {},
   "source": [
    "# Gathering the Data\n",
    "- **Name: Andrew Angulo**\n",
    "- **Data Systems Project**\n",
    "\n",
    "The purpose of this project is to acquire information from websites by scraping off the data. Doing this will allow us to further operate within SQL and asses our data properly"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c55a96d",
   "metadata": {},
   "source": [
    "---\n",
    "### Website URL's\n",
    "In order to aquire the data we have to first either retrieve a .HTML of the websites current page or we can simply\n",
    "retrieve it using **request**\n",
    "- [Arknights Operator Table](https://gamepress.gg/arknights/tools/interactive-operator-list#tags=null##cn##stats)\n",
    "    - **Description:** This website includes tabular data based on the characters rarity and the stats of the given character\n",
    "    - **Columns:** 2\n",
    "    - **Rows:** 269 (as of November 2022)\n",
    "    - **Data Format:** Long\n",
    "    - **Created By:** Gamepress\n",
    "- [Arknights Banner Table](https://gamepress.gg/arknights/database/banner-list-gacha)\n",
    "    - **Description:** This website includes tabular data based on the upcoming banners that players are able to look forward to which also provide the specific character that will be on that banner\n",
    "    - **Columns:** 5\n",
    "    - **Rows:** 9 (as of November 2022)\n",
    "    - **Data Format:** Wide\n",
    "    - **Created By:** Daniel O'Brien, NorseFTX\n",
    "    \n",
    "### Goal of this project:\n",
    "- The goal of this project is to determine **If some characters are better than others based on their stats, and how much would the player have to invest in the game to acquire that character?**\n",
    "\n",
    "### Permission to Scrape Website:\n",
    "- [Evidence 1](https://i.imgur.com/uSV9kiN.png)\n",
    "    - **NorseFTX:** is the person who created these datasets and publishes them on **gamepress** within the Arknights section\n",
    "- [Evidence 2](https://i.imgur.com/ObkCO0d.png)\n",
    "    - **ChibiChu:** he is a staff member who works at **gamepress**\n",
    "    \n",
    "<u><i>**NOTE:** All information and permission to scrape the websites were asked to them directly through their [Discord](https://discord.com/invite/yq8D9GX) as **gamepress** does include a link to their discord server where their staff does resides</u></i>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e4afad1",
   "metadata": {},
   "source": [
    "---\n",
    "### Description of Imports:\n",
    "\n",
    "- we use **pandas** so once we grab our data and store it into a Dictionary of List we can then transfer that data into a Dataframe\n",
    "\n",
    "\n",
    "- we use **requests** so we can grab all of the HTML from just the URL\n",
    "\n",
    "\n",
    "- we use **lmxl import html** because since we are working with **requests** we can utilize xpath to parse and traverse the html contents and return the data we need\n",
    "\n",
    "\n",
    "- we use **os.path** in order to obtain the file based on the location specificed within the computer\n",
    "\n",
    "\n",
    "- we use **json** so we are able to load data from a JSON files such as creds.json\n",
    "\n",
    "\n",
    "- we use **sqlalchemy** so we are able to interact with the SQL database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "1c751762",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import requests\n",
    "import os.path\n",
    "import json\n",
    "import sqlalchemy as sa\n",
    "from lxml import html"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "646b3cf7",
   "metadata": {},
   "source": [
    "---\n",
    "### Why are we scraping these websites?\n",
    "\n",
    "We are scraping this website because it provides us data that we need to answer our central question: **Are some characters better than others based on their stats, and how much would the player have to invest in the game to collect that character?**\n",
    "\n",
    "##### Variables:\n",
    "- **OperatorURL:** Stores a string of the link to the website that contains the table of all of the operators\n",
    "- **BannerURL:** Stores a string of the link to the website that contains the table of all of the upcoming banners\n",
    "- **OperatorPage/BannerPage:** retrieves the information from the given URL\n",
    "- **OperatorTree/BannerTree:** parses and stores the HTML from the given website\n",
    "\n",
    "##### Why do we need this?\n",
    "- The reason we need these variables is because without it we can't answer our proposal question due to the fact we will have no HTML to work with or any data in particular to work with. With these variables we are one step closer to answering our proposal question\n",
    "\n",
    "##### How do we grab the data?\n",
    "- We grab the data by using **requests.get()** as it retrieves all of the HTML from the specifice URL after that we parse it with **html.fromstring(<u>insert_variable</u>.content)** which allows us to read it and go through all of its contents using xpath and other methods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "4656efbc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Retrieving the URL of the website\n",
    "OperatorURL = \"https://gamepress.gg/arknights/tools/interactive-operator-list#tags=null##cn##stats\"\n",
    "BannerURL = \"https://gamepress.gg/arknights/database/banner-list-gacha\"\n",
    "\n",
    "#Retrieving the page\n",
    "OperatorPage = requests.get(OperatorURL)\n",
    "BannerPage = requests.get(BannerURL)\n",
    "\n",
    "# Parsing the page\n",
    "OperatorTree = html.fromstring(OperatorPage.content) \n",
    "BannerTree = html.fromstring(BannerPage.content) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "641783e0",
   "metadata": {},
   "source": [
    "---\n",
    "### Grabbing the Operator Data:\n",
    "\n",
    "##### Variables:\n",
    "- **Operators:** Grabs the names of all the operators\n",
    "- **HP:** Grabs the health of all the operators\n",
    "- **ATK:** Grabs the attack of all the operators\n",
    "- **COST:** Grabs the cost to deploy the operator\n",
    "- **BLOCK:** Grabs the amount an operator can block an enemy\n",
    "- **REDEPLOY:** Grabs the amount needed to redeploy the operator\n",
    "- **INTERVAL:** Grabs the second it takes for the next attack to be processed by the operator\n",
    "- **TARGET:** Grabs the amount of enemys the operator can target\n",
    "- **DAMAGE_TYPE:** Grabs the type of damage the operator deals\n",
    "- **ROLE:** Grabs the role the operator belongs to\n",
    "\n",
    "##### Why do we need this?\n",
    "- The reason we need this variables is to set up our dictionary of lists and also because in order to answer our proposal question we need all of this data so we can finally see if **some characters are better then one another and if its worth investing into them** \n",
    "\n",
    "##### How do we grab the data?\n",
    "- In order to grab the data we are using **List Comprehensions** and **xpath** in order to store all of the data from the HTML into a list while also cleaning up the values by either turning it into a integer, float or just stripping it and removing '\\n' or any other problems."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "138513a4",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "#Grabbing the Operators\n",
    "Operators = OperatorTree.xpath(\"/.//td[@class = 'operator-cell']/div[@class = 'operator-title']/a/text()\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "61f53ec5",
   "metadata": {},
   "outputs": [],
   "source": [
    "HP = [int(i.strip()) for i in OperatorTree.xpath(\"/.//tr[@class = 'trustStat']/td[position() = 1]/text()\")]\n",
    "ATK = [int(i.strip()) for i in OperatorTree.xpath(\"/.//tr[@class = 'trustStat']/td[position() = 2]/text()\")]\n",
    "DEF = [int(i.strip()) for i in OperatorTree.xpath(\"/.//tr[@class = 'trustStat']/td[position() = 3]/text()\")]\n",
    "COST = [int(i.strip()) for i in OperatorTree.xpath(\"/.//tr[@class = 'trustStat']/td[position() = 4]/text()\")]\n",
    "RES = [int(i.strip()) for i in OperatorTree.xpath(\"/.//div[@class = 'stats-table-cell']/table[position() = 2]/tbody/tr[position() =1]/td[position() = 1]/text()\")]\n",
    "BLOCK = [int(i.strip()) for i in OperatorTree.xpath(\"/.//div[@class = 'stats-table-cell']/table[position() = 2]/tbody/tr[position() = 1]/td[position() = 2]/text()\")]\n",
    "REDEPLOY = [int(i.strip()) for i in OperatorTree.xpath(\"/.//div[@class = 'stats-table-cell']/table[position() = 2]/tbody/tr[position() = 1]/td[position() = 3]/span[position() =1]/text()\")]\n",
    "INTERVAL = [float(i.strip().replace('s', '')) for i in OperatorTree.xpath(\"/.//div[@class = 'stats-table-cell']/table[position() = 2]/tbody/tr[position() = 1]/td[position() = 4]/text()\")]\n",
    "TARGET = [i.strip().replace('Target: ', '').replace(' (Block #)', '') for i in OperatorTree.xpath(\"/.//div[@class = 'target-cell']/text()\")]\n",
    "DAMAGE_TYPE = [i.strip() for i in OperatorTree.xpath(\"/.//div[@class = 'target-damage-type stats-section tab-section']/div/a/text()\")]\n",
    "ROLE = [i.strip() for i in OperatorTree.xpath(\"/.//div[@class = 'info-div']/span[position() = 1]/text()\")]\n",
    "\n",
    "for i in range(len(TARGET)): #Converts string to an int if it isnt a string\n",
    "    try:\n",
    "        TARGET[i] = int(TARGET[i])\n",
    "    except:\n",
    "        continue"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e87471a",
   "metadata": {},
   "source": [
    "---\n",
    "### Creating the Dictionary:\n",
    "\n",
    "When we create the Dictionary you might be wondering **\"why arent we using the header within the table as our keys within the dictionary\"**. To answer your question the reason why we aren't using the headers as our keys is because in relation to our proposal question we are mainly seeking the stats of the operators so we can eventually compare them to other operators and see if possibly they are better or worse."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "45669418",
   "metadata": {},
   "outputs": [],
   "source": [
    "OperatorDoL = {'Operators': Operators, 'HP': HP, 'ATK': ATK, 'DEF': DEF, 'COST': COST, 'RES': RES, 'BLOCK': BLOCK\n",
    "      , 'REDEPLOY': REDEPLOY, 'INTERVAL': INTERVAL, 'TARGET': TARGET, 'DMG': DAMAGE_TYPE, 'ROLE': ROLE}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3d992b98",
   "metadata": {},
   "source": [
    "---\n",
    "### Assembling the Dataframe:\n",
    "\n",
    "Now that we have our data in a respective dictionary we can translate it towards into a pandas dataframe. By doing this we can further visualize our dataset and eventually translate this into SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c1969af0",
   "metadata": {
    "scrolled": false
   },
   "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>Operators</th>\n",
       "      <th>HP</th>\n",
       "      <th>ATK</th>\n",
       "      <th>DEF</th>\n",
       "      <th>COST</th>\n",
       "      <th>RES</th>\n",
       "      <th>BLOCK</th>\n",
       "      <th>REDEPLOY</th>\n",
       "      <th>INTERVAL</th>\n",
       "      <th>TARGET</th>\n",
       "      <th>DMG</th>\n",
       "      <th>ROLE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Vigil</td>\n",
       "      <td>1755</td>\n",
       "      <td>542</td>\n",
       "      <td>154</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>70</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Vanguard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Penance</td>\n",
       "      <td>4655</td>\n",
       "      <td>916</td>\n",
       "      <td>616</td>\n",
       "      <td>36</td>\n",
       "      <td>10</td>\n",
       "      <td>3</td>\n",
       "      <td>70</td>\n",
       "      <td>1.60</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Defender</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Texas the Omertosa</td>\n",
       "      <td>1598</td>\n",
       "      <td>659</td>\n",
       "      <td>320</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>0.93</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Specialist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Stainless</td>\n",
       "      <td>2723</td>\n",
       "      <td>633</td>\n",
       "      <td>461</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>70</td>\n",
       "      <td>1.50</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Supporter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Młynar</td>\n",
       "      <td>4266</td>\n",
       "      <td>385</td>\n",
       "      <td>502</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>70</td>\n",
       "      <td>1.20</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Guard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>264</th>\n",
       "      <td>Yato</td>\n",
       "      <td>1030</td>\n",
       "      <td>262</td>\n",
       "      <td>192</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>70</td>\n",
       "      <td>1.05</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Vanguard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265</th>\n",
       "      <td>'Justice Knight'</td>\n",
       "      <td>595</td>\n",
       "      <td>217</td>\n",
       "      <td>41</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>200</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Sniper</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>266</th>\n",
       "      <td>THRM-EX</td>\n",
       "      <td>1443</td>\n",
       "      <td>350</td>\n",
       "      <td>443</td>\n",
       "      <td>3</td>\n",
       "      <td>50</td>\n",
       "      <td>0</td>\n",
       "      <td>200</td>\n",
       "      <td>0.93</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Specialist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267</th>\n",
       "      <td>Castle-3</td>\n",
       "      <td>1391</td>\n",
       "      <td>413</td>\n",
       "      <td>90</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>200</td>\n",
       "      <td>1.50</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Guard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268</th>\n",
       "      <td>Lancet-2</td>\n",
       "      <td>535</td>\n",
       "      <td>110</td>\n",
       "      <td>27</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>200</td>\n",
       "      <td>2.85</td>\n",
       "      <td>1</td>\n",
       "      <td>Healing</td>\n",
       "      <td>Medic</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>269 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              Operators    HP  ATK  DEF  COST  RES  BLOCK  REDEPLOY  INTERVAL  \\\n",
       "0                 Vigil  1755  542  154    17    0      1        70      1.00   \n",
       "1               Penance  4655  916  616    36   10      3        70      1.60   \n",
       "2    Texas the Omertosa  1598  659  320    10    0      1        18      0.93   \n",
       "3             Stainless  2723  633  461    19    0      2        70      1.50   \n",
       "4                Młynar  4266  385  502    12   15      3        70      1.20   \n",
       "..                  ...   ...  ...  ...   ...  ...    ...       ...       ...   \n",
       "264                Yato  1030  262  192     7    0      2        70      1.05   \n",
       "265    'Justice Knight'   595  217   41     3    0      1       200      1.00   \n",
       "266             THRM-EX  1443  350  443     3   50      0       200      0.93   \n",
       "267            Castle-3  1391  413   90     3    0      1       200      1.50   \n",
       "268            Lancet-2   535  110   27     3    0      1       200      2.85   \n",
       "\n",
       "    TARGET       DMG        ROLE  \n",
       "0        1  Physical    Vanguard  \n",
       "1        1  Physical    Defender  \n",
       "2        1  Physical  Specialist  \n",
       "3        1  Physical   Supporter  \n",
       "4        1  Physical       Guard  \n",
       "..     ...       ...         ...  \n",
       "264      1  Physical    Vanguard  \n",
       "265      1  Physical      Sniper  \n",
       "266      1  Physical  Specialist  \n",
       "267      1  Physical       Guard  \n",
       "268      1   Healing       Medic  \n",
       "\n",
       "[269 rows x 12 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "OperatorDF = pd.DataFrame.from_dict(OperatorDoL)\n",
    "OperatorDF"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bcf4cd11",
   "metadata": {},
   "source": [
    "---\n",
    "### Tidying the Dataframe:\n",
    "After accessing **OperatorDF** we can clearly see that this dictionary is not tidy at all. There is no index, some of the columns are unclear. In order to fix this we can do a simple pivot and tidy up our data so we can have the respective index that translate to the data we want of that specific character\n",
    "\n",
    "\n",
    "- **Independent Variable:** Role, Operators\n",
    "- **Dependent Variable:** ATK, BLOCK, COST, DEF, DMG, HP, INTERVAL, REDEPLOY, RES, TARGET \n",
    "\n",
    "The independent variables are **Role and Operators** because within the game Arknights each operator has a distinct name and are assigned to a role ex: Caster, Vanguard. With these two keys we can access the deoendent variables which give us the operators stats within the game"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f54f5162",
   "metadata": {
    "scrolled": false
   },
   "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></th>\n",
       "      <th>ATK</th>\n",
       "      <th>BLOCK</th>\n",
       "      <th>COST</th>\n",
       "      <th>DEF</th>\n",
       "      <th>DMG</th>\n",
       "      <th>HP</th>\n",
       "      <th>INTERVAL</th>\n",
       "      <th>REDEPLOY</th>\n",
       "      <th>RES</th>\n",
       "      <th>TARGET</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ROLE</th>\n",
       "      <th>Operators</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Caster</th>\n",
       "      <th>12F</th>\n",
       "      <td>482</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>50</td>\n",
       "      <td>Arts</td>\n",
       "      <td>1461</td>\n",
       "      <td>2.9</td>\n",
       "      <td>70</td>\n",
       "      <td>10</td>\n",
       "      <td>AoE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Absinthe</th>\n",
       "      <td>703</td>\n",
       "      <td>1</td>\n",
       "      <td>22</td>\n",
       "      <td>124</td>\n",
       "      <td>Arts</td>\n",
       "      <td>1420</td>\n",
       "      <td>1.6</td>\n",
       "      <td>80</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Amiya</th>\n",
       "      <td>682</td>\n",
       "      <td>1</td>\n",
       "      <td>20</td>\n",
       "      <td>121</td>\n",
       "      <td>Arts</td>\n",
       "      <td>1680</td>\n",
       "      <td>1.6</td>\n",
       "      <td>70</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Astgenne</th>\n",
       "      <td>705</td>\n",
       "      <td>1</td>\n",
       "      <td>34</td>\n",
       "      <td>122</td>\n",
       "      <td>Arts</td>\n",
       "      <td>1440</td>\n",
       "      <td>2.3</td>\n",
       "      <td>80</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Beeswax</th>\n",
       "      <td>805</td>\n",
       "      <td>1</td>\n",
       "      <td>23</td>\n",
       "      <td>225</td>\n",
       "      <td>Arts</td>\n",
       "      <td>2005</td>\n",
       "      <td>2.0</td>\n",
       "      <td>70</td>\n",
       "      <td>15</td>\n",
       "      <td>AoE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Vanguard</th>\n",
       "      <th>Vigil</th>\n",
       "      <td>542</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>154</td>\n",
       "      <td>Physical</td>\n",
       "      <td>1755</td>\n",
       "      <td>1.0</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vigna</th>\n",
       "      <td>618</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>351</td>\n",
       "      <td>Physical</td>\n",
       "      <td>1845</td>\n",
       "      <td>1.0</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wild Mane</th>\n",
       "      <td>628</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>372</td>\n",
       "      <td>Physical</td>\n",
       "      <td>2225</td>\n",
       "      <td>1.0</td>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yato</th>\n",
       "      <td>262</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>192</td>\n",
       "      <td>Physical</td>\n",
       "      <td>1030</td>\n",
       "      <td>1.05</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zima</th>\n",
       "      <td>470</td>\n",
       "      <td>2</td>\n",
       "      <td>13</td>\n",
       "      <td>420</td>\n",
       "      <td>Physical</td>\n",
       "      <td>2150</td>\n",
       "      <td>1.05</td>\n",
       "      <td>70</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>269 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    ATK BLOCK COST  DEF       DMG    HP INTERVAL REDEPLOY RES  \\\n",
       "ROLE     Operators                                                              \n",
       "Caster   12F        482     1   24   50      Arts  1461      2.9       70  10   \n",
       "         Absinthe   703     1   22  124      Arts  1420      1.6       80  20   \n",
       "         Amiya      682     1   20  121      Arts  1680      1.6       70  20   \n",
       "         Astgenne   705     1   34  122      Arts  1440      2.3       80  20   \n",
       "         Beeswax    805     1   23  225      Arts  2005      2.0       70  15   \n",
       "...                 ...   ...  ...  ...       ...   ...      ...      ...  ..   \n",
       "Vanguard Vigil      542     1   17  154  Physical  1755      1.0       70   0   \n",
       "         Vigna      618     1   11  351  Physical  1845      1.0       70   0   \n",
       "         Wild Mane  628     1   14  372  Physical  2225      1.0       80   0   \n",
       "         Yato       262     2    7  192  Physical  1030     1.05       70   0   \n",
       "         Zima       470     2   13  420  Physical  2150     1.05       70   0   \n",
       "\n",
       "                   TARGET  \n",
       "ROLE     Operators         \n",
       "Caster   12F          AoE  \n",
       "         Absinthe       1  \n",
       "         Amiya          1  \n",
       "         Astgenne       1  \n",
       "         Beeswax      AoE  \n",
       "...                   ...  \n",
       "Vanguard Vigil          1  \n",
       "         Vigna          1  \n",
       "         Wild Mane      1  \n",
       "         Yato           1  \n",
       "         Zima           1  \n",
       "\n",
       "[269 rows x 10 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "OperatorDFTidy = OperatorDF.pivot_table(index=['ROLE', 'Operators'],aggfunc=lambda x: ''.join(str(v) for v in x))\n",
    "OperatorDFTidy"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "61ea7127",
   "metadata": {},
   "source": [
    "---\n",
    "### Getting the Banner Data:\n",
    "\n",
    "##### Variables:\n",
    "- **BannerHeaders:** Grabs the header from the table within the HTML of the website using xpath\n",
    "- **BannerName:** Grabs the name of the banners within the HTML of the website using xpath\n",
    "- **CNDate:** Grabs the date of the banners when it was released in China within the HTML using xpath\n",
    "- **ENDate:** Grabs the date of the banners when it was released globally within the HTML using xpath\n",
    "    - **NOTE:** Only works if there are values within the table. Currently the table doesnt contain dates for upcoming banners for global. So the code is commentend out\n",
    "- **FinalOperators:** Stores all of the characters from the List of List and converts it into a List of strings.\n",
    "    - **NOTE:** This variable is in relation with **FeaturedOperators** as that variable contains the List of List of all the character names using procedural xpath.\n",
    "    \n",
    "##### Why do we need this?\n",
    "- We need all of these variables to help us answer our proposal question **Are some characters better than others based on their stats, and how much would the player have to invest in the game to collect that character?** In order to determine how much the player has to invest into the game to obtain a specific operator they want, we have to obtain the dates and retrieve the amount of days or in-game currency they have have to spend to obtain this character.\n",
    "\n",
    "##### How do we grab the data?\n",
    "- The way we grab this data is mainly by obtaining the URL, then we parse it, after that for the headers we can use a xpath to retrieve the data. For everything else, we can use List comprehensions and xpath to properly store the data into a list while also cleaning it if nessecary. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "bc0155af",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Event Banner', 'Banner Date (CN)', 'Banner Date (NA)', 'Featured Characters']\n"
     ]
    }
   ],
   "source": [
    "# Grabbing the headers\n",
    "BannerHeaders = BannerTree.xpath(\"//table[@class = 'views-table views-view-table cols-4']/thead/tr/th/text()\")\n",
    "print(BannerHeaders)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "aeeb2ea5",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Blue Poison, Hibiscus the Purifier, Ebenholz', 'Ptilopsis, Greyy the Lightningbearer, Dorothy', 'Gavial the Invincible, Pozyomka (Позёмка), Cantabile', 'Skyfire, Magallan, Bagpipe, Shamare, Mr. Nothing, Toddifons, Passenger, Carnelian, La Pluma, Mulberry', 'Franka, Proviso, Młynar', 'Jackie, Mudrock, Whisperain, Roberta, Mulberry, Saileach, Chestnut, Rockrock, Horn', 'Cliffheart, Totter, Paprika, Stainless', 'Texas the Omertosa, Penance, Lunacub', 'Sora, Sesa, Ayerscarpe, Suzuran, Eunectes, Ashlock, Fartooth, Corroserum, Goldenglow, Kazemaru', 'Pramanix, Aurora, Gnosis']\n"
     ]
    }
   ],
   "source": [
    "BannerName = [i.strip() for i in BannerTree.xpath(\"/.//td[@headers = 'view-field-event-banner-table-column']/a/div/text()\") if i != '\\n' ]\n",
    "CNDate = [i for i in BannerTree.xpath(\"/.//td[@headers = 'view-field-cn-end-date-table-column']/time/text()\")]\n",
    "CNDate = [' - '.join(CNDate[i : i+2]) for i in range(0, len(CNDate), +2)]\n",
    "\n",
    "# ENDate wont work because no values are under the NA Date\n",
    "# It will work if there are dates\n",
    "#ENDate = [i for i in BannerTree.xpath(\"/.//td[@headers = 'view-field-start-time-table-column]/time/text()\")]\n",
    "#ENDate = [' - '.join(CNDate[i : i+2]) for i in range(0, len(CNDate), +2)]\n",
    "\n",
    "PlaceHolder = []\n",
    "FeaturedOperators = []\n",
    "FinalOperators = []\n",
    "for child in BannerTree.xpath(\"/.//td[@headers = 'view-field-featured-characters-table-column']\"):\n",
    "    for descendants in child:\n",
    "        PlaceHolder.append(descendants.text)\n",
    "    FeaturedOperators.append(PlaceHolder)\n",
    "    PlaceHolder = []\n",
    "for i in FeaturedOperators: #Converts LoL to a List that contains strings\n",
    "    pH = ', '.join(i)\n",
    "    FinalOperators.append(pH)\n",
    "print(FinalOperators)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8532134",
   "metadata": {},
   "source": [
    "---\n",
    "### Creating the Dictionary & Dataframe:\n",
    "\n",
    "When creating this dictionary we can refer to the headers we grabbed earlier and use them as our keys. However if you notice to our dictionary you might wonder where is **Banner Data (EN)**. Since we dont have any values for that due to the website not inputting any we can simply not include it. So for the keys we can use the headers and the values we can input BannerName, CNDate, and FinalOperators as those variables contain Lists of the data we are looking for"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ef8a0948",
   "metadata": {
    "scrolled": false
   },
   "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>Event Banner</th>\n",
       "      <th>Banner Date (CN)</th>\n",
       "      <th>Featured Characters</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dissonanzen</td>\n",
       "      <td>2022-06-09 - 2022-06-23</td>\n",
       "      <td>Blue Poison, Hibiscus the Purifier, Ebenholz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Pathfinder of Sands</td>\n",
       "      <td>2022-07-05 - 2022-07-19</td>\n",
       "      <td>Ptilopsis, Greyy the Lightningbearer, Dorothy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Great Axe and Pen Nib - [Summer] Series Limite...</td>\n",
       "      <td>2022-08-11 - 2022-08-25</td>\n",
       "      <td>Gavial the Invincible, Pozyomka (Позёмка), Can...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Joint Operation 7</td>\n",
       "      <td>2022-08-25 - 2022-09-08</td>\n",
       "      <td>Skyfire, Magallan, Bagpipe, Shamare, Mr. Nothi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Never Vowed</td>\n",
       "      <td>2022-09-08 - 2022-09-22</td>\n",
       "      <td>Franka, Proviso, Młynar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>The Front That Was</td>\n",
       "      <td>2022-09-27 - 2022-10-11</td>\n",
       "      <td>Jackie, Mudrock, Whisperain, Roberta, Mulberry...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Bearing and Sparks</td>\n",
       "      <td>2022-10-11 - 2022-10-25</td>\n",
       "      <td>Cliffheart, Totter, Paprika, Stainless</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Chop the Thorns: Open Circuits - Celebration S...</td>\n",
       "      <td>2022-11-01 - 2022-11-15</td>\n",
       "      <td>Texas the Omertosa, Penance, Lunacub</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Joint Operation 8</td>\n",
       "      <td>2022-11-15 - 2022-11-29</td>\n",
       "      <td>Sora, Sesa, Ayerscarpe, Suzuran, Eunectes, Ash...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Edelweiss' Vow Rerun</td>\n",
       "      <td>2022-12-01 - 2022-12-15</td>\n",
       "      <td>Pramanix, Aurora, Gnosis</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        Event Banner         Banner Date (CN)  \\\n",
       "0                                        Dissonanzen  2022-06-09 - 2022-06-23   \n",
       "1                                Pathfinder of Sands  2022-07-05 - 2022-07-19   \n",
       "2  Great Axe and Pen Nib - [Summer] Series Limite...  2022-08-11 - 2022-08-25   \n",
       "3                                  Joint Operation 7  2022-08-25 - 2022-09-08   \n",
       "4                                        Never Vowed  2022-09-08 - 2022-09-22   \n",
       "5                                 The Front That Was  2022-09-27 - 2022-10-11   \n",
       "6                                 Bearing and Sparks  2022-10-11 - 2022-10-25   \n",
       "7  Chop the Thorns: Open Circuits - Celebration S...  2022-11-01 - 2022-11-15   \n",
       "8                                  Joint Operation 8  2022-11-15 - 2022-11-29   \n",
       "9                               Edelweiss' Vow Rerun  2022-12-01 - 2022-12-15   \n",
       "\n",
       "                                 Featured Characters  \n",
       "0       Blue Poison, Hibiscus the Purifier, Ebenholz  \n",
       "1      Ptilopsis, Greyy the Lightningbearer, Dorothy  \n",
       "2  Gavial the Invincible, Pozyomka (Позёмка), Can...  \n",
       "3  Skyfire, Magallan, Bagpipe, Shamare, Mr. Nothi...  \n",
       "4                            Franka, Proviso, Młynar  \n",
       "5  Jackie, Mudrock, Whisperain, Roberta, Mulberry...  \n",
       "6             Cliffheart, Totter, Paprika, Stainless  \n",
       "7               Texas the Omertosa, Penance, Lunacub  \n",
       "8  Sora, Sesa, Ayerscarpe, Suzuran, Eunectes, Ash...  \n",
       "9                           Pramanix, Aurora, Gnosis  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bannerDict = {BannerHeaders[0]: BannerName, BannerHeaders[1]: CNDate, BannerHeaders[3]: FinalOperators}\n",
    "\n",
    "bannerDF = pd.DataFrame.from_dict(bannerDict)\n",
    "\n",
    "\n",
    "OperatorsDF = pd.DataFrame(FeaturedOperators)\n",
    "bannerDF"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28fd320c",
   "metadata": {},
   "source": [
    "---\n",
    "### Tidying the Dataframe:\n",
    "After accessing **OperatorDF** we can clearly see that this dictionary is not tidy at all. There is no index, some of the columns are unclear. In order to fix this we can do a simple pivot and tidy up our data so we can have the respective index that translate to the data we want of that specific character\n",
    "\n",
    "\n",
    "- **Independent Variable:** Event Banner\n",
    "- **Dependent Variable:** Banner Date(CN), Featured Characters\n",
    "\n",
    "The independent variable we can see is **Event Banner** because of this we can access all the data of that respective banner as it will give us the date when it release, and the featured operators that the banner came with."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "2cda6118",
   "metadata": {
    "scrolled": false
   },
   "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>bannerName</th>\n",
       "      <th>ChineseDate</th>\n",
       "      <th>characterID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dissonanzen</td>\n",
       "      <td>2022-06-09 - 2022-06-23</td>\n",
       "      <td>Blue Poison</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Pathfinder of Sands</td>\n",
       "      <td>2022-07-05 - 2022-07-19</td>\n",
       "      <td>Ptilopsis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Great Axe and Pen Nib - [Summer] Series Limite...</td>\n",
       "      <td>2022-08-11 - 2022-08-25</td>\n",
       "      <td>Gavial the Invincible</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Joint Operation 7</td>\n",
       "      <td>2022-08-25 - 2022-09-08</td>\n",
       "      <td>Skyfire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Never Vowed</td>\n",
       "      <td>2022-09-08 - 2022-09-22</td>\n",
       "      <td>Franka</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>The Front That Was</td>\n",
       "      <td>2022-09-27 - 2022-10-11</td>\n",
       "      <td>Jackie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Bearing and Sparks</td>\n",
       "      <td>2022-10-11 - 2022-10-25</td>\n",
       "      <td>Cliffheart</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Chop the Thorns: Open Circuits - Celebration S...</td>\n",
       "      <td>2022-11-01 - 2022-11-15</td>\n",
       "      <td>Texas the Omertosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Joint Operation 8</td>\n",
       "      <td>2022-11-15 - 2022-11-29</td>\n",
       "      <td>Sora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Edelweiss' Vow Rerun</td>\n",
       "      <td>2022-12-01 - 2022-12-15</td>\n",
       "      <td>Pramanix</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Dissonanzen</td>\n",
       "      <td>2022-06-09 - 2022-06-23</td>\n",
       "      <td>Hibiscus the Purifier</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Pathfinder of Sands</td>\n",
       "      <td>2022-07-05 - 2022-07-19</td>\n",
       "      <td>Greyy the Lightningbearer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Great Axe and Pen Nib - [Summer] Series Limite...</td>\n",
       "      <td>2022-08-11 - 2022-08-25</td>\n",
       "      <td>Pozyomka (Позёмка)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Joint Operation 7</td>\n",
       "      <td>2022-08-25 - 2022-09-08</td>\n",
       "      <td>Magallan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Never Vowed</td>\n",
       "      <td>2022-09-08 - 2022-09-22</td>\n",
       "      <td>Proviso</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>The Front That Was</td>\n",
       "      <td>2022-09-27 - 2022-10-11</td>\n",
       "      <td>Mudrock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Bearing and Sparks</td>\n",
       "      <td>2022-10-11 - 2022-10-25</td>\n",
       "      <td>Totter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Chop the Thorns: Open Circuits - Celebration S...</td>\n",
       "      <td>2022-11-01 - 2022-11-15</td>\n",
       "      <td>Penance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Joint Operation 8</td>\n",
       "      <td>2022-11-15 - 2022-11-29</td>\n",
       "      <td>Sesa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Edelweiss' Vow Rerun</td>\n",
       "      <td>2022-12-01 - 2022-12-15</td>\n",
       "      <td>Aurora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Dissonanzen</td>\n",
       "      <td>2022-06-09 - 2022-06-23</td>\n",
       "      <td>Ebenholz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Pathfinder of Sands</td>\n",
       "      <td>2022-07-05 - 2022-07-19</td>\n",
       "      <td>Dorothy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Great Axe and Pen Nib - [Summer] Series Limite...</td>\n",
       "      <td>2022-08-11 - 2022-08-25</td>\n",
       "      <td>Cantabile</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Joint Operation 7</td>\n",
       "      <td>2022-08-25 - 2022-09-08</td>\n",
       "      <td>Bagpipe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Never Vowed</td>\n",
       "      <td>2022-09-08 - 2022-09-22</td>\n",
       "      <td>Młynar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>The Front That Was</td>\n",
       "      <td>2022-09-27 - 2022-10-11</td>\n",
       "      <td>Whisperain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Bearing and Sparks</td>\n",
       "      <td>2022-10-11 - 2022-10-25</td>\n",
       "      <td>Paprika</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Chop the Thorns: Open Circuits - Celebration S...</td>\n",
       "      <td>2022-11-01 - 2022-11-15</td>\n",
       "      <td>Lunacub</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Joint Operation 8</td>\n",
       "      <td>2022-11-15 - 2022-11-29</td>\n",
       "      <td>Ayerscarpe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Edelweiss' Vow Rerun</td>\n",
       "      <td>2022-12-01 - 2022-12-15</td>\n",
       "      <td>Gnosis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Joint Operation 7</td>\n",
       "      <td>2022-08-25 - 2022-09-08</td>\n",
       "      <td>Shamare</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>The Front That Was</td>\n",
       "      <td>2022-09-27 - 2022-10-11</td>\n",
       "      <td>Roberta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Bearing and Sparks</td>\n",
       "      <td>2022-10-11 - 2022-10-25</td>\n",
       "      <td>Stainless</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Joint Operation 8</td>\n",
       "      <td>2022-11-15 - 2022-11-29</td>\n",
       "      <td>Suzuran</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Joint Operation 7</td>\n",
       "      <td>2022-08-25 - 2022-09-08</td>\n",
       "      <td>Mr. Nothing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>The Front That Was</td>\n",
       "      <td>2022-09-27 - 2022-10-11</td>\n",
       "      <td>Mulberry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>Joint Operation 8</td>\n",
       "      <td>2022-11-15 - 2022-11-29</td>\n",
       "      <td>Eunectes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>Joint Operation 7</td>\n",
       "      <td>2022-08-25 - 2022-09-08</td>\n",
       "      <td>Toddifons</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>The Front That Was</td>\n",
       "      <td>2022-09-27 - 2022-10-11</td>\n",
       "      <td>Saileach</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>Joint Operation 8</td>\n",
       "      <td>2022-11-15 - 2022-11-29</td>\n",
       "      <td>Ashlock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>Joint Operation 7</td>\n",
       "      <td>2022-08-25 - 2022-09-08</td>\n",
       "      <td>Passenger</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>The Front That Was</td>\n",
       "      <td>2022-09-27 - 2022-10-11</td>\n",
       "      <td>Chestnut</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>Joint Operation 8</td>\n",
       "      <td>2022-11-15 - 2022-11-29</td>\n",
       "      <td>Fartooth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>Joint Operation 7</td>\n",
       "      <td>2022-08-25 - 2022-09-08</td>\n",
       "      <td>Carnelian</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>The Front That Was</td>\n",
       "      <td>2022-09-27 - 2022-10-11</td>\n",
       "      <td>Rockrock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>Joint Operation 8</td>\n",
       "      <td>2022-11-15 - 2022-11-29</td>\n",
       "      <td>Corroserum</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>Joint Operation 7</td>\n",
       "      <td>2022-08-25 - 2022-09-08</td>\n",
       "      <td>La Pluma</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>The Front That Was</td>\n",
       "      <td>2022-09-27 - 2022-10-11</td>\n",
       "      <td>Horn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>Joint Operation 8</td>\n",
       "      <td>2022-11-15 - 2022-11-29</td>\n",
       "      <td>Goldenglow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>Joint Operation 7</td>\n",
       "      <td>2022-08-25 - 2022-09-08</td>\n",
       "      <td>Mulberry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>Joint Operation 8</td>\n",
       "      <td>2022-11-15 - 2022-11-29</td>\n",
       "      <td>Kazemaru</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           bannerName  \\\n",
       "0                                         Dissonanzen   \n",
       "1                                 Pathfinder of Sands   \n",
       "2   Great Axe and Pen Nib - [Summer] Series Limite...   \n",
       "3                                   Joint Operation 7   \n",
       "4                                         Never Vowed   \n",
       "5                                  The Front That Was   \n",
       "6                                  Bearing and Sparks   \n",
       "7   Chop the Thorns: Open Circuits - Celebration S...   \n",
       "8                                   Joint Operation 8   \n",
       "9                                Edelweiss' Vow Rerun   \n",
       "10                                        Dissonanzen   \n",
       "11                                Pathfinder of Sands   \n",
       "12  Great Axe and Pen Nib - [Summer] Series Limite...   \n",
       "13                                  Joint Operation 7   \n",
       "14                                        Never Vowed   \n",
       "15                                 The Front That Was   \n",
       "16                                 Bearing and Sparks   \n",
       "17  Chop the Thorns: Open Circuits - Celebration S...   \n",
       "18                                  Joint Operation 8   \n",
       "19                               Edelweiss' Vow Rerun   \n",
       "20                                        Dissonanzen   \n",
       "21                                Pathfinder of Sands   \n",
       "22  Great Axe and Pen Nib - [Summer] Series Limite...   \n",
       "23                                  Joint Operation 7   \n",
       "24                                        Never Vowed   \n",
       "25                                 The Front That Was   \n",
       "26                                 Bearing and Sparks   \n",
       "27  Chop the Thorns: Open Circuits - Celebration S...   \n",
       "28                                  Joint Operation 8   \n",
       "29                               Edelweiss' Vow Rerun   \n",
       "33                                  Joint Operation 7   \n",
       "35                                 The Front That Was   \n",
       "36                                 Bearing and Sparks   \n",
       "38                                  Joint Operation 8   \n",
       "43                                  Joint Operation 7   \n",
       "45                                 The Front That Was   \n",
       "48                                  Joint Operation 8   \n",
       "53                                  Joint Operation 7   \n",
       "55                                 The Front That Was   \n",
       "58                                  Joint Operation 8   \n",
       "63                                  Joint Operation 7   \n",
       "65                                 The Front That Was   \n",
       "68                                  Joint Operation 8   \n",
       "73                                  Joint Operation 7   \n",
       "75                                 The Front That Was   \n",
       "78                                  Joint Operation 8   \n",
       "83                                  Joint Operation 7   \n",
       "85                                 The Front That Was   \n",
       "88                                  Joint Operation 8   \n",
       "93                                  Joint Operation 7   \n",
       "98                                  Joint Operation 8   \n",
       "\n",
       "                ChineseDate                characterID  \n",
       "0   2022-06-09 - 2022-06-23                Blue Poison  \n",
       "1   2022-07-05 - 2022-07-19                  Ptilopsis  \n",
       "2   2022-08-11 - 2022-08-25      Gavial the Invincible  \n",
       "3   2022-08-25 - 2022-09-08                    Skyfire  \n",
       "4   2022-09-08 - 2022-09-22                     Franka  \n",
       "5   2022-09-27 - 2022-10-11                     Jackie  \n",
       "6   2022-10-11 - 2022-10-25                 Cliffheart  \n",
       "7   2022-11-01 - 2022-11-15         Texas the Omertosa  \n",
       "8   2022-11-15 - 2022-11-29                       Sora  \n",
       "9   2022-12-01 - 2022-12-15                   Pramanix  \n",
       "10  2022-06-09 - 2022-06-23      Hibiscus the Purifier  \n",
       "11  2022-07-05 - 2022-07-19  Greyy the Lightningbearer  \n",
       "12  2022-08-11 - 2022-08-25         Pozyomka (Позёмка)  \n",
       "13  2022-08-25 - 2022-09-08                   Magallan  \n",
       "14  2022-09-08 - 2022-09-22                    Proviso  \n",
       "15  2022-09-27 - 2022-10-11                    Mudrock  \n",
       "16  2022-10-11 - 2022-10-25                     Totter  \n",
       "17  2022-11-01 - 2022-11-15                    Penance  \n",
       "18  2022-11-15 - 2022-11-29                       Sesa  \n",
       "19  2022-12-01 - 2022-12-15                     Aurora  \n",
       "20  2022-06-09 - 2022-06-23                   Ebenholz  \n",
       "21  2022-07-05 - 2022-07-19                    Dorothy  \n",
       "22  2022-08-11 - 2022-08-25                  Cantabile  \n",
       "23  2022-08-25 - 2022-09-08                    Bagpipe  \n",
       "24  2022-09-08 - 2022-09-22                     Młynar  \n",
       "25  2022-09-27 - 2022-10-11                 Whisperain  \n",
       "26  2022-10-11 - 2022-10-25                    Paprika  \n",
       "27  2022-11-01 - 2022-11-15                    Lunacub  \n",
       "28  2022-11-15 - 2022-11-29                 Ayerscarpe  \n",
       "29  2022-12-01 - 2022-12-15                     Gnosis  \n",
       "33  2022-08-25 - 2022-09-08                    Shamare  \n",
       "35  2022-09-27 - 2022-10-11                    Roberta  \n",
       "36  2022-10-11 - 2022-10-25                  Stainless  \n",
       "38  2022-11-15 - 2022-11-29                    Suzuran  \n",
       "43  2022-08-25 - 2022-09-08                Mr. Nothing  \n",
       "45  2022-09-27 - 2022-10-11                   Mulberry  \n",
       "48  2022-11-15 - 2022-11-29                   Eunectes  \n",
       "53  2022-08-25 - 2022-09-08                  Toddifons  \n",
       "55  2022-09-27 - 2022-10-11                   Saileach  \n",
       "58  2022-11-15 - 2022-11-29                    Ashlock  \n",
       "63  2022-08-25 - 2022-09-08                  Passenger  \n",
       "65  2022-09-27 - 2022-10-11                   Chestnut  \n",
       "68  2022-11-15 - 2022-11-29                   Fartooth  \n",
       "73  2022-08-25 - 2022-09-08                  Carnelian  \n",
       "75  2022-09-27 - 2022-10-11                   Rockrock  \n",
       "78  2022-11-15 - 2022-11-29                 Corroserum  \n",
       "83  2022-08-25 - 2022-09-08                   La Pluma  \n",
       "85  2022-09-27 - 2022-10-11                       Horn  \n",
       "88  2022-11-15 - 2022-11-29                 Goldenglow  \n",
       "93  2022-08-25 - 2022-09-08                   Mulberry  \n",
       "98  2022-11-15 - 2022-11-29                   Kazemaru  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bannerDFTidy = bannerDF.copy()\n",
    "FCSeries = bannerDFTidy.pop(\"Featured Characters\")\n",
    "\n",
    "bannerDFTidy = pd.concat([bannerDFTidy, OperatorsDF], axis=1)\n",
    "\n",
    "bdfTidyOp = bannerDFTidy.melt(id_vars=\"Event Banner\", value_vars=[0, 1, 2, 3, 4, 5, 6, 7, 8, 9], value_name = \"Operator Name\").drop(\"variable\", axis=1)\n",
    "bdfTidyDate = bannerDFTidy.melt(id_vars=\"Banner Date (CN)\", value_name=\"operator Name\").drop(\"variable\", axis=1)\n",
    "bannerDFTidy = pd.concat([bdfTidyDate, bdfTidyOp], axis=1)\n",
    "bannerDFTidy = bannerDFTidy.iloc[:, [2, 0, 3]]\n",
    "bannerDFTidy = bannerDFTidy.dropna()\n",
    "bannerDFTidy = bannerDFTidy.rename(columns = {'Event Banner':'bannerName', 'Banner Date (CN)':'ChineseDate', 'Operator Name': 'characterID'})\n",
    "\n",
    "bannerDFTidy.head(1000)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30227088",
   "metadata": {},
   "source": [
    "---\n",
    "### Getting the Popular Characters Data:\n",
    "\n",
    "##### Variables:\n",
    "- **PopularChar:** Grabs the popular characters from the HTML and stores it into a List\n",
    "    \n",
    "##### Why do we need this?\n",
    "- The reason we are grabbing this data is because maybe a user wants to look at the popular characters of the day and make their mission to unlock one of these Operators. Relating this back to our proposal question **Are some characters better than others based on their stats, and how much would the player have to invest in the game to collect that character?** Grabbing this data will help the user possibly make them have a better judgment on who to invest for\n",
    "\n",
    "##### How do we grab the data?\n",
    "- We grab this data by using xpath and list comprehensions so we can store all of the data within a List. We also use .strip() within the list comprehension so we can possibly get rid of any malicious characters that will hinder our data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "05dd67d4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Erato', 'Skadi', 'Irene', 'Surtr', 'Texas the Omertosa', 'Specter the Unchained', 'Gladiia', 'Lumen', 'Mudrock', 'La Pluma']\n"
     ]
    }
   ],
   "source": [
    "PopularChar = [i.strip() for i in BannerTree.xpath(\"/.//div[@class = 'popular-items-block popular-block']/ul/li/a/span[@class = 'pages-ranking-title']/text()\")]\n",
    "print(PopularChar)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "102505a3",
   "metadata": {},
   "source": [
    "---\n",
    "### Assembling the Dataframe:\n",
    "\n",
    "When creating this Dataframe since it isn't as complex as the ones we have made before all we really need to do is call a **pd.DataFrame** with our List and increase the index by 1 because we are showing the top 10 operators of the day. After that we can simply just show the data in our Dataframe. This Dataframe doesn't need to be tidy at all because all were representing is one column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "b842b703",
   "metadata": {
    "scrolled": true
   },
   "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>Popular Operators Today</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Erato</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Skadi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Irene</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Surtr</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Texas the Omertosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Specter the Unchained</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Gladiia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Lumen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Mudrock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>La Pluma</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Popular Operators Today\n",
       "1                    Erato\n",
       "2                    Skadi\n",
       "3                    Irene\n",
       "4                    Surtr\n",
       "5       Texas the Omertosa\n",
       "6    Specter the Unchained\n",
       "7                  Gladiia\n",
       "8                    Lumen\n",
       "9                  Mudrock\n",
       "10                La Pluma"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PopularOperatorDF = pd.DataFrame(PopularChar, columns=['Popular Operators Today'])\n",
    "PopularOperatorDF.index += 1 \n",
    "PopularOperatorDF"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "61eda858",
   "metadata": {},
   "source": [
    "### My intention with all of this data\n",
    "\n",
    "With all of this data I intend to first answer the question **If some characters are better than others based on their stats, and how much would the player have to invest in the game to acquire that character?** While also creating functions that properly apply the foundation to assure that the proposal question is answered. Also further representing this data within a SQL database will provide a better understanding to see if the data has any relation with one another. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc52ed19",
   "metadata": {},
   "source": [
    "---\n",
    "## Assembling SQL\n",
    "\n",
    "Now that we have all of our data within their respective pandas dataframe we are able to transfer the data to a SQL database that will properly store all of the data within a single .db file"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "786304ee",
   "metadata": {},
   "source": [
    "## Getting a connection\n",
    "\n",
    "In order to get our connection we have created a cred.json file that stores all of the information of the database from which we will be working in. After that we will be utilizing both **getsqlite_creds** and **buildConnectionString** to retrieve the creds.json file and to build a SQL connection based from the file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "c1842f4e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def getsqlite_creds(dirname=\".\",filename=\"creds.json\",source=\"sqlite\"):\n",
    "    \"\"\" Using directory and filename parameters, open a credentials file\n",
    "        and obtain the two parts needed for a connection string to\n",
    "        a local provider using the \"sqlite\" dictionary within\n",
    "        an outer dictionary.  \n",
    "        \n",
    "        Return a scheme and a dbfile\n",
    "    \"\"\"\n",
    "    assert os.path.isfile(os.path.join(dirname, filename))\n",
    "    with open(os.path.join(dirname, filename)) as f:\n",
    "        D = json.load(f)\n",
    "    sqlite = D[source]\n",
    "    return sqlite[\"scheme\"], sqlite[\"dbdir\"], sqlite[\"database\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "3cb5cdaa",
   "metadata": {},
   "outputs": [],
   "source": [
    "def buildConnectionString(source):\n",
    "    scheme, dbdir, database = getsqlite_creds(source=source)\n",
    "    template = '{}:///{}/{}.db'\n",
    "    return template.format(scheme, dbdir, database)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3823b2ef",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "ca8cb1ce",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connection string: sqlite:///dbfile/arknights.db\n"
     ]
    }
   ],
   "source": [
    "# Build the conection string\n",
    "cstring = buildConnectionString(\"sqlite_arknights\")\n",
    "print(\"Connection string:\", cstring)\n",
    "\n",
    "# Connect to the database\n",
    "engine = sa.create_engine(cstring)\n",
    "connection = engine.connect()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b705299d",
   "metadata": {},
   "source": [
    "---\n",
    "### Creating Banners SQL Table\n",
    "\n",
    "In order to store all the data from **bannerDFTidy** we have to create a SQL table so that all of the values within the dictionary can be safely transferred to the SQL database. As we create this table within SQL we can first declare the constraints of the specific values.\n",
    "- **bannerName : CHAR & NOT NULL**\n",
    "    - The reason bannerName is CHAR and NOT NULL is because 1)The Banner names are always in a string format and 2) The reason it isn't null is because whenever information within this kind of game gets leaked the name of the banner is always revealed hence we dont have to worry about it ever being NULL\n",
    "- **ChineseDate : CHAR**\n",
    "    - The reason ChineseDate is CHAR and not a DATE format is because the way it was written within the column and because it presents a START date and a END date where with Date constraints only 1 day is allowed\n",
    "- **characterID : CHAR**\n",
    "    - The reason characterID is CHAR is because all of the characters are in string format. With this if we eventually merge or join other tables this key value will be important\n",
    "    \n",
    "### Does it adhere to the principles of good database design?\n",
    "Yes it does! Each column represent their own distinctive value, the databases are divided so our information are into subject-based tables. Our information also provides access to use inner joins from table to table. The attributes are used strictly for identification purposes such as identifying an operator and their stats! Overrall this database does apply with the principles of good database design!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "48fac3b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "bannerSQLTable = \"\"\"\n",
    "CREATE TABLE IF NOT EXISTS banners (\n",
    "    bannerName CHAR(100) NOT NULL,\n",
    "    ChineseDate CHAR,\n",
    "    characterID CHAR(50) NOT NULL,\n",
    "    PRIMARY KEY(bannerName, characterID))  \n",
    "\"\"\" #This wont work if you set the above as PRIMARY KEY()\n",
    "\n",
    "# Execute the statement\n",
    "try:\n",
    "    connection.execute(bannerSQLTable)\n",
    "except sa.exc.SQLAlchemyError as err:\n",
    "    print(\"CREATE of banners failed:\", str(err))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "e245534c",
   "metadata": {
    "scrolled": false
   },
   "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>bannerName</th>\n",
       "      <th>ChineseDate</th>\n",
       "      <th>characterID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dissonanzen</td>\n",
       "      <td>2022-06-09 - 2022-06-23</td>\n",
       "      <td>Blue Poison</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Pathfinder of Sands</td>\n",
       "      <td>2022-07-05 - 2022-07-19</td>\n",
       "      <td>Ptilopsis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Great Axe and Pen Nib - [Summer] Series Limite...</td>\n",
       "      <td>2022-08-11 - 2022-08-25</td>\n",
       "      <td>Gavial the Invincible</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Joint Operation 7</td>\n",
       "      <td>2022-08-25 - 2022-09-08</td>\n",
       "      <td>Skyfire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Never Vowed</td>\n",
       "      <td>2022-09-08 - 2022-09-22</td>\n",
       "      <td>Franka</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>The Front That Was</td>\n",
       "      <td>2022-09-27 - 2022-10-11</td>\n",
       "      <td>Jackie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Bearing and Sparks</td>\n",
       "      <td>2022-10-11 - 2022-10-25</td>\n",
       "      <td>Cliffheart</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Chop the Thorns: Open Circuits - Celebration S...</td>\n",
       "      <td>2022-11-01 - 2022-11-15</td>\n",
       "      <td>Texas the Omertosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Joint Operation 8</td>\n",
       "      <td>2022-11-15 - 2022-11-29</td>\n",
       "      <td>Sora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Edelweiss' Vow Rerun</td>\n",
       "      <td>2022-12-01 - 2022-12-15</td>\n",
       "      <td>Pramanix</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Dissonanzen</td>\n",
       "      <td>2022-06-09 - 2022-06-23</td>\n",
       "      <td>Hibiscus the Purifier</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Pathfinder of Sands</td>\n",
       "      <td>2022-07-05 - 2022-07-19</td>\n",
       "      <td>Greyy the Lightningbearer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Great Axe and Pen Nib - [Summer] Series Limite...</td>\n",
       "      <td>2022-08-11 - 2022-08-25</td>\n",
       "      <td>Pozyomka (Позёмка)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Joint Operation 7</td>\n",
       "      <td>2022-08-25 - 2022-09-08</td>\n",
       "      <td>Magallan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Never Vowed</td>\n",
       "      <td>2022-09-08 - 2022-09-22</td>\n",
       "      <td>Proviso</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>The Front That Was</td>\n",
       "      <td>2022-09-27 - 2022-10-11</td>\n",
       "      <td>Mudrock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Bearing and Sparks</td>\n",
       "      <td>2022-10-11 - 2022-10-25</td>\n",
       "      <td>Totter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Chop the Thorns: Open Circuits - Celebration S...</td>\n",
       "      <td>2022-11-01 - 2022-11-15</td>\n",
       "      <td>Penance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Joint Operation 8</td>\n",
       "      <td>2022-11-15 - 2022-11-29</td>\n",
       "      <td>Sesa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Edelweiss' Vow Rerun</td>\n",
       "      <td>2022-12-01 - 2022-12-15</td>\n",
       "      <td>Aurora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Dissonanzen</td>\n",
       "      <td>2022-06-09 - 2022-06-23</td>\n",
       "      <td>Ebenholz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Pathfinder of Sands</td>\n",
       "      <td>2022-07-05 - 2022-07-19</td>\n",
       "      <td>Dorothy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Great Axe and Pen Nib - [Summer] Series Limite...</td>\n",
       "      <td>2022-08-11 - 2022-08-25</td>\n",
       "      <td>Cantabile</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Joint Operation 7</td>\n",
       "      <td>2022-08-25 - 2022-09-08</td>\n",
       "      <td>Bagpipe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Never Vowed</td>\n",
       "      <td>2022-09-08 - 2022-09-22</td>\n",
       "      <td>Młynar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>The Front That Was</td>\n",
       "      <td>2022-09-27 - 2022-10-11</td>\n",
       "      <td>Whisperain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Bearing and Sparks</td>\n",
       "      <td>2022-10-11 - 2022-10-25</td>\n",
       "      <td>Paprika</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Chop the Thorns: Open Circuits - Celebration S...</td>\n",
       "      <td>2022-11-01 - 2022-11-15</td>\n",
       "      <td>Lunacub</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Joint Operation 8</td>\n",
       "      <td>2022-11-15 - 2022-11-29</td>\n",
       "      <td>Ayerscarpe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Edelweiss' Vow Rerun</td>\n",
       "      <td>2022-12-01 - 2022-12-15</td>\n",
       "      <td>Gnosis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Joint Operation 7</td>\n",
       "      <td>2022-08-25 - 2022-09-08</td>\n",
       "      <td>Shamare</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>The Front That Was</td>\n",
       "      <td>2022-09-27 - 2022-10-11</td>\n",
       "      <td>Roberta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Bearing and Sparks</td>\n",
       "      <td>2022-10-11 - 2022-10-25</td>\n",
       "      <td>Stainless</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Joint Operation 8</td>\n",
       "      <td>2022-11-15 - 2022-11-29</td>\n",
       "      <td>Suzuran</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Joint Operation 7</td>\n",
       "      <td>2022-08-25 - 2022-09-08</td>\n",
       "      <td>Mr. Nothing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>The Front That Was</td>\n",
       "      <td>2022-09-27 - 2022-10-11</td>\n",
       "      <td>Mulberry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Joint Operation 8</td>\n",
       "      <td>2022-11-15 - 2022-11-29</td>\n",
       "      <td>Eunectes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Joint Operation 7</td>\n",
       "      <td>2022-08-25 - 2022-09-08</td>\n",
       "      <td>Toddifons</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>The Front That Was</td>\n",
       "      <td>2022-09-27 - 2022-10-11</td>\n",
       "      <td>Saileach</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Joint Operation 8</td>\n",
       "      <td>2022-11-15 - 2022-11-29</td>\n",
       "      <td>Ashlock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Joint Operation 7</td>\n",
       "      <td>2022-08-25 - 2022-09-08</td>\n",
       "      <td>Passenger</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>The Front That Was</td>\n",
       "      <td>2022-09-27 - 2022-10-11</td>\n",
       "      <td>Chestnut</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Joint Operation 8</td>\n",
       "      <td>2022-11-15 - 2022-11-29</td>\n",
       "      <td>Fartooth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Joint Operation 7</td>\n",
       "      <td>2022-08-25 - 2022-09-08</td>\n",
       "      <td>Carnelian</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>The Front That Was</td>\n",
       "      <td>2022-09-27 - 2022-10-11</td>\n",
       "      <td>Rockrock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Joint Operation 8</td>\n",
       "      <td>2022-11-15 - 2022-11-29</td>\n",
       "      <td>Corroserum</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Joint Operation 7</td>\n",
       "      <td>2022-08-25 - 2022-09-08</td>\n",
       "      <td>La Pluma</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>The Front That Was</td>\n",
       "      <td>2022-09-27 - 2022-10-11</td>\n",
       "      <td>Horn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>Joint Operation 8</td>\n",
       "      <td>2022-11-15 - 2022-11-29</td>\n",
       "      <td>Goldenglow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Joint Operation 7</td>\n",
       "      <td>2022-08-25 - 2022-09-08</td>\n",
       "      <td>Mulberry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Joint Operation 8</td>\n",
       "      <td>2022-11-15 - 2022-11-29</td>\n",
       "      <td>Kazemaru</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           bannerName  \\\n",
       "0                                         Dissonanzen   \n",
       "1                                 Pathfinder of Sands   \n",
       "2   Great Axe and Pen Nib - [Summer] Series Limite...   \n",
       "3                                   Joint Operation 7   \n",
       "4                                         Never Vowed   \n",
       "5                                  The Front That Was   \n",
       "6                                  Bearing and Sparks   \n",
       "7   Chop the Thorns: Open Circuits - Celebration S...   \n",
       "8                                   Joint Operation 8   \n",
       "9                                Edelweiss' Vow Rerun   \n",
       "10                                        Dissonanzen   \n",
       "11                                Pathfinder of Sands   \n",
       "12  Great Axe and Pen Nib - [Summer] Series Limite...   \n",
       "13                                  Joint Operation 7   \n",
       "14                                        Never Vowed   \n",
       "15                                 The Front That Was   \n",
       "16                                 Bearing and Sparks   \n",
       "17  Chop the Thorns: Open Circuits - Celebration S...   \n",
       "18                                  Joint Operation 8   \n",
       "19                               Edelweiss' Vow Rerun   \n",
       "20                                        Dissonanzen   \n",
       "21                                Pathfinder of Sands   \n",
       "22  Great Axe and Pen Nib - [Summer] Series Limite...   \n",
       "23                                  Joint Operation 7   \n",
       "24                                        Never Vowed   \n",
       "25                                 The Front That Was   \n",
       "26                                 Bearing and Sparks   \n",
       "27  Chop the Thorns: Open Circuits - Celebration S...   \n",
       "28                                  Joint Operation 8   \n",
       "29                               Edelweiss' Vow Rerun   \n",
       "30                                  Joint Operation 7   \n",
       "31                                 The Front That Was   \n",
       "32                                 Bearing and Sparks   \n",
       "33                                  Joint Operation 8   \n",
       "34                                  Joint Operation 7   \n",
       "35                                 The Front That Was   \n",
       "36                                  Joint Operation 8   \n",
       "37                                  Joint Operation 7   \n",
       "38                                 The Front That Was   \n",
       "39                                  Joint Operation 8   \n",
       "40                                  Joint Operation 7   \n",
       "41                                 The Front That Was   \n",
       "42                                  Joint Operation 8   \n",
       "43                                  Joint Operation 7   \n",
       "44                                 The Front That Was   \n",
       "45                                  Joint Operation 8   \n",
       "46                                  Joint Operation 7   \n",
       "47                                 The Front That Was   \n",
       "48                                  Joint Operation 8   \n",
       "49                                  Joint Operation 7   \n",
       "50                                  Joint Operation 8   \n",
       "\n",
       "                ChineseDate                characterID  \n",
       "0   2022-06-09 - 2022-06-23                Blue Poison  \n",
       "1   2022-07-05 - 2022-07-19                  Ptilopsis  \n",
       "2   2022-08-11 - 2022-08-25      Gavial the Invincible  \n",
       "3   2022-08-25 - 2022-09-08                    Skyfire  \n",
       "4   2022-09-08 - 2022-09-22                     Franka  \n",
       "5   2022-09-27 - 2022-10-11                     Jackie  \n",
       "6   2022-10-11 - 2022-10-25                 Cliffheart  \n",
       "7   2022-11-01 - 2022-11-15         Texas the Omertosa  \n",
       "8   2022-11-15 - 2022-11-29                       Sora  \n",
       "9   2022-12-01 - 2022-12-15                   Pramanix  \n",
       "10  2022-06-09 - 2022-06-23      Hibiscus the Purifier  \n",
       "11  2022-07-05 - 2022-07-19  Greyy the Lightningbearer  \n",
       "12  2022-08-11 - 2022-08-25         Pozyomka (Позёмка)  \n",
       "13  2022-08-25 - 2022-09-08                   Magallan  \n",
       "14  2022-09-08 - 2022-09-22                    Proviso  \n",
       "15  2022-09-27 - 2022-10-11                    Mudrock  \n",
       "16  2022-10-11 - 2022-10-25                     Totter  \n",
       "17  2022-11-01 - 2022-11-15                    Penance  \n",
       "18  2022-11-15 - 2022-11-29                       Sesa  \n",
       "19  2022-12-01 - 2022-12-15                     Aurora  \n",
       "20  2022-06-09 - 2022-06-23                   Ebenholz  \n",
       "21  2022-07-05 - 2022-07-19                    Dorothy  \n",
       "22  2022-08-11 - 2022-08-25                  Cantabile  \n",
       "23  2022-08-25 - 2022-09-08                    Bagpipe  \n",
       "24  2022-09-08 - 2022-09-22                     Młynar  \n",
       "25  2022-09-27 - 2022-10-11                 Whisperain  \n",
       "26  2022-10-11 - 2022-10-25                    Paprika  \n",
       "27  2022-11-01 - 2022-11-15                    Lunacub  \n",
       "28  2022-11-15 - 2022-11-29                 Ayerscarpe  \n",
       "29  2022-12-01 - 2022-12-15                     Gnosis  \n",
       "30  2022-08-25 - 2022-09-08                    Shamare  \n",
       "31  2022-09-27 - 2022-10-11                    Roberta  \n",
       "32  2022-10-11 - 2022-10-25                  Stainless  \n",
       "33  2022-11-15 - 2022-11-29                    Suzuran  \n",
       "34  2022-08-25 - 2022-09-08                Mr. Nothing  \n",
       "35  2022-09-27 - 2022-10-11                   Mulberry  \n",
       "36  2022-11-15 - 2022-11-29                   Eunectes  \n",
       "37  2022-08-25 - 2022-09-08                  Toddifons  \n",
       "38  2022-09-27 - 2022-10-11                   Saileach  \n",
       "39  2022-11-15 - 2022-11-29                    Ashlock  \n",
       "40  2022-08-25 - 2022-09-08                  Passenger  \n",
       "41  2022-09-27 - 2022-10-11                   Chestnut  \n",
       "42  2022-11-15 - 2022-11-29                   Fartooth  \n",
       "43  2022-08-25 - 2022-09-08                  Carnelian  \n",
       "44  2022-09-27 - 2022-10-11                   Rockrock  \n",
       "45  2022-11-15 - 2022-11-29                 Corroserum  \n",
       "46  2022-08-25 - 2022-09-08                   La Pluma  \n",
       "47  2022-09-27 - 2022-10-11                       Horn  \n",
       "48  2022-11-15 - 2022-11-29                 Goldenglow  \n",
       "49  2022-08-25 - 2022-09-08                   Mulberry  \n",
       "50  2022-11-15 - 2022-11-29                   Kazemaru  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bannerDFTidy.to_sql(\"banners\", con=connection, if_exists=\"append\", index=False)\n",
    "df = pd.read_sql_table(\"banners\", connection)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01b43e46",
   "metadata": {},
   "source": [
    "---\n",
    "### Creating Popular Operator SQL Table\n",
    "\n",
    "In order to store all the data from **PopularOperatorDF** we have to create a SQL table so that all of the values within the dictionary can be safely transferred to the SQL database. As we create this table within SQL we can first declare the constraints of the specific values.\n",
    "- **characterID** : CHAR, NOT NULL, PRIMARY KEY\n",
    "    - The reason characterID is CHAR is because all of the characters are in string format. With this if we eventually merge or       join other tables this key value will be important, this is why we also set it as the primary key.\n",
    "    \n",
    "### Does it adhere to the principles of good database design?\n",
    "Yes it does! Each column represent their own distinctive value, the databases are divided so our information are into subject-based tables. Our information also provides access to use inner joins from table to table. The attributes are used strictly for identification purposes such as identifying an operator and their stats! Overrall this database does apply with the principles of good database design!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "ca4892c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "popOpSQLTable = \"\"\"\n",
    "CREATE TABLE IF NOT EXISTS popular (\n",
    "    characterID CHAR(50) NOT NULL PRIMARY KEY)\n",
    "\"\"\"\n",
    "\n",
    "# Execute the statement\n",
    "try:\n",
    "    connection.execute(popOpSQLTable)\n",
    "except sa.exc.SQLAlchemyError as err:\n",
    "    print(\"CREATE of popular failed:\", str(err))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "c010a430",
   "metadata": {
    "scrolled": true
   },
   "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>characterID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Erato</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Skadi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Irene</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Surtr</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Texas the Omertosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Specter the Unchained</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Gladiia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Lumen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Mudrock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>La Pluma</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             characterID\n",
       "0                  Erato\n",
       "1                  Skadi\n",
       "2                  Irene\n",
       "3                  Surtr\n",
       "4     Texas the Omertosa\n",
       "5  Specter the Unchained\n",
       "6                Gladiia\n",
       "7                  Lumen\n",
       "8                Mudrock\n",
       "9               La Pluma"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PopularOperatorDF = PopularOperatorDF.rename(columns = {'Popular Operators Today':'characterID'})\n",
    "PopularOperatorDF.to_sql(\"popular\", con=connection, if_exists=\"append\", index=False)\n",
    "df2 = pd.read_sql_table(\"popular\", connection)\n",
    "\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "fb0ddc37",
   "metadata": {
    "scrolled": false
   },
   "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>characterID</th>\n",
       "      <th>HP</th>\n",
       "      <th>ATK</th>\n",
       "      <th>DEF</th>\n",
       "      <th>COST</th>\n",
       "      <th>RES</th>\n",
       "      <th>BLOCK</th>\n",
       "      <th>REDEPLOY</th>\n",
       "      <th>INTERVAL</th>\n",
       "      <th>TARGET</th>\n",
       "      <th>DMG</th>\n",
       "      <th>ROLE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Vigil</td>\n",
       "      <td>1755</td>\n",
       "      <td>542</td>\n",
       "      <td>154</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>70</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Vanguard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Penance</td>\n",
       "      <td>4655</td>\n",
       "      <td>916</td>\n",
       "      <td>616</td>\n",
       "      <td>36</td>\n",
       "      <td>10</td>\n",
       "      <td>3</td>\n",
       "      <td>70</td>\n",
       "      <td>1.60</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Defender</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Texas the Omertosa</td>\n",
       "      <td>1598</td>\n",
       "      <td>659</td>\n",
       "      <td>320</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>0.93</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Specialist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Stainless</td>\n",
       "      <td>2723</td>\n",
       "      <td>633</td>\n",
       "      <td>461</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>70</td>\n",
       "      <td>1.50</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Supporter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Młynar</td>\n",
       "      <td>4266</td>\n",
       "      <td>385</td>\n",
       "      <td>502</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>70</td>\n",
       "      <td>1.20</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Guard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>264</th>\n",
       "      <td>Yato</td>\n",
       "      <td>1030</td>\n",
       "      <td>262</td>\n",
       "      <td>192</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>70</td>\n",
       "      <td>1.05</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Vanguard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265</th>\n",
       "      <td>'Justice Knight'</td>\n",
       "      <td>595</td>\n",
       "      <td>217</td>\n",
       "      <td>41</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>200</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Sniper</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>266</th>\n",
       "      <td>THRM-EX</td>\n",
       "      <td>1443</td>\n",
       "      <td>350</td>\n",
       "      <td>443</td>\n",
       "      <td>3</td>\n",
       "      <td>50</td>\n",
       "      <td>0</td>\n",
       "      <td>200</td>\n",
       "      <td>0.93</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Specialist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267</th>\n",
       "      <td>Castle-3</td>\n",
       "      <td>1391</td>\n",
       "      <td>413</td>\n",
       "      <td>90</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>200</td>\n",
       "      <td>1.50</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Guard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268</th>\n",
       "      <td>Lancet-2</td>\n",
       "      <td>535</td>\n",
       "      <td>110</td>\n",
       "      <td>27</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>200</td>\n",
       "      <td>2.85</td>\n",
       "      <td>1</td>\n",
       "      <td>Healing</td>\n",
       "      <td>Medic</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>269 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            characterID    HP  ATK  DEF  COST  RES  BLOCK  REDEPLOY  INTERVAL  \\\n",
       "0                 Vigil  1755  542  154    17    0      1        70      1.00   \n",
       "1               Penance  4655  916  616    36   10      3        70      1.60   \n",
       "2    Texas the Omertosa  1598  659  320    10    0      1        18      0.93   \n",
       "3             Stainless  2723  633  461    19    0      2        70      1.50   \n",
       "4                Młynar  4266  385  502    12   15      3        70      1.20   \n",
       "..                  ...   ...  ...  ...   ...  ...    ...       ...       ...   \n",
       "264                Yato  1030  262  192     7    0      2        70      1.05   \n",
       "265    'Justice Knight'   595  217   41     3    0      1       200      1.00   \n",
       "266             THRM-EX  1443  350  443     3   50      0       200      0.93   \n",
       "267            Castle-3  1391  413   90     3    0      1       200      1.50   \n",
       "268            Lancet-2   535  110   27     3    0      1       200      2.85   \n",
       "\n",
       "    TARGET       DMG        ROLE  \n",
       "0        1  Physical    Vanguard  \n",
       "1        1  Physical    Defender  \n",
       "2        1  Physical  Specialist  \n",
       "3        1  Physical   Supporter  \n",
       "4        1  Physical       Guard  \n",
       "..     ...       ...         ...  \n",
       "264      1  Physical    Vanguard  \n",
       "265      1  Physical      Sniper  \n",
       "266      1  Physical  Specialist  \n",
       "267      1  Physical       Guard  \n",
       "268      1   Healing       Medic  \n",
       "\n",
       "[269 rows x 12 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "OperatorDF = OperatorDF.rename(columns = {'Operators':'characterID'})\n",
    "OperatorDF"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c33dd8f5",
   "metadata": {},
   "source": [
    "---\n",
    "### Creating Operators SQL Table\n",
    "\n",
    "In order to store all the data from **OperatorDF** we have to create a SQL table so that all of the values within the dictionary can be safely transferred to the SQL database. As we create this table within SQL we can first declare the constraints of the specific values.\n",
    "- **characterID** : CHAR, NOT NULL, UNIQUE, PRIMARY KEY\n",
    "    - The reason characterID is CHAR is because all of the characters are in string format. With this if we eventually merge or       join other tables this key value will be important, this is why we also set it as the primary key.\n",
    "- **HP** : INT\n",
    "    - The reason HP is an int is because we need to store all of their values as a number\n",
    "- **ATK** : INT\n",
    "    - The reason ATK is an int is because we need to store all of their values as a number\n",
    "- **DEF** : INT\n",
    "    - The reason DEF is an int is because we need to store all of their values as a number\n",
    "- **COST** : INT\n",
    "    - The reason DEF is an int is because we need to store all of their values as a number\n",
    "- **RES** : INT\n",
    "    - The reason DEF is an int is because we need to store all of their values as a number\n",
    "- **BLOCK** : INT\n",
    "    - The reason DEF is an int is because we need to store all of their values as a number\n",
    "- **REDEPLOY** : INT\n",
    "    - The reason DEF is an int is because we need to store all of their values as a number\n",
    "- **INTERVAL** : FLOAT\n",
    "    - The reason INTERVAL is a float is because it tells us the amount of time the operators are able to attack which isnt in         integers.\n",
    "- **TARGET** : CHAR\n",
    "    - The reason TARGET is a char is because some operators are have an AOE target and some are numerical. So logically it           makes more sense to leave this as a char so it runs into no errors\n",
    "- **DMG** : CHAR\n",
    "    - The reason DMG is a char is because it contains strings of the operators damage type\n",
    "- **ROLE** : CHAR\n",
    "    - The reason ROLE is a char is because each operator contains a string of the specific type of role they are in the game\n",
    "    \n",
    "    \n",
    "**NOTE:** HP through ROLE are allowed to be NULL because if a character gets leaked and gets stored onto gamepress their will be no values within their stats thus resulting in an output of *NaN*. Thus its okay to leave these values as okay being NULL\n",
    "    \n",
    "### Does it adhere to the principles of good database design?\n",
    "Yes it does! Each column represent their own distinctive value, the databases are divided so our information are into subject-based tables. Our information also provides access to use inner joins from table to table. The attributes are used strictly for identification purposes such as identifying an operator and their stats! Overrall this database does apply with the principles of good database design!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "d631e38b",
   "metadata": {},
   "outputs": [],
   "source": [
    "operators = \"\"\"\n",
    "CREATE TABLE IF NOT EXISTS operators (\n",
    "    characterID CHAR NOT NULL UNIQUE PRIMARY KEY,\n",
    "    HP INT,\n",
    "    ATK INT,\n",
    "    DEF INT,\n",
    "    COST INT,\n",
    "    RES INT,\n",
    "    BLOCK INT,\n",
    "    REDEPLOY INT,\n",
    "    INTERVAL FLOAT,\n",
    "    TARGET CHAR,\n",
    "    DMG CHAR,\n",
    "    ROLE CHAR)\n",
    "\"\"\"\n",
    "\n",
    "# Execute the statement\n",
    "try:\n",
    "    connection.execute(operators)\n",
    "except sa.exc.SQLAlchemyError as err:\n",
    "    print(\"CREATE of operators failed:\", str(err))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "c4d5daef",
   "metadata": {
    "scrolled": false
   },
   "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>characterID</th>\n",
       "      <th>HP</th>\n",
       "      <th>ATK</th>\n",
       "      <th>DEF</th>\n",
       "      <th>COST</th>\n",
       "      <th>RES</th>\n",
       "      <th>BLOCK</th>\n",
       "      <th>REDEPLOY</th>\n",
       "      <th>INTERVAL</th>\n",
       "      <th>TARGET</th>\n",
       "      <th>DMG</th>\n",
       "      <th>ROLE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Vigil</td>\n",
       "      <td>1755</td>\n",
       "      <td>542</td>\n",
       "      <td>154</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>70</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Vanguard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Penance</td>\n",
       "      <td>4655</td>\n",
       "      <td>916</td>\n",
       "      <td>616</td>\n",
       "      <td>36</td>\n",
       "      <td>10</td>\n",
       "      <td>3</td>\n",
       "      <td>70</td>\n",
       "      <td>1.60</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Defender</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Texas the Omertosa</td>\n",
       "      <td>1598</td>\n",
       "      <td>659</td>\n",
       "      <td>320</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>0.93</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Specialist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Stainless</td>\n",
       "      <td>2723</td>\n",
       "      <td>633</td>\n",
       "      <td>461</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>70</td>\n",
       "      <td>1.50</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Supporter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Młynar</td>\n",
       "      <td>4266</td>\n",
       "      <td>385</td>\n",
       "      <td>502</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>70</td>\n",
       "      <td>1.20</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Guard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>264</th>\n",
       "      <td>Yato</td>\n",
       "      <td>1030</td>\n",
       "      <td>262</td>\n",
       "      <td>192</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>70</td>\n",
       "      <td>1.05</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Vanguard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265</th>\n",
       "      <td>'Justice Knight'</td>\n",
       "      <td>595</td>\n",
       "      <td>217</td>\n",
       "      <td>41</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>200</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Sniper</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>266</th>\n",
       "      <td>THRM-EX</td>\n",
       "      <td>1443</td>\n",
       "      <td>350</td>\n",
       "      <td>443</td>\n",
       "      <td>3</td>\n",
       "      <td>50</td>\n",
       "      <td>0</td>\n",
       "      <td>200</td>\n",
       "      <td>0.93</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Specialist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267</th>\n",
       "      <td>Castle-3</td>\n",
       "      <td>1391</td>\n",
       "      <td>413</td>\n",
       "      <td>90</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>200</td>\n",
       "      <td>1.50</td>\n",
       "      <td>1</td>\n",
       "      <td>Physical</td>\n",
       "      <td>Guard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268</th>\n",
       "      <td>Lancet-2</td>\n",
       "      <td>535</td>\n",
       "      <td>110</td>\n",
       "      <td>27</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>200</td>\n",
       "      <td>2.85</td>\n",
       "      <td>1</td>\n",
       "      <td>Healing</td>\n",
       "      <td>Medic</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>269 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            characterID    HP  ATK  DEF  COST  RES  BLOCK  REDEPLOY  INTERVAL  \\\n",
       "0                 Vigil  1755  542  154    17    0      1        70      1.00   \n",
       "1               Penance  4655  916  616    36   10      3        70      1.60   \n",
       "2    Texas the Omertosa  1598  659  320    10    0      1        18      0.93   \n",
       "3             Stainless  2723  633  461    19    0      2        70      1.50   \n",
       "4                Młynar  4266  385  502    12   15      3        70      1.20   \n",
       "..                  ...   ...  ...  ...   ...  ...    ...       ...       ...   \n",
       "264                Yato  1030  262  192     7    0      2        70      1.05   \n",
       "265    'Justice Knight'   595  217   41     3    0      1       200      1.00   \n",
       "266             THRM-EX  1443  350  443     3   50      0       200      0.93   \n",
       "267            Castle-3  1391  413   90     3    0      1       200      1.50   \n",
       "268            Lancet-2   535  110   27     3    0      1       200      2.85   \n",
       "\n",
       "    TARGET       DMG        ROLE  \n",
       "0        1  Physical    Vanguard  \n",
       "1        1  Physical    Defender  \n",
       "2        1  Physical  Specialist  \n",
       "3        1  Physical   Supporter  \n",
       "4        1  Physical       Guard  \n",
       "..     ...       ...         ...  \n",
       "264      1  Physical    Vanguard  \n",
       "265      1  Physical      Sniper  \n",
       "266      1  Physical  Specialist  \n",
       "267      1  Physical       Guard  \n",
       "268      1   Healing       Medic  \n",
       "\n",
       "[269 rows x 12 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "OperatorDF.to_sql(\"operators\", con=connection, if_exists=\"append\", index=False)\n",
    "df3 = pd.read_sql_table(\"operators\", connection)\n",
    "\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "323a432a",
   "metadata": {},
   "source": [
    "---\n",
    "## Challenges Faced:\n",
    "While uploading my data to SQL the only challenges I ever faced were the constraint issues where some couldn't be PRIMARY KEYS and Uniques, in order to fix this issue I was able to set 2 other columns as Primary keys which fixed my issue ultimately. \n",
    "\n",
    "## Mapping:\n",
    "\n",
    "operators [1] <---> [m] banners\n",
    "\n",
    "popular [m] <-----> [m] banners\n",
    "\n",
    "popular [m] <-----> [m] operators\n",
    "\n",
    "## Ultimatum:\n",
    "\n",
    "By grabbing all of our data and storing it within respective SQL databases and pandas dataframes this will bring us one step closer into answering our central question if some characters from the game arknights are better than others and possibly if it is worth for the user to invest in these characters"
   ]
  }
 ],
 "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}