本篇以實際的美國飛機航班延誤紀錄資料，示範在實務上如何處理與分析巨量資料。
飛機航班的延誤會直接對旅客造成不便，因此我們會希望藉著分析班機誤點的資料，找出可能造成誤點的原因，進而嘗試降低航班延誤的頻率，不過這類的資料量通常都非常龐大，比較難以傳統的方式來處理。
以下我們將使用各種巨量資料處理工具，分析美國的航班誤點資料。
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 看原始的資料是沒有太大的意義的，一般來說我們都會使用各種工具來分析資料的各種統計資訊，至於要看那些統計資訊就因人而異了，以下我們示範幾種常見的基本統計量的計算方式：
- 每一年的資料筆數是多少？
- 一週當中每一天的資料總筆數是多少？（所有年份）。
- 每一年中，
TailNum欄位為NA的資料筆數是多少？。 - 哪一年的班機誤點情況最嚴重？
- 一週當中，星期幾的班機誤點情況最嚴重？
可用來計算這些統計量的工具有很多種，我們這裡分別以 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 處理。除此之外是否還有其他的因素，我也不確定，但我想大部分的問題都是因為原始資料不完整或是格式問題所造成的。
接下來我們要介紹比較進階的分析方式與程式設計技巧，請繼續閱讀下一頁。
繼續閱讀： 12
