Pandas資料處理Excel與SQL資料函式庫整合

Pandas 作為 Python 資料分析核心工具，其 I/O 系統在 Excel 和 SQL 資料函式庫互動方面扮演關鍵角色。本文將深入探討如何運用 pandas 處理 Excel 資料，包含讀寫操作、資料型別控制及非預設位置讀取。此外，文章也將解析階層式資料的 pandas 處理方式，並示範如何使用 SQLAlchemy 和 ADBC 與 SQL 資料函式庫進行互動，包含資料寫入、讀取、查詢和更新等操作。同時，本文也將比較 SQLAlchemy 和 ADBC 的效能差異，並提供資料函式庫互動的最佳實務與效能最佳化策略，協助讀者提升資料處理效率。

pandas I/O 系統詳解：Excel 資料處理與應用

pandas 是 Python 中強大的資料分析工具，其 I/O 系統提供了多樣化的資料讀取與寫入功能，特別是在處理 Microsoft Excel 檔案時展現出卓越的靈活性與效能。本文將深入探討 pandas 如何處理 Excel 資料，包括基本讀寫操作、資料型別控制以及從非預設位置讀取資料的方法。

Microsoft Excel 基本讀寫操作

Microsoft Excel 是廣泛使用的資料分析工具，儘管其在效能和可擴充套件性方面存在侷限，但透過 pandas 可以有效彌補這些不足。pandas 提供 pd.read_excel 和 DataFrame.to_excel 方法來實作 Excel 檔案的讀取和寫入。

安裝必要套件

在開始之前，需要安裝 openpyxl 套件來支援 Excel 檔案的操作：

python -m pip install openpyxl

建立範例 DataFrame

首先，建立一個簡單的 DataFrame 用於示範：

import pandas as pd

# 建立 DataFrame
df = pd.DataFrame([
    ["Paul", "McCartney", 1942],
    ["John", "Lennon", 1940],
    ["Richard", "Starkey", 1940],
    ["George", "Harrison", 1943],
], columns=["first", "last", "birth"])

print(df)

輸出結果：

       first        last  birth
0     Paul  McCartney   1942
1     John     Lennon   1940
2  Richard    Starkey   1940
3    George   Harrison   1943

寫入 Excel 檔案

使用 to_excel 方法將 DataFrame 寫入 Excel 檔案：

import io

buf = io.BytesIO()
df.to_excel(buf, index=False)
buf.seek(0)

讀取 Excel 檔案

使用 read_excel 方法讀取剛剛寫入的 Excel 資料：

pd.read_excel(buf, dtype_backend="numpy_nullable")

輸出結果：

       first        last  birth
0     Paul  McCartney   1942
1     John     Lennon   1940
2  Richard    Starkey   1940
3    George   Harrison   1943

資料型別控制

可以透過 dtype 引數控制讀取資料的型別：

dtypes = {
    "first": pd.StringDtype(),
    "last": pd.StringDtype(),
    "birth": pd.Int16Dtype(),
}

df = pd.read_excel(buf, dtype=dtypes)
print(df.dtypes)

輸出結果：

  first    string[python]
last     string[python]
birth           Int16
dtype: object

從非預設位置讀取 Excel 資料

在實際應用中，Excel 檔案的資料可能並非從預設位置開始。pandas 提供了靈活的引數設定來處理這種情況。

範例 Excel 檔案結構

假設有一個 Excel 檔案，其中第一個工作表（Sheet1）是封面，第二個工作表（the_data）包含實際資料，且資料從第四行開始。

讀取特定工作表資料

使用 sheet_name、skiprows 和 usecols 引數來精確讀取所需資料：

pd.read_excel(
    "data/beatles.xlsx",
    dtype_backend="numpy_nullable",
    sheet_name="the_data",
    skiprows=4,
    usecols="C:E",
)

輸出結果：

       first        last  birth
0     Paul  McCartney   1942
1     John     Lennon   1940
2  Richard    Starkey   1940
3    George   Harrison   1943

使用欄位名稱選擇資料欄

也可以直接使用欄位名稱來選擇需要的資料欄：

pd.read_excel(
    "data/beatles.xlsx",
    dtype_backend="numpy_nullable",
    sheet_name="the_data",
    skiprows=4,
    usecols=["first", "last", "birth"],
)

輸出結果與前述相同。

資料讀取流程圖示

  flowchart TD
    A[開始讀取Excel] --> B{選擇工作表}
    B -->|指定工作表名稱| C[略過指定行數]
    B -->|使用預設工作表| D[直接讀取資料]
    C --> E[選擇特定資料欄]
    D --> E
    E --> F[控制資料型別]
    F --> G[傳回DataFrame]

圖表翻譯：

此圖示展示了 pandas 讀取 Excel 資料的流程。首先選擇要讀取的工作表，可以透過指定工作表名稱或使用預設工作表。接著根據需求略過指定的行數，然後選擇特定的資料欄。最後，可以控制資料的型別並傳回為 DataFrame 格式。這樣的流程設計使得 pandas 在處理 Excel 資料時具有高度的靈活性與自定義能力。

技術解析與最佳實踐

1. 效能考量：在處理大型 Excel 檔案時，應考慮使用 openpyxl 的最佳化模式以提升效能。
2. 資料型別控制：適當控制資料型別可以有效減少記憶體使用並提升運算效能。
3. 錯誤處理：在讀取 Excel 檔案時，應加入適當的錯誤處理機制，如檢查檔案是否存在或格式是否正確。
4. 引數調校：根據實際資料的結構調整 skiprows、usecols 等引數，以確保正確讀取所需資料。

資料分析中的階層式資料處理

在資料分析過程中，一項重要的任務是將詳細的資訊彙總成易於理解的摘要報告。相較於逐一檢視成千上萬筆訂單，大多數企業主管更關心的是「過去X個季度的銷售表現如何？」這樣的問題。

使用pandas處理階層式資料

Microsoft Excel常被用來彙總這類別資訊，如圖4.3所示，呈現了按地區/次地區（行）和年份/季度（列）組織的階層式資料檢視。

圖表翻譯：

此圖示展示了階層式資料在Excel中的呈現方式。橫軸代表時間維度上的年份和季度層級，縱軸則代表地理維度的地區和次地區層級。這種結構使得資料能夠按照多重維度進行組織和彙總。

傳統的SQL資料函式庫在處理這種階層式資料時會遇到困難，因為它們無法直接表示這種多層級的關係。常見的做法是將階層欄位合併，產生如2024/Q1、2024/Q2等欄位名稱，雖然便於選取個別欄位，但卻喪失了輕鬆選取特定年份（如「2024年所有銷售資料」）的能力。

幸運的是，pandas函式庫能夠更合理地處理這種階層式關係，直接在行和列索引中支援這種多層級結構。如果回顧第2章「選擇與指定」，我們曾介紹過pd.MultiIndex；透過維持這些關係，使用者可以有效地從階層結構的任何層級中進行選取。

如何實作

仔細觀察圖4.3，可以看到第1、2行包含了年份和季度的標籤，這些標籤可以形成pd.MultiIndex的層級，用於DataFrame的列。由於Microsoft Excel採用1為起始的列編號，對應到Python中實際上是索引[0,1]；我們將使用這些索引值作為header=引數，以建立列的pd.MultiIndex。

import pandas as pd

# 讀取Excel檔案並建立階層式索引
df = pd.read_excel(
 "data/hierarchical.xlsx",
 dtype_backend="numpy_nullable",
 index_col=[0,1],
 header=[0,1],
)

print(df)

輸出結果：

  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

透過這種方式，我們成功地讀入了資料並保持了行和列的階層結構。這使得我們可以利用pandas的原生功能來選取資料，例如回答「每個東部次地區的Q2表現如何隨年份變化？」這樣的問題。

# 選取東部次地區的Q2資料
result = df.loc[(slice(None), "East"), (slice(None), "Q2")]
print(result)

輸出結果：

  Year          2024   2025
Quarter        Q2     Q2
Region Sub-Region          
America East      2      8
Europe East  262144 1048576

使用SQLAlchemy與SQL資料函式庫互動

pandas函式庫提供了強大的SQL資料函式庫互動能力，讓使用者可以直接在關聯式資料函式庫中的資料上進行資料分析。

SQLAlchemy的安裝與設定

首先，我們需要安裝SQLAlchemy函式庫：

python -m pip install sqlalchemy

SQLAlchemy支援多數主流資料函式庫系統，如MySQL、PostgreSQL、MS SQL Server等。為了簡化範例，我們將使用SQLite作為資料函式庫，因為它無需額外設定即可使用。

import sqlalchemy as sa

# 建立SQLite記憶體資料函式庫引擎
engine = sa.create_engine("sqlite:///:memory:")

資料函式庫操作範例

將DataFrame寫入資料函式庫

import pandas as pd

# 建立範例DataFrame
df = pd.DataFrame([
 ["dog", 4],
 ["cat", 4],
], columns=["animal", "num_legs"])

# 將DataFrame寫入資料函式庫
df.to_sql("table_name", engine, index=False)

從資料函式庫讀取資料

# 從資料函式庫讀取資料
result = pd.read_sql("table_name", engine, dtype_backend="numpy_nullable")
print(result)

輸出結果：

   animal  num_legs
0    dog         4
1    cat         4

執行SQL查詢

# 執行SQL查詢計算總腿數
query_result = pd.read_sql(
 "SELECT SUM(num_legs) AS total_legs FROM table_name",
 engine,
 dtype_backend="numpy_nullable"
)

print(query_result)

輸出結果：

   total_legs
0           8

更新資料表

# 建立新DataFrame
new_df = pd.DataFrame([
 ["dog", 4],
 ["cat", 4],
 ["human", 2],
], columns=["animal", "num_legs"])

# 覆寫原有資料表
new_df.to_sql("table_name", engine, index=False, if_exists="replace")

# 讀取更新後的資料表
updated_result = pd.read_sql("table_name", engine, dtype_backend="numpy_nullable")
print(updated_result)

輸出結果：

   animal  num_legs
0    dog         4
1    cat         4
2  human         2

新增資料到現有資料表

# 建立新資料
append_data = pd.DataFrame([["centipede", 100]], columns=["animal", "num_legs"])

# 新增資料到現有資料表
append_data.to_sql("table_name", engine, index=False, if_exists="append")

# 讀取完整資料表
final_result = pd.read_sql("table_name", engine, dtype_backend="numpy_nullable")
print(final_result)

輸出結果：

       animal  num_legs
0        dog         4
1        cat         4
2      human         2
3  centipede       100

資料函式庫連線URL說明

SQLAlchemy使用特定的URL格式來建立資料函式庫連線：dialect+driver://username:password@host:port/database。不同的資料函式庫系統需要不同的URL組態。

SQLite連線範例

  flowchart TD
 A[建立連線] --> B{資料函式庫型別}
 B -->|SQLite| C[使用:memory:]
 B -->|其他資料函式庫| D[組態連線引數]
 C --> E[建立Engine物件]
 D --> E
 E --> F[執行資料函式庫操作]

圖表翻譯：

此圖示展示了使用SQLAlchemy建立資料函式庫連線的流程。流程首先建立連線，接著根據資料函式庫型別的不同選擇不同的組態方式。對於SQLite，可以直接使用:memory:建立記憶體資料函式庫；對於其他資料函式庫系統，則需要組態相應的連線引數。最後透過建立的Engine物件執行資料函式庫操作。

技術實作要點

1. 階層式資料處理：pandas的MultiIndex結構能夠有效處理階層式資料，支援多層級的行列索引。
2. 資料函式庫操作：SQLAlchemy提供了統一的介面來操作不同的資料函式庫系統，支援資料的讀寫與查詢。
3. 錯誤處理：在進行資料函式庫寫入操作時，需要妥善處理可能發生的錯誤，如資料表已存在等情況。

最佳實踐建議

• 在處理階層式資料時，應充分利用pandas的MultiIndex功能來維持資料的結構化特性。
• 使用SQLAlchemy進行資料函式庫操作時，應根據實際需求選擇適當的if_exists引數，以避免意外覆寫現有資料。
• 在進行複雜的SQL查詢時，應考慮使用引數化查詢以提高安全性和效能。

使用ADBC提升SQL互動效能

在資料分析領域中，資料函式庫的互動扮演著至關重要的角色。傳統上，SQLAlchemy是pandas使用者與資料函式庫互動的主要工具。然而，隨著Apache Arrow專案的興起，一種名為Arrow Database Connectivity（ADBC）的新技術應運而生，為SQL互動帶來了更卓越的效能。

為何選擇ADBC？

ADBC的出現是為瞭解決傳統ODBC/JDBC標準在處理欄位導向資料函式庫時的效能瓶頸。相較於SQLAlchemy，ADBC提供了更好的效能和型別安全性，尤其是在處理大型資料集時。

如何使用ADBC與SQLite資料函式庫互動

首先，我們需要安裝SQLite的ADBC驅動程式：

python -m pip install adbc-driver-sqlite

接下來，讓我們示範如何使用ADBC連線SQLite資料函式庫並進行資料操作：

from adbc_driver_sqlite import dbapi
import pandas as pd

# 建立範例DataFrame
df = pd.DataFrame([
    ["dog", 4],
    ["cat", 4],
    ["human", 2],
], columns=["animal", "num_legs"])

# 使用ADBC連線資料函式庫
with dbapi.connect("sqlite:///test.db") as conn:
    # 將DataFrame寫入資料函式庫
    df.to_sql("table_name", conn, index=False, if_exists="replace")
    
    # 從資料函式庫讀取資料
    df_read = pd.read_sql(
        "SELECT * FROM table_name",
        conn,
        dtype_backend="numpy_nullable",
    )

    print(df_read)

程式碼解析：

此範例程式碼展示瞭如何使用ADBC驅動程式連線SQLite資料函式庫。主要步驟包括：

1. 匯入必要的函式庫
2. 建立範例DataFrame
3. 使用dbapi.connect建立資料函式庫連線
4. 將DataFrame寫入資料函式庫表
5. 從資料函式庫表中讀取資料

效能比較：ADBC vs SQLAlchemy

為了展示ADBC的效能優勢，我們進行了簡單的效能測試：

import timeit
import pandas as pd
import numpy as np
import sqlalchemy as sa
from adbc_driver_sqlite import dbapi

# 建立測試DataFrame
np.random.seed(42)
df = pd.DataFrame(np.random.randn(10_000, 10), columns=list("abcdefghij"))

# SQLAlchemy效能測試
with sa.create_engine("sqlite:///:memory:").connect() as conn:
    func = lambda: df.to_sql("test_table", conn, if_exists="replace")
    sa_time = timeit.timeit(func, number=100)
    print(f"SQLAlchemy time: {sa_time}")

# ADBC效能測試
with dbapi.connect("sqlite:///:memory:") as conn:
    func = lambda: df.to_sql("test_table", conn, if_exists="replace")
    adbc_time = timeit.timeit(func, number=100)
    print(f"ADBC time: {adbc_time}")

效能測試結果分析：

  flowchart LR
    A[開始測試] --> B[SQLAlchemy效能測試]
    A --> C[ADBC效能測試]
    B --> D[輸出SQLAlchemy時間]
    C --> E[輸出ADBC時間]
    D --> F[比較效能]
    E --> F
    F --> G{ADBC更快?}
    G -->|是| H[使用ADBC]
    G -->|否| I[使用SQLAlchemy]

圖表解析：

此流程圖展示了ADBC和SQLAlchemy效能測試的比較過程。主要步驟包括：

1. 同時執行兩種方法的效能測試
2. 輸出各自的執行時間
3. 比較兩者的效能
4. 根據比較結果選擇合適的資料函式庫連線方式

ADBC的技術優勢

ADBC的設計充分利用了Apache Arrow的欄位導向架構，為現代資料分析系統提供了更高效的資料交換方式。其主要優勢包括：

1. 更好的效能：特別是在處理大型資料集時
2. 更高的型別安全性：減少資料型別轉換錯誤
3. 對欄位導向資料函式庫的最佳化支援

隨著資料分析需求的不斷增長，ADBC這樣的欄位導向資料函式庫連線技術將會在未來發揮越來越重要的作用。開發者應該關注ADBC驅動程式的最新發展，並根據實際需求選擇合適的技術方案。

重要注意事項

1. 使用ADBC前需確認目標資料函式庫是否有穩定的ADBC驅動程式實作
2. 對於不支援ADBC的資料函式庫，SQLAlchemy仍然是可靠的備選方案
3. 在選擇資料函式庫連線技術時需綜合考慮效能需求和資料函式庫支援情況