# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # Segunda Clase
# ## Primera parte
# En esta primera parte se miró como se pueden manipular, cargar y descargar información en archivos **_csv_** desde iPython con magic
# * El comando `%%writefile nombre_del_archivo.extension` guarda en el archivo que se le indica en `nombre_del_archivo.extension` el contenido completo de la celda
#
# Al ser un comando de Python, a menos que se le indique la ruta completa de donde se quiere que se guarde el archivo, este será almacenado en el **working directory** que se esté usando

# %%writefile "C:\Users\HP\01. Projectos de jupyter\01. Ciencia de los datos\bancos.csv" 
Ally Financial,3608-2596-5394-1054,216-82-1048
BB&T,3608-1721-4951-1198,116-81-1883
BBVA Compass,3608-1395-5632-1976,224-25-1891
BBVA Compass,3608-1721-4005-1322,116-51-1291
BBVA Compass,3608-2181-5724-1476,144-25-1448
BBVA Compass,3608-2596-5634-1497,224-99-1262
BMO Harris Bank,3608-1721-4236-1828,279-81-1912
BNP Paribas,3608-2181-5030-1465,216-51-1025
Fifth Third Bank,3608-1782-5015-1001,429-83-1156
Citizens Financial Group,3608-2181-4711-1693,177-44-1054
Comerica,3608-1333-4580-1185,216-85-1367
Comerica,3608-2596-5551-1572,116-93-1394
Deutsche Bank,3608-1782-5551-1837,339-74-1545
Discover Financial,3608-1395-4951-1668,116-51-1291
Fifth Third Bank,3608-1782-4458-1383,166-82-1605
First Republic Bank,3608-1682-4160-1476,425-82-1851
First Republic Bank,3608-2596-5696-1134,429-83-1156
JPMorgan Chase,3608-1782-5890-1999,287-74-1145
JPMorgan Chase,3608-2067-5766-1056,177-23-1359
JPMorgan Chase,3608-2181-5988-1718,116-54-1259
JPMorgan Chase,3608-2751-4236-1394,320-54-1856
MUFG Union Bank,3608-2800-5459-1497,144-54-1840
New York Community Bancorp,3608-2968-5745-1804,323-51-1535
Popular. Inc.,3608-1333-4394-1935,177-44-1159
Popular. Inc.,3608-1721-5632-1589,224-55-1496
Popular. Inc.,3608-2800-5551-1351,279-81-1912
Santander Bank,3608-1395-5691-1428,301-25-1394
Signature Bank,3608-2588-5394-1381,216-82-1048
Signature Bank,3608-2751-5015-1278,224-55-1496
SunTrust Banks,3608-1682-5152-1053,339-74-1545
MUFG Union Bank,3608-1782-4038-1052,238-81-1227
U.S. Bancorp,3608-1192-5884-1614,391-55-1442
U.S. Bancorp,3608-1333-4005-1623,177-44-1054
U.S. Bancorp,3608-2067-5394-1306,216-85-1367
U.S. Bancorp,3608-2181-4288-1394,381-54-1605
USAA,3608-1782-5791-1558,368-83-1054
Wells Fargo,3608-1782-5030-1572,339-82-1442
Wells Fargo,3608-2588-5988-1551,144-25-1448

# %%writefile "C:\Users\HP\01. Projectos de jupyter\01. Ciencia de los datos\personas.csv"
216-51-1025,(09)-5580-7527,Memphis (Tennessee),Single,<NAME>,1988-06-30
116-51-1291,(07)-2905-7818,Buffalo (New York),Married,<NAME>,1974-11-27
177-44-1159,(09)-5062-6922,Detroit (Michigan),Single,<NAME>,1969-07-27
116-81-1883,(03)-1350-7402,Chandler (Arizona),Divorced,<NAME>,1989-11-26
429-83-1156,(09)-5794-9470,Scottsdale (Arizona),Married,<NAME>,1978-05-05
381-54-1605,(05)-5330-5036,Albuquerque (New Mexico),Single,<NAME>,1982-05-02
224-99-1262,(05)-3339-3262,Milwaukee (Wisconsin),Married,<NAME>,1988-01-02
301-25-1394,(07)-4370-8507,Houston (Texas),Single,<NAME>,1972-11-12
323-51-1535,(03)-5179-6500,Las Vegas (Nevada),Married,<NAME>,1978-01-22
216-85-1367,(07)-2905-9114,<NAME> (Minnesota),Divorced,<NAME>,1973-12-16
166-82-1605,(09)-6473-4208,Irvine (California),Married,<NAME>,1975-03-27
116-54-1259,(04)-3468-6535,San Bernardino (California),Divorced,<NAME>,1980-02-01
224-55-1496,(03)-8685-6502,Aurora (Colorado),Common-Law,Tamesha Lawlor,1983-10-02
177-44-1054,(08)-5902-5867,El Paso (Texas),Single,Millie Lasher,1976-03-29
320-54-1856,(04)-3858-1079,Houston (Texas),Divorced,<NAME>,1983-09-07
144-25-1448,(09)-5179-2725,Durham (North Carolina),Divorced,<NAME>,1985-07-07
144-54-1840,(03)-7508-9910,Orlando (Florida),Single,<NAME>,1982-06-08
224-25-1891,(05)-9333-5713,Las Vegas (Nevada),Divorced,<NAME>,1971-12-19
216-82-1048,(04)-1199-9661,Las Vegas (Nevada),Married,<NAME>,1972-05-07
116-93-1394,(05)-9333-4606,Tampa (Florida),Married,<NAME>,1973-08-12
279-81-1912,(08)-2905-8942,Winston–Salem (North Carolina),Single,<NAME>,1977-10-07
425-82-1851,(03)-5794-3345,Aurora (Colorado),Married,<NAME>,1972-02-26
339-74-1545,(08)-4858-6766,Atlanta (Georgia),Married,<NAME>,1975-07-25
368-83-1054,(05)-7508-4870,Omaha (Nebraska),Married,<NAME>,1975-10-08
339-82-1442,(09)-5854-7191,Henderson (Nevada),Single,Sondra Pike,1980-06-25
391-55-1442,(05)-6865-1079,Baton Rouge (Louisiana),Divorced,<NAME>,1984-04-21
177-23-1359,(07)-5854-6781,St. Louis (Missouri),Single,Gigi Ragland,1977-01-27
238-81-1227,(03)-9999-9910,Laredo (Texas),Common-Law,Ronald Signorelli,1977-06-13
287-74-1145,(03)-5794-9130,Fremont (California),Single,<NAME>,1976-02-21

# %%writefile "C:\Users\HP\01. Projectos de jupyter\01. Ciencia de los datos\franquicias.csv"
Capital One,3608-2181-5030-1465,2023-05-27,4538,337,1400
USAA,3608-1395-4951-1668,2024-03-11,5101,240,1900
U.S. Bank,3608-1333-4394-1935,2020-03-08,4814,231,2000
Capital One,3608-1721-4951-1198,2019-05-30,3925,366,1600
PNC,3608-1782-5015-1001,2025-06-08,4241,048,1200
Capital One,3608-2181-4288-1394,2021-12-08,4253,556,1700
Wells Fargo,3608-2596-5634-1497,2018-07-15,5205,140,1200
USAA,3608-1395-5691-1428,2023-04-20,2111,512,1400
American Express,3608-2968-5745-1804,2025-12-01,5065,993,1000
American Express,3608-1333-4580-1185,2025-01-13,2377,277,1300
Discover,3608-1782-4458-1383,2018-07-19,4623,863,1500
Chase,3608-2181-5988-1718,2024-03-23,2987,452,1000
USAA,3608-2751-5015-1278,2024-04-25,2744,831,1700
U.S. Bank,3608-1333-4005-1623,2022-11-10,2117,373,1400
MasterCard,3608-2751-4236-1394,2022-07-22,7943,109,1200
MasterCard,3608-2588-5988-1551,2021-11-11,2172,945,1900
USAA,3608-2800-5459-1497,2024-04-02,7568,458,1400
Discover,3608-1395-5632-1976,2022-12-22,5884,272,1200
BarclayCard US,3608-2588-5394-1381,2020-12-24,5280,237,1200
American Express,3608-2596-5551-1572,2024-07-28,4107,438,1500
BarclayCard US,3608-2800-5551-1351,2020-09-30,4174,318,1700
Discover,3608-1682-4160-1476,2021-05-08,2135,864,2000
U.S. Bank,3608-1682-5152-1053,2024-04-27,7022,246,1100
U.S. Bank,3608-1782-5791-1558,2023-09-24,7502,188,2000
PNC,3608-1782-5030-1572,2024-07-31,6887,951,1700
MasterCard,3608-1192-5884-1614,2018-06-20,5594,800,2000
Bank of America,3608-2067-5766-1056,2025-09-30,2338,355,1200
BarclayCard US,3608-1782-4038-1052,2022-06-25,2130,117,1500
American Express,3608-1782-5890-1999,2021-11-10,3195,732,1600
Visa,3608-1782-5551-1837,2024-11-05,5357,255,2000
MasterCard,3608-1721-4236-1828,2018-05-15,3700,561,1800
U.S. Bank,3608-2596-5394-1054,2022-06-05,6787,233,1000
Discover,3608-2181-5724-1476,2022-09-18,3027,475,1000
MasterCard,3608-2181-4711-1693,2024-03-26,2739,733,1400
Bank of America,3608-1721-5632-1589,2025-02-08,6587,337,1500
Chase,3608-2067-5394-1306,2019-02-13,2544,222,1200
U.S. Bank,3608-2596-5696-1134,2024-05-22,7442,587,1900
USAA,3608-1721-4005-1322,2018-08-16,7241,201,1300

# Luego de esto leeremos el contenido de estos archivos a la base de datos de MySQL
#
# Primero hacemos la conexion a MySQL
#
# #### Importante
# Para poder hacer la conexion con MySQL para que reconozca el comando para leer archivos CSV por fuera del directorio de la base de datos se debe agregar a la conexión la opcion para que lo permita `/?local_infile=1`
# * `/` es para reconocer dentro de la conexión que no vamos a ingresar a una base de datos determinada sino al cliente de MySQL
# * `?` es para poder agregar dentro de la linea de conexión los parámetros que se necesiten
# * `local_infile=1` es el parametro que se quiere enviar para decir que permita la lectura de archivos CSV por fuera del directorio de la base de datos

# +
# %load_ext sql
# %sql mysql+pymysql://root:qwe123@localhost/?local_infile=1

# %sql create database if not exists sqldemo;
# %sql use sqldemo;
# -

# Luego se deben crear las tablas vacías donde se quiere cargar la informacion que se reqiera

# + language="sql"
#
# drop table if exists personas;
# drop table if exists franquicias;
# drop table if exists bancos;
#
# CREATE TABLE personas (
#     id            INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
#     ssn           VARCHAR(11),
#     phone         VARCHAR(14),
#     city          VARCHAR(40),
#     maritalstatus VARCHAR(10),
#     fullname      VARCHAR(40),
#     birthdate     DATE
# );
#
# CREATE TABLE franquicias (
#     id        INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
#     ccntype   VARCHAR(40),
#     ccn       VARCHAR(20),
#     validthru DATE,
#     userkey   VARCHAR(6),
#     userpin   VARCHAR(4),
#     quota     SMALLINT
# );
#
# CREATE TABLE bancos (
#     id   INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
#     bank VARCHAR(40),
#     ccn  VARCHAR(20),
#     ssn  VARCHAR(14)
# );
# -

# Posteriormente se ingresa la información en las tablas con el comando `load data infile`, sin embargo este solo sirve si la información se encuntra dentro de la misma base de datos, por lo que se tendría que cargar los archivos primero en la ruta en la cual se encuentra esta.
#
# ***Sin embargo***, gracias al comando *local* usado de la siguiente manera `load data local infile` podremos especificar la ruta completa del archivo que se encuentra en cualquier parte del computador y podrá ser cargada sin inconvenientes
#
# ***Importante***: Por seguridad el comando `load data local infile` está desabilidato por seguridad, por lo que antes de ejecutarlo se recomienda hacer esta verificación.
#
# #### ¡¡IMPORTANTISIMO!!
# Para esto se requiere dirigirse a la ruta donde se tenga el archivo my.ini, en windows la ruta es comunmente *"C:\ProgramData\MySQL\MySQL Server 8.0\my.ini"* y se debe ingresar el siguiente código y luego reiniciar el PC:
#
# ```ini
#
# [client]
# loose_local_infile=1
#
# [mysqld]
# local-infile=ON
# ```
# Ahora si prodemos proceder a ejecutar el codigo que nos permite leer información de archivos que no se encuentren dentro del directorio de la base de datos

# + language="sql"
#
# load data local infile 
#     'C:/Users/HP/01. Projectos de jupyter/01. Ciencia de los datos/bancos.csv'
# into table bancos
# fields terminated by ','
# (bank,ccn,ssn);
#
# load data local infile
#     'C:/Users/HP/01. Projectos de jupyter/01. Ciencia de los datos/personas.csv'
# into table personas
# fields terminated by ','
# (ssn, phone, city, maritalstatus, fullname, birthdate);
#
# load data local infile
#     'C:/Users/HP/01. Projectos de jupyter/01. Ciencia de los datos/franquicias.csv'
# into table franquicias
# fields terminated by ','
# (ccntype,ccn,validthru,userkey,userpin,quota);
# -

# Ahora eliminamos los CSV del disco. (Este es un comando de Python)

# +
import os

archivos = ['bancos.csv','personas.csv','franquicias.csv']


for archivo in archivos:
    try:
        os.remove(archivo)
        print("El archivo {} ha sido eliminado con éxito".format(archivo))
    except:
        print("El archivo {} no pudo ser encontrado para ser eliminado".format(archivo))
# -

# 3. Visualizar a ver si las tablas realmente quedaron bien pobladas

# + language="sql"
#
# select * from personas limit 5;

# + language="sql"
#
# select * from bancos limit 5;

# + language="sql"
#
# select * from franquicias limit 5;
# -

# ## Segunda parte
# ### Joins
# Ahora vamos a hacer un `join` para cruzar la información entre diferentes tablas

# + language="sql"
# select 
#     fullname,
#     phone,
#     bank,
#     personas.ssn,
#     bancos.ssn
# from personas
# join bancos
#     on (cast(replace(personas.ssn,"-","") as unsigned) = 
#         cast(replace(  bancos.ssn,"-","") as unsigned))
# limit 10;
# -

# # Ejercicios
# 01. Cantidad de tarjetas emitidas por cada banco:

# + language="sql"
#
# select
#     bank,
#     count(*) as numero_de_tarjetas
# from bancos
# left join franquicias
#     on (cast(replace(     bancos.ccn,"-","") as unsigned) = 
#         cast(replace(franquicias.ccn,"-","") as unsigned))
# group by bank
# order by bank
# -

# 02. tabla que contenga la cantidad de tarjetas por cada franquicia

# + language="sql"
#
# select 
#     ccntype,
#     count(*)
# from franquicias
# group by ccntype
# order by ccntype;
# -

# 03. Tabla con el cliente mas joven de cada banco

# + language="sql"
#
# with t0 as (
#     select 
#         bank,
#         min(timestampdiff(year, birthdate, now())) as min_edad
#     from personas
#     join bancos
#         on (cast(replace(personas.ssn,"-","") as unsigned) = 
#             cast(replace(  bancos.ssn,"-","") as unsigned))
#     group by bank
# ),
# t1 as (
#     select 
#         bank,
#         fullname,
#         phone,
#         birthdate,
#         timestampdiff(year, birthdate, now()) as edad
#     from personas
#     join bancos
#         on (cast(replace(personas.ssn,"-","") as unsigned) = 
#             cast(replace(  bancos.ssn,"-","") as unsigned))
#     order by bank, edad
# )
# select
#     t1.*
# from t1
# inner join t0
#     on (t0.bank = t1.bank and t0.min_edad=t1.edad)
# order by bank;
# -

# 04. tabla que contenga por cada banco, la cantidad de clientes nacidos cada año

# + language="sql"
#
# with t0 as (
#     select 
#         bank,
#         year(birthdate) as anho
#     from personas
#     join bancos
#         on (cast(replace(personas.ssn,"-","") as unsigned) = 
#             cast(replace(  bancos.ssn,"-","") as unsigned))
# )
# select
#     bank,
#     anho,
#     count(*) as num_clientes_nacidos
# from t0
# group by bank, anho
# order by bank, anho;
# -

# 05. tabla que contenga la suma de los montos (quote) por franquisia

# + language="sql"
#
# select
#     ccntype,
#     sum(quota)
# from franquicias
# group by ccntype
# order by ccntype;
# -

# 06. tabla que contenga el banco, la franquisia y el nombre del titular de la tarjeta de crédito

# + language="sql"
#
# select
#     bancos.bank         as banco,
#     franquicias.ccntype as franquicia,
#     personas.fullname   as nombre_completo
# from personas
# left join bancos
#     on (cast(replace(   personas.ssn,"-","") as unsigned) = 
#         cast(replace(     bancos.ssn,"-","") as unsigned))
# left join franquicias
#     on (cast(replace(     bancos.ccn,"-","") as unsigned) = 
#         cast(replace(franquicias.ccn,"-","") as unsigned))

StarcoderdataJupyterScriptsDedupFiltered