Comment utiliser SQL dans pandas avec des requêtes pandasql

Tutoriels

Python SQL

Voici la traduction en français :

SQL, ou Structured Query Language, est un langage de programmation utilisé pour accéder, extraire, manipuler et explorer les données stockées dans des bases de données relationnelles. pandas est une bibliothèque open source Python conçue spécifiquement pour la manipulation et l’analyse des données.

Dans ce tutoriel, nous allons discuter de quand et de comment utiliser la fonctionnalité SQL dans le cadre de pandas, ainsi que des cas où cela n’est pas possible. De plus, nous examinerons divers exemples d’implémentation de cette approche et les comparons au code équivalent en pandas pur.

Pourquoi utiliser SQL dans pandas ?

Compte tenu des définitions données dans l’introduction, pourquoi voudrait-on utiliser SQL combiné à pandas alors que ce dernier est un package tout-en-un pour l’analyse de données ?

La réponse est que dans certains cas, surtout pour des programmes complexes, les requêtes SQL paraissent beaucoup plus directes et faciles à lire que le code correspondant en pandas. Cela est particulièrement vrai pour ceux qui ont d’abord utilisé SQL pour travailler avec les données avant d’apprendre pandas.

Si vous avez besoin de plus d’entraînement sur pandas, vous pouvez consulter notre cours Manipulation des données avec pandas et le tutoriel Pandas : DataFrames en Python.

Pour voir la lisibilité du SQL en action, supposons que nous ayons une table (un dataframe) appelée penguins contenant diverses informations sur les manchots (et nous travaillerons avec cette table plus tard dans ce tutoriel). Pour extraire toutes les espèces uniques de manchots qui sont des mâles et qui ont des ailes plus longues que 210 mm, nous aurions besoin du code suivant dans pandas:

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

À la place, pour obtenir la même information en utilisant SQL, nous exécuterions le code suivant:

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

Le second morceau de code, écrit en SQL, ressemble presque à une phrase anglaise naturelle et est donc beaucoup plus intuitif. Nous pouvons encore améliorer sa lisibilité en le répartissant sur plusieurs lignes:

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

Maintenant que nous avons identifié les avantages de l’utilisation de SQL pour pandas, voyons comment les combiner techniquement.

Comment utiliser pandasql

La bibliothèque Python pandasql permet de faire des requêtes sur les dataframes pandas en exécutant des commandes SQL sans avoir à se connecter à un serveur SQL. Dans l’arrière-plan, elle utilise la syntaxe SQLite, détecte automatiquement tout dataframe pandas et le traite comme une table SQL ordinaire.

Configuration de votre environnement

Premièrement, nous devons installer pandasql:

pip install pandasql

Ensuite, nous importons les paquets nécessaires:

from pandasql import sqldf
import pandas as pd

Au-dessus, nous avons importé directement la fonction sqldf de pandasql, ce qui est pratiquement la seule fonction significative de la bibliothèque. Comme son nom l’indique, elle est appliquée pour interroger des dataframes en utilisant la syntaxe SQL. En dehors de cette fonction, pandasql est fourni avec deux jeux de données intégrés simples qui peuvent être chargés à l’aide des fonctions explicatives 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 requis qui prend en entrée une requête SQL sous forme de chaîne de caractères, et env – un paramètre facultatif (et rarement utile) qui peut être soit locals() soit globals() et permet à sqldf d’accéder au jeu de variables correspondant dans votre environnement Python.

La fonction sqldf retourne le résultat d’une requête sous forme de dataframe pandas.

Quand nous pouvons utiliser pandasql

La bibliothèque pandasql permet de travailler avec les données en utilisant le Data Query Language (DQL), qui est un des sous-ensembles de SQL. En d’autres termes, avec pandasql, nous pouvons exécuter des requêtes sur les données stockées dans une base de données pour récupérer l’information nécessaire. En particulier, nous pouvons accéder, extraire, filtrer, trier, grouper, joindre, aggreger les données, et effectuer des opérations mathématiques ou logiques sur elles.

Quand nous ne pouvons pas utiliser pandasql

pandasql ne permet pas d’utiliser les autres sous-ensembles de SQL que le DQL. Cela signifie que nous ne pouvons pas appliquer pandasql pour modifier (mettre à jour, éliminer, insérer, etc.) les tables ou pour modifier (mettre à jour, supprimer, ou insérer) les données dans une table.

De plus, comme cette bibliothèque est basée sur la syntaxe SQL, nous devrions nous méfier des coups de maintien connus de SQLite

Exemples d’utilisation de pandasql

Maintenant, nous allons effectuer une étude plus granulère sur la manière de exécuter des requêtes SQL sur des dataframes de pandas en utilisant la fonction sqldf de pandasql. Afin d’avoir des données sur lesquelles travailler, chargeons l’une des datasets intégrés de 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 devez rafraîchir vos compétences en SQL, notre skill track SQL Fundamentals est une bonne référence.

Extraction de 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

Au-dessus, nous avons extrait des informations sur les espèces et la géographie des cinq premiers pingouins du dataframe penguins. Notez que l’exécution de la fonction sqldf retourne un dataframe de pandas :

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

Sortie:

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

En pandas pur, cela serait :

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

Sortie:

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

Un autre exemple consiste à extraire les valeurs uniques d’une colonne :

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

Sortie:

     species
0     Adelie
1  Chinstrap
2     Gentoo

En pandas, cela serait :

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

Sortie:

['Adelie' 'Chinstrap' 'Gentoo']

Tri 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

Au-dessus, nous avons trié nos pingouins par la masse corporelle en ordre décroissant et affiché les cinq valeurs de masse corporelle les plus élevées.

Dans pandas, il serait :

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 les données avec pandasql

Allons essayez le même exemple que nous avons mentionné dans le chapitre Pourquoi utiliser SQL dans pandas : extraire les espèces uniques de pingouins qui sont des mâles et qui ont des ailes plus longues que 210 mm :

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

Sortie:

     species
0  Chinstrap
1     Gentoo

Au dessus, nous avons filtré les données en fonction de deux conditions : sex = 'Male' et flipper_length_mm > 210.

Le même code dans pandas serait un peu plus impressionnant :

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

Sortie:

['Chinstrap' 'Gentoo']

Grouper et aggreger les données avec pandasql

Maintenant, essayons d’appliquer le groupe de données et l’agrégation pour trouver la plus longue billes pour chaque 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 dans 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

Exécuter des opérations mathématiques avec pandasql

Avec pandasql, nous pouvons facilement effectuer des opérations mathématiques ou logiques sur les données.想象一下，如果我们想为每只企鹅计算嘴长与深度的比率，并显示这项测量的 top five values:

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

注意，这回，我们用别名 length_to_depth 为比率值的列。否则，我们会得到一个名字 bill_length_mm / bill_depth_mm 的怪物列。

在 pandas 中，我们首先需要创建一个带有比率值的新列：

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, dans ce tutoriel, nous avons exploré pourquoi et quand nous pouvons combiner la fonctionnalité de SQL pour pandas afin d’écrire un code meilleur et plus efficace. Nous avons discuté de la manière de configurer et d’utiliser la bibliothèque pandasql à cette fin et des limitations de ce package. Enfin, nous avons examiné de nombreux exemples populaires de l’application pratique de pandasql et, dans chaque cas, comparé le code avec son équivalent pandas.

Vous avez maintenant tout ce qu’il vous faut pour appliquer SQL pour pandas dans des projets réels. Un excellent endroit pour vous exercer est le DataLab, le cahier de données de DataCamp activé par l’IA avec un excellent support SQL.

Source:
https://www.datacamp.com/tutorial/how-to-use-sql-in-pandas-using-pandasql-queries