{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.10.13","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":7917573,"sourceType":"datasetVersion","datasetId":4652389}],"dockerImageVersionId":30673,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false},"colab":{"provenance":[{"file_id":"https://storage.googleapis.com/kaggle-colab-exported-notebooks/banks-project-c28f3b29-1b63-4041-b8af-e16a640f1d6e.ipynb?X-Goog-Algorithm=GOOG4-RSA-SHA256&X-Goog-Credential=gcp-kaggle-com%40kaggle-161607.iam.gserviceaccount.com/20240322/auto/storage/goog4_request&X-Goog-Date=20240322T214037Z&X-Goog-Expires=259200&X-Goog-SignedHeaders=host&X-Goog-Signature=06055d04e2a2359bed72752d7ff7f21fce0459507a30937e51203af2347f0b4c142b5a65d210e4e9bef196d384946488f2d3c3646574804739bbb11fe0c105d9619c94aa1ea35b73c8e2c387742b2c0fb07235316cd9e71ec9511f133d31ffe7c3bfca99c7aaadf0e14622acc6e4c8bb900ab67e0dd7ebcd2d9471e52f258f0d2c44dc01e8cf5908321a5a6ebab1eec18ab2663e2fb403a371d75292daa78c563238c576d831a7c7b6ca355f0ea4c6607899ce9ef3c7695fe34d99ce525a7f42c8daffdb07c7f07b71b0be13ec8ac3a28966cc51ebebcd120b98380aab46ba2959a65cefd30efe36334fb11973c7241a7b500fafc83f8ca47aabf148440cbd4c","timestamp":1711143628485}]}},"nbformat_minor":0,"nbformat":4,"cells":[{"cell_type":"markdown","source":["# Este proyecto de python corresponde a la carrera de Data Engineering ofrecido por IBM\n","EL script se encarga de recolectar informacion sobre la capitalizacion de mercado de los bancos mas grandes del mundo: En billones de USD , EUR , Libras Esterlinas y en Rupias Indias.\n","El mismo crea un script bastante robusto para analisis posterior de los datos obtenidos.\n","El script realiza lo siguiente:\n","1. Recoleccion de datos(haciendo uso de webscrapping)\n","2. Transformacion(toma los datos extraidos por la fase anterior de extraccion y les suma datos contenidos dentro de un archivo csv)  \n","3. Carga los datos transformados a un archivo csv de destino(para el posterior analisis de los datos)\n","4. Carga a una base de datos con columnas previamente definidas\n","5. Consulta a la base de datos 3 consultas sobre los datos ,  incluido un promedo sobre los billones de Libras de los mayores bancos del mundo\n","6. Por ultimo crea un archivo de log el cual se encarga de tomar todos los pasos que esta haciendo el script para tener un registro de los mismos"],"metadata":{"id":"qts437okG4P2"}},{"cell_type":"markdown","source":["# Importacion de las Librerias Necesarias"],"metadata":{"id":"erQDYW5vG4P7"}},{"cell_type":"code","source":["!pip3 install pandas bs4 requests numpy\n","!wget https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMSkillsNetwork-PY0221EN-Coursera/labs/v2/exchange_rate.csv"],"metadata":{"execution":{"iopub.status.busy":"2024-03-22T21:15:31.433608Z","iopub.execute_input":"2024-03-22T21:15:31.433984Z","iopub.status.idle":"2024-03-22T21:15:48.227131Z","shell.execute_reply.started":"2024-03-22T21:15:31.433955Z","shell.execute_reply":"2024-03-22T21:15:48.226028Z"},"trusted":true,"id":"UOH53uvNG4P8","colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"status":"ok","timestamp":1711143721445,"user_tz":180,"elapsed":7261,"user":{"displayName":"Horacio Laphitz","userId":"05970776226255252387"}},"outputId":"5fc78249-7bc7-4eb2-8621-5cd32c975fe9"},"execution_count":2,"outputs":[{"output_type":"stream","name":"stdout","text":["Requirement already satisfied: pandas in /usr/local/lib/python3.10/dist-packages (1.5.3)\n","Requirement already satisfied: bs4 in /usr/local/lib/python3.10/dist-packages (0.0.2)\n","Requirement already satisfied: requests in /usr/local/lib/python3.10/dist-packages (2.31.0)\n","Requirement already satisfied: numpy in /usr/local/lib/python3.10/dist-packages (1.25.2)\n","Requirement already satisfied: python-dateutil>=2.8.1 in /usr/local/lib/python3.10/dist-packages (from pandas) (2.8.2)\n","Requirement already satisfied: pytz>=2020.1 in /usr/local/lib/python3.10/dist-packages (from pandas) (2023.4)\n","Requirement already satisfied: beautifulsoup4 in /usr/local/lib/python3.10/dist-packages (from bs4) (4.12.3)\n","Requirement already satisfied: charset-normalizer<4,>=2 in /usr/local/lib/python3.10/dist-packages (from requests) (3.3.2)\n","Requirement already satisfied: idna<4,>=2.5 in /usr/local/lib/python3.10/dist-packages (from requests) (3.6)\n","Requirement already satisfied: urllib3<3,>=1.21.1 in /usr/local/lib/python3.10/dist-packages (from requests) (2.0.7)\n","Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.10/dist-packages (from requests) (2024.2.2)\n","Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.10/dist-packages (from python-dateutil>=2.8.1->pandas) (1.16.0)\n","Requirement already satisfied: soupsieve>1.2 in /usr/local/lib/python3.10/dist-packages (from beautifulsoup4->bs4) (2.5)\n","--2024-03-22 21:42:24--  https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMSkillsNetwork-PY0221EN-Coursera/labs/v2/exchange_rate.csv\n","Resolving cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud (cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud)... 169.45.118.108\n","Connecting to cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud (cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud)|169.45.118.108|:443... connected.\n","HTTP request sent, awaiting response... 200 OK\n","Length: 45 [text/csv]\n","Saving to: ‘exchange_rate.csv.1’\n","\n","exchange_rate.csv.1 100%[===================>]      45  --.-KB/s    in 0s      \n","\n","2024-03-22 21:42:24 (26.2 MB/s) - ‘exchange_rate.csv.1’ saved [45/45]\n","\n"]}]},{"cell_type":"code","source":["from bs4 import BeautifulSoup\n","import requests as r\n","import pandas as pd\n","import numpy as np\n","import sqlite3 as db3\n","from datetime import datetime\n"],"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2024-03-22T21:16:01.803587Z","iopub.execute_input":"2024-03-22T21:16:01.803996Z","iopub.status.idle":"2024-03-22T21:16:01.811356Z","shell.execute_reply.started":"2024-03-22T21:16:01.803964Z","shell.execute_reply":"2024-03-22T21:16:01.809934Z"},"trusted":true,"id":"vNmfkdWxG4P9","executionInfo":{"status":"ok","timestamp":1711143722281,"user_tz":180,"elapsed":853,"user":{"displayName":"Horacio Laphitz","userId":"05970776226255252387"}}},"execution_count":3,"outputs":[]},{"cell_type":"markdown","source":["# Variables con los Datos Necesarios para la Extraccion\n"],"metadata":{"id":"XWv6kMm3G4P-"}},{"cell_type":"code","source":["url = \"https://web.archive.org/web/20230908091635/https://en.wikipedia.org/wiki/List_of_largest_banks\"\n","atributos_tablas_extraccion = [\"Nombre\", \"MC_USD_Billions\"]\n","atributos_tablas_final = [\"Nombre\", \"MC_USD_Billion\", \"MC_GBP_Billion\", \"MC_EUR_Billion\", \"MC_INR_Billion\"]\n","nombre_db = 'Banks.db'\n","nombre_tabla = 'Largest_banks'\n","ruta_csv = './Largest_banks_data.csv'\n","log=\"code_log.txt\"\n","csv_adicional=\"./exchange_rate.csv\""],"metadata":{"execution":{"iopub.status.busy":"2024-03-22T21:16:33.762516Z","iopub.execute_input":"2024-03-22T21:16:33.762967Z","iopub.status.idle":"2024-03-22T21:16:33.770981Z","shell.execute_reply.started":"2024-03-22T21:16:33.762939Z","shell.execute_reply":"2024-03-22T21:16:33.769783Z"},"trusted":true,"id":"AzZ5vJzoG4P-","executionInfo":{"status":"ok","timestamp":1711143722282,"user_tz":180,"elapsed":5,"user":{"displayName":"Horacio Laphitz","userId":"05970776226255252387"}}},"execution_count":4,"outputs":[]},{"cell_type":"markdown","source":["# Creando la variable de Conexion con la Base de Datos"],"metadata":{"id":"W8gjryeSG4P_"}},{"cell_type":"code","source":["conexion = db3.connect(\"Banks.db\")"],"metadata":{"execution":{"iopub.status.busy":"2024-03-22T21:25:52.383313Z","iopub.execute_input":"2024-03-22T21:25:52.384379Z","iopub.status.idle":"2024-03-22T21:25:52.388521Z","shell.execute_reply.started":"2024-03-22T21:25:52.384344Z","shell.execute_reply":"2024-03-22T21:25:52.387606Z"},"trusted":true,"id":"R7UgQ8jMG4QA","executionInfo":{"status":"ok","timestamp":1711143740148,"user_tz":180,"elapsed":310,"user":{"displayName":"Horacio Laphitz","userId":"05970776226255252387"}}},"execution_count":13,"outputs":[]},{"cell_type":"markdown","source":["# Definiendo la Funcion Log\n","Se va a encargar de registrar todos los pasos que realiza el script dentro de un archivo de texto"],"metadata":{"id":"N7N9Vhd5G4QA"}},{"cell_type":"code","source":["def log_progress(mensaje):\n","    ''' Esta función registra el mensaje mencionado en una etapa determinada de la ejecución del código,\n","     en un archivo de registro'''\n","    ''' Aca se definen las entidades requeridas y se llama a las correspondientes.\n","    funciones en el orden correcto para completar el proyecto. .'''\n","    timestamp_format = '%Y-%h-%d-%H:%M:%S' # Año-Mes-Dia-Hora-Minutos-Segundos\n","    now = datetime.now() # fecha-hora actual\n","    timestamp = now.strftime(timestamp_format)\n","    with open(\"./etl_project_log.txt\",\"a\") as f:\n","        f.write(timestamp + ' : ' + mensaje + '\\n')"],"metadata":{"execution":{"iopub.status.busy":"2024-03-22T21:17:40.482082Z","iopub.execute_input":"2024-03-22T21:17:40.482943Z","iopub.status.idle":"2024-03-22T21:17:40.490889Z","shell.execute_reply.started":"2024-03-22T21:17:40.482907Z","shell.execute_reply":"2024-03-22T21:17:40.489604Z"},"trusted":true,"id":"MMf7FdNiG4QA","executionInfo":{"status":"ok","timestamp":1711143722764,"user_tz":180,"elapsed":486,"user":{"displayName":"Horacio Laphitz","userId":"05970776226255252387"}}},"execution_count":6,"outputs":[]},{"cell_type":"markdown","source":["# Definiendo la Funcion de Extraccion\n","La misma va a recopilar los datos de la url solicitada, extrayendo los datos de una tabla en especifico\n"],"metadata":{"id":"ZHTFPBUXG4QB"}},{"cell_type":"code","source":["def extraer(url, atributos_tablas_extraccion):\n","    ''' Esta función extrae el requerido\n","    información del sitio web y la guarda en un marco de datos.\n","    La función devuelve el marco de datos para su posterior procesamiento.'''\n","    pagina= r.get(url).text # Extraer la pagina web como texto\n","    datos = BeautifulSoup(pagina, \"html.parser\") #Pasar archivo HTML a objeto\n","    df= pd.DataFrame(columns=atributos_tablas_extraccion) #Cree un DataFrame de pandas vacío llamado df con columnas de la variable table_attribs\n","    tablas = datos.find_all('tbody')# Se Extrae todos los atributos 'tbody' del objeto HTML\n","    filas = tablas[0].find_all('tr') #Luego se extrae todas las filas de la tabla usando el atributo 'tr'.\n","    for fila in filas:\n","        col = fila.find_all('td')\n","        if len(col)!=0 :\n","            bank_name = col[1].find_all('a')[1]['title']\n","            total_assets = col[2].contents[0].replace(\"\\n\" , \"\")\n","            data_dict = {\n","                         \"Nombre\": bank_name,\n","                         \"MC_USD_Billions\": total_assets}\n","            df1 = pd.DataFrame(data_dict, index=[0])\n","            df = pd.concat([df,df1], ignore_index=True)\n","\n","\n","    return df"],"metadata":{"execution":{"iopub.status.busy":"2024-03-22T21:21:59.814572Z","iopub.execute_input":"2024-03-22T21:21:59.815583Z","iopub.status.idle":"2024-03-22T21:21:59.826676Z","shell.execute_reply.started":"2024-03-22T21:21:59.815544Z","shell.execute_reply":"2024-03-22T21:21:59.825415Z"},"trusted":true,"id":"AibBvL9IG4QB","executionInfo":{"status":"ok","timestamp":1711143722764,"user_tz":180,"elapsed":3,"user":{"displayName":"Horacio Laphitz","userId":"05970776226255252387"}}},"execution_count":7,"outputs":[]},{"cell_type":"markdown","source":["# Definiendo la Funcion de Transformacion de los Datos\n","La misma se va a encargar de tomar los datos ya colocados dentro de un DataFrame de Pandas con la tabla extraida y suma 3 columnas nuevas: \"MC_USD_Billion\", \"MC_GBP_Billion\", \"MC_EUR_Billion\", \"MC_INR_Billion\""],"metadata":{"id":"-ABezX1NG4QB"}},{"cell_type":"code","source":["def transformar(df):\n","    '''Esta función convierte la información del MarkCap de Moneda\n","    formato a valor flotante, transforma la información del MarkCap de\n","    USD (Billones) a USD (Millones) redondeando a 2 decimales.\n","    La función devuelve el marco de datos transformado'''\n","    df['MC_USD_Billions'] = df['MC_USD_Billions'].str.replace(',','').astype(float)\n","\n","    exchange_rate = pd.read_csv('exchange_rate.csv')\n","    exchange_rate['Rate'] = exchange_rate['Rate'].astype(float)\n","    # Crea las nuevas columnas en el DataFrame\n","    df['MC_GBP_Billions'] = np.round(df['MC_USD_Billions'] * exchange_rate.loc[exchange_rate['Currency'] == 'GBP', 'Rate'].values[0], 2)\n","    df['MC_EUR_Billions'] = np.round(df['MC_USD_Billions'] * exchange_rate.loc[exchange_rate['Currency'] == 'EUR', 'Rate'].values[0], 2)\n","    df['MC_INR_Billions'] = np.round(df['MC_USD_Billions'] * exchange_rate.loc[exchange_rate['Currency'] == 'INR', 'Rate'].values[0], 2)\n","\n","    return df"],"metadata":{"execution":{"iopub.status.busy":"2024-03-22T21:22:04.113773Z","iopub.execute_input":"2024-03-22T21:22:04.114528Z","iopub.status.idle":"2024-03-22T21:22:04.124689Z","shell.execute_reply.started":"2024-03-22T21:22:04.114488Z","shell.execute_reply":"2024-03-22T21:22:04.123113Z"},"trusted":true,"id":"GN8ITDdDG4QC","executionInfo":{"status":"ok","timestamp":1711143722764,"user_tz":180,"elapsed":2,"user":{"displayName":"Horacio Laphitz","userId":"05970776226255252387"}}},"execution_count":8,"outputs":[]},{"cell_type":"markdown","source":["# Definicion de la Funcion de carga a csv"],"metadata":{"id":"K0g0MMfKG4QC"}},{"cell_type":"code","source":["def cargar_a_csv(df, ruta_csv):\n","    '''Esta función guarda el marco de datos final como un archivo `CSV`\n","    en la ruta proporcionada.'''\n","\n","    df.to_csv(ruta_csv) #Utilicé el objeto de función 'to_csv()' para el marco de datos de pandas.\n","    # fase de combinacion de csvs"],"metadata":{"execution":{"iopub.status.busy":"2024-03-22T21:22:06.713996Z","iopub.execute_input":"2024-03-22T21:22:06.71445Z","iopub.status.idle":"2024-03-22T21:22:06.720906Z","shell.execute_reply.started":"2024-03-22T21:22:06.714415Z","shell.execute_reply":"2024-03-22T21:22:06.719456Z"},"trusted":true,"id":"w2uFHYhwG4QC","executionInfo":{"status":"ok","timestamp":1711143724796,"user_tz":180,"elapsed":2034,"user":{"displayName":"Horacio Laphitz","userId":"05970776226255252387"}}},"execution_count":9,"outputs":[]},{"cell_type":"markdown","source":["# Definicion de la Funcion de carga a la Base de Datos"],"metadata":{"id":"Kf4RqXgHG4QC"}},{"cell_type":"code","source":["def cargar_a_db(df, conexion, nombre_tabla):\n","    ''' Esta función guarda el marco de datos final como una tabla de base de datos.\n","    con el nombre proporcionado.'''\n","    df.to_sql(nombre_tabla , conexion ,if_exists=\"replace\" , index=False)\n","    #Utilicé el objeto de función 'to_sql()' para el marco de datos de pandas."],"metadata":{"execution":{"iopub.status.busy":"2024-03-22T21:23:22.558525Z","iopub.execute_input":"2024-03-22T21:23:22.5589Z","iopub.status.idle":"2024-03-22T21:23:22.567724Z","shell.execute_reply.started":"2024-03-22T21:23:22.558873Z","shell.execute_reply":"2024-03-22T21:23:22.566795Z"},"trusted":true,"id":"aDZP9MNGG4QC","executionInfo":{"status":"ok","timestamp":1711143724796,"user_tz":180,"elapsed":4,"user":{"displayName":"Horacio Laphitz","userId":"05970776226255252387"}}},"execution_count":10,"outputs":[]},{"cell_type":"markdown","source":["# Definicion de la Funcion de Consultas a la Base de Datos"],"metadata":{"id":"ZmnUDEUmG4QC"}},{"cell_type":"code","source":["def consultar(declaraciones_consulta, conexion):\n","    '''Esta función ejecuta la consulta indicada en la tabla de la base de datos y\n","    imprime la salida en el terminal.'''\n","\n","    for declaracion_consulta in declaraciones_consulta:\n","        print(declaracion_consulta)\n","        salida_consulta = pd.read_sql(declaracion_consulta, conexion)\n","        print(salida_consulta)"],"metadata":{"execution":{"iopub.status.busy":"2024-03-22T21:23:24.357757Z","iopub.execute_input":"2024-03-22T21:23:24.358402Z","iopub.status.idle":"2024-03-22T21:23:24.363091Z","shell.execute_reply.started":"2024-03-22T21:23:24.358372Z","shell.execute_reply":"2024-03-22T21:23:24.36229Z"},"trusted":true,"id":"5b5bT64VG4QD","executionInfo":{"status":"ok","timestamp":1711143724796,"user_tz":180,"elapsed":3,"user":{"displayName":"Horacio Laphitz","userId":"05970776226255252387"}}},"execution_count":11,"outputs":[]},{"cell_type":"markdown","source":["# Fase de creacion del archivo de Log con todos los pasos del Script\n","Contiene ademas, el cierre de la conexion a la base de datos"],"metadata":{"id":"Eg14z6bWG4QD"}},{"cell_type":"code","source":["log_progress('Fase Preliminar Completa. Iniciando el proceso de Extraccion Transformacion y Carga')\n","df = extraer(url, atributos_tablas_extraccion)\n","log_progress('Extraccion de Datos Completa. Iniciando el Proceso de Transformacion')\n","df = transformar(df)\n","log_progress('Transformacion de los Datos Completa. Iniciando el Proceso de Carga')\n","cargar_a_csv(df, ruta_csv)\n","log_progress('Datos Guardados en archivo CSV')\n","conexion_sql = db3.connect('Banks.db')\n","log_progress('Conexion a la Base de Datos SQL comenzando .')\n","cargar_a_db(df, conexion, nombre_tabla)\n","log_progress('Datos cargados en la base de datos como tabla. Ejecutando la consulta')\n","consultas= [\n","    \"SELECT * FROM Largest_banks \",\n","    \"SELECT AVG(MC_GBP_Billions) FROM Largest_banks\",\n","    \"SELECT Nombre from Largest_banks LIMIT 5\"\n","]\n","consultar(consultas, conexion)\n","log_progress('Proceso ETL Completo!.')\n","conexion.close()"],"metadata":{"execution":{"iopub.status.busy":"2024-03-22T21:25:59.143018Z","iopub.execute_input":"2024-03-22T21:25:59.143755Z","iopub.status.idle":"2024-03-22T21:25:59.956372Z","shell.execute_reply.started":"2024-03-22T21:25:59.143718Z","shell.execute_reply":"2024-03-22T21:25:59.955061Z"},"trusted":true,"id":"qpbzVMpUG4QD","colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"status":"ok","timestamp":1711143744518,"user_tz":180,"elapsed":17,"user":{"displayName":"Horacio Laphitz","userId":"05970776226255252387"}},"outputId":"c0db45d9-7ad3-4b80-9c5c-f3de60fb9581"},"execution_count":14,"outputs":[{"output_type":"stream","name":"stdout","text":["SELECT * FROM Largest_banks \n","                                    Nombre  MC_USD_Billions  MC_GBP_Billions  \\\n","0                           JPMorgan Chase           432.92           346.34   \n","1                          Bank of America           231.52           185.22   \n","2  Industrial and Commercial Bank of China           194.56           155.65   \n","3               Agricultural Bank of China           160.68           128.54   \n","4                                HDFC Bank           157.91           126.33   \n","5                              Wells Fargo           155.87           124.70   \n","6                                     HSBC           148.90           119.12   \n","7                           Morgan Stanley           140.83           112.66   \n","8                  China Construction Bank           139.82           111.86   \n","9                            Bank of China           136.81           109.45   \n","\n","   MC_EUR_Billions  MC_INR_Billions  \n","0           402.62         35910.71  \n","1           215.31         19204.58  \n","2           180.94         16138.75  \n","3           149.43         13328.41  \n","4           146.86         13098.63  \n","5           144.96         12929.42  \n","6           138.48         12351.26  \n","7           130.97         11681.85  \n","8           130.03         11598.07  \n","9           127.23         11348.39  \n","SELECT AVG(MC_GBP_Billions) FROM Largest_banks\n","   AVG(MC_GBP_Billions)\n","0               151.987\n","SELECT Nombre from Largest_banks LIMIT 5\n","                                    Nombre\n","0                           JPMorgan Chase\n","1                          Bank of America\n","2  Industrial and Commercial Bank of China\n","3               Agricultural Bank of China\n","4                                HDFC Bank\n"]}]}]}