Come usare SQL in pandas con le query di pandasql

Per vedere la leggibilità di SQL in azione, supponiamo di avere una tabella (un dataframe) chiamata penguins che contiene varie informazioni sui pinguini (e lavoreremo con una tabella di questo tipo più avanti nel tutorial). Per estrarre tutte le specie uniche di pinguini maschi con pinne più lunghe di 210 mm, in pandas avremmo bisogno del seguente codice:

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

Invece, per ottenere la stessa informazione usando SQL, eseguiremmo il seguente codice:

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

Il secondo frammento di codice, scritto in SQL, sembra quasi una frase in inglese naturale ed è quindi molto più intuitivo. Possiamo renderlo ancora più leggibile distribuendolo su più righe:

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

Ora che abbiamo individuato i vantaggi di usare SQL con pandas, vediamo come possiamo tecnicamente combinarli.

Come usare pandasql

La libreria Python pandasql consente di interrogare i dataframe di pandas eseguendo comandi SQL senza doversi connettere a un server SQL. Sotto il cofano usa la sintassi SQLite, rileva automaticamente qualsiasi dataframe di pandas e lo tratta come una normale tabella SQL.

Configurazione dell'ambiente

Per prima cosa, dobbiamo installare pandasql:

pip install pandasqlOfferto da

Poi importiamo i pacchetti necessari:

from pandasql import sqldf
import pandas as pdOfferto da

Qui sopra abbiamo importato direttamente la funzione sqldf() da pandasql, che è praticamente l'unica funzione davvero utile della libreria. Come suggerisce il nome, serve per interrogare i dataframe usando la sintassi SQL. Oltre a questa funzione, pandasql include due semplici dataset integrati che possono essere caricati con le funzioni autoesplicative load_births() e load_meat().

Sintassi di pandasql

La sintassi della funzione sqldf() è molto semplice:

sqldf(query, env=None)Offerto da

Qui, query è un parametro obbligatorio che accetta una query SQL come stringa, mentre env è un parametro opzionale (e raramente utile) che può essere locals() o globals() e permette a sqldf() di accedere al corrispondente insieme di variabili nel tuo ambiente Python.

La funzione sqldf() restituisce il risultato di una query come dataframe pandas.

Quando possiamo usare pandasql

La libreria pandasql permette di lavorare con i dati usando il Data Query Language (DQL), che è uno dei sottoinsiemi di SQL. In altre parole, con pandasql possiamo eseguire query sui dati memorizzati in un database per recuperare le informazioni necessarie. In particolare, possiamo accedere, estrarre, filtrare, ordinare, raggruppare, unire, aggregare i dati ed eseguire su di essi operazioni matematiche o logiche.

Quando non possiamo usare pandasql

pandasql non consente di usare altri sottoinsiemi di SQL oltre al DQL. Questo significa che non possiamo usare pandasql per modificare (update, truncate, insert, ecc.) le tabelle o cambiare (update, delete o insert) i dati in una tabella.

Inoltre, poiché questa libreria si basa sulla sintassi SQL, dobbiamo fare attenzione alle note stranezze di SQLite.

Esempi di utilizzo di pandasql

Ora analizzeremo più nel dettaglio come eseguire query SQL su dataframe pandas usando la funzione sqldf() di pandasql. Per avere dei dati su cui esercitarci, carichiamo uno dei dataset integrati della libreria seaborn, penguins:

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

Output:

   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  FemaleOfferto da

Se vuoi rinfrescare le tue competenze SQL, il nostro percorso di competenze SQL Fundamentals è un buon punto di riferimento.

Estrazione dei dati con pandasql

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

Output:

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

Qui sopra abbiamo estratto le informazioni su specie e isola dei primi cinque pinguini dal dataframe penguins. Nota che l'esecuzione della funzione sqldf() restituisce un dataframe pandas:

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

Output:

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

In puro pandas, sarebbe:

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

Output:

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

Un altro esempio è l'estrazione dei valori unici da una colonna:

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

Output:

     species
0     Adelie
1  Chinstrap
2     GentooOfferto da

In pandas, sarebbe:

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

Output:

['Adelie' 'Chinstrap' 'Gentoo']Offerto da

Ordinamento dei dati con pandasql

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

Output:

   body_mass_g
0       6300.0
1       6050.0
2       6000.0
3       6000.0
4       5950.0Offerto da

Qui sopra abbiamo ordinato i nostri pinguini per massa corporea in ordine decrescente e visualizzato i primi cinque valori.

In pandas, sarebbe:

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

Output:

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

Filtraggio dei dati con pandasql

Proviamo lo stesso esempio citato nel capitolo Perché usare SQL in pandas: estrarre le specie uniche di pinguini maschi con pinne più lunghe di 210 mm:

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

Output:

     species
0  Chinstrap
1     GentooOfferto da

Qui sopra abbiamo filtrato i dati in base a due condizioni: sex = 'Male' e flipper_length_mm > 210.

Lo stesso codice in pandas risulterebbe un po' più macchinoso:

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

Output:

['Chinstrap' 'Gentoo']Offerto da

Raggruppamento e aggregazione dei dati con pandasql

Ora applichiamo raggruppamento e aggregazione per trovare il becco più lungo per ciascuna specie nel dataframe:

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

Output:

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

Lo stesso codice in pandas:

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

Output:

     species  bill_length_mm
0     Adelie            46.0
1  Chinstrap            58.0
2     Gentoo            59.6Offerto da

Operazioni matematiche con pandasql

Con pandasql possiamo facilmente eseguire operazioni matematiche o logiche sui dati. Immaginiamo di voler calcolare il rapporto tra lunghezza e profondità del becco per ogni pinguino e visualizzare i primi cinque valori di questa misura:

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

Output:

   length_to_depth
0         3.612676
1         3.510490
2         3.505882
3         3.492424
4         3.458599Offerto da

Nota che questa volta abbiamo usato l'alias length_to_depth per la colonna con i valori del rapporto. Altrimenti, avremmo ottenuto una colonna con un nome chilometrico, bill_length_mm / bill_depth_mm.

In pandas, dovremmo prima creare una nuova colonna con i valori del rapporto:

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())Offerto da

Output:

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

Conclusione

Ricapitolando, in questo tutorial abbiamo visto perché e quando possiamo combinare le funzionalità di SQL con pandas per scrivere codice migliore e più efficiente. Abbiamo discusso come configurare e usare la libreria pandasql a questo scopo e quali limitazioni ha questo pacchetto. Infine, abbiamo esaminato numerosi esempi popolari di applicazione pratica di pandasql e, in ogni caso, confrontato il codice con il suo corrispettivo in pandas.

Ora hai tutto ciò che ti serve per applicare SQL con pandas in progetti reali. Un ottimo luogo per fare pratica è il DataLab, il notebook per dati di DataCamp con supporto SQL potenziato dall'AI.