ETL_Items.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "92beba70",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"items.json\", 'r', encoding='utf-8') as i: #Abro el json de los items\n",
    "    data = i.readlines()\n",
    "    \n",
    "records = [eval(line.strip()) for line in data]\n",
    "\n",
    "item = pd.DataFrame(records)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6bb3646d",
   "metadata": {},
   "outputs": [],
   "source": [
    "item.to_csv('item.csv') #Guardo en csv para que sea mas facil la carga"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "478d966f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "44abc629",
   "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>Unnamed: 0</th>\n",
       "      <th>user_id</th>\n",
       "      <th>items_count</th>\n",
       "      <th>steam_id</th>\n",
       "      <th>user_url</th>\n",
       "      <th>items</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>277</td>\n",
       "      <td>76561197970982479</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561197970...</td>\n",
       "      <td>[{'item_id': '10', 'item_name': 'Counter-Strik...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>js41637</td>\n",
       "      <td>888</td>\n",
       "      <td>76561198035864385</td>\n",
       "      <td>http://steamcommunity.com/id/js41637</td>\n",
       "      <td>[{'item_id': '10', 'item_name': 'Counter-Strik...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>evcentric</td>\n",
       "      <td>137</td>\n",
       "      <td>76561198007712555</td>\n",
       "      <td>http://steamcommunity.com/id/evcentric</td>\n",
       "      <td>[{'item_id': '1200', 'item_name': 'Red Orchest...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Riot-Punch</td>\n",
       "      <td>328</td>\n",
       "      <td>76561197963445855</td>\n",
       "      <td>http://steamcommunity.com/id/Riot-Punch</td>\n",
       "      <td>[{'item_id': '10', 'item_name': 'Counter-Strik...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>doctr</td>\n",
       "      <td>541</td>\n",
       "      <td>76561198002099482</td>\n",
       "      <td>http://steamcommunity.com/id/doctr</td>\n",
       "      <td>[{'item_id': '300', 'item_name': 'Day of Defea...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0            user_id  items_count           steam_id  \\\n",
       "0           0  76561197970982479          277  76561197970982479   \n",
       "1           1            js41637          888  76561198035864385   \n",
       "2           2          evcentric          137  76561198007712555   \n",
       "3           3         Riot-Punch          328  76561197963445855   \n",
       "4           4              doctr          541  76561198002099482   \n",
       "\n",
       "                                            user_url  \\\n",
       "0  http://steamcommunity.com/profiles/76561197970...   \n",
       "1               http://steamcommunity.com/id/js41637   \n",
       "2             http://steamcommunity.com/id/evcentric   \n",
       "3            http://steamcommunity.com/id/Riot-Punch   \n",
       "4                 http://steamcommunity.com/id/doctr   \n",
       "\n",
       "                                               items  \n",
       "0  [{'item_id': '10', 'item_name': 'Counter-Strik...  \n",
       "1  [{'item_id': '10', 'item_name': 'Counter-Strik...  \n",
       "2  [{'item_id': '1200', 'item_name': 'Red Orchest...  \n",
       "3  [{'item_id': '10', 'item_name': 'Counter-Strik...  \n",
       "4  [{'item_id': '300', 'item_name': 'Day of Defea...  "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item= pd.read_csv('item.csv',low_memory=False)\n",
    "item.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "0d53ceed",
   "metadata": {},
   "outputs": [],
   "source": [
    "item.drop(['Unnamed: 0'],axis=1, inplace= True) #Borramos la columna que se genero de los indices cuando guardamos el csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "8c91cffd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(88310, 5)"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "35e20446",
   "metadata": {},
   "outputs": [],
   "source": [
    "item.drop_duplicates(inplace=True) #Borramos los duplicados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "a0b397a8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(87653, 5)"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "7745a5ad",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(87653, 5)"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item = item.reset_index(drop=True) #Reseteamos los indices para que no allan saltos\n",
    "item.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "8c103340",
   "metadata": {},
   "outputs": [],
   "source": [
    "import random\n",
    "numeros_aleatorios = random.sample(range(0, 87652), 65739) #Generamos indices de registros que vamos a eliminar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "3a073b90",
   "metadata": {},
   "outputs": [],
   "source": [
    "item = item.drop(numeros_aleatorios) #Borramos 3/4 de los registros para un mejor procesamiento en los ejercicios"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "44d5ae5d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(21914, 5)"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "b2e05820",
   "metadata": {},
   "outputs": [],
   "source": [
    "item.to_csv('item_limpio.csv') #Guardamos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "2002edd7",
   "metadata": {},
   "outputs": [],
   "source": [
    "item=pd.read_csv('item_limpio.csv', low_memory=False) #Abrimos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "1985f68d",
   "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>Unnamed: 0</th>\n",
       "      <th>user_id</th>\n",
       "      <th>items_count</th>\n",
       "      <th>steam_id</th>\n",
       "      <th>user_url</th>\n",
       "      <th>items</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>evcentric</td>\n",
       "      <td>137</td>\n",
       "      <td>76561198007712555</td>\n",
       "      <td>http://steamcommunity.com/id/evcentric</td>\n",
       "      <td>[{'item_id': '1200', 'item_name': 'Red Orchest...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>Riot-Punch</td>\n",
       "      <td>328</td>\n",
       "      <td>76561197963445855</td>\n",
       "      <td>http://steamcommunity.com/id/Riot-Punch</td>\n",
       "      <td>[{'item_id': '10', 'item_name': 'Counter-Strik...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>NitemarePK</td>\n",
       "      <td>304</td>\n",
       "      <td>76561197990951820</td>\n",
       "      <td>http://steamcommunity.com/id/NitemarePK</td>\n",
       "      <td>[{'item_id': '240', 'item_name': 'Counter-Stri...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>themanwich</td>\n",
       "      <td>258</td>\n",
       "      <td>76561198035296505</td>\n",
       "      <td>http://steamcommunity.com/id/themanwich</td>\n",
       "      <td>[{'item_id': '220', 'item_name': 'Half-Life 2'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16</td>\n",
       "      <td>76561198089393905</td>\n",
       "      <td>103</td>\n",
       "      <td>76561198089393905</td>\n",
       "      <td>http://steamcommunity.com/profiles/76561198089...</td>\n",
       "      <td>[{'item_id': '4000', 'item_name': \"Garry's Mod...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0            user_id  items_count           steam_id  \\\n",
       "0           2          evcentric          137  76561198007712555   \n",
       "1           3         Riot-Punch          328  76561197963445855   \n",
       "2           6         NitemarePK          304  76561197990951820   \n",
       "3           7         themanwich          258  76561198035296505   \n",
       "4          16  76561198089393905          103  76561198089393905   \n",
       "\n",
       "                                            user_url  \\\n",
       "0             http://steamcommunity.com/id/evcentric   \n",
       "1            http://steamcommunity.com/id/Riot-Punch   \n",
       "2            http://steamcommunity.com/id/NitemarePK   \n",
       "3            http://steamcommunity.com/id/themanwich   \n",
       "4  http://steamcommunity.com/profiles/76561198089...   \n",
       "\n",
       "                                               items  \n",
       "0  [{'item_id': '1200', 'item_name': 'Red Orchest...  \n",
       "1  [{'item_id': '10', 'item_name': 'Counter-Strik...  \n",
       "2  [{'item_id': '240', 'item_name': 'Counter-Stri...  \n",
       "3  [{'item_id': '220', 'item_name': 'Half-Life 2'...  \n",
       "4  [{'item_id': '4000', 'item_name': \"Garry's Mod...  "
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item.head() #Revisamos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "6a54404d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(21914, 6)"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "882cd314",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'[{\\'item_id\\': \\'1200\\', \\'item_name\\': \\'Red Orchestra: Ostfront 41-45\\', \\'playtime_forever\\': 923, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'1230\\', \\'item_name\\': \\'Mare Nostrum\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'1280\\', \\'item_name\\': \"Darkest Hour: Europe \\'44-\\'45\", \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'1520\\', \\'item_name\\': \\'DEFCON\\', \\'playtime_forever\\': 158, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'220\\', \\'item_name\\': \\'Half-Life 2\\', \\'playtime_forever\\': 1323, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'320\\', \\'item_name\\': \\'Half-Life 2: Deathmatch\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'340\\', \\'item_name\\': \\'Half-Life 2: Lost Coast\\', \\'playtime_forever\\': 90, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'360\\', \\'item_name\\': \\'Half-Life Deathmatch: Source\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'380\\', \\'item_name\\': \\'Half-Life 2: Episode One\\', \\'playtime_forever\\': 234, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'400\\', \\'item_name\\': \\'Portal\\', \\'playtime_forever\\': 113, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'420\\', \\'item_name\\': \\'Half-Life 2: Episode Two\\', \\'playtime_forever\\': 507, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'13250\\', \\'item_name\\': \\'Unreal Gold\\', \\'playtime_forever\\': 21, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'10500\\', \\'item_name\\': \\'Empire: Total War\\', \\'playtime_forever\\': 6038, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'1250\\', \\'item_name\\': \\'Killing Floor\\', \\'playtime_forever\\': 1646, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'35420\\', \\'item_name\\': \\'Killing Floor Mod: Defence Alliance 2\\', \\'playtime_forever\\': 13, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'3720\\', \\'item_name\\': \\'Evil Genius\\', \\'playtime_forever\\': 1393, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'32360\\', \\'item_name\\': \\'The Secret of Monkey Island: Special Edition\\', \\'playtime_forever\\': 8, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'8980\\', \\'item_name\\': \\'Borderlands\\', \\'playtime_forever\\': 9202, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'550\\', \\'item_name\\': \\'Left 4 Dead 2\\', \\'playtime_forever\\': 82, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'223530\\', \\'item_name\\': \\'Left 4 Dead 2 Beta\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'22380\\', \\'item_name\\': \\'Fallout: New Vegas\\', \\'playtime_forever\\': 11311, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'31280\\', \\'item_name\\': \\'Poker Night at the Inventory\\', \\'playtime_forever\\': 1090, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'67000\\', \\'item_name\\': \\'The Polynomial\\', \\'playtime_forever\\': 4, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'63200\\', \\'item_name\\': \\'Monday Night Combat\\', \\'playtime_forever\\': 320, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'620\\', \\'item_name\\': \\'Portal 2\\', \\'playtime_forever\\': 1485, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'47900\\', \\'item_name\\': \\'Dragon Age II\\', \\'playtime_forever\\': 2301, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'42910\\', \\'item_name\\': \\'Magicka\\', \\'playtime_forever\\': 428, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'28050\\', \\'item_name\\': \\'Deus Ex: Human Revolution\\', \\'playtime_forever\\': 7689, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'2200\\', \\'item_name\\': \\'Quake III Arena\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'2210\\', \\'item_name\\': \\'Quake 4\\', \\'playtime_forever\\': 284, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'2270\\', \\'item_name\\': \\'Wolfenstein 3D\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'2280\\', \\'item_name\\': \\'The Ultimate DOOM\\', \\'playtime_forever\\': 87, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'2290\\', \\'item_name\\': \\'Final DOOM\\', \\'playtime_forever\\': 4, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'2300\\', \\'item_name\\': \\'DOOM II: Hell on Earth\\', \\'playtime_forever\\': 10, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'2310\\', \\'item_name\\': \\'Quake\\', \\'playtime_forever\\': 143, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'2320\\', \\'item_name\\': \\'Quake II\\', \\'playtime_forever\\': 48, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'2330\\', \\'item_name\\': \\'Quake II: The Reckoning\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'2340\\', \\'item_name\\': \\'Quake II: Ground Zero\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'2350\\', \\'item_name\\': \\'Quake III: Team Arena\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'2360\\', \\'item_name\\': \\'HeXen: Beyond Heretic\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'2370\\', \\'item_name\\': \\'HeXen: Deathkings of the Dark Citadel\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'2390\\', \\'item_name\\': \\'Heretic: Shadow of the Serpent Riders\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'9000\\', \\'item_name\\': \\'Wolfenstein 3D: Spear of Destiny\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'9010\\', \\'item_name\\': \\'Return to Castle Wolfenstein\\', \\'playtime_forever\\': 616, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'9030\\', \\'item_name\\': \\'Quake Mission Pack 2: Dissolution of Eternity\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'9040\\', \\'item_name\\': \\'Quake Mission Pack 1: Scourge of Armagon\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'9050\\', \\'item_name\\': \\'DOOM 3\\', \\'playtime_forever\\': 32, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'9060\\', \\'item_name\\': \\'HeXen II\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'9070\\', \\'item_name\\': \\'DOOM 3: Resurrection of Evil\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'9160\\', \\'item_name\\': \\'Master Levels for DOOM II\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'9180\\', \\'item_name\\': \\'Commander Keen Complete Pack\\', \\'playtime_forever\\': 218, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'22310\\', \\'item_name\\': \\'Rogue Warrior\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'22320\\', \\'item_name\\': \\'The Elder Scrolls III: Morrowind\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'22330\\', \\'item_name\\': \\'The Elder Scrolls IV: Oblivion \\', \\'playtime_forever\\': 56, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'22340\\', \\'item_name\\': \\'Call of Cthulhu: Dark Corners of the Earth\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'22350\\', \\'item_name\\': \\'BRINK\\', \\'playtime_forever\\': 933, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'22370\\', \\'item_name\\': \\'Fallout 3 - Game of the Year Edition\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'22450\\', \\'item_name\\': \"Hunted: The Demon\\'s Forge\", \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'107200\\', \\'item_name\\': \\'Space Pirates and Zombies\\', \\'playtime_forever\\': 3225, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'2820\\', \\'item_name\\': \\'X3: Terran Conflict\\', \\'playtime_forever\\': 33, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'201310\\', \\'item_name\\': \\'X3: Albion Prelude\\', \\'playtime_forever\\': 5957, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'49520\\', \\'item_name\\': \\'Borderlands 2\\', \\'playtime_forever\\': 8106, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'212680\\', \\'item_name\\': \\'FTL: Faster Than Light\\', \\'playtime_forever\\': 4053, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'205790\\', \\'item_name\\': \\'Dota 2 Test\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'57690\\', \\'item_name\\': \\'Tropico 4\\', \\'playtime_forever\\': 471, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'212070\\', \\'item_name\\': \\'Star Conflict\\', \\'playtime_forever\\': 1635, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'218230\\', \\'item_name\\': \\'PlanetSide 2\\', \\'playtime_forever\\': 5996, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'230410\\', \\'item_name\\': \\'Warframe\\', \\'playtime_forever\\': 1381, \\'playtime_2weeks\\': 59}, {\\'item_id\\': \\'3483\\', \\'item_name\\': \\'Peggle Extreme\\', \\'playtime_forever\\': 36, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'224500\\', \\'item_name\\': \\'Gnomoria\\', \\'playtime_forever\\': 13618, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'214730\\', \\'item_name\\': \\'Space Rangers HD: A War Apart\\', \\'playtime_forever\\': 598, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'233450\\', \\'item_name\\': \\'Prison Architect\\', \\'playtime_forever\\': 43, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'233720\\', \\'item_name\\': \\'Surgeon Simulator\\', \\'playtime_forever\\': 14, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'241600\\', \\'item_name\\': \\'Rogue Legacy\\', \\'playtime_forever\\': 227, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'238240\\', \\'item_name\\': \\'Edge of Space\\', \\'playtime_forever\\': 515, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'248390\\', \\'item_name\\': \\'Craft The World\\', \\'playtime_forever\\': 80, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'248820\\', \\'item_name\\': \\'Risk of Rain\\', \\'playtime_forever\\': 2328, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'239350\\', \\'item_name\\': \\'Spelunky\\', \\'playtime_forever\\': 5, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'2870\\', \\'item_name\\': \\'X Rebirth\\', \\'playtime_forever\\': 143, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'252110\\', \\'item_name\\': \\'Lovers in a Dangerous Spacetime\\', \\'playtime_forever\\': 45, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'254200\\', \\'item_name\\': \\'FortressCraft Evolved\\', \\'playtime_forever\\': 4671, \\'playtime_2weeks\\': 1}, {\\'item_id\\': \\'226620\\', \\'item_name\\': \\'Desktop Dungeons\\', \\'playtime_forever\\': 98, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'259570\\', \\'item_name\\': \\'Eden Star :: Destroy - Build - Protect\\', \\'playtime_forever\\': 158, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'221910\\', \\'item_name\\': \\'The Stanley Parable\\', \\'playtime_forever\\': 136, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'263360\\', \\'item_name\\': \\'3089 -- Futuristic Action RPG\\', \\'playtime_forever\\': 447, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'265950\\', \\'item_name\\': \\'Ether One\\', \\'playtime_forever\\': 52, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'391920\\', \\'item_name\\': \\'Ether One Redux\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'211820\\', \\'item_name\\': \\'Starbound\\', \\'playtime_forever\\': 67, \\'playtime_2weeks\\': 67}, {\\'item_id\\': \\'367540\\', \\'item_name\\': \\'Starbound - Unstable\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'275850\\', \\'item_name\\': \"No Man\\'s Sky\", \\'playtime_forever\\': 1219, \\'playtime_2weeks\\': 141}, {\\'item_id\\': \\'280220\\', \\'item_name\\': \\'Creeper World 3: Arc Eternal\\', \\'playtime_forever\\': 5690, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'282140\\', \\'item_name\\': \\'SOMA\\', \\'playtime_forever\\': 85, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'285310\\', \\'item_name\\': \\'RollerCoaster Tycoon: Deluxe\\', \\'playtime_forever\\': 2, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'292330\\', \\'item_name\\': \\'Starship Corporation\\', \\'playtime_forever\\': 269, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'226860\\', \\'item_name\\': \\'Galactic Civilizations III\\', \\'playtime_forever\\': 295, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'237930\\', \\'item_name\\': \\'Transistor\\', \\'playtime_forever\\': 508, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'38410\\', \\'item_name\\': \\'Fallout 2\\', \\'playtime_forever\\': 226, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'310380\\', \\'item_name\\': \\'Fractured Space\\', \\'playtime_forever\\': 7, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'9480\\', \\'item_name\\': \\'Saints Row 2\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'55230\\', \\'item_name\\': \\'Saints Row: The Third\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'206420\\', \\'item_name\\': \\'Saints Row IV\\', \\'playtime_forever\\': 1293, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'247080\\', \\'item_name\\': \\'Crypt of the NecroDancer\\', \\'playtime_forever\\': 114, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'315460\\', \\'item_name\\': \\'Dig or Die\\', \\'playtime_forever\\': 207, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'324260\\', \\'item_name\\': \\'VoidExpanse\\', \\'playtime_forever\\': 425, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'275670\\', \\'item_name\\': \\'Space Run\\', \\'playtime_forever\\': 580, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'328080\\', \\'item_name\\': \\'Retro-Pixel Castles\\', \\'playtime_forever\\': 2, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'730\\', \\'item_name\\': \\'Counter-Strike: Global Offensive\\', \\'playtime_forever\\': 1569, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'257510\\', \\'item_name\\': \\'The Talos Principle\\', \\'playtime_forever\\': 1164, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'319630\\', \\'item_name\\': \\'Life Is Strange™\\', \\'playtime_forever\\': 1024, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'340580\\', \\'item_name\\': \\'Armed with Wings: Rearmed\\', \\'playtime_forever\\': 7, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'344850\\', \\'item_name\\': \\'Big Pharma\\', \\'playtime_forever\\': 332, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'346010\\', \\'item_name\\': \\'Besiege\\', \\'playtime_forever\\': 22, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'245470\\', \\'item_name\\': \\'Democracy 3\\', \\'playtime_forever\\': 460, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'351510\\', \\'item_name\\': \\'Quiplash\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'365300\\', \\'item_name\\': \\'Transmissions: Element 120\\', \\'playtime_forever\\': 8, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'365450\\', \\'item_name\\': \\'Hacknet\\', \\'playtime_forever\\': 20, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'298180\\', \\'item_name\\': \\'The Desolate Hope\\', \\'playtime_forever\\': 17, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'238370\\', \\'item_name\\': \\'Magicka 2\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'397080\\', \\'item_name\\': \\'Magicka 2: Spell Balance Beta\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'370360\\', \\'item_name\\': \\'TIS-100\\', \\'playtime_forever\\': 403, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'322330\\', \\'item_name\\': \"Don\\'t Starve Together\", \\'playtime_forever\\': 138, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'372190\\', \\'item_name\\': \\'Astro Lords: Oort Cloud\\', \\'playtime_forever\\': 5, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'377160\\', \\'item_name\\': \\'Fallout 4\\', \\'playtime_forever\\': 8344, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'383120\\', \\'item_name\\': \\'Empyrion - Galactic Survival\\', \\'playtime_forever\\': 1803, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'388880\\', \\'item_name\\': \\'Oxenfree\\', \\'playtime_forever\\': 54, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'234140\\', \\'item_name\\': \\'Mad Max\\', \\'playtime_forever\\': 134, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'391540\\', \\'item_name\\': \\'Undertale\\', \\'playtime_forever\\': 30, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'396890\\', \\'item_name\\': \\'Gridworld\\', \\'playtime_forever\\': 5, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'337000\\', \\'item_name\\': \\'Deus Ex: Mankind Divided™\\', \\'playtime_forever\\': 65, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'404410\\', \\'item_name\\': \\'Endless Sky\\', \\'playtime_forever\\': 53, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'416690\\', \\'item_name\\': \\'Oh...Sir!\\', \\'playtime_forever\\': 0, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'268500\\', \\'item_name\\': \\'XCOM 2\\', \\'playtime_forever\\': 2107, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'427520\\', \\'item_name\\': \\'Factorio\\', \\'playtime_forever\\': 1658, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'364360\\', \\'item_name\\': \\'Total War: WARHAMMER\\', \\'playtime_forever\\': 29, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'452780\\', \\'item_name\\': \\'RuneScape: Idle Adventures\\', \\'playtime_forever\\': 2660, \\'playtime_2weeks\\': 2472}, {\\'item_id\\': \\'454060\\', \\'item_name\\': \\'Blueprint Tycoon\\', \\'playtime_forever\\': 23, \\'playtime_2weeks\\': 0}, {\\'item_id\\': \\'466170\\', \\'item_name\\': \\'Idling to Rule the Gods\\', \\'playtime_forever\\': 28545, \\'playtime_2weeks\\': 1554}]'"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item['items'][0] #Revisamos como estan los juegos que tenemos que desanidar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "3000f752",
   "metadata": {},
   "outputs": [],
   "source": [
    "eliminar=['items_count', 'user_url','Unnamed: 0'] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "c1e46695",
   "metadata": {},
   "outputs": [],
   "source": [
    "item.drop(eliminar, axis=1, inplace=True) #Eliminamos tablas que no nos sirvan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "76811893",
   "metadata": {},
   "outputs": [],
   "source": [
    "item['items'] = item['items'].str.replace(\"[\", \" \") #Limpiamos los textos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "2adbf337",
   "metadata": {},
   "outputs": [],
   "source": [
    "item['items'] = item['items'].str.replace(\"]\", \" \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "0e238f99",
   "metadata": {},
   "outputs": [],
   "source": [
    "item['items']= item['items'].str.split('}, {') #Los dividimos para tener los juegos por separados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "9506c2cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "item=item.explode('items', ignore_index=True)#Multiplicamos los registros para que en vez de que en un registro hayan muchos juegos, que haya 1 registro por juego del usuario"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "46d1d800",
   "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>steam_id</th>\n",
       "      <th>items</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>76561198007712555</td>\n",
       "      <td>{'item_id': '1200', 'item_name': 'Red Orchest...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>76561198007712555</td>\n",
       "      <td>'item_id': '1230', 'item_name': 'Mare Nostrum'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>76561198007712555</td>\n",
       "      <td>'item_id': '1280', 'item_name': \"Darkest Hour:...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>76561198007712555</td>\n",
       "      <td>'item_id': '1520', 'item_name': 'DEFCON', 'pla...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>76561198007712555</td>\n",
       "      <td>'item_id': '220', 'item_name': 'Half-Life 2', ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     user_id           steam_id  \\\n",
       "0  evcentric  76561198007712555   \n",
       "1  evcentric  76561198007712555   \n",
       "2  evcentric  76561198007712555   \n",
       "3  evcentric  76561198007712555   \n",
       "4  evcentric  76561198007712555   \n",
       "\n",
       "                                               items  \n",
       "0   {'item_id': '1200', 'item_name': 'Red Orchest...  \n",
       "1  'item_id': '1230', 'item_name': 'Mare Nostrum'...  \n",
       "2  'item_id': '1280', 'item_name': \"Darkest Hour:...  \n",
       "3  'item_id': '1520', 'item_name': 'DEFCON', 'pla...  \n",
       "4  'item_id': '220', 'item_name': 'Half-Life 2', ...  "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "7444c0b2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1289766, 3)"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "26b38654",
   "metadata": {},
   "outputs": [],
   "source": [
    "item['items'] = item['items'].str.replace(\"{\", \"\") #Limpiamos para normalizar todos los registros "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "e8365c86",
   "metadata": {},
   "outputs": [],
   "source": [
    "item['items'] = item['items'].str.replace(\"}\", \"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "da2aaac3",
   "metadata": {},
   "outputs": [],
   "source": [
    "item['items'] = item['items'].apply(lambda x: \"{\" + x + \"}\") #Le agregamos las llaves para que podamos convertirlos en diccionarios. Es importante la eliminacion de las llaves porque hay registros con 1 sola llave y algunos sin llaves"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "16efcd90",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"{ 'item_id': '1200', 'item_name': 'Red Orchestra: Ostfront 41-45', 'playtime_forever': 923, 'playtime_2weeks': 0}\""
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item['items'][0] #Revisamos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "82d7414b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import ast #Convertimos los registros en diccionarios\n",
    "item['items'] = item['items'].apply(lambda x: ast.literal_eval(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "d9bf2464",
   "metadata": {},
   "outputs": [],
   "source": [
    "aux= pd.json_normalize(item['items']) #Pasamos esos diccionarios a un dataframe auxiliar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "f2f52671",
   "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>item_id</th>\n",
       "      <th>item_name</th>\n",
       "      <th>playtime_forever</th>\n",
       "      <th>playtime_2weeks</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1200</td>\n",
       "      <td>Red Orchestra: Ostfront 41-45</td>\n",
       "      <td>923.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1230</td>\n",
       "      <td>Mare Nostrum</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1280</td>\n",
       "      <td>Darkest Hour: Europe '44-'45</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1520</td>\n",
       "      <td>DEFCON</td>\n",
       "      <td>158.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>220</td>\n",
       "      <td>Half-Life 2</td>\n",
       "      <td>1323.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  item_id                      item_name  playtime_forever  playtime_2weeks\n",
       "0    1200  Red Orchestra: Ostfront 41-45             923.0              0.0\n",
       "1    1230                   Mare Nostrum               0.0              0.0\n",
       "2    1280   Darkest Hour: Europe '44-'45               0.0              0.0\n",
       "3    1520                         DEFCON             158.0              0.0\n",
       "4     220                    Half-Life 2            1323.0              0.0"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aux.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "335bed21",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1289766, 4)\n",
      "(1289766, 3)\n"
     ]
    }
   ],
   "source": [
    "print(aux.shape) #Revisamos el tamaño de los dataframes para ver si coinciden los registros de uno y del otro para poder unirlos\n",
    "print (item.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "39fe8e45",
   "metadata": {},
   "outputs": [],
   "source": [
    "item['item_id']= aux['item_id'] #Agregamos la columna item_id del dataframe auxiliar al principal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "4c981fb0",
   "metadata": {},
   "outputs": [],
   "source": [
    "item['item_name']= aux['item_name'] #Agregamos la columna item_name el dataframe auxiliar al principal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "bced34aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "item['playtime_forever']= aux['playtime_forever'] #Agregamos la columna playtime_forever el dataframe auxiliar al principal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "117161da",
   "metadata": {},
   "outputs": [],
   "source": [
    "item['playtime_2weeks']= aux['playtime_2weeks']#Agregamos la columna playtime_2weeks el dataframe auxiliar al principal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "10480ccb",
   "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>steam_id</th>\n",
       "      <th>items</th>\n",
       "      <th>item_id</th>\n",
       "      <th>item_name</th>\n",
       "      <th>playtime_forever</th>\n",
       "      <th>playtime_2weeks</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>76561198007712555</td>\n",
       "      <td>{'item_id': '1200', 'item_name': 'Red Orchestr...</td>\n",
       "      <td>1200</td>\n",
       "      <td>Red Orchestra: Ostfront 41-45</td>\n",
       "      <td>923.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>76561198007712555</td>\n",
       "      <td>{'item_id': '1230', 'item_name': 'Mare Nostrum...</td>\n",
       "      <td>1230</td>\n",
       "      <td>Mare Nostrum</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>76561198007712555</td>\n",
       "      <td>{'item_id': '1280', 'item_name': 'Darkest Hour...</td>\n",
       "      <td>1280</td>\n",
       "      <td>Darkest Hour: Europe '44-'45</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>76561198007712555</td>\n",
       "      <td>{'item_id': '1520', 'item_name': 'DEFCON', 'pl...</td>\n",
       "      <td>1520</td>\n",
       "      <td>DEFCON</td>\n",
       "      <td>158.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>76561198007712555</td>\n",
       "      <td>{'item_id': '220', 'item_name': 'Half-Life 2',...</td>\n",
       "      <td>220</td>\n",
       "      <td>Half-Life 2</td>\n",
       "      <td>1323.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     user_id           steam_id  \\\n",
       "0  evcentric  76561198007712555   \n",
       "1  evcentric  76561198007712555   \n",
       "2  evcentric  76561198007712555   \n",
       "3  evcentric  76561198007712555   \n",
       "4  evcentric  76561198007712555   \n",
       "\n",
       "                                               items item_id  \\\n",
       "0  {'item_id': '1200', 'item_name': 'Red Orchestr...    1200   \n",
       "1  {'item_id': '1230', 'item_name': 'Mare Nostrum...    1230   \n",
       "2  {'item_id': '1280', 'item_name': 'Darkest Hour...    1280   \n",
       "3  {'item_id': '1520', 'item_name': 'DEFCON', 'pl...    1520   \n",
       "4  {'item_id': '220', 'item_name': 'Half-Life 2',...     220   \n",
       "\n",
       "                       item_name  playtime_forever  playtime_2weeks  \n",
       "0  Red Orchestra: Ostfront 41-45             923.0              0.0  \n",
       "1                   Mare Nostrum               0.0              0.0  \n",
       "2   Darkest Hour: Europe '44-'45               0.0              0.0  \n",
       "3                         DEFCON             158.0              0.0  \n",
       "4                    Half-Life 2            1323.0              0.0  "
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item.head() #Revisamos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "d2b59178",
   "metadata": {},
   "outputs": [],
   "source": [
    "item.drop('items', axis=1, inplace=True) #Borramos la columna que no necesitamos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "6de98bf5",
   "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>steam_id</th>\n",
       "      <th>item_id</th>\n",
       "      <th>item_name</th>\n",
       "      <th>playtime_forever</th>\n",
       "      <th>playtime_2weeks</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>76561198007712555</td>\n",
       "      <td>1200</td>\n",
       "      <td>Red Orchestra: Ostfront 41-45</td>\n",
       "      <td>923</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>76561198007712555</td>\n",
       "      <td>1230</td>\n",
       "      <td>Mare Nostrum</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>76561198007712555</td>\n",
       "      <td>1280</td>\n",
       "      <td>Darkest Hour: Europe '44-'45</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>76561198007712555</td>\n",
       "      <td>1520</td>\n",
       "      <td>DEFCON</td>\n",
       "      <td>158</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>evcentric</td>\n",
       "      <td>76561198007712555</td>\n",
       "      <td>220</td>\n",
       "      <td>Half-Life 2</td>\n",
       "      <td>1323</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     user_id           steam_id  item_id                      item_name  \\\n",
       "0  evcentric  76561198007712555     1200  Red Orchestra: Ostfront 41-45   \n",
       "1  evcentric  76561198007712555     1230                   Mare Nostrum   \n",
       "2  evcentric  76561198007712555     1280   Darkest Hour: Europe '44-'45   \n",
       "3  evcentric  76561198007712555     1520                         DEFCON   \n",
       "4  evcentric  76561198007712555      220                    Half-Life 2   \n",
       "\n",
       "   playtime_forever  playtime_2weeks  \n",
       "0               923                0  \n",
       "1                 0                0  \n",
       "2                 0                0  \n",
       "3               158                0  \n",
       "4              1323                0  "
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item.head() #Revisamos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "193f53e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "item.to_csv('item_funciones.csv', index= False) #Guardamos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "7eeb3bdb",
   "metadata": {},
   "outputs": [],
   "source": [
    "item=pd.read_csv('item_funciones.csv') #Abrimos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "a7fef860",
   "metadata": {},
   "outputs": [],
   "source": [
    "item = item.dropna(subset=['item_id']) #Borramos los registros nulos para convertir a entero"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "6c8abe57",
   "metadata": {},
   "outputs": [],
   "source": [
    "item['item_id'] = item['item_id'].astype(int) #Convertimos a entero"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "0c4dad33",
   "metadata": {},
   "outputs": [],
   "source": [
    "item['playtime_forever'] = item['playtime_forever'].astype(int) #Convertimos a entero"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "1acd73a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "item['playtime_2weeks'] = item['playtime_2weeks'].astype(int) #Convertimos a entero"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b7576b10",
   "metadata": {},
   "outputs": [],
   "source": [
    "item.drop('steam_id',axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "4c344841",
   "metadata": {},
   "outputs": [],
   "source": [
    "item.to_csv('item_funciones.csv', index= False) #Guardamos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "284195fa",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}