Original article: How to Automate Excel Tasks with Python
Excel es una herramienta sorprendentemente común para el análisis de datos.
Los analistas de datos pueden modificar, examinar y mostrar grandes cantidades de datos con facilidad utilizando Excel, lo que facilita la obtención de perspectivas y la toma de decisiones acertadas.
La versatilidad de Excel permite a los usuarios llevar a cabo una variedad de actividades de análisis de datos, desde operaciones matemáticas sencillas hasta análisis estadísticos complejos. Además, Excel ofrece automatización mediante el uso de programas de terceros como Python o el lenguaje de programación integrado VBA.
Excel se utiliza con frecuencia para el análisis de datos en una variedad de industrias, incluyendo la banca, la atención médica y el marketing, gracias a su versatilidad y facilidad de uso.
Pero como analista de datos, es posible que te encuentres repitiendo tareas mundanas a diario cuando trabajas con Excel.
Estas tareas pueden incluir copiar y pegar datos, dar formato a celdas y crear gráficos, entre otras. Con el tiempo, esto puede volverse monótono y consumir mucho tiempo, dejándote con menos tiempo para concentrarte en aspectos más importantes del análisis de datos, como identificar tendencias, valores atípicos y perspectivas.
Por eso, automatizar Excel utilizando Python puede cambiar el juego, ayudándote a optimizar tus flujos de trabajo y liberar tiempo para un análisis más significativo.
En este tutorial, te mostraré algunas formas útiles de crear, actualizar y analizar hojas de cálculo de Excel utilizando programación en Python. ¡Vamos a sumergirnos!
¿Cómo fusionar dos hojas de cálculo separadas con Python?
Los analistas de datos a menudo tienen que trabajar con muchas hojas de cálculo, lo que puede volverse agobiante cuando tienes que fusionar esos archivos juntos.
El código a continuación te ayuda a fusionar dos archivos separados juntos.
import pandas as pd
# Read in the two Excel files
file1 = pd.read_excel('file1.xlsx')file2 = pd.read_excel('file2.xlsx')
# Merge the two files using the concat() method
merged_file = pd.concat([file1, file2], ignore_index=True)
# Write the merged file to a new Excel file
merged_file.to_excel('merged_file.xlsx', index=False)En este código, primero importamos la biblioteca Pandas, que utilizaremos para leer y manipular los archivos de Excel.
Primero usamos el método read_excel() para leer tanto en file1.xlsx como en file2.xlsx.
Luego, empleamos el método concat() para unir los dos archivos juntos. El argumentoignore_index=True garantiza que los valores de índice de ambos archivos se reinicien, de modo que no terminemos con valores de índice duplicados en el archivo fusionado.
Finalmente recurrimos al método to_excel() para escribir el archivo fusionado en un nuevo archivo de Excel llamado merged_file.xlsx. También establecemos index=False para asegurarnos de que la columna de índice no se incluya en el archivo de salida.
Cómo Importar y Exportar Datos con Python
Esta tarea implica el uso de bibliotecas de Python como Pandas para leer archivos de Excel en un objeto DataFrame. Luego puedes manipularlo y analizarlo utilizando Python.
También puedes exportar datos desde Python de vuelta a un archivo de Excel utilizando las mismas bibliotecas.
import pandas as pd
# Import Excel file
df = pd.read_excel('filename.xlsx', sheet_name='Sheet1')
# Export to Excel file
df.to_excel('new_filename.xlsx', index=False)El código dado importa la biblioteca Pandas y lee un archivo de Excel llamado "filename.xlsx" desde la Hoja1 del libro de trabajo, almacenando los datos en un marco de datos de Pandas llamado "df". Luego, el marco de datos se exporta a un nuevo archivo de Excel llamado "new_filename.xlsx" utilizando el método "to_excel". El parámetro "index=False" se utiliza para excluir la indexación de filas en el archivo de salida.
Esencialmente, el código copia el contenido del archivo de Excel original a un nuevo archivo utilizando Pandas.
Cómo Limpiar y Transformar Datos usando Python
Esta tarea implica el uso de bibliotecas de Python como Pandas para limpiar y transformar datos en Excel.
Esto puede incluir la eliminación de duplicados, filtrado de datos según criterios específicos y realización de cálculos sobre los datos.
import pandas as pd
# Remove duplicates
df = df.drop_duplicates()
# Filter data
df = df[df['column_name'] > 10]
# Perform calculations
df['new_column'] = df['column1'] + df['column2']El fragmento de código anterior realiza tareas de limpieza y manipulación de datos en un marco de datos de Pandas llamado 'df' utilizando la biblioteca Pandas.
En primer lugar, elimina filas duplicadas de 'df' utilizando el método "drop_duplicates". En segundo lugar, filtra el marco de datos 'df' seleccionando las filas donde el valor en la columna 'column_name' es mayor que 10 y asigna el resultado filtrado a un nuevo marco de datos llamado 'data_df'.
Por último, se añade una nueva columna llamada 'new_column' a 'df' que contiene la suma de los valores de 'column1' y 'column2'.
En general, el código limpia y manipula eficazmente los datos eliminando duplicados, filtrando filas específicas y añadiendo una nueva columna calculada al marco de datos original.
Cómo Realizar Análisis de Datos con Python
Este proceso implica utilizar bibliotecas de Python como Pandas y NumPy para llevar a cabo análisis de datos en datos de Excel.
Esto podría abarcar desde calcular estadísticas resumidas, como la media y la desviación estándar, hasta crear informes personalizados mediante la agrupación de datos según criterios específicos.
import pandas as pd
import numpy as np
# Calculate summary statistics
df.describe()
# Create custom reports
df.pivot_table(values='column_name', index='category_name', columns='date')El código utiliza las bibliotecas Pandas y NumPy y realiza tareas de análisis de datos e informes en un marco de datos de Pandas llamado "df".
En primer lugar, calcula estadísticas resumidas para los datos numéricos en el marco de datos utilizando el método "describe". Este método genera información valiosa sobre la distribución, tendencia central y dispersión de los datos.
En segundo lugar, el código utiliza el método "pivot_table" para crear informes personalizados a partir del marco de datos. Este método resume y agrega los datos en el dataframe y puede producir tablas en varios formatos.
En este código, genera un nuevo marco de datos donde los valores de 'column_name' están agrupados por las columnas 'category_name' y 'date'.
En resumen, el código realiza tareas de análisis estadístico e informes en el marco de datos para obtener información de los datos.
Cómo Crear Gráficos con Python
Esta tarea implica utilizar bibliotecas de Python como matplotlib o seaborn para crear gráficos y diagramas a partir de datos de Excel.
Puedes personalizar estos gráficos para mostrar datos específicos y darles formato para cumplir con requisitos particulares.
import pandas as pd
import matplotlib.pyplot as plt
# Create a bar chart
df.plot(kind='bar', x='category_name', y='sales')
plt.show()
# Create a scatter plot
df.plot(kind='scatter', x='column1', y='column2')plt.show()El código importa dos bibliotecas, Pandas y matplotlib.pyplot, utilizando los alias 'pd' y 'plt', respectivamente.
Luego, se utiliza el método "plot" de Pandas para crear dos tipos de gráficos. El primer tipo de gráfico es un gráfico de barras que muestra la relación entre las columnas 'category_name' y 'sales' en el dataframe "df".
El segundo tipo de gráfico es un gráfico de dispersión que muestra la relación entre las columnas 'column1' y 'column2' en el mismo marco de datos. El código utiliza los parámetros "kind='bar'" para el gráfico de barras y "kind='scatter'" para el gráfico de dispersión para crear los respectivos gráficos.
Por último, se llama al método "show" para mostrar los gráficos en la pantalla. En resumen, el código utiliza Pandas y matplotlib para crear un gráfico de barras y un gráfico de dispersión para visualizar los datos en el marco de datos "df".
Cómo Realizar Visualización de Datos en Python
Esta tarea implica utilizar bibliotecas de Python como Plotly y Bokeh para crear visualizaciones de datos interactivas a partir de datos de Excel.
Estas visualizaciones permiten a los usuarios explorar los datos de nuevas formas, como mediante el zoom en puntos de datos específicos o el filtrado de datos según criterios específicos.
import pandas as pd
import plotly.express as px
# Create a heatmap
fig = px.imshow(df.corr())
fig.show()
# Create a line chart
fig = px.line(df, x='date', y='sales', color='category')
fig.show()El código utiliza las bibliotecas Pandas y plotly.express para crear dos tipos de visualizaciones. Primero, se crea un gráfico de mapa de calor utilizando el método "imshow" de plotly.express que visualiza la correlación entre las columnas en el marco de datos "df".
En segundo lugar, se crea un gráfico de líneas utilizando el método "line" de plotly.express que muestra la relación entre las columnas 'date' y 'sales' mientras diferencia entre las categorías basadas en la columna 'category' del dataframe. Ambos gráficos se muestran utilizando el método "show".
Cómo Automatizar la Generación de Informes con Python
Esta tarea implica utilizar scripts de Python para automatizar el proceso de generación de informes a partir de datos de Excel.
Puedes configurar estos scripts para que se ejecuten según un horario regular, como diario o semanal. También pueden actualizarse automáticamente a medida que haya nuevos datos disponibles.
import pandas as pd
# Create daily report
df_daily = df[df['date'] == '2022-01-01']
df_daily.to_excel('daily_report.xlsx', index=False)
# Create weekly report
df_weekly = df.groupby('category').sum()
df_weekly.to_excel('weekly_report.xlsx', index=False)El código crea un informe diario mediante la creación de un nuevo marco de datos "df_daily" que incluye solo las filas donde la columna 'date' es igual a '2022-01-01'. Esto se logra utilizando la función de indexación booleana de Pandas.
Posteriormente, se utiliza el método "to_excel" para exportar los datos filtrados a un archivo de Excel llamado "daily_report.xlsx", sin incluir la columna de índice.
A continuación, el código crea un informe semanal agrupando el marco de datos "df" por la columna 'category' y sumando los valores de todas las demás columnas. Esto se logra utilizando los métodos "groupby" y "sum" de Pandas.
El resultado se guarda en un nuevo marco de datos llamado "df_weekly". Por último, se utiliza el método "to_excel" para exportar los datos agregados a un archivo de Excel llamado "weekly_report.xlsx", sin incluir la columna de índice.
En resumen, el código crea dos informes utilizando la biblioteca Pandas. El primer informe es un informe diario que incluye solo datos de una fecha específica, y el segundo informe es un informe semanal que agrupa los datos por categoría. Ambos informes se exportan a archivos de Excel utilizando el método "to_excel".
Cómo Automatizar Tareas Repetitivas con Macros y Scripts en Python
Esta tarea implica utilizar Python para automatizar tareas repetitivas en Excel, como la entrada de datos o el formato.
Puedes hacer esto creando macros o scripts que se ejecuten automáticamente, o utilizando Python para interactuar directamente con la aplicación de Excel.
import win32com.client as win32
# Open Excel file
excel = win32.gencache.EnsureDispatch('Excel.Application')
workbook = excel.Workbooks.Open(r'filename.xlsx')
# Run macro
excel.Application.Run('macro_name')
# Save and close Excel
fileworkbook.Save()workbook.Close()excel.Quit()El código utiliza el módulo win32com.client para interactuar con Microsoft Excel a través de la API de Windows.
Primero, se abre una instancia de la aplicación Excel utilizando el método EnsureDispatch(), y el archivo de Excel especificado se abre utilizando el método Workbooks.Open().
A continuación, se ejecuta una macro utilizando el método Application.Run(), pasando el nombre de la macro como argumento.
Finalmente, los cambios realizados en el archivo de Excel se guardan utilizando el método Save(), el libro de trabajo se cierra utilizando el método Close(), y la aplicación de Excel se termina utilizando el método Quit().
Cómo Extraer Datos con Python
Esta tarea implica utilizar bibliotecas de Python como requests y Beautiful Soup para extraer datos de páginas web u otras fuentes e importarlos a Excel.
Luego, puedes analizar y manipular estos datos utilizando bibliotecas de Python como Pandas.
import pandas as pd
import requests
from bs4 import BeautifulSoup
# Scrape data from web page
url = 'https://www.website.com/data'
response = requests.get(url)
soup = BeautifulSoup(response.text, 'html.parser')
table = soup.find('table')
df = pd.read_html(str(table))[0]
# Export to Excel file
df.to_excel('scraped_data.xlsx', index=False)Este código utiliza la biblioteca requests para enviar una solicitud GET HTTP a la URL 'https://www.example.com'. Luego utiliza la biblioteca BeautifulSoup para analizar el contenido HTML de la respuesta en un objeto BeautifulSoup nombrado 'soup'.
Puedes luego utilizar métodos de BeautifulSoup como find_all() para extraer datos específicos del HTML:
links = []for link in soup.find_all('a'): href = link.get('href') links.append(href)
Este código encuentra todas las etiquetas de anclaje en el HTML y extrae el valor del atributo 'href' para cada una, agregándolos a una lista nombrada 'links'.
Cómo Utilizar Python para Integrar Excel con Otras Aplicaciones
Esta tarea implica utilizar Python para integrar Excel con otras aplicaciones, como bases de datos o servicios web.
Puedes hacer esto utilizando bibliotecas de Python como pyodbc para conectarte a bases de datos o mediante el uso de APIs para conectarte a servicios web. Esto permite una transferencia y análisis de datos sin problemas entre diferentes aplicaciones.
import pandas as pd
import pyodbc
# Connect to database
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=server_name;DATABASE=db_name;UID=user_id;PWD=password')
# Read data from database
query = 'SELECT * FROM table_name'
df = pd.read_sql(query, cnxn)
# Export to Excel file
df.to_excel('database_data.xlsx', index=False)El código establece una conexión a una base de datos SQL Server utilizando el método pyodbc.connect(), donde el controlador, el nombre del servidor, el nombre de la base de datos, el ID de usuario y la contraseña se proporcionan como argumentos.
Luego, se define y ejecuta una consulta SQL para recuperar datos de una tabla en la base de datos utilizando el método pd.read_sql(), donde la consulta SQL y el objeto de conexión se proporcionan como argumentos. Los datos recuperados se almacenan en un marco de datos de pandas.
Finalmente, los datos en el Marco de Datos se exportan a un archivo de Excel llamado "database_data.xlsx" utilizando el método to_excel(), excluyendo la columna de índice de la exportación al establecer el parámetro index en False.
Conclusión
Python es un lenguaje versátil que puedes utilizar para automatizar muchas tareas de Excel. También puedes utilizar diversas bibliotecas como Pandas, openpyxl, xlwings y pyautogui para manipular datos, extraer información, generar informes y automatizar tareas repetitivas.
La automatización puede ahorrar tiempo y esfuerzo, reducir errores y aumentar la productividad. La competencia en Python puede ser una habilidad valiosa para cualquier profesional que trabaje con Excel, ya sea un analista de datos o financiero. Al aprender Python, puedes llevar tu trabajo a nuevos niveles.