Pandas 的 I/O 系統簡化了資料科學家處理不同來源資料的流程，尤其在面對 Excel 檔案和 SQL 資料函式庫時，更顯其強大功能。讀取 Excel 檔案時，pd.read_excel 函式的 sheet_name、skiprows 和 usecols 等引數提供了高度彈性，讓開發者能精準讀取所需資料。階層式資料的處理也是 pandas 的強項，透過 header 和 index_col 引數，可以輕鬆建立 MultiIndex，有效管理複雜資料結構。此外，pandas 整合 SQLAlchemy，讓 Python 與各種 SQL 資料函式庫的互動更加便捷。對於追求極致效能的應用，ADBC 和 Parquet 則提供了更最佳化的解決方案。ADBC 作為新一代資料函式庫連線標準，專為列式資料函式庫設計，即使與傳統資料函式庫互動，也能展現優於 ODBC/JDBC 的效能。Parquet 則是一種高效的資料儲存格式，支援中繼資料儲存、分割儲存、查詢支援、平行處理和壓縮儲存等特性，大幅提升資料讀寫效率。
pandas I/O 系統：處理 Microsoft Excel 檔案
在資料分析中，經常需要處理來自不同來源的資料，其中 Microsoft Excel 檔案是非常常見的一種。pandas 提供了一個強大的 I/O 系統，能夠輕鬆讀取和寫入 Excel 檔案。
讀取 Excel 檔案
當讀取 Excel 檔案時，可能會遇到檔案格式不規範或資料未正確排列的情況。這時，可以使用 pd.read_excel 函式的各種引數來指定如何讀取資料。
指定工作表
預設情況下，pd.read_excel 會讀取 Excel 檔案中的第一個工作表。如果需要讀取其他工作表，可以使用 sheet_name 引數指定工作表的名稱或索引。
pd.read_excel(
"data/beatles.xlsx",
dtype_backend="numpy_nullable",
sheet_name="the_data",
skiprows=4,
usecols="C:E",
)
跳過指定列
如果 Excel 檔案中的資料不是從第一行開始，可以使用 skiprows 引數跳過指定的列數。
選擇特定欄位
可以使用 usecols 引數選擇特定的欄位。可以透過指定欄位的字母範圍（如 “C:E”）或欄位的標籤列表（如 ["first", "last", "birth"]）來選擇需要的欄位。
pd.read_excel(
"data/beatles.xlsx",
dtype_backend="numpy_nullable",
sheet_name="the_data",
skiprows=4,
usecols=["first", "last", "birth"],
)
解讀程式碼
sheet_name="the_data"指定要讀取的工作表名稱。skiprows=4跳過前 4 列。usecols="C:E"或usecols=["first", "last", "birth"]選擇特定的欄位。
處理階層式資料
在資料分析中，經常需要處理階層式資料。pandas 可以輕鬆處理這種資料，並維持資料之間的階層關係。
建立 MultiIndex
可以使用 header 和 index_col 引數建立 MultiIndex。
df = pd.read_excel(
"data/hierarchical.xlsx",
dtype_backend="numpy_nullable",
index_col=[0, 1],
header=[0, 1],
)
解讀程式碼
index_col=[0, 1]指定前兩列為 row index。header=[0, 1]指定前兩行為 column index。
結果
Year 2024 2025
Quarter Q1 Q2 Q1 Q2
Region Sub-Region
America East 1 2 4 8
West 16 32 64 128
South 256 512 1024 4096
Europe West 8192 16384 32768 65536
East 131072 262144 524288 1048576
使用 SQLAlchemy 與 SQL 資料函式庫互動
pandas 提供了一個強大的功能，可以與 SQL 資料函式庫互動。這個功能是根據 SQLAlchemy 函式庫，可以連線到各種不同的 SQL 資料函式庫。
連線資料函式庫
要連線 SQL 資料函式庫，需要使用 SQLAlchemy 的 create_engine 方法建立一個引擎物件。
理論上，pandas 可以與任何 SQLAlchemy 可以連線的資料函式庫一起工作。
重點整理
- 使用
pd.read_excel可以讀取 Excel 檔案，並透過各種引數指定如何讀取資料。 - 可以使用
sheet_name、skiprows和usecols等引數控制讀取的資料。 - pandas 可以處理階層式資料，並維持資料之間的階層關係。
- 可以使用
header和index_col建立 MultiIndex。 - pandas 可以與 SQL 資料函式庫互動，使用 SQLAlchemy 連線各種不同的 SQL 資料函式庫。
使用SQLAlchemy與ADBC進行資料函式庫操作
在進行資料函式庫操作時，pandas提供了多種方法來讀取和寫入資料。本文將介紹如何使用SQLAlchemy和ADBC來進行資料函式庫操作。
使用SQLAlchemy
首先，我們需要安裝SQLAlchemy。可以使用以下命令進行安裝：
python -m pip install sqlalchemy
SQLAlchemy支援多種資料函式庫，包括MySQL、PostgreSQL、MS SQL Server等。為了簡化操作，我們將使用SQLite作為範例資料函式庫。
建立SQLAlchemy引擎
首先，我們需要建立一個SQLAlchemy引擎。使用sa.create_engine函式可以建立一個引擎，函式的引數是一個URL字串。
import sqlalchemy as sa
engine = sa.create_engine("sqlite:///:memory:")
寫入資料到資料函式庫
使用pd.DataFrame.to_sql方法可以將一個DataFrame寫入到資料函式庫中。
import pandas as pd
df = pd.DataFrame([
["dog", 4],
["cat", 4],
], columns=["animal", "num_legs"])
df = df.convert_dtypes(dtype_backend="numpy_nullable")
df.to_sql("table_name", engine, index=False)
從資料函式庫讀取資料
使用pd.read_sql函式可以從資料函式庫中讀取資料。
pd.read_sql("table_name", engine, dtype_backend="numpy_nullable")
輸出結果：
animal num_legs
0 dog 4
1 cat 4
執行SQL查詢
使用pd.read_sql函式也可以執行SQL查詢。
pd.read_sql(
"SELECT SUM(num_legs) AS total_legs FROM table_name",
engine,
dtype_backend="numpy_nullable"
)
輸出結果：
total_legs
0 8
使用ADBC
ADBC（Arrow Database Connectivity）是Apache Arrow專案中的一個新技術，可以提供更好的效能和型別安全。
安裝ADBC驅動程式
首先，我們需要安裝ADBC驅動程式。可以使用以下命令進行安裝：
python -m pip install adbc-driver-sqlite
建立ADBC連線
使用dbapi.connect函式可以建立一個ADBC連線。
from adbc_driver_sqlite import dbapi
with dbapi.connect("file::memory:") as conn:
# 進行資料函式庫操作
pass
寫入資料到資料函式庫
使用pd.DataFrame.to_sql方法可以將一個DataFrame寫入到資料函式庫中。
df = pd.DataFrame([
["dog", 4],
["cat", 4],
["human", 2],
], columns=["animal", "num_legs"])
df = df.convert_dtypes(dtype_backend="numpy_nullable")
with dbapi.connect("file::memory:") as conn:
df.to_sql("table_name", conn, index=False, if_exists="replace")
df = pd.read_sql(
"SELECT * FROM table_name",
conn,
dtype_backend="numpy_nullable",
)
df
輸出結果：
animal num_legs
0 dog 4
1 cat 4
2 human 2
效能比較
使用ADBC可以提供更好的效能。下面是一個簡單的效能比較範例：
import timeit
import sqlalchemy as sa
import numpy as np
np.random.seed(42)
df = pd.DataFrame(
np.random.randn(10_000, 10),
columns=list("abcdefghij")
)
with sa.create_engine("sqlite:///:memory:").connect() as conn:
func = lambda: df.to_sql("test_table", conn, if_exists="replace")
print(timeit.timeit(func, number=100))
with dbapi.connect("file::memory:") as conn:
func = lambda: df.to_sql("test_table", conn, if_exists="replace")
print(timeit.timeit(func, number=100))
輸出結果：
4.898935955003253
0.7935214300014195
從結果可以看出，使用ADBC可以提供更好的效能。
內容解密：
此範例展示瞭如何使用SQLAlchemy和ADBC進行資料函式庫操作。SQLAlchemy是一個成熟的ORM工具，支援多種資料函式庫，而ADBC則是一個新的技術，可以提供更好的效能和型別安全。在選擇使用哪種技術時，需要根據具體的需求和資料函式庫型別進行考慮。
Plantuml資料函式庫操作流程
**圖表翻譯：**此圖表展示了資料函式庫操作的基本流程。首先，需要建立一個資料函式庫連線。然後，可以將資料寫入到資料函式庫中，或者從資料函式庫中讀取資料。同時，也可以執行SQL查詢來取得特定的資料。
內容解密：
此Plantuml圖表展示了資料函式庫操作的基本流程，包括建立資料函式庫連線、寫入資料、讀取資料和執行SQL查詢等步驟。透過這個流程圖，可以清晰地瞭解資料函式庫操作的基本步驟和邏輯關係。
資料儲存與讀取的最佳實踐：Apache Parquet 與 ADBC 的優勢
在資料分析和處理的過程中，選擇適當的資料儲存格式和資料函式庫互動方式至關重要。Apache Parquet 和 ADBC（Arrow Database Connectivity）是兩個重要的技術，它們分別解決了資料儲存和資料函式庫互動的問題。
為何需要標準化的資料函式庫連線？
在 1990 年代，Open Database Connectivity（ODBC）和 Java Database Connectivity（JDBC）標準的出現，簡化了不同客戶端與各種資料函式庫之間的互動。在此之前，應用程式需要為每個資料函式庫編寫特定的程式碼，以實作與資料函式庫的互動。例如，若要取得資料函式庫中的表格列表，PostgreSQL 資料函式庫需要查詢 pg_catalog.pg_tables 表格，而 SQLite 資料函式庫則需要查詢 sqlite_schema 表格。ODBC 和 JDBC 的出現，將資料函式庫互動的複雜性轉移到了驅動程式身上，使得應用程式可以與不同的資料函式庫進行互動，而無需修改應用程式本身。
ADBC：為列式資料函式庫而生的標準
雖然 ODBC 和 JDBC 標準在當時解決了許多問題，但它們主要是針對行式資料函式庫設計的。隨著列式資料函式庫的興起，例如 BigQuery、Redshift 和 Snowflake，ADBC 應運而生。ADBC 是一種列式資料函式庫連線標準，它使得 pandas 等列式資料處理函式庫可以高效地與列式資料函式庫進行互動。ADBC 驅動程式針對 Apache Arrow 進行了最佳化，因此即使不使用列式資料函式庫，也能提供比 ODBC/JDBC 更好的效能。
Apache Parquet：高效的資料儲存格式
Apache Parquet 是一種高效的資料儲存格式，適用於儲存 pd.DataFrame。它提供了多項優勢，包括：
- 中繼資料儲存：Parquet 檔案可以儲存資料型別等中繼資料。
- 分割儲存：資料可以分割成多個檔案儲存。
- 查詢支援：Parquet 檔案可以被查詢，而無需將所有資料載入記憶體。
- 平行處理：讀取 Parquet 檔案可以平行處理，以提高效率。
- 壓縮儲存：Parquet 檔案採用壓縮儲存，節省儲存空間。
使用 Apache Parquet 儲存和讀取資料
pandas 提供了 pd.read_parquet 和 pd.DataFrame.to_parquet 方法，分別用於讀取和寫入 Parquet 檔案。以下是一個範例：
import io
import pandas as pd
# 建立範例資料
df = pd.DataFrame([
["Paul", "McCartney", 1942],
["John", "Lennon", 1940],
["Richard", "Starkey", 1940],
["George", "Harrison", 1943],
], columns=["first", "last", "birth"])
# 將資料轉換為適當的資料型別
df = df.convert_dtypes(dtype_backend="numpy_nullable")
# 將 DataFrame 寫入 Parquet 檔案
buf = io.BytesIO()
df.to_parquet(buf, index=False)
# 將檔案指標移回檔案起始位置
buf.seek(0)
# 從 Parquet 檔案讀取 DataFrame
read_df = pd.read_parquet(buf)
print(read_df)
#### 內容解密：
在這個範例中，我們首先建立了一個包含 Beatles 成員資訊的 DataFrame。然後，我們將 DataFrame 寫入了一個 io.BytesIO 物件中，使用 to_parquet 方法。接著，我們將檔案指標移回檔案起始位置，並使用 read_parquet 方法從 io.BytesIO 物件中讀取 DataFrame。可以觀察到，讀取出來的 DataFrame 與原始 DataFrame 相同。
@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle
title Pandas資料處理Excel與SQLAlchemy整合應用
package "資料庫架構" {
package "應用層" {
component [連線池] as pool
component [ORM 框架] as orm
}
package "資料庫引擎" {
component [查詢解析器] as parser
component [優化器] as optimizer
component [執行引擎] as executor
}
package "儲存層" {
database [主資料庫] as master
database [讀取副本] as replica
database [快取層] as cache
}
}
pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中
master --> replica : 資料同步
note right of cache
Redis/Memcached
減少資料庫負載
end note
@enduml
圖表翻譯： 此圖表展示了使用 Apache Parquet 儲存和讀取 DataFrame 的過程。首先，建立一個 DataFrame，然後將其寫入 Parquet 檔案。接著，從 Parquet 檔案中讀取 DataFrame，並取得最終的 DataFrame。
使用 Apache Parquet 和 ADBC，可以簡化資料儲存和資料函式庫互動的過程，提高資料處理的效率和效能。這些技術在現代資料分析和處理中扮演著重要的角色。