ETL_user_reviews.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ETL australian_user_reviews.json "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importando Librerías"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import re\n",
    "import ast\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import pyarrow as pa\n",
    "import pyarrow.parquet as pq"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extracción  y lectura del dataset en formato json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_url</th>\n",
       "      <th>reviews</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted November 5, 2...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>js41637</td>\n",
       "      <td>http://steamcommunity.com/id/js41637</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted June 24, 2014...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>http://steamcommunity.com/id/evcentric</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted February 3.',...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>doctr</td>\n",
       "      <td>http://steamcommunity.com/id/doctr</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted October 14, 2...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>maplemage</td>\n",
       "      <td>http://steamcommunity.com/id/maplemage</td>\n",
       "      <td>[{'funny': '3 people found this review funny',...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25794</th>\n",
       "      <td>76561198306599751</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198306...</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted May 31.', 'la...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25795</th>\n",
       "      <td>Ghoustik</td>\n",
       "      <td>http://steamcommunity.com/id/Ghoustik</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted June 17.', 'l...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25796</th>\n",
       "      <td>76561198310819422</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198310...</td>\n",
       "      <td>[{'funny': '1 person found this review funny',...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25797</th>\n",
       "      <td>76561198312638244</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198312...</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted July 21.', 'l...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25798</th>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>http://steamcommunity.com/id/LydiaMorley</td>\n",
       "      <td>[{'funny': '1 person found this review funny',...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>25799 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 user_id                                           user_url   \n",
       "0      76561197970982479  http://steamcommunity.com/profiles/76561197970...  \\\n",
       "1                js41637               http://steamcommunity.com/id/js41637   \n",
       "2              evcentric             http://steamcommunity.com/id/evcentric   \n",
       "3                  doctr                 http://steamcommunity.com/id/doctr   \n",
       "4              maplemage             http://steamcommunity.com/id/maplemage   \n",
       "...                  ...                                                ...   \n",
       "25794  76561198306599751  http://steamcommunity.com/profiles/76561198306...   \n",
       "25795           Ghoustik              http://steamcommunity.com/id/Ghoustik   \n",
       "25796  76561198310819422  http://steamcommunity.com/profiles/76561198310...   \n",
       "25797  76561198312638244  http://steamcommunity.com/profiles/76561198312...   \n",
       "25798        LydiaMorley           http://steamcommunity.com/id/LydiaMorley   \n",
       "\n",
       "                                                 reviews  \n",
       "0      [{'funny': '', 'posted': 'Posted November 5, 2...  \n",
       "1      [{'funny': '', 'posted': 'Posted June 24, 2014...  \n",
       "2      [{'funny': '', 'posted': 'Posted February 3.',...  \n",
       "3      [{'funny': '', 'posted': 'Posted October 14, 2...  \n",
       "4      [{'funny': '3 people found this review funny',...  \n",
       "...                                                  ...  \n",
       "25794  [{'funny': '', 'posted': 'Posted May 31.', 'la...  \n",
       "25795  [{'funny': '', 'posted': 'Posted June 17.', 'l...  \n",
       "25796  [{'funny': '1 person found this review funny',...  \n",
       "25797  [{'funny': '', 'posted': 'Posted July 21.', 'l...  \n",
       "25798  [{'funny': '1 person found this review funny',...  \n",
       "\n",
       "[25799 rows x 3 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#extraccion del json\n",
    "row = [] #lista vacia para ir guardando las filas\n",
    "\n",
    "with open (\"data/australian_user_reviews.json\", 'r', encoding='utf-8') as file: #utilizo with para que el archivo se abra y cierre\n",
    "    for line in file.readlines(): #bucle para ir leyendo filas y luego agregarlas a row\n",
    "        row.append(ast.literal_eval(line)) # interpreta las lineas del json y transforma en objeto de python\n",
    "\n",
    "# genero el dataframe \n",
    "reviews = pd.DataFrame(row)\n",
    "reviews"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Transformación del dataset "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Desanidar la lista con diccionarios en la columna \"reviews\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_url</th>\n",
       "      <th>reviews</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted November 5, 20...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted July 15, 2011....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted April 21, 2011...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>js41637</td>\n",
       "      <td>http://steamcommunity.com/id/js41637</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted June 24, 2014....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>js41637</td>\n",
       "      <td>http://steamcommunity.com/id/js41637</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted September 8, 2...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25797</th>\n",
       "      <td>76561198312638244</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198312...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted July 10.', 'la...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25797</th>\n",
       "      <td>76561198312638244</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198312...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted July 8.', 'las...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25798</th>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>http://steamcommunity.com/id/LydiaMorley</td>\n",
       "      <td>{'funny': '1 person found this review funny', ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25798</th>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>http://steamcommunity.com/id/LydiaMorley</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted July 20.', 'la...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25798</th>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>http://steamcommunity.com/id/LydiaMorley</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted July 2.', 'las...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>59333 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 user_id                                           user_url   \n",
       "0      76561197970982479  http://steamcommunity.com/profiles/76561197970...  \\\n",
       "0      76561197970982479  http://steamcommunity.com/profiles/76561197970...   \n",
       "0      76561197970982479  http://steamcommunity.com/profiles/76561197970...   \n",
       "1                js41637               http://steamcommunity.com/id/js41637   \n",
       "1                js41637               http://steamcommunity.com/id/js41637   \n",
       "...                  ...                                                ...   \n",
       "25797  76561198312638244  http://steamcommunity.com/profiles/76561198312...   \n",
       "25797  76561198312638244  http://steamcommunity.com/profiles/76561198312...   \n",
       "25798        LydiaMorley           http://steamcommunity.com/id/LydiaMorley   \n",
       "25798        LydiaMorley           http://steamcommunity.com/id/LydiaMorley   \n",
       "25798        LydiaMorley           http://steamcommunity.com/id/LydiaMorley   \n",
       "\n",
       "                                                 reviews  \n",
       "0      {'funny': '', 'posted': 'Posted November 5, 20...  \n",
       "0      {'funny': '', 'posted': 'Posted July 15, 2011....  \n",
       "0      {'funny': '', 'posted': 'Posted April 21, 2011...  \n",
       "1      {'funny': '', 'posted': 'Posted June 24, 2014....  \n",
       "1      {'funny': '', 'posted': 'Posted September 8, 2...  \n",
       "...                                                  ...  \n",
       "25797  {'funny': '', 'posted': 'Posted July 10.', 'la...  \n",
       "25797  {'funny': '', 'posted': 'Posted July 8.', 'las...  \n",
       "25798  {'funny': '1 person found this review funny', ...  \n",
       "25798  {'funny': '', 'posted': 'Posted July 20.', 'la...  \n",
       "25798  {'funny': '', 'posted': 'Posted July 2.', 'las...  \n",
       "\n",
       "[59333 rows x 3 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Utilizo la función explode para explotar la columna y desanidar los datos\n",
    "exploded = reviews.explode('reviews')\n",
    "exploded \n",
    "#Conservo el resultado en una variable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>funny</th>\n",
       "      <th>posted</th>\n",
       "      <th>last_edited</th>\n",
       "      <th>item_id</th>\n",
       "      <th>helpful</th>\n",
       "      <th>recommend</th>\n",
       "      <th>review</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td></td>\n",
       "      <td>Posted November 5, 2011.</td>\n",
       "      <td></td>\n",
       "      <td>1250</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Simple yet with great replayability. In my opi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td></td>\n",
       "      <td>Posted July 15, 2011.</td>\n",
       "      <td></td>\n",
       "      <td>22200</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>It's unique and worth a playthrough.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td></td>\n",
       "      <td>Posted April 21, 2011.</td>\n",
       "      <td></td>\n",
       "      <td>43110</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Great atmosphere. The gunplay can be a bit chu...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td></td>\n",
       "      <td>Posted June 24, 2014.</td>\n",
       "      <td></td>\n",
       "      <td>251610</td>\n",
       "      <td>15 of 20 people (75%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>I know what you think when you see this title ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td></td>\n",
       "      <td>Posted September 8, 2013.</td>\n",
       "      <td></td>\n",
       "      <td>227300</td>\n",
       "      <td>0 of 1 people (0%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>For a simple (it's actually not all that simpl...</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59300</th>\n",
       "      <td></td>\n",
       "      <td>Posted July 10.</td>\n",
       "      <td></td>\n",
       "      <td>70</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>a must have classic from steam definitely wort...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59301</th>\n",
       "      <td></td>\n",
       "      <td>Posted July 8.</td>\n",
       "      <td></td>\n",
       "      <td>362890</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>this game is a perfect remake of the original ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59302</th>\n",
       "      <td>1 person found this review funny</td>\n",
       "      <td>Posted July 3.</td>\n",
       "      <td></td>\n",
       "      <td>273110</td>\n",
       "      <td>1 of 2 people (50%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>had so much fun plaing this and collecting res...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59303</th>\n",
       "      <td></td>\n",
       "      <td>Posted July 20.</td>\n",
       "      <td></td>\n",
       "      <td>730</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>:D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59304</th>\n",
       "      <td></td>\n",
       "      <td>Posted July 2.</td>\n",
       "      <td></td>\n",
       "      <td>440</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>so much fun :D</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>59305 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  funny                     posted   \n",
       "0                                         Posted November 5, 2011.  \\\n",
       "1                                            Posted July 15, 2011.   \n",
       "2                                           Posted April 21, 2011.   \n",
       "3                                            Posted June 24, 2014.   \n",
       "4                                        Posted September 8, 2013.   \n",
       "...                                 ...                        ...   \n",
       "59300                                              Posted July 10.   \n",
       "59301                                               Posted July 8.   \n",
       "59302  1 person found this review funny             Posted July 3.   \n",
       "59303                                              Posted July 20.   \n",
       "59304                                               Posted July 2.   \n",
       "\n",
       "      last_edited item_id                                          helpful   \n",
       "0                    1250                                   No ratings yet  \\\n",
       "1                   22200                                   No ratings yet   \n",
       "2                   43110                                   No ratings yet   \n",
       "3                  251610  15 of 20 people (75%) found this review helpful   \n",
       "4                  227300     0 of 1 people (0%) found this review helpful   \n",
       "...           ...     ...                                              ...   \n",
       "59300                  70                                   No ratings yet   \n",
       "59301              362890                                   No ratings yet   \n",
       "59302              273110    1 of 2 people (50%) found this review helpful   \n",
       "59303                 730                                   No ratings yet   \n",
       "59304                 440                                   No ratings yet   \n",
       "\n",
       "       recommend                                             review  \n",
       "0           True  Simple yet with great replayability. In my opi...  \n",
       "1           True               It's unique and worth a playthrough.  \n",
       "2           True  Great atmosphere. The gunplay can be a bit chu...  \n",
       "3           True  I know what you think when you see this title ...  \n",
       "4           True  For a simple (it's actually not all that simpl...  \n",
       "...          ...                                                ...  \n",
       "59300       True  a must have classic from steam definitely wort...  \n",
       "59301       True  this game is a perfect remake of the original ...  \n",
       "59302       True  had so much fun plaing this and collecting res...  \n",
       "59303       True                                                 :D  \n",
       "59304       True                                     so much fun :D  \n",
       "\n",
       "[59305 rows x 7 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#normalizo o aplano los datos \n",
    "normalizado = pd.json_normalize(exploded['reviews'].dropna())\n",
    "normalizado"
   ]
  },
  {
   "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>index</th>\n",
       "      <th>funny</th>\n",
       "      <th>posted</th>\n",
       "      <th>last_edited</th>\n",
       "      <th>item_id</th>\n",
       "      <th>helpful</th>\n",
       "      <th>recommend</th>\n",
       "      <th>review</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td>Posted November 5, 2011.</td>\n",
       "      <td></td>\n",
       "      <td>1250</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Simple yet with great replayability. In my opi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>Posted July 15, 2011.</td>\n",
       "      <td></td>\n",
       "      <td>22200</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>It's unique and worth a playthrough.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>Posted April 21, 2011.</td>\n",
       "      <td></td>\n",
       "      <td>43110</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Great atmosphere. The gunplay can be a bit chu...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td></td>\n",
       "      <td>Posted June 24, 2014.</td>\n",
       "      <td></td>\n",
       "      <td>251610</td>\n",
       "      <td>15 of 20 people (75%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>I know what you think when you see this title ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td></td>\n",
       "      <td>Posted September 8, 2013.</td>\n",
       "      <td></td>\n",
       "      <td>227300</td>\n",
       "      <td>0 of 1 people (0%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>For a simple (it's actually not all that simpl...</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59300</th>\n",
       "      <td>59300</td>\n",
       "      <td></td>\n",
       "      <td>Posted July 10.</td>\n",
       "      <td></td>\n",
       "      <td>70</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>a must have classic from steam definitely wort...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59301</th>\n",
       "      <td>59301</td>\n",
       "      <td></td>\n",
       "      <td>Posted July 8.</td>\n",
       "      <td></td>\n",
       "      <td>362890</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>this game is a perfect remake of the original ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59302</th>\n",
       "      <td>59302</td>\n",
       "      <td>1 person found this review funny</td>\n",
       "      <td>Posted July 3.</td>\n",
       "      <td></td>\n",
       "      <td>273110</td>\n",
       "      <td>1 of 2 people (50%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>had so much fun plaing this and collecting res...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59303</th>\n",
       "      <td>59303</td>\n",
       "      <td></td>\n",
       "      <td>Posted July 20.</td>\n",
       "      <td></td>\n",
       "      <td>730</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>:D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59304</th>\n",
       "      <td>59304</td>\n",
       "      <td></td>\n",
       "      <td>Posted July 2.</td>\n",
       "      <td></td>\n",
       "      <td>440</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>so much fun :D</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>59305 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       index                             funny                     posted   \n",
       "0          0                                     Posted November 5, 2011.  \\\n",
       "1          1                                        Posted July 15, 2011.   \n",
       "2          2                                       Posted April 21, 2011.   \n",
       "3          3                                        Posted June 24, 2014.   \n",
       "4          4                                    Posted September 8, 2013.   \n",
       "...      ...                               ...                        ...   \n",
       "59300  59300                                              Posted July 10.   \n",
       "59301  59301                                               Posted July 8.   \n",
       "59302  59302  1 person found this review funny             Posted July 3.   \n",
       "59303  59303                                              Posted July 20.   \n",
       "59304  59304                                               Posted July 2.   \n",
       "\n",
       "      last_edited item_id                                          helpful   \n",
       "0                    1250                                   No ratings yet  \\\n",
       "1                   22200                                   No ratings yet   \n",
       "2                   43110                                   No ratings yet   \n",
       "3                  251610  15 of 20 people (75%) found this review helpful   \n",
       "4                  227300     0 of 1 people (0%) found this review helpful   \n",
       "...           ...     ...                                              ...   \n",
       "59300                  70                                   No ratings yet   \n",
       "59301              362890                                   No ratings yet   \n",
       "59302              273110    1 of 2 people (50%) found this review helpful   \n",
       "59303                 730                                   No ratings yet   \n",
       "59304                 440                                   No ratings yet   \n",
       "\n",
       "       recommend                                             review  \n",
       "0           True  Simple yet with great replayability. In my opi...  \n",
       "1           True               It's unique and worth a playthrough.  \n",
       "2           True  Great atmosphere. The gunplay can be a bit chu...  \n",
       "3           True  I know what you think when you see this title ...  \n",
       "4           True  For a simple (it's actually not all that simpl...  \n",
       "...          ...                                                ...  \n",
       "59300       True  a must have classic from steam definitely wort...  \n",
       "59301       True  this game is a perfect remake of the original ...  \n",
       "59302       True  had so much fun plaing this and collecting res...  \n",
       "59303       True                                                 :D  \n",
       "59304       True                                     so much fun :D  \n",
       "\n",
       "[59305 rows x 8 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#reseteo los indices para que no se desordenen las filas\n",
    "normalizado.reset_index(inplace=True)\n",
    "normalizado"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>index</th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_url</th>\n",
       "      <th>reviews</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted November 5, 20...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted July 15, 2011....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted April 21, 2011...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>js41637</td>\n",
       "      <td>http://steamcommunity.com/id/js41637</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted June 24, 2014....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>js41637</td>\n",
       "      <td>http://steamcommunity.com/id/js41637</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted September 8, 2...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59328</th>\n",
       "      <td>25797</td>\n",
       "      <td>76561198312638244</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198312...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted July 10.', 'la...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59329</th>\n",
       "      <td>25797</td>\n",
       "      <td>76561198312638244</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198312...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted July 8.', 'las...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59330</th>\n",
       "      <td>25798</td>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>http://steamcommunity.com/id/LydiaMorley</td>\n",
       "      <td>{'funny': '1 person found this review funny', ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59331</th>\n",
       "      <td>25798</td>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>http://steamcommunity.com/id/LydiaMorley</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted July 20.', 'la...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59332</th>\n",
       "      <td>25798</td>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>http://steamcommunity.com/id/LydiaMorley</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted July 2.', 'las...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>59333 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       index            user_id   \n",
       "0          0  76561197970982479  \\\n",
       "1          0  76561197970982479   \n",
       "2          0  76561197970982479   \n",
       "3          1            js41637   \n",
       "4          1            js41637   \n",
       "...      ...                ...   \n",
       "59328  25797  76561198312638244   \n",
       "59329  25797  76561198312638244   \n",
       "59330  25798        LydiaMorley   \n",
       "59331  25798        LydiaMorley   \n",
       "59332  25798        LydiaMorley   \n",
       "\n",
       "                                                user_url   \n",
       "0      http://steamcommunity.com/profiles/76561197970...  \\\n",
       "1      http://steamcommunity.com/profiles/76561197970...   \n",
       "2      http://steamcommunity.com/profiles/76561197970...   \n",
       "3                   http://steamcommunity.com/id/js41637   \n",
       "4                   http://steamcommunity.com/id/js41637   \n",
       "...                                                  ...   \n",
       "59328  http://steamcommunity.com/profiles/76561198312...   \n",
       "59329  http://steamcommunity.com/profiles/76561198312...   \n",
       "59330           http://steamcommunity.com/id/LydiaMorley   \n",
       "59331           http://steamcommunity.com/id/LydiaMorley   \n",
       "59332           http://steamcommunity.com/id/LydiaMorley   \n",
       "\n",
       "                                                 reviews  \n",
       "0      {'funny': '', 'posted': 'Posted November 5, 20...  \n",
       "1      {'funny': '', 'posted': 'Posted July 15, 2011....  \n",
       "2      {'funny': '', 'posted': 'Posted April 21, 2011...  \n",
       "3      {'funny': '', 'posted': 'Posted June 24, 2014....  \n",
       "4      {'funny': '', 'posted': 'Posted September 8, 2...  \n",
       "...                                                  ...  \n",
       "59328  {'funny': '', 'posted': 'Posted July 10.', 'la...  \n",
       "59329  {'funny': '', 'posted': 'Posted July 8.', 'las...  \n",
       "59330  {'funny': '1 person found this review funny', ...  \n",
       "59331  {'funny': '', 'posted': 'Posted July 20.', 'la...  \n",
       "59332  {'funny': '', 'posted': 'Posted July 2.', 'las...  \n",
       "\n",
       "[59333 rows x 4 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exploded.reset_index(inplace=True)\n",
    "exploded"
   ]
  },
  {
   "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>index</th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_url</th>\n",
       "      <th>index</th>\n",
       "      <th>funny</th>\n",
       "      <th>posted</th>\n",
       "      <th>last_edited</th>\n",
       "      <th>item_id</th>\n",
       "      <th>helpful</th>\n",
       "      <th>recommend</th>\n",
       "      <th>review</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td>0.0</td>\n",
       "      <td></td>\n",
       "      <td>Posted November 5, 2011.</td>\n",
       "      <td></td>\n",
       "      <td>1250</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Simple yet with great replayability. In my opi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td>1.0</td>\n",
       "      <td></td>\n",
       "      <td>Posted July 15, 2011.</td>\n",
       "      <td></td>\n",
       "      <td>22200</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>It's unique and worth a playthrough.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td>2.0</td>\n",
       "      <td></td>\n",
       "      <td>Posted April 21, 2011.</td>\n",
       "      <td></td>\n",
       "      <td>43110</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Great atmosphere. The gunplay can be a bit chu...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>js41637</td>\n",
       "      <td>http://steamcommunity.com/id/js41637</td>\n",
       "      <td>3.0</td>\n",
       "      <td></td>\n",
       "      <td>Posted June 24, 2014.</td>\n",
       "      <td></td>\n",
       "      <td>251610</td>\n",
       "      <td>15 of 20 people (75%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>I know what you think when you see this title ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>js41637</td>\n",
       "      <td>http://steamcommunity.com/id/js41637</td>\n",
       "      <td>4.0</td>\n",
       "      <td></td>\n",
       "      <td>Posted September 8, 2013.</td>\n",
       "      <td></td>\n",
       "      <td>227300</td>\n",
       "      <td>0 of 1 people (0%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>For a simple (it's actually not all that simpl...</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>59328</th>\n",
       "      <td>25797</td>\n",
       "      <td>76561198312638244</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198312...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59329</th>\n",
       "      <td>25797</td>\n",
       "      <td>76561198312638244</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198312...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59330</th>\n",
       "      <td>25798</td>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>http://steamcommunity.com/id/LydiaMorley</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59331</th>\n",
       "      <td>25798</td>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>http://steamcommunity.com/id/LydiaMorley</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59332</th>\n",
       "      <td>25798</td>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>http://steamcommunity.com/id/LydiaMorley</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>59333 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       index            user_id   \n",
       "0          0  76561197970982479  \\\n",
       "1          0  76561197970982479   \n",
       "2          0  76561197970982479   \n",
       "3          1            js41637   \n",
       "4          1            js41637   \n",
       "...      ...                ...   \n",
       "59328  25797  76561198312638244   \n",
       "59329  25797  76561198312638244   \n",
       "59330  25798        LydiaMorley   \n",
       "59331  25798        LydiaMorley   \n",
       "59332  25798        LydiaMorley   \n",
       "\n",
       "                                                user_url  index funny   \n",
       "0      http://steamcommunity.com/profiles/76561197970...    0.0        \\\n",
       "1      http://steamcommunity.com/profiles/76561197970...    1.0         \n",
       "2      http://steamcommunity.com/profiles/76561197970...    2.0         \n",
       "3                   http://steamcommunity.com/id/js41637    3.0         \n",
       "4                   http://steamcommunity.com/id/js41637    4.0         \n",
       "...                                                  ...    ...   ...   \n",
       "59328  http://steamcommunity.com/profiles/76561198312...    NaN   NaN   \n",
       "59329  http://steamcommunity.com/profiles/76561198312...    NaN   NaN   \n",
       "59330           http://steamcommunity.com/id/LydiaMorley    NaN   NaN   \n",
       "59331           http://steamcommunity.com/id/LydiaMorley    NaN   NaN   \n",
       "59332           http://steamcommunity.com/id/LydiaMorley    NaN   NaN   \n",
       "\n",
       "                          posted last_edited item_id   \n",
       "0       Posted November 5, 2011.                1250  \\\n",
       "1          Posted July 15, 2011.               22200   \n",
       "2         Posted April 21, 2011.               43110   \n",
       "3          Posted June 24, 2014.              251610   \n",
       "4      Posted September 8, 2013.              227300   \n",
       "...                          ...         ...     ...   \n",
       "59328                        NaN         NaN     NaN   \n",
       "59329                        NaN         NaN     NaN   \n",
       "59330                        NaN         NaN     NaN   \n",
       "59331                        NaN         NaN     NaN   \n",
       "59332                        NaN         NaN     NaN   \n",
       "\n",
       "                                               helpful recommend   \n",
       "0                                       No ratings yet      True  \\\n",
       "1                                       No ratings yet      True   \n",
       "2                                       No ratings yet      True   \n",
       "3      15 of 20 people (75%) found this review helpful      True   \n",
       "4         0 of 1 people (0%) found this review helpful      True   \n",
       "...                                                ...       ...   \n",
       "59328                                              NaN       NaN   \n",
       "59329                                              NaN       NaN   \n",
       "59330                                              NaN       NaN   \n",
       "59331                                              NaN       NaN   \n",
       "59332                                              NaN       NaN   \n",
       "\n",
       "                                                  review  \n",
       "0      Simple yet with great replayability. In my opi...  \n",
       "1                   It's unique and worth a playthrough.  \n",
       "2      Great atmosphere. The gunplay can be a bit chu...  \n",
       "3      I know what you think when you see this title ...  \n",
       "4      For a simple (it's actually not all that simpl...  \n",
       "...                                                  ...  \n",
       "59328                                                NaN  \n",
       "59329                                                NaN  \n",
       "59330                                                NaN  \n",
       "59331                                                NaN  \n",
       "59332                                                NaN  \n",
       "\n",
       "[59333 rows x 11 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Concateno con el data orignal y elimino la columna original \"reviews\" anidada\n",
    "reviews_ok = pd.concat([exploded, normalizado], axis=1)\n",
    "reviews_ok = reviews_ok.drop(columns = ['reviews'])\n",
    "reviews_ok"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_url</th>\n",
       "      <th>funny</th>\n",
       "      <th>posted</th>\n",
       "      <th>last_edited</th>\n",
       "      <th>item_id</th>\n",
       "      <th>helpful</th>\n",
       "      <th>recommend</th>\n",
       "      <th>review</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td></td>\n",
       "      <td>Posted November 5, 2011.</td>\n",
       "      <td></td>\n",
       "      <td>1250</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Simple yet with great replayability. In my opi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td></td>\n",
       "      <td>Posted July 15, 2011.</td>\n",
       "      <td></td>\n",
       "      <td>22200</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>It's unique and worth a playthrough.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td></td>\n",
       "      <td>Posted April 21, 2011.</td>\n",
       "      <td></td>\n",
       "      <td>43110</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Great atmosphere. The gunplay can be a bit chu...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>js41637</td>\n",
       "      <td>http://steamcommunity.com/id/js41637</td>\n",
       "      <td></td>\n",
       "      <td>Posted June 24, 2014.</td>\n",
       "      <td></td>\n",
       "      <td>251610</td>\n",
       "      <td>15 of 20 people (75%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>I know what you think when you see this title ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>js41637</td>\n",
       "      <td>http://steamcommunity.com/id/js41637</td>\n",
       "      <td></td>\n",
       "      <td>Posted September 8, 2013.</td>\n",
       "      <td></td>\n",
       "      <td>227300</td>\n",
       "      <td>0 of 1 people (0%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>For a simple (it's actually not all that simpl...</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59328</th>\n",
       "      <td>76561198312638244</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198312...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59329</th>\n",
       "      <td>76561198312638244</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198312...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59330</th>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>http://steamcommunity.com/id/LydiaMorley</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59331</th>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>http://steamcommunity.com/id/LydiaMorley</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59332</th>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>http://steamcommunity.com/id/LydiaMorley</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>59333 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 user_id                                           user_url   \n",
       "0      76561197970982479  http://steamcommunity.com/profiles/76561197970...  \\\n",
       "1      76561197970982479  http://steamcommunity.com/profiles/76561197970...   \n",
       "2      76561197970982479  http://steamcommunity.com/profiles/76561197970...   \n",
       "3                js41637               http://steamcommunity.com/id/js41637   \n",
       "4                js41637               http://steamcommunity.com/id/js41637   \n",
       "...                  ...                                                ...   \n",
       "59328  76561198312638244  http://steamcommunity.com/profiles/76561198312...   \n",
       "59329  76561198312638244  http://steamcommunity.com/profiles/76561198312...   \n",
       "59330        LydiaMorley           http://steamcommunity.com/id/LydiaMorley   \n",
       "59331        LydiaMorley           http://steamcommunity.com/id/LydiaMorley   \n",
       "59332        LydiaMorley           http://steamcommunity.com/id/LydiaMorley   \n",
       "\n",
       "      funny                     posted last_edited item_id   \n",
       "0             Posted November 5, 2011.                1250  \\\n",
       "1                Posted July 15, 2011.               22200   \n",
       "2               Posted April 21, 2011.               43110   \n",
       "3                Posted June 24, 2014.              251610   \n",
       "4            Posted September 8, 2013.              227300   \n",
       "...     ...                        ...         ...     ...   \n",
       "59328   NaN                        NaN         NaN     NaN   \n",
       "59329   NaN                        NaN         NaN     NaN   \n",
       "59330   NaN                        NaN         NaN     NaN   \n",
       "59331   NaN                        NaN         NaN     NaN   \n",
       "59332   NaN                        NaN         NaN     NaN   \n",
       "\n",
       "                                               helpful recommend   \n",
       "0                                       No ratings yet      True  \\\n",
       "1                                       No ratings yet      True   \n",
       "2                                       No ratings yet      True   \n",
       "3      15 of 20 people (75%) found this review helpful      True   \n",
       "4         0 of 1 people (0%) found this review helpful      True   \n",
       "...                                                ...       ...   \n",
       "59328                                              NaN       NaN   \n",
       "59329                                              NaN       NaN   \n",
       "59330                                              NaN       NaN   \n",
       "59331                                              NaN       NaN   \n",
       "59332                                              NaN       NaN   \n",
       "\n",
       "                                                  review  \n",
       "0      Simple yet with great replayability. In my opi...  \n",
       "1                   It's unique and worth a playthrough.  \n",
       "2      Great atmosphere. The gunplay can be a bit chu...  \n",
       "3      I know what you think when you see this title ...  \n",
       "4      For a simple (it's actually not all that simpl...  \n",
       "...                                                  ...  \n",
       "59328                                                NaN  \n",
       "59329                                                NaN  \n",
       "59330                                                NaN  \n",
       "59331                                                NaN  \n",
       "59332                                                NaN  \n",
       "\n",
       "[59333 rows x 9 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#elimino el doble index para que solo quede una sola manera de ordenar por indice\n",
    "reviews_ok= reviews_ok.drop(columns=\"index\")\n",
    "reviews_ok"
   ]
  },
  {
   "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>columna</th>\n",
       "      <th>tipos_de_datos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>user_id</td>\n",
       "      <td>[&lt;class 'str'&gt;]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>user_url</td>\n",
       "      <td>[&lt;class 'str'&gt;]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>funny</td>\n",
       "      <td>[&lt;class 'str'&gt;, &lt;class 'float'&gt;]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>posted</td>\n",
       "      <td>[&lt;class 'str'&gt;, &lt;class 'float'&gt;]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>last_edited</td>\n",
       "      <td>[&lt;class 'str'&gt;, &lt;class 'float'&gt;]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>item_id</td>\n",
       "      <td>[&lt;class 'str'&gt;, &lt;class 'float'&gt;]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>helpful</td>\n",
       "      <td>[&lt;class 'str'&gt;, &lt;class 'float'&gt;]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>recommend</td>\n",
       "      <td>[&lt;class 'bool'&gt;, &lt;class 'float'&gt;]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>review</td>\n",
       "      <td>[&lt;class 'str'&gt;, &lt;class 'float'&gt;]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       columna                     tipos_de_datos\n",
       "0      user_id                    [<class 'str'>]\n",
       "1     user_url                    [<class 'str'>]\n",
       "2        funny   [<class 'str'>, <class 'float'>]\n",
       "3       posted   [<class 'str'>, <class 'float'>]\n",
       "4  last_edited   [<class 'str'>, <class 'float'>]\n",
       "5      item_id   [<class 'str'>, <class 'float'>]\n",
       "6      helpful   [<class 'str'>, <class 'float'>]\n",
       "7    recommend  [<class 'bool'>, <class 'float'>]\n",
       "8       review   [<class 'str'>, <class 'float'>]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tipo_data = {\"columna\":[],\"tipos_de_datos\":[]} #genero un diccionario vacio para ir almacenando lo que genere el bucle\n",
    "\n",
    "for columna in reviews_ok.columns: #un bucle que va recorriendo \n",
    "    tipo_data[\"columna\"].append(columna)\n",
    "    tipo_data[\"tipos_de_datos\"].append(reviews_ok[columna].apply(type).unique())\n",
    "\n",
    "analisis= pd.DataFrame(tipo_data)\n",
    "analisis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Busqueda de duplicados y nulos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### En este dataframe la busqueda y eliminación de nulos ser realiza después de la normalización, debido a la gran cantidad de información contenida en la columna anidada \"reviews\", a modo de no perder datos sin haberlos analizado"
   ]
  },
  {
   "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>user_id</th>\n",
       "      <th>user_url</th>\n",
       "      <th>funny</th>\n",
       "      <th>posted</th>\n",
       "      <th>last_edited</th>\n",
       "      <th>item_id</th>\n",
       "      <th>helpful</th>\n",
       "      <th>recommend</th>\n",
       "      <th>review</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1112</th>\n",
       "      <td>bobseagull</td>\n",
       "      <td>http://steamcommunity.com/id/bobseagull</td>\n",
       "      <td></td>\n",
       "      <td>Posted September 24, 2015.</td>\n",
       "      <td></td>\n",
       "      <td>346110</td>\n",
       "      <td>1 of 1 people (100%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>yep</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2894</th>\n",
       "      <td>ImSeriouss</td>\n",
       "      <td>http://steamcommunity.com/id/ImSeriouss</td>\n",
       "      <td></td>\n",
       "      <td>Posted January 13, 2014.</td>\n",
       "      <td></td>\n",
       "      <td>211820</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>If you want to play this game.. expect glithes...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2895</th>\n",
       "      <td>ImSeriouss</td>\n",
       "      <td>http://steamcommunity.com/id/ImSeriouss</td>\n",
       "      <td></td>\n",
       "      <td>Posted January 10, 2014.</td>\n",
       "      <td></td>\n",
       "      <td>440</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Really good game! fun! Good for people who wan...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2896</th>\n",
       "      <td>ImSeriouss</td>\n",
       "      <td>http://steamcommunity.com/id/ImSeriouss</td>\n",
       "      <td></td>\n",
       "      <td>Posted March 19, 2012.</td>\n",
       "      <td></td>\n",
       "      <td>42680</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Good but a bit overdone. Still love it though.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3582</th>\n",
       "      <td>76561198062039159</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198062...</td>\n",
       "      <td></td>\n",
       "      <td>Posted December 11, 2015.</td>\n",
       "      <td></td>\n",
       "      <td>730</td>\n",
       "      <td>0 of 1 people (0%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>I rate it R8/Revolver</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59327</th>\n",
       "      <td>76561198312638244</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198312...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59328</th>\n",
       "      <td>76561198312638244</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198312...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59329</th>\n",
       "      <td>76561198312638244</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198312...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59331</th>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>http://steamcommunity.com/id/LydiaMorley</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59332</th>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>http://steamcommunity.com/id/LydiaMorley</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>154 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 user_id                                           user_url   \n",
       "1112          bobseagull            http://steamcommunity.com/id/bobseagull  \\\n",
       "2894          ImSeriouss            http://steamcommunity.com/id/ImSeriouss   \n",
       "2895          ImSeriouss            http://steamcommunity.com/id/ImSeriouss   \n",
       "2896          ImSeriouss            http://steamcommunity.com/id/ImSeriouss   \n",
       "3582   76561198062039159  http://steamcommunity.com/profiles/76561198062...   \n",
       "...                  ...                                                ...   \n",
       "59327  76561198312638244  http://steamcommunity.com/profiles/76561198312...   \n",
       "59328  76561198312638244  http://steamcommunity.com/profiles/76561198312...   \n",
       "59329  76561198312638244  http://steamcommunity.com/profiles/76561198312...   \n",
       "59331        LydiaMorley           http://steamcommunity.com/id/LydiaMorley   \n",
       "59332        LydiaMorley           http://steamcommunity.com/id/LydiaMorley   \n",
       "\n",
       "      funny                      posted last_edited item_id   \n",
       "1112         Posted September 24, 2015.              346110  \\\n",
       "2894           Posted January 13, 2014.              211820   \n",
       "2895           Posted January 10, 2014.                 440   \n",
       "2896             Posted March 19, 2012.               42680   \n",
       "3582          Posted December 11, 2015.                 730   \n",
       "...     ...                         ...         ...     ...   \n",
       "59327   NaN                         NaN         NaN     NaN   \n",
       "59328   NaN                         NaN         NaN     NaN   \n",
       "59329   NaN                         NaN         NaN     NaN   \n",
       "59331   NaN                         NaN         NaN     NaN   \n",
       "59332   NaN                         NaN         NaN     NaN   \n",
       "\n",
       "                                              helpful recommend   \n",
       "1112   1 of 1 people (100%) found this review helpful      True  \\\n",
       "2894                                   No ratings yet      True   \n",
       "2895                                   No ratings yet      True   \n",
       "2896                                   No ratings yet      True   \n",
       "3582     0 of 1 people (0%) found this review helpful      True   \n",
       "...                                               ...       ...   \n",
       "59327                                             NaN       NaN   \n",
       "59328                                             NaN       NaN   \n",
       "59329                                             NaN       NaN   \n",
       "59331                                             NaN       NaN   \n",
       "59332                                             NaN       NaN   \n",
       "\n",
       "                                                  review  \n",
       "1112                                                 yep  \n",
       "2894   If you want to play this game.. expect glithes...  \n",
       "2895   Really good game! fun! Good for people who wan...  \n",
       "2896      Good but a bit overdone. Still love it though.  \n",
       "3582                               I rate it R8/Revolver  \n",
       "...                                                  ...  \n",
       "59327                                                NaN  \n",
       "59328                                                NaN  \n",
       "59329                                                NaN  \n",
       "59331                                                NaN  \n",
       "59332                                                NaN  \n",
       "\n",
       "[154 rows x 9 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#se utiliza la variable duplicados para guardar la busqueda y poder comparar\n",
    "duplicados= reviews_ok.loc[reviews_ok.duplicated()]\n",
    "duplicados"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Se analiza si es correcto eliminar los duplicados"
   ]
  },
  {
   "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>user_id</th>\n",
       "      <th>user_url</th>\n",
       "      <th>funny</th>\n",
       "      <th>posted</th>\n",
       "      <th>last_edited</th>\n",
       "      <th>item_id</th>\n",
       "      <th>helpful</th>\n",
       "      <th>recommend</th>\n",
       "      <th>review</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td></td>\n",
       "      <td>Posted November 5, 2011.</td>\n",
       "      <td></td>\n",
       "      <td>1250</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Simple yet with great replayability. In my opi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td></td>\n",
       "      <td>Posted July 15, 2011.</td>\n",
       "      <td></td>\n",
       "      <td>22200</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>It's unique and worth a playthrough.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td></td>\n",
       "      <td>Posted April 21, 2011.</td>\n",
       "      <td></td>\n",
       "      <td>43110</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Great atmosphere. The gunplay can be a bit chu...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>js41637</td>\n",
       "      <td>http://steamcommunity.com/id/js41637</td>\n",
       "      <td></td>\n",
       "      <td>Posted June 24, 2014.</td>\n",
       "      <td></td>\n",
       "      <td>251610</td>\n",
       "      <td>15 of 20 people (75%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>I know what you think when you see this title ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>js41637</td>\n",
       "      <td>http://steamcommunity.com/id/js41637</td>\n",
       "      <td></td>\n",
       "      <td>Posted September 8, 2013.</td>\n",
       "      <td></td>\n",
       "      <td>227300</td>\n",
       "      <td>0 of 1 people (0%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>For a simple (it's actually not all that simpl...</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59323</th>\n",
       "      <td>76561198306599751</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198306...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59324</th>\n",
       "      <td>Ghoustik</td>\n",
       "      <td>http://steamcommunity.com/id/Ghoustik</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59325</th>\n",
       "      <td>76561198310819422</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198310...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59326</th>\n",
       "      <td>76561198312638244</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198312...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59330</th>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>http://steamcommunity.com/id/LydiaMorley</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>59179 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 user_id                                           user_url   \n",
       "0      76561197970982479  http://steamcommunity.com/profiles/76561197970...  \\\n",
       "1      76561197970982479  http://steamcommunity.com/profiles/76561197970...   \n",
       "2      76561197970982479  http://steamcommunity.com/profiles/76561197970...   \n",
       "3                js41637               http://steamcommunity.com/id/js41637   \n",
       "4                js41637               http://steamcommunity.com/id/js41637   \n",
       "...                  ...                                                ...   \n",
       "59323  76561198306599751  http://steamcommunity.com/profiles/76561198306...   \n",
       "59324           Ghoustik              http://steamcommunity.com/id/Ghoustik   \n",
       "59325  76561198310819422  http://steamcommunity.com/profiles/76561198310...   \n",
       "59326  76561198312638244  http://steamcommunity.com/profiles/76561198312...   \n",
       "59330        LydiaMorley           http://steamcommunity.com/id/LydiaMorley   \n",
       "\n",
       "      funny                     posted last_edited item_id   \n",
       "0             Posted November 5, 2011.                1250  \\\n",
       "1                Posted July 15, 2011.               22200   \n",
       "2               Posted April 21, 2011.               43110   \n",
       "3                Posted June 24, 2014.              251610   \n",
       "4            Posted September 8, 2013.              227300   \n",
       "...     ...                        ...         ...     ...   \n",
       "59323   NaN                        NaN         NaN     NaN   \n",
       "59324   NaN                        NaN         NaN     NaN   \n",
       "59325   NaN                        NaN         NaN     NaN   \n",
       "59326   NaN                        NaN         NaN     NaN   \n",
       "59330   NaN                        NaN         NaN     NaN   \n",
       "\n",
       "                                               helpful recommend   \n",
       "0                                       No ratings yet      True  \\\n",
       "1                                       No ratings yet      True   \n",
       "2                                       No ratings yet      True   \n",
       "3      15 of 20 people (75%) found this review helpful      True   \n",
       "4         0 of 1 people (0%) found this review helpful      True   \n",
       "...                                                ...       ...   \n",
       "59323                                              NaN       NaN   \n",
       "59324                                              NaN       NaN   \n",
       "59325                                              NaN       NaN   \n",
       "59326                                              NaN       NaN   \n",
       "59330                                              NaN       NaN   \n",
       "\n",
       "                                                  review  \n",
       "0      Simple yet with great replayability. In my opi...  \n",
       "1                   It's unique and worth a playthrough.  \n",
       "2      Great atmosphere. The gunplay can be a bit chu...  \n",
       "3      I know what you think when you see this title ...  \n",
       "4      For a simple (it's actually not all that simpl...  \n",
       "...                                                  ...  \n",
       "59323                                                NaN  \n",
       "59324                                                NaN  \n",
       "59325                                                NaN  \n",
       "59326                                                NaN  \n",
       "59330                                                NaN  \n",
       "\n",
       "[59179 rows x 9 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reviews_ok = reviews_ok.drop_duplicates(keep='first')\n",
    "reviews_ok"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "user_id         0\n",
       "user_url        0\n",
       "funny          18\n",
       "posted         18\n",
       "last_edited    18\n",
       "item_id        18\n",
       "helpful        18\n",
       "recommend      18\n",
       "review         18\n",
       "dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nulos= reviews_ok.isnull().sum()\n",
    "nulos"
   ]
  },
  {
   "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>user_id</th>\n",
       "      <th>user_url</th>\n",
       "      <th>funny</th>\n",
       "      <th>posted</th>\n",
       "      <th>last_edited</th>\n",
       "      <th>item_id</th>\n",
       "      <th>helpful</th>\n",
       "      <th>recommend</th>\n",
       "      <th>review</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td></td>\n",
       "      <td>Posted November 5, 2011.</td>\n",
       "      <td></td>\n",
       "      <td>1250</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Simple yet with great replayability. In my opi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td></td>\n",
       "      <td>Posted July 15, 2011.</td>\n",
       "      <td></td>\n",
       "      <td>22200</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>It's unique and worth a playthrough.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td></td>\n",
       "      <td>Posted April 21, 2011.</td>\n",
       "      <td></td>\n",
       "      <td>43110</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Great atmosphere. The gunplay can be a bit chu...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>js41637</td>\n",
       "      <td>http://steamcommunity.com/id/js41637</td>\n",
       "      <td></td>\n",
       "      <td>Posted June 24, 2014.</td>\n",
       "      <td></td>\n",
       "      <td>251610</td>\n",
       "      <td>15 of 20 people (75%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>I know what you think when you see this title ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>js41637</td>\n",
       "      <td>http://steamcommunity.com/id/js41637</td>\n",
       "      <td></td>\n",
       "      <td>Posted September 8, 2013.</td>\n",
       "      <td></td>\n",
       "      <td>227300</td>\n",
       "      <td>0 of 1 people (0%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>For a simple (it's actually not all that simpl...</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59156</th>\n",
       "      <td>Fuckfhaisjnsnsjakaka</td>\n",
       "      <td>http://steamcommunity.com/id/Fuckfhaisjnsnsjakaka</td>\n",
       "      <td></td>\n",
       "      <td>Posted July 10.</td>\n",
       "      <td></td>\n",
       "      <td>70</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>a must have classic from steam definitely wort...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59157</th>\n",
       "      <td>3214213216</td>\n",
       "      <td>http://steamcommunity.com/id/3214213216</td>\n",
       "      <td></td>\n",
       "      <td>Posted July 8.</td>\n",
       "      <td></td>\n",
       "      <td>362890</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>this game is a perfect remake of the original ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59158</th>\n",
       "      <td>ChrisCoroner</td>\n",
       "      <td>http://steamcommunity.com/id/ChrisCoroner</td>\n",
       "      <td>1 person found this review funny</td>\n",
       "      <td>Posted July 3.</td>\n",
       "      <td></td>\n",
       "      <td>273110</td>\n",
       "      <td>1 of 2 people (50%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>had so much fun plaing this and collecting res...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59159</th>\n",
       "      <td>CaptainAmericaCw</td>\n",
       "      <td>http://steamcommunity.com/id/CaptainAmericaCw</td>\n",
       "      <td></td>\n",
       "      <td>Posted July 20.</td>\n",
       "      <td></td>\n",
       "      <td>730</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>:D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59160</th>\n",
       "      <td>76561198267374962</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198267...</td>\n",
       "      <td></td>\n",
       "      <td>Posted July 2.</td>\n",
       "      <td></td>\n",
       "      <td>440</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>so much fun :D</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>59161 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    user_id   \n",
       "0         76561197970982479  \\\n",
       "1         76561197970982479   \n",
       "2         76561197970982479   \n",
       "3                   js41637   \n",
       "4                   js41637   \n",
       "...                     ...   \n",
       "59156  Fuckfhaisjnsnsjakaka   \n",
       "59157            3214213216   \n",
       "59158          ChrisCoroner   \n",
       "59159      CaptainAmericaCw   \n",
       "59160     76561198267374962   \n",
       "\n",
       "                                                user_url   \n",
       "0      http://steamcommunity.com/profiles/76561197970...  \\\n",
       "1      http://steamcommunity.com/profiles/76561197970...   \n",
       "2      http://steamcommunity.com/profiles/76561197970...   \n",
       "3                   http://steamcommunity.com/id/js41637   \n",
       "4                   http://steamcommunity.com/id/js41637   \n",
       "...                                                  ...   \n",
       "59156  http://steamcommunity.com/id/Fuckfhaisjnsnsjakaka   \n",
       "59157            http://steamcommunity.com/id/3214213216   \n",
       "59158          http://steamcommunity.com/id/ChrisCoroner   \n",
       "59159      http://steamcommunity.com/id/CaptainAmericaCw   \n",
       "59160  http://steamcommunity.com/profiles/76561198267...   \n",
       "\n",
       "                                  funny                     posted   \n",
       "0                                         Posted November 5, 2011.  \\\n",
       "1                                            Posted July 15, 2011.   \n",
       "2                                           Posted April 21, 2011.   \n",
       "3                                            Posted June 24, 2014.   \n",
       "4                                        Posted September 8, 2013.   \n",
       "...                                 ...                        ...   \n",
       "59156                                              Posted July 10.   \n",
       "59157                                               Posted July 8.   \n",
       "59158  1 person found this review funny             Posted July 3.   \n",
       "59159                                              Posted July 20.   \n",
       "59160                                               Posted July 2.   \n",
       "\n",
       "      last_edited item_id                                          helpful   \n",
       "0                    1250                                   No ratings yet  \\\n",
       "1                   22200                                   No ratings yet   \n",
       "2                   43110                                   No ratings yet   \n",
       "3                  251610  15 of 20 people (75%) found this review helpful   \n",
       "4                  227300     0 of 1 people (0%) found this review helpful   \n",
       "...           ...     ...                                              ...   \n",
       "59156                  70                                   No ratings yet   \n",
       "59157              362890                                   No ratings yet   \n",
       "59158              273110    1 of 2 people (50%) found this review helpful   \n",
       "59159                 730                                   No ratings yet   \n",
       "59160                 440                                   No ratings yet   \n",
       "\n",
       "      recommend                                             review  \n",
       "0          True  Simple yet with great replayability. In my opi...  \n",
       "1          True               It's unique and worth a playthrough.  \n",
       "2          True  Great atmosphere. The gunplay can be a bit chu...  \n",
       "3          True  I know what you think when you see this title ...  \n",
       "4          True  For a simple (it's actually not all that simpl...  \n",
       "...         ...                                                ...  \n",
       "59156      True  a must have classic from steam definitely wort...  \n",
       "59157      True  this game is a perfect remake of the original ...  \n",
       "59158      True  had so much fun plaing this and collecting res...  \n",
       "59159      True                                                 :D  \n",
       "59160      True                                     so much fun :D  \n",
       "\n",
       "[59161 rows x 9 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reviews_ok = reviews_ok.dropna().reset_index(drop=True)\n",
    "reviews_ok"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Transformación de los datos de tipo fecha en \"posted\" "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### El formato de fecha en la columna es Posted November 5, 2011. Mientras que debería estar en el mismo formato YYYY-MM-DD, que en los demás dataset a fin de facilitar las consultas "
   ]
  },
  {
   "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>user_id</th>\n",
       "      <th>user_url</th>\n",
       "      <th>funny</th>\n",
       "      <th>posted</th>\n",
       "      <th>last_edited</th>\n",
       "      <th>item_id</th>\n",
       "      <th>helpful</th>\n",
       "      <th>recommend</th>\n",
       "      <th>review</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td></td>\n",
       "      <td>November 5, 2011</td>\n",
       "      <td></td>\n",
       "      <td>1250</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Simple yet with great replayability. In my opi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td></td>\n",
       "      <td>July 15, 2011</td>\n",
       "      <td></td>\n",
       "      <td>22200</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>It's unique and worth a playthrough.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td></td>\n",
       "      <td>April 21, 2011</td>\n",
       "      <td></td>\n",
       "      <td>43110</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Great atmosphere. The gunplay can be a bit chu...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             user_id                                           user_url funny   \n",
       "0  76561197970982479  http://steamcommunity.com/profiles/76561197970...        \\\n",
       "1  76561197970982479  http://steamcommunity.com/profiles/76561197970...         \n",
       "2  76561197970982479  http://steamcommunity.com/profiles/76561197970...         \n",
       "\n",
       "             posted last_edited item_id         helpful recommend   \n",
       "0  November 5, 2011                1250  No ratings yet      True  \\\n",
       "1     July 15, 2011               22200  No ratings yet      True   \n",
       "2    April 21, 2011               43110  No ratings yet      True   \n",
       "\n",
       "                                              review  \n",
       "0  Simple yet with great replayability. In my opi...  \n",
       "1               It's unique and worth a playthrough.  \n",
       "2  Great atmosphere. The gunplay can be a bit chu...  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Primer paso es extraer del la columna posted la fecha que esta en tipo de dato string, entonces puedo transformarla\n",
    "reviews_ok['posted'] = reviews_ok['posted'].str.extract(r'Posted ([\\w\\s\\d,]+)')\n",
    "reviews_ok.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_url</th>\n",
       "      <th>funny</th>\n",
       "      <th>posted</th>\n",
       "      <th>last_edited</th>\n",
       "      <th>item_id</th>\n",
       "      <th>helpful</th>\n",
       "      <th>recommend</th>\n",
       "      <th>review</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td></td>\n",
       "      <td>November 5, 2011</td>\n",
       "      <td></td>\n",
       "      <td>1250</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Simple yet with great replayability. In my opi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td></td>\n",
       "      <td>July 15, 2011</td>\n",
       "      <td></td>\n",
       "      <td>22200</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>It's unique and worth a playthrough.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td></td>\n",
       "      <td>April 21, 2011</td>\n",
       "      <td></td>\n",
       "      <td>43110</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Great atmosphere. The gunplay can be a bit chu...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             user_id                                           user_url funny   \n",
       "0  76561197970982479  http://steamcommunity.com/profiles/76561197970...        \\\n",
       "1  76561197970982479  http://steamcommunity.com/profiles/76561197970...         \n",
       "2  76561197970982479  http://steamcommunity.com/profiles/76561197970...         \n",
       "\n",
       "             posted last_edited item_id         helpful recommend   \n",
       "0  November 5, 2011                1250  No ratings yet      True  \\\n",
       "1     July 15, 2011               22200  No ratings yet      True   \n",
       "2    April 21, 2011               43110  No ratings yet      True   \n",
       "\n",
       "                                              review  \n",
       "0  Simple yet with great replayability. In my opi...  \n",
       "1               It's unique and worth a playthrough.  \n",
       "2  Great atmosphere. The gunplay can be a bit chu...  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Reemplazo la palabra Posted por espacio vacio\n",
    "reviews_ok['posted'] = reviews_ok['posted'].replace({'Posted': ''}, regex=True)\n",
    "reviews_ok.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_url</th>\n",
       "      <th>funny</th>\n",
       "      <th>posted</th>\n",
       "      <th>last_edited</th>\n",
       "      <th>item_id</th>\n",
       "      <th>helpful</th>\n",
       "      <th>recommend</th>\n",
       "      <th>review</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td></td>\n",
       "      <td>2011-11-05</td>\n",
       "      <td></td>\n",
       "      <td>1250</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Simple yet with great replayability. In my opi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td></td>\n",
       "      <td>2011-07-15</td>\n",
       "      <td></td>\n",
       "      <td>22200</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>It's unique and worth a playthrough.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td></td>\n",
       "      <td>2011-04-21</td>\n",
       "      <td></td>\n",
       "      <td>43110</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Great atmosphere. The gunplay can be a bit chu...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             user_id                                           user_url funny   \n",
       "0  76561197970982479  http://steamcommunity.com/profiles/76561197970...        \\\n",
       "1  76561197970982479  http://steamcommunity.com/profiles/76561197970...         \n",
       "2  76561197970982479  http://steamcommunity.com/profiles/76561197970...         \n",
       "\n",
       "      posted last_edited item_id         helpful recommend   \n",
       "0 2011-11-05                1250  No ratings yet      True  \\\n",
       "1 2011-07-15               22200  No ratings yet      True   \n",
       "2 2011-04-21               43110  No ratings yet      True   \n",
       "\n",
       "                                              review  \n",
       "0  Simple yet with great replayability. In my opi...  \n",
       "1               It's unique and worth a playthrough.  \n",
       "2  Great atmosphere. The gunplay can be a bit chu...  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Transformo ahora la columna a tipo de dato datetime\n",
    "reviews_ok['posted'] = pd.to_datetime(reviews_ok['posted'], errors='coerce')\n",
    "reviews_ok.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Guardo el dataframe para las consultas transformandolo a tipo de archivo parquet para optimizar su utilización"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "reviews_ok = reviews_ok.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "guardar = 'data/user_review_limpio.csv'\n",
    "reviews_ok.to_csv(guardar, index=False, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Transformo el archivo csv a parquet\n",
    "#Leo el archivo csv\n",
    "reviews_ok= pd.read_csv(\"data/user_review_limpio.csv\") \n",
    "\n",
    "#Indico donde quiero guardar el parquet y con que nombre\n",
    "output_file= \"data/user_review.parquet\"\n",
    "\n",
    "#Transformo a traves de una tabla el archivo csv en parquet\n",
    "table = pa.Table.from_pandas(reviews_ok)\n",
    "pq.write_table(table,output_file)"
   ]
  }
 ],
 "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.11.2"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}