twitch_csv_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pathlib import Path\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "import charset_normalizer as charn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(18600, 12)\n"
     ]
    },
    {
     "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>Rank</th>\n",
       "      <th>Game</th>\n",
       "      <th>Month</th>\n",
       "      <th>Year</th>\n",
       "      <th>Hours Watched</th>\n",
       "      <th>Hours Streamed</th>\n",
       "      <th>Peak Viewers</th>\n",
       "      <th>Peak Channels</th>\n",
       "      <th>Streamers</th>\n",
       "      <th>Average Viewers</th>\n",
       "      <th>Average Channels</th>\n",
       "      <th>Average Viewer Ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>League of Legends</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>94377226</td>\n",
       "      <td>1362044</td>\n",
       "      <td>530270</td>\n",
       "      <td>2903</td>\n",
       "      <td>129172</td>\n",
       "      <td>127021</td>\n",
       "      <td>1833</td>\n",
       "      <td>69.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Counter-Strike: Global Offensive</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>47832863</td>\n",
       "      <td>830105</td>\n",
       "      <td>372654</td>\n",
       "      <td>2197</td>\n",
       "      <td>120849</td>\n",
       "      <td>64378</td>\n",
       "      <td>1117</td>\n",
       "      <td>57.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Dota 2</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>45185893</td>\n",
       "      <td>433397</td>\n",
       "      <td>315083</td>\n",
       "      <td>1100</td>\n",
       "      <td>44074</td>\n",
       "      <td>60815</td>\n",
       "      <td>583</td>\n",
       "      <td>104.26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Rank                              Game  Month  Year  Hours Watched  \\\n",
       "0     1                 League of Legends      1  2016       94377226   \n",
       "1     2  Counter-Strike: Global Offensive      1  2016       47832863   \n",
       "2     3                            Dota 2      1  2016       45185893   \n",
       "\n",
       "   Hours Streamed  Peak Viewers  Peak Channels  Streamers  Average Viewers  \\\n",
       "0         1362044        530270           2903     129172           127021   \n",
       "1          830105        372654           2197     120849            64378   \n",
       "2          433397        315083           1100      44074            60815   \n",
       "\n",
       "   Average Channels  Average Viewer Ratio  \n",
       "0              1833                 69.29  \n",
       "1              1117                 57.62  \n",
       "2               583                104.26  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from charset_normalizer import from_path\n",
    "csv_path = 'Resources/Twitch_game_data_fixed_names.csv'\n",
    "\n",
    "data = from_path(csv_path).best()\n",
    "encoding = data.encoding_aliases[1]\n",
    "twitch_df = pd.read_csv(csv_path, encoding=encoding)\n",
    "print(twitch_df.shape)\n",
    "twitch_df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows: 18600\n"
     ]
    }
   ],
   "source": [
    "# Determine number of rows in csv file\n",
    "num_rows = len(twitch_df)\n",
    "print(\"Number of rows:\", num_rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Rank                      int64\n",
       "Game                     object\n",
       "Month                     int64\n",
       "Year                      int64\n",
       "Hours Watched             int64\n",
       "Hours Streamed            int64\n",
       "Peak Viewers              int64\n",
       "Peak Channels             int64\n",
       "Streamers                 int64\n",
       "Average Viewers           int64\n",
       "Average Channels          int64\n",
       "Average Viewer Ratio    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Determine data types in each column\n",
    "twitch_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Rank</th>\n",
       "      <th>Title</th>\n",
       "      <th>Month</th>\n",
       "      <th>Year</th>\n",
       "      <th>Hours Watched</th>\n",
       "      <th>Hours Streamed</th>\n",
       "      <th>Peak Viewers</th>\n",
       "      <th>Peak Channels</th>\n",
       "      <th>Streamers</th>\n",
       "      <th>Average Viewers</th>\n",
       "      <th>Average Channels</th>\n",
       "      <th>Average Viewer Ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>League of Legends</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>94377226</td>\n",
       "      <td>1362044</td>\n",
       "      <td>530270</td>\n",
       "      <td>2903</td>\n",
       "      <td>129172</td>\n",
       "      <td>127021</td>\n",
       "      <td>1833</td>\n",
       "      <td>69.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Counter-Strike: Global Offensive</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>47832863</td>\n",
       "      <td>830105</td>\n",
       "      <td>372654</td>\n",
       "      <td>2197</td>\n",
       "      <td>120849</td>\n",
       "      <td>64378</td>\n",
       "      <td>1117</td>\n",
       "      <td>57.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Dota 2</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>45185893</td>\n",
       "      <td>433397</td>\n",
       "      <td>315083</td>\n",
       "      <td>1100</td>\n",
       "      <td>44074</td>\n",
       "      <td>60815</td>\n",
       "      <td>583</td>\n",
       "      <td>104.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Hearthstone</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>39936159</td>\n",
       "      <td>235903</td>\n",
       "      <td>131357</td>\n",
       "      <td>517</td>\n",
       "      <td>36170</td>\n",
       "      <td>53749</td>\n",
       "      <td>317</td>\n",
       "      <td>169.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Call of Duty: Black Ops III</td>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>16153057</td>\n",
       "      <td>1151578</td>\n",
       "      <td>71639</td>\n",
       "      <td>3620</td>\n",
       "      <td>214054</td>\n",
       "      <td>21740</td>\n",
       "      <td>1549</td>\n",
       "      <td>14.03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Rank                             Title  Month  Year  Hours Watched  \\\n",
       "0     1                 League of Legends      1  2016       94377226   \n",
       "1     2  Counter-Strike: Global Offensive      1  2016       47832863   \n",
       "2     3                            Dota 2      1  2016       45185893   \n",
       "3     4                       Hearthstone      1  2016       39936159   \n",
       "4     5       Call of Duty: Black Ops III      1  2016       16153057   \n",
       "\n",
       "   Hours Streamed  Peak Viewers  Peak Channels  Streamers  Average Viewers  \\\n",
       "0         1362044        530270           2903     129172           127021   \n",
       "1          830105        372654           2197     120849            64378   \n",
       "2          433397        315083           1100      44074            60815   \n",
       "3          235903        131357            517      36170            53749   \n",
       "4         1151578         71639           3620     214054            21740   \n",
       "\n",
       "   Average Channels  Average Viewer Ratio  \n",
       "0              1833                 69.29  \n",
       "1              1117                 57.62  \n",
       "2               583                104.26  \n",
       "3               317                169.29  \n",
       "4              1549                 14.03  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Rename \"Game\" column to \"Title\", so to match GAME DATE.csv\n",
    "twitch_df.rename(columns={'Game': \"Title\"}, inplace=True)\n",
    "twitch_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The number of unqiue game titles that are listed in this csv file: 2160\n"
     ]
    }
   ],
   "source": [
    "# Select 'Title' columen and identify the number of unqiue Game Titles.\n",
    "unique_title = twitch_df['Title'].nunique()\n",
    "print(f'The number of unqiue game titles that are listed in this csv file: {unique_title}')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Rank</th>\n",
       "      <th>Title</th>\n",
       "      <th>Month</th>\n",
       "      <th>Year</th>\n",
       "      <th>Hours Watched</th>\n",
       "      <th>Hours Streamed</th>\n",
       "      <th>Peak Viewers</th>\n",
       "      <th>Peak Channels</th>\n",
       "      <th>Streamers</th>\n",
       "      <th>Average Viewers</th>\n",
       "      <th>Average Channels</th>\n",
       "      <th>Average Viewer Ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4598</th>\n",
       "      <td>199</td>\n",
       "      <td>.hack//G.U. Last Recode</td>\n",
       "      <td>11</td>\n",
       "      <td>2017</td>\n",
       "      <td>145350</td>\n",
       "      <td>35258</td>\n",
       "      <td>1222</td>\n",
       "      <td>258</td>\n",
       "      <td>3174</td>\n",
       "      <td>202</td>\n",
       "      <td>49</td>\n",
       "      <td>4.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15558</th>\n",
       "      <td>159</td>\n",
       "      <td>20 Minutes Till Dawn</td>\n",
       "      <td>6</td>\n",
       "      <td>2022</td>\n",
       "      <td>911356</td>\n",
       "      <td>12253</td>\n",
       "      <td>29743</td>\n",
       "      <td>80</td>\n",
       "      <td>3616</td>\n",
       "      <td>1267</td>\n",
       "      <td>17</td>\n",
       "      <td>74.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6508</th>\n",
       "      <td>109</td>\n",
       "      <td>60 Parsecs!</td>\n",
       "      <td>9</td>\n",
       "      <td>2018</td>\n",
       "      <td>529688</td>\n",
       "      <td>1867</td>\n",
       "      <td>31960</td>\n",
       "      <td>27</td>\n",
       "      <td>606</td>\n",
       "      <td>736</td>\n",
       "      <td>2</td>\n",
       "      <td>283.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1325</th>\n",
       "      <td>126</td>\n",
       "      <td>60 Seconds!</td>\n",
       "      <td>7</td>\n",
       "      <td>2016</td>\n",
       "      <td>268754</td>\n",
       "      <td>597</td>\n",
       "      <td>32505</td>\n",
       "      <td>10</td>\n",
       "      <td>275</td>\n",
       "      <td>361</td>\n",
       "      <td>0</td>\n",
       "      <td>450.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1453</th>\n",
       "      <td>54</td>\n",
       "      <td>60 Seconds!</td>\n",
       "      <td>8</td>\n",
       "      <td>2016</td>\n",
       "      <td>772786</td>\n",
       "      <td>2065</td>\n",
       "      <td>56904</td>\n",
       "      <td>30</td>\n",
       "      <td>628</td>\n",
       "      <td>1040</td>\n",
       "      <td>2</td>\n",
       "      <td>374.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2198</th>\n",
       "      <td>199</td>\n",
       "      <td>60 Seconds!</td>\n",
       "      <td>11</td>\n",
       "      <td>2016</td>\n",
       "      <td>109473</td>\n",
       "      <td>503</td>\n",
       "      <td>5297</td>\n",
       "      <td>5</td>\n",
       "      <td>193</td>\n",
       "      <td>152</td>\n",
       "      <td>0</td>\n",
       "      <td>217.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2918</th>\n",
       "      <td>119</td>\n",
       "      <td>60 Seconds!</td>\n",
       "      <td>3</td>\n",
       "      <td>2017</td>\n",
       "      <td>263482</td>\n",
       "      <td>679</td>\n",
       "      <td>31311</td>\n",
       "      <td>10</td>\n",
       "      <td>240</td>\n",
       "      <td>354</td>\n",
       "      <td>0</td>\n",
       "      <td>388.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4581</th>\n",
       "      <td>182</td>\n",
       "      <td>60 Seconds!</td>\n",
       "      <td>11</td>\n",
       "      <td>2017</td>\n",
       "      <td>179250</td>\n",
       "      <td>584</td>\n",
       "      <td>10458</td>\n",
       "      <td>8</td>\n",
       "      <td>228</td>\n",
       "      <td>249</td>\n",
       "      <td>0</td>\n",
       "      <td>306.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4785</th>\n",
       "      <td>186</td>\n",
       "      <td>60 Seconds!</td>\n",
       "      <td>12</td>\n",
       "      <td>2017</td>\n",
       "      <td>180788</td>\n",
       "      <td>669</td>\n",
       "      <td>23476</td>\n",
       "      <td>9</td>\n",
       "      <td>260</td>\n",
       "      <td>243</td>\n",
       "      <td>0</td>\n",
       "      <td>270.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6393</th>\n",
       "      <td>194</td>\n",
       "      <td>60 Seconds!</td>\n",
       "      <td>8</td>\n",
       "      <td>2018</td>\n",
       "      <td>222209</td>\n",
       "      <td>871</td>\n",
       "      <td>18245</td>\n",
       "      <td>8</td>\n",
       "      <td>414</td>\n",
       "      <td>299</td>\n",
       "      <td>1</td>\n",
       "      <td>255.12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Rank                    Title  Month  Year  Hours Watched  \\\n",
       "4598    199  .hack//G.U. Last Recode     11  2017         145350   \n",
       "15558   159     20 Minutes Till Dawn      6  2022         911356   \n",
       "6508    109              60 Parsecs!      9  2018         529688   \n",
       "1325    126              60 Seconds!      7  2016         268754   \n",
       "1453     54              60 Seconds!      8  2016         772786   \n",
       "2198    199              60 Seconds!     11  2016         109473   \n",
       "2918    119              60 Seconds!      3  2017         263482   \n",
       "4581    182              60 Seconds!     11  2017         179250   \n",
       "4785    186              60 Seconds!     12  2017         180788   \n",
       "6393    194              60 Seconds!      8  2018         222209   \n",
       "\n",
       "       Hours Streamed  Peak Viewers  Peak Channels  Streamers  \\\n",
       "4598            35258          1222            258       3174   \n",
       "15558           12253         29743             80       3616   \n",
       "6508             1867         31960             27        606   \n",
       "1325              597         32505             10        275   \n",
       "1453             2065         56904             30        628   \n",
       "2198              503          5297              5        193   \n",
       "2918              679         31311             10        240   \n",
       "4581              584         10458              8        228   \n",
       "4785              669         23476              9        260   \n",
       "6393              871         18245              8        414   \n",
       "\n",
       "       Average Viewers  Average Channels  Average Viewer Ratio  \n",
       "4598               202                49                  4.12  \n",
       "15558             1267                17                 74.38  \n",
       "6508               736                 2                283.71  \n",
       "1325               361                 0                450.17  \n",
       "1453              1040                 2                374.23  \n",
       "2198               152                 0                217.64  \n",
       "2918               354                 0                388.04  \n",
       "4581               249                 0                306.93  \n",
       "4785               243                 0                270.24  \n",
       "6393               299                 1                255.12  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sort data by 'Title' and 'Year'\n",
    "twitch_df_sorted = twitch_df.sort_values(by=['Title', 'Year'])\n",
    "twitch_df_sorted.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "twitch_df = twitch_df.drop(columns=[])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>title</th>\n",
       "      <th>avg_hours_watched</th>\n",
       "      <th>avg_hours_streamed</th>\n",
       "      <th>avg_streamers</th>\n",
       "      <th>avg_viewers</th>\n",
       "      <th>avg_channels</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>.hack//G.U. Last Recode</td>\n",
       "      <td>145350</td>\n",
       "      <td>35258</td>\n",
       "      <td>3174</td>\n",
       "      <td>202</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20 Minutes Till Dawn</td>\n",
       "      <td>911356</td>\n",
       "      <td>12253</td>\n",
       "      <td>3616</td>\n",
       "      <td>1267</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>60 Parsecs!</td>\n",
       "      <td>529688</td>\n",
       "      <td>1867</td>\n",
       "      <td>606</td>\n",
       "      <td>736</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>60 Seconds!</td>\n",
       "      <td>314900</td>\n",
       "      <td>1023</td>\n",
       "      <td>420</td>\n",
       "      <td>424</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7 Days to Die</td>\n",
       "      <td>1023033</td>\n",
       "      <td>89486</td>\n",
       "      <td>8880</td>\n",
       "      <td>1397</td>\n",
       "      <td>122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A Dance of Fire and Ice</td>\n",
       "      <td>452164</td>\n",
       "      <td>7002</td>\n",
       "      <td>2833</td>\n",
       "      <td>645</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>A Hat in Time</td>\n",
       "      <td>331964</td>\n",
       "      <td>13813</td>\n",
       "      <td>2510</td>\n",
       "      <td>450</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>A Plague Tale: Innocence</td>\n",
       "      <td>1700679</td>\n",
       "      <td>36870</td>\n",
       "      <td>5592</td>\n",
       "      <td>2305</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>A Plague Tale: Requiem</td>\n",
       "      <td>2512917</td>\n",
       "      <td>96420</td>\n",
       "      <td>15296</td>\n",
       "      <td>3399</td>\n",
       "      <td>130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>A Total War Saga: Troy</td>\n",
       "      <td>1019391</td>\n",
       "      <td>23357</td>\n",
       "      <td>3543</td>\n",
       "      <td>1371</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      title  avg_hours_watched  avg_hours_streamed  \\\n",
       "0   .hack//G.U. Last Recode             145350               35258   \n",
       "1      20 Minutes Till Dawn             911356               12253   \n",
       "2               60 Parsecs!             529688                1867   \n",
       "3               60 Seconds!             314900                1023   \n",
       "4             7 Days to Die            1023033               89486   \n",
       "5   A Dance of Fire and Ice             452164                7002   \n",
       "6             A Hat in Time             331964               13813   \n",
       "7  A Plague Tale: Innocence            1700679               36870   \n",
       "8    A Plague Tale: Requiem            2512917               96420   \n",
       "9    A Total War Saga: Troy            1019391               23357   \n",
       "\n",
       "   avg_streamers  avg_viewers  avg_channels  \n",
       "0           3174          202            49  \n",
       "1           3616         1267            17  \n",
       "2            606          736             2  \n",
       "3            420          424             0  \n",
       "4           8880         1397           122  \n",
       "5           2833          645             9  \n",
       "6           2510          450            18  \n",
       "7           5592         2305            49  \n",
       "8          15296         3399           130  \n",
       "9           3543         1371            31  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Group by 'Title' and calculate average values for the relevant columns\n",
    "twitch_df_grouped = twitch_df.groupby('Title').agg({\n",
    "    'Hours Watched': 'mean',         # Average hours watched for each title\n",
    "    'Hours Streamed': 'mean',        # Average hours streamed for each title\n",
    "    'Streamers': 'mean',            # Average number of streamers for each title\n",
    "    'Average Viewers': 'mean',      # Average number of average viewers for each title\n",
    "    'Average Channels': 'mean'      # Average number of average channels for each title\n",
    "}).reset_index()\n",
    "\n",
    "# Rename headers to match format of 'clean_steaam_dtypes.csv' file\n",
    "twitch_df_grouped.rename(columns={\n",
    "    'Title': 'title',\n",
    "    'Hours Watched': 'avg_hours_watched',\n",
    "    'Hours Streamed': 'avg_hours_streamed',\n",
    "    'Streamers': 'avg_streamers',\n",
    "    'Average Viewers': 'avg_viewers',\n",
    "    'Average Channels': 'avg_channels'\n",
    "}, inplace= True)\n",
    "\n",
    "# Round down all average values using np.floor\n",
    "twitch_df_grouped['avg_hours_watched'] = np.floor(twitch_df_grouped['avg_hours_watched']).astype(int)\n",
    "twitch_df_grouped['avg_hours_streamed'] = np.floor(twitch_df_grouped['avg_hours_streamed']).astype(int)\n",
    "twitch_df_grouped['avg_streamers'] = np.floor(twitch_df_grouped['avg_streamers']).astype(int)\n",
    "twitch_df_grouped['avg_viewers'] = np.floor(twitch_df_grouped['avg_viewers']).astype(int)\n",
    "twitch_df_grouped['avg_channels'] = np.floor(twitch_df_grouped['avg_channels']).astype(int)\n",
    "\n",
    "twitch_df_grouped.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>app_id</th>\n",
       "      <th>title</th>\n",
       "      <th>release_date</th>\n",
       "      <th>reviews_total</th>\n",
       "      <th>review_avg_percent</th>\n",
       "      <th>launch_price_cents</th>\n",
       "      <th>dataset_est_rev_cents</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>730</td>\n",
       "      <td>Counter-Strike: Global Offensive</td>\n",
       "      <td>2012-08-21</td>\n",
       "      <td>7382695</td>\n",
       "      <td>88.00</td>\n",
       "      <td>1499</td>\n",
       "      <td>11066659805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>578080</td>\n",
       "      <td>PUBG: BATTLEGROUNDS</td>\n",
       "      <td>2017-12-21</td>\n",
       "      <td>2201296</td>\n",
       "      <td>57.00</td>\n",
       "      <td>2999</td>\n",
       "      <td>6601686704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>570</td>\n",
       "      <td>Dota 2</td>\n",
       "      <td>2013-07-09</td>\n",
       "      <td>2017009</td>\n",
       "      <td>82.00</td>\n",
       "      <td>2999</td>\n",
       "      <td>6049009991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>271590</td>\n",
       "      <td>Grand Theft Auto V</td>\n",
       "      <td>2015-04-13</td>\n",
       "      <td>1322782</td>\n",
       "      <td>89.85</td>\n",
       "      <td>2999</td>\n",
       "      <td>3967023218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>359550</td>\n",
       "      <td>Tom Clancy's Rainbow Six® Siege</td>\n",
       "      <td>2015-12-01</td>\n",
       "      <td>978762</td>\n",
       "      <td>86.00</td>\n",
       "      <td>5999</td>\n",
       "      <td>5871593238</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   app_id                             title release_date  reviews_total  \\\n",
       "0     730  Counter-Strike: Global Offensive   2012-08-21        7382695   \n",
       "1  578080               PUBG: BATTLEGROUNDS   2017-12-21        2201296   \n",
       "2     570                            Dota 2   2013-07-09        2017009   \n",
       "3  271590                Grand Theft Auto V   2015-04-13        1322782   \n",
       "4  359550   Tom Clancy's Rainbow Six® Siege   2015-12-01         978762   \n",
       "\n",
       "   review_avg_percent  launch_price_cents  dataset_est_rev_cents  \n",
       "0               88.00                1499            11066659805  \n",
       "1               57.00                2999             6601686704  \n",
       "2               82.00                2999             6049009991  \n",
       "3               89.85                2999             3967023218  \n",
       "4               86.00                5999             5871593238  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read 'clean_system_dtypes.csv' file and create dataframe \n",
    "game_data_csv = pd.read_csv('Resources/clean_steam_dtypes.csv')\n",
    "game_data_csv.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>title</th>\n",
       "      <th>avg_hours_watched</th>\n",
       "      <th>avg_hours_streamed</th>\n",
       "      <th>avg_streamers</th>\n",
       "      <th>avg_viewers</th>\n",
       "      <th>avg_channels</th>\n",
       "      <th>app_id</th>\n",
       "      <th>release_date</th>\n",
       "      <th>reviews_total</th>\n",
       "      <th>review_avg_percent</th>\n",
       "      <th>launch_price_cents</th>\n",
       "      <th>dataset_est_rev_cents</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>.hack//G.U. Last Recode</td>\n",
       "      <td>145350</td>\n",
       "      <td>35258</td>\n",
       "      <td>3174</td>\n",
       "      <td>202</td>\n",
       "      <td>49</td>\n",
       "      <td>525480</td>\n",
       "      <td>2017-11-03</td>\n",
       "      <td>2689</td>\n",
       "      <td>87.0</td>\n",
       "      <td>4999</td>\n",
       "      <td>13442311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20 Minutes Till Dawn</td>\n",
       "      <td>911356</td>\n",
       "      <td>12253</td>\n",
       "      <td>3616</td>\n",
       "      <td>1267</td>\n",
       "      <td>17</td>\n",
       "      <td>1966900</td>\n",
       "      <td>2023-06-08</td>\n",
       "      <td>20942</td>\n",
       "      <td>92.0</td>\n",
       "      <td>499</td>\n",
       "      <td>10450058</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>60 Parsecs!</td>\n",
       "      <td>529688</td>\n",
       "      <td>1867</td>\n",
       "      <td>606</td>\n",
       "      <td>736</td>\n",
       "      <td>2</td>\n",
       "      <td>646270</td>\n",
       "      <td>2018-09-18</td>\n",
       "      <td>2315</td>\n",
       "      <td>89.0</td>\n",
       "      <td>999</td>\n",
       "      <td>2312685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>60 Seconds!</td>\n",
       "      <td>314900</td>\n",
       "      <td>1023</td>\n",
       "      <td>420</td>\n",
       "      <td>424</td>\n",
       "      <td>0</td>\n",
       "      <td>368360</td>\n",
       "      <td>2015-05-25</td>\n",
       "      <td>9979</td>\n",
       "      <td>84.0</td>\n",
       "      <td>899</td>\n",
       "      <td>8971121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7 Days to Die</td>\n",
       "      <td>1023033</td>\n",
       "      <td>89486</td>\n",
       "      <td>8880</td>\n",
       "      <td>1397</td>\n",
       "      <td>122</td>\n",
       "      <td>251570</td>\n",
       "      <td>2013-12-13</td>\n",
       "      <td>185945</td>\n",
       "      <td>88.0</td>\n",
       "      <td>2499</td>\n",
       "      <td>464676555</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     title  avg_hours_watched  avg_hours_streamed  \\\n",
       "0  .hack//G.U. Last Recode             145350               35258   \n",
       "1     20 Minutes Till Dawn             911356               12253   \n",
       "2              60 Parsecs!             529688                1867   \n",
       "3              60 Seconds!             314900                1023   \n",
       "4            7 Days to Die            1023033               89486   \n",
       "\n",
       "   avg_streamers  avg_viewers  avg_channels   app_id release_date  \\\n",
       "0           3174          202            49   525480   2017-11-03   \n",
       "1           3616         1267            17  1966900   2023-06-08   \n",
       "2            606          736             2   646270   2018-09-18   \n",
       "3            420          424             0   368360   2015-05-25   \n",
       "4           8880         1397           122   251570   2013-12-13   \n",
       "\n",
       "   reviews_total  review_avg_percent  launch_price_cents  \\\n",
       "0           2689                87.0                4999   \n",
       "1          20942                92.0                 499   \n",
       "2           2315                89.0                 999   \n",
       "3           9979                84.0                 899   \n",
       "4         185945                88.0                2499   \n",
       "\n",
       "   dataset_est_rev_cents  \n",
       "0               13442311  \n",
       "1               10450058  \n",
       "2                2312685  \n",
       "3                8971121  \n",
       "4              464676555  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Strip any leading/trailing whitespace from column names (just in case)\n",
    "twitch_df_grouped.columns = twitch_df_grouped.columns.str.strip()\n",
    "game_data_csv.columns = game_data_csv.columns.str.strip()\n",
    "\n",
    "# Perform an inner join on the 'Title' column\n",
    "merged_df = pd.merge(twitch_df_grouped, game_data_csv, on='title', how='inner')\n",
    "merged_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Merged file saved successfully in 'Resources' folder.\n"
     ]
    }
   ],
   "source": [
    "# Save the merged DataFrame to a new CSV file in the 'Resources' folder\n",
    "merged_df.to_csv('Resources/merge_output.csv', index= False)\n",
    "print(\"Merged file saved successfully in 'Resources' folder.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>title</th>\n",
       "      <th>avg_hours_watched</th>\n",
       "      <th>avg_hours_streamed</th>\n",
       "      <th>avg_streamers</th>\n",
       "      <th>avg_viewers</th>\n",
       "      <th>avg_channels</th>\n",
       "      <th>app_id</th>\n",
       "      <th>release_date</th>\n",
       "      <th>reviews_total</th>\n",
       "      <th>review_avg_percent</th>\n",
       "      <th>launch_price_cents</th>\n",
       "      <th>dataset_est_rev_cents</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>.hack//G.U. Last Recode</td>\n",
       "      <td>145350</td>\n",
       "      <td>35258</td>\n",
       "      <td>3174</td>\n",
       "      <td>202</td>\n",
       "      <td>49</td>\n",
       "      <td>525480</td>\n",
       "      <td>2017-11-03</td>\n",
       "      <td>2689</td>\n",
       "      <td>87.0</td>\n",
       "      <td>4999</td>\n",
       "      <td>13442311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20 Minutes Till Dawn</td>\n",
       "      <td>911356</td>\n",
       "      <td>12253</td>\n",
       "      <td>3616</td>\n",
       "      <td>1267</td>\n",
       "      <td>17</td>\n",
       "      <td>1966900</td>\n",
       "      <td>2023-06-08</td>\n",
       "      <td>20942</td>\n",
       "      <td>92.0</td>\n",
       "      <td>499</td>\n",
       "      <td>10450058</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>60 Parsecs!</td>\n",
       "      <td>529688</td>\n",
       "      <td>1867</td>\n",
       "      <td>606</td>\n",
       "      <td>736</td>\n",
       "      <td>2</td>\n",
       "      <td>646270</td>\n",
       "      <td>2018-09-18</td>\n",
       "      <td>2315</td>\n",
       "      <td>89.0</td>\n",
       "      <td>999</td>\n",
       "      <td>2312685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>60 Seconds!</td>\n",
       "      <td>314900</td>\n",
       "      <td>1023</td>\n",
       "      <td>420</td>\n",
       "      <td>424</td>\n",
       "      <td>0</td>\n",
       "      <td>368360</td>\n",
       "      <td>2015-05-25</td>\n",
       "      <td>9979</td>\n",
       "      <td>84.0</td>\n",
       "      <td>899</td>\n",
       "      <td>8971121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7 Days to Die</td>\n",
       "      <td>1023033</td>\n",
       "      <td>89486</td>\n",
       "      <td>8880</td>\n",
       "      <td>1397</td>\n",
       "      <td>122</td>\n",
       "      <td>251570</td>\n",
       "      <td>2013-12-13</td>\n",
       "      <td>185945</td>\n",
       "      <td>88.0</td>\n",
       "      <td>2499</td>\n",
       "      <td>464676555</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     title  avg_hours_watched  avg_hours_streamed  \\\n",
       "0  .hack//G.U. Last Recode             145350               35258   \n",
       "1     20 Minutes Till Dawn             911356               12253   \n",
       "2              60 Parsecs!             529688                1867   \n",
       "3              60 Seconds!             314900                1023   \n",
       "4            7 Days to Die            1023033               89486   \n",
       "\n",
       "   avg_streamers  avg_viewers  avg_channels   app_id release_date  \\\n",
       "0           3174          202            49   525480   2017-11-03   \n",
       "1           3616         1267            17  1966900   2023-06-08   \n",
       "2            606          736             2   646270   2018-09-18   \n",
       "3            420          424             0   368360   2015-05-25   \n",
       "4           8880         1397           122   251570   2013-12-13   \n",
       "\n",
       "   reviews_total  review_avg_percent  launch_price_cents  \\\n",
       "0           2689                87.0                4999   \n",
       "1          20942                92.0                 499   \n",
       "2           2315                89.0                 999   \n",
       "3           9979                84.0                 899   \n",
       "4         185945                88.0                2499   \n",
       "\n",
       "   dataset_est_rev_cents  \n",
       "0               13442311  \n",
       "1               10450058  \n",
       "2                2312685  \n",
       "3                8971121  \n",
       "4              464676555  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check merged_df \n",
    "merged_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create a df of twitch data, not summary statistics "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>app_id</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>525480</td>\n",
       "      <td>.hack//G.U. Last Recode</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1966900</td>\n",
       "      <td>20 Minutes Till Dawn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>646270</td>\n",
       "      <td>60 Parsecs!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>368360</td>\n",
       "      <td>60 Seconds!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>251570</td>\n",
       "      <td>7 Days to Die</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>857</th>\n",
       "      <td>428690</td>\n",
       "      <td>Youtubers Life</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>858</th>\n",
       "      <td>311240</td>\n",
       "      <td>Zero Escape: Zero Time Dilemma</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>859</th>\n",
       "      <td>694280</td>\n",
       "      <td>Zombie Army 4: Dead War</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>860</th>\n",
       "      <td>266410</td>\n",
       "      <td>iRacing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>861</th>\n",
       "      <td>365960</td>\n",
       "      <td>rFactor 2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>862 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      app_id                           title\n",
       "0     525480         .hack//G.U. Last Recode\n",
       "1    1966900            20 Minutes Till Dawn\n",
       "2     646270                     60 Parsecs!\n",
       "3     368360                     60 Seconds!\n",
       "4     251570                   7 Days to Die\n",
       "..       ...                             ...\n",
       "857   428690                  Youtubers Life\n",
       "858   311240  Zero Escape: Zero Time Dilemma\n",
       "859   694280         Zombie Army 4: Dead War\n",
       "860   266410                         iRacing\n",
       "861   365960                       rFactor 2\n",
       "\n",
       "[862 rows x 2 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_of_valid_id_title = merged_df[['app_id', 'title']]\n",
    "df_of_valid_id_title"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['rank',\n",
       " 'title',\n",
       " 'month',\n",
       " 'year',\n",
       " 'hours_watched',\n",
       " 'hours_streamed',\n",
       " 'peak_viewers',\n",
       " 'peak_channels',\n",
       " 'streamers',\n",
       " 'average_viewers',\n",
       " 'average_channels']"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_col_names = [col.lower().replace(' ', '_') for col in twitch_df_sorted.columns]\n",
    "new_col_names[:-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "twitch_df_sorted.head(3)\n",
    "renamed_cols_twitch = twitch_df_sorted.rename(columns={\n",
    "    'Rank' : 'rank', \n",
    "    'Title' : 'title',\n",
    "    'Month' : 'month',\n",
    "    'Year' : 'year',\n",
    "    'Hours Watched' : 'hours_watched',\n",
    "    'Hours Streamed' : 'hours_streamed',\n",
    "    'Peak Viewers' : 'peak_viewers',\n",
    "    'Peak Channels' : 'peak_channels',\n",
    "    'Streamers' : 'streamers',\n",
    "    'Average Viewers' : 'average_viewers',\n",
    "    'Average Channels' : 'average_channels',\n",
    "    'Average Viewer Ratio' : 'average_viewer_ratio'\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>rank</th>\n",
       "      <th>title</th>\n",
       "      <th>month</th>\n",
       "      <th>year</th>\n",
       "      <th>hours_watched</th>\n",
       "      <th>hours_streamed</th>\n",
       "      <th>peak_viewers</th>\n",
       "      <th>peak_channels</th>\n",
       "      <th>streamers</th>\n",
       "      <th>average_viewers</th>\n",
       "      <th>average_channels</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4598</th>\n",
       "      <td>199</td>\n",
       "      <td>.hack//G.U. Last Recode</td>\n",
       "      <td>11</td>\n",
       "      <td>2017</td>\n",
       "      <td>145350</td>\n",
       "      <td>35258</td>\n",
       "      <td>1222</td>\n",
       "      <td>258</td>\n",
       "      <td>3174</td>\n",
       "      <td>202</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15558</th>\n",
       "      <td>159</td>\n",
       "      <td>20 Minutes Till Dawn</td>\n",
       "      <td>6</td>\n",
       "      <td>2022</td>\n",
       "      <td>911356</td>\n",
       "      <td>12253</td>\n",
       "      <td>29743</td>\n",
       "      <td>80</td>\n",
       "      <td>3616</td>\n",
       "      <td>1267</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6508</th>\n",
       "      <td>109</td>\n",
       "      <td>60 Parsecs!</td>\n",
       "      <td>9</td>\n",
       "      <td>2018</td>\n",
       "      <td>529688</td>\n",
       "      <td>1867</td>\n",
       "      <td>31960</td>\n",
       "      <td>27</td>\n",
       "      <td>606</td>\n",
       "      <td>736</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1325</th>\n",
       "      <td>126</td>\n",
       "      <td>60 Seconds!</td>\n",
       "      <td>7</td>\n",
       "      <td>2016</td>\n",
       "      <td>268754</td>\n",
       "      <td>597</td>\n",
       "      <td>32505</td>\n",
       "      <td>10</td>\n",
       "      <td>275</td>\n",
       "      <td>361</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1453</th>\n",
       "      <td>54</td>\n",
       "      <td>60 Seconds!</td>\n",
       "      <td>8</td>\n",
       "      <td>2016</td>\n",
       "      <td>772786</td>\n",
       "      <td>2065</td>\n",
       "      <td>56904</td>\n",
       "      <td>30</td>\n",
       "      <td>628</td>\n",
       "      <td>1040</td>\n",
       "      <td>2</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4541</th>\n",
       "      <td>142</td>\n",
       "      <td>theHunter: Call of the Wild</td>\n",
       "      <td>11</td>\n",
       "      <td>2017</td>\n",
       "      <td>275393</td>\n",
       "      <td>9241</td>\n",
       "      <td>13279</td>\n",
       "      <td>41</td>\n",
       "      <td>1312</td>\n",
       "      <td>383</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4762</th>\n",
       "      <td>163</td>\n",
       "      <td>theHunter: Call of the Wild</td>\n",
       "      <td>12</td>\n",
       "      <td>2017</td>\n",
       "      <td>224528</td>\n",
       "      <td>11504</td>\n",
       "      <td>5978</td>\n",
       "      <td>40</td>\n",
       "      <td>1474</td>\n",
       "      <td>302</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6162</th>\n",
       "      <td>163</td>\n",
       "      <td>theHunter: Call of the Wild</td>\n",
       "      <td>7</td>\n",
       "      <td>2018</td>\n",
       "      <td>342667</td>\n",
       "      <td>8633</td>\n",
       "      <td>27600</td>\n",
       "      <td>28</td>\n",
       "      <td>1851</td>\n",
       "      <td>461</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8395</th>\n",
       "      <td>196</td>\n",
       "      <td>theHunter: Call of the Wild</td>\n",
       "      <td>6</td>\n",
       "      <td>2019</td>\n",
       "      <td>312124</td>\n",
       "      <td>16140</td>\n",
       "      <td>13252</td>\n",
       "      <td>69</td>\n",
       "      <td>2602</td>\n",
       "      <td>434</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7561</th>\n",
       "      <td>162</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>2019</td>\n",
       "      <td>317951</td>\n",
       "      <td>77004</td>\n",
       "      <td>30919</td>\n",
       "      <td>5804</td>\n",
       "      <td>21758</td>\n",
       "      <td>473</td>\n",
       "      <td>114</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>18600 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       rank                        title  month  year  hours_watched  \\\n",
       "4598    199      .hack//G.U. Last Recode     11  2017         145350   \n",
       "15558   159         20 Minutes Till Dawn      6  2022         911356   \n",
       "6508    109                  60 Parsecs!      9  2018         529688   \n",
       "1325    126                  60 Seconds!      7  2016         268754   \n",
       "1453     54                  60 Seconds!      8  2016         772786   \n",
       "...     ...                          ...    ...   ...            ...   \n",
       "4541    142  theHunter: Call of the Wild     11  2017         275393   \n",
       "4762    163  theHunter: Call of the Wild     12  2017         224528   \n",
       "6162    163  theHunter: Call of the Wild      7  2018         342667   \n",
       "8395    196  theHunter: Call of the Wild      6  2019         312124   \n",
       "7561    162                          NaN      2  2019         317951   \n",
       "\n",
       "       hours_streamed  peak_viewers  peak_channels  streamers  \\\n",
       "4598            35258          1222            258       3174   \n",
       "15558           12253         29743             80       3616   \n",
       "6508             1867         31960             27        606   \n",
       "1325              597         32505             10        275   \n",
       "1453             2065         56904             30        628   \n",
       "...               ...           ...            ...        ...   \n",
       "4541             9241         13279             41       1312   \n",
       "4762            11504          5978             40       1474   \n",
       "6162             8633         27600             28       1851   \n",
       "8395            16140         13252             69       2602   \n",
       "7561            77004         30919           5804      21758   \n",
       "\n",
       "       average_viewers  average_channels  \n",
       "4598               202                49  \n",
       "15558             1267                17  \n",
       "6508               736                 2  \n",
       "1325               361                 0  \n",
       "1453              1040                 2  \n",
       "...                ...               ...  \n",
       "4541               383                12  \n",
       "4762               302                15  \n",
       "6162               461                11  \n",
       "8395               434                22  \n",
       "7561               473               114  \n",
       "\n",
       "[18600 rows x 11 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "twitch_monthly = renamed_cols_twitch.drop(columns='average_viewer_ratio')\n",
    "twitch_monthly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>app_id</th>\n",
       "      <th>title</th>\n",
       "      <th>rank</th>\n",
       "      <th>month</th>\n",
       "      <th>year</th>\n",
       "      <th>hours_watched</th>\n",
       "      <th>hours_streamed</th>\n",
       "      <th>peak_viewers</th>\n",
       "      <th>peak_channels</th>\n",
       "      <th>streamers</th>\n",
       "      <th>average_viewers</th>\n",
       "      <th>average_channels</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>525480</td>\n",
       "      <td>.hack//G.U. Last Recode</td>\n",
       "      <td>199</td>\n",
       "      <td>11</td>\n",
       "      <td>2017</td>\n",
       "      <td>145350</td>\n",
       "      <td>35258</td>\n",
       "      <td>1222</td>\n",
       "      <td>258</td>\n",
       "      <td>3174</td>\n",
       "      <td>202</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1966900</td>\n",
       "      <td>20 Minutes Till Dawn</td>\n",
       "      <td>159</td>\n",
       "      <td>6</td>\n",
       "      <td>2022</td>\n",
       "      <td>911356</td>\n",
       "      <td>12253</td>\n",
       "      <td>29743</td>\n",
       "      <td>80</td>\n",
       "      <td>3616</td>\n",
       "      <td>1267</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>646270</td>\n",
       "      <td>60 Parsecs!</td>\n",
       "      <td>109</td>\n",
       "      <td>9</td>\n",
       "      <td>2018</td>\n",
       "      <td>529688</td>\n",
       "      <td>1867</td>\n",
       "      <td>31960</td>\n",
       "      <td>27</td>\n",
       "      <td>606</td>\n",
       "      <td>736</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>368360</td>\n",
       "      <td>60 Seconds!</td>\n",
       "      <td>126</td>\n",
       "      <td>7</td>\n",
       "      <td>2016</td>\n",
       "      <td>268754</td>\n",
       "      <td>597</td>\n",
       "      <td>32505</td>\n",
       "      <td>10</td>\n",
       "      <td>275</td>\n",
       "      <td>361</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>368360</td>\n",
       "      <td>60 Seconds!</td>\n",
       "      <td>54</td>\n",
       "      <td>8</td>\n",
       "      <td>2016</td>\n",
       "      <td>772786</td>\n",
       "      <td>2065</td>\n",
       "      <td>56904</td>\n",
       "      <td>30</td>\n",
       "      <td>628</td>\n",
       "      <td>1040</td>\n",
       "      <td>2</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>5492</th>\n",
       "      <td>266410</td>\n",
       "      <td>iRacing</td>\n",
       "      <td>131</td>\n",
       "      <td>6</td>\n",
       "      <td>2023</td>\n",
       "      <td>939311</td>\n",
       "      <td>62917</td>\n",
       "      <td>7138</td>\n",
       "      <td>601</td>\n",
       "      <td>5980</td>\n",
       "      <td>1306</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5493</th>\n",
       "      <td>266410</td>\n",
       "      <td>iRacing</td>\n",
       "      <td>125</td>\n",
       "      <td>7</td>\n",
       "      <td>2023</td>\n",
       "      <td>1201528</td>\n",
       "      <td>76612</td>\n",
       "      <td>10074</td>\n",
       "      <td>1049</td>\n",
       "      <td>5926</td>\n",
       "      <td>1617</td>\n",
       "      <td>103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5494</th>\n",
       "      <td>266410</td>\n",
       "      <td>iRacing</td>\n",
       "      <td>119</td>\n",
       "      <td>8</td>\n",
       "      <td>2023</td>\n",
       "      <td>1297921</td>\n",
       "      <td>67838</td>\n",
       "      <td>9264</td>\n",
       "      <td>498</td>\n",
       "      <td>5781</td>\n",
       "      <td>1746</td>\n",
       "      <td>91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5495</th>\n",
       "      <td>266410</td>\n",
       "      <td>iRacing</td>\n",
       "      <td>123</td>\n",
       "      <td>9</td>\n",
       "      <td>2023</td>\n",
       "      <td>1133090</td>\n",
       "      <td>57084</td>\n",
       "      <td>9183</td>\n",
       "      <td>284</td>\n",
       "      <td>5564</td>\n",
       "      <td>1575</td>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5496</th>\n",
       "      <td>365960</td>\n",
       "      <td>rFactor 2</td>\n",
       "      <td>161</td>\n",
       "      <td>6</td>\n",
       "      <td>2020</td>\n",
       "      <td>645424</td>\n",
       "      <td>3489</td>\n",
       "      <td>62961</td>\n",
       "      <td>54</td>\n",
       "      <td>490</td>\n",
       "      <td>897</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5497 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       app_id                    title  rank  month  year  hours_watched  \\\n",
       "0      525480  .hack//G.U. Last Recode   199     11  2017         145350   \n",
       "1     1966900     20 Minutes Till Dawn   159      6  2022         911356   \n",
       "2      646270              60 Parsecs!   109      9  2018         529688   \n",
       "3      368360              60 Seconds!   126      7  2016         268754   \n",
       "4      368360              60 Seconds!    54      8  2016         772786   \n",
       "...       ...                      ...   ...    ...   ...            ...   \n",
       "5492   266410                  iRacing   131      6  2023         939311   \n",
       "5493   266410                  iRacing   125      7  2023        1201528   \n",
       "5494   266410                  iRacing   119      8  2023        1297921   \n",
       "5495   266410                  iRacing   123      9  2023        1133090   \n",
       "5496   365960                rFactor 2   161      6  2020         645424   \n",
       "\n",
       "      hours_streamed  peak_viewers  peak_channels  streamers  average_viewers  \\\n",
       "0              35258          1222            258       3174              202   \n",
       "1              12253         29743             80       3616             1267   \n",
       "2               1867         31960             27        606              736   \n",
       "3                597         32505             10        275              361   \n",
       "4               2065         56904             30        628             1040   \n",
       "...              ...           ...            ...        ...              ...   \n",
       "5492           62917          7138            601       5980             1306   \n",
       "5493           76612         10074           1049       5926             1617   \n",
       "5494           67838          9264            498       5781             1746   \n",
       "5495           57084          9183            284       5564             1575   \n",
       "5496            3489         62961             54        490              897   \n",
       "\n",
       "      average_channels  \n",
       "0                   49  \n",
       "1                   17  \n",
       "2                    2  \n",
       "3                    0  \n",
       "4                    2  \n",
       "...                ...  \n",
       "5492                87  \n",
       "5493               103  \n",
       "5494                91  \n",
       "5495                79  \n",
       "5496                 4  \n",
       "\n",
       "[5497 rows x 12 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "twitch_w_app_id = pd.merge(df_of_valid_id_title, twitch_monthly, on='title', how='inner')\n",
    "twitch_w_app_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "twitch_w_app_id.to_csv('Resources/app_id_twitch_monthly.csv', index= False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "dev",
   "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.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}