Difference between revisions of "IS428 AY2019-20T1 Assign Chua Xuan Ni, Rachel DATA TRANSFORMATION"
Jump to navigation
Jump to search
Line 32: | Line 32: | ||
{| class="wikitable" | {| class="wikitable" | ||
|- | |- | ||
− | ! Issue !! Solution | + | ! Issue !! Solution |
|- | |- | ||
| Issue 1: In the current dataset, “mc1-reports-data.csv”. The different categories of damage are a column by itself, making it difficult to compare the damage by the categories. || Pivot the different categories of damage – sewer_and_water, power, roads_and_bridges, medical and buildings into a new column, “Damage Area”, with their respective damage level in a new column, “Damage Level (0-10)”. || To do this, I used Tableau Prep’s “Pivot function” to make the changes. After applying the pivot function, you can see that I now have 2 new columns containing the data from sewer_and_water, power, roads_and_bridges, medical and buildings merged as one. | | Issue 1: In the current dataset, “mc1-reports-data.csv”. The different categories of damage are a column by itself, making it difficult to compare the damage by the categories. || Pivot the different categories of damage – sewer_and_water, power, roads_and_bridges, medical and buildings into a new column, “Damage Area”, with their respective damage level in a new column, “Damage Level (0-10)”. || To do this, I used Tableau Prep’s “Pivot function” to make the changes. After applying the pivot function, you can see that I now have 2 new columns containing the data from sewer_and_water, power, roads_and_bridges, medical and buildings merged as one. | ||
Line 38: | Line 38: | ||
| Issue 2: In the given data – mc1-majorquake-shakemap.png, we can see that each value of Damage Level from 1 to 10 is categorized into the perceived shaking and we should align the values of each damage level as per the perceived shaking as well. | | Issue 2: In the given data – mc1-majorquake-shakemap.png, we can see that each value of Damage Level from 1 to 10 is categorized into the perceived shaking and we should align the values of each damage level as per the perceived shaking as well. | ||
[[File:Rachel.chua.2017 issue2.1.png|frameless|center]] | [[File:Rachel.chua.2017 issue2.1.png|frameless|center]] | ||
− | || As seen from the data, damage level has 0 as well, but the perceived shaking does not include 0. However, since 1 is categorized as “not felt”, 0 should fit into this category as well since it is the mildest possible perceived shaking level. | + | || As seen from the data, damage level has 0 as well, but the perceived shaking does not include 0. However, since 1 is categorized as “not felt”, 0 should fit into this category as well since it is the mildest possible perceived shaking level. To do this, I have created a new calculated field / column, “Damage Category”, to bin all the values respectively (ie. damage level between 0-1 == “Not Felt”, damage level between 2-3 == “Weak”, damage level 4 == “Light”, damage level 5 == “Moderate”, damage level 6 == “Strong”, damage level 7 == “Very Strong”, damage level 8 == “Severe”, damage level 9 == “Violent” and damage level 10 == “Extreme”). Do the same for shake intensity and create a new calculated field/column, “Shake Intensity Category”. |
[[File:Rachel.chua.2017 issue2.2new.png|frameless|center]] | [[File:Rachel.chua.2017 issue2.2new.png|frameless|center]] | ||
|- | |- | ||
− | | | + | | Issue 3: Since we know that the earthquake happened on 8th April, I would also like to categorize the data into pre-quake, earthquake and post-quake to look as the possible uncertainties in the data. || In order to do this, I have created another calculated field, “Event” to split the data by the day part of the datetime field so as to allow filter base on the day. Pre-quake will include data from 6th-7th April, Earthquake will include data from 8th April and Post-quake will include data from 9th April onwards. |
+ | [[File:Rachel.chua.2017 issue3.1.png|frameless|center]] | ||
|- | |- | ||
| Example || Example || Example | | Example || Example || Example |
Revision as of 19:39, 11 October 2019
|
|
|
|
|
Dataset Analysis & Transformation Process
Issue | Solution | |
---|---|---|
Issue 1: In the current dataset, “mc1-reports-data.csv”. The different categories of damage are a column by itself, making it difficult to compare the damage by the categories. | Pivot the different categories of damage – sewer_and_water, power, roads_and_bridges, medical and buildings into a new column, “Damage Area”, with their respective damage level in a new column, “Damage Level (0-10)”. | To do this, I used Tableau Prep’s “Pivot function” to make the changes. After applying the pivot function, you can see that I now have 2 new columns containing the data from sewer_and_water, power, roads_and_bridges, medical and buildings merged as one. |
Issue 2: In the given data – mc1-majorquake-shakemap.png, we can see that each value of Damage Level from 1 to 10 is categorized into the perceived shaking and we should align the values of each damage level as per the perceived shaking as well. | As seen from the data, damage level has 0 as well, but the perceived shaking does not include 0. However, since 1 is categorized as “not felt”, 0 should fit into this category as well since it is the mildest possible perceived shaking level. To do this, I have created a new calculated field / column, “Damage Category”, to bin all the values respectively (ie. damage level between 0-1 == “Not Felt”, damage level between 2-3 == “Weak”, damage level 4 == “Light”, damage level 5 == “Moderate”, damage level 6 == “Strong”, damage level 7 == “Very Strong”, damage level 8 == “Severe”, damage level 9 == “Violent” and damage level 10 == “Extreme”). Do the same for shake intensity and create a new calculated field/column, “Shake Intensity Category”. | |
Issue 3: Since we know that the earthquake happened on 8th April, I would also like to categorize the data into pre-quake, earthquake and post-quake to look as the possible uncertainties in the data. | In order to do this, I have created another calculated field, “Event” to split the data by the day part of the datetime field so as to allow filter base on the day. Pre-quake will include data from 6th-7th April, Earthquake will include data from 8th April and Post-quake will include data from 9th April onwards. | |
Example | Example | Example |
Example | Example | Example |