European Cargo airports - new dashboard. (2010-2012)

Turned out to be quite easy to adapt the passenger dashboard to do the same for the freight & mail data from Eurostat (avia_gor_xx). So see the link to the new dashboard above.

Flights: are commercial freight & mail flight departures (FRM_CAF_DEP in Eurostat terms)
Tonnes: are tonnes of freight & mail on board on departure (including direct transit cargo that stayed on the aircraft - FRM_BRD_DEP)

European airport departures - 2013 & 2014 data updated.

A delay in new posts, while I work on extracting a new dataset and turning it into some useful dashboards. Watch this space.

But meanwhile, the good news is that Eurostat have published updated data for 2013 and 2014, sorting out some earlier problems and adding more months. Check out the data in the 'production version' - link is at the top of the page: I've renamed the two dashboards into something more meaningful (I hope):

• Departure Patterns shows departures from the selected EU28 airport in terms of destinations, load factors, distances, aircraft sizes, pax, flights.
• Departure Points lets you select any airport (including those outside Europe) and see details of where flights leave EU28 airports to reach that point.

I've also re-size so it fits on the ipad better - but discovered Tableau menus work better in Safari than Chrome on the ipad.

Tableau - More on Top N

In my posts of 3 & 5 Nov, I was looking at how to plot monthly graphs for just the top N airports out of many. Originally I achieved this through a complicated method (3/11) creating a combined dimension, but it's rather easier than that.

This is for graphs of main departure airports to a single destination.

1. As before, create a graph by putting month in the column shelf, and one or more measures in the row shelf (eg passengers and flights).
2. Filter by year  (I only want to show a single year's seasonal cycle) and destination airport.
3. Create a calculated field which is just Index() - I named it Rank (which is more or less what it is). Check that it's discrete.
4. Filter on Rank, and from the drop-down select 'edit table calculation'.
5. Choose sort by departing pax descending (so rank 1 is the biggest) - or whatever you want to rank by.
6. In partitioning, select airport of departure.
7. When you click OK you get the description something like: Results are computed along ADEP (sorted descending by Sum of Departing Pax) for each Month of Month. In your case, in place of ADEP (=airport of departure) you will see the dimension for each value of which you want a line on your graph.
8. From the drop-down on rank in the filter pane, select filter and select the top N boxes, however many you want.

So, it still has quite a few steps, but it avoids calculating combo dimensions. 

You can see the results in the 'seasonality at main departure airports' graph in the DepFromAP dashboard. I decided not to make N user selectable, just adds complication for the user.

From EU to you: new flight dashboard

I've added a second dashboard 'DepFromAP' that looks at the Eurostat data. 

For destination airports (intended to be outside the EU, but I think you can still select inside) you can see which are the main EU28 departure points, the nature of the traffic (passengers, flights, load factors, aircraft size) and how these rankings have changed with time.

For example, you can see how in 2014 YTD, both Gatwick and Manchester have overtaken Frankfurt ranked in terms of passenger departures to Dubai.

The new dashboard is in the development and full versions (still with a caveat on 2013 & 2014 data). See links at the top of the page.

I did work out that there's an easier way to pick the top N for the seasonality graph - but I'll explain that in a later post.

Energy dashboard again - more on year to date

Getting to grips with how to partition:

1. Calculating a reference ID for each year such as Div(datediff('week',[BaseDate],[Date]),52) 
2. which can easily be converted to a readable label: STR(9+[YrID])+"/"+Str(10+[YrID])
3. and using first() to pull out the first value in the partition (or more accurately lookup(max([gas]),first()).

That's just what's needed for the energy data, which I choose to start counting from mid September ([BaseDate]), and where they are meter readings, so I need to keep taking differences to get meaningful values.

Learn About Tableau

European flight data dashboard - updated to 2014

The main dashboard is now loaded with all of the data to 2014 - as much as Eurostat currently has.

Check the 'DataAvailability' tab for a rough indication of what's available.

Beware, the 2013-2014 numbers look to be twice the size of the 2012; Eurostat are looking into this as a potential upload problem (probably safe just to halve the values, for a rough estimate - and it means load factors & seats/flight are probably still correct).

I'll freeze APFlows now and work on a different view.

Eurostat - air passenger flow data completeness

Check out the latest version of the full dashboard - now with bump chart to show main destinations varying over time.

To get a sense of the completeness of the avia_par_xx data series that I've loaded from Eurostat, here's a quick chart of the counts of pax from the data that are loaded into the dashboard.

Shows quickly that it's Greece (EL) and France (EF) where delivery seems to have stopped after 2010 - and France missed data in 2004-2006. But other States are pretty complete, even if some joined the process only recently.

The issue with French data seems to be that they reported pax on board and commercial pax flights in 2005, but not departing seats - I'll need to work out how to patch up the gap and check if it's the same issue with the Greek data.

Tableau tech - more on top N within another filter

In investigating bump charts (see development version of dashboard), there may be an easier way to select a subset of destinations for a given departure point than that in the previous post.

This Tableau advice on bump charts (example 4) helped. I used line labels directly rather than their proposed way to label the ends of the lines through other reports (which I couldn't get to work, and in any case could be more difficult if you have a variety of possible start and end years).

I'll come back later to see if I can adapt the main destinations time series chart to use the same method.

Tableau tech - Top N within another filter

Instead of the Top N, which is easy to do in Tableau, I wanted the Top N for the selected departure airport (and even for the year). It took a while to find the right search terms, but the answer is here on the Tableau site. (and on a linked page).

In brief, to get a set of line charts for the top N destination airport (ADES) from a given departure airport (ADEP) you need to do the following:

1. Set up your line chart, with Month in the column shelf say, and one or measures in the row shelf.
2. Filter by departure airport.
3. Select both ADEP and ADES (ctrl-click) and create combined dimension.
4. Drag this combo into the colour field in the measures pane, and sort by descending sum(pax) (or whatever).
5. Create calculated field which is just Index() - I named it Rank (which is more or less what it is).
6. Set it as discrete.
7. Drag to the filter.
8. Right-click and select compute using the combo field.
9. Choose the filter values (eg 1-5).

Obvious! (I hope I recalled all those steps correctly). Here's the final dashboard  - with test data only.

[For links to dashboard - production and development versions - see top of page.]

European airport flows - now complete to 2012

So the APFlows dashboard is now complete, with all the data to 2012 at least. There do seem to be some gaps when monthly data has not been provided by some States (Greece, France for some years). There was a problem earlier with the 2013 data, so I'll come back to this at a future date, after I've linked APComp to a more complete set of data.

There are two versions of the workbook 'Pax' has the full data in - 'Pax Dev' uses only a subset, so if you find only a few airports, you should check.

Here's the Pax. (The seasonality doesn't look as if it's airport of departure specific - I'll check).


[For links to dashboard - production and development versions - see top of page.]

European Airports dashboard - total versus flows

Done some more analysis of the data, and its clear that the thresholds that apply when States send data to Eurostat mean that adding up the flows doesn't give very helpful totals. Which is a shame.

So with the avia_par_xx datasets, focus on showing the flows, like this. Will come back and derive the totals from another dataset.

[For links to dashboard - production and development versions - see top of page.]

European airports dashboard - improving usability

Gradually adding more countries, but focusing more on the usability of the airport-comparison dashboard.

Tried out hierarchical menus in Tableau (countries, then airports), but not very smooth, and as I saw someone else comment, this can slow things down. 

So instead I settled for letting you search for airports and then tick them - this seems quite forgiving. Coupled with the fact that I've merged ICAO 4-letter airport codes and airport names to help readability, you can search on both at the same time.

Enjoy.

Learn About Tableau

Tech - Eurostat data

I take back what I said about the difficulty of accessing Eurostat data, having discovered SDMX, their web service method for extracting their data. Yes, it's still not trivial to use, but thankfully some kind folk from the Bank of Italy have provided an R package, RJSDMX: Attilio Mattiocco, Diana Nicoletti, Gianpaolo Lopez, thanks a lot!

So, look forward to a broader range of data becoming available soon.

Tableau - first flights

The Eurostat data on flights, seats and passengers aren't the easiest thing to handle. But here's a first attempt - a dashboard that is intended for comparing a small handful of airports.

This is a test, only Ireland, Belgium and Denmark are currently loaded. Some of the values need further checking. Oh, and I've only loaded data up to 2012 since recently it seems there was some problem with 2013.

Learn About Tableau

Tableau - Energy dashboard

Still trying to get the date axes to zoom - without success. But improved formatting, including working out how to get two curves on the same axis, and get rid of the default which is then for the right and left axes to scale differently.

As a result, something which I had not realised - how close the night and day consumption of electricity actually is.

Also found

1. That the dashboard updates in the blog automatically when I add to it.
2. That you can get drill down up (see the bubble plot) in certain circumstances.

Learn About Tableau

Tableau tech - converting from reading to rates

Technically, I didn't make my life easier by starting with an irregular series of meter readings of electricity and gas for plotting in Tableau. 

I was determined that Tableau should do all the work. I just provided a .xls file with dates and meter readings, rather than tidy up in Excel/OpenOffice. Tableau read and understood the file happily enough - though it thought 'Day' was a date rather than a reading of daytime electricity, it was simple enough to move the variable from the 'dimensions' to the 'measures' section.

I had to convert the data from readings into some sensible units within Tableau: I chose daily consumption rates.

This took 2 steps:
1) Convert the dates of the readings into number of days:
Define the measure 'DayDiff'  as 

DATEDIFF('day',lookup(Max([Date]),-1),max([Date]))

2) Then calculate the rates. For gas there is a multiplier to get to kWh (9.8805 according to my supplier)

GasRate
9.8805 * (Max([Gas]) - lookup(max([Gas]),-1))/[DayDiff]

Why you have to have the aggregate function (Max) around the variable, I don't know, but Tableau insists.

First steps with Tableau

I wanted to try out Tableau, and chose to start with some data of my own. 

After quite a lot of trial and error, I ended up with this:

Learn About Tableau

Pretty, though not interactive as I thought it might have been.
I wonder whether I'm much further on than I was when I was using R to explore the same data, and got this:

In some ways, I prefer the step graph in R, but I'll come back to that.