Difference between revisions of "IS428 AY2019-20T1 Assign Wong Kuan Wai Gordon"
Line 148: | Line 148: | ||
Application.ScreenUpdating = True | Application.ScreenUpdating = True | ||
|}</p></center> | |}</p></center> | ||
+ | |||
+ | With that, I am now able to display neighborhoods which do not have any damage reports at the specified time. As illustration of the desired result is as follows: | ||
+ | [[Image:Map4.png|500px|center]] | ||
==Data Preparation for Damage Reports== | ==Data Preparation for Damage Reports== |
Revision as of 04:13, 12 October 2019
Contents
Overview
St. Himark has been hit by an earthquake, leaving officials scrambling to determine the extent of the damage and dispatch limited resources to the areas in most need. They quickly receive seismic readings and use those for an initial deployment but realize they need more information to make sure they have a realistic understanding of the true conditions throughout the city.
In a prescient move of community engagement, the city had released a new damage reporting mobile application shortly before the earthquake. This app allows citizens to provide more timely information to the city to help them understand damage and prioritize their response. In this mini-challenge, use app responses in conjunction with shake maps of the earthquake strength to identify areas of concern and advise emergency planners. Note: the shake maps are from April 6 and April 8 respectively. With emergency services stretched thin, officials are relying on citizens to provide them with much needed information about the effects of the quake to help focus recovery efforts. By combining seismic readings of the quake, responses from the app, and background knowledge of the city, help the city triage their efforts for rescue and recovery.
Objective
My task as a visual analytics expert is to analyze and provide interactive visualizations through seismic readings of the earthquake, damage reports from the mobile application and background knowledge of the city. These visualizations will enable citizens and emergency planners with situational awareness of the disaster. Ultimately, this will further optimize the response of emergency services by enabling them to dispatch their limited resources to the right place at the right time with the right emergency tools, prioritizing their efforts for rescue and recovery. Therefore, my task as a visual analytics expert will explore the following:
1) Geometric damage patterns of St. Himark and its neighborhood
2) Earthquake scale and classes based on seismic readings
3) Sending the correct emergency response based on infrastructure damages
4) Damage inflicted to St. Himark infrastructures in each neighborhood
5) Uncertainty of damage reports
Task #1: Emergency responders will base their initial response on the earthquake shake map. Use visual analytics to determine how their response should change based on damage reports from citizens on the ground. How would you prioritize neighborhoods for response? Which parts of the city are hardest hit?
Task #2: Use visual analytics to show uncertainty in the data. Compare the reliability of neighborhood reports. Which neighborhoods are providing reliable reports? Provide a rationale for your response.
Task #3: How do conditions change over time? How does uncertainty in change over time? Describe the key changes you see.
About the Data
The data for Mini-Challenge 1 includes one (CSV) file spanning the entire length of the event, containing (categorical) individual reports of shaking/damage by neighborhood over time. Reports are made by citizens at any time, however, they are only recorded in 5-minute batches/increments due to the server configuration. The fields of the data and its relevance are as follows:
1) time: timestamp of incoming report/record, in the format YYYY-MM-DD hh:mm:ss
2) sewer_and_water: Value of how bad the damage was to St. Himark's sewer and water infrastructure (0 - lowest, 10 - highest; missing data allowed)
3) power: Value of how bad the damage was to St. Himark's power infrastructure (0 - lowest, 10 - highest; missing data allowed)
4) roads_and_bridges: Value of how bad the damage was to St. Himark's roads and bridges infrastructure (0 - lowest, 10 - highest; missing data allowed)
5) medical: Value of how bad the damage was to St. Himark's medical infrastructure (0 - lowest, 10 - highest; missing data allowed)
6) buildings: Value of how bad the damage was to St. Himark's buildings infrastructure (0 - lowest, 10 - highest; missing data allowed)
7) shake_intensity: Value of how violent the shaking was (0 - lowest, 10 - highest; missing data allowed)
8) location: ID of neighborhood where person reporting is feeling the shaking and/or seeing the damage
The Shapefile "StHimark.shp" and image "StHimarkMapBlank.png" was taken from Mini-Challenge 2 to be used to map and display the St. Himark map
Data Preparation
Tools Used:
Data Preparation for St. Himark Map
The aim for this step is to be able to display the background overlay "StHimarkBlankMap.png" along with the given the Shapefile "StHimark.shp" from Mini-Challenge 2.
However, performing these steps alone produced the following undesired results:
With reference to Kelvin Chia's discussion in the class forum and research in the Tableau community, this undesired result was produced as Tableau is unable to identify and determine how the polygons are scaled by the Geometry which then defaults the polygons to be small in size and placed as centroids.
Therefore, a solution to this problem is to create our own custom filled map. This is done through research and an online tool by the Tableau community. As a results, 2 CSV files were generated: StHimark_Features.csv and StHimark_Points.csv.
StHimark_Features.csv will contain a row for each row in the shape file with all the feature details (including the calculated coordinates of the center of the shapes), StHimark_Points.csv will contain a row per boundary point, structured as needed to draw polygons in Tableau. Used like that, with no optional parameters, both files will include a generated unique ID column, which can be used to join the two files, so classifying details from the shapefiles can be used as dimensions in the Tableau workbook. The sample screenshot for StHimark_Features.csv and StHimark_Points.csv are illustrated respectively:
We then perform an inner join between StHimark_Features.csv and StHimark_Points.csv by StHimark ID and the desired result is achieved:
Neighborhoods with No Reports at a Specified Date and Time
As I look into the raw data in "mc1-reports-data.csv", I realized that if we filter the damage by date (Eg. 06/04/2020 00:00:00), the map will only display neighborhoods that have received damage reports. Neighborhoods that DID NOT receive any damage reports during this time will not be displayed in the map. This is not what I want the visualization to look like as I want all neighborhoods to be displayed regardless of whether they received any damage reports or not. The undesired sample display of the map is as follows:
" Therefore the solution to this issue is to create dummy data. For example, at "06/04/2020 00:00:00" only locations 2, 3, 4, 8, 15, 17 and 18 received AT LEAST 1 damage report. As such, I will create and insert rows for locations 1, 5, 6, 7, 9, 10, 11, 12, 13, 14, 16 and 19. However, since there is technically no damage reports made for these locations, I will assign NULL values for all the infrastructure damages and shake intensity. Lastly, I assign a value of "-1" instead of a NULL value for the average damage. To perform this step, I utilized Microsoft Excel's Visual Basic for Application (VBA) to help me automate this process. To allow my program for continuous usage , I have created a new Excel workbook with the VBA that is able to upload the raw data "mc1-reports-data.csv" and perform the automation. My VBA source codes are as follows:
Dim x As Long
Dim dict As New Scripting.Dictionary Dim uniqueId As String Dim location As String Dim datetimeString As String Dim nextDatetimeString As String Dim NumRows As Long Dim lastRow As Long Application.ScreenUpdating = False 'Set numrows = number of rows of data. NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count lastRow = NumRows 'Select cell A2. Range("A2").Select 'Establish "For" loop to loop "NumRows" number of times. 'Using a dictionary 'Loop through each row and add the location ID in to the dictionary 'For loop to loop through 1 to 19 to check if dictionary contains the location IDs 'If no, insert dummy data with datetime, locationId and "-1" average damage into last row For x = 2 To NumRows datetimeString = Format(Range("A" + CStr(x)).Value, "DD/MM/YYYY HH:MM:SS") location = Range("I" + CStr(x)).Value If x = 2 Then dict.Add Key:=location, Item:=location End If nextDatetimeString = Format(Range("A" + CStr(x + 1)).Value, "DD/MM/YYYY HH:MM:SS") If datetimeString = nextDatetimeString And dict.Exists(location) = False Then dict.Add Key:=location, Item:=location ElseIf datetimeString <> nextDatetimeString And dict.Exists(location) = False Then dict.Add Key:=location, Item:=location For y = 1 To 19 If dict.Exists(CStr(y)) = False Then lastRow = lastRow + 1 Range("A" + CStr(lastRow)).Value = datetimeString Range("B" + CStr(lastRow)).Value = -1 Range("I" + CStr(lastRow)).Value = CStr(y) End If Next dict.RemoveAll ElseIf datetimeString <> nextDatetimeString And dict.Exists(location) = True Then For y = 1 To 19 If dict.Exists(CStr(y)) = False Then lastRow = lastRow + 1 Range("A" + CStr(lastRow)).Value = datetimeString Range("B" + CStr(lastRow)).Value = -1 Range("I" + CStr(lastRow)).Value = CStr(y) End If Next dict.RemoveAll End If Next Application.ScreenUpdating = True |
With that, I am now able to display neighborhoods which do not have any damage reports at the specified time. As illustration of the desired result is as follows:
Data Preparation for Damage Reports
This step aims to clean and transform the raw data and produce one with the following fields: 1) damageReportId: Unique ID of the damage report 2) time: Date and time the report was made (DD/MM/YYYY HH:MM:SS) 3) location: Location ID of the neighborhood 4) Nbrhood: Name of the neighborhood 5) Infrastructure: Any of the 5 types of infrastructure of St. Himark (Sewer and Water, Power, Medical, Roads and Bridges, Buildings) 6) Damage: Value of how bad the damage is (0 - lowest, 10 - highest; missing data allowed)
To achieve the desired data, I used Tableau Prep Builder to perform the cleaning and transformation. The illustration of the steps taken are as follows:
In the first cleaning step, I removed the field "shake_intensity" as this is not a field that I want in this data. I then renamed the fields to be easily understood (Eg. "Sewer and Water" instead of "sewer_and_water". The next step is the pivoting of the fields; Sewer and Water, Power, Medical, Roads and Bridges and Buildings. This step is illustrated as follows:
Next, as my current output only contains the neighborhood ID, I want the neighborhood Name to be specified in my data as well. To do this, I imported the StHimark_Features.csv file obtained from "Data Preparation for St. Himark Map". First, I remove the unwanted fields; StHimark_Id, latitude and longitude. Next, I then performed an inner join of "location" field with StHimark_Features.csv's "Id" field. This step is illustrated as follows:
My final output is as follows: (Note that I did not remove damage reports with NULL values for damage)
The Visualization
Testing
Tasks
Testing
References
Testing
Comments
Feel free to provide feedback!