Comment utiliser SQL dans pandas avec des requêtes pandasql

Pour constater la lisibilité de SQL, imaginons une table (un dataframe) appelée penguins contenant diverses informations sur des manchots (nous utiliserons effectivement cette table plus loin). Pour extraire toutes les espèces uniques de manchots mâles dont les nageoires dépassent 210 mm, voici le code nécessaire en pandas :

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

Pour obtenir la même information en SQL, nous exécuterions :

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

Ce second extrait, en SQL, ressemble presque à une phrase en anglais naturel et est donc plus intuitif. On peut encore améliorer la lisibilité en l’écrivant sur plusieurs lignes :

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

Maintenant que nous avons identifié les atouts de SQL avec pandas, voyons comment les combiner techniquement.

Comment utiliser pandasql

La bibliothèque Python pandasql permet d’interroger des dataframes pandas en exécutant des commandes SQL sans se connecter à un serveur SQL. En coulisses, elle utilise la syntaxe SQLite, détecte automatiquement les dataframes pandas et les traite comme des tables SQL classiques.

Préparer votre environnement

Commençons par installer pandasql :

pip install pandasql

Puis importons les packages requis :

from pandasql import sqldf
import pandas as pd

Ci‑dessus, nous importons directement la fonction sqldf() depuis pandasql, qui est quasiment la seule fonction réellement utile de la bibliothèque. Comme son nom l’indique, elle sert à interroger des dataframes avec une syntaxe SQL. En complément, pandasql fournit deux jeux de données intégrés, chargeables avec les fonctions explicites load_births() et load_meat().

Syntaxe de pandasql

La syntaxe de la fonction sqldf() est très simple :

sqldf(query, env=None)

Ici, query est un paramètre obligatoire qui reçoit une requête SQL sous forme de chaîne de caractères, et env est un paramètre optionnel (rarement nécessaire), pouvant être locals() ou globals(), qui permet à sqldf() d’accéder à l’ensemble de variables correspondant dans votre environnement Python.

La fonction sqldf() renvoie le résultat de la requête sous forme de dataframe pandas.

Quand utiliser pandasql

La bibliothèque pandasql permet de travailler avec les données via le Data Query Language (DQL), l’un des sous‑ensembles de SQL. Autrement dit, avec pandasql, on exécute des requêtes sur les données pour en extraire l’information nécessaire. En particulier, on peut accéder, extraire, filtrer, trier, regrouper, joindre, agréger les données, et effectuer des opérations mathématiques ou logiques.

Quand ne pas utiliser pandasql

pandasql ne prend pas en charge les autres sous‑ensembles de SQL en dehors du DQL. Cela signifie qu’on ne peut pas l’utiliser pour modifier des tables (update, truncate, insert, etc.) ni pour changer les données d’une table (update, delete ou insert).

De plus, comme cette bibliothèque s’appuie sur la syntaxe SQL, il faut garder à l’esprit certaines spécificités de SQLite bien connues.

Exemples d’utilisation de pandasql

Examinons maintenant plus en détail comment exécuter des requêtes SQL sur des dataframes pandas avec la fonction sqldf() de pandasql. Pour disposer de données d’exemple, chargeons l’un des jeux de données intégrés à la bibliothèque seaborn : penguins :

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

Sortie :

   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 vous souhaitez réviser vos bases SQL, notre parcours de compétences SQL Fundamentals est une excellente référence.

Extraire des données avec pandasql

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

Sortie :

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

Ici, nous avons extrait l’espèce et l’île des cinq premiers manchots du dataframe penguins. Notez que l’appel à sqldf() renvoie un dataframe pandas :

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

Sortie :

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

En pur pandas, cela donnerait :

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

Sortie :

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

Autre exemple : extraire les valeurs uniques d’une colonne :

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

Sortie :

     species
0     Adelie
1  Chinstrap
2     Gentoo

En pandas, ce serait :

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

Sortie :

['Adelie' 'Chinstrap' 'Gentoo']

Trier des données avec pandasql

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

Sortie :

   body_mass_g
0       6300.0
1       6050.0
2       6000.0
3       6000.0
4       5950.0

Ici, nous avons trié les manchots par masse corporelle décroissante et affiché les cinq plus fortes valeurs.

En pandas, cela donnerait :

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

Sortie :

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

Filtrer des données avec pandasql

Reprenons l’exemple évoqué plus haut (Pourquoi utiliser SQL dans pandas) : extraire les espèces uniques de manchots mâles dont les nageoires dépassent 210 mm :

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

Sortie :

     species
0  Chinstrap
1     Gentoo

Ici, nous avons filtré selon deux conditions : sex = 'Male' et flipper_length_mm > 210.

L’équivalent en pandas est un peu plus chargé :

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

Sortie :

['Chinstrap' 'Gentoo']

Regrouper et agréger des données avec pandasql

Appliquons maintenant un regroupement et une agrégation pour trouver la longueur de bec maximale par espèce dans le dataframe :

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

Sortie :

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

Le même code en pandas :

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

Sortie :

     species  bill_length_mm
0     Adelie            46.0
1  Chinstrap            58.0
2     Gentoo            59.6

Effectuer des opérations mathématiques avec pandasql

Avec pandasql, il est facile d’effectuer des opérations mathématiques ou logiques sur les données. Imaginons que nous voulions calculer le ratio longueur/profondeur du bec pour chaque manchot et afficher les cinq plus grandes valeurs :

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

Sortie :

   length_to_depth
0         3.612676
1         3.510490
2         3.505882
3         3.492424
4         3.458599

Notez que nous avons utilisé l’alias length_to_depth pour la colonne du ratio. Sans cela, nous obtiendrions un nom de colonne peu pratique : bill_length_mm / bill_depth_mm.

En pandas, il faudrait d’abord créer une nouvelle colonne pour ce 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())

Sortie :

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

Conclusion

Pour conclure, nous avons vu pourquoi et quand il peut être utile de combiner SQL et pandas pour écrire un code plus clair et plus efficace. Nous avons expliqué comment configurer et utiliser la bibliothèque pandasql à cette fin ainsi que ses limites. Enfin, nous avons parcouru de nombreux exemples pratiques d’utilisation de pandasql et comparé chaque fois le code avec son équivalent en pandas.

Vous avez désormais tout le nécessaire pour appliquer SQL avec pandas dans des projets concrets. Un excellent terrain d’entraînement est DataLab, le notebook de données de DataCamp dopé à l’IA, avec un excellent support SQL.