04-feature-eng.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Preparación de conjuntos de datos para consultas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Importamos las librerías necesarias"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "import ast\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import pyarrow as pa\n",
    "import pyarrow.parquet as pq\n",
    "import nltk\n",
    "from textblob import TextBlob\n",
    "from nltk.sentiment import SentimentIntensityAnalyzer\n",
    "from datetime import datetime, timedelta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "games = pd.read_parquet('data/01-steam-games.parquet')\n",
    "reviews= pd.read_parquet('data/02-user-reviews.parquet')\n",
    "items = pd.read_parquet('data/03-users-items.parquet')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 57367 entries, 0 to 57366\n",
      "Data columns (total 7 columns):\n",
      " #   Column             Non-Null Count  Dtype \n",
      "---  ------             --------------  ----- \n",
      " 0   user_id            57367 non-null  object\n",
      " 1   user_url           57367 non-null  object\n",
      " 2   reviews_item_id    57367 non-null  int64 \n",
      " 3   reviews_helpful    57367 non-null  object\n",
      " 4   reviews_recommend  57367 non-null  bool  \n",
      " 5   reviews_review     57367 non-null  object\n",
      " 6   posted_year        57367 non-null  int64 \n",
      "dtypes: bool(1), int64(2), object(4)\n",
      "memory usage: 2.7+ MB\n"
     ]
    }
   ],
   "source": [
    "reviews.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se pide crear una nueva columna llamada 'sentiment_analysis' que reemplace a 'reviews_review' donde se realice un análisis de sentimiento de los comentarios con la siguiente escala:\n",
    "\n",
    "* 0 si es malo,\n",
    "* 1 si es neutral o esta sin review\n",
    "* 2 si es positivo.\n",
    "\n",
    "Dado que el objetivo de este proyecto es realizar una prueba de concepto, consiguiendo un producto mínimo viable, se realiza un análisis de sentimiento básico utilizando TextBlob que es una biblioteca de procesamiento de lenguaje natural (NLP) en Python. El objetivo de esta metodología es asignar un valor numérico a un texto, en este caso a los comentarios que los usuarios dejaron para un juego determinado, para representar si el sentimiento expresado en el texto es negativo, neutral o positivo. \n",
    "\n",
    "Esta metodología toma una revisión de texto como entrada, utiliza TextBlob para calcular la polaridad de sentimiento y luego clasifica la revisión como negativa, neutral o positiva en función de la polaridad calculada. En este caso, se consideraron las polaridades por defecto del modelo, el cuál utiliza umbrales -0.2 y 0.2, siendo polaridades negativas por debajo de -0.2, positivas por encima de 0.2 y neutrales entre medio de ambos."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "def analisis_sentimiento(review):\n",
    "    '''\n",
    "    Realiza un análisis de sentimiento en un texto dado y devuelve un valor numérico que representa el sentimiento.\n",
    "\n",
    "    Esta función utiliza la librería TextBlob para analizar el sentimiento en un texto dado y\n",
    "    asigna un valor numérico de acuerdo a la polaridad del sentimiento.\n",
    "\n",
    "    Parameters:\n",
    "        review (str): El texto que se va a analizar para determinar su sentimiento.\n",
    "\n",
    "    Returns:\n",
    "        int: Un valor numérico que representa el sentimiento del texto:\n",
    "             - 0 para sentimiento negativo.\n",
    "             - 1 para sentimiento neutral o no clasificable.\n",
    "             - 2 para sentimiento positivo.\n",
    "    '''\n",
    "    if review is None:\n",
    "        return 1\n",
    "    analysis = TextBlob(review)\n",
    "    polarity = analysis.sentiment.polarity\n",
    "    if polarity < -0.2:\n",
    "        return 0  \n",
    "    elif polarity > 0.2: \n",
    "        return 2 \n",
    "    else:\n",
    "        return 1 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "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_item_id</th>\n",
       "      <th>reviews_helpful</th>\n",
       "      <th>reviews_recommend</th>\n",
       "      <th>reviews_review</th>\n",
       "      <th>posted_year</th>\n",
       "      <th>sentiment_analysis</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>1250</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>Simple yet with great replayability. In my opi...</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>js41637</td>\n",
       "      <td>http://steamcommunity.com/id/js41637</td>\n",
       "      <td>251610</td>\n",
       "      <td>15 of 20 people (75%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>I know what you think when you see this title ...</td>\n",
       "      <td>2014</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>http://steamcommunity.com/id/evcentric</td>\n",
       "      <td>248820</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>A suitably punishing roguelike platformer.  Wi...</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>doctr</td>\n",
       "      <td>http://steamcommunity.com/id/doctr</td>\n",
       "      <td>250320</td>\n",
       "      <td>2 of 2 people (100%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>This game... is so fun. The fight sequences ha...</td>\n",
       "      <td>2013</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>maplemage</td>\n",
       "      <td>http://steamcommunity.com/id/maplemage</td>\n",
       "      <td>211420</td>\n",
       "      <td>35 of 43 people (81%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>Git gud</td>\n",
       "      <td>2014</td>\n",
       "      <td>1</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_item_id                                  reviews_helpful  \\\n",
       "0             1250                                   No ratings yet   \n",
       "1           251610  15 of 20 people (75%) found this review helpful   \n",
       "2           248820                                   No ratings yet   \n",
       "3           250320   2 of 2 people (100%) found this review helpful   \n",
       "4           211420  35 of 43 people (81%) found this review helpful   \n",
       "\n",
       "   reviews_recommend                                     reviews_review  \\\n",
       "0               True  Simple yet with great replayability. In my opi...   \n",
       "1               True  I know what you think when you see this title ...   \n",
       "2               True  A suitably punishing roguelike platformer.  Wi...   \n",
       "3               True  This game... is so fun. The fight sequences ha...   \n",
       "4               True                                            Git gud   \n",
       "\n",
       "   posted_year  sentiment_analysis  \n",
       "0         2011                   1  \n",
       "1         2014                   1  \n",
       "2         2010                   2  \n",
       "3         2013                   2  \n",
       "4         2014                   1  "
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reviews['sentiment_analysis'] = reviews['reviews_review'].apply(analisis_sentimiento)\n",
    "reviews.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sentiment_analysis\n",
       "1    35243\n",
       "2    17097\n",
       "0     5027\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Observamos los resultados del análisis de sentimientos\n",
    "conteo_sentimientos = reviews['sentiment_analysis'].value_counts()\n",
    "conteo_sentimientos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "def revision_sentimiento(reviews, sentiments):\n",
    "    \"\"\"\n",
    "    Muestra ejemplos de revisiones para cada categoría de análisis de sentimiento.\n",
    "\n",
    "    Parámetros:\n",
    "    - reviews (list): Lista de revisiones.\n",
    "    - sentiments (list): Lista de valores de análisis de sentimiento correspondientes a cada revisión.\n",
    "\n",
    "    La función imprime ejemplos de revisiones para cada categoría de análisis de sentimiento, \n",
    "    seleccionando aleatoriamente hasta 5 revisiones de cada categoría.\n",
    "    \"\"\"\n",
    "    import random\n",
    "    for sentiment_value in range(3):\n",
    "            print(f\"Para la categoría de análisis de sentimiento {sentiment_value} se tienen estos ejemplos de reviews:\")\n",
    "            sentiment_reviews = [review for review, sentiment in zip(reviews, sentiments) if sentiment == sentiment_value]\n",
    "            \n",
    "            for i, review in enumerate(random.sample(sentiment_reviews, 5), start=1):\n",
    "                print(f\"Review {i}: {review}\")\n",
    "            \n",
    "            print(\"=\"*100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Para la categoría de análisis de sentimiento 0 se tienen estos ejemplos de reviews:\n",
      "Review 1: 10/10 IGA1. build tiny car2. die3.get ♥♥♥♥♥♥4. get to rank 10Best Game Ever\n",
      "Review 2: PLZ BUFF REVOLVER NOT STRONG ENOUGHNeeds-No recoil-100 bullets-Automatic fire-4 scopes-Aimbot-Walls\n",
      "Review 3: Manliest Game...\n",
      "Review 4: I was driving in a SUV with someone when hear 'ALLAHU AKBAR' in a building, i check out the building, guess they were some crazy serial killers on the lose.10/10 would drive in a car with a stranger lel\n",
      "Review 5: Running Linux? This game ain't gonna load.\n",
      "====================================================================================================\n",
      "Para la categoría de análisis de sentimiento 1 se tienen estos ejemplos de reviews:\n",
      "Review 1: Sound in game doesnt work at all and a lot of people are having this problem and Activision dont seem to care nore want to fix it. So could be fed that i got vaced for modding in single player other than that its basically just mico tansactions for everything.\n",
      "Review 2: This game is for the ultimate AvP fans, and that only.You can choose between 3 species, the Aliens, the Predators, and the Marines (Humans). They each get their own single player campaign, which are all interesting in their own way. The Alien gets you to sneak in the shadows, silently taking out their prey. The Predator gets you in the open, with your cloak, you can sneak up on people and collect their heads as trophies. The Marine is when you really are screwed. You are almost always in dark areas with a pistol or a pulse rifle with Aliens that could be anywhere.But... you don't get much replayibility out of it unless you're a hardcore AvP fan, or an Alien or Predator fan. Like me, I could pick this up and play it, but for others, it is really boring to play through another time.Would you buy this game?\n",
      "Review 3: Dear, Gaben  I gave CS:GO a 10/10 For that, I request a knife in the next case I openthx <3#allhomoLove,         Kiwiw\n",
      "Review 4: Completely Butchers CS 1.6 0/10 - IGN\n",
      "Review 5: Muito ruim Jogo cheio de bug perdi dinheiro nessa porcaria!\n",
      "====================================================================================================\n",
      "Para la categoría de análisis de sentimiento 2 se tienen estos ejemplos de reviews:\n",
      "Review 1: Dota 2 Better than Warcraft 3 FT Map Mod THE FIRST DOTA SERIES   played in South east asia\n",
      "Review 2: Awesome, its the best.  8.5/10\n",
      "Review 3: its hecking fantastic mateys :))))))))\n",
      "Review 4: fun.\n",
      "Review 5: VERY good free to play\n",
      "====================================================================================================\n"
     ]
    }
   ],
   "source": [
    "revision_sentimiento(reviews['reviews_review'], reviews['sentiment_analysis'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se observa que no es totalmente preciso, pero para el objetivo del proyecto, es suficientemente bueno"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['user_id', 'user_url', 'reviews_item_id', 'reviews_helpful',\n",
       "       'reviews_recommend', 'posted_year', 'sentiment_analysis'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reviews = reviews.drop(columns=['reviews_review'])\n",
    "reviews.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cantidad de dinero gastado por usuario y cantidad de items consumidos\n",
    "\n",
    "Se crea el dataframe 'df_gastos_items' que contiene por cada usuario el gasto en videojuegos y la cantidad de items consumidos. Para ello se realizan una serie de pasos creando algunos dataframe auxiliares para luego unir los resultados."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "items = pd.read_parquet('data/03-users-items.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "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>items_count</th>\n",
       "      <th>user_id</th>\n",
       "      <th>item_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>277</td>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>277</td>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>277</td>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>277</td>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>277</td>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>3830</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   items_count            user_id  item_id\n",
       "0          277  76561197970982479       10\n",
       "1          277  76561197970982479       30\n",
       "2          277  76561197970982479      300\n",
       "3          277  76561197970982479      240\n",
       "4          277  76561197970982479     3830"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Se extraen las columnas 'items_count', 'user_id' e 'item_id'\n",
    "gasto_items = items[['items_count', 'user_id', 'item_id']]\n",
    "gasto_items.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se reserva el dataframe anterior y se realiza un nuevo dataframe auxiliar, pero con el precio de cada juego."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "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>price</th>\n",
       "      <th>item_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4.99</td>\n",
       "      <td>761140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.00</td>\n",
       "      <td>643980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0.00</td>\n",
       "      <td>670290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>0.99</td>\n",
       "      <td>767400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>3.99</td>\n",
       "      <td>772540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71028</th>\n",
       "      <td>1.99</td>\n",
       "      <td>745400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71032</th>\n",
       "      <td>1.99</td>\n",
       "      <td>773640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71036</th>\n",
       "      <td>4.99</td>\n",
       "      <td>733530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71039</th>\n",
       "      <td>1.99</td>\n",
       "      <td>610660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71042</th>\n",
       "      <td>4.99</td>\n",
       "      <td>658870</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>28842 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       price  item_id\n",
       "0       4.99   761140\n",
       "5       0.00   643980\n",
       "9       0.00   670290\n",
       "14      0.99   767400\n",
       "17      3.99   772540\n",
       "...      ...      ...\n",
       "71028   1.99   745400\n",
       "71032   1.99   773640\n",
       "71036   4.99   733530\n",
       "71039   1.99   610660\n",
       "71042   4.99   658870\n",
       "\n",
       "[28842 rows x 2 columns]"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "precio_juego = games[['price', 'id']]\n",
    "# Elimina los duplicados\n",
    "precio_juego = precio_juego.drop_duplicates(subset='id', keep='first')\n",
    "# Se renombra 'id' por 'item_id' para unir mas adelante\n",
    "precio_juego = precio_juego.rename(columns={'id':'item_id'})\n",
    "precio_juego"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finalmente, se unen los dos dataframe auxiliares para conformar un dataframe final llamado `df_gastos_items`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "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>items_count</th>\n",
       "      <th>user_id</th>\n",
       "      <th>item_id</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>277</td>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>10</td>\n",
       "      <td>9.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>277</td>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>30</td>\n",
       "      <td>4.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>277</td>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>300</td>\n",
       "      <td>9.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>277</td>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>240</td>\n",
       "      <td>19.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>277</td>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>3830</td>\n",
       "      <td>9.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3246370</th>\n",
       "      <td>7</td>\n",
       "      <td>76561198329548331</td>\n",
       "      <td>304930</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3246371</th>\n",
       "      <td>7</td>\n",
       "      <td>76561198329548331</td>\n",
       "      <td>227940</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3246372</th>\n",
       "      <td>7</td>\n",
       "      <td>76561198329548331</td>\n",
       "      <td>388490</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3246373</th>\n",
       "      <td>7</td>\n",
       "      <td>76561198329548331</td>\n",
       "      <td>521570</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3246374</th>\n",
       "      <td>7</td>\n",
       "      <td>76561198329548331</td>\n",
       "      <td>519140</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3246375 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         items_count            user_id  item_id  price\n",
       "0                277  76561197970982479       10   9.99\n",
       "1                277  76561197970982479       30   4.99\n",
       "2                277  76561197970982479      300   9.99\n",
       "3                277  76561197970982479      240  19.99\n",
       "4                277  76561197970982479     3830   9.99\n",
       "...              ...                ...      ...    ...\n",
       "3246370            7  76561198329548331   304930   0.00\n",
       "3246371            7  76561198329548331   227940   0.00\n",
       "3246372            7  76561198329548331   388490   0.00\n",
       "3246373            7  76561198329548331   521570   0.00\n",
       "3246374            7  76561198329548331   519140    NaN\n",
       "\n",
       "[3246375 rows x 4 columns]"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gasto_items = gasto_items.merge(precio_juego, on='item_id', how='left')\n",
    "gasto_items"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "price          15.201848\n",
       "items_count     0.000000\n",
       "user_id         0.000000\n",
       "item_id         0.000000\n",
       "dtype: float64"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gasto_items.isna().sum().sort_values(ascending= False)/len(items) * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3246375 entries, 0 to 3246374\n",
      "Data columns (total 4 columns):\n",
      " #   Column       Dtype  \n",
      "---  ------       -----  \n",
      " 0   items_count  int64  \n",
      " 1   user_id      object \n",
      " 2   item_id      int64  \n",
      " 3   price        float64\n",
      "dtypes: float64(1), int64(2), object(1)\n",
      "memory usage: 99.1+ MB\n"
     ]
    }
   ],
   "source": [
    "fill_precio = gasto_items['price'].fillna(0.0)\n",
    "# Se borra la columna original y se concatena la columna rellena con todo el dataframe\n",
    "gasto_items = pd.concat([gasto_items.drop('price', axis=1), fill_precio], axis=1)\n",
    "gasto_items.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "items_count    0.0\n",
       "user_id        0.0\n",
       "item_id        0.0\n",
       "price          0.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gasto_items.isna().sum().sort_values(ascending= False)/len(items) * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "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>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>--000--</td>\n",
       "      <td>182.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>--ace--</td>\n",
       "      <td>122.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>--ionex--</td>\n",
       "      <td>99.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-2SV-vuLB-Kg</td>\n",
       "      <td>234.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-404PageNotFound-</td>\n",
       "      <td>1154.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68398</th>\n",
       "      <td>zzonci</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68399</th>\n",
       "      <td>zzoptimuszz</td>\n",
       "      <td>4.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68400</th>\n",
       "      <td>zzydrax</td>\n",
       "      <td>99.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68401</th>\n",
       "      <td>zzyfo</td>\n",
       "      <td>484.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68402</th>\n",
       "      <td>zzzmidmiss</td>\n",
       "      <td>338.74</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>68403 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 user_id    price\n",
       "0                --000--   182.84\n",
       "1                --ace--   122.89\n",
       "2              --ionex--    99.93\n",
       "3           -2SV-vuLB-Kg   234.69\n",
       "4      -404PageNotFound-  1154.47\n",
       "...                  ...      ...\n",
       "68398             zzonci     0.00\n",
       "68399        zzoptimuszz     4.99\n",
       "68400            zzydrax    99.94\n",
       "68401              zzyfo   484.73\n",
       "68402         zzzmidmiss   338.74\n",
       "\n",
       "[68403 rows x 2 columns]"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Se elimina 'item_id'\n",
    "gasto_items = gasto_items.drop('item_id', axis=1)\n",
    "\n",
    "# Se agrupa por usuario sumando los precios de los juegos consumidos\n",
    "gasto_items_usuario = gasto_items.groupby('user_id')['price'].sum().reset_index()\n",
    "gasto_items_usuario"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Para volver a agregar los items consumidos por cada usuario se hace un dataframe auxiliar 'df_count_items' conteniendo la cantidad de items por usuario."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "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>items_count</th>\n",
       "      <th>user_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>277</td>\n",
       "      <td>76561197970982479</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>888</td>\n",
       "      <td>js41637</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>717</th>\n",
       "      <td>137</td>\n",
       "      <td>evcentric</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>821</th>\n",
       "      <td>328</td>\n",
       "      <td>Riot-Punch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>951</th>\n",
       "      <td>541</td>\n",
       "      <td>doctr</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3246360</th>\n",
       "      <td>5</td>\n",
       "      <td>76561198320038728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3246365</th>\n",
       "      <td>321</td>\n",
       "      <td>76561198320136420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3246367</th>\n",
       "      <td>4</td>\n",
       "      <td>ArkPlays7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3246369</th>\n",
       "      <td>22</td>\n",
       "      <td>76561198323066619</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3246370</th>\n",
       "      <td>7</td>\n",
       "      <td>76561198329548331</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>68403 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         items_count            user_id\n",
       "0                277  76561197970982479\n",
       "198              888            js41637\n",
       "717              137          evcentric\n",
       "821              328         Riot-Punch\n",
       "951              541              doctr\n",
       "...              ...                ...\n",
       "3246360            5  76561198320038728\n",
       "3246365          321  76561198320136420\n",
       "3246367            4          ArkPlays7\n",
       "3246369           22  76561198323066619\n",
       "3246370            7  76561198329548331\n",
       "\n",
       "[68403 rows x 2 columns]"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conteo_items_usuario = gasto_items[['items_count', 'user_id']]\n",
    "\n",
    "# se eliminan los duplicados\n",
    "conteo_items_usuario = conteo_items_usuario.drop_duplicates(subset='user_id', keep='first')\n",
    "conteo_items_usuario"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "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>items_count</th>\n",
       "      <th>user_id</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>58</td>\n",
       "      <td>--000--</td>\n",
       "      <td>182.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>44</td>\n",
       "      <td>--ace--</td>\n",
       "      <td>122.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>23</td>\n",
       "      <td>--ionex--</td>\n",
       "      <td>99.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>68</td>\n",
       "      <td>-2SV-vuLB-Kg</td>\n",
       "      <td>234.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>149</td>\n",
       "      <td>-404PageNotFound-</td>\n",
       "      <td>1154.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68398</th>\n",
       "      <td>5</td>\n",
       "      <td>zzonci</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68399</th>\n",
       "      <td>61</td>\n",
       "      <td>zzoptimuszz</td>\n",
       "      <td>4.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68400</th>\n",
       "      <td>13</td>\n",
       "      <td>zzydrax</td>\n",
       "      <td>99.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68401</th>\n",
       "      <td>84</td>\n",
       "      <td>zzyfo</td>\n",
       "      <td>484.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68402</th>\n",
       "      <td>69</td>\n",
       "      <td>zzzmidmiss</td>\n",
       "      <td>338.74</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>68403 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       items_count            user_id    price\n",
       "0               58            --000--   182.84\n",
       "1               44            --ace--   122.89\n",
       "2               23          --ionex--    99.93\n",
       "3               68       -2SV-vuLB-Kg   234.69\n",
       "4              149  -404PageNotFound-  1154.47\n",
       "...            ...                ...      ...\n",
       "68398            5             zzonci     0.00\n",
       "68399           61        zzoptimuszz     4.99\n",
       "68400           13            zzydrax    99.94\n",
       "68401           84              zzyfo   484.73\n",
       "68402           69         zzzmidmiss   338.74\n",
       "\n",
       "[68403 rows x 3 columns]"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gasto_items = conteo_items_usuario.merge(gasto_items_usuario, on='user_id', how='right')\n",
    "gasto_items"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Playtime_forever por usuario\n",
    "\n",
    "Se busca generar un dataframe que contenga por género de videojuego, el tiempo jugado por cada usuario con su id y url del perfil. Para ello, se realizarán algunos pasos generando algunos dataframes auxiliares.  \n",
    "En primer lugar, se extrae de `df_items` las columnas 'playtime_forever', 'user_id' y 'item_id' y se reserva."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['item_id', 'item_name', 'playtime_forever', 'steam_id', 'items_count',\n",
       "       'user_id'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "items.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "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>playtime_forever</th>\n",
       "      <th>user_id</th>\n",
       "      <th>item_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.10</td>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.12</td>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>78.88</td>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>30.88</td>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.55</td>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>3830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3246370</th>\n",
       "      <td>11.28</td>\n",
       "      <td>76561198329548331</td>\n",
       "      <td>304930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3246371</th>\n",
       "      <td>0.72</td>\n",
       "      <td>76561198329548331</td>\n",
       "      <td>227940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3246372</th>\n",
       "      <td>0.05</td>\n",
       "      <td>76561198329548331</td>\n",
       "      <td>388490</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3246373</th>\n",
       "      <td>0.07</td>\n",
       "      <td>76561198329548331</td>\n",
       "      <td>521570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3246374</th>\n",
       "      <td>0.05</td>\n",
       "      <td>76561198329548331</td>\n",
       "      <td>519140</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3246375 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         playtime_forever            user_id  item_id\n",
       "0                    0.10  76561197970982479       10\n",
       "1                    0.12  76561197970982479       30\n",
       "2                   78.88  76561197970982479      300\n",
       "3                   30.88  76561197970982479      240\n",
       "4                    5.55  76561197970982479     3830\n",
       "...                   ...                ...      ...\n",
       "3246370             11.28  76561198329548331   304930\n",
       "3246371              0.72  76561198329548331   227940\n",
       "3246372              0.05  76561198329548331   388490\n",
       "3246373              0.07  76561198329548331   521570\n",
       "3246374              0.05  76561198329548331   519140\n",
       "\n",
       "[3246375 rows x 3 columns]"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tiempo_total_usuario_item = items[['playtime_forever', 'user_id', 'item_id']]\n",
    "tiempo_total_usuario_item"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Del dataframe `df_games` se extrae el 'id' del item y el género."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "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>genres</th>\n",
       "      <th>item_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Action</td>\n",
       "      <td>761140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Casual</td>\n",
       "      <td>761140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Indie</td>\n",
       "      <td>761140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Simulation</td>\n",
       "      <td>761140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Strategy</td>\n",
       "      <td>761140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71039</th>\n",
       "      <td>Indie</td>\n",
       "      <td>610660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71040</th>\n",
       "      <td>Racing</td>\n",
       "      <td>610660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71041</th>\n",
       "      <td>Simulation</td>\n",
       "      <td>610660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71042</th>\n",
       "      <td>Casual</td>\n",
       "      <td>658870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71043</th>\n",
       "      <td>Indie</td>\n",
       "      <td>658870</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>71044 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           genres  item_id\n",
       "0          Action   761140\n",
       "1          Casual   761140\n",
       "2           Indie   761140\n",
       "3      Simulation   761140\n",
       "4        Strategy   761140\n",
       "...           ...      ...\n",
       "71039       Indie   610660\n",
       "71040      Racing   610660\n",
       "71041  Simulation   610660\n",
       "71042      Casual   658870\n",
       "71043       Indie   658870\n",
       "\n",
       "[71044 rows x 2 columns]"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item_genero = games[['genres', 'id']]\n",
    "# Se renombra la columna 'id' para unirla con el dataframe anterior\n",
    "item_genero = item_genero.rename(columns={'id':'item_id'})\n",
    "item_genero"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ahora se unen las dos tablas anteriores para obtener los generos de todos los usuarios con sus id y url."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "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>playtime_forever</th>\n",
       "      <th>user_id</th>\n",
       "      <th>item_id</th>\n",
       "      <th>genres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.10</td>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>10</td>\n",
       "      <td>Action</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>323</th>\n",
       "      <td>3.67</td>\n",
       "      <td>js41637</td>\n",
       "      <td>300</td>\n",
       "      <td>Action</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1485</th>\n",
       "      <td>15.38</td>\n",
       "      <td>evcentric</td>\n",
       "      <td>1200</td>\n",
       "      <td>Action</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1723</th>\n",
       "      <td>1.12</td>\n",
       "      <td>Riot-Punch</td>\n",
       "      <td>300</td>\n",
       "      <td>Action</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1934</th>\n",
       "      <td>18.85</td>\n",
       "      <td>doctr</td>\n",
       "      <td>300</td>\n",
       "      <td>Action</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6805829</th>\n",
       "      <td>0.63</td>\n",
       "      <td>76561198320038728</td>\n",
       "      <td>304930</td>\n",
       "      <td>Action</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6805847</th>\n",
       "      <td>1.72</td>\n",
       "      <td>76561198320136420</td>\n",
       "      <td>273350</td>\n",
       "      <td>Action</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6805849</th>\n",
       "      <td>75.95</td>\n",
       "      <td>ArkPlays7</td>\n",
       "      <td>730</td>\n",
       "      <td>Action</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6805855</th>\n",
       "      <td>0.13</td>\n",
       "      <td>76561198323066619</td>\n",
       "      <td>417860</td>\n",
       "      <td>Adventure</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6805860</th>\n",
       "      <td>11.28</td>\n",
       "      <td>76561198329548331</td>\n",
       "      <td>304930</td>\n",
       "      <td>Action</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>67848 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         playtime_forever            user_id  item_id     genres\n",
       "0                    0.10  76561197970982479       10     Action\n",
       "323                  3.67            js41637      300     Action\n",
       "1485                15.38          evcentric     1200     Action\n",
       "1723                 1.12         Riot-Punch      300     Action\n",
       "1934                18.85              doctr      300     Action\n",
       "...                   ...                ...      ...        ...\n",
       "6805829              0.63  76561198320038728   304930     Action\n",
       "6805847              1.72  76561198320136420   273350     Action\n",
       "6805849             75.95          ArkPlays7      730     Action\n",
       "6805855              0.13  76561198323066619   417860  Adventure\n",
       "6805860             11.28  76561198329548331   304930     Action\n",
       "\n",
       "[67848 rows x 4 columns]"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tiempo_juego_genero = tiempo_total_usuario_item.merge(item_genero, on='item_id')\n",
    "tiempo_juego_genero = tiempo_juego_genero.drop_duplicates(subset='user_id')\n",
    "\n",
    "tiempo_juego_genero"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "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>genres</th>\n",
       "      <th>user_id</th>\n",
       "      <th>playtime_forever</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Action</td>\n",
       "      <td>--ionex--</td>\n",
       "      <td>1.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Action</td>\n",
       "      <td>-2SV-vuLB-Kg</td>\n",
       "      <td>1.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Action</td>\n",
       "      <td>-404PageNotFound-</td>\n",
       "      <td>66.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Action</td>\n",
       "      <td>-AnimeIsMyThing-</td>\n",
       "      <td>101.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Action</td>\n",
       "      <td>-Azsael-</td>\n",
       "      <td>24.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67843</th>\n",
       "      <td>Utilities</td>\n",
       "      <td>76561198100513560</td>\n",
       "      <td>0.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67844</th>\n",
       "      <td>Utilities</td>\n",
       "      <td>76561198216710301</td>\n",
       "      <td>1.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67845</th>\n",
       "      <td>Utilities</td>\n",
       "      <td>76561198262719220</td>\n",
       "      <td>0.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67846</th>\n",
       "      <td>Utilities</td>\n",
       "      <td>ZertixCSGO</td>\n",
       "      <td>27.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67847</th>\n",
       "      <td>Utilities</td>\n",
       "      <td>ZildGOD</td>\n",
       "      <td>1.60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>67848 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          genres            user_id  playtime_forever\n",
       "0         Action          --ionex--              1.75\n",
       "1         Action       -2SV-vuLB-Kg              1.78\n",
       "2         Action  -404PageNotFound-             66.05\n",
       "3         Action   -AnimeIsMyThing-            101.98\n",
       "4         Action           -Azsael-             24.27\n",
       "...          ...                ...               ...\n",
       "67843  Utilities  76561198100513560              0.02\n",
       "67844  Utilities  76561198216710301              1.97\n",
       "67845  Utilities  76561198262719220              0.02\n",
       "67846  Utilities         ZertixCSGO             27.15\n",
       "67847  Utilities            ZildGOD              1.60\n",
       "\n",
       "[67848 rows x 3 columns]"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Se agrupa por usuario y se suma el tiempo de juego\n",
    "genero_usuario_agrupado = tiempo_juego_genero.groupby(['genres', 'user_id'])['playtime_forever'].sum().reset_index()\n",
    "\n",
    "genero_usuario_agrupado"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tiempo de juego y año de lanzamiento"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se crea un dataframe donde se encuentre el id_item y su año  de lanzamiento con las información del usuario y sus horas de juego"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "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>playtime_forever</th>\n",
       "      <th>user_id</th>\n",
       "      <th>item_id</th>\n",
       "      <th>genres</th>\n",
       "      <th>release_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.10</td>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>10</td>\n",
       "      <td>Action</td>\n",
       "      <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.67</td>\n",
       "      <td>js41637</td>\n",
       "      <td>300</td>\n",
       "      <td>Action</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>15.38</td>\n",
       "      <td>evcentric</td>\n",
       "      <td>1200</td>\n",
       "      <td>Action</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.12</td>\n",
       "      <td>Riot-Punch</td>\n",
       "      <td>300</td>\n",
       "      <td>Action</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>18.85</td>\n",
       "      <td>doctr</td>\n",
       "      <td>300</td>\n",
       "      <td>Action</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113486</th>\n",
       "      <td>0.63</td>\n",
       "      <td>76561198320038728</td>\n",
       "      <td>304930</td>\n",
       "      <td>Action</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113491</th>\n",
       "      <td>1.72</td>\n",
       "      <td>76561198320136420</td>\n",
       "      <td>273350</td>\n",
       "      <td>Action</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113493</th>\n",
       "      <td>75.95</td>\n",
       "      <td>ArkPlays7</td>\n",
       "      <td>730</td>\n",
       "      <td>Action</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113494</th>\n",
       "      <td>0.13</td>\n",
       "      <td>76561198323066619</td>\n",
       "      <td>417860</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113499</th>\n",
       "      <td>11.28</td>\n",
       "      <td>76561198329548331</td>\n",
       "      <td>304930</td>\n",
       "      <td>Action</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>67848 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        playtime_forever            user_id  item_id     genres  release_year\n",
       "0                   0.10  76561197970982479       10     Action          2000\n",
       "1                   3.67            js41637      300     Action          2010\n",
       "2                  15.38          evcentric     1200     Action          2006\n",
       "3                   1.12         Riot-Punch      300     Action          2010\n",
       "4                  18.85              doctr      300     Action          2010\n",
       "...                  ...                ...      ...        ...           ...\n",
       "113486              0.63  76561198320038728   304930     Action          2017\n",
       "113491              1.72  76561198320136420   273350     Action          2015\n",
       "113493             75.95          ArkPlays7      730     Action          2012\n",
       "113494              0.13  76561198323066619   417860  Adventure          2015\n",
       "113499             11.28  76561198329548331   304930     Action          2017\n",
       "\n",
       "[67848 rows x 5 columns]"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "anio_lanzamiento = games[['id', 'release_year']]\n",
    "\n",
    "# Se renombra la columna 'id' para unirla con el dataframe anterior\n",
    "anio_lanzamiento = anio_lanzamiento.rename(columns={'id':'item_id'})\n",
    "\n",
    "\n",
    "# Se une el lanzamiento con el item de reviews\n",
    "user_time_year = tiempo_juego_genero.merge(anio_lanzamiento,how='left', on='item_id')\n",
    "\n",
    "user_time_year = user_time_year.drop_duplicates()\n",
    "user_time_year"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ranking de géneros por tiempo de juego\n",
    "\n",
    "En este punto lo que se busca es un dataframe auxiliar que contenga el ranking de los géneros de videojuegos en función del tiempo jugados de cada uno. Se aprovecha que ya se hizo para el punto anterior un dataframe auxiliar que contiene los géneros y el tiempo jugado, por lo que se usa el mismo. Primero se agrupa por género, luego se ordena y se agrega una columna de ranking."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "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>genres</th>\n",
       "      <th>playtime_forever</th>\n",
       "      <th>ranking</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Action</td>\n",
       "      <td>51006463.49</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Indie</td>\n",
       "      <td>24583856.80</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>RPG</td>\n",
       "      <td>17075692.62</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Adventure</td>\n",
       "      <td>14854328.32</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Simulation</td>\n",
       "      <td>14250062.30</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Strategy</td>\n",
       "      <td>10844877.05</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Free to Play</td>\n",
       "      <td>9847852.69</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Massively Multiplayer</td>\n",
       "      <td>7303609.42</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Casual</td>\n",
       "      <td>4103446.59</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Early Access</td>\n",
       "      <td>2611348.71</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Sports</td>\n",
       "      <td>1081974.71</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Racing</td>\n",
       "      <td>1035744.31</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Utilities</td>\n",
       "      <td>74564.55</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Design &amp; Illustration</td>\n",
       "      <td>67928.62</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Web Publishing</td>\n",
       "      <td>46013.78</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Video Production</td>\n",
       "      <td>39798.50</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Animation &amp; Modeling</td>\n",
       "      <td>33243.63</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Software Training</td>\n",
       "      <td>9333.49</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Audio Production</td>\n",
       "      <td>9087.19</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Education</td>\n",
       "      <td>6902.24</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Photo Editing</td>\n",
       "      <td>1908.90</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   genres  playtime_forever  ranking\n",
       "0                  Action       51006463.49        1\n",
       "9                   Indie       24583856.80        2\n",
       "12                    RPG       17075692.62        3\n",
       "1               Adventure       14854328.32        4\n",
       "14             Simulation       14250062.30        5\n",
       "17               Strategy       10844877.05        6\n",
       "8            Free to Play        9847852.69        7\n",
       "10  Massively Multiplayer        7303609.42        8\n",
       "4                  Casual        4103446.59        9\n",
       "6            Early Access        2611348.71       10\n",
       "16                 Sports        1081974.71       11\n",
       "13                 Racing        1035744.31       12\n",
       "18              Utilities          74564.55       13\n",
       "5   Design & Illustration          67928.62       14\n",
       "20         Web Publishing          46013.78       15\n",
       "19       Video Production          39798.50       16\n",
       "2    Animation & Modeling          33243.63       17\n",
       "15      Software Training           9333.49       18\n",
       "3        Audio Production           9087.19       19\n",
       "7               Education           6902.24       20\n",
       "11          Photo Editing           1908.90       21"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ranking_genero = genero_usuario_agrupado.groupby('genres')['playtime_forever'].sum().reset_index()\n",
    "# Se ordena por 'playtime_horas'\n",
    "\n",
    "ranking_genero = ranking_genero.sort_values(by='playtime_forever', ascending=False)\n",
    "\n",
    "# Agregar una columna con la posición del ranking\n",
    "ranking_genero['ranking'] = ranking_genero['playtime_forever'].rank(ascending=False).astype(int)\n",
    "ranking_genero"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cantidad de items y contenido free por desarrollador\n",
    "\n",
    "En este punto se busca un dataframe que contenga para cada desarrollador de juegos, los items que desarrolla, el año de lanzamiento y el precio de cada uno."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "precio_anual_developer = games[['price', 'release_year', 'developer', 'id']]\n",
    "\n",
    "# Se renombra la columna 'id' para unirla con el dataframe anterior\n",
    "precio_anual_developer = precio_anual_developer.rename(columns={'id':'item_id'})\n",
    "\n",
    "# se eliminan los duplicados\n",
    "items_developer = precio_anual_developer.drop_duplicates()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Desarrolladores top según 'reviews_recommend' y 'sentiment_analysis'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "sentimiento_item = reviews[['reviews_item_id','reviews_recommend','sentiment_analysis']]\n",
    "\n",
    "# Se renombra la columna 'id' para unirla con el dataframe anterior\n",
    "sentimiento_item = sentimiento_item.rename(columns={'reviews_item_id':'item_id'})\n",
    "\n",
    "\n",
    "# Se une el lanzamiento con el item de reviews\n",
    "top_dev = items_developer.merge(sentimiento_item,how='left', on='item_id')\n",
    "\n",
    "# Elimino los desarrolladores que no tienen un item con review\n",
    "top_dev = top_dev.dropna(subset=['reviews_recommend'])\n",
    "\n",
    "# Elimino duplicados\n",
    "top_dev = top_dev.drop_duplicates()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "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>price</th>\n",
       "      <th>release_year</th>\n",
       "      <th>developer</th>\n",
       "      <th>item_id</th>\n",
       "      <th>reviews_recommend</th>\n",
       "      <th>sentiment_analysis</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>9.99</td>\n",
       "      <td>1997</td>\n",
       "      <td>Stainless Games Ltd</td>\n",
       "      <td>282010</td>\n",
       "      <td>True</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>9.99</td>\n",
       "      <td>1998</td>\n",
       "      <td>Valve</td>\n",
       "      <td>70</td>\n",
       "      <td>True</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>9.99</td>\n",
       "      <td>1998</td>\n",
       "      <td>Valve</td>\n",
       "      <td>70</td>\n",
       "      <td>True</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>9.99</td>\n",
       "      <td>1998</td>\n",
       "      <td>Valve</td>\n",
       "      <td>70</td>\n",
       "      <td>True</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>9.99</td>\n",
       "      <td>1998</td>\n",
       "      <td>Valve</td>\n",
       "      <td>70</td>\n",
       "      <td>False</td>\n",
       "      <td>2.0</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>74435</th>\n",
       "      <td>9.99</td>\n",
       "      <td>2004</td>\n",
       "      <td>Valve</td>\n",
       "      <td>80</td>\n",
       "      <td>True</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74436</th>\n",
       "      <td>9.99</td>\n",
       "      <td>2004</td>\n",
       "      <td>Valve</td>\n",
       "      <td>80</td>\n",
       "      <td>True</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74440</th>\n",
       "      <td>9.99</td>\n",
       "      <td>2004</td>\n",
       "      <td>Valve</td>\n",
       "      <td>80</td>\n",
       "      <td>True</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74441</th>\n",
       "      <td>9.99</td>\n",
       "      <td>2004</td>\n",
       "      <td>Valve</td>\n",
       "      <td>80</td>\n",
       "      <td>False</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74446</th>\n",
       "      <td>9.99</td>\n",
       "      <td>2004</td>\n",
       "      <td>Valve</td>\n",
       "      <td>80</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6441 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       price  release_year            developer  item_id reviews_recommend  \\\n",
       "21      9.99          1997  Stainless Games Ltd   282010              True   \n",
       "22      9.99          1998                Valve       70              True   \n",
       "26      9.99          1998                Valve       70              True   \n",
       "28      9.99          1998                Valve       70              True   \n",
       "58      9.99          1998                Valve       70             False   \n",
       "...      ...           ...                  ...      ...               ...   \n",
       "74435   9.99          2004                Valve       80              True   \n",
       "74436   9.99          2004                Valve       80              True   \n",
       "74440   9.99          2004                Valve       80              True   \n",
       "74441   9.99          2004                Valve       80             False   \n",
       "74446   9.99          2004                Valve       80             False   \n",
       "\n",
       "       sentiment_analysis  \n",
       "21                    1.0  \n",
       "22                    1.0  \n",
       "26                    0.0  \n",
       "28                    2.0  \n",
       "58                    2.0  \n",
       "...                   ...  \n",
       "74435                 1.0  \n",
       "74436                 2.0  \n",
       "74440                 0.0  \n",
       "74441                 1.0  \n",
       "74446                 0.0  \n",
       "\n",
       "[6441 rows x 6 columns]"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top_dev"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 'release_anio' en `df_reviews`\n",
    "\n",
    "En este punto se agrega el año de lanzamiento de un juego al dataframe `df_reviews`. En primer lugar se extraen las columnas de id del juego y el año de lanzamiento del mismo, se borran los duplicados y luego se unen con el dataframe de reviews."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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_item_id</th>\n",
       "      <th>reviews_helpful</th>\n",
       "      <th>reviews_recommend</th>\n",
       "      <th>posted_year</th>\n",
       "      <th>sentiment_analysis</th>\n",
       "      <th>release_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td>1250</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>http://steamcommunity.com/id/evcentric</td>\n",
       "      <td>248820</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>2013</td>\n",
       "      <td>2</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>doctr</td>\n",
       "      <td>http://steamcommunity.com/id/doctr</td>\n",
       "      <td>250320</td>\n",
       "      <td>2 of 2 people (100%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>2013</td>\n",
       "      <td>2</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>maplemage</td>\n",
       "      <td>http://steamcommunity.com/id/maplemage</td>\n",
       "      <td>211420</td>\n",
       "      <td>35 of 43 people (81%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>2014</td>\n",
       "      <td>1</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Wackky</td>\n",
       "      <td>http://steamcommunity.com/id/Wackky</td>\n",
       "      <td>249130</td>\n",
       "      <td>7 of 8 people (88%) found this review helpful</td>\n",
       "      <td>True</td>\n",
       "      <td>2014</td>\n",
       "      <td>0</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>48685</th>\n",
       "      <td>kushikushigani</td>\n",
       "      <td>http://steamcommunity.com/id/kushikushigani</td>\n",
       "      <td>332310</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48686</th>\n",
       "      <td>How51</td>\n",
       "      <td>http://steamcommunity.com/id/How51</td>\n",
       "      <td>440</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>2014</td>\n",
       "      <td>1</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48687</th>\n",
       "      <td>76561198111410893</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198111...</td>\n",
       "      <td>304930</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>2014</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48688</th>\n",
       "      <td>zaza147</td>\n",
       "      <td>http://steamcommunity.com/id/zaza147</td>\n",
       "      <td>265630</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>2015</td>\n",
       "      <td>2</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48689</th>\n",
       "      <td>lifeonhigh</td>\n",
       "      <td>http://steamcommunity.com/id/lifeonhigh</td>\n",
       "      <td>304050</td>\n",
       "      <td>No ratings yet</td>\n",
       "      <td>True</td>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>48690 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 user_id                                           user_url  \\\n",
       "0      76561197970982479  http://steamcommunity.com/profiles/76561197970...   \n",
       "1              evcentric             http://steamcommunity.com/id/evcentric   \n",
       "2                  doctr                 http://steamcommunity.com/id/doctr   \n",
       "3              maplemage             http://steamcommunity.com/id/maplemage   \n",
       "4                 Wackky                http://steamcommunity.com/id/Wackky   \n",
       "...                  ...                                                ...   \n",
       "48685     kushikushigani        http://steamcommunity.com/id/kushikushigani   \n",
       "48686              How51                 http://steamcommunity.com/id/How51   \n",
       "48687  76561198111410893  http://steamcommunity.com/profiles/76561198111...   \n",
       "48688            zaza147               http://steamcommunity.com/id/zaza147   \n",
       "48689         lifeonhigh            http://steamcommunity.com/id/lifeonhigh   \n",
       "\n",
       "       reviews_item_id                                  reviews_helpful  \\\n",
       "0                 1250                                   No ratings yet   \n",
       "1               248820                                   No ratings yet   \n",
       "2               250320   2 of 2 people (100%) found this review helpful   \n",
       "3               211420  35 of 43 people (81%) found this review helpful   \n",
       "4               249130    7 of 8 people (88%) found this review helpful   \n",
       "...                ...                                              ...   \n",
       "48685           332310                                   No ratings yet   \n",
       "48686              440                                   No ratings yet   \n",
       "48687           304930                                   No ratings yet   \n",
       "48688           265630                                   No ratings yet   \n",
       "48689           304050                                   No ratings yet   \n",
       "\n",
       "       reviews_recommend  posted_year  sentiment_analysis  release_year  \n",
       "0                   True         2011                   1          2009  \n",
       "1                   True         2013                   2          2013  \n",
       "2                   True         2013                   2          2013  \n",
       "3                   True         2014                   1          2012  \n",
       "4                   True         2014                   0          2013  \n",
       "...                  ...          ...                 ...           ...  \n",
       "48685               True         2015                   1          2017  \n",
       "48686               True         2014                   1          2007  \n",
       "48687               True         2014                   1          2017  \n",
       "48688               True         2015                   2          2014  \n",
       "48689               True         2015                   1          2015  \n",
       "\n",
       "[48690 rows x 8 columns]"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lanzamiento_item = games[['id', 'release_year']]\n",
    "\n",
    "# Se renombra la columna 'id' para unirla con el dataframe anterior\n",
    "lanzamiento_item = lanzamiento_item.rename(columns={'id':'reviews_item_id'})\n",
    "\n",
    "# se eliminan los duplicados\n",
    "lanzamiento_item = lanzamiento_item.drop_duplicates()\n",
    "\n",
    "# Se une el lanzamiento con el item de reviews\n",
    "reviews = reviews.merge(lanzamiento_item, on='reviews_item_id')\n",
    "reviews"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se guardan los dataframes en CSV "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = [reviews, gasto_items, ranking_genero, tiempo_juego_genero, items_developer, user_time_year, top_dev]\n",
    "# Nombres correspondientes a cada DataFrame\n",
    "names = ['02-user-reviews', '04-gasto-items', '05-ranking-genero', '06-tiempo-juego-genero', '07-items-developer', '08-user-time-year', '09-top-dev']\n",
    "\n",
    "for df, name in zip(dfs, names):\n",
    "    archivo = f'data/{name}.csv'\n",
    "    df.to_csv(archivo, index=False, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se pasan a parquet para optimizar el espacio para el deploy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "for df, name in zip(dfs, names):\n",
    "    archivo = f'data/{name}.parquet'\n",
    "    pq.write_table(pa.Table.from_pandas(df), archivo)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "env",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}