Python Data Engineering Project.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "19d1ec47-fc0e-4387-ae44-fdaf7505b4e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "#!mamba install pandas==1.3.3 -y\n",
    "#!mamba install requests==2.26.0 -y"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "fdfa6f52-88f6-4ef7-9f7d-70411ed1ea0e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import glob\n",
    "import pandas as pd\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "9de307eb-27f1-4214-a193-cbbfee27a7f8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--2022-09-17 12:31:50--  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.1’\n",
      "\n",
      "bank_market_cap_1.j 100%[===================>]   2.75K  --.-KB/s    in 0s      \n",
      "\n",
      "2022-09-17 12:31:52 (21.7 MB/s) - ‘bank_market_cap_1.json.1’ saved [2815/2815]\n",
      "\n",
      "--2022-09-17 12:31:52--  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.1’\n",
      "\n",
      "bank_market_cap_2.j 100%[===================>]   1.40K  --.-KB/s    in 0s      \n",
      "\n",
      "2022-09-17 12:31:54 (6.27 MB/s) - ‘bank_market_cap_2.json.1’ saved [1429/1429]\n",
      "\n",
      "--2022-09-17 12:31:55--  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.1’\n",
      "\n",
      "exchange_rates.csv. 100%[===================>]     590  --.-KB/s    in 0s      \n",
      "\n",
      "2022-09-17 12:31:56 (4.15 MB/s) - ‘exchange_rates.csv.1’ 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": "code",
   "execution_count": 69,
   "id": "1fce184d-19bf-4b6c-9baf-7e50eea08b42",
   "metadata": {},
   "outputs": [],
   "source": [
    "def extract_from_json(file_to_process):\n",
    "    dataframe = pd.read_json(file_to_process)\n",
    "    return dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "4262bc91-7dda-4948-b324-7183821d7a67",
   "metadata": {},
   "outputs": [],
   "source": [
    "bmc = extract_from_json('bank_market_cap_1.json')\n",
    "columns = ['Name','Market_Cap (US$ Billion)']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "fa704535-b048-4db1-9f69-26620c92e9dd",
   "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>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": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bmc"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "356e212d-9207-492c-ab15-219d29ca29fd",
   "metadata": {},
   "source": [
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "35edfccb-4104-4a02-bb2e-d0c45d142da1",
   "metadata": {},
   "outputs": [],
   "source": [
    "def extract():\n",
    "   extracted_data = pd.DataFrame(columns= ['Name','Market Cap (US$ Billion)'])\n",
    "\n",
    "   for jsonfile in glob.glob(\"*_1.json\"):\n",
    "        extracted_data = extracted_data.append(extract_from_json(jsonfile), ignore_index=True)\n",
    "        \n",
    "        return extracted_data       \n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b739352d-17cd-4aa5-8e88-1ba9c9e5e7bf",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "942037d9-ac72-4e06-a277-640bb536d5a2",
   "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>Rates</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AUD</th>\n",
       "      <td>1.297088</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BGN</th>\n",
       "      <td>1.608653</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BRL</th>\n",
       "      <td>5.409196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CAD</th>\n",
       "      <td>1.271426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CHF</th>\n",
       "      <td>0.886083</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Rates\n",
       "AUD  1.297088\n",
       "BGN  1.608653\n",
       "BRL  5.409196\n",
       "CAD  1.271426\n",
       "CHF  0.886083"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Loading the exchange_rates.csv as a dataFrame  \n",
    "er = pd.read_csv(\"exchange_rates.csv\", index_col= 0)\n",
    "er = pd.DataFrame(er)\n",
    "\n",
    "# Displaying the first five rows\n",
    "er.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8435c073-aa9c-4974-a22c-013ffb17500b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "1ae6986d-4f44-4da5-b781-fd77e776fb86",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.7323984208000001"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#finding the exchange rate\n",
    "exchange_rate = float(er.loc['GBP'])\n",
    "type(exchange_rate)\n",
    "exchange_rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ffe34029-3900-4e4a-829e-c1b02650d6c1",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "761d2468-9c5c-4d7e-8030-da94ac01b7f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# TRANSFORMATION\n",
    "\n",
    "# Changing the Marketcap FROM USD to GB\n",
    "# Rounding the market cap to 3 decimals\n",
    "#Renaming the marketcap (US$ Billion ) to Marketcap(GBP$ Billion)\n",
    "\n",
    "def transform(extracted_data):\n",
    "    extracted_data['Market Cap (US$ Billion)']= round(extracted_data['Market Cap (US$ Billion)']*exchange_rate,3)\n",
    "    extracted_data.rename(columns ={'Market Cap(US$ Billion)':'Market Cap (GBP$ Billion)'}, inplace = True)\n",
    "    return extracted_data\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aaf91b9d-1406-4751-8459-ce5db58aa125",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "4a72e248-35dc-4c73-972b-72de2fc6217b",
   "metadata": {},
   "outputs": [],
   "source": [
    "                    # LOADING\n",
    "\n",
    "\n",
    "targetfile = \"bank_market_cap_gbp.csv\"  # file where transformed data is stored\n",
    "\n",
    "def load(targetfile,data_to_load):\n",
    "    data_to_load.to_csv(targetfile, index= False)\n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "56d065b2-25f2-42cc-ab58-bd3d54f42f89",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Logging Entries\n",
    "\n",
    "def log(message):\n",
    "    timestamp_format = '%Y-%h-%d-%H:%M:%S' # Year-Monthname-Day-Hour-Minute-Second\n",
    "    now = datetime.now() # get current timestamp\n",
    "    timestamp =now.strftime(timestamp_format)\n",
    "    with open (\"logfile.txt\", \"a\") as f:\n",
    "        f.write(timestamp  + ',' + message + '\\n')\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "872c288f-db40-44b0-8aaa-d0e6a27d98b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "#RUNNING THE ETL PROCESS\n",
    "\n",
    "log(\"ETL Job started\")\n",
    "log(\"Extract phase Started\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "4c5f8332-8fba-4649-b8b0-a493482f28c0",
   "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>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>209.700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Industrial and Commercial Bank of China</td>\n",
       "      <td>185.175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bank of America</td>\n",
       "      <td>174.510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Wells Fargo</td>\n",
       "      <td>165.220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>China Construction Bank</td>\n",
       "      <td>138.071</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>20.380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>Standard Chartered</td>\n",
       "      <td>20.018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>United Overseas Bank</td>\n",
       "      <td>18.843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>QNB Group</td>\n",
       "      <td>18.002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>Bank Rakyat</td>\n",
       "      <td>17.745</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                   209.700\n",
       "1   Industrial and Commercial Bank of China                   185.175\n",
       "2                           Bank of America                   174.510\n",
       "3                               Wells Fargo                   165.220\n",
       "4                   China Construction Bank                   138.071\n",
       "..                                      ...                       ...\n",
       "65                             Ping An Bank                    20.380\n",
       "66                       Standard Chartered                    20.018\n",
       "67                     United Overseas Bank                    18.843\n",
       "68                                QNB Group                    18.002\n",
       "69                              Bank Rakyat                    17.745\n",
       "\n",
       "[70 rows x 2 columns]"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# printing some of the rows\n",
    "transformed_data = transform(extracted_data)\n",
    "log(\"Transform phase Ended\")\n",
    "transformed_data\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "e1cf82e7-8454-4c7d-8baf-05ffff344af7",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Load phase started\n",
    "log(\"Load phase Started\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "72b83f21-a21b-4d10-9f9d-d8c7b845d230",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calling the function\n",
    "load(targetfile,transformed_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "a9b5acd5-6571-43ea-9e6b-05b7bf80deda",
   "metadata": {},
   "outputs": [],
   "source": [
    "log(\"Load phase Started\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7771bfc6-8ce7-44d0-8115-5172291787b0",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}