banks_project.py

# Install Library
# code name: banks_project.py


# python -m pip install pandas
# python -m pip install numpy
# python -m pip install bs4

# python.exe -m pip install --upgrade pip
# python -m pip install requests beautifulsoup4 pandas numpy
# wget https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMSkillsNetwork-PY0221EN-Coursera/labs/v2/exchange_rate.csv
# curl -O https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMSkillsNetwork-PY0221EN-Coursera/labs/v2/exchange_rate.csv

# python3 banks_project.py



# Code for ETL operations on Largest Banks data
from bs4 import BeautifulSoup
import requests
import pandas as pd
import numpy as np
import sqlite3
from datetime import datetime

# Initialize all known variables
url = 'https://web.archive.org/web/20230908091635/https://en.wikipedia.org/wiki/List_of_largest_banks'
exchange_rate_csv = 'https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMSkillsNetwork-PY0221EN-Coursera/labs/v2/exchange_rate.csv'
table_attribs = ["Name", "MC_USD_Billion"]
db_name = 'Banks.db'
table_name = 'Largest_banks'
csv_path = './Largest_banks_data.csv'
log_file = 'code_log.txt'

def log_progress(message):
    ''' This function logs the mentioned message of a given stage of the
    code execution to a log file. Function returns nothing'''
    timestamp_format = '%Y-%h-%d-%H:%M:%S'
    now = datetime.now()
    timestamp = now.strftime(timestamp_format)
    with open(log_file, "a") as f:
        f.write(f"{timestamp} : {message}\n")

def extract(url, table_attribs):
    ''' This function aims to extract the required
    information from the website and save it to a data frame. '''
    page = requests.get(url).text
    data = BeautifulSoup(page, 'html.parser')
    df = pd.DataFrame(columns=table_attribs)
    
    # Locate the tables and identify the one under 'By market capitalization'
    tables = data.find_all('table', {"class": "wikitable"})
    # On this specific archive, the 'Market Cap' table is the first wikitable
    rows = tables[0].find_all('tr')

    for row in rows:
        col = row.find_all('td')
        if len(col) != 0:
            # Task 2 requirement: Extract Name and Market Cap, 
            # remove '\n' and typecast to float
            bank_name = col[1].text.strip()
            market_cap = float(col[2].text.strip())
            
            data_dict = {"Name": bank_name,
                         "MC_USD_Billion": market_cap}
            df1 = pd.DataFrame(data_dict, index=[0])
            df = pd.concat([df, df1], ignore_index=True)
            
    return df

def transform(df, csv_path):
    ''' This function accesses the CSV file for exchange rate
    information, and adds three columns to the data frame '''
    # Read exchange rate CSV
    exchange_df = pd.read_csv(csv_path)
    
    # Convert to dictionary: {Currency: Rate}
    exchange_rate = exchange_df.set_index('Currency').to_dict()['Rate']
    
    # Add columns scaled by exchange rate and rounded to 2 decimal places
    df['MC_GBP_Billion'] = [np.round(x * float(exchange_rate['GBP']), 2) for x in df['MC_USD_Billion']]
    df['MC_EUR_Billion'] = [np.round(x * float(exchange_rate['EUR']), 2) for x in df['MC_USD_Billion']]
    df['MC_INR_Billion'] = [np.round(x * float(exchange_rate['INR']), 2) for x in df['MC_USD_Billion']]
    
    return df

def load_to_csv(df, output_path):
    ''' This function saves the final data frame as a CSV file '''
    df.to_csv(output_path, index=False)

def load_to_db(df, sql_connection, table_name):
    ''' This function saves the final data frame to a database table '''
    df.to_sql(table_name, sql_connection, if_exists='replace', index=False)

def run_query(query_statement, sql_connection):
    ''' This function runs the query on the database table and prints output '''
    print(f"Query: {query_statement}")
    query_output = pd.read_sql(query_statement, sql_connection)
    print(query_output)
    print("-" * 30)

# --- Execution Flow ---

# Task 1: Preliminaries
log_progress('Preliminaries complete. Initiating ETL process')

# Task 2: Extraction
df = extract(url, table_attribs)
print(df) # Verification for Task 2
log_progress('Data extraction complete. Initiating Transformation process')

# Task 3: Transformation
df = transform(df, exchange_rate_csv)
# Quiz Hint: Market capitalization of the 5th largest bank in EUR
print(f"MC_EUR_Billion for 5th bank: {df['MC_EUR_Billion'][4]}") 
log_progress('Data transformation complete. Initiating Loading process')

# Task 4: Load to CSV
load_to_csv(df, csv_path)
log_progress('Data saved to CSV file')

# Task 5: Load to Database
sql_connection = sqlite3.connect(db_name)
log_progress('SQL Connection initiated')

load_to_db(df, sql_connection, table_name)
log_progress('Data loaded to Database as a table, Executing queries')

# Task 6: Run Queries
run_query("SELECT * FROM Largest_banks", sql_connection)
run_query("SELECT AVG(MC_GBP_Billion) FROM Largest_banks", sql_connection)
run_query("SELECT Name from Largest_banks LIMIT 5", sql_connection)

log_progress('Process Complete')

# Close Connection
sql_connection.close()
log_progress('Server Connection closed')