SQL gebruiken in pandas met pandasql-queries

Om de leesbaarheid van SQL in actie te zien, nemen we aan dat we een tabel (een dataframe) hebben met de naam penguins met allerlei informatie over pinguïns (en we werken later in deze tutorial met zo’n tabel). Om alle unieke soorten pinguïns op te halen die mannelijk zijn en een flipperlengte hebben van meer dan 210 mm, hebben we in pandas de volgende code nodig:

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

Om dezelfde informatie met SQL te krijgen, zouden we de volgende code draaien:

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

Het tweede codeblok, geschreven in SQL, leest bijna als een normale Engelse zin en is daardoor veel intuïtiever. We kunnen de leesbaarheid nog verhogen door het over meerdere regels te spreiden:

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

Nu we de voordelen van SQL voor pandas hebben benoemd, kijken we hoe we ze technisch kunnen combineren.

pandasql gebruiken

De pandasql Python-bibliotheek laat je pandas-dataframes bevragen met SQL-commando’s zonder verbinding te maken met een SQL-server. Onder de motorkap gebruikt het SQLite-syntaxis, detecteert automatisch elk pandas-dataframe en behandelt het als een reguliere SQL-tabel.

Je omgeving instellen

Eerst installeren we pandasql:

pip install pandasqlMogelijk gemaakt door

Vervolgens importeren we de vereiste packages:

from pandasql import sqldf
import pandas as pdMogelijk gemaakt door

Hierboven importeerden we direct de functie sqldf() uit pandasql, die in feite de enige echt zinvolle functie van de bibliotheek is. Zoals de naam al doet vermoeden, gebruik je die om dataframes te bevragen met SQL-syntaxis. Naast deze functie bevat pandasql twee eenvoudige ingebouwde datasets die je kunt laden met de veelzeggende functies load_births() en load_meat().

pandasql-syntaxis

De syntaxis van de functie sqldf() is heel eenvoudig:

sqldf(query, env=None)Mogelijk gemaakt door

Hier is query een vereiste parameter die een SQL-query als string verwacht, en env—een optionele (en zelden nuttige) parameter die ofwel locals() of globals() kan zijn en sqldf() toegang geeft tot de bijbehorende set variabelen in je Python-omgeving.

De functie sqldf() retourneert het resultaat van een query als een pandas-dataframe.

Wanneer we pandasql kunnen gebruiken

De pandasql-bibliotheek laat je werken met data via de Data Query Language (DQL), een subset van SQL. Met andere woorden: met pandasql kunnen we queries uitvoeren op de in een database opgeslagen data om de benodigde informatie op te halen. In het bijzonder kunnen we de data benaderen, extraheren, filteren, sorteren, groeperen, joinen, aggregeren en er wiskundige of logische bewerkingen op uitvoeren.

Wanneer we pandasql niet kunnen gebruiken

pandasql ondersteunt geen andere SQL-subsets dan DQL. Dat betekent dat we pandasql niet kunnen gebruiken om tabellen te wijzigen (update, truncate, insert, enz.) of om data in een tabel te wijzigen (update, delete of insert).

Daarnaast moeten we, omdat deze bibliotheek is gebaseerd op SQL-syntaxis, rekening houden met de bekende eigenaardigheden van SQLite.

Voorbeelden van pandasql-gebruik

Nu kijken we meer in detail hoe je SQL-queries uitvoert op pandas-dataframes met de functie sqldf() van pandasql. Om wat oefendata te hebben, laden we een van de ingebouwde datasets van de seaborn-bibliotheek—penguins:

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

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  FemaleMogelijk gemaakt door

Als je je SQL-vaardigheden wilt opfrissen, is onze skill track SQL Fundamentals een goed startpunt.

Data extraheren met pandasql

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

Output:

  species  island
0  Adelie  Torgersen
1  Adelie  Torgersen
2  Adelie  Torgersen
3  Adelie  Torgersen
4  Adelie  Torgersen
Mogelijk gemaakt door

Hierboven haalden we informatie op over de soort en de locatie van de eerste vijf pinguïns uit het penguins-dataframe. Let op dat het aanroepen van de functie sqldf() een pandas-dataframe retourneert:

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

Output:

<class 'pandas.core.frame.DataFrame'>Mogelijk gemaakt door

In puur pandas zou het zijn:

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

Output:

   species island
0  Adelie  Torgersen
1  Adelie  Torgersen
2  Adelie  Torgersen
3  Adelie  Torgersen
4  Adelie  Torgersen
Mogelijk gemaakt door

Een ander voorbeeld is het extraheren van unieke waarden uit een kolom:

print(sqldf('''SELECT DISTINCT species 
                 FROM penguins'''))Mogelijk gemaakt door

Output:

     species
0     Adelie
1  Chinstrap
2     GentooMogelijk gemaakt door

In pandas zou het zijn:

print(penguins['species'].unique())Mogelijk gemaakt door

Output:

['Adelie' 'Chinstrap' 'Gentoo']Mogelijk gemaakt door

Data sorteren met pandasql

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

Output:

   body_mass_g
0       6300.0
1       6050.0
2       6000.0
3       6000.0
4       5950.0Mogelijk gemaakt door

Hierboven sorteerden we onze pinguïns op lichaamsmassa in aflopende volgorde en toonden we de vijf hoogste waarden van lichaamsmassa.

In pandas zou het zijn:

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

Output:

0    6300.0
1    6050.0
2    6000.0
3    6000.0
4    5950.0
Name: body_mass_g, dtype: float64Mogelijk gemaakt door

Data filteren met pandasql

Laten we hetzelfde voorbeeld proberen dat we noemden in het hoofdstuk Waarom SQL gebruiken in pandas: het extraheren van de unieke soorten pinguïns die mannelijk zijn en een flipperlengte hebben van meer dan 210 mm:

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

Output:

     species
0  Chinstrap
1     GentooMogelijk gemaakt door

Hierboven filterden we de data op basis van twee voorwaarden: sex = 'Male' en flipper_length_mm > 210.

Dezelfde code in pandas oogt wat overweldigender:

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

Output:

['Chinstrap' 'Gentoo']Mogelijk gemaakt door

Groeperen en aggregeren met pandasql

Laten we nu groeperen en aggregeren toepassen om de langste snavel per soort in het dataframe te vinden:

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

Output:

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

Dezelfde code in pandas:

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

Output:

     species  bill_length_mm
0     Adelie            46.0
1  Chinstrap            58.0
2     Gentoo            59.6Mogelijk gemaakt door

Wiskundige bewerkingen uitvoeren met pandasql

Met pandasql kunnen we gemakkelijk wiskundige of logische bewerkingen op de data uitvoeren. Stel dat we de verhouding tussen snavellengte en -diepte voor elke pinguïn willen berekenen en de top vijf waarden van deze maat willen tonen:

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

Output:

   length_to_depth
0         3.612676
1         3.510490
2         3.505882
3         3.492424
4         3.458599Mogelijk gemaakt door

Let op dat we dit keer de alias length_to_depth hebben gebruikt voor de kolom met de verhoudingswaarden. Anders zouden we een kolom krijgen met de monsterlijke naam bill_length_mm / bill_depth_mm.

In pandas zouden we eerst een nieuwe kolom met de verhoudingswaarden moeten aanmaken:

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())Mogelijk gemaakt door

Output:

0    3.612676
1    3.510490
2    3.505882
3    3.492424
4    3.458599
Name: length_to_depth, dtype: float64Mogelijk gemaakt door

Conclusie

Kortom, in deze tutorial hebben we onderzocht waarom en wanneer we de functionaliteit van SQL met pandas kunnen combineren om betere, efficiëntere code te schrijven. We bespraken hoe je de pandasql-bibliotheek hiervoor instelt en gebruikt, en welke beperkingen dit pakket heeft. Tot slot behandelden we tal van populaire voorbeelden van praktische toepassingen van pandasql en vergeleken we in elk geval de code met het pandas-alternatief.

Je hebt nu alles wat je nodig hebt om SQL voor pandas in realistische projecten toe te passen. Een geweldige plek om te oefenen is DataLab, DataCamp’s AI-ondersteunde datanotebook met uitstekende SQL-ondersteuning.