Desafio02-IGTI_MongoDB.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1>Ingestão de Dados no MongoDB</h1>\n",
    "Éverton Bin | Desafio 2 - Bootcamp Cientista de Dados IGTI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importando os pacotes:\n",
    "import pymongo\n",
    "import json\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Conectando com o banco de dados:\n",
    "con = pymongo.MongoClient('localhost', 27017)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['admin', 'config', 'db_desafio', 'local']"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Listando os bancos de dados:\n",
    "con.list_database_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Selecionando o banco de dados:\n",
    "db = con.db_desafio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['collection_desafio']"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Listando as coleções:\n",
    "db.list_collection_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Selecionando a coleção:\n",
    "collection = db.collection_desafio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lendo o arquivo .json para ser inserido na coleção:\n",
    "doc_r = open('videogame_sales_mongo.json', )\n",
    "doc = json.load(doc_r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Excluindo o id:\n",
    "doc_no_id = []\n",
    "for i in range(0, len(doc)):\n",
    "    sub_doc = {'Rank': doc[i]['Rank'], 'Name': doc[i]['Name'], 'Platform': doc[i]['Platform'], 'Year': doc[i]['Year'], \n",
    "               'Genre': doc[i]['Genre'], 'Publisher': doc[i]['Publisher'], 'NA_Sales': doc[i]['NA_Sales'], \n",
    "               'EU_Sales': doc[i]['EU_Sales'], 'JP_Sales': doc[i]['JP_Sales'], 'Other_Sales': doc[i]['Other_Sales'], \n",
    "               'Global_Sales': doc[i]['Global_Sales']}\n",
    "    doc_no_id.append(sub_doc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.InsertManyResult at 0x1039df2c948>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Inserindo os documentos na coleção:\n",
    "collection.insert_many(doc_no_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1>Consultando o Banco de Dados MongoDB</h1>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': ObjectId('5f84c7e2b894e143aad8db8b'),\n",
       " 'Rank': 18,\n",
       " 'Name': 'Grand Theft Auto: San Andreas',\n",
       " 'Platform': 'PS2',\n",
       " 'Year': 2004,\n",
       " 'Genre': 'Action',\n",
       " 'Publisher': 'Take-Two Interactive',\n",
       " 'NA_Sales': 9.43,\n",
       " 'EU_Sales': 0.4,\n",
       " 'JP_Sales': 0.41,\n",
       " 'Other_Sales': 10.57,\n",
       " 'Global_Sales': 20.81}"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Qual jogo possui a maior \"Golbal Sales\" para a plataforma PS2?\n",
    "collection.find_one({'Platform': 'PS2'}, sort = [('Global_Sales', -1)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('5f84c7e2b894e143aad8db7a'), 'Rank': 1, 'Name': 'Wii Sports', 'Platform': 'Wii', 'Year': 2006, 'Genre': 'Sports', 'Publisher': 'Nintendo', 'NA_Sales': 41.49, 'EU_Sales': 29.02, 'JP_Sales': 3.77, 'Other_Sales': 8.46, 'Global_Sales': 82.74}\n",
      "{'_id': ObjectId('5f84c7e2b894e143aad8db7c'), 'Rank': 3, 'Name': 'Mario Kart Wii', 'Platform': 'Wii', 'Year': 2008, 'Genre': 'Racing', 'Publisher': 'Nintendo', 'NA_Sales': 15.85, 'EU_Sales': 12.88, 'JP_Sales': 3.79, 'Other_Sales': 3.31, 'Global_Sales': 35.82}\n"
     ]
    }
   ],
   "source": [
    "# Em qual posição no rank global está o segundo jogo mais vendido na Europa?\n",
    "print(collection.find({}, sort = [('EU_Sales', -1)])[0])\n",
    "print(collection.find({}, sort = [('EU_Sales', -1)])[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': ObjectId('5f84c7e2b894e143aad919ec'),\n",
       " 'Rank': 15989,\n",
       " 'Name': 'Sora no Otoshimono: DokiDoki Summer Vacation',\n",
       " 'Platform': 'PSP',\n",
       " 'Year': 2010,\n",
       " 'Genre': 'Adventure',\n",
       " 'Publisher': 'Kadokawa Shoten',\n",
       " 'NA_Sales': 0,\n",
       " 'EU_Sales': 0,\n",
       " 'JP_Sales': 0.01,\n",
       " 'Other_Sales': 0,\n",
       " 'Global_Sales': 0.01}"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Qual é o jogo que possui a menor venda global no ano de 2010?\n",
    "collection.find_one({'Year': 2010}, sort = [('Global_Sales', 1)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': ObjectId('5f84c7e2b894e143aad8db7c'),\n",
       " 'Rank': 3,\n",
       " 'Name': 'Mario Kart Wii',\n",
       " 'Platform': 'Wii',\n",
       " 'Year': 2008,\n",
       " 'Genre': 'Racing',\n",
       " 'Publisher': 'Nintendo',\n",
       " 'NA_Sales': 15.85,\n",
       " 'EU_Sales': 12.88,\n",
       " 'JP_Sales': 3.79,\n",
       " 'Other_Sales': 3.31,\n",
       " 'Global_Sales': 35.82}"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Qual é a plataforma do jogo mais vendido globalmente no ano de 2008?\n",
    "collection.find_one({'Year': 2008}, sort = [('Global_Sales', -1)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': ObjectId('5f84c7e2b894e143aad8db9b'),\n",
       " 'Rank': 34,\n",
       " 'Name': 'Call of Duty: Black Ops 3',\n",
       " 'Platform': 'PS4',\n",
       " 'Year': 2015,\n",
       " 'Genre': 'Shooter',\n",
       " 'Publisher': 'Activision',\n",
       " 'NA_Sales': 5.77,\n",
       " 'EU_Sales': 5.81,\n",
       " 'JP_Sales': 0.35,\n",
       " 'Other_Sales': 2.31,\n",
       " 'Global_Sales': 14.24}"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Qual jogo é exibido ao solicitarmos o primeiro documento com a condição de Plataforma = PS4?\n",
    "collection.find_one({'Platform': 'PS4'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1265"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Quantos documentos existem no banco de dados para a Plataforma X360?\n",
    "x360 = collection.find({'Platform': 'X360'})\n",
    "x360.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': ObjectId('5f84c7e2b894e143aad8dd18'),\n",
       " 'Rank': 415,\n",
       " 'Name': 'Monster Hunter X',\n",
       " 'Platform': '3DS',\n",
       " 'Year': 2015,\n",
       " 'Genre': 'Action',\n",
       " 'Publisher': 'Capcom',\n",
       " 'NA_Sales': 0.25,\n",
       " 'EU_Sales': 0.19,\n",
       " 'JP_Sales': 2.78,\n",
       " 'Other_Sales': 0.04,\n",
       " 'Global_Sales': 3.26}"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Qual é a plataforma do jogo mais vendido no Japão no ano de 2015?\n",
    "collection.find_one({'Year': 2015}, sort = [('JP_Sales', -1)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': ObjectId('5f84c7e2b894e143aad8e1bf'), 'Rank': 1607, 'Name': 'The Witcher 3: Wild Hunt', 'Platform': 'XOne', 'Year': 2015, 'Genre': 'Role-Playing', 'Publisher': 'Namco Bandai Games', 'NA_Sales': 0.66, 'EU_Sales': 0.47, 'JP_Sales': 0, 'Other_Sales': 0.11, 'Global_Sales': 1.24}\n"
     ]
    }
   ],
   "source": [
    "# Em que posição do Rank o jogo \"The Witcher 3: Wild Hunt\" está, considerando a plataforma XOne?\n",
    "for docs in collection.find({'Name': 'The Witcher 3: Wild Hunt', 'Platform': 'XOne'}):\n",
    "    print(docs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "851"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# O banco de dados possui quantos documentos cujo valor de vendas globais seja maior ou igual a 2?\n",
    "global_sales = collection.find({'Global_Sales': {'$gte': 2}})\n",
    "global_sales.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "con.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}