pickem-wrapped-data-exploration.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9e197310",
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext autoreload\n",
    "%autoreload 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e5d39763",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pandasql import sqldf \n",
    "import data_helpers as dh\n",
    "from identifiers import name_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "d201ff18",
   "metadata": {},
   "outputs": [],
   "source": [
    "game_info = dh.get_table('game_info')\n",
    "all_picks = dh.get_table('all_picks')\n",
    "all_picks_info = dh.get_table('all_picks_info')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f40ccd2f",
   "metadata": {},
   "source": [
    "## Comparisons"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "362a1847",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_weekly_comparison_table(week_num):\n",
    "    df = dh.load_pickem_raw(week_num)\n",
    "    first_row = df.index[df.isnull().all(1)].values[0]\n",
    "    df = df[first_row+1:]\n",
    "    df = df\n",
    "    col_names = [name_dict[e] for e in df.iloc[0][1:8]]\n",
    "    df = df.iloc[:, :9]\n",
    "    df.columns = ['game_id'] + col_names + ['winner']\n",
    "    df = df[2:]\n",
    "    df = df[df['winner'].notnull()]\n",
    "    return df.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "633bd576",
   "metadata": {},
   "outputs": [],
   "source": [
    "weekly_comparison_lst = []\n",
    "for i in range(1,19):\n",
    "    weekly_comparison_lst.append(get_weekly_comparison_table(i))\n",
    "comp_table = pd.concat(weekly_comparison_lst)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "0a508e81",
   "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>daniel</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chiefs</td>\n",
       "      <td>Lions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Panthers</td>\n",
       "      <td>Falcons</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Texans</td>\n",
       "      <td>Ravens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bengals</td>\n",
       "      <td>Browns</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Bears</td>\n",
       "      <td>Packers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Patriots</td>\n",
       "      <td>Eagles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Seahawks</td>\n",
       "      <td>Rams</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Giants</td>\n",
       "      <td>Cowboys</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Bills</td>\n",
       "      <td>Jets</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Vikings</td>\n",
       "      <td>PUSH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Chargers</td>\n",
       "      <td>Titans</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Raiders</td>\n",
       "      <td>Bills</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Patriots</td>\n",
       "      <td>Dolphins</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Saints</td>\n",
       "      <td>PUSH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Browns</td>\n",
       "      <td>Steelers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Titans</td>\n",
       "      <td>Browns</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Saints</td>\n",
       "      <td>PUSH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Cowboys</td>\n",
       "      <td>Cardinals</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bears</td>\n",
       "      <td>PUSH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Steelers</td>\n",
       "      <td>Texans</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Bengals</td>\n",
       "      <td>Titans</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Raiders</td>\n",
       "      <td>Chargers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Patriots</td>\n",
       "      <td>Cowboys</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Cardinals</td>\n",
       "      <td>49ers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Chiefs</td>\n",
       "      <td>Jets</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Commanders</td>\n",
       "      <td>Bears</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bills</td>\n",
       "      <td>Jaguars</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Texans</td>\n",
       "      <td>PUSH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Panthers</td>\n",
       "      <td>Lions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Titans</td>\n",
       "      <td>Colts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Patriots</td>\n",
       "      <td>Saints</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Ravens</td>\n",
       "      <td>Steelers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Vikings</td>\n",
       "      <td>Chiefs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Cowboys</td>\n",
       "      <td>49ers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Packers</td>\n",
       "      <td>Raiders</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Titans</td>\n",
       "      <td>Ravens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Panthers</td>\n",
       "      <td>Dolphins</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Colts</td>\n",
       "      <td>Jaguars</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Saints</td>\n",
       "      <td>Texans</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>49ers</td>\n",
       "      <td>Browns</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Patriots</td>\n",
       "      <td>Raiders</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Cardinals</td>\n",
       "      <td>Rams</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Buccaneers</td>\n",
       "      <td>Lions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Eagles</td>\n",
       "      <td>Jets</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Bills</td>\n",
       "      <td>Giants</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Chargers</td>\n",
       "      <td>Cowboys</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lions</td>\n",
       "      <td>Ravens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Raiders</td>\n",
       "      <td>Bears</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Browns</td>\n",
       "      <td>Colts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Commanders</td>\n",
       "      <td>Giants</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        daniel     winner\n",
       "0       Chiefs      Lions\n",
       "1     Panthers    Falcons\n",
       "2       Texans     Ravens\n",
       "3      Bengals     Browns\n",
       "9        Bears    Packers\n",
       "12    Patriots     Eagles\n",
       "13    Seahawks       Rams\n",
       "14      Giants    Cowboys\n",
       "15       Bills       Jets\n",
       "0      Vikings       PUSH\n",
       "6     Chargers     Titans\n",
       "7      Raiders      Bills\n",
       "13    Patriots   Dolphins\n",
       "14      Saints       PUSH\n",
       "15      Browns   Steelers\n",
       "2       Titans     Browns\n",
       "4       Saints       PUSH\n",
       "11     Cowboys  Cardinals\n",
       "4        Bears       PUSH\n",
       "6     Steelers     Texans\n",
       "10     Bengals     Titans\n",
       "11     Raiders   Chargers\n",
       "12    Patriots    Cowboys\n",
       "13   Cardinals      49ers\n",
       "14      Chiefs       Jets\n",
       "0   Commanders      Bears\n",
       "1        Bills    Jaguars\n",
       "2       Texans       PUSH\n",
       "3     Panthers      Lions\n",
       "4       Titans      Colts\n",
       "6     Patriots     Saints\n",
       "7       Ravens   Steelers\n",
       "11     Vikings     Chiefs\n",
       "12     Cowboys      49ers\n",
       "13     Packers    Raiders\n",
       "1       Titans     Ravens\n",
       "2     Panthers   Dolphins\n",
       "3        Colts    Jaguars\n",
       "5       Saints     Texans\n",
       "7        49ers     Browns\n",
       "9     Patriots    Raiders\n",
       "10   Cardinals       Rams\n",
       "11  Buccaneers      Lions\n",
       "12      Eagles       Jets\n",
       "13       Bills     Giants\n",
       "14    Chargers    Cowboys\n",
       "2        Lions     Ravens\n",
       "3      Raiders      Bears\n",
       "4       Browns      Colts\n",
       "5   Commanders     Giants"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comp_table[comp_table['daniel'] != comp_table['winner']][['daniel', 'winner']].iloc[:50]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "5a6a4617",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.5147058823529411"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum(comp_table['jordan'] == comp_table['daniel']) / len(comp_table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "a25f9471",
   "metadata": {},
   "outputs": [],
   "source": [
    "#for p1, p2 in all_combinations:\n",
    "rows = []\n",
    "for p1 in users:\n",
    "    for p2 in users:\n",
    "        table = comp_table[[p1, p2]]\n",
    "        agree_rate = round(sum(comp_table[p1] == comp_table[p2]) / len(comp_table), 3)\n",
    "        agree_table = comp_table[(comp_table[p1] == comp_table[p2]) & (comp_table['winner'] != 'PUSH')]\n",
    "        disagree_table = comp_table[(comp_table[p1] != comp_table[p2]) & (comp_table['winner'] != 'PUSH')]\n",
    "        win_pct_when_agree = round((sum(agree_table[p1] == agree_table['winner']) / len(agree_table)), 3)\n",
    "        try:\n",
    "            p1_win_pct_when_disagree = round((sum(disagree_table[p1] == disagree_table['winner']) / len(disagree_table)), 3)\n",
    "        except ZeroDivisionError:\n",
    "            p1_win_pct_when_disagree = None\n",
    "        #print(p1, p2, agree_rate, win_pct_when_agree, p1_win_pct_when_disagree)\n",
    "        rows.append([p1, p2, agree_rate, win_pct_when_agree, p1_win_pct_when_disagree])\n",
    "columns = ['p1', 'p2', 'agree_rate', 'win_pct_when_agree', 'p1_win_pct_when_disagree']\n",
    "comp_statistics = pd.DataFrame(rows, columns=columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "aad87fee",
   "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>p1</th>\n",
       "      <th>p2</th>\n",
       "      <th>agree_rate</th>\n",
       "      <th>win_pct_when_agree</th>\n",
       "      <th>p1_win_pct_when_disagree</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>adam</td>\n",
       "      <td>andres</td>\n",
       "      <td>0.467</td>\n",
       "      <td>0.496</td>\n",
       "      <td>0.507</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>adam</td>\n",
       "      <td>james</td>\n",
       "      <td>0.467</td>\n",
       "      <td>0.549</td>\n",
       "      <td>0.460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>adam</td>\n",
       "      <td>greg</td>\n",
       "      <td>0.493</td>\n",
       "      <td>0.528</td>\n",
       "      <td>0.477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>adam</td>\n",
       "      <td>jordan</td>\n",
       "      <td>0.500</td>\n",
       "      <td>0.543</td>\n",
       "      <td>0.462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>adam</td>\n",
       "      <td>daniel</td>\n",
       "      <td>0.515</td>\n",
       "      <td>0.496</td>\n",
       "      <td>0.508</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>adam</td>\n",
       "      <td>colin</td>\n",
       "      <td>0.551</td>\n",
       "      <td>0.518</td>\n",
       "      <td>0.483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>adam</td>\n",
       "      <td>adam</td>\n",
       "      <td>0.993</td>\n",
       "      <td>0.506</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>andres</td>\n",
       "      <td>colin</td>\n",
       "      <td>0.434</td>\n",
       "      <td>0.504</td>\n",
       "      <td>0.479</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>andres</td>\n",
       "      <td>daniel</td>\n",
       "      <td>0.445</td>\n",
       "      <td>0.478</td>\n",
       "      <td>0.500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>andres</td>\n",
       "      <td>adam</td>\n",
       "      <td>0.467</td>\n",
       "      <td>0.496</td>\n",
       "      <td>0.486</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>andres</td>\n",
       "      <td>james</td>\n",
       "      <td>0.507</td>\n",
       "      <td>0.523</td>\n",
       "      <td>0.457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>andres</td>\n",
       "      <td>greg</td>\n",
       "      <td>0.529</td>\n",
       "      <td>0.511</td>\n",
       "      <td>0.467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>andres</td>\n",
       "      <td>jordan</td>\n",
       "      <td>0.596</td>\n",
       "      <td>0.526</td>\n",
       "      <td>0.437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>andres</td>\n",
       "      <td>andres</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.490</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>colin</td>\n",
       "      <td>andres</td>\n",
       "      <td>0.434</td>\n",
       "      <td>0.504</td>\n",
       "      <td>0.521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>colin</td>\n",
       "      <td>daniel</td>\n",
       "      <td>0.474</td>\n",
       "      <td>0.504</td>\n",
       "      <td>0.522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>colin</td>\n",
       "      <td>jordan</td>\n",
       "      <td>0.482</td>\n",
       "      <td>0.556</td>\n",
       "      <td>0.474</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>colin</td>\n",
       "      <td>greg</td>\n",
       "      <td>0.507</td>\n",
       "      <td>0.534</td>\n",
       "      <td>0.492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>colin</td>\n",
       "      <td>james</td>\n",
       "      <td>0.515</td>\n",
       "      <td>0.545</td>\n",
       "      <td>0.480</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>colin</td>\n",
       "      <td>adam</td>\n",
       "      <td>0.551</td>\n",
       "      <td>0.518</td>\n",
       "      <td>0.508</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>colin</td>\n",
       "      <td>colin</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.514</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>daniel</td>\n",
       "      <td>andres</td>\n",
       "      <td>0.445</td>\n",
       "      <td>0.478</td>\n",
       "      <td>0.500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>daniel</td>\n",
       "      <td>colin</td>\n",
       "      <td>0.474</td>\n",
       "      <td>0.504</td>\n",
       "      <td>0.478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>daniel</td>\n",
       "      <td>adam</td>\n",
       "      <td>0.515</td>\n",
       "      <td>0.496</td>\n",
       "      <td>0.484</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>daniel</td>\n",
       "      <td>jordan</td>\n",
       "      <td>0.515</td>\n",
       "      <td>0.533</td>\n",
       "      <td>0.444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>daniel</td>\n",
       "      <td>james</td>\n",
       "      <td>0.526</td>\n",
       "      <td>0.522</td>\n",
       "      <td>0.455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>daniel</td>\n",
       "      <td>greg</td>\n",
       "      <td>0.533</td>\n",
       "      <td>0.511</td>\n",
       "      <td>0.467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>daniel</td>\n",
       "      <td>daniel</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.490</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>greg</td>\n",
       "      <td>adam</td>\n",
       "      <td>0.493</td>\n",
       "      <td>0.528</td>\n",
       "      <td>0.515</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>greg</td>\n",
       "      <td>colin</td>\n",
       "      <td>0.507</td>\n",
       "      <td>0.534</td>\n",
       "      <td>0.508</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>greg</td>\n",
       "      <td>andres</td>\n",
       "      <td>0.529</td>\n",
       "      <td>0.511</td>\n",
       "      <td>0.533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>greg</td>\n",
       "      <td>daniel</td>\n",
       "      <td>0.533</td>\n",
       "      <td>0.511</td>\n",
       "      <td>0.533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>greg</td>\n",
       "      <td>james</td>\n",
       "      <td>0.537</td>\n",
       "      <td>0.550</td>\n",
       "      <td>0.487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>greg</td>\n",
       "      <td>jordan</td>\n",
       "      <td>0.570</td>\n",
       "      <td>0.554</td>\n",
       "      <td>0.477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>greg</td>\n",
       "      <td>greg</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.521</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>james</td>\n",
       "      <td>adam</td>\n",
       "      <td>0.467</td>\n",
       "      <td>0.549</td>\n",
       "      <td>0.518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>james</td>\n",
       "      <td>andres</td>\n",
       "      <td>0.507</td>\n",
       "      <td>0.523</td>\n",
       "      <td>0.543</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>james</td>\n",
       "      <td>colin</td>\n",
       "      <td>0.515</td>\n",
       "      <td>0.545</td>\n",
       "      <td>0.520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>james</td>\n",
       "      <td>daniel</td>\n",
       "      <td>0.526</td>\n",
       "      <td>0.522</td>\n",
       "      <td>0.545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>james</td>\n",
       "      <td>greg</td>\n",
       "      <td>0.537</td>\n",
       "      <td>0.550</td>\n",
       "      <td>0.513</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>james</td>\n",
       "      <td>jordan</td>\n",
       "      <td>0.548</td>\n",
       "      <td>0.570</td>\n",
       "      <td>0.487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>james</td>\n",
       "      <td>james</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.533</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>jordan</td>\n",
       "      <td>colin</td>\n",
       "      <td>0.482</td>\n",
       "      <td>0.556</td>\n",
       "      <td>0.526</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>jordan</td>\n",
       "      <td>adam</td>\n",
       "      <td>0.500</td>\n",
       "      <td>0.543</td>\n",
       "      <td>0.538</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>jordan</td>\n",
       "      <td>daniel</td>\n",
       "      <td>0.515</td>\n",
       "      <td>0.533</td>\n",
       "      <td>0.548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>jordan</td>\n",
       "      <td>james</td>\n",
       "      <td>0.548</td>\n",
       "      <td>0.570</td>\n",
       "      <td>0.504</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>jordan</td>\n",
       "      <td>greg</td>\n",
       "      <td>0.570</td>\n",
       "      <td>0.554</td>\n",
       "      <td>0.523</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>jordan</td>\n",
       "      <td>andres</td>\n",
       "      <td>0.596</td>\n",
       "      <td>0.526</td>\n",
       "      <td>0.563</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>jordan</td>\n",
       "      <td>jordan</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.541</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        p1      p2  agree_rate  win_pct_when_agree  p1_win_pct_when_disagree\n",
       "1     adam  andres       0.467               0.496                     0.507\n",
       "5     adam   james       0.467               0.549                     0.460\n",
       "3     adam    greg       0.493               0.528                     0.477\n",
       "4     adam  jordan       0.500               0.543                     0.462\n",
       "2     adam  daniel       0.515               0.496                     0.508\n",
       "6     adam   colin       0.551               0.518                     0.483\n",
       "0     adam    adam       0.993               0.506                     0.000\n",
       "13  andres   colin       0.434               0.504                     0.479\n",
       "9   andres  daniel       0.445               0.478                     0.500\n",
       "7   andres    adam       0.467               0.496                     0.486\n",
       "12  andres   james       0.507               0.523                     0.457\n",
       "10  andres    greg       0.529               0.511                     0.467\n",
       "11  andres  jordan       0.596               0.526                     0.437\n",
       "8   andres  andres       1.000               0.490                       NaN\n",
       "43   colin  andres       0.434               0.504                     0.521\n",
       "44   colin  daniel       0.474               0.504                     0.522\n",
       "46   colin  jordan       0.482               0.556                     0.474\n",
       "45   colin    greg       0.507               0.534                     0.492\n",
       "47   colin   james       0.515               0.545                     0.480\n",
       "42   colin    adam       0.551               0.518                     0.508\n",
       "48   colin   colin       1.000               0.514                       NaN\n",
       "15  daniel  andres       0.445               0.478                     0.500\n",
       "20  daniel   colin       0.474               0.504                     0.478\n",
       "14  daniel    adam       0.515               0.496                     0.484\n",
       "18  daniel  jordan       0.515               0.533                     0.444\n",
       "19  daniel   james       0.526               0.522                     0.455\n",
       "17  daniel    greg       0.533               0.511                     0.467\n",
       "16  daniel  daniel       1.000               0.490                       NaN\n",
       "21    greg    adam       0.493               0.528                     0.515\n",
       "27    greg   colin       0.507               0.534                     0.508\n",
       "22    greg  andres       0.529               0.511                     0.533\n",
       "23    greg  daniel       0.533               0.511                     0.533\n",
       "26    greg   james       0.537               0.550                     0.487\n",
       "25    greg  jordan       0.570               0.554                     0.477\n",
       "24    greg    greg       1.000               0.521                       NaN\n",
       "35   james    adam       0.467               0.549                     0.518\n",
       "36   james  andres       0.507               0.523                     0.543\n",
       "41   james   colin       0.515               0.545                     0.520\n",
       "37   james  daniel       0.526               0.522                     0.545\n",
       "38   james    greg       0.537               0.550                     0.513\n",
       "39   james  jordan       0.548               0.570                     0.487\n",
       "40   james   james       1.000               0.533                       NaN\n",
       "34  jordan   colin       0.482               0.556                     0.526\n",
       "28  jordan    adam       0.500               0.543                     0.538\n",
       "30  jordan  daniel       0.515               0.533                     0.548\n",
       "33  jordan   james       0.548               0.570                     0.504\n",
       "31  jordan    greg       0.570               0.554                     0.523\n",
       "29  jordan  andres       0.596               0.526                     0.563\n",
       "32  jordan  jordan       1.000               0.541                       NaN"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comp_statistics.sort_values(by=['p1','agree_rate'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c80e6732",
   "metadata": {},
   "source": [
    "## ATS Bonus"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "fb1db89b",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_ats_bonus(week_num):\n",
    "    df = dh.load_pickem_raw(week_num)\n",
    "    first_row = df.index[df.isnull().all(1)].values[0]\n",
    "    df = df[:first_row]\n",
    "    df.drop(['Timestamp', 'Name', 'Email'], axis=1, inplace=True, errors='ignore')\n",
    "    df.rename(columns={'Email Address': 'email', 'ATS Bonus':'bonus'}, inplace=True)\n",
    "    df = df.loc[:, 'email':'bonus']\n",
    "    df['week_num'] = week_num\n",
    "    df['user_id'] = df['email'].apply(lambda x: name_dict[x])\n",
    "    df['bonus'] = df['bonus'].str.strip()\n",
    "    return df[['user_id','bonus','week_num']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "3138e759",
   "metadata": {},
   "outputs": [],
   "source": [
    "bonus_dfs = []\n",
    "for i in range(1,19):\n",
    "    bonus_dfs.append(get_ats_bonus(i))\n",
    "bonus_df = pd.concat(bonus_dfs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "db180e82",
   "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>user_id</th>\n",
       "      <th>bonus</th>\n",
       "      <th>num_ats</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>greg</td>\n",
       "      <td>Broncos</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>greg</td>\n",
       "      <td>Browns</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>greg</td>\n",
       "      <td>Chiefs</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>greg</td>\n",
       "      <td>Raiders</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>greg</td>\n",
       "      <td>Texans</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>greg</td>\n",
       "      <td>Vikings</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>greg</td>\n",
       "      <td>Buccaneers</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>greg</td>\n",
       "      <td>Colts</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>greg</td>\n",
       "      <td>Eagles</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>greg</td>\n",
       "      <td>Patriots</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>greg</td>\n",
       "      <td>Ravens</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>greg</td>\n",
       "      <td>Seahawks</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id       bonus  num_ats\n",
       "11    greg     Broncos        2\n",
       "12    greg      Browns        2\n",
       "15    greg      Chiefs        2\n",
       "24    greg     Raiders        2\n",
       "28    greg      Texans        2\n",
       "30    greg     Vikings        2\n",
       "40    greg  Buccaneers        1\n",
       "49    greg       Colts        1\n",
       "56    greg      Eagles        1\n",
       "66    greg    Patriots        1\n",
       "70    greg      Ravens        1\n",
       "74    greg    Seahawks        1"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "q = '''\n",
    "select \n",
    "    user_id,\n",
    "    bonus, \n",
    "    count(*) num_ats\n",
    "from bonus_df\n",
    "group by 1,2\n",
    "order by 3 desc, 2\n",
    "'''\n",
    "df = sqldf(q)\n",
    "df[df['user_id'] == 'greg']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7419c50",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}