Cómo usar SQL en pandas Usando pandasql Queries

SQL, o Lenguaje de Consulta Estructurado, es un lenguaje de programación utilizado para acceder, extraer, manejar y explorar datos almacenados en bases de datos relacionales. pandas es una biblioteca de código abierto de Python diseñada específicamente para la manipulación y el análisis de datos.

En este tutorial, vamos a discutir cuándo y cómo podemos (y cuándo no podemos) utilizar la funcionalidad SQL en el marco de pandas. Además, veremos varios ejemplos de implementación de este enfoque y compararemos los resultados con el código equivalente en pandas puro.

¿Por qué usar SQL en pandas?

Dadas las definiciones de la introducción, ¿por qué querría uno utilizar SQL combinado con pandas cuando este último es un paquete todo incluido para el análisis de datos?

La respuesta es que en algunas ocasiones, especialmente para programas complejos, las consultas SQL parecen mucho más sencillas y fáciles de leer que el código correspondiente en pandas. Esto es particularmente cierto para aquellas personas que inicialmente utilizaban SQL para trabajar con datos y más tarde aprendieron pandas.

Si necesitas más formación sobre pandas, puedes consultar nuestro curso Manipulación de datos con pandas y Pandas Tutorial: DataFrames en Python.

Para ver la legibilidad de SQL en acción, supongamos que tenemos una tabla (un marco de datos) llamada penguins que contiene diversa información sobre los pingüinos (y trabajaremos con una tabla de este tipo más adelante en este tutorial). Para extraer todas las especies únicas de pingüinos que son machos y que tienen aletas de más de 210 mm, necesitaríamos el siguiente código en pandas:

penguins[(penguins['sex'] == 'Male') & (penguins['flipper_length_mm'] > 210)]['species'].unique()

En su lugar, para obtener la misma información utilizando SQL, ejecutaríamos el siguiente código:

SELECT DISTINCT species FROM penguins WHERE sex = 'Male' AND flipper_length_mm > 210

El segundo fragmento de código, escrito en SQL, parece casi una frase natural en inglés y, por tanto, es mucho más intuitivo. Podemos aumentar aún más su legibilidad repartiéndola en varias líneas:

SELECT DISTINCT species
  FROM penguins 
 WHERE sex = 'Male' 
   AND flipper_length_mm > 210

Ahora que hemos identificado las ventajas de usar SQL para pandas, veamos cómo podemos combinar técnicamente ambos.

Cómo utilizar pandasql

La librería pandasql Python permite realizar consultas a los dataframes de pandas ejecutando comandos SQL sin tener que conectarse a ningún servidor SQL. Bajo el capó, utiliza la sintaxis SQLite, detecta automáticamente cualquier dataframe pandas, y lo trata como una tabla SQL regular.

Configuración del entorno

En primer lugar, necesitamos instalar pandasql:

pip install pandasql

A continuación, importamos los paquetes necesarios:

from pandasql import sqldf
import pandas as pd

Arriba, importamos directamente la función sqldf de pandasql, que es prácticamente la única función con sentido de la biblioteca. Como su nombre indica, se aplica para consultar marcos de datos utilizando la sintaxis SQL. Aparte de esta función, pandasql viene con dos conjuntos de datos sencillos incorporados que se pueden cargar utilizando las funciones autoexplicativas load_births() y load_meat().

pandasql Syntax

La sintaxis de la función sqldf es muy sencilla:

sqldf(query, env=None)

Aquí, query es un parámetro obligatorio que toma una consulta SQL como cadena, y env-un parámetro opcional (y raramente útil) que puede ser locals() o globals() y permite a sqldf acceder al correspondiente conjunto de variables en tu entorno Python.

La función sqldf devuelve el resultado de una consulta como un marco de datos pandas.

Cuando podemos usar pandasql

La librería pandasql permite trabajar con datos utilizando el Lenguaje de Consulta de Datos (DQL), que es uno de los subconjuntos de SQL. En otras palabras, con pandasql podemos ejecutar consultas sobre los datos almacenados en una base de datos para recuperar de ella la información necesaria. En concreto, podemos acceder a los datos, extraerlos, filtrarlos, ordenarlos, agruparlos, unirlos, agregarlos y realizar operaciones matemáticas o lógicas con ellos.

Cuando no podemos utilizar pandasql

pandasql no permite emplear ningún otro subconjunto de SQL aparte de DQL. Esto significa que no podemos aplicar pandasql para modificar (actualizar, truncar, insertar, etc.) tablas o cambiar (actualizar, borrar o insertar) los datos de una tabla.

Además, dado que esta biblioteca se basa en la sintaxis SQL, debemos tener cuidado con las peculiaridades conocidas de SQLite.

Ejemplos de uso de pandasql

Ahora, vamos a echar un vistazo más granular a cómo ejecutar consultas SQL en pandas dataframes utilizando la función sqldf de pandasql. Para tener algunos datos con los que practicar, vamos a cargar uno de los conjuntos de datos incorporados en la biblioteca seaborn-penguins:

import seaborn as sns
penguins = sns.load_dataset('penguins')
print(penguins.head())

Salida:

   species island     bill_length_mm  bill_depth_mm  flipper_length_mm  \
0  Adelie  Torgersen            39.1           18.7              181.0   
1  Adelie  Torgersen            39.5           17.4              186.0   
2  Adelie  Torgersen            40.3           18.0              195.0   
3  Adelie  Torgersen             NaN            NaN                NaN   
4  Adelie  Torgersen            36.7           19.3              193.0   

   body_mass_g     sex  
0       3750.0    Male  
1       3800.0  Female  
2       3250.0  Female  
3          NaN     NaN  
4       3450.0  Female

Si necesita refrescar sus conocimientos de SQL, nuestro curso de conocimientos básicos de SQL es un buen punto de referencia.

Extracción de datos con pandasql

print(sqldf('''SELECT species, island 
FROM penguins 
LIMIT 5'''))

Salida:

  species  island
0  Adelie  Torgersen
1  Adelie  Torgersen
2  Adelie  Torgersen
3  Adelie  Torgersen
4  Adelie  Torgersen

Más arriba, hemos extraído información sobre la especie y la geografía de los cinco primeros pingüinos del marco de datos penguins. Tenga en cuenta que la ejecución de la función sqldf devuelve un marco de datos pandas:

print(type(sqldf('''SELECT species, island 
                      FROM penguins 
                     LIMIT 5''')))

Salida:

<class 'pandas.core.frame.DataFrame'>

En puros pandas, sería:

print(penguins[['species', 'island']].head())

Salida:

   species island
0  Adelie  Torgersen
1  Adelie  Torgersen
2  Adelie  Torgersen
3  Adelie  Torgersen
4  Adelie  Torgersen

Otro ejemplo es la extracción de valores únicos de una columna:

print(sqldf('''SELECT DISTINCT species 
                 FROM penguins'''))

Salida:

     species
0     Adelie
1  Chinstrap
2     Gentoo

En pandas, sería:

print(penguins['species'].unique())

Salida:

['Adelie' 'Chinstrap' 'Gentoo']

Ordenación de datos con pandasql

print(sqldf('''SELECT body_mass_g 
                 FROM penguins 
                ORDER BY body_mass_g DESC 
                LIMIT 5'''))

Salida:

   body_mass_g
0       6300.0
1       6050.0
2       6000.0
3       6000.0
4       5950.0

Arriba, ordenamos nuestros pingüinos por masa corporal en orden descendente y mostramos los cinco valores más altos de masa corporal.

En pandas, sería:

print(penguins['body_mass_g'].sort_values(ascending=False, 
ignore_index=True).head())

Salida:

0    6300.0
1    6050.0
2    6000.0
3    6000.0
4    5950.0
Name: body_mass_g, dtype: float64

Filtrado de datos con pandasql

Intentemos el mismo ejemplo que mencionamos en el capítulo Por qué usar SQL en pandas: extraer las especies únicas de pingüinos que son machos y que tienen aletas de más de 210 mm de longitud:

print(sqldf('''SELECT DISTINCT species
                 FROM penguins 
                WHERE sex = 'Male' 
                  AND flipper_length_mm > 210'''))

Salida:

     species
0  Chinstrap
1     Gentoo

Más arriba, hemos filtrado los datos en función de dos condiciones: sex = 'Male' y flipper_length_mm > 210.

El mismo código en pandas parecería un poco más abrumador:

print(penguins[(penguins['sex'] == 'Male') & (penguins['flipper_length_mm'] > 210)]['species'].unique())

Salida:

['Chinstrap' 'Gentoo']

Agrupación y agregación de datos con pandasql

Ahora, vamos a aplicar la agrupación y agregación de datos para encontrar el pico más largo de cada especie en el marco de datos:

print(sqldf('''SELECT species, MAX(bill_length_mm)
                 FROM penguins 
                GROUP BY species'''))

Salida:

     species  MAX(bill_length_mm)
0     Adelie                 46.0
1  Chinstrap                 58.0
2     Gentoo                 59.6

El mismo código en pandas:

print(penguins[['species', 'bill_length_mm']].groupby('species', as_index=False).max())

Salida:

     species  bill_length_mm
0     Adelie            46.0
1  Chinstrap            58.0
2     Gentoo            59.6

Realización de operaciones matemáticas con pandasql

Con pandasql, podemos realizar fácilmente operaciones matemáticas o lógicas sobre los datos. Imaginemos que queremos calcular la relación entre la longitud y la profundidad del pico de cada pingüino y mostrar los cinco valores más altos de esta medida:

print(sqldf('''SELECT bill_length_mm / bill_depth_mm AS length_to_depth
                 FROM penguins
                ORDER BY length_to_depth DESC
                LIMIT 5'''))

Salida:

   length_to_depth
0         3.612676
1         3.510490
2         3.505882
3         3.492424
4         3.458599

Observe que esta vez hemos utilizado el alias length_to_depth para la columna con los valores de ratio. De lo contrario, obtendríamos una columna con un nombre monstruoso bill_length_mm / bill_depth_mm.

En pandas, primero tendríamos que crear una nueva columna con los valores del ratio:

penguins['length_to_depth'] = penguins['bill_length_mm'] / penguins['bill_depth_mm']
print(penguins['length_to_depth'].sort_values(ascending=False, ignore_index=True).head())

Salida:

0    3.612676
1    3.510490
2    3.505882
3    3.492424
4    3.458599
Name: length_to_depth, dtype: float64

Conclusión

Para terminar, en este tutorial, hemos explorado por qué y cuándo podemos combinar la funcionalidad de SQL para pandas para escribir un código mejor y más eficiente. Discutimos cómo configurar y utilizar la biblioteca pandasql para este propósito y qué limitaciones tiene este paquete. Por último, consideramos numerosos ejemplos populares de la aplicación práctica de pandasql y, en cada caso, comparamos el código con su homólogo de pandas.

Ahora tienes todo lo que necesitas para aplicar SQL para pandas en proyectos del mundo real. Un lugar ideal para tu práctica es el DataLab, el cuaderno de datos de DataCamp habilitado para IA con gran compatibilidad SQL.