Enlace a Perspicacias Comparando consultas SQL y consultas Python utilizando Analítica de Librería

Comparing SQL and Python queries using Library Analytics, link

¿Qué enfoque es mejor para su análisis exploratorio de datos?

Foto de Ayman Yusuf en Unsplash

SQL es la base fundamental de cualquier caja de herramientas de un científico de datos: la capacidad de extraer rápidamente datos de una fuente para su análisis es una habilidad esencial para cualquier persona que trabaje con grandes cantidades de datos. En esta publicación, quería dar algunos ejemplos de algunas consultas básicas que suelo usar en SQL durante el proceso de análisis exploratorio de datos. Compararé estas consultas con scripts similares en Python que producen el mismo resultado, como una comparación entre los dos enfoques.

Para este análisis, utilizaré algunos datos sintéticos sobre los libros mejor valorados del año pasado de una cadena hipotética de librerías (la Librería de Ficción Total). Aquí se puede encontrar un enlace a la carpeta de GitHub de este proyecto, donde se detallan los pasos para ejecutar el análisis.

Foto de Eugenio Mazzone en Unsplash

Como nota al margen, aunque me centro principalmente en las consultas SQL en este artículo, vale la pena señalar que estas consultas se pueden integrar perfectamente con Python utilizando la biblioteca pandaSQL (como he hecho para este proyecto). Esto se puede ver en detalle en el cuaderno Jupyter del enlace de GitHub de este proyecto, pero la estructura de esta consulta generalmente es la siguiente:

query = """SELECT * FROM DATA"""output = sqldf(query,locals())output

PandaSQL es una biblioteca muy práctica para aquellos que tienen más familiaridad con las consultas SQL que con la manipulación típica de conjuntos de datos de Pandas, y a menudo es mucho más fácil de leer, como mostraré aquí.

El conjunto de datos

A continuación se muestra un fragmento del conjunto de datos: hay columnas para el título del libro y el año en que se publicó, el número de páginas, los géneros, la calificación promedio del libro, el autor, el número de unidades vendidas y los ingresos del libro.

Datos sintéticos a analizar (datos por autor)

Análisis de ingresos por década

Supongamos que quiero saber en qué década se han publicado los libros más rentables para la librería. El conjunto de datos original no tiene una columna que indique en qué década se publicaron los libros, pero esto es relativamente sencillo de agregar a los datos. Ejecuto una subconsulta para dividir el año usando la división entera y multiplicarlo por 10 para obtener los datos de la década, luego agrego y obtengo el promedio de los ingresos por década. Luego ordeno los resultados por ingresos totales para obtener las décadas más rentables de libros publicados en la librería.

WITH bookshop AS(SELECT TITLE, YEARPUBLISHED,(YEARPUBLISHED/10) * 10 AS DECADE,NUMPAGES, GENRES, RATING, AUTHOR, UNITSSOLD,REVENUEfrom df)SELECT DECADE, SUM(REVENUE) AS TOTAL_REVENUE,ROUND(AVG(REVENUE),0) AS AVG_REVENUEFROM bookshopGROUP BY DECADEORDER BY TOTAL_REVENUE DESC

En comparación, una salida equivalente en Python se vería algo así como el fragmento de código a continuación. Aplico una función lambda que realiza la división entera y muestra la década, y a partir de ahí agrego los ingresos por década y ordeno el resultado por ingresos totales.

# creando df bookshopbookshop = df.copy()bookshop['Decade'] = (bookshop['YearPublished'] // 10) * 10# agrupar por década, agregar ingresos por suma y mediareturn = bookshop.groupby('DECADE') \                 .agg({'Revenue': ['sum', 'mean']}) \                 .reset_index()result.columns = ['Decade', 'Total_Revenue', 'Avg_Revenue']# ordenar por decadereturn = result.sort_values('Total_Revenue')

Tenga en cuenta el mayor número de pasos separados que hay en el script de Python para lograr el mismo resultado: las funciones son incómodas y difíciles de entender a primera vista. En comparación, el script SQL es mucho más claro en su presentación y mucho más fácil de leer.

Ahora puedo tomar esta consulta y visualizarla para tener una idea de las tendencias de ingresos de los libros a lo largo de las décadas, configurando un gráfico de matplotlib utilizando el siguiente script: los gráficos de barras muestran los ingresos totales por década, con un gráfico de dispersión en el eje secundario para mostrar los ingresos promedio de los libros.

# Creando el eje y primario (ingreso total)
fig, ax1 = plt.subplots(figsize=(15, 9))
ax1.bar(agg_decade['DECADE'], agg_decade['TOTAL_REVENUE'],
        width=0.4, align='center', label='Ingreso Total (Dólares)')
ax1.set_xlabel('Década')
ax1.set_ylabel('Ingreso Total (Dólares)', color='blue')
# Ajustando las líneas de la cuadrícula en el eje y primario
ax1.grid(color='blue', linestyle='--', linewidth=0.5, alpha=0.5)
# Creando el eje y secundario (ingreso promedio)
ax2 = ax1.twinx()
ax2.scatter(agg_decade['DECADE'], agg_decade['AVG_REVENUE'],
            marker='o', color='red', label='Ingreso Promedio (Dólares)')
ax2.set_ylabel('Ingreso Promedio (Dólares)', color='red')
# Ajustando las líneas de la cuadrícula en el eje y secundario
ax2.grid(color='red', linestyle='--', linewidth=0.5, alpha=0.5)
# Estableciendo los mismos límites en el eje y para ax1 y ax2
ax1.set_ylim(0, 1.1 * max(agg_decade['TOTAL_REVENUE']))
ax2.set_ylim(0, 1.1 * max(agg_decade['AVG_REVENUE']))
# Combinando las leyendas para ambos ejes
lines, labels = ax1.get_legend_handles_labels()
lines2, labels2 = ax2.get_legend_handles_labels()
ax2.legend(lines + lines2, labels + labels2, loc='upper left')
# Estableciendo el título
plt.title('Ingreso Total y Promedio por Década')
# Mostrando el gráfico
plt.show()

La visualización se puede ver a continuación: los libros publicados en la década de 1960 son aparentemente los más rentables para la librería, generando más de $192,000 en ingresos para Total Fiction Bookstore. En comparación, los libros de la lista de la década de 1900 son más rentables en promedio, aunque no se vendieron tan bien como los libros de la década de 1960.

Ingreso total y promedio por década (imagen del autor)

Los ingresos promedio de los libros siguen una tendencia similar a los ingresos totales en todas las décadas de libros publicados, con la excepción de los libros de la década de 1900 y 1980, que son más rentables en promedio pero no en general.

Análisis del autor

Ahora, supongamos que quiero obtener datos sobre los 10 mejores autores de la lista, ordenados por sus ingresos generados totales. Para esta consulta, quiero saber la cantidad de libros que han hecho que aparecen en la lista, los ingresos totales que han generado en esos libros, sus ingresos promedio por libro y la calificación promedio de esos libros en la librería. Una pregunta bastante simple de responder usando SQL: puedo usar una declaración count para obtener el número total de libros que han hecho, y declaraciones avg para obtener el ingreso promedio y la calificación promedio por autor. Después de eso, puedo agrupar estas declaraciones por director.

SELECT AUTHOR,COUNT(TITLE) AS NUM_BOOKS,SUM(REVENUE) AS TOTAL_REVENUE,ROUND(AVG(REVENUE),0) AS AVG_REVENUE,ROUND(AVG(RATING),2) AS AVG_RATING_PER_BOOKFROM bookshopGROUP BY AUTHORORDER BY TOTAL_REVENUE DESCLIMIT 10

Un script equivalente en Python se vería así: aproximadamente la misma longitud, pero mucho más complejo para la misma salida. Agrupo los valores por autor antes de especificar cómo agregar cada columna en la función agg, luego ordeno los valores por ingreso total. Nuevamente, el script SQL es mucho más claro en comparación.

result = bookshop.groupby('Author') \ .agg({ 'Title': 'count', 'Revenue': ['sum', 'mean'], 'Rating': 'mean' }) \ .reset_index()result.columns = ['Author', 'Num_Books', 'Total_Revenue', 'Avg_Revenue', 'Avg_Rating_per_Book']# Ordenando por ingreso totalresult = result.sort_values('Total_Revenue', ascending=False)# Top 10result_top10 = result.head(10)

El resultado de esta consulta se puede ver a continuación: Ava Mitchell lidera el campo, con una facturación total de más de $152,000 por sus ventas de libros. Emma Hayes ocupa el segundo lugar con más de $85,000, seguida de cerca por Liam Parker con más de $83,000.

Resultado de la consulta de autores de libros

Visualizando esto en matplotlib usando el siguiente script, podemos generar gráficos de barras de la facturación total con puntos de datos que muestran la facturación promedio por autor. La calificación promedio por autor también se traza en un eje secundario.

# Creando figura y ejefig1, ax1 = plt.subplots(figsize=(15, 9))# trazando gráfico de barras de facturación totalax1.bar(agg_author['Author'], agg_author['TOTAL_REVENUE'],         width=0.4, align='center', color='silver', label='Facturación Total (Dólares)')ax1.set_xlabel('Autor')ax1.set_xticklabels(agg_author['Author'], rotation=-45, ha='left')ax1.set_ylabel('Facturación Total (Dólares)', color='blue')# Ajustando las líneas de la cuadrícula en el eje y primarioax1.grid(color='blue', linestyle='--', linewidth=0.5, alpha=0.5)# creando gráfico de dispersión de facturación promedioax1.scatter(agg_author['Author'], agg_author['AVG_REVENUE'],          marker="D", color='blue', label='Facturación Promedio por Libro (Dólares)')# Creando gráfico de dispersión de calificación promedio en el eje secundarioax2 = ax1.twinx()ax2.scatter(agg_author['Author'], agg_author['AVG_RATING_PER_BOOK'],          marker='^', color='red', label='Calificación Promedio por Libro')ax2.set_ylabel('Calificación Promedio', color='red')# Ajustando las líneas de la cuadrícula en el eje y secundarioax2.grid(color='red', linestyle='--', linewidth=0.5, alpha=0.5)# Combinando leyendas para ambos ejeslines, labels = ax1.get_legend_handles_labels()lines2, labels2 = ax2.get_legend_handles_labels()ax1.legend(lines + lines2, labels + labels2, loc='upper right')# Estableciendo el títuloplt.title('Top 10 Autores por Facturación, Calificación')# Mostrando el gráficoplt.show()

Ejecutando esto, obtenemos el siguiente gráfico:

Top 10 autores por facturación y calificación (imagen del autor)

Este gráfico apunta a una afirmación bastante clara: la facturación no se correlaciona con la calificación promedio para cada autor. Ava Mitchell tiene la facturación más alta pero se encuentra en la mediana en términos de calificación para los autores mencionados anteriormente. Olivia Hudson tiene la calificación más alta mientras ocupa el octavo lugar en votos totales; no hay una tendencia observable entre la facturación de un autor y su popularidad.

Comparando la Longitud del Libro vs Facturación

Finalmente, supongamos que quiero mostrar cómo varía la facturación del libro en función de la longitud del libro. Para responder a esta pregunta, primero quiero dividir los libros en 4 categorías basadas en los cuartiles de longitud del libro, lo que dará una mejor idea de las tendencias generales de facturación vs longitud del libro.

En primer lugar, defino los cuartiles en SQL, utilizando una subconsulta para generar estos valores, antes de clasificar los libros en estos grupos utilizando una declaración de caso cuando.

WITH PERCENTILES AS (    SELECT         PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY NUMPAGES)         AS PERCENTILE_25,        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY NUMPAGES)         AS MEDIAN,        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY NUMPAGES)         AS PERCENTILE_75    FROM bookshop)SELECT     TITLE, TITLE, REVENUE, NUMPAGES,    CASE        WHEN NUMPAGES< (SELECT PERCENTILE_25 FROM PERCENTILES)         THEN 'Cuartil 1'        WHEN NUMPAGES BETWEEN (SELECT PERCENTILE_25 FROM PERCENTILES)         AND (SELECT MEDIAN FROM PERCENTILES) THEN 'Cuartil 2'        WHEN NUMPAGES BETWEEN (SELECT MEDIAN FROM PERCENTILES)         AND (SELECT PERCENTILE_75 FROM PERCENTILES) THEN 'Cuartil 3'        WHEN NUMPAGES > (SELECT PERCENTILE_75 FROM PERCENTILES)         THEN 'Cuartil 4'    END AS CUARTIL_LONGITUD_LIBROFROM bookshopORDER BY REVENUE DESC

Alternativamente (para dialectos de SQL que no admiten funciones de percentil, como SQLite), los cuartiles se pueden calcular por separado antes de ingresarlos manualmente en la declaración case when.

--Para el dialecto de SQLiteSELECT TITULO, INGRESOS, NUMPAGINAS,CASEWHEN NUMPAGINAS < 318 THEN 'Cuartil 1'WHEN NUMPAGINAS BETWEEN 318 AND 375 THEN 'Cuartil 2'WHEN NUMPAGINAS BETWEEN 375 AND 438 THEN 'Cuartil 3'WHEN NUMPAGINAS > 438 THEN 'Cuartil 4'END AS CUARTIL_LONGITUD_PAGINAFROM libreriaORDENAR POR INGRESOS DESC

Ejecutando esta misma consulta en Python, defino los percentiles usando numpy antes de usar la función cut para clasificar los libros en sus grupos, luego ordeno los valores por la longitud del libro en páginas. Como antes, este proceso es notablemente más complejo que el script equivalente en SQL.

# Definir los percentiles usando numpypercentiles = np.percentile(libreria['NumPaginas'], [25, 50, 75])# Definir los límites de los grupos usando los percentiles calculadosbin_edges = [-float('inf'), *percentiles, float('inf')]# Definir las etiquetas para los gruposbucket_labels = ['Cuartil 1', 'Cuartil 2', 'Cuartil 3', 'Cuartil 4']# Crear la columna 'GRUPO_TIEMPO_EJECUCION' basada en los límites y etiquetas de los gruposlibreria['GRUPO_TIEMPO_EJECUCION'] = pd.cut(libreria['NumPaginas'], bins=bin_edges,                                 labels=bucket_labels)resultado = libreria[['Titulo', 'Ingresos',                'NumPaginas', 'CUARTIL_LONGITUD_PAGINA']].sort_values(by='NumPaginas',                                                           ascending=False)

La salida de esta consulta se puede visualizar como diagramas de caja utilizando seaborn, se puede ver un fragmento del script utilizado para generar los diagramas de caja a continuación. Tenga en cuenta que los grupos de tiempo de ejecución se ordenaron manualmente en el orden correcto para que se presenten correctamente.

# Establecer el estilo para los gráficos de cuadrícula blancasns.set(style="whitegrid")# Establecer el orden de los grupos de ingresospagelength_bucket_order = ['Cuartil 1', 'Cuartil 2',                         'Cuartil 3', 'Cuartil 4']# Crear el diagrama de cajaplt.figure(figsize=(16, 10))sns.boxplot(x='CUARTIL_LONGITUD_PAGINA', y='Ingresos',             data=pagelength_output, order = pagelength_bucket_order,             showfliers=True)# Agregar etiquetas y títuloplt.xlabel('Cuartil de Longitud de Página')plt.ylabel('Ingresos (Dólares)')plt.title('Diagrama de Caja de Ingresos por Grupo de Longitud de Página')# Mostrar el gráficoplt.show()

Los diagramas de caja se pueden ver a continuación: observe que los ingresos medios para cada cuartil de longitud de libro tienden a aumentar a medida que los libros son más largos. Esto sugiere que los libros más largos son más rentables en la librería.

Diagrama de caja de ingresos por cuartil de longitud de libro (imagen del autor)

Además, el rango del cuartil 4 es mucho más amplio en comparación con los otros cuartiles, lo que indica que hay más variación en el punto de precio para los libros más grandes.

Reflexiones Finales y Aplicaciones Adicionales

En conclusión, el uso de SQL para consultas de análisis de datos suele ser mucho más sencillo que el uso de operaciones equivalentes en Python. El lenguaje es más fácil de escribir que las consultas en Python, pero tiene la capacidad de producir resultados similares. No argumentaría que uno sea mejor que el otro, he utilizado una combinación de ambos lenguajes en este análisis. Más bien, creo que el uso de una combinación de ambos lenguajes juntos puede producir un análisis de datos más eficiente y efectivo.

Por lo tanto, dado el mayor nivel de claridad al escribir consultas de SQL en comparación con las consultas en Python, creo que es mucho más natural utilizar SQL al realizar el EDA inicial de un proyecto. SQL es mucho más fácil de leer y escribir, como he mostrado en este artículo, lo que lo hace especialmente ventajoso para estas tareas exploratorias iniciales. A menudo lo utilizo al comenzar un proyecto y recomendaría este enfoque a cualquier persona que ya tenga un buen dominio de las consultas SQL.

We will continue to update Zepes; if you have any questions or suggestions, please contact us!

Share:

Was this article helpful?

93 out of 132 found this helpful

Discover more

Inteligencia Artificial

Investigadores del MIT combinan el aprendizaje profundo y la física para corregir las imágenes de resonancia magnética afectadas por el movimiento

El desafío implica más que simplemente una imagen JPEG borrosa. Arreglar los artefactos de movimiento en la imagen mé...

Inteligencia Artificial

La nueva función de diseño de Amazon Textract introduce eficiencias en tareas de procesamiento de documentos de inteligencia artificial generales y generativos.

Amazon Textract es un servicio de aprendizaje automático (ML) que extrae automáticamente texto, escritura a mano y da...

Inteligencia Artificial

Auriculares para monitorear el cerebro y el cuerpo

Estos auriculares te escuchan, para determinar si tu cerebro y/o cuerpo están funcionando al máximo rendimiento.

Inteligencia Artificial

El Ejército de los Estados Unidos pone a prueba la Inteligencia Artificial Generativa

El Departamento de Defensa de los Estados Unidos está probando cinco modelos de lenguaje grandes como parte de un esf...