IS428 AY2019-20T1 Assign Wendy Ng Sock Ling Transformation
VAST Challenge 2019 MC1: Crowdsourcing for Situational Awareness
|
|
|
|
|
Contents
Transforming Data Given
With the dataset given, the reports do not have an identifier. Hence, a Report ID column is created as seen below.
This file is then imported into tableau as a data source. Next, a pivot is done on the following columns in tableau:
- Buildings
- sewer_and_water
- power
- roads_and bridges
- medical
- shake_intensity
The following is the data with the columns above pivoted. The columns are then renamed as "Objects/shake intensity" and "Extend of damage/ shake intensity".
Creating An Aggregated Data Set
The aggregated data set is created for the ease of analysis done on the higher level where all reports for the timestamp and location are aggregated into one row. This data set is created using tableau prep where the given data is aggregated by location and time column as seen below. The following are the aggregated fields:
- Sum of the number of rows
- Average damage on the buildings
- Average damage on the medical facility
- Average damage on the power
- Average damage on the roads and bridges
- Average damage on the sewer and water
- Average shake intensity
The file is then saved as a csv output and imported into tableau as a data source. Next, a pivot is done on the following columns in tableau in the same way as the previous data set:
- Buildings
- Medical
- Power
- Roads and Bridges
- Sewer and Water
- Shake Intensity
The following is the data with the columns above pivoted. The columns are then renamed as "Objects/shake intensity" and "Extend of damage/ shake intensity".
Creating the Location Coordinates
Putting the Data Sets Together
This step is combining transformed data set and the aggregated data set with the custom map created. Both data set will be left joined to the custom map created in order to get the coordinates of the locations. When joining the locations, ensure that both the “Location” in the “Location coordinates” are of the data type "string" to prevent error in joining. The X and Y coordinates should have the data type changed to Longitude and Latitude respectively if they are not already changed.
The following is the transformed data set with the location coordinates left joined to it.
The following is the aggregated data set with the location coordinates file left joined to it.
