El día de hoy presentaremos el proceso en el cual manejaremos el reto numero 5 de fp20analytics.
Por acá comparto el link para los que desean acceder: https://fp20analytics.com/challenge
En este post se presenta el proceso para manejar el reto número 5 de fp20analytics, el cual implica crear un modelo de datos en PostgreSQL a partir de un archivo XLSX y realizar la importación de los datos.
Se detalla el proceso de creación de una tabla de aterrizaje en PostgreSQL y se proporciona el código de Python para importar los datos del archivo XLSX a la tabla.
También se describe el proceso de creación de la tabla para la segunda hoja del archivo XLSX y se proporciona el código correspondiente para importar los datos a la tabla.
Para este reto tenemos un fichero único de extensión XLSX, en cual visualizamos los datos que deseamos.
Cuál es el input en el cual trabajaremos:
De tipo XLSX en el cual manejaremos llamado Business Intel_Phone Sales_Prep with Calendar Table.
Don crearemos este modelo de datos en ese caso en PostgreSQL.
Lo crearemos en PostgreSQL, dado que con este podemos incrementar nuestras habilidades en SQL, ahora debemos crear el importador en postgresql para llevar la data hacia la base de datos y realizar la limpieza en ella.
Paso 1) Lo primero que realizaremos será acceder al editor de query dentro de la BBDD que deseamos utilizar para crear nuestra tabla de tipo stage o aterrizaje
Paso 2) Analizaremos el tipo de columna inicial que tiene cada el fichero para así crear las columnas en postgres, debemos considerar en el cual debemos controlar el tamaño de los caracteres en los cuales insertaremos la data , en base al máximo de cada uno crearemos las columnas.
La formula utilizada para obtener el máximo de caracteres de las columnas en Excel es MAX(largo(Columna)) este devolverá el numero de caracteres máximo de cada fila lo ordenas de mayor a menor y tienes el tamaño maximo a colocar en el script de postgresql.
Paso 3) Script para crear la tabla dentro de postgres , recordemos que esta será la tabla de aterrizaje para el input externo no es la tabla definitiva.
Create table stage
(
Date_original date,
Country varchar(14),
Distribuitor varchar(14),
Brand varchar(8),
Operators varchar(8),
Unit_Cost int,
Amount int,
Unit_Price int ,
Sales int
)
Acá encontraras en GitHub el código completo:
https://github.com/vicente2121/Importadores_reto_5_fpchallenge/blob/main/Tabla%20stage%20hoja%20dos
select * from stage
Paso 4) Importador en Python para insertar los datos a la tabla creada, recordemos debemos crear el script en Python utilizando las librerías que explicaremos y a continuación el código creado.
Antes de crear el código procedo a realizar un breve explicación sobre que hace cada parte de este script
Python
Copy code
import pandas as pd
import psycopg2
Importa dos bibliotecas de Python, pandas y psycopg2. La biblioteca pandas se utiliza para manipular y trabajar con datos en formato tabular. La biblioteca psycopg2 se utiliza para conectarse y trabajar con bases de datos PostgreSQL.
python
Copy code
conn = psycopg2.connect(database=»Prueba_dos», user=»postgres», password=»contraseña», host=» «, port=»5432»)
cur = conn.cursor()
Crea una conexión a la base de datos de PostgreSQL utilizando los parámetros proporcionados: la base de datos a la que se desea conectar («Prueba_dos»), el usuario con el que se desea conectar («postgres»), la contraseña para el usuario («contraseña»), el host (normalmente es la dirección IP del servidor donde se encuentra la base de datos) y el puerto que se va a utilizar para conectarse (por defecto, es el puerto 5432).
Crea un cursor, que es un objeto que se utiliza para ejecutar comandos en la base de datos.
python
Copy code
df = pd.read_excel(«C:\\Business Intel_Phone Sales_Prep with Calendar Table.xlsx»)
Lee el archivo de Excel «Business Intel_Phone Sales_Prep with Calendar Table.xlsx» que se encuentra en la ubicación «C:\» y lo convierte en un DataFrame de Pandas. Un DataFrame es una estructura de datos de dos dimensiones que se utiliza para trabajar con datos en formato tabular.
python
Copy code
tuples = [tuple(x) for x in df.to_numpy()]
Convierte el DataFrame de Pandas en una lista de tuplas. Cada tupla representa una fila en el archivo de Excel. La función «to_numpy()» se utiliza para convertir el DataFrame en una matriz NumPy y luego la lista de comprensión se utiliza para crear una lista de tuplas a partir de la matriz.
python
Copy code
values = «,».join([«%s»] * len(tuples))
insert_stmt = f»INSERT INTO stage (Date_original, Country, Distribuitor,Brand,Operators,Unit_Cost,Amount,Unit_Price,Sales) VALUES {values}»
Crea una cadena de inserción de SQL. La cadena de inserción es una cadena de texto que especifica qué tabla se va a insertar y los valores que se van a insertar en la tabla. La tabla se llama «stage» y los nombres de las columnas son «Date_original», «Country», «Distribuitor», «Brand», «Operators», «Unit_Cost», «Amount», «Unit_Price» y «Sales». La sintaxis «{values}» se utiliza para indicar que los valores se van a insertar en la tabla posteriormente. La variable «values» contiene una cadena que especifica el número de valores que se van a insertar en la tabla.
python
Copy code
cur.execute(insert_stmt, tuples)
conn.commit()
Ejecuta la consulta SQL utilizando el cursor «cur» y los parámetros «insert_stmt» y «tuples». La función «execute()» se utiliza para ejecutar la consulta SQL. Los valores de las tuplas se insertan en la tabla utilizando los parámetros. La función «commit()» se utiliza para confirmar los cambios en la base de datos.
python
Copy code
cur.close()
conn.close()
Cierra la conexión y el cursor para liberar los recursos. La función «close()» se utiliza para cerrar tanto el cursor como la conexión.
Acá en GitHub dejo el código del importador completo https://github.com/vicente2121/Importadores_reto_5_fpchallenge/tree/main
Paso 5) Ahora Pasamos para la creación de la tabla para la segunda hoja de nuestro fichero en Excel que es fechas, lo primero será ver las dimensiones y el tamaño de las columna con texto aplicamos el mismo código visto
La formula utilizada para obtener el máximo de caracteres de las columnas en Excel es MAX(largo(Columna)) este devolverá el numero de caracteres máximo de cada fila lo ordenas de mayor a menor y tienes el tamaño maximo a colocar en el script de postgresql.
Paso 6) Creamos la tabla para la segunda hoja , recordemos son tablas de aterrizaje para luego hacer las transformaciones y pasarlo a tablas de hechos y dimensiones en cada caso.
Create table dates
(
Year int,
Quarter int,
Month int,
Month_Name varchar(3),
Month_Year varchar(8),
Week_Day int,
Day_Name varchar(3),
Week_Number int,
Quater_Q varchar(2),
Dates date
)
Acá encontraras en GitHub el código completo: https://github.com/vicente2121/Importadores_reto_5_fpchallenge/blob/main/Tabla%20stage%20codigo%20postgresql
Paso 7) Por ultimo pasamos a crear el segundo script para Python y el nuevo importador pero ahora leyendo la segunda hoja de nuestro input o fichero, y claro está cambiando las nueva tabla y sus campos
import pandas as pd
import psycopg2
# Conectarse a la base de datos
conn = psycopg2.connect(database=»Prueba_dos», user=»usuario de tu bbdd», password=»contraseña de tu bbdd», host=»localhost», port=»5432″)
cur = conn.cursor()
# Leer el archivo de Excel en un dataframe de pandas
df = pd.read_excel(«C:\\Business Intel_Phone Sales_Prep with Calendar Table.xlsx»,sheet_name=1)
# Convertir el dataframe de pandas en una lista de tuplas
tuples = [tuple(x) for x in df.to_numpy()]
# Crear una cadena de inserción de SQL
values = «,».join([«%s»] * len(tuples))
insert_stmt = f»INSERT INTO dates (Year, Quarter, Month,Month_Name,Month_Year,Week_Day,Day_Name,Week_Number,Quater_Q,Dates) VALUES {values}»
# Insertar las tuplas en la tabla de la base de datos
cur.execute(insert_stmt, tuples)
conn.commit()
# Cerrar la conexión
cur.close()
conn.close()
Acá encontraras en GitHub el código completo https://github.com/vicente2121/Importadores_reto_5_fpchallenge/tree/main
Con estos pasos tenemos el fichero con sus dos hojas en dos tablas de aterrizaje para poder empezar a modelar los datos.
La importación de datos son procesos esenciales en la creación de modelos de datos. Con este post, se presenta una forma de manejar estos procesos utilizando PostgreSQL y Python.
El proceso detallado en este post puede servir como guía para aquellos que necesitan crear modelos de datos a partir de archivos XLSX y desean utilizar herramientas como PostgreSQL y Python para llevar a cabo este proceso.
Al seguir estos pasos, se puede obtener una tabla de aterrizajeo para empezar a limpiar y ser utilizada para crear el modelado de datos.
Vicente Antonio Juan Magallanes
Business Intelligence Technical.
Perfil linkedin.
