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

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