data_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np, pandas as pd\n",
    "from datetime import datetime\n",
    "from langdetect import detect"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "At this moment we have 3 .csv files for EACH platform:\n",
    "<br/>1- Games information\n",
    "<br/>2- Critics reviews\n",
    "<br/>3- Users reviews\n",
    "<br/>Total: 9 files\n",
    "<br/><br/>The objective is to have just 2 cleaned .csv files: \n",
    "<br/>1- Games information of all platforms\n",
    "<br/>2- Reviews (critics + users) of all platforms"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Games info"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### PS4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We first need to load the .csv file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/ps4_games.csv', lineterminator='\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take a look at first 5 rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>title</th>\n",
       "      <td>10 Second Ninja X</td>\n",
       "      <td>1000 Top Rated</td>\n",
       "      <td>1001 Spikes</td>\n",
       "      <td>100ft Robot Golf</td>\n",
       "      <td>101 Ways to Die</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>platform</th>\n",
       "      <td>PlayStation 4</td>\n",
       "      <td>PlayStation 4</td>\n",
       "      <td>PlayStation 4</td>\n",
       "      <td>PlayStation 4</td>\n",
       "      <td>PlayStation 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>developer</th>\n",
       "      <td>GameDesignDan</td>\n",
       "      <td>Top Rated</td>\n",
       "      <td>Nicalis</td>\n",
       "      <td>No Goblin</td>\n",
       "      <td>4 Door Lemon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>genre</th>\n",
       "      <td>['Action', 'Platformer', '2D']</td>\n",
       "      <td>['Miscellaneous', 'Party / Minigame']</td>\n",
       "      <td>['Action', 'Platformer', 'Platformer', '2D', '...</td>\n",
       "      <td>['Sports', 'Individual', 'Golf', 'Arcade']</td>\n",
       "      <td>['Action', 'Platformer', '2D']</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rating</th>\n",
       "      <td>T</td>\n",
       "      <td>NaN</td>\n",
       "      <td>T</td>\n",
       "      <td>E10+</td>\n",
       "      <td>T</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>release_date</th>\n",
       "      <td>Jul 19, 2016</td>\n",
       "      <td>Jun 27, 2017</td>\n",
       "      <td>Jun  3, 2014</td>\n",
       "      <td>Sep 27, 2017</td>\n",
       "      <td>Mar 22, 2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>summary</th>\n",
       "      <td>If you want a cheesy story to go along with a ...</td>\n",
       "      <td>This game is a puzzle game, but it's more like...</td>\n",
       "      <td>If you miss the crippling difficulty of the NE...</td>\n",
       "      <td>100ft Robot Golf is a real-time golf game thro...</td>\n",
       "      <td>A game that makes me laugh and want to play mo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>meta_score</th>\n",
       "      <td>76</td>\n",
       "      <td>0</td>\n",
       "      <td>78</td>\n",
       "      <td>56</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>meta_overview</th>\n",
       "      <td>Generally favorable reviews</td>\n",
       "      <td>No score yet</td>\n",
       "      <td>Generally favorable reviews</td>\n",
       "      <td>Mixed or average reviews</td>\n",
       "      <td>Mixed or average reviews</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>meta_pos</th>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>meta_mixed</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>meta_neg</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>user_score</th>\n",
       "      <td>5.5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6.6</td>\n",
       "      <td>5.9</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>user_overview</th>\n",
       "      <td>Mixed or average reviews</td>\n",
       "      <td>Generally unfavorable reviews</td>\n",
       "      <td>Mixed or average reviews</td>\n",
       "      <td>Mixed or average reviews</td>\n",
       "      <td>Mixed or average reviews</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>user_pos</th>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>user_mixed</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>user_neg</th>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                               0  \\\n",
       "title                                          10 Second Ninja X   \n",
       "platform                                           PlayStation 4   \n",
       "developer                                          GameDesignDan   \n",
       "genre                             ['Action', 'Platformer', '2D']   \n",
       "rating                                                         T   \n",
       "release_date                                        Jul 19, 2016   \n",
       "summary        If you want a cheesy story to go along with a ...   \n",
       "meta_score                                                    76   \n",
       "meta_overview                        Generally favorable reviews   \n",
       "meta_pos                                                      11   \n",
       "meta_mixed                                                     5   \n",
       "meta_neg                                                       0   \n",
       "user_score                                                   5.5   \n",
       "user_overview                           Mixed or average reviews   \n",
       "user_pos                                                       8   \n",
       "user_mixed                                                     3   \n",
       "user_neg                                                       6   \n",
       "\n",
       "                                                               1  \\\n",
       "title                                             1000 Top Rated   \n",
       "platform                                           PlayStation 4   \n",
       "developer                                              Top Rated   \n",
       "genre                      ['Miscellaneous', 'Party / Minigame']   \n",
       "rating                                                       NaN   \n",
       "release_date                                        Jun 27, 2017   \n",
       "summary        This game is a puzzle game, but it's more like...   \n",
       "meta_score                                                     0   \n",
       "meta_overview                                       No score yet   \n",
       "meta_pos                                                       0   \n",
       "meta_mixed                                                     0   \n",
       "meta_neg                                                       0   \n",
       "user_score                                                   2.0   \n",
       "user_overview                      Generally unfavorable reviews   \n",
       "user_pos                                                       0   \n",
       "user_mixed                                                     1   \n",
       "user_neg                                                       3   \n",
       "\n",
       "                                                               2  \\\n",
       "title                                                1001 Spikes   \n",
       "platform                                           PlayStation 4   \n",
       "developer                                                Nicalis   \n",
       "genre          ['Action', 'Platformer', 'Platformer', '2D', '...   \n",
       "rating                                                         T   \n",
       "release_date                                        Jun  3, 2014   \n",
       "summary        If you miss the crippling difficulty of the NE...   \n",
       "meta_score                                                    78   \n",
       "meta_overview                        Generally favorable reviews   \n",
       "meta_pos                                                       6   \n",
       "meta_mixed                                                     2   \n",
       "meta_neg                                                       0   \n",
       "user_score                                                   6.6   \n",
       "user_overview                           Mixed or average reviews   \n",
       "user_pos                                                      21   \n",
       "user_mixed                                                     7   \n",
       "user_neg                                                       8   \n",
       "\n",
       "                                                               3  \\\n",
       "title                                           100ft Robot Golf   \n",
       "platform                                           PlayStation 4   \n",
       "developer                                              No Goblin   \n",
       "genre                 ['Sports', 'Individual', 'Golf', 'Arcade']   \n",
       "rating                                                      E10+   \n",
       "release_date                                        Sep 27, 2017   \n",
       "summary        100ft Robot Golf is a real-time golf game thro...   \n",
       "meta_score                                                    56   \n",
       "meta_overview                           Mixed or average reviews   \n",
       "meta_pos                                                       3   \n",
       "meta_mixed                                                    12   \n",
       "meta_neg                                                       3   \n",
       "user_score                                                   5.9   \n",
       "user_overview                           Mixed or average reviews   \n",
       "user_pos                                                       9   \n",
       "user_mixed                                                     2   \n",
       "user_neg                                                       6   \n",
       "\n",
       "                                                               4  \n",
       "title                                            101 Ways to Die  \n",
       "platform                                           PlayStation 4  \n",
       "developer                                           4 Door Lemon  \n",
       "genre                             ['Action', 'Platformer', '2D']  \n",
       "rating                                                         T  \n",
       "release_date                                        Mar 22, 2016  \n",
       "summary        A game that makes me laugh and want to play mo...  \n",
       "meta_score                                                    60  \n",
       "meta_overview                           Mixed or average reviews  \n",
       "meta_pos                                                       1  \n",
       "meta_mixed                                                     5  \n",
       "meta_neg                                                       0  \n",
       "user_score                                                   5.0  \n",
       "user_overview                           Mixed or average reviews  \n",
       "user_pos                                                       2  \n",
       "user_mixed                                                     0  \n",
       "user_neg                                                       2  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head().transpose()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Are there missing values ? what about formats?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 2704 entries, 0 to 2703\n",
      "Data columns (total 17 columns):\n",
      "title            2704 non-null object\n",
      "platform         2704 non-null object\n",
      "developer        2695 non-null object\n",
      "genre            2704 non-null object\n",
      "rating           2428 non-null object\n",
      "release_date     2704 non-null object\n",
      "summary          2396 non-null object\n",
      "meta_score       2704 non-null int64\n",
      "meta_overview    2703 non-null object\n",
      "meta_pos         2703 non-null float64\n",
      "meta_mixed       2703 non-null float64\n",
      "meta_neg         2703 non-null float64\n",
      "user_score       2703 non-null object\n",
      "user_overview    2703 non-null object\n",
      "user_pos         2703 non-null object\n",
      "user_mixed       2703 non-null object\n",
      "user_neg         2703 non-null object\n",
      "dtypes: float64(3), int64(1), object(13)\n",
      "memory usage: 380.2+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The table has 2704 entries. There are rows with missing values."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The columns user_score, user_pos, user_mixed and user_neg must have numbers but their format is \"object\". They need to be transformed. Null values are replaced with 0."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def obj_to_numeric(df, cols):\n",
    "    for col in cols:\n",
    "        df[col] = pd.to_numeric(df[col], errors='coerce')\n",
    "        df.loc[df[col].isnull(), col] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "obj_to_numeric(df, ['user_score', 'user_pos', 'user_mixed', 'user_neg'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fill meta_overview and user_overview null values with their corresponding categories when there's no score."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df.loc[df['meta_overview'].isnull(), 'meta_overview'] = 'No score yet'\n",
    "df.loc[df['user_overview'].isnull(), 'user_overview'] = 'No user score yet'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 2704 entries, 0 to 2703\n",
      "Data columns (total 17 columns):\n",
      "title            2704 non-null object\n",
      "platform         2704 non-null object\n",
      "developer        2695 non-null object\n",
      "genre            2704 non-null object\n",
      "rating           2428 non-null object\n",
      "release_date     2704 non-null object\n",
      "summary          2396 non-null object\n",
      "meta_score       2704 non-null int64\n",
      "meta_overview    2704 non-null object\n",
      "meta_pos         2704 non-null float64\n",
      "meta_mixed       2704 non-null float64\n",
      "meta_neg         2704 non-null float64\n",
      "user_score       2704 non-null float64\n",
      "user_overview    2704 non-null object\n",
      "user_pos         2704 non-null float64\n",
      "user_mixed       2704 non-null float64\n",
      "user_neg         2704 non-null float64\n",
      "dtypes: float64(7), int64(1), object(9)\n",
      "memory usage: 380.2+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's see meta_score and user_score min and max values. We are looking for outliers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>meta_score</th>\n",
       "      <th>user_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>97.0</td>\n",
       "      <td>9.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     meta_score  user_score\n",
       "min         0.0         0.0\n",
       "max        97.0         9.6"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe().loc[['min', 'max'], ['meta_score', 'user_score']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "No outliers but the columns are in different range. They should be in the same range in order to be able to compare them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df['n_user_score'] = df['user_score'] * 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "release_date column contain strings, for instance \"Jul 19, 2016\". We must transform those strings into datetime."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df['release_date'] = df['release_date'].apply(lambda x: pd.to_datetime(datetime.strptime(x, '%b %d, %Y')))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Save cleaned dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df.to_csv('ps4_games_cleaned.csv', index=False, encoding = 'utf-8')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "#### Xbox One"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Apply the same steps for Xbox One and Switch game information files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('xboxone_games.csv', lineterminator='\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1976 entries, 0 to 1975\n",
      "Data columns (total 17 columns):\n",
      "title            1976 non-null object\n",
      "platform         1976 non-null object\n",
      "developer        1967 non-null object\n",
      "genre            1976 non-null object\n",
      "rating           1636 non-null object\n",
      "release_date     1976 non-null object\n",
      "summary          1596 non-null object\n",
      "meta_score       1976 non-null int64\n",
      "meta_overview    1975 non-null object\n",
      "meta_pos         1975 non-null float64\n",
      "meta_mixed       1975 non-null float64\n",
      "meta_neg         1975 non-null float64\n",
      "user_score       1975 non-null object\n",
      "user_overview    1975 non-null object\n",
      "user_pos         1975 non-null object\n",
      "user_mixed       1975 non-null float64\n",
      "user_neg         1975 non-null float64\n",
      "dtypes: float64(5), int64(1), object(11)\n",
      "memory usage: 277.9+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "obj_to_numeric(df, ['user_score', 'user_pos', 'user_mixed', 'user_neg'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df.loc[df['meta_overview'].isnull(), 'meta_overview'] = 'No score yet'\n",
    "df.loc[df['user_overview'].isnull(), 'user_overview'] = 'No user score yet'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1976 entries, 0 to 1975\n",
      "Data columns (total 17 columns):\n",
      "title            1976 non-null object\n",
      "platform         1976 non-null object\n",
      "developer        1967 non-null object\n",
      "genre            1976 non-null object\n",
      "rating           1636 non-null object\n",
      "release_date     1976 non-null object\n",
      "summary          1596 non-null object\n",
      "meta_score       1976 non-null int64\n",
      "meta_overview    1976 non-null object\n",
      "meta_pos         1976 non-null float64\n",
      "meta_mixed       1976 non-null float64\n",
      "meta_neg         1976 non-null float64\n",
      "user_score       1976 non-null float64\n",
      "user_overview    1976 non-null object\n",
      "user_pos         1976 non-null float64\n",
      "user_mixed       1976 non-null float64\n",
      "user_neg         1976 non-null float64\n",
      "dtypes: float64(7), int64(1), object(9)\n",
      "memory usage: 277.9+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>meta_score</th>\n",
       "      <th>user_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>97.0</td>\n",
       "      <td>9.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     meta_score  user_score\n",
       "min         0.0         0.0\n",
       "max        97.0         9.5"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe().loc[['min', 'max'], ['meta_score', 'user_score']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df['n_user_score'] = df['user_score'] * 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df['release_date'] = df['release_date'].apply(lambda x: pd.to_datetime(datetime.strptime(x, '%b %d, %Y')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df.to_csv('xboxone_games_cleaned.csv', index=False, encoding = 'utf-8')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "#### Switch"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('switch_games.csv', lineterminator='\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 478 entries, 0 to 477\n",
      "Data columns (total 17 columns):\n",
      "title            478 non-null object\n",
      "platform         478 non-null object\n",
      "developer        477 non-null object\n",
      "genre            478 non-null object\n",
      "rating           440 non-null object\n",
      "release_date     478 non-null object\n",
      "summary          387 non-null object\n",
      "meta_score       478 non-null int64\n",
      "meta_overview    478 non-null object\n",
      "meta_pos         478 non-null int64\n",
      "meta_mixed       478 non-null int64\n",
      "meta_neg         478 non-null int64\n",
      "user_score       478 non-null object\n",
      "user_overview    478 non-null object\n",
      "user_pos         478 non-null object\n",
      "user_mixed       478 non-null int64\n",
      "user_neg         478 non-null object\n",
      "dtypes: int64(5), object(12)\n",
      "memory usage: 67.2+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "obj_to_numeric(df, ['meta_pos', 'meta_mixed', 'meta_neg', 'user_score', 'user_pos', 'user_mixed', 'user_neg'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>meta_score</th>\n",
       "      <th>user_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>97.0</td>\n",
       "      <td>9.3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     meta_score  user_score\n",
       "min         0.0         0.0\n",
       "max        97.0         9.3"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe().loc[['min', 'max'], ['meta_score', 'user_score']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df['n_user_score'] = df['user_score'] * 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df['release_date'] = df['release_date'].apply(lambda x: pd.to_datetime(datetime.strptime(x, '%b %d, %Y')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df.to_csv('switch_games_cleaned.csv', index=False, encoding = 'utf-8')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "#### Merge"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Let's merge the 3 .csv files into a single one called games.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "consoles = ['ps4', 'xboxone', 'switch']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "tables = [pd.read_csv(f'{c}_games_cleaned.csv', lineterminator='\\n') for c in consoles]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2704, 18)\n",
      "(1976, 18)\n",
      "(478, 18)\n"
     ]
    }
   ],
   "source": [
    "for t in tables: print(t.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df = pd.concat(tables)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5158, 18)"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df.to_csv('games.csv', index=False, encoding = 'utf-8')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reviews"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "#### Meta reviews"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Load critics reviews of each platform"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "meta_reviews = [pd.read_csv(f'{c}_meta_reviews.csv', lineterminator='\\n') for c in consoles]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(33754, 6)\n",
      "(13678, 6)\n",
      "(4375, 6)\n"
     ]
    }
   ],
   "source": [
    "for t in meta_reviews: print(t.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>score</th>\n",
       "      <th>text</th>\n",
       "      <th>critic</th>\n",
       "      <th>date</th>\n",
       "      <th>title</th>\n",
       "      <th>platform</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>74.0</td>\n",
       "      <td>It makes a terrible first impression, but #kil...</td>\n",
       "      <td>Gaming Nexus</td>\n",
       "      <td>Nov 18, 2014</td>\n",
       "      <td>#KILLALLZOMBIES</td>\n",
       "      <td>PlayStation 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>65.0</td>\n",
       "      <td>#killallzombies brings some fresh and inventiv...</td>\n",
       "      <td>Playstation Universe</td>\n",
       "      <td>Nov 17, 2014</td>\n",
       "      <td>#KILLALLZOMBIES</td>\n",
       "      <td>PlayStation 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>60.0</td>\n",
       "      <td>A fun main mode of play that can get quite add...</td>\n",
       "      <td>Gamers' Temple</td>\n",
       "      <td>Feb 16, 2015</td>\n",
       "      <td>#KILLALLZOMBIES</td>\n",
       "      <td>PlayStation 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>60.0</td>\n",
       "      <td>Simply put, #killallzombies feels like half of...</td>\n",
       "      <td>DarkStation</td>\n",
       "      <td>Dec  4, 2014</td>\n",
       "      <td>#KILLALLZOMBIES</td>\n",
       "      <td>PlayStation 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>60.0</td>\n",
       "      <td>Combined with the price and lack of cross-buy,...</td>\n",
       "      <td>Worth Playing</td>\n",
       "      <td>Nov 30, 2014</td>\n",
       "      <td>#KILLALLZOMBIES</td>\n",
       "      <td>PlayStation 4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   score                                               text  \\\n",
       "0   74.0  It makes a terrible first impression, but #kil...   \n",
       "1   65.0  #killallzombies brings some fresh and inventiv...   \n",
       "2   60.0  A fun main mode of play that can get quite add...   \n",
       "3   60.0  Simply put, #killallzombies feels like half of...   \n",
       "4   60.0  Combined with the price and lack of cross-buy,...   \n",
       "\n",
       "                 critic          date            title       platform  \n",
       "0          Gaming Nexus  Nov 18, 2014  #KILLALLZOMBIES  PlayStation 4  \n",
       "1  Playstation Universe  Nov 17, 2014  #KILLALLZOMBIES  PlayStation 4  \n",
       "2        Gamers' Temple  Feb 16, 2015  #KILLALLZOMBIES  PlayStation 4  \n",
       "3           DarkStation  Dec  4, 2014  #KILLALLZOMBIES  PlayStation 4  \n",
       "4         Worth Playing  Nov 30, 2014  #KILLALLZOMBIES  PlayStation 4  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "meta_reviews[0].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Same analysis as we did with games info to find missing values and incorrect data formats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 33754 entries, 0 to 33753\n",
      "Data columns (total 6 columns):\n",
      "score       33753 non-null float64\n",
      "text        33754 non-null object\n",
      "critic      33754 non-null object\n",
      "date        33754 non-null object\n",
      "title       33754 non-null object\n",
      "platform    33754 non-null object\n",
      "dtypes: float64(1), object(5)\n",
      "memory usage: 1.5+ MB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 13678 entries, 0 to 13677\n",
      "Data columns (total 6 columns):\n",
      "score       13678 non-null int64\n",
      "text        13678 non-null object\n",
      "critic      13678 non-null object\n",
      "date        13678 non-null object\n",
      "title       13678 non-null object\n",
      "platform    13678 non-null object\n",
      "dtypes: int64(1), object(5)\n",
      "memory usage: 641.2+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4375 entries, 0 to 4374\n",
      "Data columns (total 6 columns):\n",
      "score       4375 non-null int64\n",
      "text        4375 non-null object\n",
      "critic      4375 non-null object\n",
      "date        4375 non-null object\n",
      "title       4375 non-null object\n",
      "platform    4375 non-null object\n",
      "dtypes: int64(1), object(5)\n",
      "memory usage: 205.2+ KB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "for t in meta_reviews: print(t.info())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "In the first dataframe there's a null score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>score</th>\n",
       "      <th>text</th>\n",
       "      <th>critic</th>\n",
       "      <th>date</th>\n",
       "      <th>title</th>\n",
       "      <th>platform</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>26547</th>\n",
       "      <td>NaN</td>\n",
       "      <td>I swear this is a real thing that is on the Pl...</td>\n",
       "      <td>The Jimquisition</td>\n",
       "      <td>Jan 11, 2018</td>\n",
       "      <td>Sword of Fortress the Onomuzim</td>\n",
       "      <td>PlayStation 4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       score                                               text  \\\n",
       "26547    NaN  I swear this is a real thing that is on the Pl...   \n",
       "\n",
       "                 critic          date                           title  \\\n",
       "26547  The Jimquisition  Jan 11, 2018  Sword of Fortress the Onomuzim   \n",
       "\n",
       "            platform  \n",
       "26547  PlayStation 4  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "meta_reviews[0].loc[meta_reviews[0]['score'].isnull()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Let's delete its row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "meta_reviews[0].drop(26547, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df = pd.concat(meta_reviews)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df['date'] = df['date'].apply(lambda x: pd.to_datetime(datetime.strptime(x, '%b %d, %Y')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df.to_csv('meta_reviews.csv', index=False, encoding = 'utf-8')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "#### User reviews"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "user_reviews = [pd.read_csv(f'{c}_user_reviews.csv', lineterminator='\\n') for c in consoles]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(57237, 6)\n",
      "(18840, 6)\n",
      "(4802, 6)\n"
     ]
    }
   ],
   "source": [
    "for t in user_reviews: print(t.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>score</th>\n",
       "      <th>text</th>\n",
       "      <th>critic</th>\n",
       "      <th>date</th>\n",
       "      <th>title</th>\n",
       "      <th>platform</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9</td>\n",
       "      <td>For all (twin-stick) shooter lovers must have!...</td>\n",
       "      <td>413x</td>\n",
       "      <td>Nov  3, 2014</td>\n",
       "      <td>#KILLALLZOMBIES</td>\n",
       "      <td>PlayStation 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8</td>\n",
       "      <td>Simple twin stick shooter that throws enough r...</td>\n",
       "      <td>FriedConsole</td>\n",
       "      <td>Apr 24, 2017</td>\n",
       "      <td>#KILLALLZOMBIES</td>\n",
       "      <td>PlayStation 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10</td>\n",
       "      <td>Recién acabo de completar el volumen 1 y he de...</td>\n",
       "      <td>Evangelion37</td>\n",
       "      <td>Dec 26, 2017</td>\n",
       "      <td>.hack//G.U. Last Recode</td>\n",
       "      <td>PlayStation 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9</td>\n",
       "      <td>The best way to play the ps2 classic in this d...</td>\n",
       "      <td>Belial2Spooku</td>\n",
       "      <td>Nov  3, 2017</td>\n",
       "      <td>.hack//G.U. Last Recode</td>\n",
       "      <td>PlayStation 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9</td>\n",
       "      <td>.Hack//GU Last Recode is a great remaster and ...</td>\n",
       "      <td>Ffrenchie</td>\n",
       "      <td>Nov  5, 2017</td>\n",
       "      <td>.hack//G.U. Last Recode</td>\n",
       "      <td>PlayStation 4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   score                                               text         critic  \\\n",
       "0      9  For all (twin-stick) shooter lovers must have!...           413x   \n",
       "1      8  Simple twin stick shooter that throws enough r...   FriedConsole   \n",
       "2     10  Recién acabo de completar el volumen 1 y he de...   Evangelion37   \n",
       "3      9  The best way to play the ps2 classic in this d...  Belial2Spooku   \n",
       "4      9  .Hack//GU Last Recode is a great remaster and ...      Ffrenchie   \n",
       "\n",
       "           date                    title       platform  \n",
       "0  Nov  3, 2014          #KILLALLZOMBIES  PlayStation 4  \n",
       "1  Apr 24, 2017          #KILLALLZOMBIES  PlayStation 4  \n",
       "2  Dec 26, 2017  .hack//G.U. Last Recode  PlayStation 4  \n",
       "3  Nov  3, 2017  .hack//G.U. Last Recode  PlayStation 4  \n",
       "4  Nov  5, 2017  .hack//G.U. Last Recode  PlayStation 4  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_reviews[0].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 57237 entries, 0 to 57236\n",
      "Data columns (total 6 columns):\n",
      "score       57237 non-null int64\n",
      "text        57236 non-null object\n",
      "critic      57237 non-null object\n",
      "date        57237 non-null object\n",
      "title       57237 non-null object\n",
      "platform    57237 non-null object\n",
      "dtypes: int64(1), object(5)\n",
      "memory usage: 3.1+ MB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 18840 entries, 0 to 18839\n",
      "Data columns (total 6 columns):\n",
      "score       18840 non-null int64\n",
      "text        18840 non-null object\n",
      "critic      18840 non-null object\n",
      "date        18840 non-null object\n",
      "title       18840 non-null object\n",
      "platform    18840 non-null object\n",
      "dtypes: int64(1), object(5)\n",
      "memory usage: 1.0+ MB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 4802 entries, 0 to 4801\n",
      "Data columns (total 6 columns):\n",
      "score       4802 non-null int64\n",
      "text        4802 non-null object\n",
      "critic      4802 non-null object\n",
      "date        4802 non-null object\n",
      "title       4802 non-null object\n",
      "platform    4802 non-null object\n",
      "dtypes: int64(1), object(5)\n",
      "memory usage: 262.6+ KB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "for t in user_reviews: print(t.info())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>score</th>\n",
       "      <th>text</th>\n",
       "      <th>critic</th>\n",
       "      <th>date</th>\n",
       "      <th>title</th>\n",
       "      <th>platform</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15918</th>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Trefinn</td>\n",
       "      <td>Nov  2, 2015</td>\n",
       "      <td>F1 2015</td>\n",
       "      <td>PlayStation 4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       score text   critic          date    title       platform\n",
       "15918      9  NaN  Trefinn  Nov  2, 2015  F1 2015  PlayStation 4"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_reviews[0].loc[user_reviews[0]['text'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "user_reviews[0].drop(15918, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df = pd.concat(user_reviews)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df['date'] = df['date'].apply(lambda x: pd.to_datetime(datetime.strptime(x, '%b %d, %Y')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df.to_csv('user_reviews.csv', index=False, encoding = 'utf-8')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "#### Merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "meta_df = pd.read_csv('meta_reviews.csv', lineterminator='\\n')\n",
    "user_df = pd.read_csv('user_reviews.csv', lineterminator='\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((51806, 6), (80878, 6))"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(meta_df.shape, user_df.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     score\n",
       "min    0.0\n",
       "max  100.0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "meta_df.describe().loc[['min', 'max'], ['score']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     score\n",
       "min    0.0\n",
       "max   10.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_df.describe().loc[['min', 'max'], ['score']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "As in games info, critics and user scores are in different range. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "user_df['score'] = user_df['score'] * 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df = pd.concat([meta_df, user_df]).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Let's read some reviews"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "def print_examples(df, qty=1):\n",
    "    for i in range(qty):\n",
    "        print(df.iloc[i]['text'])\n",
    "        print('\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ".hack//G.U. Last Recode had a lot of love poured into its development and planning because it looks amazing.\n",
      "\n",
      "\n",
      "101 Ways To Die is great fun; each level is superbly crafted and the whole experience is enjoyable. Loading times are almost non-existent and the install size is only notable by the fact that it’s a mere 289mb. How they packed it all in to that, I’ll never understand.\n",
      "\n",
      "\n",
      "2064: Read Only Memories presents an interesting, if not predictable, narrative that is propelled by its strong cast of characters and excellent world-building. It’s refreshing to see a developer put so much care into creating a cast as diverse and interesting as this.\n",
      "\n",
      "\n",
      "It’s so purely fun. It’s a thrilling, charming, and humorous game that celebrates many positive things at once with an almost childlike innocence, all while containing a deep mystery that has you constantly thirsting for more information. After a difficult past year, it’s the type of thing that is sorely needed right now as we tackle this one with newfound resolve and hope. 2064: Read Only Memories, with its contemporary and thought-provoking themes, accessibility through its diverse and rich characters, and beautiful story about what it means to be human, is an uplifting experience that I’ll remember for years to come.\n",
      "\n",
      "\n",
      "2064: Read Only Memories is an excellent cyberpunk adventure for anyone looking for a point-and-click title inspired by games like Snatcher. Don't forget the catchy soundtrack.\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print_examples(df.loc[df['score'] > 85], 5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sadly, 100ft Robot Golf is not the wonderful experience we expected it to be. A fantastic concept with poor execution.\n",
      "\n",
      "\n",
      "2Dark will not make you feel annoyed, it will make you feel sad, and that's definitely worse. [May 2017, p.96]\n",
      "\n",
      "\n",
      "2Dark tries to do too many things at once. Its stealth mechanics, while occasionally satisfying, are frustrating and pedestrian. Similarly, its attempts at horror, while presented well, are undermined by bad writing and repetitive gameplay. A lack of clear signposting and a terrible UI do nothing to help this maddening experience.\n",
      "\n",
      "\n",
      "Visually and thematically, 2Dark succeeds in paying homage to its forebears. Everything else, however, is desperately lacking. Alone in the Dark had twenty years in which to rise and fall but Raynal’s latest stab at survival horror barely gets twenty minutes.\n",
      "\n",
      "\n",
      "7 Days to Die has resulted in an infamous port from the PC game. It still features some good ideas like crafting or the combat system, but the technical aspect is full of bugs and graphics issues.\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print_examples(df.loc[df['score'] < 40], 5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "A lot of reviews contain the game name. This could be a problem by the time we train a sentiment analysis classifier. The algorithm could use that information to classify a review as positive or negative, something that won't be useful for new unseen data (of different games). Let's remove them (and also transform all texts to lower case)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df['text'] = df.apply(lambda x: x.text.lower().replace(f'{(x[\"title\"]).lower()}', ''), 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Let's print the same previous examples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sadly,  is not the wonderful experience we expected it to be. a fantastic concept with poor execution.\n",
      "\n",
      "\n",
      " will not make you feel annoyed, it will make you feel sad, and that's definitely worse. [may 2017, p.96]\n",
      "\n",
      "\n",
      " tries to do too many things at once. its stealth mechanics, while occasionally satisfying, are frustrating and pedestrian. similarly, its attempts at horror, while presented well, are undermined by bad writing and repetitive gameplay. a lack of clear signposting and a terrible ui do nothing to help this maddening experience.\n",
      "\n",
      "\n",
      "visually and thematically,  succeeds in paying homage to its forebears. everything else, however, is desperately lacking. alone in the dark had twenty years in which to rise and fall but raynal’s latest stab at survival horror barely gets twenty minutes.\n",
      "\n",
      "\n",
      " has resulted in an infamous port from the pc game. it still features some good ideas like crafting or the combat system, but the technical aspect is full of bugs and graphics issues.\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print_examples(df.loc[df['score'] < 40], 5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Printing reviews I noticed that some of them are not english. For example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'133小时完结散花，\\r 画面、新手教学、地图指引不好，\\r 音乐、过场演出、地图设计、剧情和设定很棒，\\r 掌机模式降到300p左右分辨率依然跑图跑的很开心就tmd很奇葩，\\r 剩下还有一堆东西要肝，全部打完估计一共200小时了，\\r 今年能玩到p5和xb2真是太好了，\\r 我永远喜欢吼姆拉.jpg'"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.iloc[132654]['text']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'magistral!. para mi el mejor juego del año, hace tiempo no me enganchaba tanto con un vídeo-juego, el sistema de combate es perfecto los mundos son inmensos y con una puesta en escena impresionante. el único punto en contra que le veo es que algunas, no todas, misiones secundarias son un poco aburridas. pero la historia principal está a un muy alto nivel.\\rdefinitivamente una obra maestra!'"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.iloc[132648]['text']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "The langdetect library is useful to find the language of a given text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "def detect_lang(row):\n",
    "    try:\n",
    "        lang = detect(row.text)\n",
    "    except:\n",
    "        lang = \"error\"\n",
    "    return lang"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df['lang'] = df.apply(lambda x: detect_lang(x), 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "en    127699\n",
       "es      2201\n",
       "pt       868\n",
       "ru       596\n",
       "fr       306\n",
       "de       238\n",
       "it       169\n",
       "tr       113\n",
       "pl        86\n",
       "so        47\n",
       "Name: lang, dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['lang'].value_counts()[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "We are only interested in english reviews"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df = df.loc[df['lang'] == 'en']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df.to_csv('reviews.csv', index=False, encoding = 'utf-8')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}