normalize_stats.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6a963fa3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13665949",
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs =pd.read_excel(\"Boomer Bowl TDM Stats.xlsx\",sheet_name=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5318bbc6",
   "metadata": {},
   "outputs": [],
   "source": [
    "from collections import defaultdict\n",
    "\n",
    "matches = defaultdict(dict)\n",
    "\n",
    "for k, df in dfs.items():\n",
    "    if k == 'Rankings':\n",
    "        pass\n",
    "    opponent = ''\n",
    "    for row in df.itertuples():\n",
    "        rv = row[1]\n",
    "        blank = type(rv) == float and np.isnan(rv)\n",
    "        if rv == 'Map':\n",
    "            dfn = df.iloc[row[0]+2:row[0]+6,:7].copy()\n",
    "            dfn.columns = list(df.iloc[row[0]+1,:7])\n",
    "            map_name = row[2]\n",
    "            kval = tuple(sorted([k,opponent]) + [map_name])\n",
    "            matches[kval][k] = dfn\n",
    "            print('\\t',map_name)\n",
    "        if rv == 'Match':\n",
    "            opponent = row[2].split()[-1]\n",
    "            if opponent == 'mode?':\n",
    "                opponent = 'Mode'\n",
    "            if opponent == 'X-Rated':\n",
    "                opponent = 'XRated'\n",
    "            print(k, opponent)\n",
    "    print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0384036a",
   "metadata": {},
   "outputs": [],
   "source": [
    "maps = defaultdict(list)\n",
    "for k,v in matches.items():\n",
    "    ting = [v2.mean(0) for k2,v2 in v.items()]\n",
    "    maps[k[-1]].append(ting)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d5b3916",
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = list(maps[list(maps.keys())[0]][0][0].keys())\n",
    "df_maps = pd.DataFrame({k:np.mean(sum(v,[]),axis=0) for k,v in maps.items()}).T\n",
    "df_maps.columns = cols\n",
    "df_maps.sort_values('Damage Given')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "21987c5d",
   "metadata": {},
   "outputs": [],
   "source": [
    "{k: list(v.keys()) for k,v in matches.items() if len(v.keys()) == 1}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "304a5fdd",
   "metadata": {},
   "outputs": [],
   "source": [
    "aliases = {\n",
    "    'm:(es':'miles',\n",
    "    'miles of shit':'miles',\n",
    "    'fuckin miles':'miles',\n",
    "    't41TG':'t41TG',\n",
    "    'ᵠᶠᴸt41TG':'t41TG',\n",
    "    'miles morales SPIDERMAN':'miles',\n",
    "    'davE':'icel0re',\n",
    "    '1999 icel0re':'icel0re',\n",
    "    'konz':'konfuzed',\n",
    "    'a1 sotrix':'sotrix',\n",
    "    'zog':'zig',\n",
    "    'imaloser':'sotrix',\n",
    "    'Raul':'raul',\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ced55977",
   "metadata": {},
   "outputs": [],
   "source": [
    "tot_tables = {k: pd.concat(v.values()).set_index('Player') for k,v in matches.items()}\n",
    "for k,v in tot_tables.items():\n",
    "    v.index = pd.Index([aliases.get(_,_) for _ in v.index],name='Player')\n",
    "nrm_tables = {k:v/v.mean(0) for k,v in tot_tables.items()}\n",
    "\n",
    "for k,v in nrm_tables.items():\n",
    "    v['map'] = k[2]\n",
    "    v['team1'] = k[1]\n",
    "    v['team2'] = k[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "86f3d7c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "v = nrm_tables[('Mode',  'SavageZ',  'dredwerkz')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4fd00264",
   "metadata": {},
   "outputs": [],
   "source": [
    "dft = pd.concat(nrm_tables.values()).sort_values('Damage Given',0,False).drop(['Net F/D','Net Damage'],1)\n",
    "\n",
    "dft['NormNet Damage'] = dft['Damage Given']-dft['Damage Taken']\n",
    "dft['Norm(FmD)'] = dft['Frags']-dft['Deaths']\n",
    "dft['sNetE'] = dft['Frags'] + ( dft['Damage Given']/ 181.2 ) + ( dft['Deaths'] / -3.5 ) + ( dft['Damage Taken'] / -275.1 )\n",
    "dft['sNetE'] = dft['Frags'] + ( dft['Damage Given'] ) + ( dft['Deaths'] / -3.5 ) + ( dft['Damage Taken'] / -1.5 )\n",
    "dft = dft.sort_values('sNetE',0,False)\n",
    "dft.to_csv('xzist_out2.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b175e454",
   "metadata": {},
   "outputs": [],
   "source": [
    "dft"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "876f465c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ci = dft.drop(['map','team1','team2'],1).groupby('Player')\n",
    "df_ci = 1.96*df_ci.std()/np.sqrt(df_ci.count())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dd098b25",
   "metadata": {},
   "outputs": [],
   "source": [
    "dft2 = dft.groupby('Player').mean().sort_values('sNetE',0,False)\n",
    "dft2['lb_snet'] = dft2['sNetE'] - df_ci['sNetE']\n",
    "dft2['ub_snet'] = dft2['sNetE'] + df_ci['sNetE']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7ea9b223",
   "metadata": {},
   "outputs": [],
   "source": [
    "dft2.to_csv('xzist_out.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca6420f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "dft2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c8a70c4b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ci.std()"
   ]
  }
 ],
 "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}