IS428 2018-19 T1 Assign Cliff Halim Tirtamaya
Contents
Problem & Motivation
WIP
Official Air Quality
Official Air Quality measurements are stored in the EEA dataset. For this particular dataset, the pollutant concentration measurements were collected over a span of 7 years from 2013 to 2018 by 5 to 6 stations. Over the years, some stations have either been decommissioned or installed in various locations. However, at any point in time, there is usually 5-6 stations collecting measurements.
Data Pre-processing of EEA Data
The EEA Dataset comprises of air quality data for Sofia in separate spread sheets according to years. As such, to be able to analyse the measurements across different years, the spreadsheets that were separated by years had to be combined to a single csv file.
This was done by utilizing the copy function within command prompt to iterate and read through all the csvs in a single folder and copy their contents onto a single new csv. For this to work properly, each csv had to be in a similar format with the same headers (which is so in this case). The code and running the process can be found in the screenshot below.
After combining the csvs, the final combined dataset is loaded onto Tableau with the metadata csv, left joined to the combined data, in order to provide the geographic (longitude and latitude) data for each of the stations. </P
Link: http://www.tomnash.eu/how-to-combine-multiple-csv-files-into-one-using-cmd/ Link of Google Map of Sofia: https://www.google.com.sg/maps/place/Sofia+City+Province,+Bulgaria/@42.6860472,23.3214962,10.96z/data=!4m5!3m4!1s0x40aa868332a0a5a3:0x300a01269bf4d70!8m2!3d42.7570109!4d23.4504683
Task 1: Spatio-temporal Analysis of Official Air Quality
Based on the Official Air Quality data (EEA dataset), the average pollutant concentration levels of 5 out of the 6 stations have been on a decreasing trend since the beginning of 2018 (dated from 6th Jan 2018). With the exception of a spike at the end of January 2018, the trend has been on a general decline as showcased by the trendline in the chart below, implying that air quality has generally improved in Sofia city.
 |
 |
From the start of February 2018, average concentration levels have remained below 50 μg/m3, the limit of EU standard. These levels have been fluctuating within a range of 10-40 μg/m3 till beginning of September 2018. The overall year-on-year analysis shows that air quality has improved significantly from previous years.
A deep-dive down to a day-to-day detail of concentration levels yielded a notable observation. Comparison of day-to-day concentration levels showed that average concentration in the nighttime is consistently higher than that of daytime. For a clearer view of this observation, the bar chart below shows an average breakdown by the hour throughout the day for the year of 2017 and 2018.
For a typical day in Sofia city, concentration levels dips down during the day starting around 11:00 hours. At approximately 16:00 hours, the concentration levels begin to climb and remain consistently higher for the nighttime before repeating the cycle again. This could be an effect of the temperature changes throughout the day which will be elaborated upon in the later section.
With the longitude and latitude data provided by the metadata, the 5 stations were plotted onto the map Sofia city to showcase the locations of these stations. The distribution of the stations is shown below. This is displayed in a side-by-side format with a google map view of Sofia city (with its man-made features and infrastructures) in order to better provide an understanding with the area in direct proximity with the stations.
The size of the plots for each of the stations was set to correspond with the average concentration levels for each of the station. From the plot chart above, in 2018, it can be observed that the 3 stations located in central-west area of Sofia city have higher average concentration levels as compared the 2 stations in the south east area. A comparison with the view of google map of Sofia city indicates a higher level of human and industrial activity in the central and western area of the city. There is a denser population volume in the stated area and these are all possible contributing factors to a higher average concentration level in the area.
The datasets provided are not fully cleaned with all data integrity. As such, there were several anomalies that can be observed through data visualizations:
| Suspected Anomaly | Visual Evidence |
|---|---|
|
For the year of 2017, data is only collected for the month of December (beginning from 28th November 2018). This can be inferred from the chart below that shows a sudden jump in average concentration levels from 25 December 2016 to 26 November 2017. |
 |
|
For the year of 2013 and 2014, the data did not show an hourly breakdown and measurements were only taken on 00:00 hour. Similarly so for the year 2015, only an additional measurement was taken on 23:00 hour. No information was provided for this anomaly from the dataset provided. A possibility would be a change in the measurement process of the concentration levels. |
 |
- This could be attributed to the environmental phenomena concerning air pollutants. (Research on temperature and time of day affecting concentration levels)
- Station Analyses
- For the recent years of 2016-2018, stations that are located on the western part of Sofia City exhibited higher pollutant concentration levels. (List out the stations). This may be due to the concentration of population and industrial facilities located in the area, contributing to a higher level of pollution.
- Higher altitude --> less pollutant?
Citizen Science Air Quality Measurements Data
The Citizen Science Air Quality Measurements data comprises of sensors located all across the city, measuring presumably, pollutant concentration levels, under P1 and P2 attributes. The data also includes measurement of humidity, temperature and pressure for the various stations.
Topography data is included as a geohash code. In order to convert the geohash code into longitude and latitude data, the dataset was loaded into Rstudio. A R package, Ironholds Geohash, was installed for the conversion. After obtaining the longitude and latitude data of the geohash code for the sensors, the sensor locations were mapped out geographically on Tableau.
Task 2: Spatio-temporal Analysis of Citizen Science Air Quality Measurements
Sensor locations were mapped out geographically on the open source map within Tableau. Several of the sensors were located outside the Sofia city. The sensors located within were selected and grouped as a set for filtration purposes.
The coverage of the sensors are heavily condensed within the central area of Sofia city. The edges of the city, particularly so for northern and southern region, have far lesser number of sensors measuring for air quality. From the map shown, the extreme south of the country have close to zero sensors in the area. However, an assessment of geographical features seem to indicate less man-made features, possibly indicating lesser population volume in the area.
map of Sofia here
Sensor activity and performance is analysed by the measurements collected at the respective timings. For example, temperature readings of 0°C or negative values are assumed to be malfunctioning. P1 and P2 readings that shows a value of 0 can possibly indicate the lack of measurement at that point in time, also indicating equipment malfunction.
METEO and Topography Data
Task 3: External Factors
WIP
Interactive Visualization
WIP
| Table Example | WIP | WIP |
|---|---|---|
|
| ||
|
| ||
|
|
Home Dashboard
WIP
References
In the completion of the analysis, the following references have been extremely useful:
- Tableau Training Video - Polygon Maps (http://www.tableau.com/learn/tutorials/on-demand/polygon-maps-8.2)
- Tableau Training Video - Background Images (http://www.tableau.com/learn/tutorials/on-demand/background-images-8)
- Creating Custom Polygons On A Background Image (https://tableauandbehold.com/2015/04/13/creating-custom-polygons-on-a-background-image/)
- Dynamically Switch Images Using Filters (https://community.tableau.com/message/182926#182926)
- Tableau Tip Week - Dashboard Navigation Buttons (http://www.thedataschool.co.uk/niccolo-cirone/tableau-tip-week-wednesday-creating-dashboard-navigator-buttons/)
