2-Exercies.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ca93c92e",
   "metadata": {},
   "source": [
    "# ETL II: Exercises\n",
    "\n",
    "## 1.- The flights data\n",
    "\n",
    "In this exercise, you will make an API call to obtain some data, and your task is to store that data in an SQL database with specific fields and format.\n",
    "\n",
    "\n",
    "\n",
    "The destination table should have the following structure:\n",
    "\n",
    "- id: A unique identifier for each flight (composed of the date in the format YYYYMMDD + flight number)\n",
    "- flightNumber: The flight number in the format AA0000 (can contain null values)\n",
    "- flightDate: The date of the flight (DATE format)\n",
    "- flightTime: The departure time of the flight in HH:MM format\n",
    "- arrivalTime: The arrival time of the flight in HH:MM format\n",
    "- aircraftType: The aircraft model in XXXX format\n",
    "- aircraftPlate: The aircraft plate number in XXXXXX format (can contain null values)\n",
    "- origin: The airport code of departure in XX format\n",
    "- destination: The airport code of arrival in XX format\n",
    "- status: The flight status in text format\n",
    "\n",
    "**TIP**: Before executing the Python code, you can practice making the requests in POSTMAN.\n",
    "\n",
    "**Notes:**\n",
    "- API base: `https://api.oan.one/assembler/`\n",
    "- API endpoint: `/etl/ej2`\n",
    "- Token Header: `oanToken`\n",
    "- Token: `ZF7xNEuLAZ5DKLQAEGVUq6VquGLQdsL7`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b81a1fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Type your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6cce2c5a",
   "metadata": {},
   "source": [
    "## 2. Spain Cities and Villages\n",
    "\n",
    "For this exercise, you need to create a database containing data from the geography of Spain.\n",
    "\n",
    "To accomplish this, you'll need to create three SQL tables containing the following information:\n",
    "\n",
    "**State (comunidades autónomas)**\n",
    "- id -> Sequential numeric value starting from 1001\n",
    "- countryCode -> Always \"ES\" in our case\n",
    "- name -> Name of the state\n",
    "\n",
    "**Region (provincia)**\n",
    "- id -> Sequential numeric value starting from 1001\n",
    "- stateID -> ID of the corresponding state\n",
    "- name -> Name of the region\n",
    "\n",
    "**Cities**\n",
    "- id -> Sequential numeric value starting from 1001\n",
    "- regionID -> ID of the corresponding region\n",
    "- Name -> Name of the city\n",
    "- Residenst -> Number of residents in each city\n",
    "\n",
    "<br>\n",
    "\n",
    "**Notes:**\n",
    "\n",
    "- The data must be extracted from Wikipedia by performing web scraping on the page: https://es.wikipedia.org/wiki/Anexo:Municipios_de_Espa%C3%B1a_por_poblaci%C3%B3n\n",
    "\n",
    "- Keep in mind that you'll need to clean and standardize the data formats."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ef750259",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Type your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a43b442e",
   "metadata": {},
   "source": [
    "## 3. Web Scrapping - Ibex 35\n",
    "\n",
    "<img src = \"https://media.istockphoto.com/id/1317587887/es/foto/gr%C3%A1ficos-de-trading-en-una-pantalla.jpg?s=612x612&w=0&k=20&c=LyckBY4eyaJIm-y9pUVfA5THPwnRYRIHTm7sIt3dVgc=\">\n",
    "\n",
    "Now, we're going to \"download\" (haha) some data from a journal about economic new called Expansion. In the digital version of this newspaper they are showing information about the Ibex35 (The IBEX 35 is the benchmark stock market index of the Spanish stock exchange), if you go to the following website https://www.expansion.com/mercados/cotizaciones/indices/ibex35_I.IB.html you can find the stock market information about the best 35 companies in Spain. Our mission ? - Simple, do some webscrapping with Beautiful Soup and get the dataframe of the table with the information about the 35 companies.\n",
    "\n",
    "**Optional**: Export the data to a relational database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fb903673",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Type your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a3929383",
   "metadata": {},
   "source": [
    "## 4. Web Scrapping - The \"millardos\" List\n",
    "\n",
    "<img src = \"https://upload.wikimedia.org/wikipedia/commons/thumb/d/db/Forbes_logo.svg/640px-Forbes_logo.svg.png\">\n",
    "\n",
    "Lets keep \"downloading\" more data, now we're going to get data from Wikipedia, in this case information about the concept \"_millardo_\" (in Spanish) that means number of multimillionaries, to do this, take the following url https://es.wikipedia.org/wiki/Anexo:Milmillonarios_seg%C3%BAn_Forbes you must obtain the table that appears in the statistics that are in the heading: \"Estadísticas. De acuerdo con la RAE la palabra \"millardo\" es la que expresa la cantidad de mil millones. Finally, plot the time series of Year and number of multi millionaries\n",
    "\n",
    "**Notes**: Here the way to use the `find` function will be a little different, since we are not going to search for a specific _id_, our table is contained within an element `<table class = .../ >`, therefore, the way to access the code is a little different, here we show how to access said table element.\n",
    "\n",
    "`table = soup.find(\"table\", {\"class\":\"wikitable sortable col1cen col2cen col3cen\"})`\n",
    "\n",
    "**Optional**: Export the data to a relational database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "63a26a32",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Type your code here"
   ]
  }
 ],
 "metadata": {
  "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.10.9"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}