02-etl-reviews.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ETL del dataset `steam_reviews`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "En esta jupyter notebook se desarrolla la extracción, transformación y carga (ETL) del conjunto de datos `steam_reviews.json.gz`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Descripción de Columnas en user_reviews.gz.json\n",
    "\n",
    "| Columna     | Descripción                                   | Ejemplo                                                                      |\n",
    "|-------------|-----------------------------------------------|------------------------------------------------------------------------------|\n",
    "| user_id     | Identificador único de usuario                | [76561197970982479, evcentric, maplemage]                                   |\n",
    "| user_url    | URL del perfil del usuario                    | [http://steamcommunity.com/id/evcentric]                                    |\n",
    "| reviews     | Revisión del usuario en formato JSON          | {'funny': '', 'posted': 'Posted September 8, 2013.', 'last_edited': '', 'item_id': '227300', 'helpful': '0 of 1 people (0%) found this review helpful', 'recommend': True, 'review': \"For a simple (it's actually not all that simple but it can be!) truck driving Simulator, it is quite a fun and relaxing game. Playing on simple (or easy?) its just the basic WASD keys for driving but (if you want) the game can be much harder and realistic with having to manually change gears, much harder turning, etc. And reversing in this game is a ♥♥♥♥♥, as I imagine it would be with an actual truck. Luckily, you don't have to reverse park it but you get extra points if you do cause it is bloody hard. But this is surprisingly a nice truck driving game and I had a bit of fun with it.\"} |\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import gzip  # Para trabajar con archivos comprimidos en formato Gzip\n",
    "import json  # Para trabajar con datos en formato JSON\n",
    "import matplotlib.pyplot as plt  # Para crear gráficos y visualizaciones\n",
    "import numpy as np  # Para operaciones matemáticas eficientes y manipulación de arrays\n",
    "import pandas as pd  # Para el análisis y manipulación de datos en forma de DataFrames\n",
    "import pyarrow as pa  # Herramientas para trabajar con datos en formato de flecha (Arrow)\n",
    "import pyarrow.parquet as pq  # Para trabajar con archivos en formato Parquet\n",
    "import seaborn as sns  # Biblioteca de visualización de datos basada en matplotlib\n",
    "import ast # Proporciona funciones para analizar y manipular el árbol de sintaxis abstracta\n",
    "from datetime import datetime, timedelta"
   ]
  },
  {
   "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": [
    "filas_review = []  # creamos una lista vacía para ir agregando las filas del archivo json\n",
    "with gzip.open(\"data/user_reviews.json.gz\", 'rt', encoding='utf-8') as file:\n",
    "    for line in file.readlines():\n",
    "        filas_review.append(ast.literal_eval(line))\n",
    "\n",
    "# Se convierte en dataframe\n",
    "reviews = pd.DataFrame(filas_review)\n",
    "reviews"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ahora es necesario desanidar los registros que contienen listas"
   ]
  },
  {
   "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>9</th>\n",
       "      <td>76561198156664158</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198156...</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted June 16.', 'l...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Rivtex</td>\n",
       "      <td>http://steamcommunity.com/id/Rivtex</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted December 23, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>76561198094224872</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198094...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>DieMadchenschanderin</td>\n",
       "      <td>http://steamcommunity.com/id/DieMadchenschanderin</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted August 29, 20...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>relesprit</td>\n",
       "      <td>http://steamcommunity.com/id/relesprit</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted December 27, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17819</th>\n",
       "      <td>76561198076474887</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198076...</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted April 12.', '...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17916</th>\n",
       "      <td>yolofaceguy</td>\n",
       "      <td>http://steamcommunity.com/id/yolofaceguy</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted October 31, 2...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18028</th>\n",
       "      <td>76561198075591109</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198075...</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted December 26, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18234</th>\n",
       "      <td>76561198092022514</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198092...</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted July 3.', 'la...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18309</th>\n",
       "      <td>SuchGayMuchWow</td>\n",
       "      <td>http://steamcommunity.com/id/SuchGayMuchWow</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted July 18, 2015...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>623 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    user_id  \\\n",
       "9         76561198156664158   \n",
       "50                   Rivtex   \n",
       "83        76561198094224872   \n",
       "119    DieMadchenschanderin   \n",
       "147               relesprit   \n",
       "...                     ...   \n",
       "17819     76561198076474887   \n",
       "17916           yolofaceguy   \n",
       "18028     76561198075591109   \n",
       "18234     76561198092022514   \n",
       "18309        SuchGayMuchWow   \n",
       "\n",
       "                                                user_url  \\\n",
       "9      http://steamcommunity.com/profiles/76561198156...   \n",
       "50                   http://steamcommunity.com/id/Rivtex   \n",
       "83     http://steamcommunity.com/profiles/76561198094...   \n",
       "119    http://steamcommunity.com/id/DieMadchenschanderin   \n",
       "147               http://steamcommunity.com/id/relesprit   \n",
       "...                                                  ...   \n",
       "17819  http://steamcommunity.com/profiles/76561198076...   \n",
       "17916           http://steamcommunity.com/id/yolofaceguy   \n",
       "18028  http://steamcommunity.com/profiles/76561198075...   \n",
       "18234  http://steamcommunity.com/profiles/76561198092...   \n",
       "18309        http://steamcommunity.com/id/SuchGayMuchWow   \n",
       "\n",
       "                                                 reviews  \n",
       "9      [{'funny': '', 'posted': 'Posted June 16.', 'l...  \n",
       "50     [{'funny': '', 'posted': 'Posted December 23, ...  \n",
       "83                                                    []  \n",
       "119    [{'funny': '', 'posted': 'Posted August 29, 20...  \n",
       "147    [{'funny': '', 'posted': 'Posted December 27, ...  \n",
       "...                                                  ...  \n",
       "17819  [{'funny': '', 'posted': 'Posted April 12.', '...  \n",
       "17916  [{'funny': '', 'posted': 'Posted October 31, 2...  \n",
       "18028  [{'funny': '', 'posted': 'Posted December 26, ...  \n",
       "18234  [{'funny': '', 'posted': 'Posted July 3.', 'la...  \n",
       "18309  [{'funny': '', 'posted': 'Posted July 18, 2015...  \n",
       "\n",
       "[623 rows x 3 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "duplicados_columnas = reviews[reviews.duplicated(subset=['user_id'], keep=False)] # Verificamos si hay filas duplicadas en la columna id\n",
    "duplicados_columnas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "This game to me it is so good that it is better than any of the games out their and $15 worth it\n",
      "this is the best third person game ever that i have played\n",
      "this will be the  number one game if it have more competitive things\n",
      "====================================================================================================\n",
      "This game to me it is so good that it is better than any of the games out their and $15 worth it\n",
      "this is the best third person game ever that i have played\n",
      "this will be the  number one game if it have more competitive things\n",
      "====================================================================================================\n",
      "10/10 too many children with mikes.\n",
      "====================================================================================================\n",
      "10/10 too many children with mikes.\n",
      "====================================================================================================\n",
      "Excelente e cada vez mais bonito.Comprei o jogo pouco tempo depois de seu lançamento, e cheguei as minhas primeiras 24 horas jogadas em um piscar de olhos. Com jogabilidade simples, bela musica e arte o jogo me conquistou quase que de imediato.Devo admitir que após algumas horas de jogo, me senti frustrado com alguns bugs falhas na contrução do mundo que fui encontrando enquanto ia evoluindo ou após comessar um novo jogo. Porem, após a ultima atualização, o jogo prativcamente se transformou, mantendo a sua premissa mas se tornando mais belo ( tanto a qualidade e a variedade de suas músicas quando a arte visual como um todo) e muito mais dinâmico e coeso.Obrigado por ensinar os personagens a correr :D.Build, defend, expand.\n",
      "====================================================================================================\n",
      "Excelente e cada vez mais bonito.Comprei o jogo pouco tempo depois de seu lançamento, e cheguei as minhas primeiras 24 horas jogadas em um piscar de olhos. Com jogabilidade simples, bela musica e arte o jogo me conquistou quase que de imediato.Devo admitir que após algumas horas de jogo, me senti frustrado com alguns bugs falhas na contrução do mundo que fui encontrando enquanto ia evoluindo ou após comessar um novo jogo. Porem, após a ultima atualização, o jogo prativcamente se transformou, mantendo a sua premissa mas se tornando mais belo ( tanto a qualidade e a variedade de suas músicas quando a arte visual como um todo) e muito mais dinâmico e coeso.Obrigado por ensinar os personagens a correr :D.Build, defend, expand.\n",
      "====================================================================================================\n"
     ]
    }
   ],
   "source": [
    "# Se revisan tres usuarios  de forma aleatoria\n",
    "\n",
    "user_id = '05041129'\n",
    "user_reviews = duplicados_columnas[duplicados_columnas['user_id'] == user_id]['reviews']\n",
    "\n",
    "for review_list in user_reviews:\n",
    "    for review in review_list:\n",
    "        print(review['review'])\n",
    "    print('=' * 100)\n",
    "    \n",
    "user_id = 'SuchGayMuchWow'\n",
    "user_reviews = duplicados_columnas[duplicados_columnas['user_id'] == user_id]['reviews']\n",
    "\n",
    "for review_list in user_reviews:\n",
    "    for review in review_list:\n",
    "        print(review['review'])\n",
    "    print('=' * 100)\n",
    "    \n",
    "user_id = '76561198076474887'\n",
    "user_reviews = duplicados_columnas[duplicados_columnas['user_id'] == user_id]['reviews']\n",
    "\n",
    "for review_list in user_reviews:\n",
    "    for review in review_list:\n",
    "        print(review['review'])\n",
    "    print('=' * 100)"
   ]
  },
  {
   "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>user_id</th>\n",
       "      <th>user_url</th>\n",
       "      <th>reviews</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [user_id, user_url, reviews]\n",
       "Index: []"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reviews = reviews.drop_duplicates(subset='user_id', keep='first')\n",
    "duplicados_columnas = reviews[reviews.duplicated(subset=['user_id'], keep=False)]\n",
    "duplicados_columnas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(25485, 3)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reviews.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se revisa la columna review"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'funny': '',\n",
       "  'posted': 'Posted November 5, 2011.',\n",
       "  'last_edited': '',\n",
       "  'item_id': '1250',\n",
       "  'helpful': 'No ratings yet',\n",
       "  'recommend': True,\n",
       "  'review': 'Simple yet with great replayability. In my opinion does \"zombie\" hordes and team work better than left 4 dead plus has a global leveling system. Alot of down to earth \"zombie\" splattering fun for the whole family. Amazed this sort of FPS is so rare.'},\n",
       " {'funny': '',\n",
       "  'posted': 'Posted July 15, 2011.',\n",
       "  'last_edited': '',\n",
       "  'item_id': '22200',\n",
       "  'helpful': 'No ratings yet',\n",
       "  'recommend': True,\n",
       "  'review': \"It's unique and worth a playthrough.\"},\n",
       " {'funny': '',\n",
       "  'posted': 'Posted April 21, 2011.',\n",
       "  'last_edited': '',\n",
       "  'item_id': '43110',\n",
       "  'helpful': 'No ratings yet',\n",
       "  'recommend': True,\n",
       "  'review': 'Great atmosphere. The gunplay can be a bit chunky at times but at the end of the day this game is definitely worth it and I hope they do a sequel...so buy the game so I get a sequel!'}]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reviews['reviews'][0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "| Campo         | Descripción                                                                      |\n",
    "|---------------|----------------------------------------------------------------------------------|\n",
    "| user_id       | Identificador único para el usuario.                                             |\n",
    "| user_url      | URL del perfil del usuario en streamcommunity.                                   |\n",
    "| reviews       | Lista de diccionarios que contienen información sobre los reviews del usuario.  |\n",
    "| - funny       | Indica si alguien puso emoticón de gracioso al review.                           |\n",
    "| - posted      | Fecha de posteo del review en formato \"Posted April 21, 2011\".                    |\n",
    "| - last_edited | Fecha de la última edición del review.                                           |\n",
    "| - item_id     | Identificador único del item (juego).                                           |\n",
    "| - helpful     | Estadística donde otros usuarios indican si fue útil la información.            |\n",
    "| - recommend   | Booleano que indica si el usuario recomienda o no el juego.                      |\n",
    "| - review      | Sentencia string con los comentarios sobre el juego.                             |\n"
   ]
  },
  {
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{'funny': '', 'posted': 'Posted November 5, 20...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted July 15, 2011....</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted April 21, 2011...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{'funny': '', 'posted': 'Posted June 24, 2014....</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted September 8, 2...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted November 29, 2...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{'funny': '', 'posted': 'Posted February 3.', ...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted December 4, 20...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted November 3, 20...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted October 15, 20...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted October 15, 20...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted October 15, 20...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{'funny': '', 'posted': 'Posted October 14, 20...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted July 28, 2012....</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted June 2, 2012.'...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted June 29, 2014....</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted November 22, 2...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted February 23, 2...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{'funny': '3 people found this review funny', ...</td>\n",
       "      <td>{'funny': '1 person found this review funny', ...</td>\n",
       "      <td>{'funny': '2 people found this review funny', ...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted July 11, 2013....</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   0  \\\n",
       "0  {'funny': '', 'posted': 'Posted November 5, 20...   \n",
       "1  {'funny': '', 'posted': 'Posted June 24, 2014....   \n",
       "2  {'funny': '', 'posted': 'Posted February 3.', ...   \n",
       "3  {'funny': '', 'posted': 'Posted October 14, 20...   \n",
       "4  {'funny': '3 people found this review funny', ...   \n",
       "\n",
       "                                                   1  \\\n",
       "0  {'funny': '', 'posted': 'Posted July 15, 2011....   \n",
       "1  {'funny': '', 'posted': 'Posted September 8, 2...   \n",
       "2  {'funny': '', 'posted': 'Posted December 4, 20...   \n",
       "3  {'funny': '', 'posted': 'Posted July 28, 2012....   \n",
       "4  {'funny': '1 person found this review funny', ...   \n",
       "\n",
       "                                                   2  \\\n",
       "0  {'funny': '', 'posted': 'Posted April 21, 2011...   \n",
       "1  {'funny': '', 'posted': 'Posted November 29, 2...   \n",
       "2  {'funny': '', 'posted': 'Posted November 3, 20...   \n",
       "3  {'funny': '', 'posted': 'Posted June 2, 2012.'...   \n",
       "4  {'funny': '2 people found this review funny', ...   \n",
       "\n",
       "                                                   3  \\\n",
       "0                                               None   \n",
       "1                                               None   \n",
       "2  {'funny': '', 'posted': 'Posted October 15, 20...   \n",
       "3  {'funny': '', 'posted': 'Posted June 29, 2014....   \n",
       "4  {'funny': '', 'posted': 'Posted July 11, 2013....   \n",
       "\n",
       "                                                   4  \\\n",
       "0                                               None   \n",
       "1                                               None   \n",
       "2  {'funny': '', 'posted': 'Posted October 15, 20...   \n",
       "3  {'funny': '', 'posted': 'Posted November 22, 2...   \n",
       "4                                               None   \n",
       "\n",
       "                                                   5     6     7     8     9  \n",
       "0                                               None  None  None  None  None  \n",
       "1                                               None  None  None  None  None  \n",
       "2  {'funny': '', 'posted': 'Posted October 15, 20...  None  None  None  None  \n",
       "3  {'funny': '', 'posted': 'Posted February 23, 2...  None  None  None  None  \n",
       "4                                               None  None  None  None  None  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "review_norm = pd.json_normalize(reviews['reviews'].dropna()) # Utilizamos el método json_normalize para normalizar los datos\n",
    "review_norm.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se pierde user id y user url"
   ]
  },
  {
   "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>user_id</th>\n",
       "      <th>user_url</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</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",
       "      <td>{'funny': '', 'posted': 'Posted July 15, 2011....</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted April 21, 2011...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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",
       "      <td>{'funny': '', 'posted': 'Posted September 8, 2...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted November 29, 2...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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",
       "      <td>{'funny': '', 'posted': 'Posted December 4, 20...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted November 3, 20...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted October 15, 20...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted October 15, 20...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted October 15, 20...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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, 20...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted July 28, 2012....</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted June 2, 2012.'...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted June 29, 2014....</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted November 22, 2...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted February 23, 2...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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",
       "      <td>{'funny': '1 person found this review funny', ...</td>\n",
       "      <td>{'funny': '2 people found this review funny', ...</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted July 11, 2013....</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\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",
       "                                                   0  \\\n",
       "0  {'funny': '', 'posted': 'Posted November 5, 20...   \n",
       "1  {'funny': '', 'posted': 'Posted June 24, 2014....   \n",
       "2  {'funny': '', 'posted': 'Posted February 3.', ...   \n",
       "3  {'funny': '', 'posted': 'Posted October 14, 20...   \n",
       "4  {'funny': '3 people found this review funny', ...   \n",
       "\n",
       "                                                   1  \\\n",
       "0  {'funny': '', 'posted': 'Posted July 15, 2011....   \n",
       "1  {'funny': '', 'posted': 'Posted September 8, 2...   \n",
       "2  {'funny': '', 'posted': 'Posted December 4, 20...   \n",
       "3  {'funny': '', 'posted': 'Posted July 28, 2012....   \n",
       "4  {'funny': '1 person found this review funny', ...   \n",
       "\n",
       "                                                   2  \\\n",
       "0  {'funny': '', 'posted': 'Posted April 21, 2011...   \n",
       "1  {'funny': '', 'posted': 'Posted November 29, 2...   \n",
       "2  {'funny': '', 'posted': 'Posted November 3, 20...   \n",
       "3  {'funny': '', 'posted': 'Posted June 2, 2012.'...   \n",
       "4  {'funny': '2 people found this review funny', ...   \n",
       "\n",
       "                                                   3  \\\n",
       "0                                               None   \n",
       "1                                               None   \n",
       "2  {'funny': '', 'posted': 'Posted October 15, 20...   \n",
       "3  {'funny': '', 'posted': 'Posted June 29, 2014....   \n",
       "4  {'funny': '', 'posted': 'Posted July 11, 2013....   \n",
       "\n",
       "                                                   4  \\\n",
       "0                                               None   \n",
       "1                                               None   \n",
       "2  {'funny': '', 'posted': 'Posted October 15, 20...   \n",
       "3  {'funny': '', 'posted': 'Posted November 22, 2...   \n",
       "4                                               None   \n",
       "\n",
       "                                                   5     6     7     8     9  \n",
       "0                                               None  None  None  None  None  \n",
       "1                                               None  None  None  None  None  \n",
       "2  {'funny': '', 'posted': 'Posted October 15, 20...  None  None  None  None  \n",
       "3  {'funny': '', 'posted': 'Posted February 23, 2...  None  None  None  None  \n",
       "4                                               None  None  None  None  None  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Se agrega el 'user_id' y 'user_url' a las columnas separadas \n",
    "review_norm = pd.concat([reviews[['user_id', 'user_url']], review_norm], axis=1)\n",
    "review_norm.head()"
   ]
  },
  {
   "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>variable</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>0</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted November 5, 20...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>js41637</td>\n",
       "      <td>http://steamcommunity.com/id/js41637</td>\n",
       "      <td>0</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>0</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>0</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted October 14, 20...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>maplemage</td>\n",
       "      <td>http://steamcommunity.com/id/maplemage</td>\n",
       "      <td>0</td>\n",
       "      <td>{'funny': '3 people found this review funny', ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\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",
       "  variable                                            reviews  \n",
       "0        0  {'funny': '', 'posted': 'Posted November 5, 20...  \n",
       "1        0  {'funny': '', 'posted': 'Posted June 24, 2014....  \n",
       "2        0  {'funny': '', 'posted': 'Posted February 3.', ...  \n",
       "3        0  {'funny': '', 'posted': 'Posted October 14, 20...  \n",
       "4        0  {'funny': '3 people found this review funny', ...  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Se utiliza pd.melt para transformar las columnas en filas conservando el 'user_id' y 'user_url'\n",
    "review_norm = pd.melt(review_norm, id_vars=['user_id', 'user_url'], \n",
    "                       value_vars=list(range(9)),\n",
    "                       value_name='reviews')\n",
    "review_norm.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_url</th>\n",
       "      <th>variable</th>\n",
       "      <th>reviews</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>http://steamcommunity.com/id/evcentric</td>\n",
       "      <td>0</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted February 3.', ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25801</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>http://steamcommunity.com/id/evcentric</td>\n",
       "      <td>1</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted December 4, 20...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51600</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>http://steamcommunity.com/id/evcentric</td>\n",
       "      <td>2</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted November 3, 20...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77399</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>http://steamcommunity.com/id/evcentric</td>\n",
       "      <td>3</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted October 15, 20...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103198</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>http://steamcommunity.com/id/evcentric</td>\n",
       "      <td>4</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted October 15, 20...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128997</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>http://steamcommunity.com/id/evcentric</td>\n",
       "      <td>5</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted October 15, 20...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154796</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>http://steamcommunity.com/id/evcentric</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180595</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>http://steamcommunity.com/id/evcentric</td>\n",
       "      <td>7</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206394</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>http://steamcommunity.com/id/evcentric</td>\n",
       "      <td>8</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          user_id                                user_url variable  \\\n",
       "2       evcentric  http://steamcommunity.com/id/evcentric        0   \n",
       "25801   evcentric  http://steamcommunity.com/id/evcentric        1   \n",
       "51600   evcentric  http://steamcommunity.com/id/evcentric        2   \n",
       "77399   evcentric  http://steamcommunity.com/id/evcentric        3   \n",
       "103198  evcentric  http://steamcommunity.com/id/evcentric        4   \n",
       "128997  evcentric  http://steamcommunity.com/id/evcentric        5   \n",
       "154796  evcentric  http://steamcommunity.com/id/evcentric        6   \n",
       "180595  evcentric  http://steamcommunity.com/id/evcentric        7   \n",
       "206394  evcentric  http://steamcommunity.com/id/evcentric        8   \n",
       "\n",
       "                                                  reviews  \n",
       "2       {'funny': '', 'posted': 'Posted February 3.', ...  \n",
       "25801   {'funny': '', 'posted': 'Posted December 4, 20...  \n",
       "51600   {'funny': '', 'posted': 'Posted November 3, 20...  \n",
       "77399   {'funny': '', 'posted': 'Posted October 15, 20...  \n",
       "103198  {'funny': '', 'posted': 'Posted October 15, 20...  \n",
       "128997  {'funny': '', 'posted': 'Posted October 15, 20...  \n",
       "154796                                               None  \n",
       "180595                                               None  \n",
       "206394                                               None  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "review_norm[review_norm['user_id']=='evcentric']"
   ]
  },
  {
   "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>variable</th>\n",
       "      <th>reviews</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>http://steamcommunity.com/id/evcentric</td>\n",
       "      <td>0</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted February 3.', ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25801</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>http://steamcommunity.com/id/evcentric</td>\n",
       "      <td>1</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted December 4, 20...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51600</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>http://steamcommunity.com/id/evcentric</td>\n",
       "      <td>2</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted November 3, 20...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77399</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>http://steamcommunity.com/id/evcentric</td>\n",
       "      <td>3</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted October 15, 20...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103198</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>http://steamcommunity.com/id/evcentric</td>\n",
       "      <td>4</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted October 15, 20...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128997</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>http://steamcommunity.com/id/evcentric</td>\n",
       "      <td>5</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted October 15, 20...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          user_id                                user_url variable  \\\n",
       "2       evcentric  http://steamcommunity.com/id/evcentric        0   \n",
       "25801   evcentric  http://steamcommunity.com/id/evcentric        1   \n",
       "51600   evcentric  http://steamcommunity.com/id/evcentric        2   \n",
       "77399   evcentric  http://steamcommunity.com/id/evcentric        3   \n",
       "103198  evcentric  http://steamcommunity.com/id/evcentric        4   \n",
       "128997  evcentric  http://steamcommunity.com/id/evcentric        5   \n",
       "\n",
       "                                                  reviews  \n",
       "2       {'funny': '', 'posted': 'Posted February 3.', ...  \n",
       "25801   {'funny': '', 'posted': 'Posted December 4, 20...  \n",
       "51600   {'funny': '', 'posted': 'Posted November 3, 20...  \n",
       "77399   {'funny': '', 'posted': 'Posted October 15, 20...  \n",
       "103198  {'funny': '', 'posted': 'Posted October 15, 20...  \n",
       "128997  {'funny': '', 'posted': 'Posted October 15, 20...  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Se eliminan las filas con valor None\n",
    "review_norm = review_norm.dropna()\n",
    "# Se verifica que solo queden el 'user_id' con la cantidad de diccionarios que le corresponde\n",
    "review_norm[review_norm['user_id']=='evcentric']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>reviews_funny</th>\n",
       "      <th>reviews_posted</th>\n",
       "      <th>reviews_last_edited</th>\n",
       "      <th>reviews_item_id</th>\n",
       "      <th>reviews_helpful</th>\n",
       "      <th>reviews_recommend</th>\n",
       "      <th>reviews_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 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>2</th>\n",
       "      <td></td>\n",
       "      <td>Posted February 3.</td>\n",
       "      <td></td>\n",
       "      <td>248820</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>A suitably punishing roguelike platformer.  Wi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td></td>\n",
       "      <td>Posted October 14, 2013.</td>\n",
       "      <td></td>\n",
       "      <td>250320</td>\n",
       "      <td>2 of 2 people (100%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>This game... is so fun. The fight sequences ha...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3 people found this review funny</td>\n",
       "      <td>Posted April 15, 2014.</td>\n",
       "      <td></td>\n",
       "      <td>211420</td>\n",
       "      <td>35 of 43 people (81%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>Git gud</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      reviews_funny            reviews_posted  \\\n",
       "0                                    Posted November 5, 2011.   \n",
       "1                                       Posted June 24, 2014.   \n",
       "2                                          Posted February 3.   \n",
       "3                                    Posted October 14, 2013.   \n",
       "4  3 people found this review funny    Posted April 15, 2014.   \n",
       "\n",
       "  reviews_last_edited reviews_item_id  \\\n",
       "0                                1250   \n",
       "1                              251610   \n",
       "2                              248820   \n",
       "3                              250320   \n",
       "4                              211420   \n",
       "\n",
       "                                   reviews_helpful  reviews_recommend  \\\n",
       "0                                   No ratings yet               True   \n",
       "1  15 of 20 people (75%) found this review helpful               True   \n",
       "2                                   No ratings yet               True   \n",
       "3   2 of 2 people (100%) found this review helpful               True   \n",
       "4  35 of 43 people (81%) found this review helpful               True   \n",
       "\n",
       "                                      reviews_review  \n",
       "0  Simple yet with great replayability. In my opi...  \n",
       "1  I know what you think when you see this title ...  \n",
       "2  A suitably punishing roguelike platformer.  Wi...  \n",
       "3  This game... is so fun. The fight sequences ha...  \n",
       "4                                            Git gud  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Se separan por columnas cada una de las claves de 'reviews'\n",
    "reviews = review_norm['reviews'].apply(pd.Series, dtype='object')\n",
    "reviews =reviews.add_prefix('reviews_')\n",
    "reviews.head()"
   ]
  },
  {
   "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>reviews_funny</th>\n",
       "      <th>reviews_posted</th>\n",
       "      <th>reviews_last_edited</th>\n",
       "      <th>reviews_item_id</th>\n",
       "      <th>reviews_helpful</th>\n",
       "      <th>reviews_recommend</th>\n",
       "      <th>reviews_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>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>2</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>http://steamcommunity.com/id/evcentric</td>\n",
       "      <td></td>\n",
       "      <td>Posted February 3.</td>\n",
       "      <td></td>\n",
       "      <td>248820</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>A suitably punishing roguelike platformer.  Wi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>doctr</td>\n",
       "      <td>http://steamcommunity.com/id/doctr</td>\n",
       "      <td></td>\n",
       "      <td>Posted October 14, 2013.</td>\n",
       "      <td></td>\n",
       "      <td>250320</td>\n",
       "      <td>2 of 2 people (100%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>This game... is so fun. The fight sequences ha...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>maplemage</td>\n",
       "      <td>http://steamcommunity.com/id/maplemage</td>\n",
       "      <td>3 people found this review funny</td>\n",
       "      <td>Posted April 15, 2014.</td>\n",
       "      <td></td>\n",
       "      <td>211420</td>\n",
       "      <td>35 of 43 people (81%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>Git gud</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\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",
       "                      reviews_funny            reviews_posted  \\\n",
       "0                                    Posted November 5, 2011.   \n",
       "1                                       Posted June 24, 2014.   \n",
       "2                                          Posted February 3.   \n",
       "3                                    Posted October 14, 2013.   \n",
       "4  3 people found this review funny    Posted April 15, 2014.   \n",
       "\n",
       "  reviews_last_edited reviews_item_id  \\\n",
       "0                                1250   \n",
       "1                              251610   \n",
       "2                              248820   \n",
       "3                              250320   \n",
       "4                              211420   \n",
       "\n",
       "                                   reviews_helpful  reviews_recommend  \\\n",
       "0                                   No ratings yet               True   \n",
       "1  15 of 20 people (75%) found this review helpful               True   \n",
       "2                                   No ratings yet               True   \n",
       "3   2 of 2 people (100%) found this review helpful               True   \n",
       "4  35 of 43 people (81%) found this review helpful               True   \n",
       "\n",
       "                                      reviews_review  \n",
       "0  Simple yet with great replayability. In my opi...  \n",
       "1  I know what you think when you see this title ...  \n",
       "2  A suitably punishing roguelike platformer.  Wi...  \n",
       "3  This game... is so fun. The fight sequences ha...  \n",
       "4                                            Git gud  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Se une con el 'user_id' y 'user_url'\n",
    "reviews = pd.concat([review_norm[['user_id', 'user_url']], reviews], axis=1)\n",
    "reviews.head()"
   ]
  },
  {
   "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>reviews_funny</th>\n",
       "      <th>reviews_posted</th>\n",
       "      <th>reviews_last_edited</th>\n",
       "      <th>reviews_item_id</th>\n",
       "      <th>reviews_helpful</th>\n",
       "      <th>reviews_recommend</th>\n",
       "      <th>reviews_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>None</td>\n",
       "      <td>Posted November 5, 2011.</td>\n",
       "      <td>None</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>js41637</td>\n",
       "      <td>http://steamcommunity.com/id/js41637</td>\n",
       "      <td>None</td>\n",
       "      <td>Posted June 24, 2014.</td>\n",
       "      <td>None</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>2</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>http://steamcommunity.com/id/evcentric</td>\n",
       "      <td>None</td>\n",
       "      <td>Posted February 3.</td>\n",
       "      <td>None</td>\n",
       "      <td>248820</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>A suitably punishing roguelike platformer.  Wi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>doctr</td>\n",
       "      <td>http://steamcommunity.com/id/doctr</td>\n",
       "      <td>None</td>\n",
       "      <td>Posted October 14, 2013.</td>\n",
       "      <td>None</td>\n",
       "      <td>250320</td>\n",
       "      <td>2 of 2 people (100%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>This game... is so fun. The fight sequences ha...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>maplemage</td>\n",
       "      <td>http://steamcommunity.com/id/maplemage</td>\n",
       "      <td>3 people found this review funny</td>\n",
       "      <td>Posted April 15, 2014.</td>\n",
       "      <td>None</td>\n",
       "      <td>211420</td>\n",
       "      <td>35 of 43 people (81%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>Git gud</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\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",
       "                      reviews_funny            reviews_posted  \\\n",
       "0                              None  Posted November 5, 2011.   \n",
       "1                              None     Posted June 24, 2014.   \n",
       "2                              None        Posted February 3.   \n",
       "3                              None  Posted October 14, 2013.   \n",
       "4  3 people found this review funny    Posted April 15, 2014.   \n",
       "\n",
       "  reviews_last_edited reviews_item_id  \\\n",
       "0                None            1250   \n",
       "1                None          251610   \n",
       "2                None          248820   \n",
       "3                None          250320   \n",
       "4                None          211420   \n",
       "\n",
       "                                   reviews_helpful  reviews_recommend  \\\n",
       "0                                   No ratings yet               True   \n",
       "1  15 of 20 people (75%) found this review helpful               True   \n",
       "2                                   No ratings yet               True   \n",
       "3   2 of 2 people (100%) found this review helpful               True   \n",
       "4  35 of 43 people (81%) found this review helpful               True   \n",
       "\n",
       "                                      reviews_review  \n",
       "0  Simple yet with great replayability. In my opi...  \n",
       "1  I know what you think when you see this title ...  \n",
       "2  A suitably punishing roguelike platformer.  Wi...  \n",
       "3  This game... is so fun. The fight sequences ha...  \n",
       "4                                            Git gud  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reviews.replace('', None, inplace=True)\n",
    "# Mostrar las primeras filas del DataFrame\n",
    "reviews.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 57397 entries, 0 to 231501\n",
      "Data columns (total 9 columns):\n",
      " #   Column               Non-Null Count  Dtype \n",
      "---  ------               --------------  ----- \n",
      " 0   user_id              57397 non-null  object\n",
      " 1   user_url             57397 non-null  object\n",
      " 2   reviews_funny        7899 non-null   object\n",
      " 3   reviews_posted       57397 non-null  object\n",
      " 4   reviews_last_edited  5898 non-null   object\n",
      " 5   reviews_item_id      57397 non-null  object\n",
      " 6   reviews_helpful      57397 non-null  object\n",
      " 7   reviews_recommend    57397 non-null  bool  \n",
      " 8   reviews_review       57367 non-null  object\n",
      "dtypes: bool(1), object(8)\n",
      "memory usage: 4.0+ MB\n",
      "====================================================================================================\n",
      "reviews_last_edited    89.724202\n",
      "reviews_funny          86.237957\n",
      "reviews_review          0.052268\n",
      "user_id                 0.000000\n",
      "user_url                0.000000\n",
      "reviews_posted          0.000000\n",
      "reviews_item_id         0.000000\n",
      "reviews_helpful         0.000000\n",
      "reviews_recommend       0.000000\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "reviews.info()\n",
    "print('=' * 100)\n",
    "print(reviews.isna().sum().sort_values(ascending= False)/len(reviews) * 100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Se eliminan las columnas 'reviews_funny' y 'reviews_last_edited'\n",
    "reviews = reviews.drop(columns=['reviews_funny', 'reviews_last_edited'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['user_id', 'user_url', 'reviews_posted', 'reviews_item_id',\n",
       "       'reviews_helpful', 'reviews_recommend', 'reviews_review'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reviews.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Transformación de la columna 'reviews_posted'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se necesita que la fecha donde se hizo el posteo de la review este en formato `YYYY-MM-DD`, pero se encuentra como `Posted November 5, 2011.`. Por lo tanto, es necesario procesar la fecha y extraer los elementos relevantes. Se utilizará expresiones regulares para buscar y capturar los valores de año, mes y día dentro de la cadena de texto."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9771"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reviews['reviews_posted'] = reviews['reviews_posted'].str.extract(r'Posted ([\\w\\s\\d,]+)') # Utilizamos regex para extraer la fecha\n",
    "reviews['reviews_posted'] = reviews['reviews_posted'].replace({'Posted': ''}, regex=True) # Eliminamos la palabra Posted\n",
    "reviews['reviews_posted'] = pd.to_datetime(reviews['reviews_posted'], errors='coerce') # Convertimos la columna reviews_posted a datetime\n",
    "reviews[\"reviews_posted\"].isnull().sum() # Contamos los valores nulos de la columna reviews_posted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2015-12-31 00:00:00\n",
      "2010-10-16 00:00:00\n"
     ]
    }
   ],
   "source": [
    "print(reviews[\"reviews_posted\"].max()) # Buscamos la fecha maxima\n",
    "print(reviews[\"reviews_posted\"].min()) # Buscamos la fecha minima\n",
    "\n",
    "# Definimos un rango temporal adecuado para el conjunto de datos\n",
    "start_date = datetime(2010, 10, 16)\n",
    "end_date = datetime(2015, 12, 31)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Imputamos fechas aleatorias a las filas con valores nulos en 'posted'\n",
    "reviews.loc[reviews['reviews_posted'].isnull(), 'reviews_posted'] = np.random.choice(pd.date_range(start_date, end_date), reviews['reviews_posted'].isnull().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reviews[\"reviews_posted\"].isnull().sum() # Revisamos nuevamente la cantidad de valores nulos de la columna posted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_url</th>\n",
       "      <th>reviews_posted</th>\n",
       "      <th>reviews_item_id</th>\n",
       "      <th>reviews_helpful</th>\n",
       "      <th>reviews_recommend</th>\n",
       "      <th>reviews_review</th>\n",
       "      <th>posted_year</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>2011-11-05</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",
       "      <td>2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>js41637</td>\n",
       "      <td>http://steamcommunity.com/id/js41637</td>\n",
       "      <td>2014-06-24</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",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\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",
       "\n",
       "  reviews_posted reviews_item_id  \\\n",
       "0     2011-11-05            1250   \n",
       "1     2014-06-24          251610   \n",
       "\n",
       "                                   reviews_helpful  reviews_recommend  \\\n",
       "0                                   No ratings yet               True   \n",
       "1  15 of 20 people (75%) found this review helpful               True   \n",
       "\n",
       "                                      reviews_review  posted_year  \n",
       "0  Simple yet with great replayability. In my opi...         2011  \n",
       "1  I know what you think when you see this title ...         2014  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Extraemos el año de la columna \"release_date\" y creamos una nueva columna llamada \"release_year\"\n",
    "reviews[\"posted_year\"] = reviews[\"reviews_posted\"].dt.year \n",
    "reviews.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "reviews = reviews.drop('reviews_posted', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "reviews_review       0.052268\n",
       "user_id              0.000000\n",
       "user_url             0.000000\n",
       "reviews_item_id      0.000000\n",
       "reviews_helpful      0.000000\n",
       "reviews_recommend    0.000000\n",
       "posted_year          0.000000\n",
       "dtype: float64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reviews.isna().sum().sort_values(ascending= False)/len(reviews) * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "reviews = reviews.dropna(subset=['reviews_review'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "user_id              0.0\n",
       "user_url             0.0\n",
       "reviews_item_id      0.0\n",
       "reviews_helpful      0.0\n",
       "reviews_recommend    0.0\n",
       "reviews_review       0.0\n",
       "posted_year          0.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reviews.isna().sum().sort_values(ascending= False)/len(reviews) * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "reviews.to_csv(\"data/02-user-reviews.csv\", index=False, encoding='utf-8') # Exportamos el dataframe a csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "reviews = pd.read_csv(\"data/02-user-reviews.csv\") # leemos el archivo csv\n",
    "\n",
    "tabla = pa.Table.from_pandas(reviews) # convertimos el dataframe en una tabla\n",
    "pq.write_table(tabla,\"data/02-user-reviews.parquet\") # guardamos la tabla en un archivo parquet"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "env",
   "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.12.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}