DuckDB高效JSON資料處理技巧解析

– 20 分鐘閱讀

DuckDB 提供了強大的 JSON 處理能力，能有效應對資料分析中常見的 JSON 格式資料。不同於傳統關聯式資料函式庫，DuckDB 不需要預先定義 schema 即可載入 JSON 資料，大幅提升了處理效率。本文將深入解析 DuckDB 的 JSON 處理技巧，涵蓋資料載入、查詢、分析與視覺化等導向，並提供最佳實務與程式碼範例，協助讀者快速上手。

在資料科學領域，我們經常會遇到各種不同結構的 JSON 資料。DuckDB 的 read_json_auto() 函式能自動偵測 JSON 結構並載入資料，省去手動定義 schema 的繁瑣步驟。對於結構更複雜的 JSON 資料，read_json() 函式提供了更精細的控制，允許指定資料型別和巢狀結構。此外，DuckDB 也支援使用 COPY FROM 命令從檔案系統載入 JSON 資料，方便批次處理大量資料。搭配 SQL 查詢語法，我們可以輕鬆地從 JSON 資料中提取所需資訊，進行聚合、篩選和排序等操作。結合 Python 和 Matplotlib 等視覺化工具，更能將分析結果以圖表形式呈現，提升資料洞察力。

探索機場間的航班頻率：玄貓的分析視角

在航空資料分析中，瞭解不同機場之間的航班頻率是個常見的需求。透過航班資料，我們可以深入瞭解哪些航線最受歡迎，以及航空公司如何安排航班。現在，讓我們一起來看看如何使用 DuckDB 進行這項分析，並從玄貓的角度分享一些獨到的見解。

找出最繁忙的航線：起飛機場分析

首先，讓我們從最簡單的任務開始：找出每個機場的起飛航班數量。以下 SQL 查詢可以幫助我們達成這個目標：

SELECT
    ORIGIN_AIRPORT,
    COUNT(ORIGIN_AIRPORT) AS COUNT
FROM
    flights
GROUP BY
    ORIGIN_AIRPORT
ORDER BY
    COUNT DESC;

這段程式碼會列出每個機場的起飛航班總數，並依航班數量從多到少排序。例如，亞特蘭大機場（ATL）的起飛航班數量最多，這反映了該機場作為重要航空樞紐的地位。

內容解密

  • SELECT ORIGIN_AIRPORT, COUNT(ORIGIN_AIRPORT) AS COUNT: 選擇起飛機場程式碼和對應的航班數量，並將數量命名為 COUNT。
  • FROM flights: 從 flights 表格中讀取資料。
  • GROUP BY ORIGIN_AIRPORT: 根據起飛機場程式碼進行分組，以便計算每個機場的航班數量。
  • ORDER BY COUNT DESC: 依照航班數量降序排列，從而找出最繁忙的起飛機場。

熱門航線揭秘：起飛與目的地機場分析

更進一步，我們可以分析每對起飛機場和目的地機場之間的航班數量。這能讓我們瞭解哪些航線最受歡迎。以下 SQL 查詢可以達成這個目標：

SELECT
    ORIGIN_AIRPORT,
    DESTINATION_AIRPORT,
    COUNT(*) AS COUNT
FROM
    flights
GROUP BY
    ORIGIN_AIRPORT,
    DESTINATION_AIRPORT
ORDER BY
    COUNT DESC;

這段程式碼會列出每對機場之間的航班總數，並依航班數量從多到少排序。例如，舊金山國際機場（SFO）和洛杉磯國際機場（LAX）之間的航班最頻繁，這反映了這兩個城市之間頻繁的商務和旅遊活動。

內容解密

  • SELECT ORIGIN_AIRPORT, DESTINATION_AIRPORT, COUNT(*) AS COUNT: 選擇起飛機場程式碼、目的地機場程式碼和對應的航班數量，並將數量命名為 COUNT。
  • FROM flights: 從 flights 表格中讀取資料。
  • GROUP BY ORIGIN_AIRPORT, DESTINATION_AIRPORT: 根據起飛機場程式碼和目的地機場程式碼進行分組，以便計算每對機場之間的航班數量。
  • ORDER BY COUNT DESC: 依照航班數量降序排列，從而找出最繁忙的航線。

航空公司視角：達美航空（DL）的航線分析

如果我們想更深入瞭解特定航空公司的航線，例如達美航空（DL），我們可以修改上述查詢：

SELECT
    ORIGIN_AIRPORT,
    DESTINATION_AIRPORT,
    COUNT(*) AS COUNT
FROM
    flights
WHERE
    AIRLINE = 'DL'
GROUP BY
    ORIGIN_AIRPORT,
    DESTINATION_AIRPORT
ORDER BY
    COUNT DESC;

這段程式碼會列出達美航空每對機場之間的航班總數，並依航班數量從多到少排序。例如，拉瓜迪亞機場（LGA）和亞特蘭大機場（ATL）之間的航班是達美航空最熱門的航線。

內容解密

  • SELECT ORIGIN_AIRPORT, DESTINATION_AIRPORT, COUNT(*) AS COUNT: 選擇起飛機場程式碼、目的地機場程式碼和對應的航班數量，並將數量命名為 COUNT。
  • FROM flights: 從 flights 表格中讀取資料。
  • WHERE AIRLINE = 'DL': 篩選出航空公司程式碼為 ‘DL’ 的航班，即達美航空的航班。
  • GROUP BY ORIGIN_AIRPORT, DESTINATION_AIRPORT: 根據起飛機場程式碼和目的地機場程式碼進行分組，以便計算每對機場之間的航班數量。
  • ORDER BY COUNT DESC: 依照航班數量降序排列，從而找出達美航空最繁忙的航線。

航班取消分析：找出取消率最高的航線

除了航班頻率，航班取消也是一個重要的指標。讓我們來看看如何使用 DuckDB 找出取消航班的相關資訊。

達美航空（DL）的航班取消分析

首先，我們可以找出達美航空在 2015 年取消的航班數量：

SELECT
    ORIGIN_AIRPORT,
    DESTINATION_AIRPORT,
    COUNT(*) AS COUNT
FROM
    flights
WHERE
    AIRLINE = 'DL'
    AND CANCELLED = 1
GROUP BY
    ORIGIN_AIRPORT,
    DESTINATION_AIRPORT
ORDER BY
    COUNT DESC;

這段程式碼會列出達美航空取消的每對機場之間的航班總數，並依取消數量從多到少排序。

內容解密

  • SELECT ORIGIN_AIRPORT, DESTINATION_AIRPORT, COUNT(*) AS COUNT: 選擇起飛機場程式碼、目的地機場程式碼和對應的航班數量，並將數量命名為 COUNT。
  • FROM flights: 從 flights 表格中讀取資料。
  • WHERE AIRLINE = 'DL' AND CANCELLED = 1: 篩選出航空公司程式碼為 ‘DL’ 與 CANCELLED 欄位為 1 的航班，即達美航空取消的航班。
  • GROUP BY ORIGIN_AIRPORT, DESTINATION_AIRPORT: 根據起飛機場程式碼和目的地機場程式碼進行分組，以便計算每對機場之間取消的航班數量。
  • ORDER BY COUNT DESC: 依照取消航班數量降序排列，從而找出達美航空取消次數最多的航線。

衡量取消率：取消百分比分析

瞭解取消航班的百分比比單純的數量更有意義。以下 SQL 查詢可以計算達美航空的航班取消百分比：

SELECT
    ORIGIN_AIRPORT,
    DESTINATION_AIRPORT,
    (SUM(CANCELLED) * 100.0) / COUNT(*) AS CANCELLED_PERCENT
FROM
    flights
WHERE
    AIRLINE = 'DL'
GROUP BY
    ORIGIN_AIRPORT,
    DESTINATION_AIRPORT
ORDER BY
    CANCELLED_PERCENT DESC;

這段程式碼會列出達美航空每對機場之間的航班取消百分比，並依取消百分比從高到低排序。例如，波士頓機場（BOS）和拉瓜迪亞機場（LGA）之間的航班取消率較高。

內容解密

  • SELECT ORIGIN_AIRPORT, DESTINATION_AIRPORT, (SUM(CANCELLED) * 100.0) / COUNT(*) AS CANCELLED_PERCENT: 選擇起飛機場程式碼、目的地機場程式碼和對應的取消百分比，並將百分比命名為 CANCELLED_PERCENT。
    • SUM(CANCELLED): 計算取消航班的總數（CANCELLED 欄位為 1 的總和）。
    • COUNT(*): 計算總航班數。
    • (SUM(CANCELLED) * 100.0) / COUNT(*): 計算取消百分比。
  • FROM flights: 從 flights 表格中讀取資料。
  • WHERE AIRLINE = 'DL': 篩選出航空公司程式碼為 ‘DL’ 的航班，即達美航空的航班。
  • GROUP BY ORIGIN_AIRPORT, DESTINATION_AIRPORT: 根據起飛機場程式碼和目的地機場程式碼進行分組，以便計算每對機場之間的取消百分比。
  • ORDER BY CANCELLED_PERCENT DESC: 依照取消百分比降序排列，從而找出達美航空取消率最高的航線。

整體取消率：達美航空的整體表現

若要了解達美航空的整體取消率，可以使用以下查詢：

SELECT
    (SUM(CANCELLED) * 100.0) / COUNT(*) AS CANCELLED_PERCENT
FROM
    flights
WHERE
    AIRLINE = 'DL';

這段程式碼會計算達美航空在 2015 年取消的所有航班的百分比，結果顯示約為 0.44%。

內容解密

  • SELECT (SUM(CANCELLED) * 100.0) / COUNT(*) AS CANCELLED_PERCENT: 選擇取消百分比，並將百分比命名為 CANCELLED_PERCENT。
    • SUM(CANCELLED): 計算取消航班的總數（CANCELLED 欄位為 1 的總和）。
    • COUNT(*): 計算總航班數。
    • (SUM(CANCELLED) * 100.0) / COUNT(*): 計算取消百分比。
  • FROM flights: 從 flights 表格中讀取資料。
  • WHERE AIRLINE = 'DL': 篩選出航空公司程式碼為 ‘DL’ 的航班，即達美航空的航班。

航空公司取消率比較：找出表現最佳的航空公司

單一航空公司的取消率可能不夠有說服力，因此，我們需要比較不同航空公司的取消率。以下程式碼可以計算所有航空公司的取消率，並使用 Matplotlib 繪製長條圖：

import matplotlib.pyplot as plt
import duckdb
conn = duckdb.connect('flights.duckdb')

df = conn.execute('''
SELECT
    AIRLINE,
    (SUM(CANCELLED) * 100.0) / COUNT(*) AS CANCELLED_PERCENT
FROM
    flights
GROUP BY
    AIRLINE
ORDER BY
    CANCELLED_PERCENT DESC
''').df()

df.plot(kind='bar', x='AIRLINE', y='CANCELLED_PERCENT')
plt.xlabel('航空公司')
plt.ylabel('取消百分比')
plt.title('不同航空公司的取消百分比')
plt.show()

這段程式碼首先計算每家航空公司的取消百分比，然後使用長條圖顯示結果。從圖表中可以看出，達美航空的取消率並不算太差，在所有航空公司中排名倒數第三。

內容解密

  • import matplotlib.pyplot as plt: 匯入 Matplotlib 函式庫，用於繪製圖表。
  • import duckdb: 匯入 DuckDB 函式庫，用於連線和查詢 DuckDB 資料函式庫。
  • conn = duckdb.connect('flights.duckdb'): 連線到名為 flights.duckdb 的 DuckDB 資料函式庫。
  • df = conn.execute(...): 執行 SQL 查詢，並將結果儲存在 Pandas DataFrame 中。
    • SELECT AIRLINE, (SUM(CANCELLED) * 100.0) / COUNT(*) AS CANCELLED_PERCENT: 選擇航空公司程式碼和對應的取消百分比，並將百分比命名為 CANCELLED_PERCENT。
      • SUM(CANCELLED): 計算取消航班的總數（CANCELLED 欄位為 1 的總和）。
      • COUNT(*): 計算總航班數。
      • (SUM(CANCELLED) * 100.0) / COUNT(*): 計算取消百分比。
    • FROM flights: 從 flights 表格中讀取資料。
    • GROUP BY AIRLINE: 根據航空公司程式碼進行分組，以便計算每家航空公司的取消百分比。
    • ORDER BY CANCELLED_PERCENT DESC: 依照取消百分比降序排列，從而找出取消率最高的航空公司。
  • df.plot(kind='bar', x='AIRLINE', y='CANCELLED_PERCENT'): 使用 Pandas DataFrame 的 plot 函式繪製長條圖。
    • kind='bar': 指定圖表型別為長條圖。
    • x='AIRLINE': 指定 x 軸的資料為航空公司程式碼。
    • y='CANCELLED_PERCENT': 指定 y 軸的資料為取消百分比。
  • plt.xlabel('航空公司'): 設定 x 軸標籤為 “航空公司”。
  • plt.ylabel('取消百分比'): 設定 y 軸標籤為 “取消百分比”。
  • plt.title('不同航空公司的取消百分比'): 設定圖表標題為 “不同航空公司的取消百分比”。
  • plt.show(): 顯示圖表。

顯示航空公司全名：JOIN 操作

長條圖的 x 軸標籤顯示的是航空公司程式碼，如果能顯示完整的航空公司名稱會更好。為此，我們需要使用 JOIN 操作將 flights 表格和 airlines 表格連線起來：

import matplotlib.pyplot as plt
import duckdb
conn = duckdb.connect('flights.duckdb')

df = conn.execute('''
SELECT
    a.AIRLINE,
    (SUM(f.CANCELLED) * 100.0) / COUNT(*) AS CANCELLED_PERCENT
FROM
    flights f
JOIN
    airlines a ON f.AIRLINE = a.IATA_CODE
GROUP BY
    a.AIRLINE
ORDER BY
    CANCELLED_PERCENT DESC
''').df()

df.plot(kind='bar', x='AIRLINE', y='CANCELLED_PERCENT')
plt.xlabel('航空公司')
plt.ylabel('取消百分比')
plt.title('不同航空公司的取消百分比')
plt.show()

這段程式碼使用 JOIN 操作將 flights 表格和 airlines 表格連線起來，並顯示完整的航空公司名稱。

內容解密

  • import matplotlib.pyplot as plt: 匯入 Matplotlib 函式庫，用於繪製圖表。
  • import duckdb: 匯入 DuckDB 函式庫，用於連線和查詢 DuckDB 資料函式庫。
  • conn = duckdb.connect('flights.duckdb'): 連線到名為 flights.duckdb 的 DuckDB 資料函式庫。
  • df = conn.execute(...): 執行 SQL 查詢，並將結果儲存在 Pandas DataFrame 中。
    • SELECT a.AIRLINE, (SUM(f.CANCELLED) * 100.0) / COUNT(*) AS CANCELLED_PERCENT: 選擇航空公司名稱和對應的取消百分比，並將百分比命名為 CANCELLED_PERCENT。
      • a.AIRLINE: 從 airlines 表格中選擇航空公司名稱。
      • SUM(f.CANCELLED): 計算取消航班的總數（從 flights 表格中選擇 CANCELLED 欄位為 1 的總和）。
      • COUNT(*): 計算總航班數。
      • (SUM(f.CANCELLED) * 100.0) / COUNT(*): 計算取消百分比。
    • FROM flights f JOIN airlines a ON f.AIRLINE = a.IATA_CODE: 從 flights 表格和 airlines 表格中讀取資料，並使用 JOIN 操作將兩個表格連線起來。
      • f.AIRLINE = a.IATA_CODE: 指定連線條件為 flights 表格的 AIRLINE 欄位和 airlines 表格的 IATA_CODE 欄位相等。
    • GROUP BY a.AIRLINE: 根據航空公司名稱進行分組，以便計算每家航空公司的取消百分比。
    • ORDER BY CANCELLED_PERCENT DESC: 依照取消百分比降序排列，從而找出取消率最高的航空公司。
  • df.plot(kind='bar', x='AIRLINE', y='CANCELLED_PERCENT'): 使用 Pandas DataFrame 的 plot 函式繪製長條圖。
    • kind='bar': 指定圖表型別為長條圖。
    • x='AIRLINE': 指定 x 軸的資料為航空公司名稱。
    • y='CANCELLED_PERCENT': 指定 y 軸的資料為取消百分比。
  • plt.xlabel('航空公司'): 設定 x 軸標籤為 “航空公司”。
  • plt.ylabel('取消百分比'): 設定 y 軸標籤為 “取消百分比”。
  • plt.title('不同航空公司的取消百分比'): 設定圖表標題為 “不同航空公司的取消百分比”。
  • plt.show(): 顯示圖表。

使用圓餅圖顯示取消百分比

除了長條圖，我們還可以使用圓餅圖來顯示取消百分比：

import matplotlib.pyplot as plt
import duckdb
conn = duckdb.connect('flights.duckdb')

df = conn.execute('''
SELECT
    a.AIRLINE,
    (SUM(f.CANCELLED) * 100.0) / COUNT(*) AS CANCELLED_PERCENT
FROM
    flights f
JOIN
    airlines a ON f.AIRLINE = a.IATA_CODE
GROUP BY
    a.AIRLINE
ORDER BY
    CANCELLED_PERCENT DESC
''').df()

ax = df.plot(kind='pie',
            x='AIRLINE',
            y='CANCELLED_PERCENT',
            labels = df['AIRLINE'],
            autopct = '%.0f%%',
            legend=False
            )
ax.get_yaxis().set_visible(False)
plt.xlabel('航空公司')
plt.title('不同航空公司的取消百分比')
plt.show()

這段程式碼使用圓餅圖顯示每家航空公司的取消百分比，讓讀者可以更直觀地比較不同航空公司的表現。

內容解密

  • import matplotlib.pyplot as plt: 匯入 Matplotlib 函式庫，用於繪製圖表。
  • import duckdb: 匯入 DuckDB 函式庫，用於連線和查詢 DuckDB 資料函式庫。
  • conn = duckdb.connect('flights.duckdb'): 連線到名為 flights.duckdb 的 DuckDB 資料函式庫。
  • df = conn.execute(...): 執行 SQL 查詢，並將結果儲存在 Pandas DataFrame 中。
    • SELECT a.AIRLINE, (SUM(f.CANCELLED) * 100.0) / COUNT(*) AS CANCELLED_PERCENT: 選擇航空公司名稱和對應的取消百分比，並將百分比命名為 CANCELLED_PERCENT。
      • a.AIRLINE: 從 airlines 表格中選擇航空公司名稱。
      • SUM(f.CANCELLED): 計算取消航班的總數（從 flights 表格中選擇 CANCELLED 欄位為 1 的總和）。
      • COUNT(*): 計算總航班數。
      • (SUM(f.CANCELLED) * 100.0) / COUNT(*): 計算取消百分比。
    • FROM flights f JOIN airlines a ON f.AIRLINE = a.IATA_CODE: 從 flights 表格和 airlines 表格中讀取資料，並使用 JOIN 操作將兩個表格連線起來。
      • f.AIRLINE = a.IATA_CODE: 指定連線條件為 flights 表格的 AIRLINE 欄位和 airlines 表格的 IATA_CODE 欄位相等。
    • GROUP BY a.AIRLINE: 根據航空公司名稱進行分組，以便計算每家航空公司的取消百分比。
    • ORDER BY CANCELLED_PERCENT DESC: 依照取消百分比降序排列，從而找出取消率最高的航空公司。
  • ax = df.plot(kind='pie', ...): 使用 Pandas DataFrame 的 plot 函式繪製圓餅圖。
    • kind='pie': 指定圖表型別為圓餅圖。
    • x='AIRLINE': 指定圓餅圖的標籤為航空公司名稱。
    • y='CANCELLED_PERCENT': 指定圓餅圖的大小為取消百分比。
    • labels = df['AIRLINE']: 設定圓餅圖的標籤為航空公司名稱。
    • autopct = '%.0f%%': 設定圓餅圖上顯示的百分比格式。
    • legend=False: 隱藏圖例。
  • ax.get_yaxis().set_visible(False): 隱藏 y 軸。
  • plt.xlabel('航空公司'): 設定 x 軸標籤為 “航空公司”。
  • plt.title('不同航空公司的取消百分比'): 設定圖表標題為 “不同航空公司的取消百分比”。
  • plt.show(): 顯示圖表。

每週航班數量分析：找出航班較少的日子

除了取消率，瞭解每週不同日子的航班數量也很有用。這可以幫助我們找出哪些日子航班較少。

找出每週航班數量

以下程式碼可以找出每週不同日子的航班數量：

import duckdb
conn = duckdb.connect('flights.duckdb')

df_flights_day_of_week = conn.execute('''
SELECT
    day_of_week,
    origin_airport,
    destination_airport,
    COUNT(*) AS flight_count
FROM
    flights
WHERE
    CANCELLED = 0  -- Exclude cancelled flights
GROUP BY
    day_of_week,
    origin_airport,
    destination_airport
ORDER BY
    day_of_week,
    origin_airport,
    destination_airport;
''').df()

print(df_flights_day_of_week)

這段程式碼會列出每週不同日子、不同機場組合的航班數量，並排除取消的航班。

內容解密

  • import duckdb: 匯入 DuckDB 函式庫，用於連線和查詢 DuckDB 資料函式庫。
  • conn = duckdb.connect('flights.duckdb'): 連線到名為 flights.duckdb 的 DuckDB 資料函式庫。
  • df_flights_day_of_week = conn.execute(...): 執行 SQL 查詢，並將結果儲存在 Pandas DataFrame 中。
    • SELECT day_of_week, origin_airport, destination_airport, COUNT(*) AS flight_count: 選擇星期幾、起飛機場程式碼、目的地機場程式碼和對應的航班數量，並將數量命名為 flight_count。
    • FROM flights: 從 flights 表格中讀取資料。
    • WHERE CANCELLED = 0: 排除取消的航班。
    • GROUP BY day_of_week, origin_airport, destination_airport: 根據星期幾、起飛機場程式碼和目的地機場程式碼進行分組，以便計算每週不同日子、不同機場組合的航班數量。
    • ORDER BY day_of_week, origin_airport, destination_airport: 依照星期幾、起飛機場程式碼和目的地機場程式碼排序結果。
  • print(df_flights_day_of_week): 顯示 DataFrame 的內容。

透過這些分析，玄貓可以更深入地瞭解航空資料，並為航空公司提供有價值的見解。

在這次的航空資料探索中，玄貓帶領大家使用 DuckDB 分析了航班頻率、取消率以及每週航班數量等關鍵指標。這些分析不僅能幫助我們瞭解航空公司的營運狀況，也能為旅客提供更有用的資訊，例如選擇哪家航空公司比較不容易遇到航班取消，或是哪幾天搭飛機可能比較不擁擠。希望這些分析能讓大家對航空資料分析有更深入的瞭解，並能應用到實際生活中。

探索航班資料：從機場程式碼驗證到延遲時段分析

在航空資料分析中，瞭解航班的各種特性對於提升營運效率和改善乘客體驗至關重要。玄貓將分享如何使用 DuckDB 進行探索性資料分析（EDA），從驗證機場程式碼到找出航班延遲最常發生的時段。

機場程式碼的奧秘

在處理航班資料時，首先會注意到機場程式碼的格式不一致。有些機場使用標準的字母程式碼（例如 SFO、LAX），而有些則使用數字程式碼。為了確認這一點，可以使用以下 SQL 查詢來檢查 ORIGIN_AIRPORT 和 DESTINATION_AIRPORT 欄位中非字母數字值：

SELECT YEAR, MONTH, DAY, ORIGIN_AIRPORT, DESTINATION_AIRPORT
FROM flights
WHERE ORIGIN_AIRPORT NOT SIMILAR TO '[A-Za-z]+'
OR DESTINATION_AIRPORT NOT SIMILAR TO '[A-Za-z]+';

這個查詢可以幫助我們識別出使用數字程式碼的機場，這些程式碼通常代表較小的機場、貨運樞紐或未指定的地點。

航班組合分析

瞭解特定航線的航班數量對於航空公司來說至關重要。以下是如何按星期幾對每個航班組合的航班數量進行分組：

SELECT
    DAY_OF_WEEK,
    ORIGIN_AIRPORT,
    DESTINATION_AIRPORT,
    COUNT(*) AS flight_count
FROM
    flights
GROUP BY
    DAY_OF_WEEK,
    ORIGIN_AIRPORT,
    DESTINATION_AIRPORT
ORDER BY
    DAY_OF_WEEK,
    ORIGIN_AIRPORT,
    DESTINATION_AIRPORT;

這個查詢將顯示每週每一天特定航線的航班數量，有助於航空公司瞭解哪些航線在哪些日子最繁忙。

例如，若要繪製從 SFO 飛往 LAX 的航班數量，可以使用以下程式碼：

from_airport = 'SFO'
to_airport = 'LAX'
df_flights_result = df_flights_day_of_week.query(
    f'ORIGIN_AIRPORT=="{from_airport}" & DESTINATION_AIRPORT=="{to_airport}"')
df_flights_result.plot(kind='bar',
                      x='DAY_OF_WEEK',
                      y='flight_count',
                      legend=False)
plt.xlabel('Day of Week')
plt.ylabel('Number of Flights')
plt.title(f'Number of Flights from {from_airport} to {to_airport}')
plt.xticks(df_flights_result['DAY_OF_WEEK'] - 1,
           ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun'])
plt.show()

這段程式碼將生成一個條形圖，顯示從 SFO 飛往 LAX 的航班數量在每週不同日子的變化情況。

航班百分比分析

除了航班數量，計算每個工作日的航班百分比也很有用。以下查詢計算了每個工作日特定起飛地和目的地機場的航班百分比，並排除了已取消的航班：

WITH t1 AS (
    SELECT
        day_of_week,
        origin_airport,
        destination_airport,
        COUNT(*) AS flight_count_per_weekday
    FROM
        flights
    WHERE
        CANCELLED = 0  -- Exclude cancelled flights
    GROUP BY
        day_of_week,
        origin_airport,
        destination_airport
    ORDER BY
        day_of_week,
        origin_airport,
        destination_airport
),
t2 AS (
    SELECT
        origin_airport,
        destination_airport,
        count(*) AS total_flight_count
    FROM
        flights
    WHERE
        CANCELLED = 0  -- Exclude cancelled flights
    GROUP BY
        origin_airport,
        destination_airport
)
SELECT
    t1.origin_airport,
    t1.destination_airport,
    t1.day_of_week,
    t2.total_flight_count,
    100.0 * (t1.flight_count_per_weekday / t2.total_flight_count) AS
        percent_flights_on_weekday
FROM t1
JOIN t2
ON
    t1.origin_airport = t2.origin_airport AND
    t1.destination_airport = t2.destination_airport;

這個查詢使用了兩個 CTE（Common Table Expressions）來計算每個工作日的航班數量和總航班數量，然後計算百分比。

航班延遲時段分析

另一個有趣的統計資料是航班延遲最常發生的時間。玄貓將一天分為四個時段：

  • 凌晨 12 點到早上 6 點
  • 早上 6 點到中午 12 點
  • 中午 12 點到下午 6 點
  • 下午 6 點到午夜 12 點

以下 SQL 查詢可以找出每個時段的航班延遲情況：

SELECT
    DAY_OF_WEEK,
    CASE
        WHEN SCHEDULED_DEPARTURE BETWEEN '0000' AND '0559' THEN '00:00-06:00'
        WHEN SCHEDULED_DEPARTURE BETWEEN '0600' AND '1159' THEN '06:00-12:00'
        WHEN SCHEDULED_DEPARTURE BETWEEN '1200' AND '1759' THEN '12:00-18:00'
        WHEN SCHEDULED_DEPARTURE BETWEEN '1800' AND '2400' THEN '18:00-24:00'
        ELSE 'Other'
    END AS DEPARTURE_TIME_INTERVAL,
    AVG(ARRIVAL_DELAY) AS AVG_ARRIVAL_DELAY
FROM
    flights
WHERE
    ARRIVAL_DELAY > 0
GROUP BY
    DAY_OF_WEEK,
    CASE
        WHEN SCHEDULED_DEPARTURE BETWEEN '0000' AND '0559' THEN '00:00-06:00'
        WHEN SCHEDULED_DEPARTURE BETWEEN '0600' AND '1159' THEN '06:00-12:00'
        WHEN SCHEDULED_DEPARTURE BETWEEN '1200' AND '1759' THEN '12:00-18:00'
        WHEN SCHEDULED_DEPARTURE BETWEEN '1800' AND '2400' THEN '18:00-24:00'
        ELSE 'Other'
    END
ORDER BY
    DAY_OF_WEEK, DEPARTURE_TIME_INTERVAL;

這個查詢將顯示每週每一天各個時段的平均到達延遲時間，有助於航空公司瞭解哪些時段最容易發生延遲。

總之，透過使用 DuckDB 進行 EDA，我們可以深入瞭解航班資料的各種特性，從驗證機場程式碼到找出航班延遲最常發生的時段。這些分析結果可以幫助航空公司提升營運效率和改善乘客體驗。



### 航班延遲分析：找出最佳飛行時間與航空公司選擇

在資料分析中，視覺化是不可或缺的一環。單看 DataFrame 的數值可能難以洞察，因此玄貓將使用長條圖來呈現每個時段的航班延遲情況。首先，需要對 DataFrame 進行重塑（pivot），將星期幾（DAY_OF_WEEK）設為索引，起飛時間間隔（DEPARTURE_TIME_INTERVAL）設為欄位。

```python
df_delays_by_week_pivot = df_delays_by_week.pivot(
    index='DAY_OF_WEEK',
    columns='DEPARTURE_TIME_INTERVAL',
    values='AVG_ARRIVAL_DELAY'
)
df_delays_by_week_pivot

這個重塑後的 DataFrame 能夠更清晰地展示航班延遲與星期幾和起飛時間的關係。接下來，玄貓將繪製長條圖：

# 繪製長條圖
df_delays_by_week_pivot.plot(kind='bar',
                            stacked=False,
                            figsize=(10, 6))

# 更新 X 軸標籤，顯示星期幾
days_of_week = ['Monday', 'Tuesday', 'Wednesday',
                'Thursday', 'Friday', 'Saturday', 'Sunday']
plt.xticks(ticks=range(len(days_of_week)), labels=days_of_week, rotation=0)
plt.title('平均抵達延遲時間（依起飛時間與星期幾區分）')
plt.xlabel('星期幾')
plt.ylabel('平均抵達延遲時間（分鐘）')
plt.legend(title='起飛時間間隔', bbox_to_anchor=(1, 1))
plt.tight_layout()  # 調整佈局，避免標籤重疊
plt.show()

內容解密：

  1. df_delays_by_week_pivot.plot(kind='bar', stacked=False, figsize=(10, 6)): 這行程式碼使用 Pandas DataFrame 的 plot 函式來繪製長條圖。
    • kind='bar'：指定繪製長條圖。
    • stacked=False：設定長條圖不堆積疊顯示。
    • figsize=(10, 6)：設定圖表大小為寬 10 英寸，高 6 英寸。
  2. days_of_week = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']: 定義一個包含星期幾名稱的列表。
  3. plt.xticks(ticks=range(len(days_of_week)), labels=days_of_week, rotation=0): 這行程式碼設定 X 軸的刻度和標籤。
    • ticks=range(len(days_of_week))：設定刻度位置為 0 到 6，對應星期幾的數量。
    • labels=days_of_week：設定刻度標籤為星期幾的名稱。
    • rotation=0：設定標籤不旋轉。
  4. plt.title('平均抵達延遲時間（依起飛時間與星期幾區分）'): 設定圖表標題。
  5. plt.xlabel('星期幾'): 設定 X 軸標籤。
  6. plt.ylabel('平均抵達延遲時間（分鐘）'): 設定 Y 軸標籤。
  7. plt.legend(title='起飛時間間隔', bbox_to_anchor=(1, 1)): 設定圖例標題和位置。
    • title='起飛時間間隔'：設定圖例的標題。
    • bbox_to_anchor=(1, 1)：設定圖例的位置在圖表的右上角。
  8. plt.tight_layout(): 調整圖表佈局，避免標籤重疊。
  9. plt.show(): 顯示圖表。

從長條圖中，玄貓可以觀察到：

  • 晚間時段（下午 6 點到午夜 12 點）的平均延遲時間最長。
  • 在大多數日子裡（除了週四、週五和週日），上午時段（早上 6 點到中午 12 點）的平均延遲時間最短。
  • 如果想要儘量避免航班延遲，可以考慮在週四或週五的清晨（午夜 12 點到早上 6 點）出行。

航空公司延遲分析：找出最準時與最常延遲的航空公司

瞭解了最佳飛行時間後，接下來玄貓將分析哪些航空公司的航班延遲情況最嚴重，哪些航空公司最準時。首先，玄貓統計每家航空公司的延遲航班數量：

df_most_delays = conn.execute("""
SELECT
    count(airlines.AIRLINE) as Count,
    airlines.AIRLINE
FROM flights, airlines
WHERE airlines.IATA_CODE = flights.AIRLINE AND flights.ARRIVAL_DELAY > 0
GROUP BY airlines.AIRLINE
ORDER BY COUNT DESC
""").df()
df_most_delays

這個查詢結果顯示了每家航空公司延遲航班的總數。然而，單純比較延遲航班的數量可能不夠公平。例如，一家航空公司每天只有兩班航班，其中一班延遲，雖然延遲航班只有一班，但延遲比例卻高達 50%。

更準確的方法是計算每家航空公司的航班延遲百分比。以下 SQL 查詢可以實作這個目標：

df_percent_delay = conn.execute("""
WITH flight_delays AS (
    SELECT
        AIRLINE,
        1.0 * count(*) as TotalFlights,
        1.0 * sum(case when ARRIVAL_DELAY > 0 then 1 else 0 end) as Delays,
        (1.0 * sum(case when ARRIVAL_DELAY > 0 then 1 else 0 end) /
        count(*)) * 100 as Percentage
    FROM flights
    GROUP BY AIRLINE
)
SELECT
    flight_delays.Percentage,
    airlines.IATA_CODE,
    airlines.AIRLINE
FROM flight_delays
JOIN airlines ON airlines.IATA_CODE = flight_delays.AIRLINE
ORDER BY flight_delays.Percentage DESC;
""").df()
df_percent_delay

內容解密：

  1. WITH flight_delays AS (...): 這部分定義了一個名為 flight_delays 的 Common Table Expression (CTE)。CTE 允許你定義一個臨時的結果集，然後在後面的查詢中參照它。
  2. SELECT AIRLINE, 1.0 * count(*) as TotalFlights, ... FROM flights GROUP BY AIRLINE: 這個 SELECT 陳述式計算每個航空公司的總航班數 (TotalFlights)、延遲航班數 (Delays) 和延遲百分比 (Percentage)。
    • 1.0 * count(*) as TotalFlights: 計算每個航空公司的總航班數。1.0 * 是為了確保結果是浮點數，以便後續計算百分比。
    • 1.0 * sum(case when ARRIVAL_DELAY > 0 then 1 else 0 end) as Delays: 計算每個航空公司的延遲航班數。CASE WHEN 陳述式用於判斷 ARRIVAL_DELAY 是否大於 0，如果是，則計數為 1，否則計數為 0。
    • (1.0 * sum(case when ARRIVAL_DELAY > 0 then 1 else 0 end) / count(*)) * 100 as Percentage: 計算每個航空公司的延遲百分比。
  3. SELECT flight_delays.Percentage, airlines.IATA_CODE, airlines.AIRLINE FROM flight_delays JOIN airlines ON airlines.IATA_CODE = flight_delays.AIRLINE ORDER BY flight_delays.Percentage DESC: 這個 SELECT 陳述式從 flight_delays CTE 和 airlines 表中選擇需要的欄位，並按照延遲百分比降序排列。
    • JOIN airlines ON airlines.IATA_CODE = flight_delays.AIRLINE: 將 flight_delays CTE 和 airlines 表連線起來，使用 IATA_CODE 作為連線鍵。

接下來，玄貓使用長條圖來視覺化結果：

plt.bar(df_percent_delay['AIRLINE'],
        df_percent_delay['Percentage'],
        color='skyblue')
plt.title('各航空公司航班延遲百分比')
plt.xlabel('航空公司')
plt.ylabel('航班延遲百分比')
plt.xticks(rotation=90)
plt.grid(axis='y')
plt.show()

內容解密：

  1. plt.bar(df_percent_delay['AIRLINE'], df_percent_delay['Percentage'], color='skyblue'): 這行程式碼使用 Matplotlib 的 bar 函式繪製長條圖。
    • df_percent_delay['AIRLINE']: 設定 X 軸的資料為航空公司的名稱。
    • df_percent_delay['Percentage']: 設定 Y 軸的資料為航班延遲百分比。
    • color='skyblue'：設定長條圖的顏色為天藍色。
  2. plt.title('各航空公司航班延遲百分比'): 設定圖表標題。
  3. plt.xlabel('航空公司'): 設定 X 軸標籤。
  4. plt.ylabel('航班延遲百分比'): 設定 Y 軸標籤。
  5. plt.xticks(rotation=90): 設定 X 軸標籤旋轉 90 度，以便更好地顯示。
  6. plt.grid(axis='y'): 在 Y 軸上顯示網格線。
  7. plt.show(): 顯示圖表。

從圖表中可以看出，精神航空（Spirit Air Lines）的航班延遲情況最嚴重，有 48.46% 的航班沒有準時。

如果想找出最準時（甚至提前抵達）的航空公司，只需將 SQL 查詢中的 > 符號改為 <= 即可：

df_percent_on_time = conn.execute("""
WITH flight_delays AS (
    SELECT
        AIRLINE,
        1.0 * count(*) as TotalFlights,
        1.0 * sum(case when ARRIVAL_DELAY <= 0 then 1 else 0 end) as OnTimeFlights,
        (1.0 * sum(case when ARRIVAL_DELAY <= 0 then 1 else 0 end) /
        count(*)) * 100 as Percentage
    FROM flights
    GROUP BY AIRLINE
)
SELECT
    flight_delays.Percentage,
    airlines.IATA_CODE,
    airlines.AIRLINE
FROM flight_delays
JOIN airlines ON airlines.IATA_CODE = flight_delays.AIRLINE
ORDER BY flight_delays.Percentage DESC;
""").df()
df_percent_on_time

同樣地，玄貓繪製長條圖來視覺化結果：

plt.bar(df_percent_on_time['AIRLINE'],
        df_percent_on_time['Percentage'],
        color='skyblue')
plt.title('各航空公司航班準點百分比')
plt.xlabel('航空公司')
plt.ylabel('航班準點百分比')
plt.xticks(rotation=90)
plt.grid(axis='y')
plt.show()

從圖表中可以看出，達美航空（Delta Air Lines）的航班準點率最高，有 70.72% 的航班準時（或提前）抵達。 身為玄貓（BlackCat），我將根據提供的章節內容，重新創作並探討如何在 DuckDB 中使用 JSON 檔案。

DuckDB 與 JSON 檔案：玄貓的實戰解析

在資料處理的實務中，JSON (JavaScript Object Notation) 格式因其靈活性和動態結構而廣受開發者歡迎。與需要預先定義結構的格式不同，JSON 允許使用動態的鍵值對來表示資料。玄貓將帶領大家深入瞭解在 DuckDB 中處理 JSON 檔案的各種技巧。

本文將探討如何將不同結構的 JSON 檔案載入 DuckDB，並根據玄貓的經驗，針對不同情境推薦最適合的函式。

JSON 格式：玄貓的快速導覽

JSON 是一種輕量級的資料交換格式，易於人類閱讀和理解，同時也方便電腦解析和生成。JSON 支援以下資料型別：

  • 物件 (Object)
  • 字串 (String)
  • 布林值 (Boolean)
  • 數值 (Number)
  • 陣列 (Array)
  • 空值 (Null)

讓玄貓來詳細說明這些資料型別。

物件 (Object)

物件是由鍵值對組成的無序集合，使用大括號 {} 包圍。例如：

{
  "key": "value"
}

物件也可以是空的，表示不包含任何鍵值對：

{}

字串 (String)

物件的鍵必須是字串，而值可以是字串、布林值、數值、陣列、空值或另一個物件。例如：

{
  "firstName": "John"
}

一個物件可以有多個鍵值對，彼此之間用逗號 , 分隔：

{
  "firstName": "John",
  "lastName": "Doe"
}

注意： 物件中的鍵必須是唯一的。以下 JSON 字串無效，因為 firstName 鍵重複出現：

{
  "firstName": "John",
  "firstName": "Doe"
}

布林值 (Boolean)

布林值可以是 true 或 false：

{
  "firstName": "John",
  "lastName": "Doe",
  "isMember": true,
  "single": false
}

數值 (Number)

數值可以是整數或浮點數：

{
  "firstName": "John",
  "lastName": "Doe",
  "isMember": true,
  "single": false,
  "weight": 79.5,
  "height": 1.73,
  "children": 3
}

巢狀物件 (Nested Object)

鍵的值也可以是另一個物件，形成巢狀結構：

{
  "firstName": "John",
  "lastName": "Doe",
  "isMember": true,
  "single": false,
  "weight": 79.5,
  "height": 1.73,
  "children": 3,
  "address": {
    "line1": "123 Street",
    "line2": "San Francisco",
    "state": "CA",
    "postal": "12345"
  }
}

陣列 (Array)

陣列是由物件組成的有序序列，使用中括號 [] 包圍，各物件之間用逗號 , 分隔：

{
  "firstName": "John",
  "lastName": "Doe",
  "isMember": true,
  "single": false,
  "weight": 79.5,
  "height": 1.73,
  "children": 3,
  "address": {
    "line1": "123 Street",
    "line2": "San Francisco",
    "state": "CA",
    "postal": "12345"
  },
  "phone": [
    {
      "type": "work",
      "number": "1234567"
    },
    {
      "type": "home",
      "number": "8765432"
    },
    {
      "type": "mobile",
      "number": "1234876"
    }
  ]
}

空值 (Null)

當鍵沒有值時，可以賦予 null 值：

{
  "firstName": "John",
  "lastName": "Doe",
  "isMember": true,
  "single": false,
  "weight": 79.5,
  "height": 1.73,
  "children": 3,
  "address": {
    "line1": "123 Street",
    "line2": "San Francisco",
    "state": "CA",
    "postal": "12345"
  },
  "phone": [
    {
      "type": "work",
      "number": "1234567"
    },
    {
      "type": "home",
      "number": "8765432"
    },
    {
      "type": "mobile",
      "number": "1234876"
    }
  ],
  "oldMembershipNo": null
}

掌握了 JSON 的基本知識後，現在讓玄貓帶領大家學習如何將 JSON 檔案載入 DuckDB。

將 JSON 檔案載入 DuckDB：玄貓的實用技巧

讓玄貓以 json1.json 檔案為例，其內容如下：

[
  {
    "id": 1,
    "name": "Sarah Johnson",
    "address": "4321 Oak Street Apartment 304 Los Angeles, CA 90001",
    "email": "sarah_johnson478@gmail.com",
    "weight": 140.50
  },
  {
    "id": 2,
    "name": "David Martinez",
    "address": "789 Maple Avenue Suite 102 New York, NY 10001",
    "email": "david_martinez431@gmail.com",
    "weight": 155.0
  },
  {
    "id": 3,
    "name": "Emily Wilson",
    "address": "567 Pine Road Unit 5B Chicago, IL 60601",
    "email": "emily_wilson998@gmail.com",
    "weight": 200.1
  }
]

此 JSON 檔案包含一個 JSON 物件陣列，每個物件包含以下欄位：

  • id
  • name
  • address
  • email
  • weight

玄貓將介紹如何使用以下方法將不同結構的 JSON 檔案載入 DuckDB：

  • read_json_auto()
  • read_json()
  • COPY-FROM

讓玄貓從 read_json_auto() 函式開始。

使用 read_json_auto() 函式

read_json_auto() 函式是載入 JSON 檔案最簡便的方法。以下是如何使用它的範例：

import duckdb
conn = duckdb.connect()
conn.execute('''
SELECT
  *
FROM read_json_auto('json1.json')
''').df()

這段程式碼會傳回一個包含五個欄位的表格。

若要將表格儲存在 DuckDB 中，可以修改查詢如下：

conn.execute('''
CREATE TABLE People AS
SELECT *
FROM read_json_auto('json1.json')
''')

read_json_auto() 函式會自動解析 JSON 檔案中的鍵值對，並將其載入 DuckDB。結果會是一個包含五個欄位的表格，欄位名稱與 JSON 檔案中的鍵值對應。

在 DuckDB CLI 中，使用以下 SQL 查詢載入 JSON 檔案，會看到類別似以下的輸出：

SELECT *
FROM read_json_auto('json1.json');

輸出結果會顯示每個欄位的資料型別，這在需要了解載入資料的型別時特別有用。

玄貓

玄貓

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