JupyterNotebooks/01c_ETL_user_reviews.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a target=\"_blank\" href=\"https://colab.research.google.com/github/IngCarlaPezzone/PI1_MLOps_videojuegos/blob/main/JupyterNotebooks/01c_ETL_user_reviews.ipynb\">\n",
    "  <img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/>\n",
    "</a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ETL del dataset  `australian_user_reviews`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "En esta jupyter notebook se realiza la extracción, transformación y carga del conjunto de datos `australian_user_reviews`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importaciones"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The autoreload extension is already loaded. To reload it, use:\n",
      "  %reload_ext autoreload\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import ast\n",
    "\n",
    "%load_ext autoreload\n",
    "%autoreload 2\n",
    "import utils\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extracción, primera exploración y eliminación de duplicados"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se lee el dataset que contiene las reviews que hacen los usuarios y se guarda en un dataframe."
   ]
  },
  {
   "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>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": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Ruta al dataset australian_user_reviews\n",
    "ruta_review = 'data/australian_user_reviews.json'\n",
    "\n",
    "# Se lee de cada línea del dataset\n",
    "filas_review = []\n",
    "with open(ruta_review) as f:\n",
    "    for line in f.readlines():\n",
    "        filas_review.append(ast.literal_eval(line))\n",
    "\n",
    "# Se convierte en dataframe\n",
    "df_reviews = pd.DataFrame(filas_review)\n",
    "df_reviews"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se verifican los tipos de datos para cada columna y la cantidad de nulos."
   ]
  },
  {
   "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>nombre_campo</th>\n",
       "      <th>tipo_datos</th>\n",
       "      <th>no_nulos_%</th>\n",
       "      <th>nulos_%</th>\n",
       "      <th>nulos</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",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>user_url</td>\n",
       "      <td>[&lt;class 'str'&gt;]</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>reviews</td>\n",
       "      <td>[&lt;class 'list'&gt;]</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  nombre_campo        tipo_datos  no_nulos_%  nulos_%  nulos\n",
       "0      user_id   [<class 'str'>]       100.0      0.0      0\n",
       "1     user_url   [<class 'str'>]       100.0      0.0      0\n",
       "2      reviews  [<class 'list'>]       100.0      0.0      0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "utils.verificar_tipo_datos(df_reviews)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se verifican si hay o no duplicados por cada 'user_id'."
   ]
  },
  {
   "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>reviews</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12888</th>\n",
       "      <td>05041129</td>\n",
       "      <td>http://steamcommunity.com/id/05041129</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted May 18, 2015....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5250</th>\n",
       "      <td>05041129</td>\n",
       "      <td>http://steamcommunity.com/id/05041129</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted May 18, 2015....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3133</th>\n",
       "      <td>111222333444555666888</td>\n",
       "      <td>http://steamcommunity.com/id/11122233344455566...</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted December 22, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3134</th>\n",
       "      <td>111222333444555666888</td>\n",
       "      <td>http://steamcommunity.com/id/11122233344455566...</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted December 22, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4139</th>\n",
       "      <td>29123</td>\n",
       "      <td>http://steamcommunity.com/id/29123</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted March 26.', '...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2721</th>\n",
       "      <td>xXAussieRockXx</td>\n",
       "      <td>http://steamcommunity.com/id/xXAussieRockXx</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted July 17, 2015...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2680</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>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>5855</th>\n",
       "      <td>zeroblade</td>\n",
       "      <td>http://steamcommunity.com/id/zeroblade</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted November 30, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13975</th>\n",
       "      <td>zeroblade</td>\n",
       "      <td>http://steamcommunity.com/id/zeroblade</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted November 30, ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>623 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     user_id  \\\n",
       "12888               05041129   \n",
       "5250                05041129   \n",
       "3133   111222333444555666888   \n",
       "3134   111222333444555666888   \n",
       "4139                   29123   \n",
       "...                      ...   \n",
       "2721          xXAussieRockXx   \n",
       "2680             yolofaceguy   \n",
       "17916            yolofaceguy   \n",
       "5855               zeroblade   \n",
       "13975              zeroblade   \n",
       "\n",
       "                                                user_url  \\\n",
       "12888              http://steamcommunity.com/id/05041129   \n",
       "5250               http://steamcommunity.com/id/05041129   \n",
       "3133   http://steamcommunity.com/id/11122233344455566...   \n",
       "3134   http://steamcommunity.com/id/11122233344455566...   \n",
       "4139                  http://steamcommunity.com/id/29123   \n",
       "...                                                  ...   \n",
       "2721         http://steamcommunity.com/id/xXAussieRockXx   \n",
       "2680            http://steamcommunity.com/id/yolofaceguy   \n",
       "17916           http://steamcommunity.com/id/yolofaceguy   \n",
       "5855              http://steamcommunity.com/id/zeroblade   \n",
       "13975             http://steamcommunity.com/id/zeroblade   \n",
       "\n",
       "                                                 reviews  \n",
       "12888  [{'funny': '', 'posted': 'Posted May 18, 2015....  \n",
       "5250   [{'funny': '', 'posted': 'Posted May 18, 2015....  \n",
       "3133   [{'funny': '', 'posted': 'Posted December 22, ...  \n",
       "3134   [{'funny': '', 'posted': 'Posted December 22, ...  \n",
       "4139   [{'funny': '', 'posted': 'Posted March 26.', '...  \n",
       "...                                                  ...  \n",
       "2721   [{'funny': '', 'posted': 'Posted July 17, 2015...  \n",
       "2680   [{'funny': '', 'posted': 'Posted October 31, 2...  \n",
       "17916  [{'funny': '', 'posted': 'Posted October 31, 2...  \n",
       "5855   [{'funny': '', 'posted': 'Posted November 30, ...  \n",
       "13975  [{'funny': '', 'posted': 'Posted November 30, ...  \n",
       "\n",
       "[623 rows x 3 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filas_duplicadas = utils.verifica_duplicados_por_columna(df_reviews, 'user_id')\n",
    "filas_duplicadas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se observan 623 filas duplicadas en la columna 'ser_id', pero se revisan si los review dentro de los datos anidados de 'review' la información se encuentra duplicada o si solo se duplica el 'user_id' porque hay mas de un comentario realizado por ese usuario."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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"
     ]
    }
   ],
   "source": [
    "# Se revisa un usuario de ejemplo\n",
    "user_id = '05041129'\n",
    "user_reviews = filas_duplicadas[filas_duplicadas['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('-' * 40)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se puede ver que los review son exactamente los mismos para cada registro, por lo que se decide borrar los duplicados, dejando la primer ocurrencia de los registros."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'No hay duplicados'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reviews = df_reviews.drop_duplicates(subset='user_id', keep='first')\n",
    "utils.verifica_duplicados_por_columna(df_reviews, 'user_id')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se revisa la columna 'review' para entender el tipo de dato."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Se observa el tipo de dato que contiene 'review'\n",
    "df_reviews['reviews'][0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Este conjunto contiene 3 columnas y 25799 filas, sin valores nulos. Las columnas son:\n",
    "\n",
    "* **user_id**: es un identificador único para el usuario.\n",
    "* **user_url**: es la url del perfil del usuario en streamcommunity.\n",
    "* **reviews**: contiene una lista de diccionarios. Para cada usuario se tiene uno o mas diccionario con el review. Cada diccionario contiene:\n",
    "    * **funny**: indica si alguien puso emoticón de gracioso al review.\n",
    "    * **posted**: es la fecha de posteo del review en formato Posted April 21, 2011.\n",
    "    * **last_edited**: es la fecha de la última edición.\n",
    "    * **item_id**: es el identificador único del item, es decir, del juego.\n",
    "    * **helpful**: es la estadística donde otros usuarios indican si fue útil la información.\n",
    "    * **recommend**: es un booleano que indica si el usuario recomienda o no el juego.\n",
    "    * **review**: es una sentencia string con los comentarios sobre el juego."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transformación de la columna 'reviews'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "La columna 'reviews' se presenta anidada, siendo una lista con uno o mas diccionarios como elementos. Se busca generar una columna por cada diccionario para posteriormente hacer un registro por cada diccionario."
   ]
  },
  {
   "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>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": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Se transforma a columnas cada elemento de las listas\n",
    "df_reviews2 = pd.json_normalize(df_reviews['reviews'])\n",
    "df_reviews2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "En esta transformación, se pierde el 'user_id' y 'user_url' al que pertenece cada diccionario, pero aún mantiene la misma posición. Para volver a tener la trazabilidad del usuario, se concatena con el dataframe anterior."
   ]
  },
  {
   "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>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": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Se agrega el 'user_id' y 'user_url' a las columnas separadas \n",
    "df_reviews2 = pd.concat([df_reviews[['user_id', 'user_url']], df_reviews2], axis=1)\n",
    "df_reviews2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ahora que se tienen los diccionarios por columnas, con el usuario que genera dicha información, se genera un registro por cada diccionario, manteniendo en cada caso el usuario que lo genera."
   ]
  },
  {
   "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>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": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Se utiliza pd.melt para transformar las columnas en filas conservando el 'user_id' y 'user_url'\n",
    "df_reviews2 = pd.melt(df_reviews2, id_vars=['user_id', 'user_url'], \n",
    "                       value_vars=list(range(9)),\n",
    "                       value_name='reviews')\n",
    "df_reviews2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Al hacer esto último se puede ver que quedan registros None. Esto ocurre porque hay usuarios que hicieron mas reviews que otros. En este ejemplo se puede ver este caso:"
   ]
  },
  {
   "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>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>25799</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td>1</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted July 15, 2011....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51598</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td>2</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted April 21, 2011...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77397</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103196</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128995</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td>5</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154794</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180593</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td>7</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206392</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td>8</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",
       "25799   76561197970982479  http://steamcommunity.com/profiles/76561197970...   \n",
       "51598   76561197970982479  http://steamcommunity.com/profiles/76561197970...   \n",
       "77397   76561197970982479  http://steamcommunity.com/profiles/76561197970...   \n",
       "103196  76561197970982479  http://steamcommunity.com/profiles/76561197970...   \n",
       "128995  76561197970982479  http://steamcommunity.com/profiles/76561197970...   \n",
       "154794  76561197970982479  http://steamcommunity.com/profiles/76561197970...   \n",
       "180593  76561197970982479  http://steamcommunity.com/profiles/76561197970...   \n",
       "206392  76561197970982479  http://steamcommunity.com/profiles/76561197970...   \n",
       "\n",
       "       variable                                            reviews  \n",
       "0             0  {'funny': '', 'posted': 'Posted November 5, 20...  \n",
       "25799         1  {'funny': '', 'posted': 'Posted July 15, 2011....  \n",
       "51598         2  {'funny': '', 'posted': 'Posted April 21, 2011...  \n",
       "77397         3                                               None  \n",
       "103196        4                                               None  \n",
       "128995        5                                               None  \n",
       "154794        6                                               None  \n",
       "180593        7                                               None  \n",
       "206392        8                                               None  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reviews2[df_reviews2['user_id']=='76561197970982479']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se eliminan los registros que tienen None en 'reviews'."
   ]
  },
  {
   "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>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>25799</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td>1</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted July 15, 2011....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51598</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td>2</td>\n",
       "      <td>{'funny': '', 'posted': 'Posted April 21, 2011...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 user_id                                           user_url  \\\n",
       "0      76561197970982479  http://steamcommunity.com/profiles/76561197970...   \n",
       "25799  76561197970982479  http://steamcommunity.com/profiles/76561197970...   \n",
       "51598  76561197970982479  http://steamcommunity.com/profiles/76561197970...   \n",
       "\n",
       "      variable                                            reviews  \n",
       "0            0  {'funny': '', 'posted': 'Posted November 5, 20...  \n",
       "25799        1  {'funny': '', 'posted': 'Posted July 15, 2011....  \n",
       "51598        2  {'funny': '', 'posted': 'Posted April 21, 2011...  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Se eliminan las filas con valor None\n",
    "df_reviews2 = df_reviews2.dropna()\n",
    "# Se verifica que solo queden el 'user_id' con la cantidad de diccionarios que le corresponde\n",
    "df_reviews2[df_reviews2['user_id']=='76561197970982479']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "En este punto ya es posible convertir cada diccionario en columna."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Se separan por columnas cada una de las claves de 'reviews'\n",
    "df_reviews = df_reviews2['reviews'].apply(pd.Series, dtype='object')\n",
    "df_reviews = df_reviews.add_prefix('reviews_')\n",
    "df_reviews.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "En el procesamiento anterior, se puede ver que la columna de 'user_id' y 'user_url' se perdió nuevamente, por lo que se vuelve a concatenar."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Se une con el 'user_id' y 'user_url'\n",
    "df_reviews = pd.concat([df_reviews2[['user_id', 'user_url']], df_reviews], axis=1)\n",
    "df_reviews.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se observa que hay valores faltantes en algunas columnas, pero no estan como nulos, probablemente deben tener un espacio. Se compueba esto."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "''"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reviews['reviews_last_edited'][0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se reemplazar esos espacios como valores nulos."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reviews.replace('', None, inplace=True)\n",
    "df_reviews.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se analizan los tipos de datos y los nulos que quedaron luego de desanidar la columna 'reviews'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>nombre_campo</th>\n",
       "      <th>tipo_datos</th>\n",
       "      <th>no_nulos_%</th>\n",
       "      <th>nulos_%</th>\n",
       "      <th>nulos</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",
       "      <td>100.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>user_url</td>\n",
       "      <td>[&lt;class 'str'&gt;]</td>\n",
       "      <td>100.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>reviews_funny</td>\n",
       "      <td>[&lt;class 'NoneType'&gt;, &lt;class 'str'&gt;]</td>\n",
       "      <td>13.76</td>\n",
       "      <td>86.24</td>\n",
       "      <td>49498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>reviews_posted</td>\n",
       "      <td>[&lt;class 'str'&gt;]</td>\n",
       "      <td>100.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>reviews_last_edited</td>\n",
       "      <td>[&lt;class 'NoneType'&gt;, &lt;class 'str'&gt;]</td>\n",
       "      <td>10.28</td>\n",
       "      <td>89.72</td>\n",
       "      <td>51499</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>reviews_item_id</td>\n",
       "      <td>[&lt;class 'str'&gt;]</td>\n",
       "      <td>100.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>reviews_helpful</td>\n",
       "      <td>[&lt;class 'str'&gt;]</td>\n",
       "      <td>100.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>reviews_recommend</td>\n",
       "      <td>[&lt;class 'bool'&gt;]</td>\n",
       "      <td>100.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>reviews_review</td>\n",
       "      <td>[&lt;class 'str'&gt;, &lt;class 'NoneType'&gt;]</td>\n",
       "      <td>99.95</td>\n",
       "      <td>0.05</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          nombre_campo                           tipo_datos  no_nulos_%  \\\n",
       "0              user_id                      [<class 'str'>]      100.00   \n",
       "1             user_url                      [<class 'str'>]      100.00   \n",
       "2        reviews_funny  [<class 'NoneType'>, <class 'str'>]       13.76   \n",
       "3       reviews_posted                      [<class 'str'>]      100.00   \n",
       "4  reviews_last_edited  [<class 'NoneType'>, <class 'str'>]       10.28   \n",
       "5      reviews_item_id                      [<class 'str'>]      100.00   \n",
       "6      reviews_helpful                      [<class 'str'>]      100.00   \n",
       "7    reviews_recommend                     [<class 'bool'>]      100.00   \n",
       "8       reviews_review  [<class 'str'>, <class 'NoneType'>]       99.95   \n",
       "\n",
       "   nulos_%  nulos  \n",
       "0     0.00      0  \n",
       "1     0.00      0  \n",
       "2    86.24  49498  \n",
       "3     0.00      0  \n",
       "4    89.72  51499  \n",
       "5     0.00      0  \n",
       "6     0.00      0  \n",
       "7     0.00      0  \n",
       "8     0.05     30  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "utils.verificar_tipo_datos(df_reviews)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se observa entre un 86 a 89% de faltantes de datos en las columnas 'reviews_funny' y 'reviews_last_edited' por lo que se decide eliminar estas columnas. Por otra parte hay un 5% de faltantes de datos en la columna propiamente de reviews, pero no se eliminarán esos registros porque se considerarán como un comentario neutral."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Se eliminan las columnas 'reviews_funny' y 'reviews_last_edited'\n",
    "df_reviews = df_reviews.drop(columns=['reviews_funny', 'reviews_last_edited'])\n",
    "df_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": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0               2011-11-05\n",
       "1               2014-06-24\n",
       "2         Formato inválido\n",
       "3               2013-10-14\n",
       "4               2014-04-15\n",
       "                ...       \n",
       "231291          2014-08-15\n",
       "231293          2014-08-02\n",
       "231419          2015-07-31\n",
       "231499          2015-12-20\n",
       "231501    Formato inválido\n",
       "Name: reviews_date, Length: 57397, dtype: object"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reviews['reviews_date'] = df_reviews['reviews_posted'].apply(utils.convertir_fecha)\n",
    "df_reviews['reviews_date']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se puede observar que hay 9771 registros que contienen un formato inválido distinto a los demas registros. En este caso, no contiene el año del posteo, pero con la función se imputó como 'Formato inválido'. Estos registros no se podrán consultar desde la API, pero las demás columnas serán útiles para aportar información."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>reviews_date</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>Posted February 3.</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",
       "      <td>Formato inválido</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>76561198079601835</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198079...</td>\n",
       "      <td>Posted May 20.</td>\n",
       "      <td>730</td>\n",
       "      <td>0 of 1 people (0%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>ZIKA DO BAILE</td>\n",
       "      <td>Formato inválido</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>MeaTCompany</td>\n",
       "      <td>http://steamcommunity.com/id/MeaTCompany</td>\n",
       "      <td>Posted July 24.</td>\n",
       "      <td>730</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>BEST GAME IN THE BLOODY WORLD</td>\n",
       "      <td>Formato inválido</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>76561198156664158</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198156...</td>\n",
       "      <td>Posted June 16.</td>\n",
       "      <td>252950</td>\n",
       "      <td>0 of 1 people (0%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>love it</td>\n",
       "      <td>Formato inválido</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>76561198077246154</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198077...</td>\n",
       "      <td>Posted June 11.</td>\n",
       "      <td>440</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>mt bom</td>\n",
       "      <td>Formato inválido</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>223569</th>\n",
       "      <td>76561198040184950</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198040...</td>\n",
       "      <td>Posted April 12.</td>\n",
       "      <td>394690</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>I cannot say much right now due to the game no...</td>\n",
       "      <td>Formato inválido</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226105</th>\n",
       "      <td>76561198046474248</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198046...</td>\n",
       "      <td>Posted March 28.</td>\n",
       "      <td>234140</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Oh what a day .., What a lovely day to play th...</td>\n",
       "      <td>Formato inválido</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228109</th>\n",
       "      <td>dmitry_who</td>\n",
       "      <td>http://steamcommunity.com/id/dmitry_who</td>\n",
       "      <td>Posted May 17.</td>\n",
       "      <td>376210</td>\n",
       "      <td>10 of 28 people (36%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>░░░░░░░░░░░█▀▀░░█░░░░░░░░░░░▄▀▀▀▀░░░░░█▄▄░░░░░...</td>\n",
       "      <td>Formato inválido</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>229231</th>\n",
       "      <td>76561198079507136</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198079...</td>\n",
       "      <td>Posted January 3.</td>\n",
       "      <td>730</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>False</td>\n",
       "      <td>got VACed</td>\n",
       "      <td>Formato inválido</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>231501</th>\n",
       "      <td>EchoXSilence</td>\n",
       "      <td>http://steamcommunity.com/id/EchoXSilence</td>\n",
       "      <td>Posted February 3.</td>\n",
       "      <td>570</td>\n",
       "      <td>0 of 2 people (0%) found this review helpful</td>\n",
       "      <td>False</td>\n",
       "      <td>DOTA 2.normal skill - 2ez for me&gt;high skill - ...</td>\n",
       "      <td>Formato inválido</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9771 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  user_id                                           user_url  \\\n",
       "2               evcentric             http://steamcommunity.com/id/evcentric   \n",
       "6       76561198079601835  http://steamcommunity.com/profiles/76561198079...   \n",
       "7             MeaTCompany           http://steamcommunity.com/id/MeaTCompany   \n",
       "9       76561198156664158  http://steamcommunity.com/profiles/76561198156...   \n",
       "10      76561198077246154  http://steamcommunity.com/profiles/76561198077...   \n",
       "...                   ...                                                ...   \n",
       "223569  76561198040184950  http://steamcommunity.com/profiles/76561198040...   \n",
       "226105  76561198046474248  http://steamcommunity.com/profiles/76561198046...   \n",
       "228109         dmitry_who            http://steamcommunity.com/id/dmitry_who   \n",
       "229231  76561198079507136  http://steamcommunity.com/profiles/76561198079...   \n",
       "231501       EchoXSilence          http://steamcommunity.com/id/EchoXSilence   \n",
       "\n",
       "            reviews_posted reviews_item_id  \\\n",
       "2       Posted February 3.          248820   \n",
       "6           Posted May 20.             730   \n",
       "7          Posted July 24.             730   \n",
       "9          Posted June 16.          252950   \n",
       "10         Posted June 11.             440   \n",
       "...                    ...             ...   \n",
       "223569    Posted April 12.          394690   \n",
       "226105    Posted March 28.          234140   \n",
       "228109      Posted May 17.          376210   \n",
       "229231   Posted January 3.             730   \n",
       "231501  Posted February 3.             570   \n",
       "\n",
       "                                        reviews_helpful  reviews_recommend  \\\n",
       "2                                        No ratings yet               True   \n",
       "6          0 of 1 people (0%) found this review helpful               True   \n",
       "7                                        No ratings yet               True   \n",
       "9          0 of 1 people (0%) found this review helpful               True   \n",
       "10                                       No ratings yet               True   \n",
       "...                                                 ...                ...   \n",
       "223569                                   No ratings yet               True   \n",
       "226105                                   No ratings yet               True   \n",
       "228109  10 of 28 people (36%) found this review helpful               True   \n",
       "229231                                   No ratings yet              False   \n",
       "231501     0 of 2 people (0%) found this review helpful              False   \n",
       "\n",
       "                                           reviews_review      reviews_date  \n",
       "2       A suitably punishing roguelike platformer.  Wi...  Formato inválido  \n",
       "6                                           ZIKA DO BAILE  Formato inválido  \n",
       "7                           BEST GAME IN THE BLOODY WORLD  Formato inválido  \n",
       "9                                                 love it  Formato inválido  \n",
       "10                                                 mt bom  Formato inválido  \n",
       "...                                                   ...               ...  \n",
       "223569  I cannot say much right now due to the game no...  Formato inválido  \n",
       "226105  Oh what a day .., What a lovely day to play th...  Formato inválido  \n",
       "228109  ░░░░░░░░░░░█▀▀░░█░░░░░░░░░░░▄▀▀▀▀░░░░░█▄▄░░░░░...  Formato inválido  \n",
       "229231                                          got VACed  Formato inválido  \n",
       "231501  DOTA 2.normal skill - 2ez for me>high skill - ...  Formato inválido  \n",
       "\n",
       "[9771 rows x 8 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reviews[df_reviews['reviews_date'] == 'Formato inválido']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se decide elimina la columna 'reviews_posted' porque no aportan mayor información, ya que solo indica el día y el mes del posteo."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['user_id', 'user_url', 'reviews_item_id', 'reviews_helpful',\n",
       "       'reviews_recommend', 'reviews_review', 'reviews_date'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reviews = df_reviews.drop('reviews_posted', axis=1)\n",
    "df_reviews.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Columna 'reviews_review'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Esta columna tiene un 5% de valores nulos, por lo que se eliminan."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>nombre_campo</th>\n",
       "      <th>tipo_datos</th>\n",
       "      <th>no_nulos_%</th>\n",
       "      <th>nulos_%</th>\n",
       "      <th>nulos</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",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>user_url</td>\n",
       "      <td>[&lt;class 'str'&gt;]</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>reviews_item_id</td>\n",
       "      <td>[&lt;class 'str'&gt;]</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>reviews_helpful</td>\n",
       "      <td>[&lt;class 'str'&gt;]</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>reviews_recommend</td>\n",
       "      <td>[&lt;class 'bool'&gt;]</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>reviews_review</td>\n",
       "      <td>[&lt;class 'str'&gt;]</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>reviews_date</td>\n",
       "      <td>[&lt;class 'str'&gt;]</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        nombre_campo        tipo_datos  no_nulos_%  nulos_%  nulos\n",
       "0            user_id   [<class 'str'>]       100.0      0.0      0\n",
       "1           user_url   [<class 'str'>]       100.0      0.0      0\n",
       "2    reviews_item_id   [<class 'str'>]       100.0      0.0      0\n",
       "3    reviews_helpful   [<class 'str'>]       100.0      0.0      0\n",
       "4  reviews_recommend  [<class 'bool'>]       100.0      0.0      0\n",
       "5     reviews_review   [<class 'str'>]       100.0      0.0      0\n",
       "6       reviews_date   [<class 'str'>]       100.0      0.0      0"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reviews = df_reviews.dropna(subset=['reviews_review'])\n",
    "# Se verifican nulos\n",
    "utils.verificar_tipo_datos(df_reviews)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Carga del dataset `australian_user_reviews`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se guarda el conjunto de datos transformado como `user_review_limpio`. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Se guardó el archivo data/user_review_limpio.csv\n"
     ]
    }
   ],
   "source": [
    "archivo_limpio = 'data/user_review_limpio.csv'\n",
    "df_reviews.to_csv(archivo_limpio, index=False, encoding='utf-8')\n",
    "print(f'Se guardó el archivo {archivo_limpio}')"
   ]
  }
 ],
 "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.10.12"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}