Cara Menggunakan SQL di pandas dengan Kueri pandasql

Untuk melihat keterbacaan SQL secara langsung, misalkan kita memiliki sebuah tabel (dataframe) bernama penguins yang berisi berbagai informasi tentang penguin (dan kita akan bekerja dengan tabel seperti itu nanti dalam tutorial ini). Untuk mengekstrak semua spesies penguin unik yang berjenis kelamin jantan dan memiliki panjang sirip lebih dari 210 mm, kita memerlukan kode berikut di pandas:

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

Sebagai gantinya, untuk mendapatkan informasi yang sama menggunakan SQL, kita akan menjalankan kode berikut:

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

Potongan kode kedua, yang ditulis dalam SQL, terlihat hampir seperti kalimat bahasa Inggris alami dan karenanya jauh lebih intuitif. Kita dapat lebih meningkatkan keterbacaannya dengan membaginya ke beberapa baris:

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

Sekarang setelah kita mengidentifikasi keunggulan menggunakan SQL untuk pandas, mari lihat bagaimana kita dapat menggabungkannya secara teknis.

Cara Menggunakan pandasql

Pustaka Python pandasql memungkinkan menjalankan kueri pada dataframe pandas dengan perintah SQL tanpa harus terhubung ke server SQL mana pun. Di balik layar, pustaka ini menggunakan sintaks SQLite, secara otomatis mendeteksi setiap dataframe pandas, dan memperlakukannya sebagai tabel SQL biasa.

Menyiapkan lingkungan Anda

Pertama, kita perlu memasang pandasql:

pip install pandasqlDidukung oleh

Lalu, kita impor paket yang diperlukan:

from pandasql import sqldf
import pandas as pdDidukung oleh

Di atas, kita langsung mengimpor fungsi sqldf() dari pandasql, yang praktis merupakan satu-satunya fungsi yang bermakna dari pustaka tersebut. Sesuai namanya, fungsi ini diterapkan untuk menjalankan kueri pada dataframe menggunakan sintaks SQL. Selain fungsi ini, pandasql hadir dengan dua set data bawaan sederhana yang dapat dimuat menggunakan fungsi yang jelas namanya, yaitu load_births() dan load_meat().

Sintaks pandasql

Sintaks fungsi sqldf() sangat sederhana:

sqldf(query, env=None)Didukung oleh

Di sini, query adalah parameter wajib yang menerima kueri SQL sebagai string, dan env—parameter opsional (dan jarang berguna) yang bisa berupa locals() atau globals() dan memungkinkan sqldf() mengakses sekumpulan variabel terkait di lingkungan Python Anda.

Fungsi sqldf() mengembalikan hasil kueri sebagai dataframe pandas.

Kapan kita dapat menggunakan pandasql

Pustaka pandasql memungkinkan bekerja dengan data menggunakan Data Query Language (DQL), yang merupakan salah satu subset SQL. Dengan kata lain, dengan pandasql, kita dapat menjalankan kueri pada data yang tersimpan dalam basis data untuk mengambil informasi yang diperlukan. Secara khusus, kita dapat mengakses, mengekstrak, memfilter, mengurutkan, mengelompokkan, menggabungkan, melakukan agregasi data, serta menjalankan operasi matematis atau logis atas data tersebut.

Kapan kita tidak dapat menggunakan pandasql

pandasql tidak memungkinkan penggunaan subset SQL lain selain DQL. Ini berarti kita tidak dapat menerapkan pandasql untuk memodifikasi (update, truncate, insert, dll.) tabel atau mengubah (update, delete, atau insert) data dalam sebuah tabel.

Selain itu, karena pustaka ini berbasis pada sintaks SQL, kita perlu mewaspadai keanehan yang diketahui di SQLite.

Contoh penggunaan pandasql

Sekarang, kita akan melihat lebih rinci cara menjalankan kueri SQL pada dataframe pandas menggunakan fungsi sqldf() dari pandasql. Agar ada data untuk berlatih, mari muat salah satu set data bawaan dari pustaka seaborn—penguins:

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

Keluaran:

   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  FemaleDidukung oleh

Jika Anda perlu menyegarkan kembali keterampilan SQL, SQL Fundamentals skill track kami adalah titik rujukan yang baik.

Menarik data dengan pandasql

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

Keluaran:

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

Di atas, kita mengekstrak informasi tentang spesies dan lokasi untuk lima penguin pertama dari dataframe penguins. Perhatikan bahwa menjalankan fungsi sqldf() mengembalikan sebuah dataframe pandas:

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

Keluaran:

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

Dalam pandas murni, akan seperti ini:

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

Keluaran:

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

Contoh lain adalah mengekstrak nilai unik dari sebuah kolom:

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

Keluaran:

     species
0     Adelie
1  Chinstrap
2     GentooDidukung oleh

Di pandas, akan seperti ini:

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

Keluaran:

['Adelie' 'Chinstrap' 'Gentoo']Didukung oleh

Mengurutkan data dengan pandasql

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

Keluaran:

   body_mass_g
0       6300.0
1       6050.0
2       6000.0
3       6000.0
4       5950.0Didukung oleh

Di atas, kita mengurutkan penguin berdasarkan massa tubuh secara menurun dan menampilkan lima nilai massa tubuh teratas.

Di pandas, akan seperti ini:

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

Keluaran:

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

Memfilter data dengan pandasql

Mari coba contoh yang sama yang kita sebutkan di bab Mengapa menggunakan SQL di pandas: mengekstrak spesies penguin unik yang berjenis kelamin jantan dan memiliki panjang sirip lebih dari 210 mm:

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

Keluaran:

     species
0  Chinstrap
1     GentooDidukung oleh

Di atas, kita memfilter data berdasarkan dua kondisi: sex = 'Male' dan flipper_length_mm > 210.

Kode yang sama di pandas terlihat sedikit lebih rumit:

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

Keluaran:

['Chinstrap' 'Gentoo']Didukung oleh

Mengelompokkan dan mengagregasi data dengan pandasql

Sekarang, mari terapkan pengelompokan dan agregasi data untuk mencari panjang paruh terpanjang untuk setiap spesies dalam dataframe:

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

Keluaran:

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

Kode yang sama di pandas:

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

Keluaran:

     species  bill_length_mm
0     Adelie            46.0
1  Chinstrap            58.0
2     Gentoo            59.6Didukung oleh

Melakukan operasi matematis dengan pandasql

Dengan pandasql, kita dapat dengan mudah melakukan operasi matematis atau logis pada data. Misalkan kita ingin menghitung rasio panjang terhadap kedalaman paruh untuk setiap penguin dan menampilkan lima nilai teratas dari pengukuran ini:

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

Keluaran:

   length_to_depth
0         3.612676
1         3.510490
2         3.505882
3         3.492424
4         3.458599Didukung oleh

Perhatikan bahwa kali ini, kita menggunakan alias length_to_depth untuk kolom dengan nilai rasio. Jika tidak, kita akan mendapatkan kolom dengan nama yang sangat panjang bill_length_mm / bill_depth_mm.

Di pandas, kita perlu terlebih dahulu membuat kolom baru dengan nilai rasio:

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())Didukung oleh

Keluaran:

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

Kesimpulan

Sebagai penutup, dalam tutorial ini, kita mengeksplorasi mengapa dan kapan kita dapat menggabungkan fungsionalitas SQL untuk pandas guna menulis kode yang lebih baik dan lebih efisien. Kita membahas cara menyiapkan dan menggunakan pustaka pandasql untuk tujuan ini serta keterbatasan paket ini. Terakhir, kita meninjau berbagai contoh populer penerapan praktis pandasql dan, pada setiap kasus, membandingkan kodenya dengan padanan di pandas.

Sekarang Anda memiliki semua yang diperlukan untuk menerapkan SQL untuk pandas dalam proyek dunia nyata. Tempat yang tepat untuk berlatih adalah DataLab, notebook data bertenaga AI dari DataCamp dengan dukungan SQL yang hebat.