Curso
Importación intermedia de datos en Python
2 h
214.3K
Excel, mi viejo amigo
Yo soy de los que trastean con datos en hojas de cálculo simplemente porque puedo. La semana pasada, mi responsable envió una lista de personas que no habían actualizado sus checklists de proyecto y, en cuanto la abrí, empecé a montar tablas y a sacar estadísticas por persona; luego le pasé por chat un resumen con los datos desglosados. Seguramente le volví un poco loco, pero no lo puedo evitar: me encanta el análisis de datos y me encanta Excel. Lo uso desde 2001 y, hasta hace unos meses, era mi herramienta preferida para organizar, analizar y visualizar datos. Es genial para estructurar información, hacer cálculos y análisis, e incluso funciona como una base de datos sencilla. Lo he usado para todo: desde llevar mi presupuesto, gastos y entrenamientos, hasta informes de varias páginas sobre fuerza de personal en el Ejército. Allí Excel funcionaba bastante bien y era prácticamente la única opción. Por motivos de seguridad no tenía acceso a VBA, Python ni a ningún otro tipo de automatización. Además, los conjuntos de datos eran lo bastante pequeños como para que Excel los manejara sin problemas.
Hace casi un año empecé a trabajar en el Abilities Lab de Cerner como ingeniero de sistemas en pruebas de rendimiento de software. En pocas palabras: pruebas de rendimiento. Probamos software nuevo y actualizado antes de que llegue a los clientes y lo medimos todo: CPU y memoria en Windows y Unix, rendimiento de la JVM, tiempos de respuesta tanto en servicios backend como en la interfaz de usuario, rendimiento de bases de datos, el impacto en nuestro software actual, rendimiento de red... y prácticamente cualquier cosa que se te ocurra. Ninguna prueba se limita a un único equipo; la mayoría combinan dispositivos Windows y Unix (físicos y virtuales), sistemas de usuario final y servidores. En uno de los proyectos en los que participé, teníamos más de 20 sistemas de los que había que recopilar datos.
Según la duración del proyecto, puede haber más de 50 pruebas que revisar, analizar y reportar. Todos esos datos hay que resumirlos en un informe final de resultados que se publica en una página interna de Jira para que lo consuma el equipo de desarrollo. Tenemos algunas herramientas internas personalizadas que agregan gran parte de los datos, pero no todos, y los informes que generan no se pueden personalizar al nivel que necesito para mi tipo de pruebas. Mi herramienta preferida para redactar y entregar estos informes era Excel, y ¿por qué no? Venía de una larga experiencia con Excel y creía que podía conseguir que hiciera cualquier cosa.
Excel, tenemos un problema...
No entraré en el tedioso proceso de pasar datos de nuestras herramientas internas a Excel, pero me llevaba horas y soy muy diestro con Excel. Había que copiar datos, limpiarlos, ordenarlos y filtrarlos. Diera como diera las fórmulas, casi siempre había que retocarlas; luego había que ajustar tablas y gráficos según el número de pruebas, nodos y distintos tiempos de respuesta registrados durante el proyecto. Después venía el festival de buscar y reemplazar para que los nombres de las pruebas quedaran bien, asegurarme de que los formatos numéricos y los resaltados fueran correctos en todas las filas de todas las hojas y, en general, limpiar datos. Probé con plantillas, macros y scripts en VBA. Nada era lo bastante flexible como para manejar un número variable de pruebas, dispositivos y métricas de rendimiento sin una limpieza seria en cada caso. Conseguí una plantilla que aceleraba algo el proceso, pero aún así me llevaba medio día dejarlo todo fino.
Ahora me río al recordar algunas de las fórmulas y scripts de VBA absurdamente complejos que incluí en esa plantilla. Todo empeoró cuando actualizamos la versión de Java y nuestras herramientas internas dejaron de procesar bien los logs. Pasamos a una herramienta externa llamada GC Easy que parsea muy bien los logs a un archivo JSON que se puede cargar en Excel. Esos JSON se pueden cargar con PowerQuery, pero es un proceso lento y pesado; y cuando intentas parsear más de 50 archivos JSON con más de un millón de filas cada uno, rompes Excel con bastante frecuencia. Súmale que necesitaba automatizar el proceso y no encontraba una forma decente de hacerlo. Incluso cuando conseguía cargar los datos en Excel, las visualizaciones (gráficos de áreas apiladas del tamaño del heap en el tiempo) se congelaban y provocaban más bloqueos. Y aunque resolviera eso, los JSON eran solo el comienzo.
Copiar, limpiar y analizar datos a mano siempre acaba en errores. Somos humanos: se nos pasan cosas, nos distraemos, hacemos clic donde no es. Mi proceso no funcionaba y nunca funcionaría para todo el equipo salvo que todos pasaran por una formación intensiva en Excel. Quizá sea por mi pasado militar, quizá por mi forma de ser, pero siempre busco mejorar procesos y ganar eficiencia, así que me puse a pensar cómo simplificarlo todo.
Nuestra herramienta interna, por entonces, no tenía API ni una exportación que funcionara. No podía extraer datos directamente sin copiar y pegar. La siguiente idea fue ir a la fuente: los archivos de log de cada dispositivo de la prueba. Eso implicaba recorrer un directorio de prueba con un montón de subcarpetas, extraer datos de múltiples tipos de archivo en distintos formatos, hacer todos los cálculos y escribirlo en Excel con el formato que necesitaba, con visualizaciones y formato condicional incluidos.
Los tipos de archivo que tenía que parsear eran .txt, .csv, .json (con estructuras diversas), .xml (con estructuras diversas), .dat, .html, y además había datos que sacar de una base de datos Oracle. Tenía que contemplar estructuras de carpetas variables, números y estructuras de archivo cambiantes, .xml sin etiquetas de cierre y tamaños por encima de 10 GB. El proceso, además, debía ser fácil de seguir, lo más automatizado posible y capaz de manejar millones de filas. Excel no iba a cubrir todo eso, así que probé con algunas herramientas de BI conocidas: Power BI y Tableau. No quiero convertir esto en una comparativa de BI, así que diré simplemente que, en mi caso, no cumplieron.
Entonces, ¿qué herramienta hace todo lo que hace Excel, puede escribir informes en Excel y, además, cumple todos estos requisitos?
Te presento a mis nuevos amigos: Python y pandas
Si te manejas con Excel, creo que te va a encantar pandas para Python. pandas reúne la funcionalidad de Excel con la potencia del lenguaje Python. Las columnas de Excel se convierten en Series de pandas, las tablas en DataFrames y las fórmulas complejas en funciones de Python. pandas puede hacer todo lo que hace Excel:
Lectura de datos
Excel se defiende bastante bien leyendo archivos planos y, con PowerQuery, tiene cierta capacidad para consultar bases de datos y leer algunos .xml y .json. Si lees la documentación parece la herramienta perfecta para leer casi cualquier cosa, pero en la práctica no es así. La interfaz es torpe y requiere mucha curva de aprendizaje, no hay una forma buena de automatizar el proceso y la limpieza de datos es casi tan divertida como ir al dentista. pandas, en cambio, hace que leer datos sea pan comido.
Esta librería de manipulación de datos en Python puede leer de forma nativa datos de un montón de fuentes. Echa un vistazo a la página de documentación para ver la lista completa. Y lo mejor: si pandas no lo lee de forma nativa, es muy probable que exista un paquete de Python que sí lo haga o que puedas escribir unas pocas líneas de código para resolverlo. En DataCamp tienes la serie Importing Data in Python, entre otros cursos, para profundizar en este tema.
Con pandas pude leer todos los tipos de archivo que mencioné antes, incluidos esos .xml sin etiquetas de cierre. Con un poco de procesamiento de lenguaje natural también saco los metadatos de las pruebas de los .txt en bruto, algo que no podía hacer en Excel porque no están formateados como datos. La versión del script que uso ahora no solo lee todos los tipos de archivo que necesitaba; con unas pocas líneas extra de Python también consigo automáticamente los logs de recolección de basura de Java en .json desde GC Easy consumiendo su API; algo imposible con Excel. ¿Cómo se compara con Excel? Con pandas puedo recorrer todo un directorio de pruebas y devolver todos los datos en DataFrames limpios y ordenados en un par de minutos. Incluso maneja sin despeinarse esos logs que pasan de 10 GB. Puede que no te parezca un tamaño enorme, pero intenta leer algo así con Excel.
Incluso he empezado a pasar de los archivos planos a extraer datos de una base de datos Oracle de varios terabytes. No puedo compararlo con Excel; salvo un experimento puntual, he evitado las consultas a BD en Excel. Por lo que recuerdo, no es una experiencia que quiera repetir. Conectar desde Python, en cambio, es sencillísimo con SQLAlchemy. La conexión es simple y la velocidad de consulta deja a Excel a años luz. Mira Introduction to Relational Databases in Python para una buena introducción.
Limpieza de datos
Visualizaciones aparte, creo firmemente que los datos deben estar en tablas limpias, ordenadas, formateadas y bien etiquetadas. pandas tiene opciones estupendas para limpiar datos y presentar tablas con formato. La página de estilos del sitio oficial de pandas es mi guía favorita para dar formato a DataFrames. Con unas pocas líneas puedes fijar decimales, formatear números, añadir formato por filas y condicional, y el proceso se repite igual en tantos documentos como necesites. Poder automatizar partes de la limpieza, sea cual sea el tamaño, me ha ahorrado infinidad de horas.
Siendo justos, en Excel también puedes limpiar datos. Tiene muchas herramientas integradas para ello, y era exactamente lo que hacía cada vez que (por fin) lograba cargar todos los datos. Pero es un proceso en gran parte manual, con demasiados clics y, otra vez, muy propenso a errores y consumiendo mucho tiempo. Aunque domines todas las herramientas de Excel, los fallos son inevitables. Hay formas de acelerarlo con fórmulas, macros y scripts en VBA (ahora voy a eso), pero dista mucho de ser una solución perfecta.
Análisis de datos
Seré directo: para analizar datos, pandas gana de calle a Excel. No hay comparación justa. En Excel puedes escribir fórmulas y usar funciones integradas; lo hice durante años y me apañé. Di clases de análisis de datos en Excel, lo defendí más de una vez y cursé estadística usando Excel como herramienta principal. No digo que no sirva para analizar. Pero compararlo con pandas es como comparar papel y lápiz con una calculadora; juegan en ligas distintas.
En agosto del año pasado, Kaggle informó de que Python había superado a R en Data Science y, en diciembre, Quartz llamó a pandas la «herramienta más importante en Data Science». Poco más que añadir.
Macros y VBA
En 2012, aún en servicio activo, fui a una revisión rutinaria al dentista en Fort Bliss (El Paso, Texas). Al sentarme me dijo que había que quitar las muelas del juicio y, como tenía hueco, me lo hizo en el momento solo con anestesia local. Fue una experiencia horrible y estuve con dolor varios días. Si tuviera que elegir entre repetir aquello o trabajar con macros de Excel o Visual Basic, creo que preferiría que me sacaran más muelas.
Hay mucha gente muy buena en esas áreas y he visto cosas increíbles con scripts ingeniosos en VBA; chapó por ellos. Yo, claramente, no soy fan y no se lo recomiendo a nadie, sobre todo pudiendo usar Python. Es una alternativa a VBA tan popular que Microsoft se plantea incorporar Python como lenguaje oficial de scripting en Excel.
Pero aún tengo que usar Excel
Por mucho que me gustaría hacerlo todo en Python, en mi caso no es realista. Por ahora, los resultados del análisis tengo que entregarlos en Excel. Por suerte, hay paquetes como OpenPyXL y XlsxWriter hechos justo para eso. Uso XlsxWriter a diario para convertir DataFrames en informes muy profesionales en Excel, sin fórmulas, macros ni scripts de VBA. Incluso hace visualizaciones.
Hablando de visualizar datos...
Adiós a los gráficos aburridos
Nunca he sido un entusiasta de la visualización. Siempre he preferido ver los datos en filas y columnas con resaltados en lo importante, quizá alguna barra por aquí y por allá. Excel cumple decentemente ofreciendo muchas opciones para visualizar, pero mi conocimiento se limitaba a gráficos de áreas apiladas, de barras y de líneas. Para cada uno, seleccionaba los datos, le daba a «Insertar gráfico» y elegía el que tocaba. Luego quizá filtraba algo y añadía un título, y poco más.
Siendo franco, Excel tiene un montón de opciones para personalizar gráficos, pero el proceso puede ser complicado y la curva de aprendizaje es pronunciada. Sigo sin disfrutar haciendo gráficos en Excel, pero con Python es otra historia.
Ahora soy un firme defensor de la visualización y dedico tiempo a crear gráficos que aporten de verdad al análisis. Al empezar a hacer cursos en DataCamp y descubrir paquetes como Matplotlib y Bokeh, entendí el poder y el propósito real de visualizar datos. Con Matplotlib, las visualizaciones se generan fácilmente desde pandas o importando pyplot. La lista de gráficos posibles en pyplot es, como poco, extensa, y casi cualquier aspecto se puede personalizar. Introduction to Data Visualization With Python muestra cómo crear visualizaciones excelentes con Matplotlib.
Por muy bien que esté Matplotlib, necesitaba algo más de interactividad: seleccionar, hacer zoom, pasar el ratón para ver detalles, ordenar y filtrar datos en el propio gráfico, y desde luego no quería aprender react.js ni D3. Aprender Python ha sido (y es) un gusto, pero JavaScript no es lo mío. Por suerte, Python vuelve al rescate con paquetes como Bokeh y Dash.
Bokeh, con el apoyo de Anaconda, incorpora D3.js y Python para crear gráficos totalmente interactivos que puedes exportar como documentos HTML independientes, incrustar en páginas web o ejecutar en un servidor de Bokeh. Aunque la curva de aprendizaje es más pronunciada que con Matplotlib, creo que coincidirás en que las visualizaciones merecen la pena; echa un vistazo a la galería y juzga tú mismo. Aún estoy lejos del nivel que quiero alcanzar con Bokeh, pero se convirtió en uno de mis paquetes favoritos desde la primera lección de Interactive Data Visualization With Bokeh. Si Bokeh no encaja con lo que necesitas, tienes Dash de la gente de Plotly.
Dash es otra herramienta gratuita y de código abierto para visualización en Python que usa React.js en lugar de D3. La descubrí hace poco y también genera visualizaciones interactivas fantásticas sin escribir ni una línea de JavaScript. Es difícil comparar Dash con Bokeh porque ambas producen grandes resultados. Mi único «pero» con Dash por ahora es que solo he conseguido ejecutar las visualizaciones en un servidor Dash. No he logrado incrustarlas en documentos independientes, páginas HTML ni cuadernos Jupyter. Aun así, promete mucho, como se ve en su galería.
Ahorra tiempo con programación y automatización de tareas
En el verano de 2014 teníamos un problema en la organización donde trabajaba. La Reserva del Ejército se rige por una serie de indicadores llamados «Readiness». Es el porcentaje de personal asignado a una unidad que podría ser movilizado. Ese número se calcula con varias categorías de datos: médico, dental, formación, condición física y un buen puñado más. No puedo dar el número, pero el nuestro estaba muy por debajo de lo necesario.
Mi solución fue un informe en Excel de varias páginas con tablas y gráficos compilados desde distintas bases de datos de personal. Incluía datos agregados de la organización, información de cada persona en todas las categorías de medición y proyecciones de dónde estaría el indicador en los próximos tres meses en función de las acciones en curso esa semana. Fue un éxito y nuestro «Readiness» subió de forma notable y se mantuvo así mucho tiempo.
Funcionó, sí, pero fue un caos. Llegaba temprano cada lunes, extraía a mano la información de las distintas fuentes, luego limpiaba los datos y los pegaba en el informe de esa semana. Después pasaba al menos una hora revisando errores y limpiando más. Luego había que enviarlo por correo al personal administrativo para que verificara los datos y añadiera actualizaciones que aún no estaban en las bases. De media, tardábamos tres días en mandar el informe. El coste en horas de trabajo era excesivo e innecesario.
Con un poco de scripting en Python podría haber extraído automáticamente todos los datos de las distintas fuentes, compilado todo en DataFrames de pandas, escrito el informe completo en Excel y enviado el correo al equipo administrativo, también de forma automática. Lo mejor: se podría haber programado para que corriera a las 00:01 del lunes y habría ahorrado incontables horas. En sistemas Linux es facilísimo programar scripts de Python con crontab. En mi trabajo actual tratamos con tantos sistemas que crontab es esencial y programar tareas allí es una habilidad clave. Si tienes que extraer datos cada medianoche y cargarlos en un informe, en Linux crontab es el camino.
Soy fan de Linux, pero la mayor parte de mi trabajo la hago en Windows, que además es mi sistema preferido. Ahí tienes varias opciones para programar tareas con el Programador de tareas de Windows, Schtask.exe o PowerShell. Con cualquiera de ellas puedes lanzar tus scripts en las horas de menos carga, cuando hay más recursos libres, o justo después de que sepas que una fuente de datos se ha actualizado. Ojalá hubiera tenido esta opción hace un par de años.
Mi herramienta favorita: Jupyter Notebooks
Después de tanto tiempo con Excel, aprender Python fue como haber vivido solo con unos alicates y, de pronto, descubrir una caja de herramientas completa y reluciente. Todas son útiles, pero si tuviera que quedarme con una, sería Jupyter Notebooks. Todo lo que he escrito en Python lo he hecho en un cuaderno de Jupyter, ya sea para consumo directo o para convertirlo después en un script independiente. Jupyter es fantástico para escribir funciones, probar código, hacer análisis exploratorio e incluso presentar el resultado final. También sirve para escribir posts (como este) o publicar en GitHub Pages. Si te das una vuelta por los tutoriales de DataCamp verás que muchos se han creado así.
¿Y por qué mencionarlo al hablar de Excel?
Porque Jupyter Notebooks puede sustituir a Excel como producto final del análisis. Puedes escribir el código, mostrar DataFrames de pandas, visualizaciones y conclusiones en una sola página que se convierte a HTML con un clic. Uno de mis objetivos personales es dejar Excel por completo y usar Jupyter Notebooks para enviar informes terminados. Esto es aún más viable ahora que se ha lanzado JupyterLab. Entre eso y los widgets puedes convertir un cuaderno en una aplicación interactiva de análisis de datos.
Reflexiones finales
Sigo usando Excel a diario; es una gran herramienta y está arraigada en muchas organizaciones, incluida la mía. Casi todo el mundo la conoce y puede usarla para consumir información y hacer análisis sencillos. Lo que mucha gente no sabe es que hacer ese mismo análisis con pandas y Python no solo es más eficiente: es más fácil.
Mi objetivo es dejar Excel por completo antes de fin de año, y podré hacerlo con las herramientas que he mencionado. No te convertirás en experto de la noche a la mañana, pero con Excel pasa lo mismo. Espero que esto te anime a dar el paso y probar algunas de estas herramientas. Ganarás eficiencia, productividad y, en definitiva, acelerarás tu flujo de trabajo.
