{ "nbformat": 4, "nbformat_minor": 0, "metadata": { "colab": { "provenance": [] }, "kernelspec": { "name": "python3", "display_name": "Python 3" }, "language_info": { "name": "python" } }, "cells": [ { "cell_type": "markdown", "source": [ "# Descarga de datos" ], "metadata": { "id": "LrkD-O27_uMb" } }, { "cell_type": "code", "source": [ "from google.colab import output\n", "!pip install gdown\n", "!gdown https://drive.google.com/uc?id=1L5kNJEwRRlNtBvXVt4KZB0EiTQk23iaV # pokedex_processed.csv\n", "!gdown https://drive.google.com/uc?id=19dw2vVgF6pht_-GF5_IvxrVLOSkT8kCQ # pokedex_processed.json\n", "output.clear()" ], "metadata": { "id": "49NvaucrxgAx" }, "execution_count": 2, "outputs": [] }, { "cell_type": "markdown", "source": [ "# Datos\n", "\n", "Los datos corresponden al mundo de _Pokémon_ separado en dos bases de datos: Una relacional SQL (PostgreSQL) y otra de documentos NoSQL (MongoDB). Ambas son levantadas dentro del entorno de ejecución del notebook en Google Colab.\n", "\n", "A continuación se crea y describe a cada una:" ], "metadata": { "id": "91HdJj2_njyA" } }, { "cell_type": "markdown", "source": [ "# PostgreSQL\n", "\n", "Cada columna de la tabla de pokedex se describe a continuación:\n", "\n", "```text\n", "###################\n", "## Tabla pokedex ##\n", "###################\n", "\n", "Id : (int) Identificador único en el Pokédex\n", "Name : (str) Nombre del Pokémon\n", "Category : (str) La \"especie\" del Pokémon (en Pokémon Sol y Luna)\n", "Height (m) : (int) Altura en metros\n", "Weight (kg) : (int) Masa en kilogramos\n", "Capture Rate : (int) Cuantifica la probabilidad de capturar al Pokémon (a mayor valor, más probable).\n", "Egg Steps : (int) Cuantifica el número de pasos que requiere en el juego para salir de un huevo.\n", "Exp Group : (str) Categoría que determina cuanta experiencia necesita para subir de nivel.\n", "HP : (int) Vida\n", "Attack : (int) Ataque\n", "Defense : (int) Defensa\n", "Sp. Attack : (int) Ataque Especial\n", "Sp. Defense : (int) Defensa Especial\n", "Speed : (int) Velocidad\n", "\n", "```" ], "metadata": { "id": "rzyH3vy1vmAr" } }, { "cell_type": "code", "execution_count": 3, "metadata": { "id": "Gy0QSzg_nLyx" }, "outputs": [], "source": [ "# install\n", "!apt update\n", "!apt install postgresql postgresql-contrib &>log\n", "!service postgresql start\n", "!sudo -u postgres psql -c \"CREATE USER root WITH SUPERUSER\"\n", "# pin package versions for google colab compatibility\n", "!pip install SQLAlchemy==1.4.46\n", "!pip install ipython-sql==0.4.1\n", "# set connection\n", "%load_ext sql\n", "%config SqlMagic.feedback=False\n", "%config SqlMagic.autopandas=True\n", "%config SqlMagic.displaycon = False\n", "%sql postgresql+psycopg2://@/postgres\n", "output.clear()" ] }, { "cell_type": "code", "source": [ "import pandas as pd\n", "from sqlalchemy import create_engine\n", "engine = create_engine(\"postgresql+psycopg2://@/postgres\", echo=False)\n", "df_pokedex = pd.read_csv(\"pokedex_processed.csv\", index_col=False)\n", "df_pokedex.to_sql(\"pokedex\", con=engine, if_exists=\"replace\", index=False)" ], "metadata": { "id": "5vJcpsIhT3NL" }, "execution_count": null, "outputs": [] }, { "cell_type": "code", "source": [ "%%sql\n", "SELECT *\n", "FROM pokedex\n", "LIMIT 10" ], "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 407 }, "id": "L_3X_BE4Rr0q", "outputId": "e50d918a-8a39-4c93-c7c8-fdf83b080374" }, "execution_count": 5, "outputs": [ { "output_type": "execute_result", "data": { "text/plain": [ " Id Name Category Height (m) Weight (kg) Capture Rate \\\n", "0 1 Bulbasaur Seed Pokémon 0.7 6.9 45 \n", "1 2 Ivysaur Seed Pokémon 1.0 13.0 45 \n", "2 3 Venusaur Seed Pokémon 2.0 100.0 45 \n", "3 4 Charmander Lizard Pokémon 0.6 8.5 45 \n", "4 5 Charmeleon Flame Pokémon 1.1 19.0 45 \n", "5 6 Charizard Flame Pokémon 1.7 90.5 45 \n", "6 7 Squirtle Tiny Turtle Pokémon 0.5 9.0 45 \n", "7 8 Wartortle Turtle Pokémon 1.0 22.5 45 \n", "8 9 Blastoise Shellfish Pokémon 1.6 85.5 45 \n", "9 10 Caterpie Worm Pokémon 0.3 2.9 255 \n", "\n", " Egg Steps Exp Group HP Attack Defense Sp. Attack Sp. Defense Speed \n", "0 5120 Medium Slow 45 49 49 65 65 45 \n", "1 5120 Medium Slow 60 62 63 80 80 60 \n", "2 5120 Medium Slow 80 82 83 100 100 80 \n", "3 5120 Medium Slow 39 52 43 60 50 65 \n", "4 5120 Medium Slow 58 64 58 80 65 80 \n", "5 5120 Medium Slow 78 84 78 109 85 100 \n", "6 5120 Medium Slow 44 48 65 50 64 43 \n", "7 5120 Medium Slow 59 63 80 65 80 58 \n", "8 5120 Medium Slow 79 83 100 85 105 78 \n", "9 3840 Medium Fast 45 30 35 20 20 45 " ], "text/html": [ "\n", "
\n", "
\n", "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
IdNameCategoryHeight (m)Weight (kg)Capture RateEgg StepsExp GroupHPAttackDefenseSp. AttackSp. DefenseSpeed
01BulbasaurSeed Pokémon0.76.9455120Medium Slow454949656545
12IvysaurSeed Pokémon1.013.0455120Medium Slow606263808060
23VenusaurSeed Pokémon2.0100.0455120Medium Slow80828310010080
34CharmanderLizard Pokémon0.68.5455120Medium Slow395243605065
45CharmeleonFlame Pokémon1.119.0455120Medium Slow586458806580
56CharizardFlame Pokémon1.790.5455120Medium Slow78847810985100
67SquirtleTiny Turtle Pokémon0.59.0455120Medium Slow444865506443
78WartortleTurtle Pokémon1.022.5455120Medium Slow596380658058
89BlastoiseShellfish Pokémon1.685.5455120Medium Slow79831008510578
910CaterpieWorm Pokémon0.32.92553840Medium Fast453035202045
\n", "
\n", " \n", " \n", " \n", "\n", " \n", "
\n", "
\n", " " ] }, "metadata": {}, "execution_count": 5 } ] }, { "cell_type": "markdown", "source": [ "## MongoDB\n", "\n", "La colección pokedex tiene documentos con 4 entradas: Un identificador único del pokémon y arreglos con información acerca del mismo. A continuación se detalla cada uno:\n", "\n", "```text\n", "#######################\n", "## Colección pokedex ##\n", "#######################\n", "\n", "\"pokemon_id\": : (int) Número identificador único en el Pokédex\n", "\"abilities\" : (List[str]) Lista de las habilidades del Pokémon\n", "\"moves\" : (List[str]) Lista de los movimientos del Pokémon\n", "\"types\" : (List[str]) Lista de los tipos del Pokémon\n", "```" ], "metadata": { "id": "2eJi_0kxYgZ5" } }, { "cell_type": "code", "source": [ "!apt install mongodb\n", "!service mongodb start\n", "!pip install pymongo\n", "!mongoimport --db local --collection pokedex --jsonArray < 'pokedex_processed.json'\n", "output.clear()" ], "metadata": { "id": "t1x82tUDbzFP" }, "execution_count": 6, "outputs": [] }, { "cell_type": "code", "source": [ "from pymongo import MongoClient\n", "client = MongoClient()\n", "client.list_database_names() # ['admin', 'local']\n", "db = client['local']\n", "collection = db['pokedex']" ], "metadata": { "id": "nsfUhP4tdbjG" }, "execution_count": 7, "outputs": [] }, { "cell_type": "markdown", "source": [ "Para tener una idea de cómo se ven los datos en la colección, aquí les dejamos impresión del primer Pokemon en la colección:" ], "metadata": { "id": "NVePaOEVbVXN" } }, { "cell_type": "code", "source": [ "import pprint\n", "for pokemon in collection.find({}).limit(1):\n", " pprint.pprint(pokemon)" ], "metadata": { "id": "hbvueZMVva62", "colab": { "base_uri": "https://localhost:8080/" }, "outputId": "8df6b60b-8738-4854-c67d-85bb2e5cc5b5" }, "execution_count": 8, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ "{'_id': ObjectId('648a420b82e29c2db100d267'),\n", " 'abilities': ['Overgrow', 'Chlorophyll'],\n", " 'moves': ['Tackle is described as A physical attack in which the user charges '\n", " 'and slams into the target with its whole body.',\n", " 'Growl is described as The user growls in an endearing way, making '\n", " 'opposing Pokémon less wary. This lowers their Attack stat.',\n", " 'Vine Whip is described as The target is struck with slender, '\n", " 'whiplike vines to inflict damage.',\n", " 'Poison Powder is described as The user scatters a cloud of '\n", " 'poisonous dust that poisons the target.',\n", " 'Sleep Powder is described as The user scatters a big cloud of '\n", " 'sleep-inducing dust around the target.',\n", " 'Take Down is described as A reckless, full-body charge attack for '\n", " 'slamming into the target. This also damages the user a little.',\n", " 'Razor Leaf is described as Sharp-edged leaves are launched to '\n", " 'slash at the opposing Pokémon. Critical hits land more easily.',\n", " 'Sweet Scent is described as A sweet scent that harshly lowers '\n", " \"opposing Pokémon's evasiveness.\",\n", " \"Growth is described as The user's body grows all at once, raising \"\n", " 'the Attack and Sp. Atk stats.',\n", " 'Double-Edge is described as A reckless, life-risking tackle. This '\n", " 'also damages the user quite a lot.',\n", " 'Worry Seed is described as A seed that causes worry is planted on '\n", " \"the target. It prevents sleep by making the target's Ability \"\n", " 'Insomnia.',\n", " 'Synthesis is described as The user restores its own HP. The amount '\n", " 'of HP regained varies with the weather.',\n", " 'Seed Bomb is described as The user slams a barrage of hard-shelled '\n", " 'seeds down on the target from above.'],\n", " 'pokemon_id': 1,\n", " 'types': ['Grass', 'Poison']}\n" ] } ] }, { "cell_type": "markdown", "source": [ "# Preguntas\n", "\n", "Para resolver estas preguntas será necesario que consultes a `PostgreSQL` y/o `MongoDB`.\n", "\n", "En este `notebook` puedes almacenar el resultado de una consulta `PostgreSQL` en una variable como un `pandas.DataFrame` de la siguiente forma:\n", "\n", "```python\n", "df = %sql SELECT \"Name\" FROM pokedex WHERE \"Id\" = 99;\n", "```" ], "metadata": { "id": "oT0-dqCCqset" } }, { "cell_type": "markdown", "source": [ "## Pregunta 1\n", "Entregue el nombre (name) y la cantidad de habilidades (abilites) de los 10 pokemones más pesados en kilogramos" ], "metadata": { "id": "bhpyZHJkqpU-" } }, { "cell_type": "code", "source": [], "metadata": { "id": "E8jIPueoqqb-" }, "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "source": [ "## Pregunta 2\n", "\n", "Nombre los 3 pokemones con más movimientos. Se recomienda revisar la documentación de MongoDB, particularmente de [`$sort`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/sort/) y [`$limit`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/limit/)." ], "metadata": { "id": "kkCvJbF7quNQ" } }, { "cell_type": "code", "source": [], "metadata": { "id": "6dDaoE1HqvFC" }, "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "source": [ "## Pregunta 3\n", "\n", "Cuál es el elemento de los pokemones más densos? Indique los 5 elementos de pokemones más densos. Asuma que la densidad es el peso dividido en la altura, y que los pokemones tienen un solo tipo (ignore todos los elementos de cada pokemon excepto uno)." ], "metadata": { "id": "byZJFhmOyUDf" } }, { "cell_type": "code", "source": [], "metadata": { "id": "OM8TSVJeyVQE" }, "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "source": [ "## Pregunta 4\n", "\n", "Vea si existe alguna correlación entre algún atributo (ataque, defensa, ataque especial, defensa especial, velocidad y HP) y la cantidad de movimientos que tienen los pokemones.\n", "\n", "Hay algun otro par de variables (de las mencionadas anteriormente) que tengan mayor correlación entre sí? Indique el par de atributos que tengan la mayor correlación entre sí y haga un gráfico de dispersión para visualizar la correlación." ], "metadata": { "id": "2SrvuCWlyYwG" } }, { "cell_type": "code", "source": [], "metadata": { "id": "kBimnR_hE1xy" }, "execution_count": 1, "outputs": [] }, { "cell_type": "markdown", "source": [ "Los atributos con mayor correlación son YY y ZZ." ], "metadata": { "id": "OOKCMGPGI0gV" } }, { "cell_type": "code", "source": [], "metadata": { "id": "FZmGvze__Ytj" }, "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "source": [ "## Importante:\n", "Para las últimas preguntas será necesario que crees y consultes un índice de texto para el arreglo de strings `moves`." ], "metadata": { "id": "iX3rGsPhx0L9" } }, { "cell_type": "code", "source": [ "# Aquí escribe el código para crear el índice de texto\n" ], "metadata": { "id": "sGsiV5cux195" }, "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "source": [ "## Pregunta 5\n", "Entregue el nombre de todos los pokemones los cuales en sus movimientos contengan las palabras `fire` y `thunder` y tengan un Ataque Especial (Sp. Attack) entre `110` y `130` (ambos valores incluidos)" ], "metadata": { "id": "RuQdiWyNyarx" } }, { "cell_type": "code", "source": [], "metadata": { "id": "CiYsZtaiybPR" }, "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "source": [ "## Pregunta 6\n", "Entregue el nombre junto con la suma de las estadísticas de combate (HP, Attack, Defense, Sp. Attack, Sp. Defense y Speed) de los pokemones que en sus movimientos contengan las palabras `ice`, `freeze`, `snow` o `cold`, **pero no** las palabras `water` y `wind`. Deben estar ordenado alfabéticamente por su nombre." ], "metadata": { "id": "Xh15tdKwyby1" } }, { "cell_type": "code", "source": [], "metadata": { "id": "Uwssos-aycNj" }, "execution_count": null, "outputs": [] } ] }