{"id":108,"date":"2023-06-01T06:33:45","date_gmt":"2023-06-01T05:33:45","guid":{"rendered":"https:\/\/zdebla.me\/?p=108"},"modified":"2023-06-07T21:00:19","modified_gmt":"2023-06-07T20:00:19","slug":"essip-vizualization-data-preparation","status":"publish","type":"post","link":"https:\/\/zdebla.me\/index.php\/2023\/06\/01\/essip-vizualization-data-preparation\/","title":{"rendered":"ESSIP vizualization – data preparation"},"content":{"rendered":"\n

Deployment data sourcing, transformation and cleaning\u00a0<\/strong><\/p>\n\n\n\n

The souce of the data is https:\/\/www.atmmasterplan.eu\/depl\/essip_objectives\/map<\/a> . I\u202fcouldnt find a\u202fway to download a\u202fcomplete raw data for all the objectives for all the years of the ESSIP\/LSSIP process, so I\u202fdownloaded manually data for the last 2 years for 20 objectives. <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n

40 excel files were saved into a\u202ffolder. <\/p>\n\n\n\n

Using the powerful Power Query \u2013 Get data \u2013 From file \u2013 From folder \u2013 Tranform data – Combine files  <\/em><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

The souce excel files combined some introductory columns (10). The best way to remove them was to filter out NULL values in the column 2. <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Data cleaning \u2013 removing the first column and some additional ones, using the first row as headers. Filtering out some of the headers remaining from combining the worksheets. <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Since I am keeping the data for 2 years \u2013 2021 and 2022, I need the data for the edition year. These come from the first column, which is based on the imported excel file name, like \u201ceATMPortal_Maptool_AOM13.1_Edition2021_2023-MM-DD.xls\u201d. To extract the Edition year, I used the combination of =MID<\/em> and =FIND<\/em> formulas, looking for the information after the third underscore and extracting 11 characters, like this: <\/p>\n\n\n\n

=MID(A2,FIND(“_”,A2,FIND(“_”,A2,FIND(“_”,A2)+1)+1)+1,11) <\/strong><\/p>\n\n\n\n

Data cleaned \u2013 1760 rows remained<\/strong> \u2013 20 objectives \u2013 44 States \u2013 2 years.  <\/p>\n\n\n\n

Objectives additional information \u2013 stakeholders, SESAR solutions, ICAO Block Modules\u00a0<\/strong><\/p>\n\n\n\n

Downloading the data from https:\/\/www.atmmasterplan.eu\/downloads<\/a> – Implementation Objectives excel file at the bottom.   <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Removing some of the sheets which will not be used. <\/p>\n\n\n\n

Converting the first column to just leave out just the objective name, using the inbuilt Data<\/em> \u2013 Text to columns <\/em>function, using the \u201e-\u201e delimiter. One could also use the combination of the combination of =LEFT and =SEARCH, but it is not necessary since the Text to columns <\/em>function works great.  <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Moving the remaing 3 sheets into the deployment data worksheet.  <\/p>\n\n\n\n

    <\/ol>\n\n\n\n
    \"\"<\/figure>\n\n\n\n

    Data modeling<\/strong><\/p>\n\n\n\n

    The 3 reports, showing additional information per each objectives ( stakeholders, SESAR solutions, ICAO Block Modules) were modeled in Tableau Data source<\/em> tab, creating relationship between the objectives names (abbreviations).<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n
    \"\"<\/figure>\n","protected":false},"excerpt":{"rendered":"

    Deployment data sourcing, transformation and cleaning\u00a0 The souce of the data is https:\/\/www.atmmasterplan.eu\/depl\/essip_objectives\/map . I\u202fcouldnt find a\u202fway to download a\u202fcomplete raw data for all the objectives for all the years of the ESSIP\/LSSIP process, so I\u202fdownloaded manually data for the last 2 years for 20 objectives.  40 excel files were saved into a\u202ffolder.  Using the […]<\/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-108","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\/108","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=108"}],"version-history":[{"count":2,"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/posts\/108\/revisions"}],"predecessor-version":[{"id":176,"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/posts\/108\/revisions\/176"}],"wp:attachment":[{"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/media?parent=108"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/categories?post=108"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/tags?post=108"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}