ISSS608 2017-18 T3 Assign Yang Zhengyan Data Preparation

   Mini-Challenge 2 : Like a Duck to Water

Background

Data Preparation

Methodology

Insights

Feedback

Data Description

Data field

Id: Identification number for the record (only for bookkeeping) Value: Measured value for the chemical or property in this record Location: Name of the location sample was taken from. See the map for geo-location of the sampling site. Sample Date: Date sample was taken from the location Measure: Chemicals (e.g., Sodium) or water properties (e.g., Water temperature) measured in the record

Sample Data:
id,value,location,sample date,measure
2221,2,Boonsri,11-Jan-98,Water temperature
2223,9.1,Boonsri,11-Jan-98,Dissolved oxygen
2227,0.33,Boonsri,11-Jan-98,Ammonium
2228,0.01,Boonsri,11-Jan-98,Nitrites
2229,1.47,Boonsri,11-Jan-98,Nitrates
2230,0.06,Boonsri,11-Jan-98,Orthophosphate-phosphorus
2231,0.09,Boonsri,11-Jan-98,Total phosphorus
2232,13.9,Boonsri,11-Jan-98,Sodium

Data tools

SAS JMP Pro Tableau

Variable distribution

There are 10 locations, 106 measures in the dataset. So it is very difficult to apply all measures into the analysis. And we need to characterize the past and most recent situation with respect to chemical contamination in the Boonsong Lekagul waterways. So I filter out the reading values with recent years first (2011-2016) and exclude the remaining data.

The following screens showing the variables remained at dataset.

Variable clustering

Through initial data exploration, there are some types of measures with similar trend and values, e.g Orthophosphate-phosphorus, Total dissolved phosphorus, Total phosphorus. So we need to exclude those similar measures and remain one typical one for further investigation.

Firstly, Hierarchical clustering can handle those categorical data. So I put those records into 10 clusters, and check the scatterplot matrix for those variable. For the mean value comparison across all measures. We can check the pairs comparisons by Tukeu-Kramer HSD.

And then, we can see the difference between each measure and exclude those with very similar pattern reading value.

Finally, we can choose 19 measures to check the insights.

Mapping Geo-coordination

Because there is no direct coordination with the dataset, so we can input background and find out the geo-code by adding annotation into each location. The following one is the coordination of each location.After getting the geo-code, we can join two table with corresponding locations.
Boonsri 137.4 211.3
Kannika 174.6 61
Chai 159.72 127.97
Kohsoom 196.92 175.09
Somchair 77.37 134.92
Achara 102.68 167.65
Busarakhan 197.41 144.83
Decha 20.33 96.72
Sakda 136.9 15.87
Tansanee 75.89 68.95