Difference between revisions of "IS428 AY2019-20T1 Assign Foo Yong Long Data Transformation"
Ylfoo.2017 (talk | contribs) (Created page with "<div style=background:#aec6cf border:#A3BFB1> 145px|left <b><font size = 6; color="#43464b"> VAST Challenge 2019 Mini-Challenge 2:<br>Ci...") |
Ylfoo.2017 (talk | contribs) |
||
(7 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
− | + | [[File:cover.png|900px|center]] | |
− | [[File: | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | | | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | | style=" | + | <!-- Header Start --> |
− | + | {|style="background-color:#E5E4E2; color:#dfdddc; padding: 10 0 10 0;" width="100%" cellspacing="0" cellpadding="0" valign="top" border="0" | | |
− | [[IS428 AY2019-20T1 Assign Foo Yong | + | | style="padding:0.3em; font-size:100%; background-color:#dfdddc; border-bottom:0px solid #000000; text-align:center; color:#000000" width="19%" | |
+ | [[IS428 AY2019-20T1 Assign Foo Yong Long|<font color="#000000" size=2><b>OVERVIEW</b></font>]] | ||
− | | style=" | + | | style="border-bottom:0px solid #dfdddc; background:none;" width="1%" | |
− | + | | style="padding:0.3em; font-size:100%; background-color:#dfdddc; border-bottom:0px solid #000000; text-align:center; color:#000000" width="19%" | | |
− | [[IS428 AY2019-20T1 Assign Foo Yong | + | [[IS428 AY2019-20T1 Assign Foo Yong Long_Data Transformation|<font color="#000000" size=2><b>DATA TRANSFORMATION</b></font>]] |
− | | style=" | + | | style="border-bottom:0px solid #dfdddc; background:none;" width="1%" | |
− | + | | style="padding:0.3em; font-size:100%; background-color:#dfdddc; border-bottom:0px solid #000000; text-align:center; color:#000000" width="19%" | | |
− | [[IS428 AY2019-20T1 Assign Foo Yong | + | [[IS428 AY2019-20T1 Assign Foo Yong Long_RiskAnalysis|<font color="#000000" size=2><b>RISKS</b></font>]] |
− | | style=" | + | | style="border-bottom:0px solid #dfdddc; background:none;" width="1%" | |
− | + | | style="padding:0.3em; font-size:100%; background-color:#dfdddc; border-bottom:0px solid #000000; text-align:center; color:#000000" width="19%" | | |
− | [[IS428 AY2019-20T1 Assign Foo Yong | + | [[IS428 AY2019-20T1 Assign Foo Yong Long:R&R|<font color="#000000" size=2><b>RECOMMENDATION AND RATIONALE</b></font>]] |
− | | | + | | style="border-bottom:0px solid #dfdddc; background:none;" width="1%" | |
+ | | style="padding:0.3em; font-size:100%; background-color:#dfdddc; border-bottom:0px solid #000000; text-align:center; color:#000000" width="19%" | | ||
+ | [[IS428 AY2019-20T1 Assign Foo Yong Long: Visualization|<font color="#000000" size=2><b>VISUALIZATION</b></font>]] | ||
|} | |} | ||
+ | <!-- Header End --> | ||
+ | <br> | ||
<p> MC2 contains data of the Mobile and Static Sensors which display radiation levels in Counts Per Minute(CPM) that supports the entire length of the simulation(6th April 2020,0000Hrs - 10th April 2020,2359Hrs). The data cleaning process is as follow: | <p> MC2 contains data of the Mobile and Static Sensors which display radiation levels in Counts Per Minute(CPM) that supports the entire length of the simulation(6th April 2020,0000Hrs - 10th April 2020,2359Hrs). The data cleaning process is as follow: | ||
Line 49: | Line 42: | ||
− | The data preparation was entirely done in Tableau Prep Builder. The final flow is as follow | + | The data preparation was entirely done in Tableau Prep Builder. The final flow is as follow: |
+ | |||
− | [File:MC2FINALFLOW.png|900px|center]] | + | [[File:MC2FINALFLOW.png|900px|center]] |
Line 65: | Line 59: | ||
[[File:StaticCombined.png|900px|center]] | [[File:StaticCombined.png|900px|center]] | ||
− | The static sensor data consist of two files. One of the files contains the | + | The static sensor data consist of two files. One of the files contains the longitudes and latitudes of the Sensors while the other file contains the timestamp of the sensors. Both files were combined utilizing an inner join by matching their Sensor IDs. |
|- | |- | ||
| | | | ||
Line 71: | Line 65: | ||
|| | || | ||
− | + | The column "Value" was removed from both the sensors file as it is redundant. A column "Entity" was created for all data to identify the respective data points." | |
+ | |||
+ | "MobileSensor" value in column "Entity" represents the data points belonging to mobile sensors. | ||
+ | |||
+ | "StaticSensor" value in column "Entity" represents the data points belonging to static sensors. | ||
+ | |||
+ | "Hospital" value in column "Entity" represents the data points belonging to hospitals. | ||
+ | |||
+ | "Always Safe Nuclear" value in column "Entity" represents the data points belonging to Always Safe nuclear plant. | ||
+ | |||
+ | These were used to differentiate the data points when combing the various data into one single data table which will be explained later. | ||
+ | |||
+ | |||
+ | [[File:CombinedClean.png|900px|center]] | ||
+ | |||
+ | |||
|- | |- | ||
| | | | ||
Line 77: | Line 86: | ||
|| | || | ||
− | + | [[File:Union1.png|900px|center]] | |
+ | [[File:Union2.png|900px|center]] | ||
+ | [[File:Union3.png|900px|center]] | ||
+ | |||
+ | Next, we utilize the common field, "Entity", and the similarity in format and columns to create a union for the different datasets. | ||
+ | |||
|- | |- | ||
| | | | ||
4 | 4 | ||
|| | || | ||
− | + | [[File:finaloutput.png|900px|center]] | |
− | | | + | |
− | | | + | The data is output as an extract and imported into Tableau. |
− | |||
− | |||
− | |||
|} | |} | ||
+ | |||
<br> | <br> | ||
<p> | <p> | ||
− | == | + | == Additional Fields in Tableau == |
{| class="wikitable" style="background-color:#FFFFFF;" width="100%" | {| class="wikitable" style="background-color:#FFFFFF;" width="100%" | ||
Line 103: | Line 115: | ||
1 | 1 | ||
|| | || | ||
− | + | ||
+ | To ensure a more in-depth analysis and visualization, columns indicating '''distances in miles''' are being created to highlight the distance of each point from different entities such as hospitals and the nuclear plant. There are 3 new columns created in total : | ||
+ | |||
+ | 1)"Distance from Nuclear Plant (Miles) " | ||
+ | |||
+ | The column showing the distance of each data point from the nuclear plant | ||
+ | |||
+ | [[File:DistanceFromPlant.png|900px|center]] | ||
+ | |||
+ | 2)"1.Distance from PH HP (Miles)" | ||
+ | |||
+ | The column showing the distance of each data point from the hospital in Palace Hills. | ||
+ | |||
+ | [[File:DistanceFromHospital.png|900px|center]] | ||
+ | |||
+ | |||
+ | 3)"Shortest Distance from Hospital " | ||
+ | |||
+ | The column showing the distance of each data point from the nearest hospital. | ||
+ | |||
+ | [[File:ShortestDistanceFromHospitals.png|900px|center]] | ||
+ | |||
|} | |} | ||
<br> | <br> | ||
<p> | <p> |
Latest revision as of 05:11, 13 October 2019
MC2 contains data of the Mobile and Static Sensors which display radiation levels in Counts Per Minute(CPM) that supports the entire length of the simulation(6th April 2020,0000Hrs - 10th April 2020,2359Hrs). The data cleaning process is as follow:
Data Preparation
To ensure a thorough analysis of the data, the values and locations of both Mobile and Static sensor categories have to be analyzed together. I have created two excel files labeled "HospitalLocations.csv" and "AlwaysSafe.csv" as shown below. This data will be utilized for distance calculation and to create a more interactive dashboard for the end-user.
Longitude and Latitude of Hospital Locations
Longitude and Latitude of Always Safe Plant
The data preparation was entirely done in Tableau Prep Builder. The final flow is as follow:
Step | Description |
---|---|
1 |
The static sensor data consist of two files. One of the files contains the longitudes and latitudes of the Sensors while the other file contains the timestamp of the sensors. Both files were combined utilizing an inner join by matching their Sensor IDs. |
2 |
The column "Value" was removed from both the sensors file as it is redundant. A column "Entity" was created for all data to identify the respective data points." "MobileSensor" value in column "Entity" represents the data points belonging to mobile sensors. "StaticSensor" value in column "Entity" represents the data points belonging to static sensors. "Hospital" value in column "Entity" represents the data points belonging to hospitals. "Always Safe Nuclear" value in column "Entity" represents the data points belonging to Always Safe nuclear plant. These were used to differentiate the data points when combing the various data into one single data table which will be explained later.
|
3 |
Next, we utilize the common field, "Entity", and the similarity in format and columns to create a union for the different datasets. |
4 |
The data is output as an extract and imported into Tableau. |
Additional Fields in Tableau
Step | Description |
---|---|
1 |
To ensure a more in-depth analysis and visualization, columns indicating distances in miles are being created to highlight the distance of each point from different entities such as hospitals and the nuclear plant. There are 3 new columns created in total : 1)"Distance from Nuclear Plant (Miles) " The column showing the distance of each data point from the nuclear plant 2)"1.Distance from PH HP (Miles)" The column showing the distance of each data point from the hospital in Palace Hills.
The column showing the distance of each data point from the nearest hospital. |