{"id":307,"date":"2023-06-16T11:47:12","date_gmt":"2023-06-16T10:47:12","guid":{"rendered":"https:\/\/zdebla.me\/?p=307"},"modified":"2023-06-16T11:47:12","modified_gmt":"2023-06-16T10:47:12","slug":"atm-cns-provision-costs-and-traffic-volumes-data-preparation-and-validation","status":"publish","type":"post","link":"https:\/\/zdebla.me\/index.php\/2023\/06\/16\/atm-cns-provision-costs-and-traffic-volumes-data-preparation-and-validation\/","title":{"rendered":"ATM\/CNS provision costs and traffic volumes &#8211; data preparation and validation"},"content":{"rendered":"\n<p>For this visulasation, I needed two sets of data:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>ATM\/CNS provision costs per ANSPs<\/li>\n\n\n\n<li>Composite flight hours per ANSPs<\/li>\n<\/ul>\n\n\n\n<p class=\"has-text-align-center has-vivid-cyan-blue-color has-text-color has-medium-font-size\">Definitions<\/p>\n\n\n\n<p style=\"margin-top:var(--wp--preset--spacing--30);margin-right:var(--wp--preset--spacing--30);margin-bottom:var(--wp--preset--spacing--30);margin-left:var(--wp--preset--spacing--30)\"><a href=\"https:\/\/ansperformance.eu\/economics\/ace\/ace-handbook\/methodology.html\">First the definitions.<\/a> ATM\/CNS provision costs is a metric used in the ACE reports and it excludes MET costs, EUROCONTROL costs and States\/NSAs costs from the overal costs equation. <\/p>\n\n\n\n<p style=\"margin-top:var(--wp--preset--spacing--30);margin-right:var(--wp--preset--spacing--30);margin-bottom:var(--wp--preset--spacing--30);margin-left:var(--wp--preset--spacing--30)\">The \u201ccomposite gate-to-gate flight-hours\u201dconsiders a \u201cgate-to-gate\u201d perspective, &#8220;because the boundaries used to allocate costs between en-route and terminal ANS vary between ANSPs and might introduce a bias in the cost-effectiveness analysis.&#8221;<\/p>\n\n\n\n<p style=\"margin-top:var(--wp--preset--spacing--30);margin-right:var(--wp--preset--spacing--30);margin-bottom:var(--wp--preset--spacing--30);margin-left:var(--wp--preset--spacing--30)\">&#8220;The \u201ccomposite gate-to-gate flight-hours\u201d are determined by weighting the output measures by their respective average cost of the service for the whole Pan-European system. This average weighting factor is based on the total monetary value of the outputs and amounts to 0.27.&#8221;<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"853\" height=\"45\" src=\"https:\/\/zdebla.me\/wp-content\/uploads\/2023\/06\/obrazok-33.png\" alt=\"\" class=\"wp-image-308\" srcset=\"https:\/\/zdebla.me\/wp-content\/uploads\/2023\/06\/obrazok-33.png 853w, https:\/\/zdebla.me\/wp-content\/uploads\/2023\/06\/obrazok-33-300x16.png 300w, https:\/\/zdebla.me\/wp-content\/uploads\/2023\/06\/obrazok-33-768x41.png 768w\" sizes=\"auto, (max-width: 853px) 100vw, 853px\" \/><\/figure>\n\n\n\n<p class=\"has-text-align-center has-vivid-cyan-blue-color has-text-color has-medium-font-size\"><strong>Sourcing the data<\/strong><\/p>\n\n\n\n<p style=\"margin-top:var(--wp--preset--spacing--30);margin-right:var(--wp--preset--spacing--30);margin-bottom:var(--wp--preset--spacing--30);margin-left:var(--wp--preset--spacing--30)\">Since I wasn\u00b4t able to find the standalone dataset on the ATM\/CNS provision costs, I had to extract it from the yearly <a href=\"https:\/\/ansperformance.eu\/publications\/prc\/ace\/\">ACE reports <\/a>(2016-2022), available in PDF.<\/p>\n\n\n\n<p style=\"margin-top:var(--wp--preset--spacing--30);margin-right:var(--wp--preset--spacing--30);margin-bottom:var(--wp--preset--spacing--30);margin-left:var(--wp--preset--spacing--30)\">To extract the tables, I used Power Query, similar to the process I wrote down in<a href=\"https:\/\/zdebla.me\/index.php\/2023\/06\/09\/sourcing-icao-world-total-revenue-traffic\/\" data-type=\"post\" data-id=\"244\"> this article.<\/a><\/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\/obrazok-34.png\" alt=\"\" class=\"wp-image-309\" width=\"645\" height=\"598\" srcset=\"https:\/\/zdebla.me\/wp-content\/uploads\/2023\/06\/obrazok-34.png 605w, https:\/\/zdebla.me\/wp-content\/uploads\/2023\/06\/obrazok-34-300x278.png 300w\" sizes=\"auto, (max-width: 645px) 100vw, 645px\" \/><\/figure>\n\n\n\n<p>For each year the table with the data was situated at a different page, so I couldn\u00b4t fully automate the process, however after selecting the correct page, all the following steps of cleaning the data was identical.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"251\" height=\"216\" src=\"https:\/\/zdebla.me\/wp-content\/uploads\/2023\/06\/obrazok-35.png\" alt=\"\" class=\"wp-image-310\"\/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"605\" height=\"348\" src=\"https:\/\/zdebla.me\/wp-content\/uploads\/2023\/06\/obrazok-36.png\" alt=\"\" class=\"wp-image-311\" srcset=\"https:\/\/zdebla.me\/wp-content\/uploads\/2023\/06\/obrazok-36.png 605w, https:\/\/zdebla.me\/wp-content\/uploads\/2023\/06\/obrazok-36-300x173.png 300w\" sizes=\"auto, (max-width: 605px) 100vw, 605px\" \/><\/figure>\n\n\n\n<p>This way I had the 5 queries (5 tables) extracted quickly and I could append them into a single query.<\/p>\n\n\n\n<p class=\"has-text-align-center has-vivid-cyan-blue-color has-text-color has-medium-font-size\">Composite flight hours &#8211; data verification<\/p>\n\n\n\n<p style=\"margin-top:var(--wp--preset--spacing--30);margin-right:var(--wp--preset--spacing--30);margin-bottom:var(--wp--preset--spacing--30);margin-left:var(--wp--preset--spacing--30)\">At first I started calculating the composite flight hours based on the figures for the en-route and airport arrival delays from the <em>En-route IFR flights and ATFM delays <\/em>and <em>Airport arrival ATFM delays &#8211; (no post ops adjustments)<\/em> <a href=\"https:\/\/ansperformance.eu\/data\/\">datasets<\/a>.<\/p>\n\n\n\n<p style=\"margin-top:var(--wp--preset--spacing--30);margin-right:var(--wp--preset--spacing--30);margin-bottom:var(--wp--preset--spacing--30);margin-left:var(--wp--preset--spacing--30)\">However I noticed they are already available in the yearly ACE reports as well, so I copied them from there.<\/p>\n\n\n\n<p style=\"margin-top:var(--wp--preset--spacing--30);margin-right:var(--wp--preset--spacing--30);margin-bottom:var(--wp--preset--spacing--30);margin-left:var(--wp--preset--spacing--30)\">To be sure, I wanted to verify them and my understanding, so I made calculations at least for a one year, using the formula A + 0.27 * B.<\/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\/obrazok-37.png\" alt=\"\" class=\"wp-image-312\" width=\"653\" height=\"887\" srcset=\"https:\/\/zdebla.me\/wp-content\/uploads\/2023\/06\/obrazok-37.png 608w, https:\/\/zdebla.me\/wp-content\/uploads\/2023\/06\/obrazok-37-221x300.png 221w\" sizes=\"auto, (max-width: 653px) 100vw, 653px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/zdebla.me\/wp-content\/uploads\/2023\/06\/obrazok-38.png\" alt=\"\" class=\"wp-image-313\" width=\"198\" height=\"110\"\/><\/figure>\n\n\n\n<p>Note the figure in the calculated table and the figure extracted from the ACE report for 2020.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>For this visulasation, I needed two sets of data: Definitions First the definitions. ATM\/CNS provision costs is a metric used in the ACE reports and it excludes MET costs, EUROCONTROL costs and States\/NSAs costs from the overal costs equation. The \u201ccomposite gate-to-gate flight-hours\u201dconsiders a \u201cgate-to-gate\u201d perspective, &#8220;because the boundaries used to allocate costs between en-route [&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-307","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\/307","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=307"}],"version-history":[{"count":1,"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/posts\/307\/revisions"}],"predecessor-version":[{"id":314,"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/posts\/307\/revisions\/314"}],"wp:attachment":[{"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/media?parent=307"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/categories?post=307"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/tags?post=307"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}