Excel <\/strong><\/p>\n\n\n\nGetting (aggregating) this data in a Pivot table is quite simple, which is one of the reasons why it is so popular. <\/p>\n\n\n\n
All we have to do is to choose the column FLT_TOT_1 (Number total IFR movements), SUM of it, for the Values, <\/em>and YEAR and APT_NAME for the Rows (in most cases using the ICAO code would be more exact as it would eliminate the possibility of having incostistencies in the Name field of the airport, which would lead to aggregation mistakes. However the EUROCONTROL data are very constistent in this manner)<\/p>\n\n\n\n
Next we want to sort the data. <\/p>\n\n\n\n
The resulting data set is what we wanted, however the format is not usable yet for a\u202fBI application yet. <\/p>\n\n\n\n
To change the format, we must first change, in the tab Design \u2013 Subtotals \u2013 Do not show subtotals, <\/em>next Report Layout \u2013 Show in tabular form <\/em>and finally Report Layout \u2013 Repeat all items labels. <\/em>The resulting table is in a\u202fformat usable in a\u202fBI application. <\/p>\n\n\n\n\n
\n
\n
Next we copy the data into a\u202fnew workbook and paste values only.<\/p>\n\n\n\n
\n
\n
\n
We may want to sort the pasta data by Sum of FLT_TOT_1. <\/em>This was not possible in the Pivot table as it was limited to sorting by Year and SUM (see screenshot of sorting, a bit above). <\/p>\n\n\n\nSQL <\/strong><\/p>\n\n\n\nThe beauty of SQL is that we can get the results usable in BI application in a\u202fsingle SQL statement. <\/p>\n\n\n\n
Considering the name of the mock table is \u201eairport_traffic\u201c and containing the data same column names as the excel file (see metadata) : <\/p>\n\n\n\n
<\/p>\n\n\n\n
SELECT <\/strong>airport_traffic.apt_name, airport_traffic.Year, Sum(airport_traffic.flt_tot_1) AS sum_flt <\/p>\n\n\n\nFROM <\/strong>airport_traffic <\/p>\n\n\n\nGROUP BY<\/strong> airport_traffic.Year, airport_traffic.apt_name <\/p>\n\n\n\nORDER BY <\/strong> Sum(airport_traffic.flt_tot_1) DESC<\/strong>, Table1.Year DESC<\/strong>; <\/p>\n\n\n\n
Compare the result with the final sorted data extracted in Excel.<\/p>\n\n\n\n
R<\/strong><\/p>\n\n\n\nRunning a command in R (using RStudio) is another great way to aggregate the data, using also just one command.<\/p>\n\n\n\n
airport_traffic <- read_csv(“Airport_Traffic.csv”)<\/p>\n\n\n\n
Actually thanks to being able to import the CSV data into a data frame and work with it directly is even more straightforward than the previous steps.<\/p>\n\n\n\n
airport_traffic %>% group_by(YEAR, APT_NAME) %>% summarise(sum_flt = sum(FLT_TOT_1)) %>% arrange(-sum_flt)<\/p>\n\n\n\nIn this post I\u202fwant to show 3 different ways to aggregate the data for the busiest airports in EUROCONTROL area over the period of 2016-2022 vizualization.\u00a0\u00a0 In one of them I\u202fwill be using MS Excel with Pivot Table, second one in the second one a \u202fSQL statement (running on a mock database table created in […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11,1],"tags":[],"class_list":["post-137","post","type-post","status-publish","format-standard","hentry","category-articles","category-uncategorised"],"_links":{"self":[{"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/posts\/137","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=137"}],"version-history":[{"count":7,"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/posts\/137\/revisions"}],"predecessor-version":[{"id":327,"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/posts\/137\/revisions\/327"}],"wp:attachment":[{"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/media?parent=137"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/categories?post=137"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zdebla.me\/index.php\/wp-json\/wp\/v2\/tags?post=137"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"https:\/\/zdebla.me\/wp-content\/uploads\/2023\/06\/image-10.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/zdebla.me\/wp-content\/uploads\/2023\/06\/obrazok-44.png\")
<\/figure>\n","protected":false},"excerpt":{"rendered":"