ETL_Engineer_Peer_Review_Assignment.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style=\"text-align:center\">\n",
    "    <a href=\"https://skills.network/?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkPY0221ENSkillsNetwork899-2023-01-01\">\n",
    "    <img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/assets/logos/SN_web_lightmode.png\" width=\"200\" alt=\"Skills Network Logo\"  />\n",
    "    </a>\n",
    "</p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Peer Review Assignment - Data Engineer - ETL\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Estimated time needed: **20** minutes\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Objectives\n",
    "\n",
    "In this final part you will:\n",
    "\n",
    "- Run the ETL process\n",
    "- Extract bank and market cap data from the JSON file `bank_market_cap.json`\n",
    "- Transform the market cap currency using the exchange rate data\n",
    "- Load the transformed data into a seperate CSV\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For this lab, we are going to be using Python and several Python libraries. Some of these libraries might be installed in your lab environment or in SN Labs. Others may need to be installed by you. The cells below will install these libraries when executed.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "#!mamba install pandas==1.3.3 -y\n",
    "#!mamba install requests==2.26.0 -y"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Imports\n",
    "\n",
    "Import any additional libraries you may need here.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import glob\n",
    "import pandas as pd\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As the exchange rate fluctuates, we will download the same dataset to make marking simpler. This will be in the same format as the dataset you used in the last section  \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--2023-08-23 13:52:48--  https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-PY0221EN-SkillsNetwork/labs/module%206/Lab%20-%20Extract%20Transform%20Load/data/bank_market_cap_1.json\n",
      "Resolving cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud (cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud)... 169.63.118.104\n",
      "Connecting to cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud (cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud)|169.63.118.104|:443... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2815 (2.7K) [application/json]\n",
      "Saving to: ‘bank_market_cap_1.json’\n",
      "\n",
      "bank_market_cap_1.j 100%[===================>]   2.75K  --.-KB/s    in 0s      \n",
      "\n",
      "2023-08-23 13:52:48 (13.7 MB/s) - ‘bank_market_cap_1.json’ saved [2815/2815]\n",
      "\n",
      "--2023-08-23 13:52:49--  https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-PY0221EN-SkillsNetwork/labs/module%206/Lab%20-%20Extract%20Transform%20Load/data/bank_market_cap_2.json\n",
      "Resolving cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud (cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud)... 169.63.118.104\n",
      "Connecting to cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud (cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud)|169.63.118.104|:443... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 1429 (1.4K) [application/json]\n",
      "Saving to: ‘bank_market_cap_2.json’\n",
      "\n",
      "bank_market_cap_2.j 100%[===================>]   1.40K  --.-KB/s    in 0s      \n",
      "\n",
      "2023-08-23 13:52:49 (11.4 MB/s) - ‘bank_market_cap_2.json’ saved [1429/1429]\n",
      "\n",
      "--2023-08-23 13:52:50--  https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-PY0221EN-SkillsNetwork/labs/module%206/Final%20Assignment/exchange_rates.csv\n",
      "Resolving cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud (cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud)... 169.63.118.104\n",
      "Connecting to cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud (cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud)|169.63.118.104|:443... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 590 [text/csv]\n",
      "Saving to: ‘exchange_rates.csv’\n",
      "\n",
      "exchange_rates.csv  100%[===================>]     590  --.-KB/s    in 0s      \n",
      "\n",
      "2023-08-23 13:52:50 (4.87 MB/s) - ‘exchange_rates.csv’ saved [590/590]\n",
      "\n"
     ]
    }
   ],
   "source": [
    "!wget https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-PY0221EN-SkillsNetwork/labs/module%206/Lab%20-%20Extract%20Transform%20Load/data/bank_market_cap_1.json\n",
    "!wget https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-PY0221EN-SkillsNetwork/labs/module%206/Lab%20-%20Extract%20Transform%20Load/data/bank_market_cap_2.json\n",
    "!wget https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-PY0221EN-SkillsNetwork/labs/module%206/Final%20Assignment/exchange_rates.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extract\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### JSON Extract Function\n",
    "\n",
    "This function will extract JSON files.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "def extract_from_json(file_to_process):\n",
    "    dataframe = pd.read_json(file_to_process)\n",
    "    return dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extract Function\n",
    "\n",
    "Define the extract function that finds JSON file `bank_market_cap_1.json` and calls the function created above to extract data from them. Store the data in a `pandas` dataframe. Use the following list for the columns.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "columns=['Name','Market Cap (US$ Billion)']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "tags": []
   },
   "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>Name</th>\n",
       "      <th>Market Cap (US$ Billion)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>JPMorgan Chase</td>\n",
       "      <td>390.934</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Industrial and Commercial Bank of China</td>\n",
       "      <td>345.214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bank of America</td>\n",
       "      <td>325.331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Wells Fargo</td>\n",
       "      <td>308.013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>China Construction Bank</td>\n",
       "      <td>257.399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>Ping An Bank</td>\n",
       "      <td>37.993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>Standard Chartered</td>\n",
       "      <td>37.319</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>United Overseas Bank</td>\n",
       "      <td>35.128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>QNB Group</td>\n",
       "      <td>33.560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>Bank Rakyat</td>\n",
       "      <td>33.081</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>70 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       Name  Market Cap (US$ Billion)\n",
       "0                            JPMorgan Chase                   390.934\n",
       "1   Industrial and Commercial Bank of China                   345.214\n",
       "2                           Bank of America                   325.331\n",
       "3                               Wells Fargo                   308.013\n",
       "4                   China Construction Bank                   257.399\n",
       "..                                      ...                       ...\n",
       "65                             Ping An Bank                    37.993\n",
       "66                       Standard Chartered                    37.319\n",
       "67                     United Overseas Bank                    35.128\n",
       "68                                QNB Group                    33.560\n",
       "69                              Bank Rakyat                    33.081\n",
       "\n",
       "[70 rows x 2 columns]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def extract():\n",
    "    extracted_data = pd.DataFrame(extract_from_json('bank_market_cap_1.json'),columns=['Name','Market Cap (US$ Billion)'])\n",
    "    return extracted_data\n",
    "    #process all csv files\n",
    "extracted_data = extract()\n",
    "extracted_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Question 1</b> Load the file <code>exchange_rates.csv</code> as a dataframe and find the exchange rate for British pounds with the symbol <code>GBP</code>, store it in the variable  <code>exchange_rate</code>, you will be asked for the number. Hint: set the parameter  <code>index_col</code> to 0.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "tags": []
   },
   "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>Rates</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AUD</td>\n",
       "      <td>1.297088</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BGN</td>\n",
       "      <td>1.608653</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BRL</td>\n",
       "      <td>5.409196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CAD</td>\n",
       "      <td>1.271426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CHF</td>\n",
       "      <td>0.886083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CNY</td>\n",
       "      <td>6.483139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CZK</td>\n",
       "      <td>21.510117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>DKK</td>\n",
       "      <td>6.119757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>EUR</td>\n",
       "      <td>0.822504</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>GBP</td>\n",
       "      <td>0.732398</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>HKD</td>\n",
       "      <td>7.752509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>HRK</td>\n",
       "      <td>6.222652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>HUF</td>\n",
       "      <td>294.135549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>IDR</td>\n",
       "      <td>14097.902616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>ILS</td>\n",
       "      <td>3.281132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>INR</td>\n",
       "      <td>73.011186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>ISK</td>\n",
       "      <td>129.133081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>JPY</td>\n",
       "      <td>103.791742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>KRW</td>\n",
       "      <td>1105.839776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>MXN</td>\n",
       "      <td>19.932966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>MYR</td>\n",
       "      <td>4.043017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>NOK</td>\n",
       "      <td>8.497121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>NZD</td>\n",
       "      <td>1.393733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>PHP</td>\n",
       "      <td>48.070406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>PLN</td>\n",
       "      <td>3.732933</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>RON</td>\n",
       "      <td>4.008883</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>RUB</td>\n",
       "      <td>74.930827</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>SEK</td>\n",
       "      <td>8.292071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>SGD</td>\n",
       "      <td>1.328261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>THB</td>\n",
       "      <td>30.009870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>TRY</td>\n",
       "      <td>7.418572</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>USD</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>ZAR</td>\n",
       "      <td>15.118441</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0         Rates\n",
       "0         AUD      1.297088\n",
       "1         BGN      1.608653\n",
       "2         BRL      5.409196\n",
       "3         CAD      1.271426\n",
       "4         CHF      0.886083\n",
       "5         CNY      6.483139\n",
       "6         CZK     21.510117\n",
       "7         DKK      6.119757\n",
       "8         EUR      0.822504\n",
       "9         GBP      0.732398\n",
       "10        HKD      7.752509\n",
       "11        HRK      6.222652\n",
       "12        HUF    294.135549\n",
       "13        IDR  14097.902616\n",
       "14        ILS      3.281132\n",
       "15        INR     73.011186\n",
       "16        ISK    129.133081\n",
       "17        JPY    103.791742\n",
       "18        KRW   1105.839776\n",
       "19        MXN     19.932966\n",
       "20        MYR      4.043017\n",
       "21        NOK      8.497121\n",
       "22        NZD      1.393733\n",
       "23        PHP     48.070406\n",
       "24        PLN      3.732933\n",
       "25        RON      4.008883\n",
       "26        RUB     74.930827\n",
       "27        SEK      8.292071\n",
       "28        SGD      1.328261\n",
       "29        THB     30.009870\n",
       "30        TRY      7.418572\n",
       "31        USD      1.000000\n",
       "32        ZAR     15.118441"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Write your code here\n",
    "rate_data = pd.read_csv('exchange_rates.csv')\n",
    "\n",
    "targetfile = \"bank_market_cap_gbp.csv\"\n",
    "logfile = \"bank_market_cap_logfile.txt\"\n",
    "\n",
    "rate_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Transform\n",
    "\n",
    "Using <code>exchange_rate</code> and the `exchange_rates.csv` file find the exchange rate of USD to GBP. Write a transform function that\n",
    "\n",
    "1. Changes the `Market Cap (US$ Billion)` column from USD to GBP\n",
    "2. Rounds the Market Cap (US$ Billion)` column to 3 decimal places\n",
    "3. Rename `Market Cap (US$ Billion)` to `Market Cap (GBP$ Billion)`\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "tags": []
   },
   "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>Name</th>\n",
       "      <th>Market Cap (GBP$ Billion)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>JPMorgan Chase</td>\n",
       "      <td>286.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Industrial and Commercial Bank of China</td>\n",
       "      <td>252.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bank of America</td>\n",
       "      <td>238.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Wells Fargo</td>\n",
       "      <td>225.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>China Construction Bank</td>\n",
       "      <td>188.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>Ping An Bank</td>\n",
       "      <td>27.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>Standard Chartered</td>\n",
       "      <td>27.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>United Overseas Bank</td>\n",
       "      <td>25.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>QNB Group</td>\n",
       "      <td>24.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>Bank Rakyat</td>\n",
       "      <td>24.23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>70 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       Name  Market Cap (GBP$ Billion)\n",
       "0                            JPMorgan Chase                     286.32\n",
       "1   Industrial and Commercial Bank of China                     252.83\n",
       "2                           Bank of America                     238.27\n",
       "3                               Wells Fargo                     225.59\n",
       "4                   China Construction Bank                     188.52\n",
       "..                                      ...                        ...\n",
       "65                             Ping An Bank                      27.83\n",
       "66                       Standard Chartered                      27.33\n",
       "67                     United Overseas Bank                      25.73\n",
       "68                                QNB Group                      24.58\n",
       "69                              Bank Rakyat                      24.23\n",
       "\n",
       "[70 rows x 2 columns]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rate_data.columns = ['Currency', 'Rates']\n",
    "exchange_rate = rate_data[rate_data['Currency'] == 'GBP']['Rates'].values[0]\n",
    "\n",
    "def transform(extracted_data):\n",
    "    extracted_data['Market Cap (US$ Billion)'] = extracted_data['Market Cap (US$ Billion)'].apply(lambda x: x* exchange_rate)\n",
    "    extracted_data['Market Cap (US$ Billion)'] = extracted_data['Market Cap (US$ Billion)'].round(decimals = 2)\n",
    "    extracted_data.rename(columns={'Market Cap (US$ Billion)':'Market Cap (GBP$ Billion)'},inplace=True)\n",
    "    return extracted_data\n",
    "\n",
    "transform(extracted_data)\n",
    "extracted_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load\n",
    "\n",
    "Create a function that takes a dataframe and load it to a csv named `bank_market_cap_gbp.csv`. Make sure to set `index` to `False`.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "def load(targetfile,data_to_load):\n",
    "    data_to_load.to_csv(targetfile)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Logging Function\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Write the logging function <code>log</code> to log your data:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "def log(message):\n",
    "    timestamp_format = '%H:%M:%S-%h-%d-%Y'#Hour-Minute-Second-MonthName-Day-Year\n",
    "    now = datetime.now() #get current timestamp\n",
    "    timestamp = now.strftime(timestamp_format)\n",
    "    with open(\"bank_market_cap_logfile.txt\",\"a\") as f:\n",
    "        f.write(timestamp + ',' + message + '\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Running the ETL Process\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Log the process accordingly using the following <code>\"ETL Job Started\"</code> and <code>\"Extract phase Started\"</code>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Write your code here\n",
    "log(\"ETL Job Started\")\n",
    "log(\"Extract phase Started\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extract\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<code>Question 2</code> Use the function <code>extract</code>, and print the first 5 rows, take a screen shot:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "tags": []
   },
   "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>Name</th>\n",
       "      <th>Market Cap (US$ Billion)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>JPMorgan Chase</td>\n",
       "      <td>390.934</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Industrial and Commercial Bank of China</td>\n",
       "      <td>345.214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bank of America</td>\n",
       "      <td>325.331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Wells Fargo</td>\n",
       "      <td>308.013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>China Construction Bank</td>\n",
       "      <td>257.399</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      Name  Market Cap (US$ Billion)\n",
       "0                           JPMorgan Chase                   390.934\n",
       "1  Industrial and Commercial Bank of China                   345.214\n",
       "2                          Bank of America                   325.331\n",
       "3                              Wells Fargo                   308.013\n",
       "4                  China Construction Bank                   257.399"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Call the function here\n",
    "# Write your code here\n",
    "extracted_data = extract()\n",
    "# Print the rows here\n",
    "\n",
    "extracted_data.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Log the data as <code>\"Extract phase Ended\"</code>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Write your code here\n",
    "log(\"Extract phase Ended\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transform\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Log the following  <code>\"Transform phase Started\"</code>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Write your code here\n",
    "log(\"Transform phase Started\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<code>Question 3</code> Use the function <code>transform</code> and print the first 5 rows of the output, take a screen shot:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "tags": []
   },
   "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>Name</th>\n",
       "      <th>Market Cap (GBP$ Billion)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>JPMorgan Chase</td>\n",
       "      <td>286.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Industrial and Commercial Bank of China</td>\n",
       "      <td>252.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bank of America</td>\n",
       "      <td>238.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Wells Fargo</td>\n",
       "      <td>225.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>China Construction Bank</td>\n",
       "      <td>188.52</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      Name  Market Cap (GBP$ Billion)\n",
       "0                           JPMorgan Chase                     286.32\n",
       "1  Industrial and Commercial Bank of China                     252.83\n",
       "2                          Bank of America                     238.27\n",
       "3                              Wells Fargo                     225.59\n",
       "4                  China Construction Bank                     188.52"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Call the function here\n",
    "transformed_data = transform(extracted_data)\n",
    "\n",
    "# Print the first 5 rows here\n",
    "transformed_data.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Log your data <code>\"Transform phase Ended\"</code>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Write your code here\n",
    "log(\"Transform phase Ended\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Log the following `\"Load phase Started\"`.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Write your code here\n",
    "log(\"Load phase Started\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Call the load function\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Write your code here\n",
    "load(targetfile,transformed_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Log the following `\"Load phase Ended\"`.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Write your code here\n",
    "log(\"Load phase Ended\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Authors\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ramesh Sannareddy, Joseph Santrcangelo and Azim Hirjani\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Other Contributors\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rav Ahuja\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Change Log\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "| Date (YYYY-MM-DD) | Version | Changed By        | Change Description                 |\n",
    "| ----------------- | ------- | ----------------- | ---------------------------------- |\n",
    "| 2020-11-25        | 0.1     | Ramesh Sannareddy | Created initial version of the lab |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " Copyright © 2020 IBM Corporation. This notebook and its source code are released under the terms of the [MIT License](https://cognitiveclass.ai/mit-license?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDeveloperSkillsNetworkPY0221ENSkillsNetwork899-2023-01-01&cm_mmc=Email_Newsletter-_-Developer_Ed%2BTech-_-WW_WW-_-SkillsNetwork-Courses-IBM-DA0321EN-SkillsNetwork-21426264&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ).\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python",
   "language": "python",
   "name": "conda-env-python-py"
  },
  "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}