01-etl-games.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ETL del dataset `steam_games`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "En esta jupyter notebook se desarrolla la extracción, transformación y carga (ETL) del conjunto de datos `steam_games.json.gz`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Descripción de Columnas en steam_games.gz.json\n",
    "\n",
    "| Columna        | Descripción                                  | Ejemplo                                                         |\n",
    "|----------------|----------------------------------------------|-----------------------------------------------------------------|\n",
    "| publisher      | Empresa publicadora del contenido             | [Ubisoft, Dovetail Games - Trains, Degica]                      |\n",
    "| genres         | Género del contenido                         | [Action, Adventure, Racing, Simulation, Strategy]               |\n",
    "| app_name       | Nombre del contenido                          | [Warzone, Soundtrack, Puzzle Blocks]                            |\n",
    "| title          | Título del contenido                          | [The Dream Machine: Chapter 4, Fate/EXTELLA - Sweet Room Dream, Fate/EXTELLA - Charming Bunny] |\n",
    "| url            | URL de publicación del contenido              | [http://store.steampowered.com/app/761140/Lost_Summoner_Kitty/] |\n",
    "| release_date   | Fecha de lanzamiento                          | [2018-01-04]                                                    |\n",
    "| tags           | Etiquetas de contenido                       | [Simulation, Indie, Action, Adventure, Funny, Open World, First-Person, Sandbox, Free to Play] |\n",
    "| reviews_url    | URL de reseñas del contenido                  | [http://steamcommunity.com/app/681550/reviews/?browsefilter=mostrecent&p=1] |\n",
    "| specs          | Especificaciones                             | [Multi-player, Co-op, Cross-Platform Multiplayer, Downloadable Content] |\n",
    "| price          | Precio del contenido                          | [4.99, 9.99, Free to Use, Free to Play]                         |\n",
    "| early_access   | Acceso temprano                              | [False, True]                                                  |\n",
    "| id             | Identificador único de contenido              | [761140, 643980, 670290]                                       |\n",
    "| developer      | Desarrollador                                | [Kotoshiro, Secret Level SRL, Poolians.com]                    |\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import gzip  # Para trabajar con archivos comprimidos en formato Gzip\n",
    "import json  # Para trabajar con datos en formato JSON\n",
    "import matplotlib.pyplot as plt  # Para crear gráficos y visualizaciones\n",
    "import numpy as np  # Para operaciones matemáticas eficientes y manipulación de arrays\n",
    "import pandas as pd  # Para el análisis y manipulación de datos en forma de DataFrames\n",
    "import pyarrow as pa  # Herramientas para trabajar con datos en formato de flecha (Arrow)\n",
    "import pyarrow.parquet as pq  # Para trabajar con archivos en formato Parquet\n",
    "import seaborn as sns  # Biblioteca de visualización de datos basada en matplotlib"
   ]
  },
  {
   "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>publisher</th>\n",
       "      <th>genres</th>\n",
       "      <th>app_name</th>\n",
       "      <th>title</th>\n",
       "      <th>url</th>\n",
       "      <th>release_date</th>\n",
       "      <th>tags</th>\n",
       "      <th>reviews_url</th>\n",
       "      <th>specs</th>\n",
       "      <th>price</th>\n",
       "      <th>early_access</th>\n",
       "      <th>id</th>\n",
       "      <th>developer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  publisher genres app_name title  url release_date tags reviews_url specs  \\\n",
       "0       NaN    NaN      NaN   NaN  NaN          NaN  NaN         NaN   NaN   \n",
       "1       NaN    NaN      NaN   NaN  NaN          NaN  NaN         NaN   NaN   \n",
       "2       NaN    NaN      NaN   NaN  NaN          NaN  NaN         NaN   NaN   \n",
       "3       NaN    NaN      NaN   NaN  NaN          NaN  NaN         NaN   NaN   \n",
       "4       NaN    NaN      NaN   NaN  NaN          NaN  NaN         NaN   NaN   \n",
       "\n",
       "  price early_access   id developer  \n",
       "0   NaN          NaN  NaN       NaN  \n",
       "1   NaN          NaN  NaN       NaN  \n",
       "2   NaN          NaN  NaN       NaN  \n",
       "3   NaN          NaN  NaN       NaN  \n",
       "4   NaN          NaN  NaN       NaN  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Leemos el archivo gz que contiene el json y lo guardamos en una lista\n",
    "row = []  # creamos una lista vacía para ir agregando las filas del archivo json\n",
    "with gzip.open(\"data/steam_games.json.gz\", 'rt', encoding='utf-8') as file:\n",
    "    for line in file.readlines():  # leemos línea por línea\n",
    "        data = json.loads(line)\n",
    "        row.append(data)\n",
    "\n",
    "# Convertimos la lista en un DataFrame\n",
    "games = pd.DataFrame(row)\n",
    "games.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(120445, 13)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "games.shape # Observamos el tamaño del dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ahora es necesario observar el número de nulos presentes en el dataset, para un posible imputación o eliminación de datos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "publisher       80.004982\n",
       "developer       76.058782\n",
       "genres          76.045498\n",
       "release_date    75.035909\n",
       "title           75.021794\n",
       "price           74.463033\n",
       "specs           73.876043\n",
       "tags            73.455104\n",
       "app_name        73.321433\n",
       "reviews_url     73.321433\n",
       "id              73.321433\n",
       "url             73.319773\n",
       "early_access    73.319773\n",
       "dtype: float64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "games.isna().sum().sort_values(ascending= False)/len(games) * 100"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Conclusión:\n",
    "\n",
    "* Para la totalidad de columnas se presenta un valor mayor del 70% de vacíos\n",
    "* No se puede concluir la eliminación de columnas ya que todos presentan alto procentaje de nulos\n",
    "* Se eliminarán las filas que contengas el todas de sus registros vaciós"
   ]
  },
  {
   "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>publisher</th>\n",
       "      <th>genres</th>\n",
       "      <th>app_name</th>\n",
       "      <th>title</th>\n",
       "      <th>url</th>\n",
       "      <th>release_date</th>\n",
       "      <th>tags</th>\n",
       "      <th>reviews_url</th>\n",
       "      <th>specs</th>\n",
       "      <th>price</th>\n",
       "      <th>early_access</th>\n",
       "      <th>id</th>\n",
       "      <th>developer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kotoshiro</td>\n",
       "      <td>[Action, Casual, Indie, Simulation, Strategy]</td>\n",
       "      <td>Lost Summoner Kitty</td>\n",
       "      <td>Lost Summoner Kitty</td>\n",
       "      <td>http://store.steampowered.com/app/761140/Lost_...</td>\n",
       "      <td>2018-01-04</td>\n",
       "      <td>[Strategy, Action, Indie, Casual, Simulation]</td>\n",
       "      <td>http://steamcommunity.com/app/761140/reviews/?...</td>\n",
       "      <td>[Single-player]</td>\n",
       "      <td>4.99</td>\n",
       "      <td>False</td>\n",
       "      <td>761140</td>\n",
       "      <td>Kotoshiro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Making Fun, Inc.</td>\n",
       "      <td>[Free to Play, Indie, RPG, Strategy]</td>\n",
       "      <td>Ironbound</td>\n",
       "      <td>Ironbound</td>\n",
       "      <td>http://store.steampowered.com/app/643980/Ironb...</td>\n",
       "      <td>2018-01-04</td>\n",
       "      <td>[Free to Play, Strategy, Indie, RPG, Card Game...</td>\n",
       "      <td>http://steamcommunity.com/app/643980/reviews/?...</td>\n",
       "      <td>[Single-player, Multi-player, Online Multi-Pla...</td>\n",
       "      <td>Free To Play</td>\n",
       "      <td>False</td>\n",
       "      <td>643980</td>\n",
       "      <td>Secret Level SRL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Poolians.com</td>\n",
       "      <td>[Casual, Free to Play, Indie, Simulation, Sports]</td>\n",
       "      <td>Real Pool 3D - Poolians</td>\n",
       "      <td>Real Pool 3D - Poolians</td>\n",
       "      <td>http://store.steampowered.com/app/670290/Real_...</td>\n",
       "      <td>2017-07-24</td>\n",
       "      <td>[Free to Play, Simulation, Sports, Casual, Ind...</td>\n",
       "      <td>http://steamcommunity.com/app/670290/reviews/?...</td>\n",
       "      <td>[Single-player, Multi-player, Online Multi-Pla...</td>\n",
       "      <td>Free to Play</td>\n",
       "      <td>False</td>\n",
       "      <td>670290</td>\n",
       "      <td>Poolians.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>彼岸领域</td>\n",
       "      <td>[Action, Adventure, Casual]</td>\n",
       "      <td>弹炸人2222</td>\n",
       "      <td>弹炸人2222</td>\n",
       "      <td>http://store.steampowered.com/app/767400/2222/</td>\n",
       "      <td>2017-12-07</td>\n",
       "      <td>[Action, Adventure, Casual]</td>\n",
       "      <td>http://steamcommunity.com/app/767400/reviews/?...</td>\n",
       "      <td>[Single-player]</td>\n",
       "      <td>0.99</td>\n",
       "      <td>False</td>\n",
       "      <td>767400</td>\n",
       "      <td>彼岸领域</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Log Challenge</td>\n",
       "      <td>NaN</td>\n",
       "      <td>http://store.steampowered.com/app/773570/Log_C...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Action, Indie, Casual, Sports]</td>\n",
       "      <td>http://steamcommunity.com/app/773570/reviews/?...</td>\n",
       "      <td>[Single-player, Full controller support, HTC V...</td>\n",
       "      <td>2.99</td>\n",
       "      <td>False</td>\n",
       "      <td>773570</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          publisher                                             genres  \\\n",
       "0         Kotoshiro      [Action, Casual, Indie, Simulation, Strategy]   \n",
       "1  Making Fun, Inc.               [Free to Play, Indie, RPG, Strategy]   \n",
       "2      Poolians.com  [Casual, Free to Play, Indie, Simulation, Sports]   \n",
       "3              彼岸领域                        [Action, Adventure, Casual]   \n",
       "4               NaN                                                NaN   \n",
       "\n",
       "                  app_name                    title  \\\n",
       "0      Lost Summoner Kitty      Lost Summoner Kitty   \n",
       "1                Ironbound                Ironbound   \n",
       "2  Real Pool 3D - Poolians  Real Pool 3D - Poolians   \n",
       "3                  弹炸人2222                  弹炸人2222   \n",
       "4            Log Challenge                      NaN   \n",
       "\n",
       "                                                 url release_date  \\\n",
       "0  http://store.steampowered.com/app/761140/Lost_...   2018-01-04   \n",
       "1  http://store.steampowered.com/app/643980/Ironb...   2018-01-04   \n",
       "2  http://store.steampowered.com/app/670290/Real_...   2017-07-24   \n",
       "3     http://store.steampowered.com/app/767400/2222/   2017-12-07   \n",
       "4  http://store.steampowered.com/app/773570/Log_C...          NaN   \n",
       "\n",
       "                                                tags  \\\n",
       "0      [Strategy, Action, Indie, Casual, Simulation]   \n",
       "1  [Free to Play, Strategy, Indie, RPG, Card Game...   \n",
       "2  [Free to Play, Simulation, Sports, Casual, Ind...   \n",
       "3                        [Action, Adventure, Casual]   \n",
       "4                    [Action, Indie, Casual, Sports]   \n",
       "\n",
       "                                         reviews_url  \\\n",
       "0  http://steamcommunity.com/app/761140/reviews/?...   \n",
       "1  http://steamcommunity.com/app/643980/reviews/?...   \n",
       "2  http://steamcommunity.com/app/670290/reviews/?...   \n",
       "3  http://steamcommunity.com/app/767400/reviews/?...   \n",
       "4  http://steamcommunity.com/app/773570/reviews/?...   \n",
       "\n",
       "                                               specs         price  \\\n",
       "0                                    [Single-player]          4.99   \n",
       "1  [Single-player, Multi-player, Online Multi-Pla...  Free To Play   \n",
       "2  [Single-player, Multi-player, Online Multi-Pla...  Free to Play   \n",
       "3                                    [Single-player]          0.99   \n",
       "4  [Single-player, Full controller support, HTC V...          2.99   \n",
       "\n",
       "  early_access      id         developer  \n",
       "0        False  761140         Kotoshiro  \n",
       "1        False  643980  Secret Level SRL  \n",
       "2        False  670290      Poolians.com  \n",
       "3        False  767400              彼岸领域  \n",
       "4        False  773570               NaN  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "games = games.dropna(how=\"all\").reset_index(drop=True) # Eliminamos las filas que tienen todos sus registros como nulos\n",
    "games.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(32135, 13)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "games.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "publisher       25.056792\n",
       "developer       10.266065\n",
       "genres          10.216275\n",
       "release_date     6.432239\n",
       "title            6.379337\n",
       "price            4.285047\n",
       "specs            2.084954\n",
       "tags             0.507235\n",
       "app_name         0.006224\n",
       "reviews_url      0.006224\n",
       "id               0.006224\n",
       "url              0.000000\n",
       "early_access     0.000000\n",
       "dtype: float64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "games.isna().sum().sort_values(ascending= False)/len(games) * 100"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Conclusión:\n",
    "\n",
    "* Se redujo bastante el número de nulos en el dataset por columna, así como su tamaño de registros, disminuyó un 70% aproximadamente\n",
    "* Publisher y developer, se pueden tolerar con los vacíos\n",
    "* Genres es necesario imputar datos para evitar la perdida de información\n",
    "\n",
    "Vamos a observar las columnas para tener una mejor visión acerca de la imputación de datos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Action', 'Casual', 'Indie', 'Simulation', 'Strategy']\n",
      "['Casual', 'Indie', 'RPG']\n",
      "nan\n",
      "['Casual', 'Indie', 'Simulation']\n",
      "['Adventure']\n"
     ]
    }
   ],
   "source": [
    "# se observa el tipo de dato en 'genres' para varios registros de forma aleatoria\n",
    "print(games['genres'].iloc[0])\n",
    "print(games['genres'].iloc[15544])\n",
    "print(games['genres'].iloc[30])\n",
    "print(games['genres'].iloc[14])\n",
    "print(games['genres'].iloc[256])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Strategy', 'Action', 'Indie', 'Casual', 'Simulation']\n",
      "['RPG', 'Indie', 'Casual']\n",
      "['Action', 'Indie', 'VR']\n",
      "['Indie', 'Casual', 'Simulation']\n",
      "['Adventure', 'Point & Click', 'Comedy']\n"
     ]
    }
   ],
   "source": [
    "print(games['tags'].iloc[0])\n",
    "print(games['tags'].iloc[15544])\n",
    "print(games['tags'].iloc[30])\n",
    "print(games['tags'].iloc[14])\n",
    "print(games['tags'].iloc[256])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Single-player']\n",
      "['Single-player', 'Steam Achievements', 'Steam Trading Cards']\n",
      "['Single-player', 'Steam Achievements', 'Steam Leaderboards', 'HTC Vive', 'Oculus Rift', 'Tracked Motion Controllers', 'Room-Scale']\n",
      "['Single-player']\n",
      "['Single-player']\n"
     ]
    }
   ],
   "source": [
    "print(games['specs'].iloc[0])\n",
    "print(games['specs'].iloc[15544])\n",
    "print(games['specs'].iloc[30])\n",
    "print(games['specs'].iloc[14])\n",
    "print(games['specs'].iloc[256])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([4.99, 'Free To Play', 'Free to Play', 0.99, 2.99, 3.99, 9.99,\n",
       "       18.99, 29.99, nan, 'Free', 10.99, 1.59, 14.99, 1.99, 59.99, 8.99,\n",
       "       6.99, 7.99, 39.99, 19.99, 7.49, 12.99, 5.99, 2.49, 15.99, 1.25,\n",
       "       24.99, 17.99, 61.99, 3.49, 11.99, 13.99, 'Free Demo',\n",
       "       'Play for Free!', 34.99, 74.76, 1.49, 32.99, 99.99, 14.95, 69.99,\n",
       "       16.99, 79.99, 49.99, 5.0, 44.99, 13.98, 29.96, 119.99, 109.99,\n",
       "       149.99, 771.71, 'Install Now', 21.99, 89.99,\n",
       "       'Play WARMACHINE: Tactics Demo', 0.98, 139.92, 4.29, 64.99,\n",
       "       'Free Mod', 54.99, 74.99, 'Install Theme', 0.89, 'Third-party',\n",
       "       0.5, 'Play Now', 299.99, 1.29, 3.0, 15.0, 5.49, 23.99, 49.0, 20.99,\n",
       "       10.93, 1.39, 'Free HITMAN™ Holiday Pack', 36.99, 4.49, 2.0, 4.0,\n",
       "       9.0, 234.99, 1.95, 1.5, 199.0, 189.0, 6.66, 27.99, 10.49, 129.99,\n",
       "       179.0, 26.99, 399.99, 31.99, 399.0, 20.0, 40.0, 3.33, 199.99,\n",
       "       22.99, 320.0, 38.85, 71.7, 59.95, 995.0, 27.49, 3.39, 6.0, 19.95,\n",
       "       499.99, 16.06, 4.68, 131.4, 44.98, 202.76, 1.0, 2.3, 0.95, 172.24,\n",
       "       249.99, 2.97, 10.96, 10.0, 30.0, 2.66, 6.48, 19.29, 11.15, 18.9,\n",
       "       2.89, 'Play the Demo', 99.0, 87.94, 599.0, 8.98, 9.69, 0.49, 9.98,\n",
       "       9.95, 7.0, 'Starting at $499.00', 'Starting at $449.00', 12.89,\n",
       "       6.49, 1.87, 42.99, 41.99, 289.99, 'Free to Try', 23.96, 5.65, 12.0,\n",
       "       13.37, 189.96, 124.99, 'Free Movie', 19.98, 160.91, 'Free to Use'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# se observa el tipo de dato en 'price'\n",
    "games['price'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([False, True], dtype=object)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# se observa el tipo de dato en 'early_access'\n",
    "games['early_access'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>publisher</th>\n",
       "      <th>genres</th>\n",
       "      <th>app_name</th>\n",
       "      <th>title</th>\n",
       "      <th>url</th>\n",
       "      <th>release_date</th>\n",
       "      <th>tags</th>\n",
       "      <th>reviews_url</th>\n",
       "      <th>specs</th>\n",
       "      <th>price</th>\n",
       "      <th>early_access</th>\n",
       "      <th>id</th>\n",
       "      <th>developer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>http://store.steampowered.com/</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.99</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13894</th>\n",
       "      <td>Bethesda Softworks</td>\n",
       "      <td>[Action]</td>\n",
       "      <td>Wolfenstein II: The New Colossus</td>\n",
       "      <td>Wolfenstein II: The New Colossus</td>\n",
       "      <td>http://store.steampowered.com/app/612880/</td>\n",
       "      <td>2017-10-26</td>\n",
       "      <td>[Action, FPS, Gore, Violent, Alternate History...</td>\n",
       "      <td>http://steamcommunity.com/app/612880/reviews/?...</td>\n",
       "      <td>[Single-player, Steam Achievements, Full contr...</td>\n",
       "      <td>59.99</td>\n",
       "      <td>False</td>\n",
       "      <td>612880</td>\n",
       "      <td>Machine Games</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14573</th>\n",
       "      <td>Bethesda Softworks</td>\n",
       "      <td>[Action]</td>\n",
       "      <td>Wolfenstein II: The New Colossus</td>\n",
       "      <td>Wolfenstein II: The New Colossus</td>\n",
       "      <td>http://store.steampowered.com/app/612880/Wolfe...</td>\n",
       "      <td>2017-10-26</td>\n",
       "      <td>[Action, FPS, Gore, Violent, Alternate History...</td>\n",
       "      <td>http://steamcommunity.com/app/612880/reviews/?...</td>\n",
       "      <td>[Single-player, Steam Achievements, Full contr...</td>\n",
       "      <td>59.99</td>\n",
       "      <td>False</td>\n",
       "      <td>612880</td>\n",
       "      <td>Machine Games</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30961</th>\n",
       "      <td>Warner Bros. Interactive Entertainment, Feral ...</td>\n",
       "      <td>[Action, Adventure]</td>\n",
       "      <td>Batman: Arkham City - Game of the Year Edition</td>\n",
       "      <td>Batman: Arkham City - Game of the Year Edition</td>\n",
       "      <td>http://store.steampowered.com/app/200260</td>\n",
       "      <td>2012-09-07</td>\n",
       "      <td>[Action, Open World, Batman, Adventure, Stealt...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Single-player, Steam Achievements, Steam Trad...</td>\n",
       "      <td>19.99</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Rocksteady Studios,Feral Interactive (Mac)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               publisher               genres  \\\n",
       "74                                                   NaN                  NaN   \n",
       "13894                                 Bethesda Softworks             [Action]   \n",
       "14573                                 Bethesda Softworks             [Action]   \n",
       "30961  Warner Bros. Interactive Entertainment, Feral ...  [Action, Adventure]   \n",
       "\n",
       "                                             app_name  \\\n",
       "74                                                NaN   \n",
       "13894                Wolfenstein II: The New Colossus   \n",
       "14573                Wolfenstein II: The New Colossus   \n",
       "30961  Batman: Arkham City - Game of the Year Edition   \n",
       "\n",
       "                                                title  \\\n",
       "74                                                NaN   \n",
       "13894                Wolfenstein II: The New Colossus   \n",
       "14573                Wolfenstein II: The New Colossus   \n",
       "30961  Batman: Arkham City - Game of the Year Edition   \n",
       "\n",
       "                                                     url release_date  \\\n",
       "74                        http://store.steampowered.com/          NaN   \n",
       "13894          http://store.steampowered.com/app/612880/   2017-10-26   \n",
       "14573  http://store.steampowered.com/app/612880/Wolfe...   2017-10-26   \n",
       "30961           http://store.steampowered.com/app/200260   2012-09-07   \n",
       "\n",
       "                                                    tags  \\\n",
       "74                                                   NaN   \n",
       "13894  [Action, FPS, Gore, Violent, Alternate History...   \n",
       "14573  [Action, FPS, Gore, Violent, Alternate History...   \n",
       "30961  [Action, Open World, Batman, Adventure, Stealt...   \n",
       "\n",
       "                                             reviews_url  \\\n",
       "74                                                   NaN   \n",
       "13894  http://steamcommunity.com/app/612880/reviews/?...   \n",
       "14573  http://steamcommunity.com/app/612880/reviews/?...   \n",
       "30961                                                NaN   \n",
       "\n",
       "                                                   specs  price early_access  \\\n",
       "74                                                   NaN  19.99        False   \n",
       "13894  [Single-player, Steam Achievements, Full contr...  59.99        False   \n",
       "14573  [Single-player, Steam Achievements, Full contr...  59.99        False   \n",
       "30961  [Single-player, Steam Achievements, Steam Trad...  19.99        False   \n",
       "\n",
       "           id                                   developer  \n",
       "74        NaN                                         NaN  \n",
       "13894  612880                               Machine Games  \n",
       "14573  612880                               Machine Games  \n",
       "30961     NaN  Rocksteady Studios,Feral Interactive (Mac)  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "duplicados_columnas = games[games.duplicated(subset=['id'], keep=False)] # Verificamos si hay filas duplicadas en la columna id\n",
    "duplicados_columnas\n"
   ]
  },
  {
   "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>publisher</th>\n",
       "      <th>genres</th>\n",
       "      <th>app_name</th>\n",
       "      <th>title</th>\n",
       "      <th>url</th>\n",
       "      <th>release_date</th>\n",
       "      <th>tags</th>\n",
       "      <th>reviews_url</th>\n",
       "      <th>specs</th>\n",
       "      <th>price</th>\n",
       "      <th>early_access</th>\n",
       "      <th>id</th>\n",
       "      <th>developer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1068</th>\n",
       "      <td>Warner Bros. Interactive Entertainment, Feral ...</td>\n",
       "      <td>[Action, Adventure]</td>\n",
       "      <td>Batman: Arkham City - Game of the Year Edition</td>\n",
       "      <td>Batman: Arkham City - Game of the Year Edition</td>\n",
       "      <td>http://store.steampowered.com/app/200260/Batma...</td>\n",
       "      <td>2012-09-07</td>\n",
       "      <td>[Action, Open World, Batman, Adventure, Stealt...</td>\n",
       "      <td>http://steamcommunity.com/app/200260/reviews/?...</td>\n",
       "      <td>[Single-player, Steam Achievements, Steam Trad...</td>\n",
       "      <td>19.99</td>\n",
       "      <td>False</td>\n",
       "      <td>200260</td>\n",
       "      <td>Rocksteady Studios,Feral Interactive (Mac)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30961</th>\n",
       "      <td>Warner Bros. Interactive Entertainment, Feral ...</td>\n",
       "      <td>[Action, Adventure]</td>\n",
       "      <td>Batman: Arkham City - Game of the Year Edition</td>\n",
       "      <td>Batman: Arkham City - Game of the Year Edition</td>\n",
       "      <td>http://store.steampowered.com/app/200260</td>\n",
       "      <td>2012-09-07</td>\n",
       "      <td>[Action, Open World, Batman, Adventure, Stealt...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[Single-player, Steam Achievements, Steam Trad...</td>\n",
       "      <td>19.99</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Rocksteady Studios,Feral Interactive (Mac)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31617</th>\n",
       "      <td>Warner Bros. Interactive Entertainment, Feral ...</td>\n",
       "      <td>[Action, Adventure]</td>\n",
       "      <td>Batman: Arkham Asylum Game of the Year Edition</td>\n",
       "      <td>Batman: Arkham Asylum Game of the Year Edition</td>\n",
       "      <td>http://store.steampowered.com/app/35140/Batman...</td>\n",
       "      <td>2010-03-26</td>\n",
       "      <td>[Action, Batman, Stealth, Adventure, Third Per...</td>\n",
       "      <td>http://steamcommunity.com/app/35140/reviews/?b...</td>\n",
       "      <td>[Single-player, Steam Achievements, Steam Trad...</td>\n",
       "      <td>19.99</td>\n",
       "      <td>False</td>\n",
       "      <td>35140</td>\n",
       "      <td>Rocksteady Studios,Feral Interactive (Mac)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               publisher               genres  \\\n",
       "1068   Warner Bros. Interactive Entertainment, Feral ...  [Action, Adventure]   \n",
       "30961  Warner Bros. Interactive Entertainment, Feral ...  [Action, Adventure]   \n",
       "31617  Warner Bros. Interactive Entertainment, Feral ...  [Action, Adventure]   \n",
       "\n",
       "                                             app_name  \\\n",
       "1068   Batman: Arkham City - Game of the Year Edition   \n",
       "30961  Batman: Arkham City - Game of the Year Edition   \n",
       "31617  Batman: Arkham Asylum Game of the Year Edition   \n",
       "\n",
       "                                                title  \\\n",
       "1068   Batman: Arkham City - Game of the Year Edition   \n",
       "30961  Batman: Arkham City - Game of the Year Edition   \n",
       "31617  Batman: Arkham Asylum Game of the Year Edition   \n",
       "\n",
       "                                                     url release_date  \\\n",
       "1068   http://store.steampowered.com/app/200260/Batma...   2012-09-07   \n",
       "30961           http://store.steampowered.com/app/200260   2012-09-07   \n",
       "31617  http://store.steampowered.com/app/35140/Batman...   2010-03-26   \n",
       "\n",
       "                                                    tags  \\\n",
       "1068   [Action, Open World, Batman, Adventure, Stealt...   \n",
       "30961  [Action, Open World, Batman, Adventure, Stealt...   \n",
       "31617  [Action, Batman, Stealth, Adventure, Third Per...   \n",
       "\n",
       "                                             reviews_url  \\\n",
       "1068   http://steamcommunity.com/app/200260/reviews/?...   \n",
       "30961                                                NaN   \n",
       "31617  http://steamcommunity.com/app/35140/reviews/?b...   \n",
       "\n",
       "                                                   specs  price early_access  \\\n",
       "1068   [Single-player, Steam Achievements, Steam Trad...  19.99        False   \n",
       "30961  [Single-player, Steam Achievements, Steam Trad...  19.99        False   \n",
       "31617  [Single-player, Steam Achievements, Steam Trad...  19.99        False   \n",
       "\n",
       "           id                                   developer  \n",
       "1068   200260  Rocksteady Studios,Feral Interactive (Mac)  \n",
       "30961     NaN  Rocksteady Studios,Feral Interactive (Mac)  \n",
       "31617   35140  Rocksteady Studios,Feral Interactive (Mac)  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "games[games['developer']=='Rocksteady Studios,Feral Interactive (Mac)']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# se eliminan los index deseados\n",
    "id_drop = [14573, 74, 30961]\n",
    "games.drop(id_drop, inplace= True)"
   ]
  },
  {
   "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>publisher</th>\n",
       "      <th>genres</th>\n",
       "      <th>app_name</th>\n",
       "      <th>title</th>\n",
       "      <th>url</th>\n",
       "      <th>release_date</th>\n",
       "      <th>tags</th>\n",
       "      <th>reviews_url</th>\n",
       "      <th>specs</th>\n",
       "      <th>price</th>\n",
       "      <th>early_access</th>\n",
       "      <th>id</th>\n",
       "      <th>developer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [publisher, genres, app_name, title, url, release_date, tags, reviews_url, specs, price, early_access, id, developer]\n",
       "Index: []"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "duplicados_columnas = games[games.duplicated(subset=['id'], keep=False)] # Verificamos si hay filas duplicadas en la columna id\n",
    "duplicados_columnas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "release_date\n",
       "2012-10-16    100\n",
       "2017-08-31     92\n",
       "2017-09-26     89\n",
       "2017-06-21     82\n",
       "2017-07-25     78\n",
       "             ... \n",
       "1988-04-16      1\n",
       "2013-08-24      1\n",
       "2011-05-07      1\n",
       "2010-08-21      1\n",
       "2018-10-01      1\n",
       "Name: count, Length: 3582, dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "games['release_date'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convertimos la columna en datetime y rellenamos los valores nulos con 'Dato no disponible'\n",
    "games['release_date'] = pd.to_datetime(games['release_date'], errors='coerce')\n",
    "games = games.dropna(subset=['release_date'])\n",
    "\n",
    "# Revisamos los valores nulos\n",
    "games[\"release_date\"].isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ahora se saca el año solamente de la fecha de lanzamiento"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>publisher</th>\n",
       "      <th>genres</th>\n",
       "      <th>app_name</th>\n",
       "      <th>title</th>\n",
       "      <th>url</th>\n",
       "      <th>release_date</th>\n",
       "      <th>tags</th>\n",
       "      <th>reviews_url</th>\n",
       "      <th>specs</th>\n",
       "      <th>price</th>\n",
       "      <th>early_access</th>\n",
       "      <th>id</th>\n",
       "      <th>developer</th>\n",
       "      <th>release_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kotoshiro</td>\n",
       "      <td>[Action, Casual, Indie, Simulation, Strategy]</td>\n",
       "      <td>Lost Summoner Kitty</td>\n",
       "      <td>Lost Summoner Kitty</td>\n",
       "      <td>http://store.steampowered.com/app/761140/Lost_...</td>\n",
       "      <td>2018-01-04</td>\n",
       "      <td>[Strategy, Action, Indie, Casual, Simulation]</td>\n",
       "      <td>http://steamcommunity.com/app/761140/reviews/?...</td>\n",
       "      <td>[Single-player]</td>\n",
       "      <td>4.99</td>\n",
       "      <td>False</td>\n",
       "      <td>761140</td>\n",
       "      <td>Kotoshiro</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Making Fun, Inc.</td>\n",
       "      <td>[Free to Play, Indie, RPG, Strategy]</td>\n",
       "      <td>Ironbound</td>\n",
       "      <td>Ironbound</td>\n",
       "      <td>http://store.steampowered.com/app/643980/Ironb...</td>\n",
       "      <td>2018-01-04</td>\n",
       "      <td>[Free to Play, Strategy, Indie, RPG, Card Game...</td>\n",
       "      <td>http://steamcommunity.com/app/643980/reviews/?...</td>\n",
       "      <td>[Single-player, Multi-player, Online Multi-Pla...</td>\n",
       "      <td>Free To Play</td>\n",
       "      <td>False</td>\n",
       "      <td>643980</td>\n",
       "      <td>Secret Level SRL</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          publisher                                         genres  \\\n",
       "0         Kotoshiro  [Action, Casual, Indie, Simulation, Strategy]   \n",
       "1  Making Fun, Inc.           [Free to Play, Indie, RPG, Strategy]   \n",
       "\n",
       "              app_name                title  \\\n",
       "0  Lost Summoner Kitty  Lost Summoner Kitty   \n",
       "1            Ironbound            Ironbound   \n",
       "\n",
       "                                                 url release_date  \\\n",
       "0  http://store.steampowered.com/app/761140/Lost_...   2018-01-04   \n",
       "1  http://store.steampowered.com/app/643980/Ironb...   2018-01-04   \n",
       "\n",
       "                                                tags  \\\n",
       "0      [Strategy, Action, Indie, Casual, Simulation]   \n",
       "1  [Free to Play, Strategy, Indie, RPG, Card Game...   \n",
       "\n",
       "                                         reviews_url  \\\n",
       "0  http://steamcommunity.com/app/761140/reviews/?...   \n",
       "1  http://steamcommunity.com/app/643980/reviews/?...   \n",
       "\n",
       "                                               specs         price  \\\n",
       "0                                    [Single-player]          4.99   \n",
       "1  [Single-player, Multi-player, Online Multi-Pla...  Free To Play   \n",
       "\n",
       "  early_access      id         developer  release_year  \n",
       "0        False  761140         Kotoshiro          2018  \n",
       "1        False  643980  Secret Level SRL          2018  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "games['release_date'] = pd.to_datetime(games['release_date'], errors='coerce')\n",
    "# Creamos la columna 'release_year' y usamos la propiedad .dt.year solo para las fechas válidas\n",
    "games['release_year'] = games['release_date'].dt.year\n",
    "# Muestra las primeras 2 filas para verificar\n",
    "games.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "games.drop('release_date', axis=1, inplace= True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([2018, 2017, 1997, 1998, 2016, 2006, 2005, 2003, 2007, 2002, 2000,\n",
       "       1995, 1996, 1994, 2001, 1993, 2004, 1999, 2008, 2009, 1992, 1989,\n",
       "       2010, 2011, 2013, 2012, 2014, 1983, 1984, 2015, 1990, 1988, 1991,\n",
       "       1985, 1982, 1987, 1981, 1986, 2021, 2019, 1975, 1970, 1980])"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "games['release_year'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Definimos una función para poder reemplazar los datos string de la columna \"price\" por flotantes 0.0\n",
    "\n",
    "def str_a_float(valor):\n",
    "\n",
    "    if pd.isna(valor): # si el valor es nulo retorno 0.0\n",
    "        return 0.0\n",
    "    try:\n",
    "        flotante = float(valor) # si el valor es un flotante retorno el mismo valor\n",
    "        return flotante\n",
    "    except (ValueError, TypeError): # si el valor no es un flotante retorno 0.0\n",
    "        return 0.0\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('float64')"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "games[\"price\"] = games[\"price\"].apply(str_a_float) # aplicamos la función a la columna \"price\"\n",
    "games['price'].dtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>url</th>\n",
       "      <th>tags</th>\n",
       "      <th>reviews_url</th>\n",
       "      <th>specs</th>\n",
       "      <th>price</th>\n",
       "      <th>early_access</th>\n",
       "      <th>id</th>\n",
       "      <th>release_year</th>\n",
       "      <th>publisher</th>\n",
       "      <th>app_name</th>\n",
       "      <th>title</th>\n",
       "      <th>developer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>[Action, Casual, Indie, Simulation, Strategy]</td>\n",
       "      <td>http://store.steampowered.com/app/761140/Lost_...</td>\n",
       "      <td>[Strategy, Action, Indie, Casual, Simulation]</td>\n",
       "      <td>http://steamcommunity.com/app/761140/reviews/?...</td>\n",
       "      <td>[Single-player]</td>\n",
       "      <td>4.99</td>\n",
       "      <td>False</td>\n",
       "      <td>761140</td>\n",
       "      <td>2018</td>\n",
       "      <td>Kotoshiro</td>\n",
       "      <td>Lost Summoner Kitty</td>\n",
       "      <td>Lost Summoner Kitty</td>\n",
       "      <td>Kotoshiro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>[Free to Play, Indie, RPG, Strategy]</td>\n",
       "      <td>http://store.steampowered.com/app/643980/Ironb...</td>\n",
       "      <td>[Free to Play, Strategy, Indie, RPG, Card Game...</td>\n",
       "      <td>http://steamcommunity.com/app/643980/reviews/?...</td>\n",
       "      <td>[Single-player, Multi-player, Online Multi-Pla...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>False</td>\n",
       "      <td>643980</td>\n",
       "      <td>2018</td>\n",
       "      <td>Making Fun, Inc.</td>\n",
       "      <td>Ironbound</td>\n",
       "      <td>Ironbound</td>\n",
       "      <td>Secret Level SRL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>[Casual, Free to Play, Indie, Simulation, Sports]</td>\n",
       "      <td>http://store.steampowered.com/app/670290/Real_...</td>\n",
       "      <td>[Free to Play, Simulation, Sports, Casual, Ind...</td>\n",
       "      <td>http://steamcommunity.com/app/670290/reviews/?...</td>\n",
       "      <td>[Single-player, Multi-player, Online Multi-Pla...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>False</td>\n",
       "      <td>670290</td>\n",
       "      <td>2017</td>\n",
       "      <td>Poolians.com</td>\n",
       "      <td>Real Pool 3D - Poolians</td>\n",
       "      <td>Real Pool 3D - Poolians</td>\n",
       "      <td>Poolians.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>[Action, Adventure, Casual]</td>\n",
       "      <td>http://store.steampowered.com/app/767400/2222/</td>\n",
       "      <td>[Action, Adventure, Casual]</td>\n",
       "      <td>http://steamcommunity.com/app/767400/reviews/?...</td>\n",
       "      <td>[Single-player]</td>\n",
       "      <td>0.99</td>\n",
       "      <td>False</td>\n",
       "      <td>767400</td>\n",
       "      <td>2017</td>\n",
       "      <td>彼岸领域</td>\n",
       "      <td>弹炸人2222</td>\n",
       "      <td>弹炸人2222</td>\n",
       "      <td>彼岸领域</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>[Action, Adventure, Simulation]</td>\n",
       "      <td>http://store.steampowered.com/app/772540/Battl...</td>\n",
       "      <td>[Action, Adventure, Simulation, FPS, Shooter, ...</td>\n",
       "      <td>http://steamcommunity.com/app/772540/reviews/?...</td>\n",
       "      <td>[Single-player, Steam Achievements]</td>\n",
       "      <td>3.99</td>\n",
       "      <td>False</td>\n",
       "      <td>772540</td>\n",
       "      <td>2018</td>\n",
       "      <td>Trickjump Games Ltd</td>\n",
       "      <td>Battle Royale Trainer</td>\n",
       "      <td>Battle Royale Trainer</td>\n",
       "      <td>Trickjump Games Ltd</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              genres  \\\n",
       "0      [Action, Casual, Indie, Simulation, Strategy]   \n",
       "1               [Free to Play, Indie, RPG, Strategy]   \n",
       "2  [Casual, Free to Play, Indie, Simulation, Sports]   \n",
       "3                        [Action, Adventure, Casual]   \n",
       "5                    [Action, Adventure, Simulation]   \n",
       "\n",
       "                                                 url  \\\n",
       "0  http://store.steampowered.com/app/761140/Lost_...   \n",
       "1  http://store.steampowered.com/app/643980/Ironb...   \n",
       "2  http://store.steampowered.com/app/670290/Real_...   \n",
       "3     http://store.steampowered.com/app/767400/2222/   \n",
       "5  http://store.steampowered.com/app/772540/Battl...   \n",
       "\n",
       "                                                tags  \\\n",
       "0      [Strategy, Action, Indie, Casual, Simulation]   \n",
       "1  [Free to Play, Strategy, Indie, RPG, Card Game...   \n",
       "2  [Free to Play, Simulation, Sports, Casual, Ind...   \n",
       "3                        [Action, Adventure, Casual]   \n",
       "5  [Action, Adventure, Simulation, FPS, Shooter, ...   \n",
       "\n",
       "                                         reviews_url  \\\n",
       "0  http://steamcommunity.com/app/761140/reviews/?...   \n",
       "1  http://steamcommunity.com/app/643980/reviews/?...   \n",
       "2  http://steamcommunity.com/app/670290/reviews/?...   \n",
       "3  http://steamcommunity.com/app/767400/reviews/?...   \n",
       "5  http://steamcommunity.com/app/772540/reviews/?...   \n",
       "\n",
       "                                               specs  price early_access  \\\n",
       "0                                    [Single-player]   4.99        False   \n",
       "1  [Single-player, Multi-player, Online Multi-Pla...   0.00        False   \n",
       "2  [Single-player, Multi-player, Online Multi-Pla...   0.00        False   \n",
       "3                                    [Single-player]   0.99        False   \n",
       "5                [Single-player, Steam Achievements]   3.99        False   \n",
       "\n",
       "       id  release_year            publisher                 app_name  \\\n",
       "0  761140          2018            Kotoshiro      Lost Summoner Kitty   \n",
       "1  643980          2018     Making Fun, Inc.                Ironbound   \n",
       "2  670290          2017         Poolians.com  Real Pool 3D - Poolians   \n",
       "3  767400          2017                 彼岸领域                  弹炸人2222   \n",
       "5  772540          2018  Trickjump Games Ltd    Battle Royale Trainer   \n",
       "\n",
       "                     title            developer  \n",
       "0      Lost Summoner Kitty            Kotoshiro  \n",
       "1                Ironbound     Secret Level SRL  \n",
       "2  Real Pool 3D - Poolians         Poolians.com  \n",
       "3                  弹炸人2222                 彼岸领域  \n",
       "5    Battle Royale Trainer  Trickjump Games Ltd  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# SColumnas a transformar\n",
    "col_to_fill = ['publisher', 'app_name', 'title', 'developer']\n",
    "# Se rellenan los nulos\n",
    "df_relleno = games[col_to_fill].fillna('Sin dato disponible')\n",
    "# Se borran las columnas originales y se concatenan las rellenas con todo el dataframe\n",
    "games = pd.concat([games.drop(col_to_fill, axis=1), df_relleno], axis=1)\n",
    "games.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Usar explode para convertir las listas en filas individuales\n",
    "games = games.explode('tags')\n",
    "# Mostrar los valores únicos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "tags_genres = [\n",
    "    \"Action\",\n",
    "    \"Casual\",\n",
    "    \"Indie\",\n",
    "    \"Simulation\",\n",
    "    \"Strategy\",\n",
    "    \"Free to Play\",\n",
    "    \"RPG\",\n",
    "    \"Sports\",\n",
    "    \"Adventure\",\n",
    "    \"Racing\",\n",
    "    \"Early Access\",\n",
    "    \"Massively Multiplayer\",\n",
    "    \"Animation & Modeling\",\n",
    "    \"Video Production\",\n",
    "    \"Utilities\",\n",
    "    \"Web Publishing\",\n",
    "    \"Education\",\n",
    "    \"Software Training\",\n",
    "    \"Design & Illustration\",\n",
    "    \"Audio Production\",\n",
    "    \"Photo Editing\",\n",
    "    \"Accounting\"\n",
    "]\n",
    "\n",
    "# Conservar solo las columnas en tags_drop y eliminar el resto\n",
    "games = games[games['tags'].isin(tags_genres)]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "games['genres'] = games['genres'].fillna(games['tags'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "games = games.explode('genres')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['genres', 'price', 'early_access', 'id', 'release_year', 'publisher',\n",
       "       'app_name', 'title', 'developer'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "games = games.drop(['tags', 'specs', 'url', 'reviews_url'], axis=1)\n",
    "games.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "games = games.drop_duplicates() # Eliminamos duplicados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "genres          0.0\n",
       "price           0.0\n",
       "early_access    0.0\n",
       "id              0.0\n",
       "release_year    0.0\n",
       "publisher       0.0\n",
       "app_name        0.0\n",
       "title           0.0\n",
       "developer       0.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "games.isna().sum().sort_values(ascending= False)/len(games) * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Modifica todos los registros que cumplen con la condición en la columna especificada\n",
    "games.loc[(games['genres'] == 'Animation &amp; Modeling'), 'genres'] = 'Animation & Modeling'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "games.loc[(games['genres'] == 'Design &amp; Illustration'), 'genres'] = 'Design & Illustration'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "games = games.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 71044 entries, 0 to 32133\n",
      "Data columns (total 9 columns):\n",
      " #   Column        Non-Null Count  Dtype  \n",
      "---  ------        --------------  -----  \n",
      " 0   genres        71044 non-null  object \n",
      " 1   price         71044 non-null  float64\n",
      " 2   early_access  71044 non-null  object \n",
      " 3   id            71044 non-null  object \n",
      " 4   release_year  71044 non-null  int32  \n",
      " 5   publisher     71044 non-null  object \n",
      " 6   app_name      71044 non-null  object \n",
      " 7   title         71044 non-null  object \n",
      " 8   developer     71044 non-null  object \n",
      "dtypes: float64(1), int32(1), object(7)\n",
      "memory usage: 5.1+ MB\n"
     ]
    }
   ],
   "source": [
    "games.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "games.to_csv(\"data/01-steam-games.csv\", index=False, encoding=\"utf-8\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "games = pd.read_csv(\"data/01-steam-games.csv\") # leemos el archivo csv\n",
    "\n",
    "tabla = pa.Table.from_pandas(games) # convertimos el dataframe en una tabla\n",
    "pq.write_table(tabla,\"data/01-steam-games.parquet\") # guardamos la tabla en un archivo parquet"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "env",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}