Visualizing Real Time

Flight Data

ShiSh

Data Source

http://www.flightstats.com

API Access to Real Time Flight Data

http://developer.flightstats.com

Register for a developer account

Tools Used

• Microsoft Excel 2013 http://bit.ly/Kl0tcp

• Microsoft Power Query Preview for Excel http://bit.ly/1kiymZi

• Power Map Preview for Excel 2013 http://bit.ly/ZLP5gO

Use FlightStats Flex APIs

I will be using the Delay Index to

identify airports with delays in real

time

The Weather API will be used to

retrieve weather conditions for airports having

delays

APIs Used

• The DelayIndexService API returns a measure of the level of departure

delays a specified airport is currently experiencing• A low index (0-1) indicates that departures are running

relatively smoothly. A higher index (4-5) is indicative of significant delays and flight operations disruptions.

• The index is updated every 30 minutes.

• The Weather API includes METAR, TAF (Terminal Aerodrome/Area Forecast), and Zone Forecasts.

• METAR reports provide up-to-date information on current weather conditions at an airport

Interactive Documentation

Use the API Interactive

Documentation to build out the

Query Strings for Power Query

I used the Delay Indexes by Region

format

Building the Query String

Use the Interactive Documentation to Build

the Query String

Your AppID & AppKey

Just the Data for the large

airports

Filtering for airports with a Delay Index >=3

Use this Query String in Power

Query

Using Power Query

https://api.flightstats.com:443/flex/delayindex/rest/v1/json/region/North-America?appId=YourAppID&appKey=YourAppKey&classification=1&score=3

Use the Query String generated using the Interactive Documentation:

Data Acquisition

Click on List

Right-Click & Convert to Table

Click & Select the fields you want to

import

Data Transformation

Change Data Type to Date /Time

/Timezone

Expand Airport Data

Rename Fields

Transformed Data In Excel:

Automatic Updates

1. Click Design2. Under Refresh, Select Connection

Properties

I set the refresh freqto 30mins to match

Delay Index updates

Data Visualization

Data Visualization using Power View

for Excel

This data was captured Jan 1, 2014 at

2:30PM PST

I have set the data to automatically

update every 30 minutes

Access Weather Data for Airports

• Use Interactive Documentation for

generating Query String for Weather Data

• The goal is to get airport weather conditions

for airports facing delays

• For generating the query use a sample

aiport code: I used SEA.

Copy this Query String and use it in

Power Query

https://api.flightstats.com:443/flex/weather/rest/v1/json/metar/SEA?appId=YourAppID&appKey=YourAppKey

Acquire Weather Data for one Airport

1. Click metarRecord

2. Change Name of Query

3. Expand Weather

condiitions

4. Click Weather

Conditions to expand

5. Click on Record to see

data

Create Re-usable Function for Weather API … 1

Data returned for

SEA

This is weather data for Seattle. We need to change this

to a parameterized query so that we get weather

conditions for any airport facing delays

Click here to edit the

query

Edit this to make this a

parameterized query

The Query name will become the

name of the custom function

Create Re-usable Function for Weather API … 2

(AirportCode)=>let

Source = Json.Document(Web.Contents("https://api.flightstats.com:443/flex/weather/rest/v1/json/metar/"& (AirportCode)

&"?appId=YourAppID&appKey=YourAppKey"),1252),

metar = Source[metar],

conditions = metar[conditions],

weatherConditions = conditions[weatherConditions],

weatherConditions1 = weatherConditions{0}

in

weatherConditions1

Add the AirportCode variable as shown below in the Query and replace SEA with the variable in the Query string as shown.

Add This

Include the variable in the query string

The AirportWeather function can now be invoked with an

AirportCode

Combining Weather Data with Airport Delays

Edit the DelayIndex Query to add a new column that shows weather conditions for the Airports:

Insert Custom Column

Call the custom AirportWeather Function and

pass the FAA Code of the Airport to it

Have Fun Exploring the Data using Power View &

Power Map