領域展開!用 Google Colab, JupySQL, DuckDB 實戰分析數據

Posted on  Apr 9, 2024  in  Python 模組/套件推薦 , Python 程式設計 - 中階  by  Amo Chen  ‐ 5 min read

談到 Python 數據分析的工具,多數人應該使用 Jupyter Notebook 搭配 pandas 進行數據分析,不過使用 pandas 還是不及使用 SQL 來得直覺一些,如果能夠在 Jupyter Notebook 中直接使用 SQL 對資料進行查詢、分析,對於精通 SQL 的人而言,將會減少需要額外學習 pandas 的成本。

恰好 JupySQL 是 1 個能夠讓我們在 Jupyter 以 SQL 對資料進行查詢、分析的擴充套件(extension),而且 JupySQL 也整合 pandas, 因此可以將查詢結果轉換成為 pandas 的 DataFrame, 除此之外更整合 DuckDB, SQLite, MySQL, PostgreSQL 等常見資料庫,讓我們能夠輕鬆以 SQL 操作各種不同來源的資料庫。

本文將以 Google Colab 搭配 JupySQL 與 DuckDB, 帶你學習 Jupyter Notebook + pandas 之外的另一種方法!

本文環境

請在 Colab / Jupyter Notebook 執行以下指令安裝相關套件:

!pip install jupysql duckdb-engine matplotlib ipywidgets SQLAlchemy

Colab / Jupyter Notebook 會有中文字顯示的問題,在開始之前請在 Colab / Jupyter Notebook 執行以下程式碼設定字體:

!wget -O TaipeiSansTCBeta-Regular.ttf https://drive.google.com/uc?id=1eGAsTN1HBpJAkeVM57_C7ccp7hbgSz3_&export=download

import matplotlib as mpl
import matplotlib.pyplot as plt
from matplotlib.font_manager import fontManager

fontManager.addfont('TaipeiSansTCBeta-Regular.ttf')
mpl.rc('font', family='Taipei Sans TC Beta')

範例資料來源

本文使用政府資料開放平臺所提供的個股日成交資訊作為範例資料,該資料含有證券代號、證券名稱、成交股數、成交金額、開盤價、最高價、最低價、收盤價、漲跌價差、成交筆數資訊,可以作為練習使用。

在 Colab / Jupyter Notebook 下載檔案

如果想下載個股日成交資訊可以在網頁中以右鍵點選 CSV, 再點選 Copy Link Address :

jupysql-copy-link-address.png

取得下載連結之後,在 Colab 或 Jupyter Notebook 以 Python 內建 urlretrieve() 函式下載檔案:

from urllib.request import urlretrieve

urlretrieve("<the link address>", "stock.csv")

p.s. 請將 <the link address> 修改為你所複製的網址

上述程式碼執行之後,會多 1 個名稱為 stock.csv 的檔案。

jupyter-stock-csv.png

DuckDB 介紹

DuckDB 是 in-process 的 OLAP 資料庫解決方案,數據分析人員可以用 DuckDB 載入 CSV, Parquet, Arrow 甚至是 RDBMS 上的資料,並用 SQL 語法進行數據分析。

而所謂的 in-process 是指資料庫不需要在另外的 process 執行,應用程式能直接在自身的 process 內運作資料庫,或稱嵌入資料庫(embedded database),所以也不需要透過 client-server 的架構進行互動、傳輸資料。

此外, DuckDB 支援 Python, R, Java, Node.js, 更支援 UDF(User Defined Function), 以及 S3, Excel, Full Text Search, Iceberg 等擴充(extensions),所以分析用途之外,也適合做 ETL(Extract, Transform, Load) 等工作。

藉由 DuckDB 的幫助,我們可以輕鬆地以 SQL 操作 CSV, Parquet, Arrow 等格式的資料,同時我們也不需要額外架設資料庫,一切都能夠在 Colab, Jupyter Notebook 中進行,這也是為什麼本文需要安裝 DuckDB 的緣故。

啟用 JupySQL 擴充並連線至 DuckDB

一切都就緒之後,就可以在 Colab 或 Jupyter Notebook 輸入以下指令,啟用 JupySQL:

%load_ext sql

接著連線至 DuckDB:

%sql duckdb://

上述 duckdb:// 是指連線至 in-memory 的 DuckDB, 因此 DuckDB 能容納多少資料與可用記憶體大小有關

執行 SELECT 查詢

使用 JupySQL 執行 SQL 語句有 2 種 magic commands:

  1. %sql
  2. %%sql

這 2 種 magic commands 的差別在於 %%sql 支援多行的 SQL 語句,例如:

%%sql
SELECT *
FROM stock.csv
LIMIT 10

%sql 只能在後方接受 1 行 SQL 語句:

%sql SELECT * FROM stock.csv LIMIT 10

%sql 支援將查詢結果指定給 1 個 Python 變數的用法:

result = %sql SELECT * FROM stock.csv LIMIT 10

查詢前 10 大交易股數的股票

知道怎麼使用 %sql%%sql 之後,我們實際以 SQL 查詢前 10 大交易股數的股票試試:

%%sql
SELECT *
FROM stock.csv
ORDER BY "成交股數" DESC
LIMIT 10;

上述 SQL 語句執行結果如下圖,可以看到我們透過 DuckDB 對 stock.csv 進行查詢,並取得成交股數前十大的股票資料,第一名是近期熱門的 00940 元大台灣價值高息 ETF:

jupyter-stock-top-10.png

除了 SELECT 之外, DuckDB 也支援多種 SQL 語法,可以到官方文件一探究竟。

添加參數到 SQL 語句之中

JupySQL 也支援在 SQL 語句中放參數的用法,其參數格式為 {{variable_name}} , 例如我們可以將股票代號做為參數 code 放到 SQL 語句之中, JupySQL 會自動將同名變數放到 SQL 語句之中:

code = "00940"
%sql SELECT * FROM stock.csv WHERE "證券代號" = '{{code}}'

將查詢結果轉為 pandas DataFrame

前文提到 %sql 支援賦值的用法,所以我們可以用 %sql 將查詢結果指定給 1 個 Python 變數,並轉成各種格式,例如 pandas 的 DataFrame:

result = %sql SELECT "證券代號", "成交股數"/1000 as "成交張數", "漲跌價差" FROM stock.csv ORDER BY "成交股數" DESC LIMIT 10

上述 SQL 語句只查詢成交股數最高的前十大的證券代號、成交張數(成交股數/1,000)、漲跌價差 3 個欄位,並將查詢結果存到 result 之中。

result 是 1 個 sql.run.resultset.ResultSet ,支援將查詢結果轉為以下格式的用法:

  1. CSV
  2. pandas DataFrame
  3. polars DataFrame
  4. dict / dicts

以下示範將查詢結果轉為 pandas 的 DataFrame:

df = result.DataFrame()

接著就可以用 pandas 的方法操作 df 囉!

繪圖(Plotting)

JupySQL 也支援以 magic command %sqlplot 繪圖的功能,它其實是 matplotlib 的再包裝,不過有 JupySQL 的計算是在 SQL engine 之中,得益於各家資料庫對於記憶體管理的功能,因此在記憶體的消耗上會比起單純用 pandas, seaborn 來得好一些,不過在樣式、調整的彈性上目前仍嫌不足,所以個人認為還是可以善用 pandas 原本就有的繪圖功能。

例如,我們將查詢結果轉為 pandas DataFrame 之後,就能呼叫 DataFrame 的 plot() 方法將圖畫出來:

result = %sql SELECT "證券代號", "成交股數"/1000 as "成交張數", "漲跌價差" FROM stock.csv ORDER BY "成交股數" DESC LIMIT 10

df = result.DataFrame()

color = ["r" if x > 0 else "g" for x in df["漲跌價差"]]

df.plot(
    kind="bar",
    title="成交股數",
    x="證券代號",
    y="成交張數",
    grid=True,
    logy=True,
    color=color
)

上述先走訪每 1 筆 df["漲跌價差"] ,如果漲幅小於 0 就用 r 紅色表示,如果漲幅大於 0 則用 g 綠色表示,接著用 df.plot() 畫出 bar chart, X 軸為證券代號, Y 軸為成交張數,並用 color 參數為每個 bar 指定顏色,以方便讓我們看出漲跌。

執行結果如下圖,可以看到雖然 00940 交易張數相當多,但仍處於跌勢:

jupyter-plot.png

連線到 MySQL 或 PostgreSQL

JupySQL 也支援用 SQLAlchemy 的 engine 與資料庫進行連線,只要提供合適的 connection string 給 SQLAlchemy 的 create_engine() 函式,並搭配 %sql 即可連線到 MySQL 或者 PostgreSQL 等資料庫:

from sqlalchemy import create_engine

connection_string = f"mysql+mysqldb://user:password@localhost/db"
engine = create_engine(connection_string)
%sql engine

此外,資料庫由於保護資訊安全的緣故,都會特別設定帳號密碼,不過將密碼明文寫在 Colab 或 Jupyter Notebook 上是相當危險的作法,因此可以使用 Python 的 getpass 模組 輸入密碼,避免將密碼顯示在 Colab, Jupyter Notebook 上,改良之後的作法如下:

from getpass import getpass
from sqlalchemy import create_engine

password = getpass()
connection_string = f"mysql+mysqldb://user:{password}@localhost/db"
engine = create_engine(connection_string)

%sql engine

連線成功之後,就可以用 SQL 與 MySQL 或 PostgreSQL 等資料庫進行互動囉~

總結

在 Google Colab 或 Jupyter Notebook 使用 JupySQL 擴充後,能以 SQL 與資料庫進行互動是相當棒的一件事,甚至我們可以將 Jupyter Notebook 當作 Database client 使用,而且搭配 DuckDB 的話,還可以直接在 Google Colab 或 Jupyter Notebook 中對 RDBMS 以外的資料來源進行數據分析、製作視覺化圖表,真的相當方便!推薦你也試試!

以上!

Enjoy!

References

https://jupysql.ploomber.io/en/latest/quick-start.html

https://colab.research.google.com/github/willismax/matplotlib_show_chinese_in_colab/blob/master/matplotlib_show_chinese_in_colab.ipynb#scrollTo=RfR0uymWF3cB

對抗久坐職業傷害

研究指出每天增加 2 小時坐著的時間,會增加大腸癌、心臟疾病、肺癌的風險,也造成肩頸、腰背疼痛等常見問題。

然而對抗這些問題,卻只需要工作時定期休息跟伸展身體即可!

你想輕鬆改變現狀嗎?試試看我們的 PomodoRoll 番茄鐘吧! PomodoRoll 番茄鐘會根據你所設定的專注時間,定期建議你 1 項辦公族適用的伸展運動,幫助你打敗久坐所帶來的傷害!

贊助我們的創作

看完這篇文章了嗎? 休息一下,喝杯咖啡吧!

如果你覺得 MyApollo 有讓你獲得實用的資訊,希望能看到更多的技術分享,邀請你贊助我們一杯咖啡,讓我們有更多的動力與精力繼續提供高品質的文章,感謝你的支持!