IS428 AY2019-20T1 Assign Kok Jim Meng Data Preparation

IS428 VAST 2019 Mini Challenge 2

Problem & Motivation

Data Preparation

Interactive Visualisation

Tasks Questions and Answers

References and Comments

Data Preparation

The dataset zip file given includes:

• Mobile Sensor Readings
• Static Sensor Readings
• Static Sensor Location
• Data Description
• Maps of St Himark

Static Sensor Readings and Static Sensor Location

Figure 1: Screenshot of the two Static CSVs Tables

Issue: In the CSV file of Static Sensor Readings, there are no geographic coordinates of the static sensors as the data are in the CSV file of Static Sensor Locations. Solution: Use Tableau Prep to join the two tables into one CSV based on the common field which is Sensor-id and cleaning it by removing the extra sensor-id field. With this, it would be possible to perform a map visualization for Static Sensors using the longitude and latitude in Tableau. The following is the data preparation used in Tableau Prep for Static Sensor data.

Figure 2: Join based on Sensor-id

Figure 3: Clean the newly joined table by removing the extra sensor-id field

Figure 4: Newly joined table is created and preparing to output as a CSV file

Newly created Static Sensor Readings with Location and Motion Sensor Readings CSVs

Issue: Both the newly created Static Sensor Readings with Location and given Motion Sensor Readings tables are separated. Moreover, both tables’ Sensor IDs are just numbers where same numbers appear in both tables. This does not make sense as both static and mobile sensors are different and same numbers appear. Solution: Merge both newly created Static Sensor Readings with Location table with the given Motion Sensor Reading table based on the timestamp when the sensors have detected. Furthermore, I have classified the sensors based on their types – Static, and Mobile. In addition, I have also reassigned the sensors IDs where, X is a number, M-XX is a mobile sensor and S-XX is a static sensor.

The following steps or process is how I have merged both the newly created Static Sensor Readings with Location table with the given Motion Sensor Reading table:

Figure 5: Process of merging the two tables

First of all, I have created a new calculated field in the Mobile Sensor Readings table called Mobile-Sensor-id where the formula I have used is:

“M” + “-” + STR([Sensor-id])

This means the concatenation of M and the hyphen and the original Sensor-id data to form the Mobile Sensor ID values. Thereafter, remove the original Sensor-Id field which is not relevant anymore.

Figure 6: Create a new field called Mobile-Sensor-id and remove the original Sensor-id

Next, same procedure as above for the Mobile, I have created a new calculated field in the Static Sensor Readings with Locations table called Static-Sensor-id where the formula I have used is:

“S” + “-” + STR([Sensor-id])

This means the concatenation of S and the hyphen and the original Sensor-id data to form the Static Sensor ID values. Thereafter, remove the original Sensor-Id field in this table as it is not relevant anymore.