Preprocesamiento de datos para Excel con Python

Para procesar datos de Excel con Python es necesario entender el uso de la librería Pandas del lenguaje Python, esta herramienta nos permite analizar y manipular datos de una forma rápida y flexible. Esta librería usa como elemento principal una estructura de datos llamada dataframe que contiene filas y columnas tal como una tabla de Excel, además, también se usan las series que pueden representar las columnas de Excel.

En este artículo se va a ejemplificar el uso de los dataframes con el objetivo de manipularlos correctamente y así poder aplicar sus funciones en posteriores procesos y tareas de automatización y procesamiento de datos de Excel.

Requisitos previos

Para poder usar la librería de pandas de Python es necesario instalarla por medio de la línea de comandos. Además, en caso de que ya se tenga instalada un versión de Python en la máquina junto con algunos proyectos del mismo lenguaje entonces es posible que se necesite crear un entorno virtual de Python que nos permita aislar el ambiente de trabajo y con ello evitar la incompatibilidad de las líberías del proyecto de pandas con las librerías de Python que requieren los otros proyectos.

En seguida se detallan algunos pasos recomendados para usar pandas de la mejor manera en nuestra computadora.

• Creación de un entorno virtual para evitar incompatibilidad entre librerías de Python
• Instalación de librería pandas con el entorno virtual previamente activado, esto nos va permitir que la instalación de pandas se realice únicamente al interior del entorno y no de forma global con las librerías de Python que posiblemente ya se tengan instaladas.
• Instalación de la librería openpyxl, algunos métodos de pandas dependen directamente de la librería openpyxl, por ejemplo, el guardado de dataframes en archivos excel requiere de esta librería, por tanto también será importante instalarla aunque en un primer momento solo se trabaje con el procesamiento de datos con pandas.
• Elección del interprete correcto de Python, la correcta elección de del interprete nos va ayudar a importar a nuestro programa las librerías que se instalaron para el entorno virtual que fue creado, esto es, si se elige otro interprete que fue instalado de manera global en la computadora y para ese entorno global no se instalo pandas entonces se van a crear errores de importación en el script de trabajo, por tanto se recomienda hacer uso de un editor de código que nos permita elegir un interprete de Python de entre los diversos que existan en nuestra computadora. Un editor de mucha utilidad es Visual Studio Code.

A continuación se muestran algunos comandos usados para este artículo, el interprete de comandos usado es el de Powershell de Windows.

> python - m venv dataframes # creación del entorno virtual

> .\dataframes\Scripts\activate # activación del entorno virtual

> pip install pandas # instalación de pandas

> pip install openpyxl # instalación de openpyxl

Creación de dataframes

Los dataframes se pueden crear a partir de estructuras de datos de Python, por ejemplo, listas, diccionarios y combinaciones de estas estructuras.

A continuación se crea un diccionario a partir de una lista.

lista = ['Guayaba', 'Manzana', 'Jitomate', 'Pera', 'Kiwi', 'Rabano']

dataframe = pd.DataFrame(lista)

Así mismo, podemos crear un dataframe  a partir de un diccionario de arreglos numpy.

datos = {

    'Producto': np.array(['Mayonesa', 'Cafe', 'Galletas']),

    'Precio' : np.array([100, 50, 29]),

    'Cantidad': np.array([40, 25, 20]),

    'Unidad': np.array(['Caja', 'Caja', 'Tarima'])

}

dataframeProductos = pd.DataFrame(datos)

Índice de una dataframe

El índice de un DataFrame es una serie de etiquetas que identifican cada fila, estos índices nos permiten acceder a los datos de manera eficiente y puede ser valores numéricos (valores predeterminado) o bien, etiquetas definidas por el usuario. Cuando imprimimos en pantalla un dataframe, los índices se pueden ver como una primer columna a la izquierda de los datos. Esto se pueden ver en la siguiente imagen.

Para establecer índices definidos por el usuario podemos hacer uso del método set_index() de pandas, esto se muestra en el siguiente código.

galletas = {

    'Marca': ['SaltyCookies', 'ChocoCookies', 'Polvorin', 'Canelocas', 'BakedCookies'],

    'Categoria': ['Saladas', 'Rellenas', 'Mantequilla', 'Azucaradas', 'Saludables'],

    'UPC': ['09897241', '05892231', '19497872', '89897228', '49807382'],

    'Lote': ['MNX09', 'ZXV87', 'RTY91', 'ZXP92', 'NNQ62'],

    'Precio': [55, 30, 65, 35, 70]

}

dataframeGall = pd.DataFrame(galletas)

dataframeNuevoIndice = dataframeGall.set_index('UPC')

Así mismo, podemos acceder a una fila específica por medio del método loc, para ello tenemos que especificar el índice, en este caso el índice fue establecido por el usuario. En este caso se accede a la fila cuyo índice es el UPC 05892231, la fila se imprime por pantalla.

print(dataframeNuevoIndice.loc['05892231'])

En caso de que se desee acceder a una columna de un dataframe tenemos que especificar la etiqueta de la columna. En el siguiente código se obtiene la columna o serie y se guarda en una variable.

columnaCodigos = dataframeGall['UPC']

También podemos acceder a filas que cumplan un determinado criterio, por ejemplo, usando el dataframe que contiene datos de galletas, podemos acceder a las filas cuyo precio sea menor a 40 pesos.

datosFiltrados =  dataframeGall[dataframeGall['Precio'] < 40]

En caso de querer guardar esas filas con precio menor a 40 en un archivo de excel, podemos hacer lo siguiente.

archivo = "preciosBajos.xlsx"

datosFiltrados.to_excel(archivo)

Con las herramientas descritas anteriormente podemos comenzar a procesar datos desde Python y de esta manera usar toda su infraestructura con el propósito combinarla con las capacidades de Excel. Por ejemplo, podemos recibir datos desde una API, procesarlos con pandas y posteriormente enviarlos a Excel para llevar a cabo algún análisis específico tal como crear reportes con los datos, tomar decisiones basadas en datos, gestión de logística, etc.

Referencias

• Entornos virtuales de Python
• Guía de usuario de pandas
• El interprete de Python
• Intérprete de Python en VSCode
  

Consultas entre dos o más tablas en SQL Server

SQL Server utiliza uniones para recuperar datos de varias tablas basándose en las relaciones lógicas entre ellas. Las combinaciones son fundamentales para las operaciones de bases de datos relacionales y permiten combinar datos de dos o más tablas en un único conjunto de resultados.

SQL Server implementa tanto operaciones de unión lógica (definidas por la sintaxis de Transact-SQL) como operaciones de unión física (los algoritmos que se utilizan para ejecutar las uniones). Comprender ambos aspectos ayuda a escribir consultas eficientes y a optimizar el rendimiento de la base de datos.

Las uniones indican la forma en la que SQL Server debe usar los datos de una tabla para seleccionar las filas de otra. 

Una condición de unión define cómo se relacionan dos tablas en una consulta mediante:

• La especificación de la columna de cada tabla que se utilizará para la unión. Una condición de unión típica especifica una clave foránea de una tabla y su clave asociada en la otra.
• La especificación de un operador lógico (por ejemplo, = o <>,) que se utilizará para comparar los valores de las columnas.

Las uniones se expresan lógicamente mediante la siguiente sintaxis de Transact-SQL:

• INNER JOIN 
• LEFT JOIN 
• RIGHT JOIN 
• FULL  JOIN 

En la unión de tipo Inner Join se obtienen los datos que son comunes a 2 o más tablas en las que se realiza la consulta. Left Join devuelve todos los registros de la tabla izquierda y también los que coinciden con los registros de la tabla de la derecha (La tabla de la izquierda es la primer tabla que se define en la consulta de SQL, la de la derecha es la segunda tabla que se define en la consulta), además, si no hay coincidencia entre ambas tablas no se devuelven resultados de registros. Right join es el caso contrario, devuelve todos los registros de la tabla derecha y también los que coinciden con los registros de la tabla de la izquierda. Full Join devuelve todos los registros de las tablas, los de la tabla izquierda, los de la derecha y los que coinciden en ambas tablas.

De manera visual la uniones se muestran en la siguiente imagen.

Ejemplos

Se puede usar el siguiente código para ejemplificar el uso de INNER JOIN.

CREATE TABLE clientes ( 

idcliente int NOT NULL primary key,

nombre varchar(20) NOT NULL,

apellido varchar(30) NOT NULL,

direccion varchar(100) NOT NULL,

ciudad varchar(50) NOT NULL,

telefono numeric(10) NULL,

);

CREATE TABLE ordenes(

id_orden int NOT NULL primary key,

idcliente int foreign key references clientes(idcliente),

fecha_orden date default getdate(),

id_vendedor int NOT NULL

);

SELECT ordenes.id_orden, Clientes.nombre, Clientes.apellido, ordenes.fecha_orden -- De

la tabla ordenes elige el id de la orden, de la tabla de clientes el nombre y el

apellido y de la tabla de ordenes la fecha

FROM ordenes -- Para esto, usa la tabla de ordenes

INNER JOIN Clientes on ordenes.idcliente = Clientes.idCliente; -- y por medio un inner

JOIN con la tabla Clientes trae esos campos CUANDO el id del cliente de la tabla de

ordenes sea el mismo id del cliente en la tabla de Clientes

SELECT ordenes.id_orden, Clientes.nombre, Clientes.apellido, ordenes.fecha_orden,

ordenes.id_vendedor -- También podemos traer el id del vendedor para saber quien le ha

vendido productos al cliente Juan

FROM ordenes

INNER JOIN Clientes ON ordenes.idcliente = Clientes.idCliente

WHERE nombre = 'Juan' ORDER BY fecha_orden; -- Realizamos la misma consulta anterior

pero filtrando para el cliente con nombre Juan y ordenamos por fecha

Este código se puede ver de otra manera: Se toma cada registro de la tabla de ordenes y se checa con qué cliente está asociado esa orden por medio del id del cliente, después de esta verificación se van a devolver el nombre y el apellido en la tabla de clientes pero también el id de la orden y la fecha de la orden en la tabla de ordenes, estos campos se devuelven para cada uno de los registros de la tabla de ordenes.

Si se desea saber cuáles clientes tienen ordenes de servicio y cuáles no las tienen, se puede hacer uso de left join porque de esta manera podemos obtener aquellos registros que están relacionados con la tabla de ordenes pero también aquellos que no están relacionados, esto cumple con la definición de left join que devuelve los propios registros de la tabla izquierda y también sus coincidencias con respecto a otra tabla con la que está relacionada.

El siguiente ejemplo de código muestra cómo se puede usar left join para solucionar el problema previamente explicado.

SELECT Clientes.nombre, Clientes.apellido, ordenes.id_orden, ordenes.fecha_orden -- Devuelve el nombre y el apellido de la tabla Clientes y el id de orden y la fecha de

la tabla de ordenes

FROM Clientes --(Clientes es la tabla de la izquierda o primer tabla) Para esto, usa la tabla de ordenes

LEFT JOIN ordenes on Clientes.idcliente = ordenes.idcliente -- y por medio un left join con la tabla ordenes trae esos campos CUANDO el id del cliente de la tabla de ordenes sea el mismo id del cliente en la tabla de Clientes

order by id_orden; -- ordena la consulta por el id de la orden

SELECT cli.nombre, cli.apellido, ord.id_orden, ord.fecha_orden

FROM Clientes cli -- La tabla Clientes usa 'cli' como alias

LEFT JOIN ordenes ord on cli.idcliente = ord.idcliente -- La tabla ordenes usa 'ord' como alias

order by id_orden;

Referencias

Para obtener más información sobre las uniones, consultar el siguiente enlace.

• Uniones en SQL Server
  

Limpieza de datos en Excel

La limpieza de datos es el proceso de identificar y corregir los errores e incoherencias en los conjuntos de datos sin procesar para mejorar la calidad de la información

Ventajas de la limpieza de datos

Disponer de datos limpios aumentará la productividad general y permitirá obtener información de la más alta calidad para la toma de decisiones. Algunas de las ventajas son las siguientes.

• Eliminación de errores cuando se usan múltiples fuentes de datos. 
• La disminución de errores da como resultado clientes más felices y empleados menos frustrados.
• Monitoreo de errores y mejores informes que permiten ver de dónde provienen los errores, lo que facilita la reparación de datos incorrectos o corruptos para futuras aplicaciones.

A continuación se describen algunas herramientas comunes de Excel que nos van a permitir limpiar un conjunto de datos.

Corrección ortográfica

Con esta herramienta podemos encontrar errores ortográficos, además, podemos buscar valores que no se usan de forma coherente, como los nombres de productos o servicios de una columna en una base de datos. Esta herramienta se puede encontrar en la pestaña Revisar y en el grupo con el mismo nombre. A continuación se muestra un ejemplo de uso.

Eliminación de espacios y caracteres

Es posible que en nuestro conjunto de datos haya caracteres y espacios en blanco adicionales a los que deberían de estar, para resolver esto podemos usar las funciones LIMPIAR y ESPACIOS, es específico, LIMPIAR quita los caracteres que no se pueden imprimir y ESPACIOS borra todos los espacios con excepción de aquellos que ya se dejan entre palabras. En seguida se muestra un ejemplo de uso de la función que elimina espacios.

Formato condicional

Con la herramienta de formato condicional podemos visualizar tablas con registros y elementos duplicados, una vez que estos hayan sido identificados podemos usar la opción de Quitar duplicados que se encuentra en la pestaña de Datos. Como ejemplo, si tenemos una columna que debería tener valores de código únicos para cada elemento, entonces podemos usar estas herramientas para quitar tales registros repetidos, véase la siguiente imagen.

Uso adecuado de mayúsculas y minúsculas

Es posible que nuestros datos no estén bien escritos, por ejemplo, los nombres y apellidos no hacen uso de iniciales mayúsculas o bien, es necesario que haya datos con todas las letras en mayúscula o minúscula, para abordar esto podemos usar las fórmulas de MAYUSC, MINUSC y NOMPROPIO que nos ofrece Excel. A continuación se muestra un ejemplo de uso de la formula para nombres propios.

Separar texto en varias columnas

Otra herramienta que puede ser de utilidad es la separación del texto en un celda entre varias celdas, para esto, Excel tiene la herramienta llamada texto en columnas, como ejemplo de uso, podemos separar el nombre y apellido de las personas que estén en una sola columna, o bien, separar ciudad y país. En seguida se muestra un ejemplo de texto a columnas.

Formato de fechas

En distintas ocasiones los datos de fechas no van a estar en el formato adecuado, más bien estarán en un formato diferente al propio para el dato, entonces se puede hacer uso de la herramienta de convertir texto a columnas para realizar la corrección, en este caso no se va elegir ningún delimitador para realizar la separación, además, solo vamos a elegir el formato que tendrá la fecha resultante. En la siguiente imagen se muestra como se puede configurar el formato de la fecha de salida para lograr nuestro objetivo.

Celdas vacías

En el caso de registros con celdas o campos vacíos primero se tiene que buscar la manera de obtener los datos por diversos medios, ya sea con el dueño de la base de datos, la página web desde donde se descargo la información, etc. sin embargo, cuando esto no es posible entonces podemos proceder a eliminar los registros con datos faltantes para que el análisis devuelva mejores resultados, para esto podemos filtrar las columnas y una vez hecho esto podemos borrar los registros. En la siguiente imagen se muestra una tabla que contiene nombres de producto faltantes, podemos filtrar por medio de ese campo y luego elegir que hacer con tales elementos de acuerdo a las posibilidades.

Uso de macros
La limpieza de datos en Excel puede ser repetitiva, tardada y propensa a errores, para optimizar este tipo de flujos de trabajo podemos usar las herramientas de Visual Basic y así agilizar diversos procesos. En el siguiente ejemplo se presenta una subrutina que permite limpiar datos y quitar espacios innecesarios en columnas con datos erróneos.

Sub limpiar_quitar_espacios()

    Dim valorRango As String

    valorRango = "A2:A100"

    Dim celdaIndice As Range

    Dim valorCelda As String

    Sheets("Productos").Select

       

    For Each celdaIndice In Range(valorRango)

        valorCelda = celdaIndice.Value

        celdaIndice.Value = WorksheetFunction.Clean(valorCelda) 

        valorCelda = celdaIndice.Value

        celdaIndice.Value = WorksheetFunction.Trim(valorCelda)

    Next

    

    valorRango = "B2:B100"

    For Each celdaIndice In Range(valorRango)

        valorCelda = celdaIndice.Value

        celdaIndice.Value = WorksheetFunction.Clean(valorCelda) 

        valorCelda = celdaIndice.Value

        celdaIndice.Value = WorksheetFunction.Trim(valorCelda)

    Next

End Sub

Esta subrutina elimina los espacios y caracteres indeseables en dos rangos diferentes, en las columnas A y B, para esto es necesario reiniciar la variable valorRango con las celdas a recorrer, además se tienen que llamar a las funciones Clean y Trim que son las mismas que LIMPIAR Y ESPACIOS que se usan en el entorno gráfico de las hojas de un libro de Excel.

Referencias

A continuación se muestran los enlaces que describen los detalles técnicos de los métodos y objetos de Visual Basic utilizados en este artículo.

• Objeto WorksheetFunction
• Método Trim
  
• Método Clean
  

Modularización de consultas por medio de procedimientos almacenados en SQL Server

En SQL Server un procedimiento almacenado es un grupo de una o más instrucciones de Transact-SQL (Lenguaje de programación). Estos procedimientos aceptan parámetros de entrada y devuelven múltiples valores en forma de parámetros de salida al programa que llama al procedimiento, además contienen instrucciones de programación que realizan operaciones en la base de datos. También, estos procedimientos devuelven un valor de estado al programa que llamo al procedimiento para indicar éxito o fracaso (y el motivo del fracaso).

Gracias al control de flujo que ofrece SQL Server es que podemos usar los procedimientos almacenados. Como se describió, Transact-SQL tiene diversas instrucciones que nos permiten emplear estructuras de control tal como en los lenguajes de programación, es decir, podemos usar construcciones del lenguaje para modificar el flujo de ejecución de las instrucciones de un programa.

Los procedimientos almacenados tienen la ventaja de que permiten modularizar soluciones de software al encapsular el código en unidades manejables y con ello facilitar su reutilización. Además, con el uso de procedimientos almacenados los comandos de estos se ejecutan como un único lote de código, esto puede reducir significativamente el tráfico de red entre el servidor y el cliente, ya que solo se envía la llamada a través de la red para ejecutar un procedimiento determinado. Sin la encapsulación de código que proporciona un procedimiento, cada línea de código tendría que atravesar la red.

Creación de procedimientos almacenados

La sentencia CREATE PROCEDURE es la que nos permite la creación y persistencia de bloques de código, además, cuando se usa esta sentencia, el procedimiento se guarda en la carpeta Databases > NombreBaseDeDatos > Programmability > Stored procedures, aquí es donde podemos encontrar todos los bloques de código que se han guardado en la propia base de datos. La carpeta de procedimientos almacenados se muestra en la siguiente imagen.

Como ejemplo de código, vamos a suponer que tenemos una tabla de productos que tiene un campo cantidad y que deseamos actualizar los valores de este campo, para esto, podemos crear un procedimiento almacenado que nos va permitir llamarlo cada vez que lo necesitemos sin tener que volver a escribir el código en su interior. El código es el que se muestra en seguida.

CREATE PROCEDURE p_actualiza_stock

AS

BEGIN

    IF EXISTS(SELECT * FROM productos

            WHERE cantidad = 0)

        UPDATE 

            productos SET cantidad = 20

        WHERE 

            cantidad <= 5;

END

Con el código anterior se guarda el procedimiento y ahora solo falta ejecutarlo por medio de la sentencia EXEC, esto se muestra a continuación.

EXECUTE p_actualiza_stock;

Es importante mencionar que cada vez que un procedimiento se guarda y modifica en la base de datos, será necesario ejecutarlo o mandarlo a llamar por medio de la sentencia EXEC.

Actualización de procedimientos almacenados

Para actualizar o modificar un procedimiento que ya guardamos previamente, tenemos que usar la sentencia ALTER PROCEDURE. Como ejemplo, podemos modificar el procedimiento que describimos previamente, en este caso podemos agregar 10 unidades más a cada artículo que tenga 20 elementos, entonces el código es el siguiente.

ALTER PROCEDURE p_actualiza_stock

AS

BEGIN

    IF EXISTS(SELECT * FROM productos

            WHERE cantidad = 10)

        UPDATE 

            productos SET cantidad = 30

        WHERE 

            cantidad = 20;

END

De igual manera, la instrucción ALTER modifica y guarda el procedimiento en la base de datos en cuestión.

Borrar procedimientos almacenados

Para borrar procedimientos almacenados tenemos que usar la instrucción DROP PROCEDURE, entonces para borrar el procedimiento con el que hemos estado trabajando tenemos que usar el código que muestra a continuación.

DROP PROCEDURE p_actualiza_stock;

Con esto, el módulo de actualización de stock ya no estará disponible en la carpeta Stored Procedures de la base de datos.

Parámetros de entrada en procedimientos almacenados

Para usar parámetros o variables al interior de una procedimiento almacenado tenemos que declarar las variables antes del propio código de ejecución, es decir, antes de la clausula AS, recordemos que la declaración de variables tiene la sintaxis @nombreVar TipoDeDato = valor, además, si se usan varias variables tenemos que separarlas por comas. En el siguiente ejemplo se muestra un procedimiento almacenado que nos permite buscar un producto con determinados valores en cada campo. En este caso la tabla de productos tiene los campos nombre, precio y cantidad (existencia) en su estructura.

CREATE OR ALTER PROCEDURE p_buscar_producto

@producto VARCHAR(40) = 'Microcontrolador',

@precioProducto SMALLMONEY = 29.99,

@existencia INT = 35

AS

SELECT * FROM productos

WHERE

    nombre = @producto AND

    precio = @precioProducto AND

    cantidad = @existencia;

En esta ocasión se uso la palabra reservada ALTER en el encabezado del procedimiento almacenado, con esto podemos crear y/o modificar un procedimiento en el mismo lugar sin la necesidad de realizar estas operaciones por separado.

Referencias

En los siguientes enlaces se puede obtener más detalles técnicos sobre las sentencias de código usadas en este artículo.

• Control de flujo en SQL Server
• Sentencia CREATE PROCEDURE
  
• Sentencia EXECUTE
• Sentencia ALTER PROCEDURE
• Declaración de variables T-SQL