DuckDB高效處理Excel檔案實戰

DuckDB 作為一款嵌入式分析型資料函式庫，除了 CSV、Parquet 等常見格式外，也支援 Excel 檔案的讀寫，方便資料分析師直接處理 Excel 資料。然而，DuckDB 在處理 Excel 時的型別偵測機制仍有改進空間，例如數字欄位可能被誤判為浮點數而非整數。此外，DuckDB 預設讀取 Excel 公式為字串，而非計算結果，除非檔案儲存時已包含計算值。雖然 DuckDB 可以將資料寫入 Excel，但仍有限制，例如日期和時間戳記需要轉換為字串格式。透過 Python API，DuckDB 可以與 Python 生態系統無縫整合，例如處理 pandas DataFrame 和 Apache Arrow 表，大幅提升資料處理效率。DuckDB 的關聯式 API 更簡化了資料查詢流程，讓開發者能以更直覺的方式操作資料，模糊了關聯式資料函式庫和記憶體物件的界限，提升資料分析效率。

使用 DuckDB 處理 Excel 檔案

DuckDB 支援讀取 Excel 檔案的功能，這對於需要處理和分析 Excel 資料的使用者來說非常方便。以下將介紹如何使用 DuckDB 讀取和寫入 Excel 檔案。

讀取 Excel 檔案

DuckDB 使用 st_read 函式來讀取 Excel 檔案。假設我們有一個名為 atp_rankings.xlsx 的 Excel 檔案，我們可以使用以下陳述式讀取該檔案的第一個工作表：

SELECT ranking_date, rank, name_last
FROM st_read('atp_rankings.xlsx')
ORDER BY ranking_date LIMIT 5;

需要注意的是，DuckDB 的型別偵測機制在處理 Excel 檔案時可能不如處理 CSV 或 JSON 檔案時那樣最佳化。在上述查詢結果中，我們可以看到 ranking_date 欄位的型別被正確偵測為 date，但 rank 欄位的型別被偵測為 double，儘管它實際上應該是整數。

內容解密：

st_read 函式用於讀取 Excel 檔案。
SELECT 陳述式選擇了 ranking_date、rank 和 name_last 三個欄位。
ORDER BY 陳述式根據 ranking_date 進行排序。
LIMIT 5 陳述式限制輸出結果為前 5 行。

Excel 檔案中的公式處理

Excel 檔案中可能包含公式。預設情況下，DuckDB 將這些公式讀取為原始公式字串，而不是計算結果。不過，如果 Excel 檔案在儲存時包含了公式的最後計算結果，那麼 DuckDB 就能夠讀取到這個值。

寫入 Excel 檔案

DuckDB 也支援將資料寫入 Excel 檔案，但有一些限制。例如，日期和時間戳記等資料型別不被直接支援，需要轉換為字串格式。以下是一個將資料寫入 Excel 檔案的範例：

COPY (
  SELECT * EXCLUDE (player, wikidata_id)
  REPLACE (
    strftime(strptime(ranking_date, '%Y%m%d'), '%Y-%m-%d') AS ranking_date,
    strftime(strptime(dob, '%Y%m%d'), '%Y-%m-%d') AS dob
  )
  FROM 'atp/atp_rankings_*.csv' rankings
  JOIN 'atp/atp_players.csv' players ON players.player_id = rankings.player
  ORDER BY ranking_date ASC
)
TO 'atp_rankings_full.xlsx' WITH (FORMAT GDAL, DRIVER 'xlsx');

這個範例將 ATP 球員排名資料從 CSV 檔案讀取出來，經過處理後寫入到一個名為 atp_rankings_full.xlsx 的 Excel 檔案中。

內容解密：

COPY 陳述式用於將查詢結果寫入檔案。
SELECT 陳述式選擇了需要的欄位，並對日期欄位進行了格式轉換。
JOIN 陳述式將球員排名資料與球員資料進行了關聯。
TO 子句指定了輸出檔案的路徑和格式。

與 Python 生態系統的整合

本章節將介紹如何使用 DuckDB 的 Python API 與 Python 生態系統進行整合，包括如何使用 DuckDB 處理 pandas DataFrame 和 Apache Arrow 表等。

安裝 DuckDB Python 套件

首先，需要安裝 DuckDB 的 Python 套件。可以使用 pip 安裝：

pip install duckdb

安裝完成後，可以在 Python 環境中匯入 DuckDB：

import duckdb

並檢查 DuckDB 的版本：

duckdb.__version__

開啟資料函式庫連線

由於 DuckDB 是嵌入式資料函式庫，因此不需要額外開啟資料函式庫連線。可以直接使用 duckdb 物件進行操作。

使用 DuckDB 的 Python API

DuckDB 的 Python API 不僅實作了 Python DB-API 2.0，還提供了更多的功能，例如可以直接查詢 Python 物件，如 pandas DataFrame。

本章節將介紹如何使用 DuckDB 的 Python API 處理 pandas DataFrame 和 Apache Arrow 表，包括如何匯入資料、查詢資料和匯出資料等。

使用關聯式API與DuckDB的Python整合

DuckDB提供了一個獨特的關聯式API，讓使用者能夠以更自然的方式查詢資料，模糊了關聯式資料函式庫和記憶體中可查詢物件之間的界限。本章節將探討如何使用DuckDB的Python API來查詢CSV資料，並介紹關聯式API的基本概念。

關聯式API的基本概念

在關聯式資料函式庫中，一個關係（relation）是由元組（tuples）和屬性（attributes）組成，而不是傳統意義上的列和行。可以將關係視為表的一般化版本。每個元組對應一條記錄，每個屬性對應一列。DuckDB的Python包將這個概念帶入Python本身，讓你可以像查詢資料函式庫中的表或檢視一樣查詢不同的物件。

使用Python API讀取CSV資料

要演示如何使用DuckDB的Python API查詢資料，首先需要匯入資料。我們將使用第2章中介紹的Populations CSV檔案。使用read_csv函式可以直接從Python API讀取CSV檔案，而不需要透過SQL函式。

import duckdb

# 建立一個記憶體中的資料函式庫連線
con = duckdb.connect(database=':memory:')

# 安裝並載入httpfs擴充套件
con.execute("INSTALL httpfs")
con.execute("LOAD httpfs")

# 使用read_csv函式讀取CSV檔案
rel = con.read_csv('https://example.com/populations.csv')

# 查詢讀取的資料
result = con.sql('SELECT * FROM rel')
result.show()

內容解密：

con = duckdb.connect(database=':memory:')：建立一個記憶體中的DuckDB資料函式庫連線。
con.execute("INSTALL httpfs")：安裝httpfs擴充套件，這是一次性的操作。
con.execute("LOAD httpfs")：載入httpfs擴充套件，每次初始化新的資料函式庫時都需要執行。
rel = con.read_csv('https://example.com/populations.csv')：使用read_csv函式讀取指定的CSV檔案，並將其作為一個可查詢的關係傳回。
result = con.sql('SELECT * FROM rel')：對讀取的資料執行SQL查詢。
result.show()：顯示查詢結果。

使用關聯式API的優勢

使用DuckDB的關聯式API可以直接在Python中查詢各種資料來源，而不需要額外的SQL陳述式轉換。這種方法提供了無縫的整合，使得在Python專案中使用SQL資料函式庫和Python資料結構變得更加方便。

使用關聯式API進行資料操作

DuckDB的關聯式API提供了一種以程式設計方式建立和操作SQL查詢的方法。這個API的核心是DuckDBPyRelation物件，它代表了一個可查詢的關聯式資料結構。

建立關聯式物件

首先，我們需要從CSV檔案中讀取資料並建立一個關聯式物件：

population = con.read_csv("https://bit.ly/3KoiZR0")

這個操作不會立即執行查詢，但會傳回一個DuckDBPyRelation物件。

查詢關聯式物件

我們可以使用execute方法來執行SQL查詢，就像在Python中直接對DuckDB執行查詢一樣：

con.execute("SELECT * from population limit 2").fetchall()

不過，關聯式API提供了更多功能，讓我們可以逐步建立查詢。

計算記錄數

我們可以使用count方法來計算關聯式物件中的記錄數：

(population
 .count("*")
 .show()
)

這將輸出記錄數的結果。

內容解密：

count("*")方法用於計算關聯式物件中的記錄數。
show()方法用於觸發查詢的執行並顯示結果。
結果以表格形式呈現，包含count_star()的值。

將資料儲存到表格

為了避免每次查詢都重新下載CSV檔案，我們可以使用to_table方法將資料儲存到DuckDB表格中：

population.to_table("population")

然後，我們可以透過table函式來存取這個表格：

population_table = con.table("population")

現在，population_table是一個新的關聯式物件，代表著儲存的表格。

組合查詢

關聯式API提供了一系列方法來增強資料操作功能，包括：

filter：根據提供的謂詞函式篩選記錄。
project：只傳回指定的欄位。
limit：傳回前n條記錄。
aggregate：應用提供的聚合表示式。
order：根據提供的欄位排序記錄。

使用範例

假設我們想要找出人口超過1000萬的國家，並傳回前5個國家的國家名稱和人口數：

(population_table
 .filter('Population > 10000000')
 .project("Country, Population")
 .limit(5)
 .show()
)

這將輸出篩選和投影後的結果。

內容解密：

filter('Population > 10000000')用於篩選人口超過1000萬的記錄。
project("Country, Population")用於只傳回國家名稱和人口數欄位。
limit(5)用於限制輸出結果為前5條記錄。
show()用於觸發查詢的執行並顯示結果。

重用查詢元件

我們可以將查詢的一部分儲存到變數中，以便重用：

over_10m = population_table.filter('Population > 10000000')

這樣，我們就可以在不同的查詢中重用這個篩選條件。

內容解密：

將篩選條件儲存到over_10m變數中。
可以在後續查詢中重用這個變數，以保持查詢的一致性和可維護性。

與Python生態系統整合：深入解析DuckDB的關聯式API

DuckDB作為一個高效能的資料函式庫系統，能夠與Python生態系統無縫整合，提供強大的資料處理能力。在本章中，我們將探討如何使用DuckDB的關聯式API來進行複雜的資料查詢和分析。

使用聚合函式進行資料分析

首先，讓我們考慮一個實際的例子。假設我們有一個包含各國人口資料的表格，我們想要找出平均人口超過1000萬的大洲和地區，並按照人口數量進行排序。我們可以使用aggregate和order函式來實作這一目標。

(over_10m
.aggregate("Region, CAST(avg(Population) AS int) as pop")
.order("pop DESC")
)

內容解密：

over_10m是一個已經被定義的關系（relation），代表人口超過1000萬的國家。
aggregate函式用於計算每個地區的平均人口，並將結果命名為pop。
CAST(avg(Population) AS int)將平均人口轉換為整數型別。
order("pop DESC")按照平均人口數量進行降序排序。

執行上述查詢後，我們得到以下結果：

┌─────────────────────────────────────┬───────────┐ │ Region │ pop │ │ varchar │ int32 │ ├─────────────────────────────────────┼───────────┤ │ ASIA (EX. NEAR EAST) │ 192779730 │ │ NORTHERN AMERICA │ 165771574 │ │ LATIN AMER. & CARIB │ 48643375 │ │ C.W. OF IND. STATES │ 48487549 │ │ WESTERN EUROPE │ 38955933 │ │ NORTHERN AFRICA │ 38808343 │ │ NEAR EAST │ 32910924 │ │ SUB-SAHARAN AFRICA │ 30941436 │ │ EASTERN EUROPE │ 23691959 │ │ OCEANIA │ 20264082 │ ├─────────────────────────────────────┴───────────┤ │ 10 rows 2 columns │ └─────────────────────────────────────────────────┘

篩選特定條件下的國家

進一步地，我們可能對那些不僅人口眾多，而且人均GDP超過1萬美元的國家感興趣。這時，我們可以在over_10m關繫上應用額外的篩選條件：

(over_10m
.filter('"GDP ($ per capita)" > 10000')
.count("*")
)

內容解密：

.filter('"GDP ($ per capita)" > 10000')篩選出人均GDP超過1萬美元的國家。
.count("*")計算滿足條件的國家數量。

結果顯示，有20個國家滿足這一條件，表明這些國家不僅人口眾多，而且經濟實力較強。

多關系操作的應用

在實際應用中，我們經常需要對多個關系進行操作。DuckDB的關聯式API提供了豐富的功能來支援這些操作，包括except_、intersect、join和union等。

使用`except_`函式

假設我們需要分析人口少於1000萬的國家，可以透過排除over_10m關系中的國家來實作：

(population_table
.except_(over_10m)
.aggregate("""
Region,
CAST(avg(population) AS int) AS population,
count(*)
""")
)

內容解密：

population_table.except_(over_10m)排除人口超過1000萬的國家。
.aggregate計算每個地區的平均人口和國家數量。

結果如下：

┌─────────────────────────────────────┬────────────┬──────────────┐ │ Region │ population │ count_star() │ │ varchar │ int32 │ int64 │ ├─────────────────────────────────────┼────────────┼──────────────┤ │ EASTERN EUROPE │ 5426538 │ 9 │ │ OCEANIA │ 643379 │ 20 │ │ WESTERN EUROPE │ 2407190 │ 19 │ │ LATIN AMER. & CARIB │ 2154024 │ 35 │ │ C.W. OF IND. STATES │ 5377686 │ 7 │ │ NEAR EAST │ 2773978 │ 11 │ │ SUB-SAHARAN AFRICA │ 3322228 │ 30 │ │ NORTHERN AMERICA │ 43053 │ 3 │ │ ASIA (EX. NEAR EAST) │ 2796374 │ 9 │ │ BALTICS │ 2394991 │ 3 │ │ NORTHERN AFRICA │ 3086881 │ 2 │ ├─────────────────────────────────────┴────────────┴──────────────┤ │ 11 rows 3 columns │ └─────────────────────────────────────────────────────────────────┘

圖表解析

此圖示展示了不同地區人口少於1000萬的國家的平均人口和數量分佈。

@startuml
skinparam backgroundColor #FEFEFE
skinparam defaultTextAlignment center
skinparam rectangleBackgroundColor #F5F5F5
skinparam rectangleBorderColor #333333
skinparam arrowColor #333333

title 圖表解析

rectangle "5426538" as node1
rectangle "643379" as node2
rectangle "2407190" as node3
rectangle "2154024" as node4
rectangle "5377686" as node5
rectangle "2773978" as node6
rectangle "3322228" as node7
rectangle "43053" as node8
rectangle "2796374" as node9
rectangle "2394991" as node10
rectangle "3086881" as node11

node1 --> node2
node2 --> node3
node3 --> node4
node4 --> node5
node5 --> node6
node6 --> node7
node7 --> node8
node8 --> node9
node9 --> node10
node10 --> node11

@enduml

此圖示說明：

各地區人口少於1000萬的國家的平均人口和數量。
EASTERN EUROPE地區有9個國家，平均人口約542萬。
OCEANIA地區有20個國家，平均人口約64萬。

使用`intersect`函式找出特定地區的人口大國

接下來，讓我們找出東歐地區人口超過1000萬的國家。首先，我們篩選出東歐地區的國家：

eastern_europe = population_table \
.filter("Region ~ '.*EASTERN EUROPE.*'")

然後，使用intersect函式找出同時存在於eastern_europe和over_10m關系中的國家：

(eastern_europe
.intersect(over_10m)
.project("Country, Population")
)

內容解密：

eastern_europe.intersect(over_10m)找出同時滿足東歐地區和人口超過1000萬條件的國家。
.project("Country, Population")只保留國家名稱和人口數量兩個欄位。

結果如下：

┌─────────────────┬────────────┐ │ Country │ Population │ │ varchar │ int64 │ ├─────────────────┼────────────┤ │ Czech Republic │ 10235455 │ │ Poland │ 38536869 │ │ Romania │ 22303552 │ └─────────────────┴────────────┘

圖案匹配功能

DuckDB支援多種圖案匹配方式，包括LIKE、SIMILAR TO、GLOB和正規表示式等。這些功能可以幫助我們更靈活地進行資料篩選和查詢。

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。

DuckDB高效處理Excel檔案實戰

使用 DuckDB 處理 Excel 檔案

讀取 Excel 檔案

內容解密：

Excel 檔案中的公式處理

寫入 Excel 檔案

內容解密：

與 Python 生態系統的整合

安裝 DuckDB Python 套件

開啟資料函式庫連線

使用 DuckDB 的 Python API

使用關聯式API與DuckDB的Python整合

關聯式API的基本概念

使用Python API讀取CSV資料

內容解密：

使用關聯式API的優勢

使用關聯式API進行資料操作

建立關聯式物件

查詢關聯式物件

計算記錄數

內容解密：

將資料儲存到表格

組合查詢

使用範例

內容解密：

重用查詢元件

內容解密：

與Python生態系統整合：深入解析DuckDB的關聯式API

使用聚合函式進行資料分析

內容解密：

篩選特定條件下的國家

內容解密：

多關系操作的應用

使用except_函式

內容解密：

圖表解析

此圖示說明：

使用intersect函式找出特定地區的人口大國

內容解密：

圖案匹配功能

玄貓 BlackCat

使用`except_`函式

使用`intersect`函式找出特定地區的人口大國