Challenging data tasks: the making of "where are you flying?"

• The continent abbreviation data was almost fine: I just read in the data table, except that the abbreviation for North America, "NA", was interpreted by R as meaning "the data is Not Available", so I had to take account of that.
  
   
• The format of the country names in the Wikipedia data file is something like "Afghanistan, Islamic Republic of" (just taking the first country on the list).  Some countries don't have a comma, for example "American Samoa".  That's not too bad; I can handle that.  I started by checking that each country appears only once in the table, as problems will arise later if that's not the case.  This is relatively easy to do in R, using something like:
  
  continents_raw %>% group_by(Name) %>% count() %>% filter(n > 1)
  
  Oh dear.  It turned out that some countries appear twice in the data file, mostly those spanning Europe and Asia.  There are also interesting and significant cases (at least for my purposes) such as Madeira, which is part of Portugal but lies on the continental shelf of Africa: there are many flights to Madeira listed in the CAA flight data file (as I discovered later).  A decision had to be made for each of these.  I decided that most of the Europe/Asia countries should be classified as Asia for this purpose, for no particularly good reason.  However, for the Russian Federation, I decided to regard it as lying entirely in Europe: almost all of the flights are to Moscow or St Petersburg, which are in Europe.  This was the first set of decisions I had to make; different decisions might have made a difference to the results.
  
   
• On a more technical note, the format of the Wikipedia data is a little weird.  I regarded it as a CSV file with funny field separators (the "|" symbol), but then fields were separated by a pair of these "|" symbols.  So I ended up with lots of unnecessary columns, which I then deleted.  (Oh, and the header row was separated with "!" symbols instead, so I manually edited the file to make it consistent.)

The flight data

• The first step was to look at the data table to get a feel for it.  My initial observations included noting:
  • The country names are in UPPER CASE, whereas in the Wikipedia file they are just Capitalised.
  • The country names are short; they don't appear to have the parts after the commas in the Wikipedia file.
  • There is a column called "arrival_departure" which seems to indicate whether this row of the table is referring to flights arriving into the airport ("A") or departing from the airport ("D").
  • Some rows have 0 flights!  (The "number_flights_matched" column.)
    
     
• To handle these, I did the following:
  • I split the Wikipedia country names at the comma, and made two columns in the continents table, one with just the main name ("name.main") and one with the full name ("name.full"), and I also capitalised them; for example, "Afghanistan, Islamic Republic of" became "AFGHANISTAN" (for "name.main") and "ISLAMIC REPUBLIC OF AFGHANISTAN" (for "name.full").  By looking at a few rows of the flight data, I suspect that the "name.main" column will be much more useful.
  • For the flight data, I only kept rows which had an "arrival_departure" value of "D", and for which there was at least one flight recorded.
    
     
• At some point, I thought I really ought to check that only one country has each main name, but it turns out that this is not the case: there are two pairs of countries which have the same main name, CONGO and KOREA.  For each of these, the two countries involved are in the same continent (Africa and Asia, respectively), so I need keep only one of each pair for my purposes.  (The particular choice of which to keep does not make any political statement, though other choices I made earlier might do so.  I did, however, choose to remain consistent with the countries actually appearing in the CAA data.)
  
   
• I filtered the CAA flight data to only keep flights out of the London airports, as those are what I am interested in on this occasion.  There is a question here: which airports count as London airports?  Is "London Gatwick" a London airport?  What about "London Southend"?  I followed the CAA classification that these are both considered as "London area" airports (though in my first iteration, I excluded Southend - I later found the CAA classification).

Tying the flights and continents data together

• I started by listing all the countries appearing in the CAA table, and doing a quick visual scan.  (Something like: unique(caa_data$origin_destination_country) will do this.)  It is hard to scan over the entire file, so listing each country appearing makes this a manageable task.
  
   
• A couple of things jumped out at me: three entries have parentheses, namely "SPAIN(CANARY ISLANDS)", "PORTUGAL(MADEIRA)" and "PORTUGAL(EXCLUDING MADEIRA)".  I'll get rid of the parentheses before I go further.  (This is where I needed to decide whether Madeira is in Europe or Africa; I went with Europe.)  There's also a country called "Unknown" (not in upper case) which I removed as there are only two flights involved.  This left me with a new column called "destination country" with the cleaned-up name.
  
   
• Now, all I need to do is to match the countries in the CAA table with the short names in the continents table.  I started by checking that every country in the CAA table appears in the Wikipedia table.  It turned out that there were 19 missing.  (I used the anti_join function to do this, as in the R source code.)  13 of these just had slightly different names in the two tables, 5 had the full names in the CAA table, and 1 (KOSOVO) did not appear at all.  Kosovo is missing from the Wikipedia table, presumably because it is not currently recognised as a country by the UN, so I added it to the table of continents.  For the other 18, I manually created a new table listing these, giving the corresponding CAA and Wikipedia names, and then merged this information into the flights table.

This sort of data processing issue shows how valuable it is to make use of a widely recognised naming convention for something like country names: it makes further data analysis feasible.

Analysing the data

• At long last, I was ready to analyse the data!  It made sense to handle domestic flights separately, which I did by adding a fictitious continent called "UK" to the table of continent codes, and changing the continent code of "UNITED KINGDOM" to "UK".  That was far simpler than trying to separate out the domestic flights from the caa_data table and dealing with it as a special case.
• After that, I simply matched up the continents table to the caa_data table to associate every route with the continent it's in, then counted up the total number of flights for each continent.
• Finally, I tied up the continent names with each continent code, so that the table would be more readable.
• I then output the table as a CSV file, so that I could read it into any spreadsheet software.  This table, continents-count.csv, is reproduced at the start of the activity Where Are You Flying?

Representing the data

Now that I had the data, I had to decide how to represent it.  For the sake of this activity, I decided to use Excel to produce pictorial representations (even though R would do a perfectly good job), as that way I can play with some "interesting" representations which R might be less happy about generating.  I also used graphics software to produce some of the graphics.

Some further questions