ETL_user_reviews.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ETL Users Reviews"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import os\n",
    "import pandas as pd\n",
    "import gzip\n",
    "import ast\n",
    "import numpy as np\n",
    "import re\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cuando intenté cargar el archivo en un DataFrame, me encontré con el error:\n",
    "\n",
    "**JSONDecodeError**: Se esperaba un nombre de propiedad encerrado entre comillas dobles: línea 1 columna 3 (carácter 2)\n",
    "\n",
    "Después de una extensa búsqueda e investigación en Stack Overflow, me encontré con un artículo que fue un avance:\n",
    "\n",
    "[Convert JSON to pd.DataFrame](https://stackoverflow.com/questions/55338899/convert-json-to-pd-dataframe/65427497#65427497)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_url</th>\n",
       "      <th>reviews</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted November 5, 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",
       "  </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  \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',...  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows = []\n",
    "\n",
    "# Open the gzip-compressed JSON file\n",
    "with gzip.open(r'C:\\Users\\flore\\OneDrive\\Escritorio\\Etapa Labs\\MLOPs\\01. PI MLOps - STEAM\\user_reviews.json.gz', 'rb') as f:\n",
    "    # Iterate over each line in the file\n",
    "    for line in f.readlines():\n",
    "        # Decode the line and evaluate it as a Python literal\n",
    "        rows.append(ast.literal_eval(line.decode('utf-8')))\n",
    "        \n",
    "# Convert the list of dictionaries into a DataFrame\n",
    "df_reviews = pd.DataFrame(rows)\n",
    "\n",
    "# Display the first few rows of the DataFrame\n",
    "df_reviews.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ahora podemos mostrar los datos dentro del archivo y vemos que tenemos 3 columnas y la última columna contiene listas anidadas de diccionarios."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['user_id', 'user_url', 'reviews'], dtype='object')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reviews.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(25799, 3)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reviews.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 25799 entries, 0 to 25798\n",
      "Data columns (total 3 columns):\n",
      " #   Column    Non-Null Count  Dtype \n",
      "---  ------    --------------  ----- \n",
      " 0   user_id   25799 non-null  object\n",
      " 1   user_url  25799 non-null  object\n",
      " 2   reviews   25799 non-null  object\n",
      "dtypes: object(3)\n",
      "memory usage: 604.8+ KB\n"
     ]
    }
   ],
   "source": [
    "df_reviews.info()"
   ]
  },
  {
   "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>user_id</th>\n",
       "      <th>user_url</th>\n",
       "      <th>reviews</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>76561198156664158</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198156...</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted June 16.', 'l...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Rivtex</td>\n",
       "      <td>http://steamcommunity.com/id/Rivtex</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted December 23, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>76561198094224872</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198094...</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>DieMadchenschanderin</td>\n",
       "      <td>http://steamcommunity.com/id/DieMadchenschanderin</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted August 29, 20...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>relesprit</td>\n",
       "      <td>http://steamcommunity.com/id/relesprit</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted December 27, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17819</th>\n",
       "      <td>76561198076474887</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198076...</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted April 12.', '...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17916</th>\n",
       "      <td>yolofaceguy</td>\n",
       "      <td>http://steamcommunity.com/id/yolofaceguy</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted October 31, 2...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18028</th>\n",
       "      <td>76561198075591109</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198075...</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted December 26, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18234</th>\n",
       "      <td>76561198092022514</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198092...</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted July 3.', 'la...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18309</th>\n",
       "      <td>SuchGayMuchWow</td>\n",
       "      <td>http://steamcommunity.com/id/SuchGayMuchWow</td>\n",
       "      <td>[{'funny': '', 'posted': 'Posted July 18, 2015...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>623 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    user_id  \\\n",
       "9         76561198156664158   \n",
       "50                   Rivtex   \n",
       "83        76561198094224872   \n",
       "119    DieMadchenschanderin   \n",
       "147               relesprit   \n",
       "...                     ...   \n",
       "17819     76561198076474887   \n",
       "17916           yolofaceguy   \n",
       "18028     76561198075591109   \n",
       "18234     76561198092022514   \n",
       "18309        SuchGayMuchWow   \n",
       "\n",
       "                                                user_url  \\\n",
       "9      http://steamcommunity.com/profiles/76561198156...   \n",
       "50                   http://steamcommunity.com/id/Rivtex   \n",
       "83     http://steamcommunity.com/profiles/76561198094...   \n",
       "119    http://steamcommunity.com/id/DieMadchenschanderin   \n",
       "147               http://steamcommunity.com/id/relesprit   \n",
       "...                                                  ...   \n",
       "17819  http://steamcommunity.com/profiles/76561198076...   \n",
       "17916           http://steamcommunity.com/id/yolofaceguy   \n",
       "18028  http://steamcommunity.com/profiles/76561198075...   \n",
       "18234  http://steamcommunity.com/profiles/76561198092...   \n",
       "18309        http://steamcommunity.com/id/SuchGayMuchWow   \n",
       "\n",
       "                                                 reviews  \n",
       "9      [{'funny': '', 'posted': 'Posted June 16.', 'l...  \n",
       "50     [{'funny': '', 'posted': 'Posted December 23, ...  \n",
       "83                                                    []  \n",
       "119    [{'funny': '', 'posted': 'Posted August 29, 20...  \n",
       "147    [{'funny': '', 'posted': 'Posted December 27, ...  \n",
       "...                                                  ...  \n",
       "17819  [{'funny': '', 'posted': 'Posted April 12.', '...  \n",
       "17916  [{'funny': '', 'posted': 'Posted October 31, 2...  \n",
       "18028  [{'funny': '', 'posted': 'Posted December 26, ...  \n",
       "18234  [{'funny': '', 'posted': 'Posted July 3.', 'la...  \n",
       "18309  [{'funny': '', 'posted': 'Posted July 18, 2015...  \n",
       "\n",
       "[623 rows x 3 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Chequaemos por duplicados en base a 'user_id' y 'user_url' \n",
    "\n",
    "duplicated_rows = df_reviews[df_reviews.duplicated(subset=['user_id', 'user_url'], keep=False)]\n",
    "duplicated_rows"
   ]
  },
  {
   "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>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>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>4138</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",
       "3134   111222333444555666888   \n",
       "3133   111222333444555666888   \n",
       "4138                   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",
       "3134   http://steamcommunity.com/id/11122233344455566...   \n",
       "3133   http://steamcommunity.com/id/11122233344455566...   \n",
       "4138                  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",
       "3134   [{'funny': '', 'posted': 'Posted December 22, ...  \n",
       "3133   [{'funny': '', 'posted': 'Posted December 22, ...  \n",
       "4138   [{'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": [
    "# Ordenamos el DataFrame por 'user_id'\n",
    "df_sorted = df_reviews.sort_values(by='user_id')\n",
    "\n",
    "# Buscamos duplicados en las filas basados en las  columnas'user_id' y 'user_url'\n",
    "duplicated_rows = df_sorted[df_sorted.duplicated(subset=['user_id', 'user_url'], keep=False)]\n",
    "duplicated_rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Al comparar uno contra el otro, podemos validar que los registros están duplicados. Por lo tanto, eliminamos el valor duplicado."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_url</th>\n",
       "      <th>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>25485 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",
       "[25485 rows x 3 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dropeamos los  duplicados basados en las  columnas 'user_id' y  'user_url',nos quedamos con la primer ocurrencia del registro\n",
    "df_reviews = df_reviews.drop_duplicates(subset=['user_id', 'user_url'], keep='first')\n",
    "df_reviews"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ahora podemos proceder a investigar nuestra columna de revisiones.\n",
    "Esta columna contiene la información de todas las revisiones publicadas por el usuario identificado con el ID de usuario único.\n",
    "\n",
    "### Mostramos un ejemplo de fila para la tercera columna para comprender qué información es valiosa y cómo proceder para extraerla. Esta información es la entrada de nuestro análisis de sentimientos, por lo que es crucial."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'funny': '',\n",
       "  'posted': 'Posted June 24, 2014.',\n",
       "  'last_edited': '',\n",
       "  'item_id': '251610',\n",
       "  'helpful': '15 of 20 people (75%) found this review helpful',\n",
       "  'recommend': True,\n",
       "  'review': 'I know what you think when you see this title \"Barbie Dreamhouse Party\" but do not be intimidated by it\\'s title, this is easily one of my GOTYs. You don\\'t get any of that cliche game mechanics that all the latest games have, this is simply good core gameplay. Yes, you can\\'t 360 noscope your friends, but what you can do is show them up with your bad ♥♥♥ dance moves and put them to shame as you show them what true fashion and color combinations are.I know this game says for kids but, this is easily for any age range and any age will have a blast playing this.8/8'},\n",
       " {'funny': '',\n",
       "  'posted': 'Posted September 8, 2013.',\n",
       "  'last_edited': '',\n",
       "  'item_id': '227300',\n",
       "  'helpful': '0 of 1 people (0%) found this review helpful',\n",
       "  'recommend': True,\n",
       "  'review': \"For a simple (it's actually not all that simple but it can be!) truck driving Simulator, it is quite a fun and relaxing game. Playing on simple (or easy?) its just the basic WASD keys for driving but (if you want) the game can be much harder and realistic with having to manually change gears, much harder turning, etc. And reversing in this game is a ♥♥♥♥♥, as I imagine it would be with an actual truck. Luckily, you don't have to reverse park it but you get extra points if you do cause it is bloody hard. But this is suprisingly a nice truck driving game and I had a bit of fun with it.\"},\n",
       " {'funny': '',\n",
       "  'posted': 'Posted November 29, 2013.',\n",
       "  'last_edited': '',\n",
       "  'item_id': '239030',\n",
       "  'helpful': '1 of 4 people (25%) found this review helpful',\n",
       "  'recommend': True,\n",
       "  'review': 'Very fun little game to play when your bored or as a time passer. Very gud. Do Recommend. pls buy'}]"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reviews.iloc[1,2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'funny': '',\n",
       "  'posted': 'Posted October 13, 2014.',\n",
       "  'last_edited': '',\n",
       "  'item_id': '209870',\n",
       "  'helpful': '3 of 8 people (38%) found this review helpful',\n",
       "  'recommend': True,\n",
       "  'review': 'Its a very fun game i recomend as its nearly like TITANFALL but its FREE!Play this game now'}]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reviews.iloc[100,2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Necesitamos extraer en columnas la información presente dentro de cada campo de la tercera columna. <br>\n",
    "Los campos en columnas serán: divertido, publicado, última edición, ID de artículo, útil, recomendar, reseña."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 'reviews' es la columna que contiene una lista de diccionarios\n",
    "# Necesitamos trabajar con los valores que contiene la columna para crear el analisis de sentimiento\n",
    "# Initiazamos una lista vacia para guardar las reviews desanidadas\n",
    "unnested_reviews = []\n",
    "\n",
    "# Iteramos en cada fila del  DataFrame 'df_reviews'\n",
    "for index, row in df_reviews.iterrows():\n",
    "    user_id = row['user_id']\n",
    "    user_url = row['user_url']\n",
    "    reviews = row['reviews']\n",
    "    \n",
    "    # Dentro de la celda, iteramos en cada review  en 'reviews' list\n",
    "    for review in reviews:\n",
    "        new_review = {\n",
    "            'user_id': user_id,\n",
    "            'user_url': user_url,\n",
    "            'funny': review.get('funny', ''),\n",
    "            'posted': review.get('posted', ''),\n",
    "            'last_edited': review.get('last_edited', ''),\n",
    "            'item_id': review.get('item_id', ''),\n",
    "            'helpful': review.get('helpful', ''),\n",
    "            'recommend': review.get('recommend', ''),\n",
    "            'review_text': review.get('review', '')  # Renombramos a 'review' como 'review_text'\n",
    "        }\n",
    "        \n",
    "        # agregamos la review a la lista \n",
    "        unnested_reviews.append(new_review)\n",
    "\n",
    "# Creamos un nuevo DataFrame 'df_reviews_unnested' de la lista de reviews desanidadas\n",
    "df_reviews_unnested = pd.DataFrame(unnested_reviews)"
   ]
  },
  {
   "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>user_id</th>\n",
       "      <th>user_url</th>\n",
       "      <th>funny</th>\n",
       "      <th>posted</th>\n",
       "      <th>last_edited</th>\n",
       "      <th>item_id</th>\n",
       "      <th>helpful</th>\n",
       "      <th>recommend</th>\n",
       "      <th>review_text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td></td>\n",
       "      <td>Posted November 5, 2011.</td>\n",
       "      <td></td>\n",
       "      <td>1250</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Simple yet with great replayability. In my opi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td></td>\n",
       "      <td>Posted July 15, 2011.</td>\n",
       "      <td></td>\n",
       "      <td>22200</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>It's unique and worth a playthrough.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td></td>\n",
       "      <td>Posted April 21, 2011.</td>\n",
       "      <td></td>\n",
       "      <td>43110</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Great atmosphere. The gunplay can be a bit chu...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>js41637</td>\n",
       "      <td>http://steamcommunity.com/id/js41637</td>\n",
       "      <td></td>\n",
       "      <td>Posted June 24, 2014.</td>\n",
       "      <td></td>\n",
       "      <td>251610</td>\n",
       "      <td>15 of 20 people (75%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>I know what you think when you see this title ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>js41637</td>\n",
       "      <td>http://steamcommunity.com/id/js41637</td>\n",
       "      <td></td>\n",
       "      <td>Posted September 8, 2013.</td>\n",
       "      <td></td>\n",
       "      <td>227300</td>\n",
       "      <td>0 of 1 people (0%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>For a simple (it's actually not all that simpl...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58425</th>\n",
       "      <td>76561198312638244</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198312...</td>\n",
       "      <td></td>\n",
       "      <td>Posted July 10.</td>\n",
       "      <td></td>\n",
       "      <td>70</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>a must have classic from steam definitely wort...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58426</th>\n",
       "      <td>76561198312638244</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198312...</td>\n",
       "      <td></td>\n",
       "      <td>Posted July 8.</td>\n",
       "      <td></td>\n",
       "      <td>362890</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>this game is a perfect remake of the original ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58427</th>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>http://steamcommunity.com/id/LydiaMorley</td>\n",
       "      <td>1 person found this review funny</td>\n",
       "      <td>Posted July 3.</td>\n",
       "      <td></td>\n",
       "      <td>273110</td>\n",
       "      <td>1 of 2 people (50%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>had so much fun plaing this and collecting res...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58428</th>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>http://steamcommunity.com/id/LydiaMorley</td>\n",
       "      <td></td>\n",
       "      <td>Posted July 20.</td>\n",
       "      <td></td>\n",
       "      <td>730</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>:D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58429</th>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>http://steamcommunity.com/id/LydiaMorley</td>\n",
       "      <td></td>\n",
       "      <td>Posted July 2.</td>\n",
       "      <td></td>\n",
       "      <td>440</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>so much fun :D</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>58430 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 user_id                                           user_url  \\\n",
       "0      76561197970982479  http://steamcommunity.com/profiles/76561197970...   \n",
       "1      76561197970982479  http://steamcommunity.com/profiles/76561197970...   \n",
       "2      76561197970982479  http://steamcommunity.com/profiles/76561197970...   \n",
       "3                js41637               http://steamcommunity.com/id/js41637   \n",
       "4                js41637               http://steamcommunity.com/id/js41637   \n",
       "...                  ...                                                ...   \n",
       "58425  76561198312638244  http://steamcommunity.com/profiles/76561198312...   \n",
       "58426  76561198312638244  http://steamcommunity.com/profiles/76561198312...   \n",
       "58427        LydiaMorley           http://steamcommunity.com/id/LydiaMorley   \n",
       "58428        LydiaMorley           http://steamcommunity.com/id/LydiaMorley   \n",
       "58429        LydiaMorley           http://steamcommunity.com/id/LydiaMorley   \n",
       "\n",
       "                                  funny                     posted  \\\n",
       "0                                         Posted November 5, 2011.   \n",
       "1                                            Posted July 15, 2011.   \n",
       "2                                           Posted April 21, 2011.   \n",
       "3                                            Posted June 24, 2014.   \n",
       "4                                        Posted September 8, 2013.   \n",
       "...                                 ...                        ...   \n",
       "58425                                              Posted July 10.   \n",
       "58426                                               Posted July 8.   \n",
       "58427  1 person found this review funny             Posted July 3.   \n",
       "58428                                              Posted July 20.   \n",
       "58429                                               Posted July 2.   \n",
       "\n",
       "      last_edited item_id                                          helpful  \\\n",
       "0                    1250                                   No ratings yet   \n",
       "1                   22200                                   No ratings yet   \n",
       "2                   43110                                   No ratings yet   \n",
       "3                  251610  15 of 20 people (75%) found this review helpful   \n",
       "4                  227300     0 of 1 people (0%) found this review helpful   \n",
       "...           ...     ...                                              ...   \n",
       "58425                  70                                   No ratings yet   \n",
       "58426              362890                                   No ratings yet   \n",
       "58427              273110    1 of 2 people (50%) found this review helpful   \n",
       "58428                 730                                   No ratings yet   \n",
       "58429                 440                                   No ratings yet   \n",
       "\n",
       "       recommend                                        review_text  \n",
       "0           True  Simple yet with great replayability. In my opi...  \n",
       "1           True               It's unique and worth a playthrough.  \n",
       "2           True  Great atmosphere. The gunplay can be a bit chu...  \n",
       "3           True  I know what you think when you see this title ...  \n",
       "4           True  For a simple (it's actually not all that simpl...  \n",
       "...          ...                                                ...  \n",
       "58425       True  a must have classic from steam definitely wort...  \n",
       "58426       True  this game is a perfect remake of the original ...  \n",
       "58427       True  had so much fun plaing this and collecting res...  \n",
       "58428       True                                                 :D  \n",
       "58429       True                                     so much fun :D  \n",
       "\n",
       "[58430 rows x 9 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reviews_unnested"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['user_id', 'user_url', 'funny', 'posted', 'last_edited', 'item_id',\n",
       "       'helpful', 'recommend', 'review_text'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reviews_unnested.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_reviews = df_reviews_unnested"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 58430 entries, 0 to 58429\n",
      "Data columns (total 9 columns):\n",
      " #   Column       Non-Null Count  Dtype \n",
      "---  ------       --------------  ----- \n",
      " 0   user_id      58430 non-null  object\n",
      " 1   user_url     58430 non-null  object\n",
      " 2   funny        58430 non-null  object\n",
      " 3   posted       58430 non-null  object\n",
      " 4   last_edited  58430 non-null  object\n",
      " 5   item_id      58430 non-null  object\n",
      " 6   helpful      58430 non-null  object\n",
      " 7   recommend    58430 non-null  bool  \n",
      " 8   review_text  58430 non-null  object\n",
      "dtypes: bool(1), object(8)\n",
      "memory usage: 3.6+ MB\n"
     ]
    }
   ],
   "source": [
    "df_reviews.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Verificamos valores faltantes y duplicados\n",
    "Muchas columnas tienen ' ' como valores, que deberían convertirse en None a partir de la transformación desanidada."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# copiamos el reviews DataFrame\n",
    "df_reviews_copy = df_reviews.copy()\n",
    "\n",
    "# reemplazamos los strings vacios por None\n",
    "df_reviews_copy.replace('', np.nan, inplace=True)\n",
    "\n",
    "# ASignamos el df copiado al original \n",
    "df_reviews = df_reviews_copy"
   ]
  },
  {
   "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>Total Registers</th>\n",
       "      <th>None Count</th>\n",
       "      <th>None Percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>user_id</th>\n",
       "      <td>58430</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>user_url</th>\n",
       "      <td>58430</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>funny</th>\n",
       "      <td>58430</td>\n",
       "      <td>50420</td>\n",
       "      <td>86.291289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>posted</th>\n",
       "      <td>58430</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>last_edited</th>\n",
       "      <td>58430</td>\n",
       "      <td>52393</td>\n",
       "      <td>89.667979</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item_id</th>\n",
       "      <td>58430</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>helpful</th>\n",
       "      <td>58430</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>recommend</th>\n",
       "      <td>58430</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>review_text</th>\n",
       "      <td>58430</td>\n",
       "      <td>30</td>\n",
       "      <td>0.051343</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Total Registers  None Count  None Percentage\n",
       "user_id                58430           0         0.000000\n",
       "user_url               58430           0         0.000000\n",
       "funny                  58430       50420        86.291289\n",
       "posted                 58430           0         0.000000\n",
       "last_edited            58430       52393        89.667979\n",
       "item_id                58430           0         0.000000\n",
       "helpful                58430           0         0.000000\n",
       "recommend              58430           0         0.000000\n",
       "review_text            58430          30         0.051343"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Contamos la cantidad de valores None en cada columna\n",
    "none_count = df_reviews.isnull().sum()\n",
    "# Calculamos el porcentaje de None para cada columna\n",
    "none_percentage = (none_count / len(df_reviews)) * 100\n",
    "# Combinamos los None count y porcentajes al DataFrame\n",
    "none_info = pd.DataFrame({'None Count': none_count, 'None Percentage': none_percentage})\n",
    "# Agregamos la columna del total de Registros\n",
    "none_info['Total Registers'] = len(df_reviews)\n",
    "# Reordenamos las columnas\n",
    "none_info = none_info[['Total Registers', 'None Count', 'None Percentage']]\n",
    "none_info"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dado este conocimiento sobre los datos de las revisiones, podemos proceder a eliminar esas dos columnas que tienen principalmente valores None: last_edited y funny"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "last_edited\n",
       "Last edited November 25, 2013.    99\n",
       "Last edited October 17, 2015.     18\n",
       "Last edited July 25, 2015.        17\n",
       "Last edited June 22, 2015.        16\n",
       "Last edited December 29, 2015.    16\n",
       "                                  ..\n",
       "Last edited August 13, 2014.       1\n",
       "Last edited February 26, 2014.     1\n",
       "Last edited November 30, 2014.     1\n",
       "Last edited February 28, 2014.     1\n",
       "Last edited August 15, 2014.       1\n",
       "Name: count, Length: 1014, dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reviews['last_edited'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "funny\n",
       "1 person found this review funny        5083\n",
       "2 people found this review funny        1213\n",
       "3 people found this review funny         488\n",
       "4 people found this review funny         263\n",
       "5 people found this review funny         162\n",
       "                                        ... \n",
       "58 people found this review funny          1\n",
       "405 people found this review funny         1\n",
       "105 people found this review funny         1\n",
       "1,130 people found this review funny       1\n",
       "825 people found this review funny         1\n",
       "Name: count, Length: 185, dtype: int64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reviews['funny'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_reviews.drop(['last_edited', 'funny','user_url'], axis=1, inplace= True)"
   ]
  },
  {
   "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>user_id</th>\n",
       "      <th>posted</th>\n",
       "      <th>item_id</th>\n",
       "      <th>helpful</th>\n",
       "      <th>recommend</th>\n",
       "      <th>review_text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>Posted November 5, 2011.</td>\n",
       "      <td>1250</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Simple yet with great replayability. In my opi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>Posted July 15, 2011.</td>\n",
       "      <td>22200</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>It's unique and worth a playthrough.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>Posted April 21, 2011.</td>\n",
       "      <td>43110</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Great atmosphere. The gunplay can be a bit chu...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>js41637</td>\n",
       "      <td>Posted June 24, 2014.</td>\n",
       "      <td>251610</td>\n",
       "      <td>15 of 20 people (75%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>I know what you think when you see this title ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>js41637</td>\n",
       "      <td>Posted September 8, 2013.</td>\n",
       "      <td>227300</td>\n",
       "      <td>0 of 1 people (0%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>For a simple (it's actually not all that simpl...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58425</th>\n",
       "      <td>76561198312638244</td>\n",
       "      <td>Posted July 10.</td>\n",
       "      <td>70</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>a must have classic from steam definitely wort...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58426</th>\n",
       "      <td>76561198312638244</td>\n",
       "      <td>Posted July 8.</td>\n",
       "      <td>362890</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>this game is a perfect remake of the original ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58427</th>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>Posted July 3.</td>\n",
       "      <td>273110</td>\n",
       "      <td>1 of 2 people (50%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>had so much fun plaing this and collecting res...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58428</th>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>Posted July 20.</td>\n",
       "      <td>730</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>:D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58429</th>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>Posted July 2.</td>\n",
       "      <td>440</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>so much fun :D</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>58430 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 user_id                     posted item_id  \\\n",
       "0      76561197970982479   Posted November 5, 2011.    1250   \n",
       "1      76561197970982479      Posted July 15, 2011.   22200   \n",
       "2      76561197970982479     Posted April 21, 2011.   43110   \n",
       "3                js41637      Posted June 24, 2014.  251610   \n",
       "4                js41637  Posted September 8, 2013.  227300   \n",
       "...                  ...                        ...     ...   \n",
       "58425  76561198312638244            Posted July 10.      70   \n",
       "58426  76561198312638244             Posted July 8.  362890   \n",
       "58427        LydiaMorley             Posted July 3.  273110   \n",
       "58428        LydiaMorley            Posted July 20.     730   \n",
       "58429        LydiaMorley             Posted July 2.     440   \n",
       "\n",
       "                                               helpful  recommend  \\\n",
       "0                                       No ratings yet       True   \n",
       "1                                       No ratings yet       True   \n",
       "2                                       No ratings yet       True   \n",
       "3      15 of 20 people (75%) found this review helpful       True   \n",
       "4         0 of 1 people (0%) found this review helpful       True   \n",
       "...                                                ...        ...   \n",
       "58425                                   No ratings yet       True   \n",
       "58426                                   No ratings yet       True   \n",
       "58427    1 of 2 people (50%) found this review helpful       True   \n",
       "58428                                   No ratings yet       True   \n",
       "58429                                   No ratings yet       True   \n",
       "\n",
       "                                             review_text  \n",
       "0      Simple yet with great replayability. In my opi...  \n",
       "1                   It's unique and worth a playthrough.  \n",
       "2      Great atmosphere. The gunplay can be a bit chu...  \n",
       "3      I know what you think when you see this title ...  \n",
       "4      For a simple (it's actually not all that simpl...  \n",
       "...                                                  ...  \n",
       "58425  a must have classic from steam definitely wort...  \n",
       "58426  this game is a perfect remake of the original ...  \n",
       "58427  had so much fun plaing this and collecting res...  \n",
       "58428                                                 :D  \n",
       "58429                                     so much fun :D  \n",
       "\n",
       "[58430 rows x 6 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reviews"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### También podemos eliminar esos registros donde review_text es None."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>posted</th>\n",
       "      <th>item_id</th>\n",
       "      <th>helpful</th>\n",
       "      <th>recommend</th>\n",
       "      <th>review_text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>Posted November 5, 2011.</td>\n",
       "      <td>1250</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Simple yet with great replayability. In my opi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>Posted July 15, 2011.</td>\n",
       "      <td>22200</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>It's unique and worth a playthrough.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>Posted April 21, 2011.</td>\n",
       "      <td>43110</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Great atmosphere. The gunplay can be a bit chu...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>js41637</td>\n",
       "      <td>Posted June 24, 2014.</td>\n",
       "      <td>251610</td>\n",
       "      <td>15 of 20 people (75%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>I know what you think when you see this title ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>js41637</td>\n",
       "      <td>Posted September 8, 2013.</td>\n",
       "      <td>227300</td>\n",
       "      <td>0 of 1 people (0%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>For a simple (it's actually not all that simpl...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58425</th>\n",
       "      <td>76561198312638244</td>\n",
       "      <td>Posted July 10.</td>\n",
       "      <td>70</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>a must have classic from steam definitely wort...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58426</th>\n",
       "      <td>76561198312638244</td>\n",
       "      <td>Posted July 8.</td>\n",
       "      <td>362890</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>this game is a perfect remake of the original ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58427</th>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>Posted July 3.</td>\n",
       "      <td>273110</td>\n",
       "      <td>1 of 2 people (50%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>had so much fun plaing this and collecting res...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58428</th>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>Posted July 20.</td>\n",
       "      <td>730</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>:D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58429</th>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>Posted July 2.</td>\n",
       "      <td>440</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>so much fun :D</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>58400 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 user_id                     posted item_id  \\\n",
       "0      76561197970982479   Posted November 5, 2011.    1250   \n",
       "1      76561197970982479      Posted July 15, 2011.   22200   \n",
       "2      76561197970982479     Posted April 21, 2011.   43110   \n",
       "3                js41637      Posted June 24, 2014.  251610   \n",
       "4                js41637  Posted September 8, 2013.  227300   \n",
       "...                  ...                        ...     ...   \n",
       "58425  76561198312638244            Posted July 10.      70   \n",
       "58426  76561198312638244             Posted July 8.  362890   \n",
       "58427        LydiaMorley             Posted July 3.  273110   \n",
       "58428        LydiaMorley            Posted July 20.     730   \n",
       "58429        LydiaMorley             Posted July 2.     440   \n",
       "\n",
       "                                               helpful  recommend  \\\n",
       "0                                       No ratings yet       True   \n",
       "1                                       No ratings yet       True   \n",
       "2                                       No ratings yet       True   \n",
       "3      15 of 20 people (75%) found this review helpful       True   \n",
       "4         0 of 1 people (0%) found this review helpful       True   \n",
       "...                                                ...        ...   \n",
       "58425                                   No ratings yet       True   \n",
       "58426                                   No ratings yet       True   \n",
       "58427    1 of 2 people (50%) found this review helpful       True   \n",
       "58428                                   No ratings yet       True   \n",
       "58429                                   No ratings yet       True   \n",
       "\n",
       "                                             review_text  \n",
       "0      Simple yet with great replayability. In my opi...  \n",
       "1                   It's unique and worth a playthrough.  \n",
       "2      Great atmosphere. The gunplay can be a bit chu...  \n",
       "3      I know what you think when you see this title ...  \n",
       "4      For a simple (it's actually not all that simpl...  \n",
       "...                                                  ...  \n",
       "58425  a must have classic from steam definitely wort...  \n",
       "58426  this game is a perfect remake of the original ...  \n",
       "58427  had so much fun plaing this and collecting res...  \n",
       "58428                                                 :D  \n",
       "58429                                     so much fun :D  \n",
       "\n",
       "[58400 rows x 6 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dropeamos las filas con valores  None en 'review_text'\n",
    "df_reviews = df_reviews.dropna(subset=['review_text'])\n",
    "df_reviews"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>Total Registers</th>\n",
       "      <th>None Count</th>\n",
       "      <th>None Percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>user_id</th>\n",
       "      <td>58400</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>posted</th>\n",
       "      <td>58400</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item_id</th>\n",
       "      <td>58400</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>helpful</th>\n",
       "      <td>58400</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>recommend</th>\n",
       "      <td>58400</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>review_text</th>\n",
       "      <td>58400</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Total Registers  None Count  None Percentage\n",
       "user_id                58400           0              0.0\n",
       "posted                 58400           0              0.0\n",
       "item_id                58400           0              0.0\n",
       "helpful                58400           0              0.0\n",
       "recommend              58400           0              0.0\n",
       "review_text            58400           0              0.0"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Cantidad de valores  None values en cada columna\n",
    "none_count = df_reviews.isnull().sum()\n",
    "# Calculamos el porcentaje de None para cada columna\n",
    "none_percentage = (none_count / len(df_reviews)) * 100\n",
    "# Combinamos los None count y porcentajes al DataFrame\n",
    "none_info = pd.DataFrame({'None Count': none_count, 'None Percentage': none_percentage})\n",
    "# Agregamos la columna del total de Registros\n",
    "none_info['Total Registers'] = len(df_reviews)\n",
    "# Reordenamos las columnas\n",
    "none_info = none_info[['Total Registers', 'None Count', 'None Percentage']]\n",
    "none_info"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Publicado\n",
    "Esta columna tiene la fecha en que se publicó la reseña del juego por parte del usuario.<br>\n",
    "Pero el formato es 'Publicado <Mes> <día>, <año>' <br>\n",
    "Y necesitamos transformar esto en el formato yyyy-mm-dd.<br>\n",
    "Vamos a extraer el mes y almacenarlo en una columna 'mes', y 'día' y 'año'.<br>\n",
    "Dado que en la columna no hay un único formato sino varios:<br>\n",
    "Ejemplo: <br>\n",
    "\n",
    "- 'Publicado 5 de noviembre de 2011.'<br>\n",
    "- 'Publicado 2 de julio.'<br>\n",
    "Estos últimos tienen el año faltante, por lo que se definirán como formato no válido None<br"
   ]
  },
  {
   "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>posted</th>\n",
       "      <th>item_id</th>\n",
       "      <th>helpful</th>\n",
       "      <th>recommend</th>\n",
       "      <th>review_text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>Posted November 5, 2011.</td>\n",
       "      <td>1250</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Simple yet with great replayability. In my opi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>Posted July 15, 2011.</td>\n",
       "      <td>22200</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>It's unique and worth a playthrough.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>Posted April 21, 2011.</td>\n",
       "      <td>43110</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Great atmosphere. The gunplay can be a bit chu...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>js41637</td>\n",
       "      <td>Posted June 24, 2014.</td>\n",
       "      <td>251610</td>\n",
       "      <td>15 of 20 people (75%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>I know what you think when you see this title ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>js41637</td>\n",
       "      <td>Posted September 8, 2013.</td>\n",
       "      <td>227300</td>\n",
       "      <td>0 of 1 people (0%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>For a simple (it's actually not all that simpl...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58425</th>\n",
       "      <td>76561198312638244</td>\n",
       "      <td>Posted July 10.</td>\n",
       "      <td>70</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>a must have classic from steam definitely wort...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58426</th>\n",
       "      <td>76561198312638244</td>\n",
       "      <td>Posted July 8.</td>\n",
       "      <td>362890</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>this game is a perfect remake of the original ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58427</th>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>Posted July 3.</td>\n",
       "      <td>273110</td>\n",
       "      <td>1 of 2 people (50%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>had so much fun plaing this and collecting res...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58428</th>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>Posted July 20.</td>\n",
       "      <td>730</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>:D</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58429</th>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>Posted July 2.</td>\n",
       "      <td>440</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>so much fun :D</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>58400 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 user_id                     posted item_id  \\\n",
       "0      76561197970982479   Posted November 5, 2011.    1250   \n",
       "1      76561197970982479      Posted July 15, 2011.   22200   \n",
       "2      76561197970982479     Posted April 21, 2011.   43110   \n",
       "3                js41637      Posted June 24, 2014.  251610   \n",
       "4                js41637  Posted September 8, 2013.  227300   \n",
       "...                  ...                        ...     ...   \n",
       "58425  76561198312638244            Posted July 10.      70   \n",
       "58426  76561198312638244             Posted July 8.  362890   \n",
       "58427        LydiaMorley             Posted July 3.  273110   \n",
       "58428        LydiaMorley            Posted July 20.     730   \n",
       "58429        LydiaMorley             Posted July 2.     440   \n",
       "\n",
       "                                               helpful  recommend  \\\n",
       "0                                       No ratings yet       True   \n",
       "1                                       No ratings yet       True   \n",
       "2                                       No ratings yet       True   \n",
       "3      15 of 20 people (75%) found this review helpful       True   \n",
       "4         0 of 1 people (0%) found this review helpful       True   \n",
       "...                                                ...        ...   \n",
       "58425                                   No ratings yet       True   \n",
       "58426                                   No ratings yet       True   \n",
       "58427    1 of 2 people (50%) found this review helpful       True   \n",
       "58428                                   No ratings yet       True   \n",
       "58429                                   No ratings yet       True   \n",
       "\n",
       "                                             review_text  \n",
       "0      Simple yet with great replayability. In my opi...  \n",
       "1                   It's unique and worth a playthrough.  \n",
       "2      Great atmosphere. The gunplay can be a bit chu...  \n",
       "3      I know what you think when you see this title ...  \n",
       "4      For a simple (it's actually not all that simpl...  \n",
       "...                                                  ...  \n",
       "58425  a must have classic from steam definitely wort...  \n",
       "58426  this game is a perfect remake of the original ...  \n",
       "58427  had so much fun plaing this and collecting res...  \n",
       "58428                                                 :D  \n",
       "58429                                     so much fun :D  \n",
       "\n",
       "[58400 rows x 6 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reviews"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Definimos una funcion de  the conversion\n",
    "def convert_posted(posted_str):\n",
    "    match = re.search(r\"Posted (\\w+) (\\d{1,2})(?:,)?(?: (\\d{4}))?\", posted_str)\n",
    "    if match:\n",
    "        month_str, day_str, year_str = match.groups()\n",
    "        month_mapping = {\n",
    "            \"January\": \"01\",\n",
    "            \"February\": \"02\",\n",
    "            \"March\": \"03\",\n",
    "            \"April\": \"04\",\n",
    "            \"May\": \"05\",\n",
    "            \"June\": \"06\",\n",
    "            \"July\": \"07\",\n",
    "            \"August\": \"08\",\n",
    "            \"September\": \"09\",\n",
    "            \"October\": \"10\",\n",
    "            \"November\": \"11\",\n",
    "            \"December\": \"12\"\n",
    "        }\n",
    "        current_year = str(pd.Timestamp.now().year)\n",
    "        formatted_date = f\"{year_str or current_year}-{month_mapping[month_str]}-{day_str.zfill(2)}\"\n",
    "        return formatted_date\n",
    "    else:\n",
    "        return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>posted</th>\n",
       "      <th>item_id</th>\n",
       "      <th>helpful</th>\n",
       "      <th>recommend</th>\n",
       "      <th>review_text</th>\n",
       "      <th>date</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>Posted November 5, 2011.</td>\n",
       "      <td>1250</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Simple yet with great replayability. In my opi...</td>\n",
       "      <td>2011-11-05</td>\n",
       "      <td>2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>Posted July 15, 2011.</td>\n",
       "      <td>22200</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>It's unique and worth a playthrough.</td>\n",
       "      <td>2011-07-15</td>\n",
       "      <td>2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>Posted April 21, 2011.</td>\n",
       "      <td>43110</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Great atmosphere. The gunplay can be a bit chu...</td>\n",
       "      <td>2011-04-21</td>\n",
       "      <td>2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>js41637</td>\n",
       "      <td>Posted June 24, 2014.</td>\n",
       "      <td>251610</td>\n",
       "      <td>15 of 20 people (75%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>I know what you think when you see this title ...</td>\n",
       "      <td>2014-06-24</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>js41637</td>\n",
       "      <td>Posted September 8, 2013.</td>\n",
       "      <td>227300</td>\n",
       "      <td>0 of 1 people (0%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>For a simple (it's actually not all that simpl...</td>\n",
       "      <td>2013-09-08</td>\n",
       "      <td>2013</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>58425</th>\n",
       "      <td>76561198312638244</td>\n",
       "      <td>Posted July 10.</td>\n",
       "      <td>70</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>a must have classic from steam definitely wort...</td>\n",
       "      <td>2024-07-10</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58426</th>\n",
       "      <td>76561198312638244</td>\n",
       "      <td>Posted July 8.</td>\n",
       "      <td>362890</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>this game is a perfect remake of the original ...</td>\n",
       "      <td>2024-07-08</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58427</th>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>Posted July 3.</td>\n",
       "      <td>273110</td>\n",
       "      <td>1 of 2 people (50%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>had so much fun plaing this and collecting res...</td>\n",
       "      <td>2024-07-03</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58428</th>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>Posted July 20.</td>\n",
       "      <td>730</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>:D</td>\n",
       "      <td>2024-07-20</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58429</th>\n",
       "      <td>LydiaMorley</td>\n",
       "      <td>Posted July 2.</td>\n",
       "      <td>440</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>so much fun :D</td>\n",
       "      <td>2024-07-02</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>58400 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 user_id                     posted item_id  \\\n",
       "0      76561197970982479   Posted November 5, 2011.    1250   \n",
       "1      76561197970982479      Posted July 15, 2011.   22200   \n",
       "2      76561197970982479     Posted April 21, 2011.   43110   \n",
       "3                js41637      Posted June 24, 2014.  251610   \n",
       "4                js41637  Posted September 8, 2013.  227300   \n",
       "...                  ...                        ...     ...   \n",
       "58425  76561198312638244            Posted July 10.      70   \n",
       "58426  76561198312638244             Posted July 8.  362890   \n",
       "58427        LydiaMorley             Posted July 3.  273110   \n",
       "58428        LydiaMorley            Posted July 20.     730   \n",
       "58429        LydiaMorley             Posted July 2.     440   \n",
       "\n",
       "                                               helpful  recommend  \\\n",
       "0                                       No ratings yet       True   \n",
       "1                                       No ratings yet       True   \n",
       "2                                       No ratings yet       True   \n",
       "3      15 of 20 people (75%) found this review helpful       True   \n",
       "4         0 of 1 people (0%) found this review helpful       True   \n",
       "...                                                ...        ...   \n",
       "58425                                   No ratings yet       True   \n",
       "58426                                   No ratings yet       True   \n",
       "58427    1 of 2 people (50%) found this review helpful       True   \n",
       "58428                                   No ratings yet       True   \n",
       "58429                                   No ratings yet       True   \n",
       "\n",
       "                                             review_text       date  year  \n",
       "0      Simple yet with great replayability. In my opi... 2011-11-05  2011  \n",
       "1                   It's unique and worth a playthrough. 2011-07-15  2011  \n",
       "2      Great atmosphere. The gunplay can be a bit chu... 2011-04-21  2011  \n",
       "3      I know what you think when you see this title ... 2014-06-24  2014  \n",
       "4      For a simple (it's actually not all that simpl... 2013-09-08  2013  \n",
       "...                                                  ...        ...   ...  \n",
       "58425  a must have classic from steam definitely wort... 2024-07-10  2024  \n",
       "58426  this game is a perfect remake of the original ... 2024-07-08  2024  \n",
       "58427  had so much fun plaing this and collecting res... 2024-07-03  2024  \n",
       "58428                                                 :D 2024-07-20  2024  \n",
       "58429                                     so much fun :D 2024-07-02  2024  \n",
       "\n",
       "[58400 rows x 8 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reviews_copy = df_reviews.copy()\n",
    "# Aplicamos la funcion de conversion para parsear la columna 'posted' al Dataset\n",
    "df_reviews_copy['date'] = df_reviews_copy['posted'].apply(convert_posted)\n",
    "\n",
    "# Convertimos la columna 'date' to datetime format\n",
    "df_reviews_copy['date'] = pd.to_datetime(df_reviews_copy['date'], errors='coerce')\n",
    "\n",
    "# Extraemos el año de la columna  year \n",
    "df_reviews_copy['year'] = df_reviews_copy['date'].dt.year\n",
    "\n",
    "df_reviews_copy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_reviews = df_reviews_copy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Ahora tenemos una columna de fecha, pero esta columna tiene el año actual para aquellos registros que tenían un formato incorrecto en el campo \"Publicado\" y el año estaba ausente. Por lo tanto, estos registros, que son 9929 en total, pueden filtrarse por la columna de año. Estos registros no deben considerarse para la función best_developer_year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "year\n",
       "2014    21821\n",
       "2015    18146\n",
       "2024     9929\n",
       "2013     6707\n",
       "2012     1201\n",
       "2011      530\n",
       "2010       66\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reviews['year'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Contamos los  NaN en  'year'\n",
    "nan_count = df_reviews['year'].isna().sum()\n",
    "nan_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_reviews.drop(['posted'], axis=1, inplace= True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  Contamos los  NaN en 'user_id' \n",
    "nan_count = df_reviews['user_id'].isna().sum()\n",
    "nan_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_reviews[\"item_id\"] = pd.to_numeric(df_reviews[\"item_id\"], errors=\"coerce\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 58400 entries, 0 to 58429\n",
      "Data columns (total 7 columns):\n",
      " #   Column       Non-Null Count  Dtype         \n",
      "---  ------       --------------  -----         \n",
      " 0   user_id      58400 non-null  object        \n",
      " 1   item_id      58400 non-null  int64         \n",
      " 2   helpful      58400 non-null  object        \n",
      " 3   recommend    58400 non-null  bool          \n",
      " 4   review_text  58400 non-null  object        \n",
      " 5   date         58400 non-null  datetime64[ns]\n",
      " 6   year         58400 non-null  int32         \n",
      "dtypes: bool(1), datetime64[ns](1), int32(1), int64(1), object(3)\n",
      "memory usage: 3.0+ MB\n"
     ]
    }
   ],
   "source": [
    "df_reviews.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Almacenar el DataFrame de reviews\n",
    "Ahora que hemos realizado la carga y transformación de los datos en información valiosa, los almacenamos para proceder con el EDA.\n",
    "Elegimos almacenar los datos como .parquet debido a las limitaciones de tamaño."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "reviews DataFrame was stored into data/reviews.parquet\n"
     ]
    }
   ],
   "source": [
    "# Definimos el path para guardar nuestro trabajo en un .parquet file\n",
    "reviews = 'data/reviews.parquet'\n",
    "\n",
    "# Guardamos el DataFrame \n",
    "df_reviews.to_parquet(reviews, index=False)\n",
    "\n",
    "# Mensaje de confirmacion \n",
    "print(f'reviews DataFrame was stored into {reviews}')"
   ]
  }
 ],
 "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.12.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}