2022-04-28-vintage-metagame-data-cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "dd9a50aa-68be-42b9-a1db-940bcc2417c3",
   "metadata": {},
   "source": [
    "## Import libraries and dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "9a46d5c9-143a-498a-9633-95b06d65fec9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "import calendar\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "id": "84cefb81-8092-4aa8-9782-4b396e3cca23",
   "metadata": {},
   "outputs": [],
   "source": [
    "vintage = pd.read_csv(\"vintage-metagame.csv\", names=[\"rank\",\"player\",\"wins\",\"losses\",\"byes\",\"arch\",\"subarch\",\"deck\",\"details\",\"date\",\"event_type\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b558c060-a14c-4a84-8d91-25dc4408c26f",
   "metadata": {},
   "source": [
    "## Replace 'NA' values in 'byes' column with 0."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "id": "fc6aabf2-6ec7-4311-a3dc-48ba6a63ad80",
   "metadata": {},
   "outputs": [],
   "source": [
    "vintage.byes = vintage.byes.fillna(0)\n",
    "vintage.byes = vintage.byes.astype(\"int\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a82a8d90-30d9-4447-990e-8758068d648b",
   "metadata": {},
   "source": [
    "## Apply 'event_type' to each record."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "id": "4ec49829-d89b-4c37-b718-c92752e0320b",
   "metadata": {},
   "outputs": [],
   "source": [
    "vintage[\"event_type\"].replace({\"Showcase Qualifier\": \"Showcase_Qualifier\"}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "id": "a60c3a90-3fc6-4ba2-bead-9670bec69770",
   "metadata": {},
   "outputs": [],
   "source": [
    "event_type = vintage.event_type.tolist()\n",
    "for index,i in enumerate(event_type):\n",
    "    if isinstance(i, str):\n",
    "        new = i\n",
    "    else:\n",
    "        event_type[index] = new\n",
    "vintage[\"event_type\"] = event_type"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d883a60e-2cc1-452d-a559-5ddf9fa31870",
   "metadata": {},
   "source": [
    "## Create column for unique 'event_id'. Format 'event_date' string."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "id": "e7867d80-fd78-42a7-9769-64ae5cde8783",
   "metadata": {},
   "outputs": [],
   "source": [
    "event_id = []\n",
    "dates = vintage.date.tolist()\n",
    "event_type = vintage.event_type.to_list()\n",
    "dates_new = []\n",
    "for index,i in enumerate(dates):\n",
    "    month = i.split(\"/\")[0].zfill(2)\n",
    "    day = i.split(\"/\")[1].zfill(2)\n",
    "    year = i.split(\"/\")[2]\n",
    "    event_id.append(f\"{year}-{month}-{day}-{event_type[index]}\")\n",
    "    dates_new.append(f\"{year}-{month}-{day}\")\n",
    "vintage[\"event_id\"] = event_id"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "020b95cd-a606-45e0-8f34-9af326c67cf4",
   "metadata": {},
   "source": [
    "## Create Events table. Remove duplicate records such that each row represents a unique event."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "id": "df49ec51-2b9a-455d-bae4-cce43dcbd02a",
   "metadata": {},
   "outputs": [],
   "source": [
    "events = pd.DataFrame({\"event_id\" : event_id, \"event_type\" : event_type, \"date\" : dates_new})\n",
    "events = events.groupby([\"event_id\"], as_index=False)[\"event_type\", \"date\"].last()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c5ef6aa-2e57-43d4-83b3-b87759073384",
   "metadata": {},
   "source": [
    "## Add column 'entries' to Events table to represent number of players in each event."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "id": "a60b2c73-8a49-49e6-8988-b5a6cc148171",
   "metadata": {},
   "outputs": [],
   "source": [
    "players = vintage.groupby([\"event_id\"], as_index=False)[\"rank\"].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "id": "cb7cc212-05fe-45d8-948a-58f987a4c689",
   "metadata": {},
   "outputs": [],
   "source": [
    "events = events.merge(players, on=\"event_id\")\n",
    "events.rename(columns={\"rank\" : \"entries\", \"date\" : \"event_date\"}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed461b07-e519-401b-b33b-194406ec5db8",
   "metadata": {},
   "source": [
    "## Add column 'day_of_week' to Events table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "8cea5ed2-a0c4-4489-9c85-e444c2137f19",
   "metadata": {},
   "outputs": [],
   "source": [
    "events[\"day_of_week\"] = events[\"event_date\"].apply(lambda x: calendar.day_name[datetime.strptime(x, \"%Y-%m-%d\").weekday()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "id": "de3e82e9-4870-4abc-bcbf-609817525ae5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>event_id</th>\n",
       "      <th>event_type</th>\n",
       "      <th>event_date</th>\n",
       "      <th>entries</th>\n",
       "      <th>day_of_week</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-01-02-Challenge</td>\n",
       "      <td>Challenge</td>\n",
       "      <td>2021-01-02</td>\n",
       "      <td>72</td>\n",
       "      <td>Saturday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-01-03-Challenge</td>\n",
       "      <td>Challenge</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>59</td>\n",
       "      <td>Sunday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-09-Challenge</td>\n",
       "      <td>Challenge</td>\n",
       "      <td>2021-01-09</td>\n",
       "      <td>93</td>\n",
       "      <td>Saturday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-01-10-Challenge</td>\n",
       "      <td>Challenge</td>\n",
       "      <td>2021-01-10</td>\n",
       "      <td>63</td>\n",
       "      <td>Sunday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-01-16-Showcase</td>\n",
       "      <td>Showcase</td>\n",
       "      <td>2021-01-16</td>\n",
       "      <td>152</td>\n",
       "      <td>Saturday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               event_id event_type  event_date  entries day_of_week\n",
       "0  2021-01-02-Challenge  Challenge  2021-01-02       72    Saturday\n",
       "1  2021-01-03-Challenge  Challenge  2021-01-03       59      Sunday\n",
       "2  2021-01-09-Challenge  Challenge  2021-01-09       93    Saturday\n",
       "3  2021-01-10-Challenge  Challenge  2021-01-10       63      Sunday\n",
       "4   2021-01-16-Showcase   Showcase  2021-01-16      152    Saturday"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "events.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "beeefb32-3e6a-4657-9c51-5a3ff9fa83b1",
   "metadata": {},
   "source": [
    "## Drop 'details' column from Results table. Drop 'date' and 'event_type' columns because they are now in the Events table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "id": "dde7870c-a52f-4120-b840-93fecddd69bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "vintage.drop([\"details\"], axis=1, inplace=True)\n",
    "vintage.drop([\"date\"], axis=1, inplace=True)\n",
    "vintage.drop([\"event_type\"], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db38bfce-7b28-416f-8e10-662fdbd4a9b3",
   "metadata": {},
   "source": [
    "## Rename 'rank' column to 'finish' because 'rank' is a reserved word in MySQL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "id": "879bed06-43c4-445a-a78c-47db3a2a4459",
   "metadata": {},
   "outputs": [],
   "source": [
    "vintage.rename(columns={\"rank\" : \"finish\"}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "id": "490c70a6-bce6-4b2e-b7d7-1be08f470edb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>finish</th>\n",
       "      <th>player</th>\n",
       "      <th>wins</th>\n",
       "      <th>losses</th>\n",
       "      <th>byes</th>\n",
       "      <th>arch</th>\n",
       "      <th>subarch</th>\n",
       "      <th>deck</th>\n",
       "      <th>event_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>discoverN</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Combo</td>\n",
       "      <td>Breach</td>\n",
       "      <td>Tinker Breach</td>\n",
       "      <td>2022-04-24-Challenge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>S063</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>Combo</td>\n",
       "      <td>Breach</td>\n",
       "      <td>Laelia Breach</td>\n",
       "      <td>2022-04-24-Challenge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>_INF_</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>Blue Tinker</td>\n",
       "      <td>Other Blue Tinker</td>\n",
       "      <td>Grixis Tinker</td>\n",
       "      <td>2022-04-24-Challenge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>kasa</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>Bazaar</td>\n",
       "      <td>Dredge</td>\n",
       "      <td>Dredge</td>\n",
       "      <td>2022-04-24-Challenge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>boulderelf</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>Combo</td>\n",
       "      <td>Breach</td>\n",
       "      <td>Grixis Leilia Breach</td>\n",
       "      <td>2022-04-24-Challenge</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   finish      player  wins  losses  byes         arch            subarch  \\\n",
       "0       1   discoverN     8       1     0        Combo             Breach   \n",
       "1       2        S063     6       3     0        Combo             Breach   \n",
       "2       3       _INF_     5       3     0  Blue Tinker  Other Blue Tinker   \n",
       "3       4        kasa     5       3     0       Bazaar             Dredge   \n",
       "4       5  boulderelf     5       2     0        Combo             Breach   \n",
       "\n",
       "                   deck              event_id  \n",
       "0         Tinker Breach  2022-04-24-Challenge  \n",
       "1         Laelia Breach  2022-04-24-Challenge  \n",
       "2         Grixis Tinker  2022-04-24-Challenge  \n",
       "3                Dredge  2022-04-24-Challenge  \n",
       "4  Grixis Leilia Breach  2022-04-24-Challenge  "
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vintage.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5380add0-86df-465f-9b43-0b316886560d",
   "metadata": {},
   "source": [
    "## Replace commas because it breaks CSV importing with MySQL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "id": "38a54cb4-7327-48a9-b364-12e4f8f7f9c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "vintage[\"arch\"] = vintage[\"arch\"].apply(lambda x: str(x).replace(\",\", \"\"))\n",
    "vintage[\"subarch\"] = vintage[\"subarch\"].apply(lambda x: str(x).replace(\",\", \"\"))\n",
    "vintage[\"deck\"] = vintage[\"deck\"].apply(lambda x: str(x).replace(\",\", \"\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7552b352-fa2f-4b17-a03e-02bed3ee9d04",
   "metadata": {},
   "source": [
    "## Save and export 'vintage-results' and 'vintage-events' tables to CSV."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "id": "97216706-78ec-41ef-98fe-f501b851c656",
   "metadata": {},
   "outputs": [],
   "source": [
    "vintage.to_csv(\"vintage-results.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "id": "66544907-aa51-4b0e-a077-36162935f60a",
   "metadata": {},
   "outputs": [],
   "source": [
    "events.to_csv(\"vintage-events.csv\", index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}