2022-04-05-arenastats-tbc-data-cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "15fb0bac",
   "metadata": {},
   "source": [
    "## Import libraries. Load dataset from ArenaStats-TBC."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "639985ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import datetime\n",
    "pd.options.display.max_columns = 7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "8bbe36c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "arena = pd.read_csv(\"C:\\\\Users\\\\chris\\\\Desktop\\\\arena.csv\")\n",
    "icons = pd.read_csv(\"C:\\\\Users\\\\chris\\\\Documents\\\\Datasets\\\\WOW\\\\WOW_CLASS_ICONS.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "401a072f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'DRUID': 'https://www.progamerreview.com/wp-content/uploads/2019/07/E0FwJT8.png',\n",
       " 'HUNTER': 'https://www.progamerreview.com/wp-content/uploads/2019/07/ybbMpYe.png',\n",
       " 'MAGE': 'https://www.progamerreview.com/wp-content/uploads/2019/07/IsuHHZo.png',\n",
       " 'PALADIN': 'https://www.progamerreview.com/wp-content/uploads/2019/07/LxbAt4V.png',\n",
       " 'PRIEST': 'https://www.progamerreview.com/wp-content/uploads/2019/07/RCXyIlL.png',\n",
       " 'ROGUE': 'https://www.progamerreview.com/wp-content/uploads/2019/07/90odTOu.png',\n",
       " 'SHAMAN': 'https://www.progamerreview.com/wp-content/uploads/2019/07/q4r80Pn.png',\n",
       " 'WARLOCK': 'https://www.progamerreview.com/wp-content/uploads/2019/07/PDrI6st.png',\n",
       " 'WARRIOR': 'https://www.progamerreview.com/wp-content/uploads/2019/07/duY4PXM.png',\n",
       " '': ''}"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "class_icons = {}\n",
    "for index,row in icons.iterrows():\n",
    "    class_icons[row[\"class\"]] = row[\"class_icon\"]\n",
    "class_icons[\"\"] = \"\"\n",
    "class_icons"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38d2dbb8",
   "metadata": {},
   "source": [
    "## Append new columns (queueType, playersPerTeam)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "5987102d",
   "metadata": {},
   "outputs": [],
   "source": [
    "queue_type_col = []\n",
    "players_per_team = []\n",
    "for index,row in arena.iterrows():\n",
    "    player_count = 0\n",
    "    for i in [\"teamPlayerClass1\",\"teamPlayerClass2\",\"teamPlayerClass3\",\"teamPlayerClass4\",\"teamPlayerClass5\"]:\n",
    "        if str(row[i]) == \"nan\":\n",
    "            break\n",
    "        else:\n",
    "            player_count += 1\n",
    "    queue_type_col.append(f\"{player_count}v{player_count}\")\n",
    "    players_per_team.append(player_count)\n",
    "arena[\"queueType\"] = queue_type_col\n",
    "arena[\"playersPerTeam\"] = players_per_team\n",
    "arena.fillna(\"\",inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d7f6c36b",
   "metadata": {},
   "source": [
    "## Dataset cleaning functions. (To fix team faction/player/match errors)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "b448afbd",
   "metadata": {},
   "outputs": [],
   "source": [
    "def faction_count(players):\n",
    "    horde_races = [\"SCOURGE\",\"TROLL\",\"TAUREN\",\"BLOODELF\",\"ORC\"]\n",
    "    ally_races = [\"HUMAN\",\"GNOME\",\"DRAENEI\",\"NIGHTELF\",\"DWARF\"]\n",
    "    ally = 0\n",
    "    horde = 0\n",
    "    for i in players:\n",
    "        if (i[2] in horde_races):\n",
    "            horde += 1\n",
    "        elif (i[2] in ally_races):\n",
    "            ally += 1\n",
    "    return {\"HORDE\" : horde, \"ALLIANCE\" : ally}\n",
    "\n",
    "def players_by_faction(players):\n",
    "    horde_races = [\"SCOURGE\",\"TROLL\",\"TAUREN\",\"BLOODELF\",\"ORC\"]\n",
    "    ally_races = [\"HUMAN\",\"GNOME\",\"DRAENEI\",\"NIGHTELF\",\"DWARF\"]\n",
    "    players_horde = []\n",
    "    players_ally = []\n",
    "    for i in players:\n",
    "        if (i[2] in horde_races):\n",
    "            players_horde.append(i)\n",
    "        elif (i[2] in ally_races):\n",
    "            players_ally.append(i)\n",
    "    return {\"HORDE\" : players_horde,\"ALLIANCE\" : players_ally}\n",
    "\n",
    "def fix_players(players, players_prev):\n",
    "    if (faction_count(players)[\"HORDE\"] == row[\"playersPerTeam\"]) and (faction_count(players)[\"ALLIANCE\"] != row[\"playersPerTeam\"]):\n",
    "        return players_by_faction(players)[\"HORDE\"]\n",
    "    elif (faction_count(players)[\"HORDE\"] != row[\"playersPerTeam\"]) and (faction_count(players)[\"ALLIANCE\"] == row[\"playersPerTeam\"]):\n",
    "        return players_by_faction(players)[\"ALLIANCE\"]\n",
    "\n",
    "    players_rev = []\n",
    "    players_removed = []\n",
    "    for cur_index,cur_i in enumerate(players):\n",
    "        match = False\n",
    "        for prev_index,prev_i in enumerate(players_prev):\n",
    "            if (cur_i[1] == prev_i[1]) and (cur_i[2] == prev_i[2]):\n",
    "                players_prev.pop(prev_index)\n",
    "                match = True\n",
    "                break\n",
    "        if match == False:\n",
    "            players_rev.append(cur_i)\n",
    "    return players_rev\n",
    "\n",
    "def get_faction(players):\n",
    "    horde_races = [\"SCOURGE\",\"TROLL\",\"TAUREN\",\"BLOODELF\",\"ORC\"]\n",
    "    ally_races = [\"HUMAN\",\"GNOME\",\"DRAENEI\",\"NIGHTELF\",\"DWARF\"]\n",
    "    horde = False\n",
    "    ally = False\n",
    "    for i in players:\n",
    "        if i[2] in horde_races:\n",
    "            horde = True\n",
    "        elif i[2] in ally_races:\n",
    "            ally = True\n",
    "    if (horde and ally) or (not horde and not ally):\n",
    "        return \"NA\"\n",
    "    elif horde:\n",
    "        return \"HORDE\"\n",
    "    elif ally:\n",
    "        return \"ALLIANCE\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3124bd27",
   "metadata": {},
   "source": [
    "## Drop duplicated games and skirmishes. Fix \"ghost player\" errors. Format map name (zoneId) and startTime. Update team factions. Drop rows with unfixable errors."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "23350e86",
   "metadata": {},
   "outputs": [],
   "source": [
    "rows_to_drop = []\n",
    "for index,row in arena.iterrows():\n",
    "    # Duplicated games error.\n",
    "    if row[\"duration\"] == 0:\n",
    "        rows_to_drop.append(index)\n",
    "        continue\n",
    "    # Omit skirmishes.\n",
    "    if row[\"isRanked\"] != \"YES\":\n",
    "        rows_to_drop.append(index)\n",
    "        continue\n",
    "    if row[\"teamName\"] == \"\":\n",
    "        rows_to_drop.append(index)\n",
    "        continue\n",
    "    players = []\n",
    "    for i in range(1,6):\n",
    "        if row[f\"enemyPlayerClass{i}\"] == \"\":\n",
    "            break\n",
    "        else:\n",
    "            players.append((row[f\"enemyPlayerName{i}\"],row[f\"enemyPlayerClass{i}\"],row[f\"enemyPlayerRace{i}\"]))\n",
    "    arena.loc[index, \"enemyFaction\"] = get_faction(players)\n",
    "    #Ghost player error.\n",
    "    if len(players) > row[\"playersPerTeam\"]:\n",
    "        if index == 0:\n",
    "            rows_to_drop.append(index)\n",
    "            continue\n",
    "        players_rev = fix_players(players, players_prev)\n",
    "        if len(players_rev) != row[\"playersPerTeam\"]:\n",
    "            rows_to_drop.append(index)\n",
    "        else:\n",
    "            arena.loc[index, \"enemyFaction\"] = get_faction(players_rev)\n",
    "            for i in range(1,6):\n",
    "                arena.loc[index, f\"enemyPlayerName{i}\"] = \"\"\n",
    "                arena.loc[index, f\"enemyPlayerClass{i}\"] = \"\"\n",
    "                arena.loc[index, f\"enemyPlayerRace{i}\"] = \"\"\n",
    "            for index_rev,i in enumerate(players_rev):\n",
    "                arena.loc[index, f\"enemyPlayerName{index_rev + 1}\"] = i[0]\n",
    "                arena.loc[index, f\"enemyPlayerClass{index_rev + 1}\"] = i[1]\n",
    "                arena.loc[index, f\"enemyPlayerRace{index_rev + 1}\"] = i[2]\n",
    "    # Replace zoneId with map names.\n",
    "    if row[\"zoneId\"] == 3698:\n",
    "        arena.loc[index, \"zoneId\"] = \"Blade's Edge Arena\"\n",
    "    elif row[\"zoneId\"] == 3702:\n",
    "        arena.loc[index, \"zoneId\"] = \"Nagrand Arena\"\n",
    "    elif row[\"zoneId\"] == 3968:\n",
    "        arena.loc[index, \"zoneId\"] = \"Ruins of Lordaeron\"\n",
    "    arena.loc[index, \"startTime\"] = datetime.datetime.fromtimestamp(row[\"startTime\"]).strftime(\"%Y-%m-%d-%H:%M\")\n",
    "    players_prev = players\n",
    "arena.drop(rows_to_drop, inplace=True)\n",
    "arena.drop(\"endTime\", axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80523f07",
   "metadata": {},
   "source": [
    "## Append more columns (teamComp, enemyTeamComp, winLoss)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "1a2e9d94",
   "metadata": {},
   "outputs": [],
   "source": [
    "classes = [\"DRUID\",\"HUNTER\",\"MAGE\",\"PALADIN\",\"PRIEST\",\"ROGUE\",\"SHAMAN\",\"WARLOCK\",\"WARRIOR\"]\n",
    "team_comp_dict = {}\n",
    "enemy_team_comp_dict = {}\n",
    "team_comp = \"\"\n",
    "enemy_team_comp = \"\"\n",
    "tc_list = []\n",
    "etc_list = []\n",
    "win_loss_list = []\n",
    "\n",
    "# Initialize team comp dicts.\n",
    "for i in classes:\n",
    "    team_comp_dict[i] = 0\n",
    "    enemy_team_comp_dict[i] = 0\n",
    "for index,row in arena.iterrows():\n",
    "    team_comp = \"\"\n",
    "    enemy_team_comp = \"\"\n",
    "    for i in range(1,6):\n",
    "        if row[f\"teamPlayerClass{i}\"] in classes:\n",
    "            team_comp_dict[row[f\"teamPlayerClass{i}\"]] += 1\n",
    "        if row[f\"enemyPlayerClass{i}\"] in classes:\n",
    "            enemy_team_comp_dict[row[f\"enemyPlayerClass{i}\"]] += 1\n",
    "    for i in team_comp_dict:\n",
    "        if team_comp_dict[i] == 0:\n",
    "            continue\n",
    "        while team_comp_dict[i] > 0:\n",
    "            if team_comp != \"\":\n",
    "                team_comp += \"-\"\n",
    "            team_comp += i\n",
    "            team_comp_dict[i] -= 1\n",
    "    for i in enemy_team_comp_dict:\n",
    "        if enemy_team_comp_dict[i] == 0:\n",
    "            continue\n",
    "        while enemy_team_comp_dict[i] > 0:\n",
    "            if enemy_team_comp != \"\":\n",
    "                enemy_team_comp += \"-\"\n",
    "            enemy_team_comp += i\n",
    "            enemy_team_comp_dict[i] -= 1\n",
    "    if row[\"diffRating\"] == \"\":\n",
    "        win_loss_list.append(\"NA\")\n",
    "    elif int(row[\"diffRating\"]) > 0:\n",
    "        win_loss_list.append(\"1\")\n",
    "    else:\n",
    "        win_loss_list.append(\"0\")\n",
    "    tc_list.append(team_comp)\n",
    "    etc_list.append(enemy_team_comp)\n",
    "arena[\"teamComp\"] = tc_list\n",
    "arena[\"enemyTeamComp\"] = etc_list\n",
    "arena[\"winLoss\"] = win_loss_list"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a4be1280",
   "metadata": {},
   "source": [
    "## Append columns for class icons."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "37429129",
   "metadata": {},
   "outputs": [],
   "source": [
    "arena.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "ec05e6d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "teamClassIcon = []\n",
    "enemyTeamClassIcon = []\n",
    "\n",
    "for index,row in arena.iterrows():\n",
    "    teamClassIconRow = [\"\",\"\",\"\",\"\",\"\"]\n",
    "    enemyTeamClassIconRow = [\"\",\"\",\"\",\"\",\"\"]\n",
    "    for index,i in enumerate(row[\"teamComp\"].split(\"-\")):\n",
    "        teamClassIconRow[index] = (class_icons[i])\n",
    "    for index,i in enumerate(row[\"enemyTeamComp\"].split(\"-\")):\n",
    "        enemyTeamClassIconRow[index] = (class_icons[i])\n",
    "    teamClassIcon.append(teamClassIconRow)\n",
    "    enemyTeamClassIcon.append(enemyTeamClassIconRow)\n",
    "\n",
    "df_icons1 = pd.DataFrame(teamClassIcon, columns = [\"teamClassIcon1\",\n",
    "                                                   \"teamClassIcon2\",\n",
    "                                                   \"teamClassIcon3\",\n",
    "                                                   \"teamClassIcon4\",\n",
    "                                                   \"teamClassIcon5\"])\n",
    "df_icons2 = pd.DataFrame(enemyTeamClassIcon, columns = [\"enemyTeamClassIcon1\",\n",
    "                                                        \"enemyTeamClassIcon2\",\n",
    "                                                        \"enemyTeamClassIcon3\",\n",
    "                                                        \"enemyTeamClassIcon4\",\n",
    "                                                        \"enemyTeamClassIcon5\"])\n",
    "arena = pd.concat([arena, df_icons1, df_icons2], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7233cfb0",
   "metadata": {},
   "source": [
    "## Save and export cleaned dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "f00d4517",
   "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>index</th>\n",
       "      <th>isRanked</th>\n",
       "      <th>startTime</th>\n",
       "      <th>...</th>\n",
       "      <th>enemyTeamClassIcon3</th>\n",
       "      <th>enemyTeamClassIcon4</th>\n",
       "      <th>enemyTeamClassIcon5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>YES</td>\n",
       "      <td>2022-01-28-14:27</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>YES</td>\n",
       "      <td>2022-01-28-14:31</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>YES</td>\n",
       "      <td>2022-01-28-14:35</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>YES</td>\n",
       "      <td>2022-01-28-14:38</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>YES</td>\n",
       "      <td>2022-01-28-14:42</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 61 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index isRanked         startTime  ... enemyTeamClassIcon3  \\\n",
       "0      0      YES  2022-01-28-14:27  ...                       \n",
       "1      1      YES  2022-01-28-14:31  ...                       \n",
       "2      2      YES  2022-01-28-14:35  ...                       \n",
       "3      3      YES  2022-01-28-14:38  ...                       \n",
       "4      4      YES  2022-01-28-14:42  ...                       \n",
       "\n",
       "  enemyTeamClassIcon4 enemyTeamClassIcon5  \n",
       "0                                          \n",
       "1                                          \n",
       "2                                          \n",
       "3                                          \n",
       "4                                          \n",
       "\n",
       "[5 rows x 61 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "arena.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "cbc9bccf",
   "metadata": {},
   "outputs": [],
   "source": [
    "arena.to_csv(\"C:\\\\Users\\\\chris\\\\Desktop\\\\arena_clean.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
}