見出し画像
Photo by deoxyriboco

SQLでフライトデータを集計する

ベイジー

皆さん初めまして。この記事ではフライトデータをSQLで集計した結果を紹介します。この記事はフライトデータを解析している以下の記事に触発されて書かれています。

この記事ではフライトデータに関する幾つかの集計を図示しています。その結果をベンチマークとして、達成に必要なデータ集計をSQLで実行します。

使用するデータ

アメリカの運輸省運輸統計局は大手の航空会社が運行する国内便の運行実績を記録しています。彼らが公表した、2015年の運行実績のデータを集計します。データは以下から入手できます。

このサイトからは、airlines.csv, airports.csv, flights.csvという三つのファイルがダウンロードできます。今回は、airports.csv, flights.csvを用います。airports.csvには空港の情報が、flights.csvにはフライトの情報が記録されています。

データ概要

データの集計で使用しなかったカラムの説明は省きます。

airports.csv

  • IATA_CODE: 空港を識別するためのコード。

  • AIRPORT: 空港の名前。

flights.csv

  • MONTH: 月。

  • DAY: 日。

  • DAY OF WEEK: 曜日。1は月曜日、2は火曜日、など。

  • AIRLINE: 空港を識別するためのコード。airports.csvのIATA_CODEと同じ。

  • ORIGIN_AIRPORT: 出発空港。

  • DEPARTURE_DELAY: 何分出発が遅れたか。マイナスの数字の場合は出発が早まったことを示していると考えられます。

  • CANCELLED: 便が欠航した場合は1, 欠航しなかった場合は0をとる変数。

  • CANCELLATION_REASON: 欠航の要因を表す文字列。以下の4つの要因があります。A: Airline/Carrier(航空路線, キャリア), B: Weather(天候), C: National Air System(航空システム), D: SECURITY(セキュリティ上の問題)

  • AIR_SYSTEM_DELAY: システム上の理由による遅延。

  • SECURITY_DELAY: セキュリティ上の問題による遅延。

  • AIRLINE_DELAY: 航空路線の問題による遅延。

  • LATE_AIRCRAFT_DELAY: 航空機の遅れによる遅延。

  • WEATHER_DELAY: 天候による遅延。

SQL環境

JupySQLとDuckDBによりデータ集計を実行しました。設定や使い方については以下の記事を見て下さい。Jupyter Notebook上で簡単にSQLを実行することができてとても便利です。

問題設定

以下の10個の項目に関してデータ集計とその可視化を行いました。

  1. 航空会社毎のフライト数

  2. 月毎、日毎、曜日毎のフライト数

  3. .航空会社毎の欠航数および欠航確率

  4. 欠航理由の内訳

  5. 航空路線、キャリアの問題による欠航数と欠航確率

  6. 航空会社毎の遅延フライト数

  7. 遅延理由の内訳。

  8. 航空会社毎の遅延フライト数。

  9. 出発空港毎の遅延フライト数。

  10. 出発空港毎の欠航フライト数。

以下、data/ファイル名はファイルのパスを表しています。

1. 航空会社毎のフライト数

SELECT a.airline, COUNT(*) AS counts_of_flights
FROM 'data/flights.csv' AS f JOIN 'data/airlines.csv' AS  a
ON f.airline = a. iata_code GROUP BY a.airline ORDER BY count(*) DESC;

Southwest Airlinesのフライト数が最も多いことがわかります。

2.月毎、日毎、曜日毎のフライト数

SELECT month, count(*) AS 'counts_of_flights' 
FROM 'data/flights.csv' GROUP BY month ORDER BY MONTH;

フライト数はJulyで最も多く、Februaryで最も小さいです。2月は日数が少ないことが影響しているかもしれません。

SELECT day, count(*) AS 'counts_of_flights' 
FROM 'data/flights.csv' GROUP BY day ORDER BY day;

Day of monthが31において大きく下がっています。これは31日が存在しない月があることによるものでしょう。

SELECT day_of_week, count(*) AS 'counts_of_flights' 
FROM 'data/flights.csv' GROUP BY day_of_week ORDER BY day_of_week;

Saturdayにおいてフライト数は最も小さいということがわかります。

3.航空会社毎の欠航数および欠航確率

SELECT a.airline, 
SUM(CASE WHEN f.cancelled = '1' THEN 1 ELSE 0 END) AS 'counts_cancellation',
SUM(CASE WHEN f.cancelled = '1' THEN 1 ELSE 0 END) * 100/ COUNT(*) AS 'cancellation_percentage'
FROM 'data/flights.csv' AS f
JOIN 'data/airlines.csv' AS a
ON f.airline = a.iata_code
GROUP BY a.airline;

第二行でキャンセルされた便の数を計算しています。

欠航数、欠航確率ともに最も多いのはAmerican Eagle Airlinesです。

4.欠航理由の内訳

SELECT cancellation_reason, COUNT(*) AS count FROM 'data/flights.csv' 
WHERE cancelled = '1' 
AND cancellation_reason IS NOT NULL
GROUP BY cancellation_reason
ORDER BY cancellation_reason;

cancellation_reasonにはnullが含まれているので集計から除外します。

天候による欠航が最も多いようです。

5.航空路線、キャリアの問題による欠航数および欠航確率

SELECT a.airline AS airline,
SUM(CASE WHEN cancellation_reason = 'A' THEN 1 ELSE 0 END) 
AS 'counts_cancelled_flights',
SUM(CASE WHEN cancellation_reason = 'A' THEN 1 ELSE 0 END) * 100/ COUNT(*) 
AS 'percentage_cancellation_airline'
FROM 'data/flights.csv' AS f
JOIN 'data/airlines.csv' AS a
ON f.airline = a.iata_code
GROUP BY a.airline;

空港毎にcancellation_reasonがAであったレコードの総数を取得しています。その総数を空港毎のレコード数で割ることで、空港毎に航空路線、キャリアの問題による欠航の確率を計算できます。

航空路線、キャリアの問題によって最も多くの欠航が生じたのはSouthwest Airlinesですが、欠航確率が最も高かったのはAmerican Eagle Airlinesです。

6.航空会社毎の遅延フライト数

SELECT a.airline AS airline, 
COUNT(*) AS total_flights,
SUM(CASE WHEN f.departure_delay > 0 THEN 1 ELSE 0 END) AS delayed_flights,
AVG(f.departure_delay) as average_delay
FROM 'data/flights.csv' AS f
JOIN 'data/airlines.csv' AS a
ON f.airline = a.iata_code
GROUP BY a.airline;

まず空港でレコードをグループ分けします。次に、遅れが生じた便、すなわちdeparture_delayが0より大きいレコードの総数を取得します。

遅延フライト数が最も多かったのはSouthwest Airlinesでした。これはそもそもフライト数が多いことによると考えられます。

7.遅延理由の内訳

SELECT SUM(CASE WHEN air_system_delay > 0 THEN 1 ELSE 0 END) AS 'air system_delay',
SUM(CASE WHEN security_delay > 0 THEN 1 ELSE 0 END) AS 'security_delay',
SUM(CASE WHEN airline_delay> 0 THEN 1 ELSE 0 END) AS 'airline_delay',
SUM(CASE WHEN late_aircraft_delay > 0 THEN 1 ELSE 0 END) AS 'aircraft_delay',
SUM(CASE WHEN weather_delay > 0 THEN 1 ELSE 0 END) AS 'weather_delay'
FROM 'data/flights.csv';

各遅延要因が0より大きいレコードの数を取得しています。

遅延の主な理由は、airline_delay, air_system_delay, weather_delayによるものだとわかります。

8.出発空港毎の遅延フライト数

SELECT a.airline AS airline, 
COUNT(*) AS 'counts_of_delays'
FROM 'data/flights.csv' AS f
JOIN 'data/airlines.csv' AS a
ON f.airline = a.iata_code
WHERE f.airline_delay > 0
GROUP BY a.airline;

まず、遅延が生じているものにレコードを絞り込みます(WHERE句)。次に空港毎にグループ分けして総数を取得しています。

ORDにおいて遅延フライト数が最も多いことがわかります。

9.出発空港毎の欠航数

SELECT origin_airport AS 'origin_airport', COUNT(*) AS 'counts_cancelled'
FROM 'data/flights.csv'
WHERE cancelled = '1'
GROUP BY origin_airport
HAVING counts_cancelled > 2000
ORDER BY counts_cancelled DESC;

cancelledが1であるものに絞り込まれたレコードの集合を、origin_airportでグループ分けします。集約の結果、欠航が2000より多かったもののみを取得します(HAVING句)

遅延と同様に、ORDにおいて欠航が最も多いようです。

まとめ

SQLで2015年アメリカのフライトデータを集計してみました。うまく集計し、可視化すると、数字の羅列のみからはわからない知見が得られます。今後もさまざまなデータについてこのような集計、分析を行っていく予定です。
なお、今回の集計に使用したjupyter notebookを公開しています。


この記事が気に入ったらサポートをしてみませんか？