IS428 AY2019-20T1 Assign Benedict Then Ji Xiang Data Preperation

From Visual Analytics for Business Intelligence
Jump to navigation Jump to search

Radiation.png VAST 2019 MC2: Citizen Science to the Rescue

Overview

Data Preperation

Interactive Visualisation

Task Findings

References

Datasets

4 datasets will be used for VAST 2019 - Mini Challenege 2:

  • StaticSensorReadings.csv - contains Static Sensor radiation measurements at different timestamp
  • StaticSensorLocations.csv - contains the location of Static Sensors
  • MobileSensorReadings.csv - contains Mobile Sensor radiation measurements and cordinates at different time stamp
  • MC1-Reports-Data.csv - contains citizens reports of shaking/damage by neighborhood over time

Objectve

The objective is to combine StaticSensorReadings.csv, StaticSensorLocations.csv, MobileSensorReadings.csv into one csv file so that it will be easier to analyze on Tableau. In addition, do some data cleaning on the current datasets provided.

Data Preperations

Step 1 Combining StaticSensorReadings.csv and StaticSensorLocations.csv
Description Currently, StaticSensorReadings.csv only contains the Static Sensors radiation measurements while StaticSensorLocations.csv only contains the location of the Static Sensors. Both of these csv files will be combined into one by inner-joining the Sensor_ID field so that every Static Reading will have its known coordinates.
Illustration
Combined Dataset from Static_Locations & Static_Readings
Step 2 Removing "Units" field column and changing "Sensor_ID" field values
Description The Static Sensor ID and the Mobile Sensor ID have the identical Sensor ID numbers. Hence to prevent a clash of data, The Static Sensor ID will be changed to a unique value, for example, Static Sensor ID "1" will be changed to "S1". In addition, the datasets contain only one unit measurement (cpm). Hence, the "Units" column will be removed as it is redundant. Lastly, the "Value" field will be changed to "Value (cpm)".
Illustration
Changing the Static Sensor ID By adding "S" in front
Edited "Sensor_ID" field and remove "Units" field
Step 3 Combing MobileSensorReadings.csv with the combined dataset of Static Sensor Readings and Locations
Description The Mobile Sensor Readings will be combining with the Static Sensor readings by doing a union join as all their field names are the same. Afterwhich, Mobile Sensor ID values will be changed from "1" to "M1" so that the Static Sensor ID and Mobile Sensor ID can be differentiated.
Illustration
Changing Mobile Sensor ID by adding "M" in front
Combined dataset of Static and Mobile Sensor Readings

Final Outcome

Tableau Prep Workflow
Final Combined Dataset