bg_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a3dca86c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "4aad3926",
   "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>sort_index</th>\n",
       "      <th>object_id</th>\n",
       "      <th>game_name</th>\n",
       "      <th>year_published</th>\n",
       "      <th>min_players</th>\n",
       "      <th>max_players</th>\n",
       "      <th>min_playtime</th>\n",
       "      <th>max_playtime</th>\n",
       "      <th>average</th>\n",
       "      <th>baverage</th>\n",
       "      <th>...</th>\n",
       "      <th>category_6</th>\n",
       "      <th>mech_1</th>\n",
       "      <th>mech_2</th>\n",
       "      <th>mech_3</th>\n",
       "      <th>mech_4</th>\n",
       "      <th>mech_5</th>\n",
       "      <th>mech_6</th>\n",
       "      <th>total_votes</th>\n",
       "      <th>users_rated</th>\n",
       "      <th>avg_playtime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>174430</td>\n",
       "      <td>Gloomhaven</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>60</td>\n",
       "      <td>120</td>\n",
       "      <td>8.85292</td>\n",
       "      <td>8.58424</td>\n",
       "      <td>...</td>\n",
       "      <td>na</td>\n",
       "      <td>Campaign / Battle Card Driven</td>\n",
       "      <td>Cooperative Game</td>\n",
       "      <td>Grid Movement</td>\n",
       "      <td>Hand Management</td>\n",
       "      <td>Legacy Game</td>\n",
       "      <td>Modular Board</td>\n",
       "      <td>827</td>\n",
       "      <td>31254</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>161936</td>\n",
       "      <td>Pandemic Legacy  Season 1</td>\n",
       "      <td>2015</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>60</td>\n",
       "      <td>60</td>\n",
       "      <td>8.62499</td>\n",
       "      <td>8.47159</td>\n",
       "      <td>...</td>\n",
       "      <td>na</td>\n",
       "      <td>Action Points</td>\n",
       "      <td>Cooperative Game</td>\n",
       "      <td>Hand Management</td>\n",
       "      <td>Legacy Game</td>\n",
       "      <td>Point to Point Movement</td>\n",
       "      <td>Set Collection</td>\n",
       "      <td>549</td>\n",
       "      <td>34729</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>167791</td>\n",
       "      <td>Terraforming Mars</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>120</td>\n",
       "      <td>120</td>\n",
       "      <td>8.42299</td>\n",
       "      <td>8.26781</td>\n",
       "      <td>...</td>\n",
       "      <td>Territory Building</td>\n",
       "      <td>Card Drafting</td>\n",
       "      <td>End Game Bonuses</td>\n",
       "      <td>Hand Management</td>\n",
       "      <td>Income</td>\n",
       "      <td>Set Collection</td>\n",
       "      <td>Take That</td>\n",
       "      <td>1252</td>\n",
       "      <td>48339</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>182028</td>\n",
       "      <td>Through the Ages  A New Story of Civilization</td>\n",
       "      <td>2015</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>120</td>\n",
       "      <td>120</td>\n",
       "      <td>8.49419</td>\n",
       "      <td>8.23513</td>\n",
       "      <td>...</td>\n",
       "      <td>na</td>\n",
       "      <td>Action Points</td>\n",
       "      <td>Auction/Bidding</td>\n",
       "      <td>Auction: Dutch</td>\n",
       "      <td>Card Drafting</td>\n",
       "      <td>Income</td>\n",
       "      <td>na</td>\n",
       "      <td>436</td>\n",
       "      <td>18269</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>224517</td>\n",
       "      <td>Brass  Birmingham</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>60</td>\n",
       "      <td>120</td>\n",
       "      <td>8.62031</td>\n",
       "      <td>8.20459</td>\n",
       "      <td>...</td>\n",
       "      <td>na</td>\n",
       "      <td>Hand Management</td>\n",
       "      <td>Income</td>\n",
       "      <td>Loans</td>\n",
       "      <td>Market</td>\n",
       "      <td>Network and Route Building</td>\n",
       "      <td>Tech Trees / Tech Tracks</td>\n",
       "      <td>242</td>\n",
       "      <td>10070</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   sort_index  object_id                                      game_name  \\\n",
       "0           1     174430                                     Gloomhaven   \n",
       "1           2     161936                      Pandemic Legacy  Season 1   \n",
       "2           3     167791                              Terraforming Mars   \n",
       "3           4     182028  Through the Ages  A New Story of Civilization   \n",
       "4           5     224517                              Brass  Birmingham   \n",
       "\n",
       "   year_published  min_players  max_players  min_playtime  max_playtime  \\\n",
       "0            2017            1            4            60           120   \n",
       "1            2015            2            4            60            60   \n",
       "2            2016            1            5           120           120   \n",
       "3            2015            2            4           120           120   \n",
       "4            2018            2            4            60           120   \n",
       "\n",
       "   average  baverage  ...           category_6                         mech_1  \\\n",
       "0  8.85292   8.58424  ...                   na  Campaign / Battle Card Driven   \n",
       "1  8.62499   8.47159  ...                   na                  Action Points   \n",
       "2  8.42299   8.26781  ...   Territory Building                  Card Drafting   \n",
       "3  8.49419   8.23513  ...                   na                  Action Points   \n",
       "4  8.62031   8.20459  ...                   na                Hand Management   \n",
       "\n",
       "              mech_2            mech_3            mech_4  \\\n",
       "0   Cooperative Game     Grid Movement   Hand Management   \n",
       "1   Cooperative Game   Hand Management       Legacy Game   \n",
       "2   End Game Bonuses   Hand Management            Income   \n",
       "3    Auction/Bidding    Auction: Dutch     Card Drafting   \n",
       "4             Income             Loans            Market   \n",
       "\n",
       "                        mech_5                     mech_6 total_votes  \\\n",
       "0                  Legacy Game              Modular Board         827   \n",
       "1      Point to Point Movement             Set Collection         549   \n",
       "2               Set Collection                  Take That        1252   \n",
       "3                       Income                         na         436   \n",
       "4   Network and Route Building   Tech Trees / Tech Tracks         242   \n",
       "\n",
       "  users_rated avg_playtime  \n",
       "0       31254         30.0  \n",
       "1       34729          0.0  \n",
       "2       48339          0.0  \n",
       "3       18269          0.0  \n",
       "4       10070         30.0  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read in data, create dataframe and show;  note column count\n",
    "df = pd.read_csv(\"data/bg_fulldata1.csv\")\n",
    "bg_df = pd.DataFrame(df)\n",
    "bg_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1ecf4b32",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    20000.000000\n",
       "mean        68.096450\n",
       "std        466.502106\n",
       "min          0.000000\n",
       "25%         20.000000\n",
       "50%         30.000000\n",
       "75%         60.000000\n",
       "max      60000.000000\n",
       "Name: min_playtime, dtype: float64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bg_df['min_playtime'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "664b35bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "bg_df['min_playtime'].values[bg_df['min_playtime'].values > 525] = 525"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "84be9465",
   "metadata": {},
   "outputs": [],
   "source": [
    "bg_df['min_playtime'].values[bg_df['min_playtime'].values < 10] = 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "2b2b5630",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    20000.000000\n",
       "mean        61.161500\n",
       "std         71.386637\n",
       "min         10.000000\n",
       "25%         20.000000\n",
       "50%         30.000000\n",
       "75%         60.000000\n",
       "max        525.000000\n",
       "Name: min_playtime, dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bg_df['min_playtime'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "579cb697",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     20000.00000\n",
       "mean         94.28945\n",
       "std        1005.75196\n",
       "min           0.00000\n",
       "25%          30.00000\n",
       "50%          45.00000\n",
       "75%          90.00000\n",
       "max      120000.00000\n",
       "Name: max_playtime, dtype: float64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bg_df[\"max_playtime\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "4d8ad000",
   "metadata": {},
   "outputs": [],
   "source": [
    "bg_df['max_playtime'].values[bg_df['max_playtime'].values > 1100] = 1100\n",
    "bg_df['max_playtime'].values[bg_df['max_playtime'].values < 10] = 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "9496235e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    20000.000000\n",
       "mean        75.841750\n",
       "std        108.693212\n",
       "min         10.000000\n",
       "25%         30.000000\n",
       "50%         45.000000\n",
       "75%         90.000000\n",
       "max       1100.000000\n",
       "Name: max_playtime, dtype: float64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bg_df[\"max_playtime\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "d2f35618",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    20000.00000\n",
       "mean         5.59215\n",
       "std         15.04921\n",
       "min          0.00000\n",
       "25%          4.00000\n",
       "50%          4.00000\n",
       "75%          6.00000\n",
       "max        999.00000\n",
       "Name: max_players, dtype: float64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bg_df[\"max_players\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "1b69c01a",
   "metadata": {},
   "outputs": [],
   "source": [
    "bg_df['max_players'].values[bg_df['max_players'].values > 40] = 40\n",
    "bg_df['max_players'].values[bg_df['max_players'].values < 2] = 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "810cc908",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    20000.00000\n",
       "mean         5.04920\n",
       "std          4.16403\n",
       "min          2.00000\n",
       "25%          4.00000\n",
       "50%          4.00000\n",
       "75%          6.00000\n",
       "max         40.00000\n",
       "Name: max_players, dtype: float64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bg_df[\"max_players\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "a32deb2f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    20000.000000\n",
       "mean         2.055250\n",
       "std          0.745537\n",
       "min          0.000000\n",
       "25%          2.000000\n",
       "50%          2.000000\n",
       "75%          2.000000\n",
       "max         10.000000\n",
       "Name: min_players, dtype: float64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bg_df[\"min_players\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "d781d05f",
   "metadata": {},
   "outputs": [],
   "source": [
    "bg_df['max_players'].values[bg_df['max_players'].values < 1] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "da1ba373",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    20000.000000\n",
       "mean         2.055250\n",
       "std          0.745537\n",
       "min          0.000000\n",
       "25%          2.000000\n",
       "50%          2.000000\n",
       "75%          2.000000\n",
       "max         10.000000\n",
       "Name: min_players, dtype: float64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bg_df[\"min_players\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "6f136719",
   "metadata": {},
   "outputs": [],
   "source": [
    "bg_df.to_csv('data/bg_clean30.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd69910f",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "mlenv",
   "language": "python",
   "name": "mlenv"
  },
  "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.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}