This is a preliminary report for questions and comments.
The data used in the report was obtained from the City of Cape Town Open Data Portal in January 2017 from [2].
The data in this report are not in any way certified to be correct and in no way does the author accept any loss or liability from damages using this report or data. Please contact the author [1] for any corrections or questions. It should be noted that the author is not an air quality expert.
We confined ourself to the pollution data at the following test stations
We used the following files (here we use standardized file names)
We globbed the various pollution readings together into one table as
The data in chain brackets is nominally permissible levels. We discuss this later. Note the warning to their correctness.
Here ug/m3 is micro grams per cubic meter \(\dfrac {\mu g }{ m^{3} }\), mb is millibars, ppb is parts per billion and lngy/m2 is Langley per square meter (which is 41 840 Joules/m² or 11.622 watt-hours per square meter \(\dfrac{W h}{m^2}\) where \(\dfrac{1 W } {m^2}\) = \(\dfrac{24 W h} {m^2 day}\) .)
The work was done using Java, Postgres and R. The code (.java and report) is available on Bitbucket ([3], sign in required). However the data is too big and sits in csv files (the author will provide on request and make the data available via a API DB query. Currently there are 163 807 rows.)
We had to clean the data to make it usable. File names had to be standardized, .xls and .ods formats were changed to .csv.
Most of the pollution data is integer, however strange types occur such as in SO2 there is a row 26/10/2014 04:00;0.8;0;15;0;5;3;0;1;1;1. Here the float was replaced by 1.
Some samples contained “<Samp”, “RS232”, “Down”, “NoData”, “InVld”, “Calib” and other text labels which were replaced with “null”.
The author used the calculation of the Air Quality Index of the United Kingdom\(^{[4]}\) and Europe\(^{[5]}\) to determine permissible levels because of their availability. Clearly the values are merely reference values, because for example, we do not account for the period of measurement (nor is that information available). The permissible levels for Cape Town South Africa, will differ. Presumably the SABS will have appropriate levels.
This report should be understood as someone with a torch going into a dark forest to find what they can find.
We begin our investigation by looking at the Atlantis testing station.
We choose to look at Nitrogen Monoxide levels.
First we load the data then we visualise it.
Let us look at a summary of the data:
## date time no
## Min. :2013-01-01 Length:20449 Min. : -9
## 1st Qu.:2013-08-02 Class :character 1st Qu.: 2
## Median :2014-03-03 Mode :character Median : 6
## Mean :2014-03-20 Mean : 11
## 3rd Qu.:2014-10-02 3rd Qu.: 13
## Max. :2015-08-31 Max. :159
## NA's :9467## [1] 20449We see that we have 9467 nulls or not available data. The data goes from January 2013 to August 2015, with big holes in it. We have to be really careful to treat null data, no data and zero data correctly.
There are 20 449 lines of data. To get some kind of concept on the size we have \(365 \times 2 \times 24 + (30 + 31 + 31 + 31) \times 24 = (365\times2 + 3\times31 + 30) \times 24 = 20 472\) possible samples. The small discrepancy between 20449 and 20 472 should me investigated but is not material.
Doing the same count across all suburbs and testing stations: \(7 \times 365 \times 2 \times 24 + 14 \times (30 + 31 + 31 + 31) \times 24 = ( 365 + 3\times31 + 30) \times 24 \times 14 = 20 472\)
We look at 7 stations in the 2013 and 2014 data (14 in the 2015) which implies 163 968 rows which is not too different from 163 807 in the table.
Next we look at the histogram
The data is largely zero, and does not exceed 50. Unfortunately we have some negative data, which makes no sense.
We now look at the time series.
Initially we do a crude approximation of the x-axis as an index. We checked that the dates behaved as expectedly by sorting them and plotting them against the index. This is a naive plot to give us a feel for the data.
As mentioned unavailable data is simply not plotted - this is not merely data flagged as null, or “zero” data, but data that does not exist. This is remedied in the next plot.
Then we plot the x axis as date & time.
We also have months where there is no data at all. Also, the May - August 2015 (4 months) data is compressed relative to the Jan 2013 to Dec 2014 (24 months) data (rightmost blob) - this does not seem right. However, on reviewing the data July and August data is mainly NA.
Finally we print the data as if it is continuous.
We have successfully parsed an processed one data set. However, with the 14 stations and 17 pollutants we have \(14 \times 17 = 238\) possible data sets. This is clearly going to make a report no-one will read. We come back to this question later.
We now randomly look at \(SO_2\) in Bothasig.
## date time so2
## Min. :2013-01-01 Length:20449 Min. : 0.000
## 1st Qu.:2013-08-02 Class :character 1st Qu.: 1.000
## Median :2014-03-03 Mode :character Median : 2.000
## Mean :2014-03-20 Mean : 4.127
## 3rd Qu.:2014-10-02 3rd Qu.: 4.000
## Max. :2015-08-31 Max. :157.000
## NA's :8934## [1] 20449We repeat the process of NO in Atlantis 
Here we see a median value of 1 (rather than zero). Also, there is a little peak at 12.
Clearly the data in 2013 and 2014 are normalized differently to 2015. Furthermore, there is more data in the May to August 2015 period.
It is quite concerning that the SO2 levels have gone up is 2015. This seems to be a calibration issue but does not lead to any confidence in the absolute values.
In going through the data we had to do a lot of work, and hence make some suggestions.
In creating this data is was necessary to download the data and parse it from various formats (e.g. .xls and .ods) or else zipped. Furthermore the layout of the data changed between nominally similar data sets. The data had to be cleaned in an ad-hoc way from various text fields. A number of strange things were found in the data that required follow up (how?) such as negative results or temperatures that were clearly too low.
However, putting the data cleaning issues aside there are a number of further issues that come up. Firstly, the data ends in 2015, which is quite disappointing. It would be really useful were the data to be 24 hours old. In parsing this data, the author created an API for the pollution data - this will be made available to other software developers. However the main point is that the Open Data portal would be far more useful via an API.
Furthermore, having a metadata description of the data would make machine reading of the data for more useful.
In time the author may provide a tool to auto-update data sets.
This report is an initial review of the data in preparation for an App. The author would like the City of Cape Town to implement the suggestions contained in the above section.
It is strange that Carbon Monoxide levels (CO) and lead (Pb) are not monitored.
The next survey will be water availability and dam levels, all available on the City of Cape Town Open Data portal.
[1] Contact author at david.hislop@korwe.com or @davidjhislop.
[2] http://web1.capetown.gov.za/web1/opendataportal/DatasetDetail?DatasetName=Air%20quality
[3] https://bitbucket.org/tokollo/opendata
[4] http://ec.europa.eu/environment/air/quality/standards.htm
Here we include a useful table from [5] for completeness.
| Index | Ozone, Running 8 hourly mean (\(\mu g/m^3\)) | Nitrogen Dioxide, Hourly mean (\(\mu g/m^3\)) | Sulphur Dioxide, 15 minute mean (\(\mu g/m^3\)) | PM2.5 Particles, 24 hour mean (\(\mu g/m^3\)) | PM10 Particles, 24 hour mean (\(\mu g/m^3\)) |
|---|---|---|---|---|---|
| 1 | 0-33 | 0-67 | 0-88 | 0-11 | 0-16 |
| 2 | 34-66 | 68-134 | 89-177 | 12-23 | 17-33 |
| 3 | 67-100 | 135-200 | 178-266 | 24-35 | 34-50 |
| 4 | 101-120 | 201-267 | 267-354 | 36-41 | 51-58 |
| 5 | 121-140 | 268-334 | 355-443 | 42-47 | 59-66 |
| 6 | 141-160 | 335-400 | 444-532 | 48-53 | 67-75 |
| 7 | 161-187 | 401-467 | 533-710 | 54-58 | 76-83 |
| 8 | 188-213 | 468-534 | 711-887 | 59-64 | 84-91 |
| 9 | 214-240 | 535-600 | 888-1064 | 65-70 | 92-100 |
| 10 | \(\geq\) 241 | \(\geq\) 601 | \(\geq\) 1065 | \(\geq\) 71 | \(\geq\) 101 |