{"id":244,"date":"2023-06-09T15:45:45","date_gmt":"2023-06-09T14:45:45","guid":{"rendered":"https:\/\/zdebla.me\/?p=244"},"modified":"2023-06-18T18:04:23","modified_gmt":"2023-06-18T17:04:23","slug":"sourcing-icao-world-total-revenue-traffic","status":"publish","type":"post","link":"https:\/\/zdebla.me\/index.php\/2023\/06\/09\/sourcing-icao-world-total-revenue-traffic\/","title":{"rendered":"Preparing ICAO World total revenue traffic"},"content":{"rendered":"\n<p>I sourced the data from the tables published as annexes to the <strong>ICAO Annual Reports of the Counci<\/strong>l (and\/or \u201c<strong>World of air transport in year YYYY<\/strong>\u201d). These were downloaded from the ICAO public website. The oldest edition with data available was from 2006, which would contain data down to year 1998. I downloaded 4 different reports \u2013 2006, 2013, 2016 and 2022.<\/p>\n\n\n\n<p>I used the Power Query \u2013 <em>Get data \u2013 From PDF<\/em> to extract the relevant tables from each report, then selected Transform data. <\/p>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/zdebla.me\/wp-content\/uploads\/2023\/06\/image-11.png\" alt=\"\" class=\"wp-image-245\" width=\"648\" height=\"206\" srcset=\"https:\/\/zdebla.me\/wp-content\/uploads\/2023\/06\/image-11.png 602w, https:\/\/zdebla.me\/wp-content\/uploads\/2023\/06\/image-11-300x95.png 300w\" sizes=\"auto, (max-width: 648px) 100vw, 648px\" \/><\/figure>\n\n\n\n<p>I cleaned the first rows and appended 4 queries (4 tables) together. Since some of the figures for some years overlapped, I used Remove Rows \u2013 Remove duplicates. The annual figures for some of the years were not exactly matching, but the difference was insignificant for the purposes of this report.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"325\" height=\"361\" src=\"https:\/\/zdebla.me\/wp-content\/uploads\/2023\/06\/image-12.png\" alt=\"\" class=\"wp-image-246\" srcset=\"https:\/\/zdebla.me\/wp-content\/uploads\/2023\/06\/image-12.png 325w, https:\/\/zdebla.me\/wp-content\/uploads\/2023\/06\/image-12-270x300.png 270w\" sizes=\"auto, (max-width: 325px) 100vw, 325px\" \/><\/figure>\n\n\n\n<p>The appended data required some cleaning. There were some  inconsistencies between the appended rows, such as having a blank space between thousand of measurements and inconsistent use of decimal separator. To remove the empty spaces in thousands figures, I could use Excel with =NUMBERVALUE function.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"481\" height=\"147\" src=\"https:\/\/zdebla.me\/wp-content\/uploads\/2023\/06\/image-13.png\" alt=\"\" class=\"wp-image-247\" srcset=\"https:\/\/zdebla.me\/wp-content\/uploads\/2023\/06\/image-13.png 481w, https:\/\/zdebla.me\/wp-content\/uploads\/2023\/06\/image-13-300x92.png 300w\" sizes=\"auto, (max-width: 481px) 100vw, 481px\" \/><\/figure>\n\n\n\n<p>But I remained using Power Query, here I first replaced empty space in values and afterwards converted the data type into the whole number.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"250\" height=\"196\" src=\"https:\/\/zdebla.me\/wp-content\/uploads\/2023\/06\/image-14.png\" alt=\"\" class=\"wp-image-248\"\/><\/figure>\n\n\n\n<p>I exported the resulting table into a .csv file for easier transportation and importing. <\/p>\n","protected":false},"excerpt":{"rendered":"<p>I sourced the data from the tables published as annexes to the ICAO Annual Reports of the Council (and\/or \u201cWorld of air transport in year YYYY\u201d). These were downloaded from the ICAO public website. The oldest edition with data available was from 2006, which would contain data down to year 1998. I downloaded 4 different [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11,12],"tags":[],"class_list":["post-244","post","type-post","status-publish","format-standard","hentry","category-articles","category-data-preparation"],"_links":{"self":[{"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/posts\/244","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/comments?post=244"}],"version-history":[{"count":2,"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/posts\/244\/revisions"}],"predecessor-version":[{"id":337,"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/posts\/244\/revisions\/337"}],"wp:attachment":[{"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/media?parent=244"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/categories?post=244"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/tags?post=244"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}