Chuyển đến nội dung chính

Cách sử dụng SQL trong pandas với truy vấn pandasql

Khai thác sức mạnh của SQL trong pandas và tìm hiểu khi nào, cách dùng truy vấn SQL trong pandas bằng thư viện pandasql để tích hợp mượt mà.
· 8 phút đọc

Khám phá cùng AI

Trong hướng dẫn này, chúng ta sẽ bàn về khi nào và cách chúng ta có thể (và khi nào không thể) sử dụng chức năng SQL trong khuôn khổ của pandas. Ngoài ra, chúng ta sẽ xem xét nhiều ví dụ triển khai cách tiếp cận này và so sánh kết quả với đoạn mã tương đương viết hoàn toàn bằng pandas.

Vì sao dùng SQL trong pandas?

Tại sao lại muốn kết hợp SQL với pandas khi bản thân pandas đã là gói “tất cả trong một” cho phân tích dữ liệu?

Câu trả lời là trong một số trường hợp, đặc biệt với các chương trình phức tạp, truy vấn SQL trông mạch lạc và dễ đọc hơn nhiều so với đoạn mã tương ứng trong pandas. Điều này đặc biệt đúng với những người ban đầu làm việc với dữ liệu bằng SQL rồi mới học pandas.

Để thấy tính dễ đọc của SQL trong thực tế, giả sử chúng ta có một bảng (dataframe) tên là penguins chứa nhiều thông tin về chim cánh cụt (và chúng ta sẽ làm việc với bảng này ở phần sau của hướng dẫn). Để trích xuất tất cả các loài chim cánh cụt là giống đực và có chiều dài vây hơn 210 mm, chúng ta cần đoạn mã sau trong pandas:

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

Thay vào đó, để lấy cùng thông tin bằng SQL, chúng ta sẽ chạy đoạn mã sau:

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

Đoạn mã thứ hai, viết bằng SQL, trông gần như một câu tiếng Anh tự nhiên và do đó trực quan hơn nhiều. Ta còn có thể tăng tính dễ đọc bằng cách xuống dòng nhiều dòng:

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

Giờ khi đã thấy ưu thế của việc dùng SQL cho pandas, hãy xem cách kết hợp kỹ thuật cả hai.

Cách dùng pandasql

Thư viện Python pandasql cho phép truy vấn các dataframe của pandas bằng cách chạy lệnh SQL mà không cần kết nối tới bất kỳ máy chủ SQL nào. Bên dưới, nó dùng cú pháp SQLite, tự động phát hiện mọi dataframe của pandas và xử lý chúng như bảng SQL thông thường.

Thiết lập môi trường

Trước hết, chúng ta cần cài đặt pandasql:

pip install pandasql

Sau đó, import các gói cần thiết:

from pandasql import sqldf
import pandas as pd

Ở trên, chúng ta import trực tiếp hàm sqldf() từ pandasql, gần như là hàm có ý nghĩa duy nhất của thư viện này. Đúng như tên gọi, nó được dùng để truy vấn dataframe bằng cú pháp SQL. Ngoài hàm này, pandasql còn đi kèm hai bộ dữ liệu tích hợp đơn giản có thể tải bằng các hàm tự giải thích load_births() và load_meat().

Cú pháp pandasql

Cú pháp của hàm sqldf() rất đơn giản:

sqldf(query, env=None)

Ở đây, query là tham số bắt buộc, nhận vào một truy vấn SQL dưới dạng chuỗi, còn env—một tham số tùy chọn (và hiếm khi hữu ích)—có thể là locals() hoặc globals(), cho phép sqldf() truy cập tập biến tương ứng trong môi trường Python của bạn.

Hàm sqldf() trả về kết quả truy vấn dưới dạng một dataframe của pandas.

Khi nào có thể dùng pandasql

Thư viện pandasql cho phép làm việc với dữ liệu bằng Data Query Language (DQL), là một tập con của SQL. Nói cách khác, với pandasql, chúng ta có thể chạy truy vấn trên dữ liệu lưu trong cơ sở dữ liệu để truy xuất thông tin cần thiết. Cụ thể, chúng ta có thể truy cập, trích xuất, lọc, sắp xếp, nhóm, join, tổng hợp dữ liệu và thực hiện các phép toán học hoặc logic trên đó.

Khi nào không thể dùng pandasql

pandasql không cho phép dùng các tập con khác của SQL ngoài DQL. Điều này có nghĩa là chúng ta không thể dùng pandasql để sửa đổi (update, truncate, insert, v.v.) bảng hoặc thay đổi (update, delete, insert) dữ liệu trong bảng.

Ngoài ra, vì thư viện này dựa trên cú pháp SQL, chúng ta cần lưu ý các điểm lập dị đã biết trong SQLite.

Ví dụ sử dụng pandasql

Giờ chúng ta sẽ xem chi tiết hơn cách chạy truy vấn SQL trên các dataframe của pandas bằng hàm sqldf() của pandasql. Để có dữ liệu thực hành, hãy tải một trong các bộ dữ liệu tích hợp của thư viện seaborn—penguins:

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

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  Female

Nếu bạn cần ôn lại kỹ năng SQL, lộ trình kỹ năng SQL Fundamentals của chúng tôi là điểm tham chiếu tốt.

Trích xuất dữ liệu với pandasql

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

Output:

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

Ở trên, chúng ta trích xuất thông tin về loài và địa lý của năm cá thể đầu tiên trong dataframe penguins. Lưu ý rằng chạy hàm sqldf() trả về một dataframe của pandas:

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

Output:

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

Trong pandas thuần, sẽ là:

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

Output:

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

Một ví dụ khác là trích xuất các giá trị duy nhất từ một cột:

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

Output:

     species
0     Adelie
1  Chinstrap
2     Gentoo

Trong pandas, sẽ là:

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

Output:

['Adelie' 'Chinstrap' 'Gentoo']

Sắp xếp dữ liệu với pandasql

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

Output:

   body_mass_g
0       6300.0
1       6050.0
2       6000.0
3       6000.0
4       5950.0

Ở trên, chúng ta sắp xếp chim cánh cụt theo khối lượng cơ thể giảm dần và hiển thị năm giá trị lớn nhất.

Trong pandas, sẽ là:

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

Output:

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

Lọc dữ liệu với pandasql

Hãy thử lại ví dụ đã nêu ở phần Vì sao dùng SQL trong pandas: trích xuất các loài chim cánh cụt duy nhất là giống đực và có chiều dài vây hơn 210 mm:

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

Output:

     species
0  Chinstrap
1     Gentoo

Ở trên, chúng ta lọc dữ liệu dựa trên hai điều kiện: sex = 'Male' và flipper_length_mm > 210.

Đoạn mã tương tự trong pandas sẽ trông hơi rối hơn:

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

Output:

['Chinstrap' 'Gentoo']

Nhóm và tổng hợp dữ liệu với pandasql

Giờ hãy áp dụng nhóm và tổng hợp dữ liệu để tìm độ dài mỏ lớn nhất cho mỗi loài trong dataframe:

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

Output:

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

Đoạn mã tương tự trong pandas:

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

Output:

     species  bill_length_mm
0     Adelie            46.0
1  Chinstrap            58.0
2     Gentoo            59.6

Thực hiện phép toán với pandasql

Với pandasql, chúng ta có thể dễ dàng thực hiện các phép toán học hoặc logic trên dữ liệu. Hãy tưởng tượng chúng ta muốn tính tỷ lệ chiều dài mỏ trên độ sâu mỏ cho từng cá thể và hiển thị năm giá trị lớn nhất của phép đo này:

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

Output:

   length_to_depth
0         3.612676
1         3.510490
2         3.505882
3         3.492424
4         3.458599

Lưu ý lần này, chúng ta dùng bí danh length_to_depth cho cột chứa giá trị tỷ lệ. Nếu không, chúng ta sẽ nhận được một tên cột rất dài bill_length_mm / bill_depth_mm.

Trong pandas, chúng ta sẽ cần tạo trước một cột mới chứa giá trị tỷ lệ:

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

Output:

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

Kết luận

Tổng kết lại, trong hướng dẫn này, chúng ta đã khám phá vì sao và khi nào có thể kết hợp chức năng của SQL cho pandas để viết mã tốt hơn, hiệu quả hơn. Chúng ta đã bàn cách thiết lập và sử dụng thư viện pandasql cho mục đích này và các hạn chế mà gói này có. Cuối cùng, chúng ta đã xem nhiều ví dụ phổ biến về ứng dụng thực tế của pandasql và trong mỗi trường hợp đều so sánh đoạn mã với phần tương ứng trong pandas.

Giờ bạn đã có mọi thứ cần thiết để áp dụng SQL cho pandas trong các dự án thực tế. Nơi tuyệt vời để thực hành là DataLab, sổ tay dữ liệu tích hợp AI của DataCamp với hỗ trợ SQL mạnh mẽ.

Chủ đề

SQLPython

Liên quan

Xem thêm →