本篇以實際的美國飛機航班延誤紀錄資料,示範在實務上如何處理與分析巨量資料。

飛機航班的延誤會直接對旅客造成不便,因此我們會希望藉著分析班機誤點的資料,找出可能造成誤點的原因,進而嘗試降低航班延誤的頻率,不過這類的資料量通常都非常龐大,比較難以傳統的方式來處理。


以下我們將使用各種巨量資料處理工具,分析美國的航班誤點資料。

Airline on-time performance 資料集

美國統計協會(ASA)在 2009 年發布了一份 Airline on-time performance 資料集(亦可從 BTS 的網站篩選欄位與下載),裡面包含了全美國從 1987 年 10 月份至 2008 年 4 月份的航班延誤紀錄,這份資料集相當龐大,壓縮後的資料就有 1.6 GB,而未壓縮的話則有 12 GB。

Airline on-time performance 原始的資料可以從美國統計協會的網站下載,它是把資料依照年份分成多個逗點分隔檔(csv 檔),並且以 bzip2 壓縮起來,請先下載這些壓縮檔,並且將其解壓縮,在 Linux 下的話可以用 wget 加上 bzip2 指令來撰寫 shell 指令搞,自動下載並解壓縮:

#!/usr/bin/bash
# 用迴圈自動下載並解壓縮所有的資料
for year in {1987..2008}
do
  echo "下載 $year 年的資料"
  wget http://stat-computing.org/dataexpo/2009/$year.csv.bz2

  echo "解壓縮 $year 年的資料"
  bzip2 -d $year.csv.bz2
done

若是在 Windows 中則推薦以 7-Zip 這個軟體解壓縮。

每個 CSV 檔案都有 29 個欄位,各個欄位的說明請參考美國統計協會的網站上的敘述,接下來我們就可以開始分析裡面的資料了。

基本統計問題

由於 Airline on-time performance 的資料量相當大,直接以 Excel 看原始的資料是沒有太大的意義的,一般來說我們都會使用各種工具來分析資料的各種統計資訊,至於要看那些統計資訊就因人而異了,以下我們示範幾種常見的基本統計量的計算方式:

  1. 每一年的資料筆數是多少?
  2. 一週當中每一天的資料總筆數是多少?(所有年份)。
  3. 每一年中,TailNum 欄位為 NA 的資料筆數是多少?。
  4. 哪一年的班機誤點情況最嚴重?
  5. 一週當中,星期幾的班機誤點情況最嚴重?

可用來計算這些統計量的工具有很多種,我們這裡分別以 R 語言、Linux 指令以及 SQLite 資料庫等方式來計算這些統計數值,大家可以比較各種工具的差異。

不過若僅以單一工具來分析資料,通常都會有些限制,比較好的方式是善用各種工具的長處,總合起來分析資料。

R 語言

若要在 R 中計算每一年的資料筆數,直覺作法就是把 CSV 的資料讀進來,再計算每一年的資料筆數:

# 計算每一年的資料筆數
for(year in 1987:2008) {
  x <- read.csv(paste0(year, ".csv"))
  cat(year, "年:", nrow(x), "\n")

  # 刪除不用的資料
  rm(x)

  # 回收記憶體
  gc()
}
1987 年: 1311826
1988 年: 5202096
1989 年: 5041200
1990 年: 5270893
1991 年: 5076925
1992 年: 5092157
1993 年: 5070501
1994 年: 5180048
1995 年: 5327435
1996 年: 5351983
1997 年: 5411843
1998 年: 5384721
1999 年: 5527884
2000 年: 5683047
2001 年: 5967780
2002 年: 5271359
2003 年: 6488540
2004 年: 7129270
2005 年: 7140596
2006 年: 7141922
2007 年: 7453215
2008 年: 7009728

若要計算一週當中每一天的資料總筆數,可以這樣寫:

# 計算一週當中每一天的資料總筆數
dayCount <- rep(0, 7)
for(year in 1987:2008) {
  x <- read.csv(paste0(year, ".csv"))
  for(i in 1:7) {
    dayCount[i] <- dayCount[i] + sum(x[,"DayOfWeek"] == i)
  }
  # 刪除不用的資料
  rm(x)

  # 回收記憶體
  gc()
}
dayCount
[1] 18136111 18061938 18103222 18083800 18091338 15915382 17143178

若要計算每一年中 TailNum 欄位為 NA 的資料筆數,可以這樣寫:

# 計算每一年中 TailNum 欄位為 NA 的資料筆數
for(year in 1987:2008) {
  x <- read.csv(paste0(year, ".csv"))
  cat(year, "年:", sum(is.na(x[,"TailNum"])), "\n")

  # 刪除不用的資料
  rm(x)

  # 回收記憶體
  gc()
}
1987 年: 1311826
1988 年: 5202096
1989 年: 5041200
1990 年: 5270893
1991 年: 5076925
1992 年: 5092157
1993 年: 5070501
1994 年: 5180048
1995 年: 0
1996 年: 0
1997 年: 0
1998 年: 0
1999 年: 0
2000 年: 0
2001 年: 0
2002 年: 0
2003 年: 0
2004 年: 0
2005 年: 0
2006 年: 0
2007 年: 0
2008 年: 0

若要計算每一年班機平均的起飛與到達延遲時間,可以這樣寫:

# 計算每一年班機平均的起飛與到達延遲時間
for(year in 1987:2008) {
  x <- read.csv(paste0(year, ".csv"))
  cat(year, "年:", mean(x[,"ArrDelay"], na.rm = TRUE), "/",
    mean(x[,"DepDelay"], na.rm = TRUE), "\n")

  # 刪除不用的資料
  rm(x)

  # 回收記憶體
  gc()
}
1987 年: 9.446699 / 8.063638
1988 年: 6.54735 / 6.706768
1989 年: 8.406396 / 8.202822
1990 年: 6.809947 / 6.909441
1991 年: 4.736445 / 5.753782
1992 年: 4.879558 / 5.688272
1993 年: 5.344062 / 6.122581
1994 年: 5.66249 / 6.659922
1995 年: 7.024064 / 8.278766
1996 年: 9.673284 / 9.991075
1997 年: 7.490578 / 8.235566
1998 年: 7.586968 / 9.024561
1999 年: 8.246601 / 9.337101
2000 年: 10.47289 / 11.28068
2001 年: 5.528249 / 8.154837
2002 年: 3.191244 / 5.532276
2003 年: 3.596694 / 5.248231
2004 年: 6.508066 / 7.892314
2005 年: 7.181343 / 8.674313
2006 年: 8.68284 / 10.09364
2007 年: 10.19218 / 11.39914
2008 年: 8.168452 / 9.97257

若要計算一週當中每一天班機平均的起飛與到達延遲時間,則可這樣寫:

# 計算一週當中每一天班機平均的起飛與到達延遲時間
arrDelaySum <- rep(0, 7)
arrDelayCount <- rep(0, 7)
depDelaySum <- rep(0, 7)
depDelayCount <- rep(0, 7)
for(year in 1987:2008) {
  x <- read.csv(paste0(year, ".csv"))
  for(i in 1:7) {
    idx <- x[,"DayOfWeek"] == i
    arr <- x[idx, "ArrDelay"]
    arr <- arr[!is.na(arr)]
    dep <- x[idx, "DepDelay"]
    dep <- dep[!is.na(dep)]
    arrDelaySum[i] <- arrDelaySum[i] + sum(arr)
    arrDelayCount[i] <- arrDelayCount[i] + length(arr)
    depDelaySum[i] <- depDelaySum[i] + sum(dep)
    depDelayCount[i] <- depDelayCount[i] + length(dep)
  }
  # 刪除不用的資料
  rm(x)

  # 回收記憶體
  gc()
}
arrDelaySum / arrDelayCount
depDelaySum / depDelayCount
[1] 6.669515 5.960421 7.091502 8.945047 9.606953 4.187419 6.525040
[1]  7.850057  6.855870  7.651197  9.246910 10.151539  6.887023  8.409293

Linux 指令

由於 CSV 檔中的資料是一筆一行,所以若要在 Linux 的 shell 中計算每一年的資料筆數,可以使用 wc 指令計算每個 CSV 檔案的行數,再自己扣掉第一行的標頭行即可:

# 計算每一個 CSV 檔的行數
wc -l *.csv
    1311827 1987.csv
    5202097 1988.csv
    5041201 1989.csv
    5270894 1990.csv
    5076926 1991.csv
    5092158 1992.csv
    5070502 1993.csv
    5180049 1994.csv
    5327436 1995.csv
    5351984 1996.csv
    5411844 1997.csv
    5384722 1998.csv
    5527885 1999.csv
    5683048 2000.csv
    5967781 2001.csv
    5271360 2002.csv
    6488541 2003.csv
    7129271 2004.csv
    7140597 2005.csv
    7141923 2006.csv
    7453216 2007.csv
    7009729 2008.csv
  123534991 total

在計算一週當中每一天的資料總筆數之前,我們先把所有資料放進一個 CSV 檔案中,並去掉標頭行,這樣可以讓後續的處理比較方便一些:

# 將所有資料放進一個 CSV 檔案中,並去掉標頭行
for year in {1987..2008}
do
  tail -n+2 $year.csv >> AirlineDataNoHeader.csv
done

若要計算一週當中每一天的資料總筆數,可以使用 cut 指令將星期幾的欄位切出來,經過 sort 指令排序,最後交給 uniq 指令計算出線次數:

# 計算一週當中每一天的資料總筆數
cut -d, -f4 AirlineDataNoHeader.csv | sort | uniq -c
18136111 1
18061938 2
18103222 3
18083800 4
18091338 5
15915382 6
17143178 7

如果只要計算其中一天的資料總筆數,可以改用 grep 指令,這樣會比較快一些:

# 計算其中一天的資料總筆數
cut -d, -f4 AirlineDataNoHeader.csv | grep 6 | wc -l
15915382

若要計算每一年中 TailNum 欄位為 NA 的資料筆數,可以用 cut 指令將年份與 TailNum 欄位切出來,再用類似的方式計算:

# 計算每一年中 TailNum 欄位為 NA 的資料筆數
cut -d, -f1,11 AirlineDataNoHeader.csv | grep ',NA$' | sort | uniq -c
1311826 1987,NA
5202096 1988,NA
5041200 1989,NA
5270893 1990,NA
5076925 1991,NA
5092157 1992,NA
5070501 1993,NA
5180048 1994,NA

其餘比較複雜的計算在 Linux 的 shell 中就比較不好處理了,如果要更進一步分析的話,建議改用其他的工具。

SQLite 資料庫

資料庫可以協助我們管理較為龐大的資料,並且可以使用 SQL 的語法從大量的資料中取出有用的資訊,這裡我們以 SQLite 這個輕量級的資料庫來進行示範基本的操作方式,而這裡的 SQL 語法也可以套用在任何其他的 SQL 資料庫上。

AirlineDataNoHeader.csv 這個 CSV 檔的所在目錄下,建立一個 SQLite 資料庫檔案:

sqlite3 AirlineDelay.sqlite3

進入 SQLite shell 環境之後,先建立一個名稱為 AirlineDelay 的資料表:

CREATE TABLE AirlineDelay (
  Year int,
  Month int,
  DayofMonth int,
  DayOfWeek int,
  DepTime int,
  CRSDepTime int,
  ArrTime int,
  CRSArrTime int,
  UniqueCarrier varchar(5),
  FlightNum int,
  TailNum varchar(8),
  ActualElapsedTime int,
  CRSElapsedTime int,
  AirTime int,
  ArrDelay int,
  DepDelay int,
  Origin varchar(3),
  Dest varchar(3),
  Distance int,
  TaxiIn int,
  TaxiOut int,
  Cancelled int,
  CancellationCode varchar(1),
  Diverted varchar(1),
  CarrierDelay int,
  WeatherDelay int,
  NASDelay int,
  SecurityDelay int,
  LateAircraftDelay int
);

接著再將 AirlineDataNoHeader.csv 的資料匯入 AirlineDelay 這個資料表:

.mode csv
.import AirlineDataNoHeader.csv AirlineDelay

資料匯入完成後,就可以使用 SQL 的語法查詢各種資訊了。為了讓輸出的結果更容易閱讀,我們啟用欄位標頭,並以表格的方式輸出資料:

.header on
.mode column

若要計算每一年的資料筆數,可以在 SQLite shell 環境中執行以下 SQL 查詢指令:

-- 計算每一年的資料筆數
SELECT Year, COUNT(*) FROM AirlineDelay GROUP BY Year;
Year        COUNT(*)
----------  ----------
1987        1311826
1988        5202096
1989        5041200
1990        5270893
1991        5076925
1992        5092157
1993        5070501
1994        5180048
1995        5327435
1996        5351983
1997        5411843
1998        5384721
1999        5527884
2000        5683047
2001        5967780
2002        5271359
2003        6488540
2004        7129270
2005        7140596
2006        7141922
2007        7453215
2008        7009728

若要計算一週當中每一天的資料總筆數,則執行:

-- 計算一週當中每一天的資料總筆數
SELECT DayOfWeek, COUNT(*) FROM AirlineDelay GROUP BY DayOfWeek;
DayOfWeek   COUNT(*)
----------  ----------
1           18136111
2           18061938
3           18103222
4           18083800
5           18091338
6           15915382
7           17143178

若要計算每一年中 TailNum 欄位為 NA 的資料筆數,則執行:

-- 計算每一年中 TailNum 欄位為 NA 的資料筆數
SELECT Year, COUNT(*) FROM AirlineDelay WHERE TailNum = "NA" GROUP BY Year;
Year        COUNT(*)
----------  ----------
1987        1311826
1988        5202096
1989        5041200
1990        5270893
1991        5076925
1992        5092157
1993        5070501
1994        5180048
-- 計算每一年班機平均的起飛與到達延遲時間
SELECT Year, AVG(ArrDelay), AVG(DepDelay) FROM AirlineDelay GROUP BY Year;
Year        AVG(ArrDelay)     AVG(DepDelay)
----------  ----------------  ----------------
1987        9.27747124999809  7.94263644721175
1988        6.46604580153846  6.64209541692426
1989        8.25797806077918  8.08214393398397
1990        6.72155951562667  6.84067557432868
1991        4.68411627116808  5.70447721800105
1992        4.818018965244    5.62925082631977
1993        5.27009816189761  6.05031869631817
1994        5.57630045126995  6.57411475723777
1995        6.88905674118971  8.13594647330282
1996        9.41544302364189  9.75112383578199
1997        7.33854234130591  8.08679353780219
1998        7.36481370158268  8.78236996865762
1999        7.99617539007693  9.07645547554905
2000        10.1011063255328  10.9085196726334
2001        5.30212038647537  7.83891061667823
2002        3.14674773620996  5.46390883261793
2003        3.53413911295916  5.16615771806909
2004        6.37885828422826  7.75088347053766
2005        7.03274222487871  8.51185923415917
2006        8.51491993331767  9.92131347836059
2007        9.94886756923019  11.1532895535685
2008        7.9881812817844   9.77873620773873

若要計算一週當中,每一天班機平均的起飛與到達延遲時間,可執行:

-- 計算一週當中每一天班機平均的起飛與到達延遲時間
SELECT DayOfWeek, AVG(ArrDelay), AVG(DepDelay) FROM AirlineDelay GROUP BY DayOfWeek;
DayOfWeek   AVG(ArrDelay)     AVG(DepDelay)
----------  ----------------  ----------------
1           6.52783559827132  7.70100216082709
2           5.82249258080722  6.71302021964642
3           6.93274048122483  7.49766168696379
4           8.74715087536912  9.06447627158009
5           9.40303503256641  9.96035776900526
6           4.10892732577829  6.77322046055822
7           6.41214715264579  8.28289019690515

若要離開 SQLite shell 環境,則執行:

.exit

這裡在 SQLite shell 環境中計算的結果,跟上面 R 的計算結果有些差異,主要的原因在於對缺失值(NA)的處理方式不同,在 SQLite 中若遇到文字時,會直接當作 0 處理。除此之外是否還有其他的因素,我也不確定,但我想大部分的問題都是因為原始資料不完整或是格式問題所造成的。

接下來我們要介紹比較進階的分析方式與程式設計技巧,請繼續閱讀下一頁。