Difference between revisions of "ISSS608 2017-18 T3 Assign Kalai Selvi Data Preparation"
Kalaisr.2017 (talk | contribs) |
Kalaisr.2017 (talk | contribs) (Undo revision 11244 by Kalaisr.2017 (talk)) |
||
| (13 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
| + | <div style=background:#0000FF border:#A3BFB1> | ||
| + | [[File:MC2 2018.jpg|400 px]] | ||
| + | <b><font size = 6; color="#F5FDFF"> VAST Challenge 2018 MC2: Like a Duck to Water </font></b> | ||
| + | </div> | ||
| + | |||
| + | <!--MAIN HEADER --> | ||
| + | {|style="background-color: #5588ff;" width="100%" cellspacing="0" cellpadding="0" valign="top" border="0" | | ||
| + | | style="font-family: Calibri; font-size:120%; text-align:center;" width="25%" | | ||
| + | |||
| + | [[ISSS608_2017-18_T3_Assign_Kalai Selvi| <font color="#FFFFFF">Problem Background</font>]] | ||
| + | |||
| + | | style="font-family: Calibri; font-size:120%; text-align:center;" width="25%" | | ||
| + | [[ISSS608_2017-18_T3_Assign_Kalai Selvi_Data_Preparation| <font color="#FFFFFF">Data Preparation</font>]] | ||
| + | |||
| + | | style="font-family: Calibri; font-size:120%; text-align:center;" width="25%" | | ||
| + | [[ISSS608_2017-18_T3_Assign_Kalai Selvi_Methodology and Dashboard Design| <font color="#FFFFFF">Methodology and Dashboard Design</font>]] | ||
| + | |||
| + | | style="font-family: Calibri; font-size:120%; text-align:center;" width="25%" | | ||
| + | [[ISSS608_2017-18_T3_Assign_Kalai Selvi_Insights| <font color="#FFFFFF">Insights and Conclusions</font>]] | ||
| + | |||
| + | | style="font-family: Calibri; font-size:120%; text-align:center;" width="25%" | | ||
| + | [[Assignment Dropbox G1|<font color="#FFFFFF">Back to Assignments</font>]] | ||
| + | |||
| + | | | ||
| + | |} | ||
| + | |||
| + | ==Data Description== | ||
| + | The Boonsong Lekagul waterways readings data contains the 5 data fields: | ||
| + | * id | ||
| + | * value | ||
| + | *location | ||
| + | * sample date | ||
| + | *measure | ||
| + | |||
| + | The chemical units of measure csv data contains the 2 fields: | ||
| + | * measure | ||
| + | * unit | ||
| + | |||
==Check for Missing Records== | ==Check for Missing Records== | ||
| − | + | Vlookup function is used in the Boonsong Lekagul waterways readings to add the ‘unit’ field from the chemical units of measure data. | |
[[File:1.jpg|600 px]] | [[File:1.jpg|600 px]] | ||
| Line 8: | Line 46: | ||
==Check for Chemical Measures with 0 values== | ==Check for Chemical Measures with 0 values== | ||
| − | There are 9,700 measures with 0 values. Records with 0 chemical reading value are deleted from the data as these are treated as | + | There are 9,700 measures with 0 values. Records with 0 chemical reading value are deleted from the data as these are treated as NA entries. There were 127,124 records in the Final Boonsong Lekagul waterways readings after deleting the 9,700 records. |
[[File:3.jpg|400 px]] | [[File:3.jpg|400 px]] | ||
==Create Locations.csv data and find x,y coordinates== | ==Create Locations.csv data and find x,y coordinates== | ||
| − | A new excel file, Locations.csv is created and the fields are the Locations, X and Y coordinates. | + | A new excel file, Locations.csv is created and the fields are the Locations, X and Y coordinates. 10 locations were updated in this Location data based on the locations given in the Final Boonsong Lekagul waterways readings. The X and Y coordinates were left as empty cells. |
The Location data was imported into Tableau. Using the Background Image tab, Waterways Final.jpg image was imported. After editing the background image, X is dragged into Column shelf and Y is dragged into Rows shelf as shown below. | The Location data was imported into Tableau. Using the Background Image tab, Waterways Final.jpg image was imported. After editing the background image, X is dragged into Column shelf and Y is dragged into Rows shelf as shown below. | ||
| − | [[File: | + | [[File:4 edit.JPG|500 px]] |
Each location in the image is annotated as a point to find the (x,y) coordinate respectively. An example of the annotation for Boonsri is as below. After finding all the (x,y) coordinates for each location, the X and Y coordinates are updated in the Locations data. | Each location in the image is annotated as a point to find the (x,y) coordinate respectively. An example of the annotation for Boonsri is as below. After finding all the (x,y) coordinates for each location, the X and Y coordinates are updated in the Locations data. | ||
| + | |||
| + | [[File:5.jpg|400 px]] | ||
==Join Tables== | ==Join Tables== | ||
| − | Boonsong Lekagul waterways readings.csv and Locations.csv data are imported into Tableau and joined using the common key, location in both datasets. | + | Boonsong Lekagul waterways readings.csv and Locations.csv data are imported into Tableau and joined using the common key, 'location' in both datasets. |
| + | |||
| + | [[File:6.jpg|500 px]] | ||
Latest revision as of 04:07, 8 August 2018
VAST Challenge 2018 MC2: Like a Duck to Water
Contents
Data Description
The Boonsong Lekagul waterways readings data contains the 5 data fields:
- id
- value
- location
- sample date
- measure
The chemical units of measure csv data contains the 2 fields:
- measure
- unit
Check for Missing Records
Vlookup function is used in the Boonsong Lekagul waterways readings to add the ‘unit’ field from the chemical units of measure data.
The Boonsong Lekagul waterways readings data was imported into JMP to check for missing records. There were 1,273 records with missing units and these are for the Macrozoobenthos chemical. Macrozoobenthos is a type of animal species and a unit of measurement is not required for this Macrozoobenthos measure.
Check for Chemical Measures with 0 values
There are 9,700 measures with 0 values. Records with 0 chemical reading value are deleted from the data as these are treated as NA entries. There were 127,124 records in the Final Boonsong Lekagul waterways readings after deleting the 9,700 records.
Create Locations.csv data and find x,y coordinates
A new excel file, Locations.csv is created and the fields are the Locations, X and Y coordinates. 10 locations were updated in this Location data based on the locations given in the Final Boonsong Lekagul waterways readings. The X and Y coordinates were left as empty cells.
The Location data was imported into Tableau. Using the Background Image tab, Waterways Final.jpg image was imported. After editing the background image, X is dragged into Column shelf and Y is dragged into Rows shelf as shown below.
Each location in the image is annotated as a point to find the (x,y) coordinate respectively. An example of the annotation for Boonsri is as below. After finding all the (x,y) coordinates for each location, the X and Y coordinates are updated in the Locations data.
Join Tables
Boonsong Lekagul waterways readings.csv and Locations.csv data are imported into Tableau and joined using the common key, 'location' in both datasets.
