View source for IS428 2018-19 T1 Assign Koh How Han Vincent

== Problem & Motivation ==
Air pollution is an important risk factor for health in Europe and worldwide. A recent review of the global burden of disease showed that it is one of the top ten risk factors for health globally. Worldwide an estimated 7 million people died prematurely because of pollution; in the European Union (EU) 400,000 people suffer a premature death. The Organisation for Economic Cooperation and Development (OECD) predicts that in 2050 outdoor air pollution will be the top cause of environmentally related deaths worldwide. In addition, air pollution has also been classified as the leading environmental cause of cancer.

Air quality in Bulgaria is a big concern: measurements show that citizens all over the country breathe in air that is considered harmful to health. For example, concentrations of PM2.5 and PM10 are much higher than what the EU and the World Health Organization (WHO) have set to protect health.

Bulgaria had the highest PM2.5 concentrations of all EU-28 member states in urban areas over a three-year average. For PM10, Bulgaria is also leading on the top polluted countries with 77 μg/m3on the daily mean concentration (EU limit value is 50 μg/m3).

According to the WHO, 60 percent of the urban population in Bulgaria is exposed to dangerous (unhealthy) levels of particulate matter (PM10).


== Dataset Analysis & Transformation Process ==

=== Dataset Analysis ===
{| class="wikitable"
|-
! Dataset  !! Data Description !! Observation
|-
| EEA Data.zip 
|| 
Content of EEA
[[File:Screenshot 2018-11-11 at 10.53.38 AM.png|frameless|800px|center|Content of EEA]]
Metadata of EEA
[[File:Screenshot 2018-11-11 at 10.53.49 AM.png|frameless|800px|center|Metadata of EEA]]
||
# Official Air Quality Measurements (EEA Data.zip)
# Contain official data recorded from 6 different station
# Druzhba, Hipodruma, IAOS/Pavlovo and Nadezhda contain data from 2013 to 2018 
# Mladost contain data only in January 2018 
# Metadata is provided. Contain details of each station including latitude and longitude
# All data provided have undergo QA and are valid and verified. 
|-
| Air Tube.zip || [[File:Screenshot 2018-11-11 at 8.50.34 PM.png|frameless|600px|center|Metadata of EEA]] 
|| 
# Citizen Science Air Quality Measurements (Air Tube.zip)
# Contains data from September 2017 to August 2018
# Also contain meteorological data (Temperature, Humidity & Pressure)
# As dataset contain geohash which is not readable by Tableau, transformation is required to convert it into latitude and longitude
# P1 is PM10 and P2 is PM2.5
|-
| METEO-data.zip 
|| 
[[File:Screenshot 2018-11-11 at 11.27.32 AM.png|frameless|600px|center|Content of METEO-data]]
|| 
# Meteorological measurements (1 station)(METEO-data.zip)
# Contain data July 2017 to September 2018
# Contain min, average, max for temperature, surface wind, pressure, humidity, precipitation, visibility and dew point
# Visibility contain missing value for min and max (-9999)
|-
| TOPO-DATA.zip || [[File:Screenshot 2018-11-11 at 8.46.19 PM.png|frameless|600px|center|Metadata of EEA]] 
||
# Topography data (TOPO-DATA)
# Contain latitude, longitude and elevation. 
|}

=== Transformation Process === 

==== Official air quality measurements ====
Using various Air Quality Stations around Sofia City from the year 2013 – 2018, Sofia City is able to obtain daily / hourly PM10 concentration readings of air quality (µg/m3) <br> 
<br> 
'''Issue''': Dataset are split into various CSV files based on the air quality station and years. This will make analysis process difficult as data might be required to load in multiple times, causing unnecessary inefficient and possible inaccurate insight drawn. <br>
'''Solution''': Using Python programming language and Pandas library to combine all relevant dataset into one full dataset.<br>
<br>
'''Issue''': Incomplete dataset from 2 of the 6 air quality stations, STA-BG0054A and STA-BG0079A. <br>
'''Solution''': Exclude incomplete datasets from merging.<br>
<br>
'''Issue''': As dataset are recorded by air quality station and on different year, the period of recording is on different scale (example: certain year contains both day and hourly data). As such, this will cause inaccurate insight being drawn out. In addition, there are plans to benchmark data using EC air quality standards which is only shown in daily or yearly.<br>
'''Solution''': As daily data could not be drill down further to hourly / var, I will adopt the method of drill up by taking the daily mean instead of hourly / var (daily mean = sum of hourly recorded data per day / number of rows of per day).<br>
<br>

==== Citizen science air quality measurements ====
'''Issue''': Dataset does not provide any latitude or longitude data, instead, only Geohash data are provided. As Tableau is unable to interpret Geohash data, decoding must be perform to retrieve the latitude and longitude data.<br>
'''Solution''': Using Python programming language and pygeohash library to decode and retrieve latitude and longitude of the location. <br>
[[File:Screenshot 2018-11-11 at 11.56.07 AM.png|800px|frameless|center|Decode Geohash]]
<br>
'''Issue''': Dataset are split into various CSV files based on the air quality station and years. This will make analysis process difficult as data might be required to load in multiple times, causing unnecessary inefficient and possible inaccurate insight drawn. <br>
'''Solution''': Using Python programming language and Pandas library to combine all relevant dataset into one full dataset.<br>
<br>
'''Issue''': Dataset contains data outside of Sofia City<br>
[[File:Screenshot 2018-11-11 at 11.38.17 AM.png|frameless|900px|center|Original number of sensors]]
'''Solution''': Manually high and exclude data that are outside of Sofia City.<br>
[[File:Screenshot 2018-11-11 at 11.39.00 AM.png|frameless|900px|center|Exclusion of sensors outside sofia city]]

==== Meteorological measurements (1 station) ====
'''Issue''': Dataset contain day, month and year separately.<br>
'''Solution''': Make use of Tableau MAKEDATE() to form the date variable. With the date variable, visualization can perform drilldown properly. <br>
<br>
'''Issue''': Visibility contains negative value which represent missing values in the dataset. However, this could cause potential shift in scale during visualization.<br>
'''Solution''': Change all values than is less than 0 to null<br>
[[File:Screenshot 2018-11-11 at 11.47.22 AM.png|frameless|600px|center|Conversion of negative value to 0]]
<br>

== Dataset Import Structure & Process ==

With all the relevant data analysis and transformation completed, the next step is to import data into Tableau.

[[File:Screenshot 2018-11-11 at 1.42.37 PM.png|800px|frameless|center|Data Source 1, 2 & 6]]
[[File:Screenshot 2018-11-11 at 1.42.51 PM.png|800px|frameless|center|Data Source 3 and 4]]
[[File:Screenshot 2018-11-11 at 1.43.00 PM.png|800px|frameless|center|Data source 5 and 7]]
[[File:Screenshot 2018-11-11 at 1.43.09 PM.png|800px|frameless|center|Data source 8]]

Each of the above table represent 1 data source. In total, the visualization contains 8 data source.

Additional processing is performed for the following data source
{| class="wikitable"
|-
! Data Source !! Issue or Limitation face with original data !! Process Technique
|-
| Task 1: 2013 - 2017 || EEA processed data (Merged with 2013 - 2017 data) and metadata are 2 separate file || Use Tableau Inner Join (Selected Air quality station from both dataset)
|-
| Task 1: Hourly data <br> Task 3: Topo + Task 1 || EEA processed data (All 2018 data) and metadata are 2 separate file || Use Tableau Inner Join (Selected Air quality station from both dataset)
|-
| Task 1: 2013 - 2017 <br> Task 1: Hourly data <br> Task 2 <br> Task 3: Meteorological Data || No dropdown to select date type || Setup Parameter control 
[[File:Screenshot 2018-11-11 at 1.59.58 PM.png|400px|frameless|center|Setup parameter control for Date Type]]
|-
| Task 1: 2013 - 2017 <br> Task 1: Hourly data <br> Task 2 <br> Task 3: Meteorological Data || As sorting is done automatically by Tableau, having a custom date format would help to prevent wrong sorting from happening during visualization || Setup custom month and quarter and selection field
[[File:Screenshot 2018-11-11 at 2.13.11 PM.png|400px|frameless|center|Custom quarter and month for viz]]
Setup date selection
[[File:Screenshot 2018-11-11 at 2.17.25 PM.png|400px|frameless|center|Condition for date selection]]
|-
| Task 2 <br> Task 3: Meteorological Data || Use to visualize data in day basics. Also to prevent wrong sorting by Tableau. || Setup custom day
[[File:Screenshot 2018-11-11 at 2.25.43 PM.png|400px|frameless|center|Custom day]]
|}

== Interactive Visualization ==
Public Tableau Visualization can be access from here: https://public.tableau.com/profile/vincent.koh#!/vizhome/VA-Assignment/MainDashboard?publish=yes <br>
For best viewing experience, viewers are recommended to change their browser screen resolution to 1366 x 768 with full screen mode. 
<br>
To improve user experience, several interactive tool has been setup to ensure flexibility in navigating and generating of visualization.
[[File:Screenshot 2018-11-11 at 5.43.50 PM.png|800px|frameless|center|Common button]]
{| class="wikitable"
|-
! Interactive Technique !! Rationale !! Implementation Steps
|-
| Click to go back one visualization || To allow viewers to return to the previous dashboard. ||
# Click "Floating" under Objects in Dashboard (On the left side of the screen)
# Drag "Button" into dashboard
# Click on the dragged button and right click. Select "Edit Button"
# Select the previous dashboard to navigate to under "Navigate to"
# Choose your desired images by click on "Choose" under "Image"
|-
| Click to proceed to next visualization || To allow viewers to proceed to the next dashboard. || Repeat above steps with a few point to take note. 
* "Navigate to" must be the next dashboard
* Choose another image to prevent confusion
|-
| Click to return to main dashboard || To allow viewers to return back to main dashboard  || Repeat above steps with a few point to take note. 
* "Navigate to" must be the main dashboard
* Choose another image to prevent confusion
|-
|}
'''Notes''': Certain dashboard might not have all 3. Depending on the numbering of the dashboard, button for proceed and return will appear accordingly.


=== Main Dashboard === 
As there is a lot of visualization involved for this assignment, using only 1 dashboard is impossible. In addition, with 4 dataset given for generating of visualization, having all 4 in one dashboard may not be able to use the dataset to its fullest potential. Hence, I have decided to split the dashboard into multiple dashboard. These dashboard will be categorise into 3 different category. Official Data, Citizen Data and relationship between Official Data and/or Citizen Data with Topological and/or Meteorological data.  

[[File:Screenshot 2018-11-11 at 4.32.32 PM.png|800px|frameless|center|Main Dashboard Guide]]

The following technique has been adopted to help viewers in navigating between dashboards.
{| class="wikitable"
|-
! Interactive Technique !! Rationale !! Implementation Steps
|-
| Navigate across dashboard || To allow viewers to access other dashboard easily with a click of the button ||
# Click "Floating" under Objects in Dashboard (On the left side of the screen)
# Drag "Button" into dashboard
# Click on the dragged button and right click. Select "Edit Button"
# Select the dashboard to navigate to under "Navigate to"
# Choose your desired images by click on "Choose" under "Image"
|-
| Display text upon hovering on button || Provide user information on what to expect for the next dashboard.  ||
# Under Tooltip of "Edit Button", type in your desired texts for viewers. 
|-
|}

=== Typical day of Sofia City ===
The following image shows the typical day of Sofia City

[[File:Screenshot 2018-11-11 at 4.41.01 PM.png|800px|frameless|center|Typical day of Sofia City]]

The following technique has been adopted to help viewers in navigating between dashboards.
{| class="wikitable"
|-
! Interactive Technique !! Rationale !! Implementation Steps
|-
| Hover to see average concentration by week|| To allow viewers to see the average concentration by week in terms of day ||
# Right click on "Datetime Begin", select "Change Data Type" and select "Date & Time"
# Drag "Datetime Begin" into "Rows" of the sheet.
# Right click on "Datetime Begin", hover to the first "More" seen in the popup menu, select "Weekday"
# Drag "Concentration" into "Label" and another "Concentration" into "Color"
# Return to the sheet that contain calendar heatmap
# Click on "Tooltip" under Mars. Create a new line after the last entry of the Tooltip. Click "Insert" and hover to "Sheet" and select the visualization created previously.
# Once the code has been generated, add "Common Name" and "WEEK(Datetime Begin)" into filter.
(<Sheet name="Avg Con. by Weekday" maxwidth="300" maxheight="300" filter="'''<Common Name>,<WEEK(Datetime Begin)>'''">)
|-
| Click to filter by "Common Name" || To provide viewers in depth view of data collected by individual air quality station||
# Drag "Common Name" into calendar heatmap's Filters. Right click and select "Show Filter"
# Drag Calendar Heatmap into Dashboard
|-
|}

=== Trends & Anomalies ===
The following image shows the trends & anomalies
[[File:Screenshot_2018-11-11_at_10.52.57_PM.png|800px|frameless|center|Distribution & Status of Sensors and Data]]

{| class="wikitable"
|-
! Interactive Technique !! Rationale !! Implementation Steps
|-
| Filter visualization by date type || To provide viewers the ability to show the trend in different date setting || Ensure visualization is using the custom date selection created during the additional processing stage in Tableau.
|-
| Highlight all chart by common name || To provide viewers the ability to view the air quality station only in all 3 visualization || Ensure all visualization are using the same "Common Name". Move to the top right hand corner of the legend and click on the funnel (When hover, a tooltip will appear "Highlight Selected Items")
|-
|}

=== Distribution & Status of Sensors and Data ===
The following image shows the distribution and status of sensors. In addition, data from the sensors is also shown in the line chart.

[[File:Screenshot 2018-11-11 at 5.08.08 PM.png|800px|frameless|center|Distribution & Status of Sensors and Data]]

{| class="wikitable"
|-
! Interactive Technique !! Rationale !! Implementation Steps
|-
| Drag to highlight the line chart. Selected line will show the location of the sensor in the geographic map || To allow viewers to see multiple sensor location based on the recorded data shown in the line chart  || 
# Click on Dashboard on Tableau header and select "Action"
# Follow the configuration shown in the image. 
[[File:Screenshot 2018-11-11 at 5.12.17 PM.png|400px|frameless|center|Filter configuration]]
|-
|}

=== Local Energy Source ===
The following image shows the concentration of P1 and P2 in Sofia. 

[[File:Screenshot 2018-11-11 at 5.23.05 PM.png|800px|frameless|center|Local energy source]]
{| class="wikitable"
|-
! Interactive Technique !! Rationale !! Implementation Steps
|-
| Drag the slider to find the date. Geographic map will show the density of the concentration for P1 and P2 || To allow viewers to see selected day concentration. || Ensure visualization is using the custom date selection created during the additional processing 
|-
|}

=== Local Meteorological Data ===
The following image shows the meteorological data with the average P1 and P2.  

[[File:Screenshot 2018-11-11 at 5.26.41 PM.png|800px|frameless|center|Local meteorology data ]]
{| class="wikitable"
|-
! Interactive Technique !! Rationale !! Implementation Steps
|-
| Select data type to see all visualization based on selected date setting || To provide viewers the ability to show the trend in different date setting || Ensure visualization is using the custom date selection created during the additional processing 
|-
|}

== Tasks ==
=== Task 1: Spatio-temporal Analysis of Official Air Quality ===
==== What does a typical day look like for Sofia city? ====
Based on the visualization below, air pollution are still on the higher side for the first 3 months of 2018. 
[[File:Screenshot 2018-11-11 at 6.11.28 PM.png|800px|frameless|center|Overall view]]
<br>
Drilling down to week level and using one of the sensor, Druzhba, we can see that concentration are relatively low throughout the 24 periods. However, there are special cases whereby at certain period of the day, concentration would be higher as compare to others. One such example would be week 31, 0600hrs. <br>
[[File:Screenshot 2018-11-11 at 6.14.46 PM.png|800px|frameless|center|Week view]]

However, these visualization can be misleading as it does not show the trend throughout the year. Therefore, another visualization is introduce to prevent any inaccurate or bias in analysis. Based on the visualization below, observation can be made that the average air pollution by year has decrease (evident from 2013Q3 - 2014Q1 having higher than 2013Q2, 2013Q3 and 2014Q2). However, certain period of the year still have higher average concentration. <br>
[[File:Screenshot 2018-11-11 at 6.06.33 PM.png|800px|frameless|center|Quarter view]]
<br>
==== Do you see any trends of possible interest in this investigation? ==== 
# After generating the average concentration of p1 by month, I have notice that on the month of October, average concentration start to rise and at January, average concentration start to drop till about February to March. One possible reason would be seasonality. Based on the visualization above, there might be a possibility that at lower temperature, concentration will increase. 
[[File:Screenshot 2018-11-11 at 6.18.47 PM.png|800px|frameless|center|Quarter over the years]]
<br>
==== What anomalies do you find in the official air quality dataset? ====
# Missing data happens to all sensors in all years with 2017 being one of the year that has missing data from January 2017 to 27 November 2018 [[File:Screenshot 2018-11-11 at 6.22.40 PM.png|800px|frameless|center|missing data]]
# During 2017, air quality station with common name Druzhba, has a high average concentration as compare to other air quality station. In addition, air quality station Nadezhda, has a lower average concentration. [[File:Screenshot 2018-11-11 at 6.24.28 PM.png|800px|frameless|center|Year View]]
# Nadezhda has unusual high concentration during the period of April to May 2018 between 4am to 11am. [[File:Screenshot 2018-11-11 at 10.14.39 PM.png|800px|frameless|center|Unusual Data recording]]
# All 4 sensors contains daily records that are high in concentration. [[File:Screenshot 2018-11-11 at 10.44.42 PM.png|800px|frameless|center|Day outlier]]
<br>
==== How do these affect your analysis of potential problems to the environment? ====
{| class="wikitable"
|-
! Anomaly / Unusual Events !! Potential Trend / Issue
|-
| Missing data happens to all sensors in all years with 2017 being one of the year that has missing data from January 2017 to 27 November 2018 || This could potentially cause an inaccurate analysis. As there might be a correlation between season and air pollution, having only December will cause average concentration to be high for the year of 2017, leading to inaccurate analysis. 
|-
| During 2017, air quality station with common name Druzhba, has a high average concentration as compare to other air quality station. In addition, air quality station Nadezhda, has a lower average concentration.  || 
Initial calculation of the 2 sensors distance is approximately 10km apart. Within 10km, concentration shouldn't be too far apart between this 2 sensors. For Nadezhda, one possible would be the amount of data recorded as compare to other 3 sensors. With only 274 days of data, insight could be inaccurate due to lack of data. For Druzhba, there is a possibilities that the sensor is picking up incorrect readings. 
|-
| Nadezhda has unusual high concentration during the period of April to May 2018 between 4am to 11am. || As the sensor is located within the vicinity of home for locals, it may be difficult to narrow down to the actual cause of this issue because there could be many different factors ranging from incorrect reading to household burning of fossil fuels that affect the reading.
|-
| All 4 sensors contains daily records that are high in concentration. || Since the amount of outliers are fairly consistent, there might be a pattern in the data.
|-
|}

=== Task 2: Spatio-temporal Analysis of Citizen Science Air Quality Measurements ===
==== Are they well distributed over the entire city? ==== 
Sensors are relatively distributed around the city, however, using a density map, I observe that north-east side of Sofia city has slightly more sensors as compare to other part of the city. 
[[File:Screenshot 2018-11-11 at 6.58.15 PM.png|800px|frameless|center|Sensor distribution]]

==== Are they all working properly at all times? ====
# Most of the time, sensors are working well with a steady increase in the first few months and having a stable number of sensors during the later part of 2018. However, at certain period of the dataset, there are sensors that stop working. This can be seen as there is a sudden dip in the visualization.
# On 26 March 2018 12pm, there is a sudden increase by approximate 40 sensors. This could be new user trying out the sensors during that period of the day. 
[[File:Screenshot 2018-11-11 at 6.59.51 PM.png|800px|frameless|center|Status of sensor]]

==== Can you detect any unexpected behaviors of the sensors through analyzing the readings they capture? ====
# On certain occasion, average concentration of p1 and p2 seem to be higher as compare to usual. Using the visualization below, we can see that there are several spike in the data.
[[File:Screenshot 2018-11-11 at 7.02.00 PM.png|800px|frameless|center|P1 and P2 comparison]]

==== Which part of the city shows relatively higher readings than others? Are these differences time dependent? ====
As outlier data has been remove prior to generating this chart and this dashboard only show data point that are above the EC Air Quality Standards for p1 and p2. By highlight all the data point in the above chart (Avg Con. by Hour By Location), the data point that have relatively high reading will be show in the sensor distribution map. <br>
Based on the result and some Google Map search on latitude and longitude, 2 of the location ([42.676, 23.320], [42.696, 23.304]) are denser with locals. Higher reading from this 2 location could be due to household burning of fossil fuels or transportation. The last 2 location ([42.67, 23.232], [42.610, 23.344]) are rather less denser with locals (based on Google Street View), however high readings due to household burning could not be rejected as there are still locals living in this 2 locations. <br>
These higher readings are quite consistent as shown in the chart (Avg Con. by Hour by Location) throughout the 24 hours. 
[[File:Screenshot 2018-11-11 at 7.04.13 PM.png|800px|frameless|center|Higher reading]]

=== Task 3 ===
==== Local energy sources ====
After reviewing data from 2017 to 2018, I realise that as the place get denser with local (ex: central of Sofia City), the concentration seem to be higher. This could be due to household burning of fossil fuels or transportation. However, the result can be inconclusive as there could be other external factors that affect the concentration. 
[[File:Screenshot 2018-11-11 at 1.35.00 PM.png|800px|frameless|center|Local Energy Sources vs P1 & P2]]

==== Local meteorology such as temperature, pressure, rainfall, humidity, wind etc  ====
One relationship can be observe between local meteorology and air pollution. When temperature start to decrease, concentration for PM2.5 and PM10 start to increases, precipitation and visibility start to decreases.
One possible reasons could be due to temperature inversion. Where higher air sinks due to the air being squeezed or compressed from higher air pressure at lower elevation, sinking air can be warm to the point it is warmer than air below it. Also known as upper air inversion, it create a barrier to prevent thunderstorms from forming. This can cause poor air quality as pollutant can build up.
[[File:Screenshot 2018-11-11 at 1.21.13 PM.png|800px|frameless|center|Relationship between meteorology and air pollution]]

==== Complex interactions between local topography and meteorological characteristics. ==== 
One relationship can be observe is that when surface temperature increase or decrease, high grounds (darker part of the map in the visualization) does not seem to be affected by the increase in concentration level. One possible reason is that high ground causes difficulties for the pollutant to be disperse which results the concentration of P1 and P2 relatively high in Sofia City.
[[File:Screenshot 2018-11-11 at 1.36.31 PM.png|800px|frameless|center|Topology vs Meteorology]]

==== Transboundary pollution for example the haze that intruded into Singapore from our neighbours. ====
After viewing the data by week. Within the visualization, it was noted that the next high concentration area is at Plovdiv (See below image). <br>
After some research, I found out that there is a heating station at that location. Manage by Toplofikatsiya Plovdiv in the city of Plovdiv in Southern Bulgaria, it is one of the largest heating network in the country. Consisting of 2 power stations, one is steam-gas power station for electricity and thermal power and another, heat-only boiler station. <br> 
Publish on 6 April 2018, novinite (Sofia news agency) reported that Plovdiv has the most polluted air in the last heating season. With the news as evident and the distance between 2 state, I am confident to say that the power station located at Plovdiv could be a reason that Sofia city is experiencing high air pollution.
[[File:Screenshot 2018-11-11 at 1.18.12 PM.png|800px|frameless|center|Transboundary identification]]

== References ==
#EC Air Quality Standard [http://ec.europa.eu/environment/air/quality/standards.htmhttp://ec.europa.eu/environment/air/quality/standards.htm]
#How winter affect air pollution [https://airlief.com/air-pollution-during-winter/]
#Environment in Sofia [https://www.youtube.com/watch?v=HCQPxk8uBSM]
#Cause, effect and solution for air pollution [https://www.conserve-energy-future.com/causes-effects-solutions-of-air-pollution.php]
#How Meteo affect air pollutant [https://www.qld.gov.au/environment/pollution/monitoring/air/air-monitoring/meteorology-influence/meteorology-factors]
#Bulgaria season  [https://seasonsyear.com/Bulgaria]
#News on Plovdiv (For Transboundary) [https://www.novinite.com/articles/189297/Plovdiv+had+the+most+Polluted+Air+in+the+Last+Heating+Season]
#Topo Layer on Tableau Map [https://www.mapbox.com/]

== Comments ==