Difference between revisions of "IS428 AY2019-20T1 Assign Lee Cheng Leng EDA"

From Visual Analytics for Business Intelligence
Jump to navigation Jump to search
 
(5 intermediate revisions by the same user not shown)
Line 24: Line 24:
  
 
= Data Cleaning Process =
 
= Data Cleaning Process =
−
<!--
 
 
{| class="wikitable"
 
{| class="wikitable"
 
|-
 
|-
 
!  !! Problem #1
 
!  !! Problem #1
 
|-
 
|-
−
| Issue || The Static Sensor Locations and Readings are in two separate files. In order to make meaningful analysis, we would require the data from both files to be joined together into one table.
+
| Issue || Upon data exploration, I found that the Static Sensor Locations and Readings are in two separate files. In order to make meaningful analysis, we would require the data from both files.  
 
|-
 
|-
−
| Solution || Join the two files in Tableau Prep Builder using the 'Join' function. We would be joining them on the sensor-id, which are unique identifiers of the static sensors.  
+
| Solution || Join the two files in Tableau Prep Builder using the 'Join' function. We would be joining them on the sensor-id, which are unique identifiers of the static sensors. <br>
 +
Steps:
 +
# Import the StaticSensorLocations.csv and StaticSensorReadings.csv files into Tableau Prep Builder.
 +
# Join the two files together on the sensor-id column as shown below.
 +
<center>[[File:DataClean-1.png|400px|frameless]]</center>
 +
 
 
|}
 
|}
  
Line 38: Line 42:
 
! !! Problem #2  
 
! !! Problem #2  
 
|-
 
|-
−
| Issue || It was found that there are sensor-ids that overlap in the static and mobile sensors, such as 15. If we were to combine the two data sources for analysis in Tableau, it might be misleading as the user would not know if the sensor is a static or mobile one.  
+
| Issue || From the data description, we can tell that the user-id of the mobile sensors are not unique as some users choose not to change their user-id from the default, ‘MySensor’. This means that user-id is not a unique identifier of the mobile sensors. Furthermore, static sensors do not have any user-id tagged to it. Hence, it is redundant. Also, all the readings are taken in the units of counts per minute (cpm), hence there is no need for that column as all the column values are identical.
 
|-
 
|-
−
| Solution || Add a calculated field "Sensor Type", so as to identify if the sensor is a mobile or static sensor. This was done in Tableau Prep Builder.
+
| Solution || Drop the user-id and units column during data cleaning so as to remove any unnecessary attributes and reduce runtime. This can be done in Tableau Prep Builder.
 +
<center>[[File:DataClean-2.png|400px|frameless]]</center>
 
|}
 
|}
 +
 +
<br>
 +
Final Data Preparation Workflow from Tableau Prep Builder: <br>
 +
<center>[[File:DataClean-3.png|500px|frameless]]</center>
 +
<br>
  
 
{| class="wikitable"
 
{| class="wikitable"
Line 47: Line 57:
 
! !! Problem #3
 
! !! Problem #3
 
|-
 
|-
−
| Issue || The mobile and static sensor readings were contained in two different files, StaticSensorReadings and MobileSensorReadings. However, in order to create a Tableau dashboard with both, both files would need to be merged together.
+
| Issue || The mobile sensor readings file only contains latitude and longitude data for the sensor positions, but does not provide any information regarding the neighbourhoods which the sensors are currently located in. This would pose a problem if we are trying to analyse the sensor readings based on the neighbourhoods, in order to identify which neighbourhoods have high uncertainty in readings.
 
|-
 
|-
−
| Solution || Upon data cleaning of the static and mobile sensor data, join the sources together with ‘Union’ on Tableau Data Prep.  
+
| Solution || Solution: We would be able to map each of the points by doing a spatial join between the MobileSensorReadings.csv and StHimark.shp shapefile, namely the Intercept Inner Join. This would create an additional ‘Neighbourhood’ column for us to identify the neighbourhood that the sensor is currently located in for us to do more detailed analysis. <br>
 +
Steps:
 +
# Import the StHimark.shp file and the MobileSensorReadings.csv file into Tableau.
 +
# Combine the files using the Join function. The input should be as follows:
 +
<center>[[File:DataClean-4.png|400px|frameless]]</center>
 
|}
 
|}
  
Line 56: Line 70:
 
! !! Problem #4
 
! !! Problem #4
 
|-
 
|-
−
| Issue || Among the mobile sensors, the one with sensor-id 12 has an outlier value of 57345 on Day 9, 2am. We can hypothesise that this value was due to a malfunction in the device as the other readings collected by sensors in the vicinity of sensor-id 12 at that time, such as sensor-id 7 and 8, both record readings that average to 10.5.  
+
| Issue || From the data description, we can tell that the user-id of the mobile sensors are not unique as some users choose not to change their user-id from the default, ‘MySensor’. This means that user-id is not a unique identifier of the mobile sensors. Furthermore, static sensors do not have any user-id tagged to it. Hence, it is redundant. Also, all the readings are taken in the units of counts per minute (cpm), hence there is no need for that column as all the column values are identical.
 
|-
 
|-
−
| Solution || I chose to remove the outlier from the dataset as it caused the axis to be heavily skewed, such that we would not be able to gain insights from the other data points. This can be done easily on Tableau Prep Builder using the ‘Filter’ function.
+
| Solution || Drop the user-id and units column during data cleaning so as to remove any unnecessary attributes and reduce runtime. This can be done in Tableau Prep Builder.
 
|}
 
|}
  
Line 65: Line 79:
 
! !! Problem #5
 
! !! Problem #5
 
|-
 
|-
−
| Issue || For each sensor, the readings are taken every five seconds. As a result, I realised that the granularity of the data is too fine, which might hinder us from being able to create visualisations from which actionable insights can be derived effectively.
+
| Issue || The mobile sensor readings file only contains latitude and longitude data for the sensor positions, but does not have any neighbourhood data. This would pose a problem if we are trying to analyse the sensor readings based on the neighbourhood it is currently located in, in order to compare across neighbourhoods for our analysis.
 
|-
 
|-
−
| Solution || I chose to group the data by a longer time frame of 10 minute intervals. This can be done in Tableau Prep Builder using a calculated field, such that the data that is used in Tableau would be a much smaller subset of the original provided data. This would allow the visualisations to be generated quicker, and for actionable insights to be derived more effectively.  
+
| Solution || We would be able to map each of the points by doing a spatial join between the MobileSensorReadings.csv and StHimark.shp shapefile, namely the Intercept Outer Join. This would create an additional ‘Neighbourhood’ column for us to identify the neighbourhood that the sensor is currently located in for us to do more detailed analysis.
 
|}
 
|}
 +
  
 
{| class="wikitable"
 
{| class="wikitable"
 
|-
 
|-
 
! !! Problem #6
 
! !! Problem #6
−
|-
 
−
| Issue || Having solved the above issue, there were still almost a million data rows present in the combined sensor data file. This was because upon grouping the sensor readings into 10 minute intervals, the number of records present in the file would still remain the same.
 
−
|-
 
−
| Solution || Hence, I chose to aggregate the data based on the combination of 'Sensor Type', 'sensor-id', 'Timestamp', 'Lat' and 'Long'. I also aggregated the values by taking the average of the readings across the 10 minute interval, so as to provide a more accurate representation of the sensor readings within the 10 minute interval. 
 
−
|}
 
−
 
−
{| class="wikitable"
 
−
|-
 
−
! !! Problem #7
 
−
|-
 
−
| Issue || From the data description, we can tell that the user-id of the mobile sensors are not unique as some users choose not to change their user-id from the default, ‘MySensor’. This means that user-id is not a unique identifier of the mobile sensors. Furthermore, static sensors do not have any user-id tagged to it. Hence, it is redundant.
 
−
|-
 
−
| Solution || Drop the user-id column during data cleaning so as to remove any unnecessary attributes and reduce noise.
 
−
|}
 
−
 
−
{| class="wikitable"
 
−
|-
 
−
! !! Problem #7
 
 
|-
 
|-
 
| Issue || The map provided, 'StHimarkNeighborhoodMap.png', is too rich in colour to be used as a background map image.  
 
| Issue || The map provided, 'StHimarkNeighborhoodMap.png', is too rich in colour to be used as a background map image.  
 
|-
 
|-
 
| Solution || I used Powerpoint to recolour the image to a greyscale. Upon applying washout before applying it as the background map image, I would then be able to obtain a background that does not take away the attention from the actual data points.  
 
| Solution || I used Powerpoint to recolour the image to a greyscale. Upon applying washout before applying it as the background map image, I would then be able to obtain a background that does not take away the attention from the actual data points.  
 +
<center>[[File:DataClean-5.png|400px|frameless]]</center>
 
|}
 
|}
−
-->
 

Latest revision as of 15:25, 12 October 2019

Frederic-paulussen-LWnD8U2OReU-unsplash.jpg VAST 2019 MC2: Citizen Science to the Rescue

Overview

Data Exploration and Transformation

Interactive Visualisation

Task Findings

References

Data Cleaning Process

Problem #1
Issue Upon data exploration, I found that the Static Sensor Locations and Readings are in two separate files. In order to make meaningful analysis, we would require the data from both files.
Solution Join the two files in Tableau Prep Builder using the 'Join' function. We would be joining them on the sensor-id, which are unique identifiers of the static sensors.

Steps:

  1. Import the StaticSensorLocations.csv and StaticSensorReadings.csv files into Tableau Prep Builder.
  2. Join the two files together on the sensor-id column as shown below.
DataClean-1.png
Problem #2
Issue From the data description, we can tell that the user-id of the mobile sensors are not unique as some users choose not to change their user-id from the default, ‘MySensor’. This means that user-id is not a unique identifier of the mobile sensors. Furthermore, static sensors do not have any user-id tagged to it. Hence, it is redundant. Also, all the readings are taken in the units of counts per minute (cpm), hence there is no need for that column as all the column values are identical.
Solution Drop the user-id and units column during data cleaning so as to remove any unnecessary attributes and reduce runtime. This can be done in Tableau Prep Builder.
DataClean-2.png


Final Data Preparation Workflow from Tableau Prep Builder:

DataClean-3.png


Problem #3
Issue The mobile sensor readings file only contains latitude and longitude data for the sensor positions, but does not provide any information regarding the neighbourhoods which the sensors are currently located in. This would pose a problem if we are trying to analyse the sensor readings based on the neighbourhoods, in order to identify which neighbourhoods have high uncertainty in readings.
Solution Solution: We would be able to map each of the points by doing a spatial join between the MobileSensorReadings.csv and StHimark.shp shapefile, namely the Intercept Inner Join. This would create an additional ‘Neighbourhood’ column for us to identify the neighbourhood that the sensor is currently located in for us to do more detailed analysis.

Steps:

  1. Import the StHimark.shp file and the MobileSensorReadings.csv file into Tableau.
  2. Combine the files using the Join function. The input should be as follows:
DataClean-4.png
Problem #4
Issue From the data description, we can tell that the user-id of the mobile sensors are not unique as some users choose not to change their user-id from the default, ‘MySensor’. This means that user-id is not a unique identifier of the mobile sensors. Furthermore, static sensors do not have any user-id tagged to it. Hence, it is redundant. Also, all the readings are taken in the units of counts per minute (cpm), hence there is no need for that column as all the column values are identical.
Solution Drop the user-id and units column during data cleaning so as to remove any unnecessary attributes and reduce runtime. This can be done in Tableau Prep Builder.
Problem #5
Issue The mobile sensor readings file only contains latitude and longitude data for the sensor positions, but does not have any neighbourhood data. This would pose a problem if we are trying to analyse the sensor readings based on the neighbourhood it is currently located in, in order to compare across neighbourhoods for our analysis.
Solution We would be able to map each of the points by doing a spatial join between the MobileSensorReadings.csv and StHimark.shp shapefile, namely the Intercept Outer Join. This would create an additional ‘Neighbourhood’ column for us to identify the neighbourhood that the sensor is currently located in for us to do more detailed analysis.


Problem #6
Issue The map provided, 'StHimarkNeighborhoodMap.png', is too rich in colour to be used as a background map image.
Solution I used Powerpoint to recolour the image to a greyscale. Upon applying washout before applying it as the background map image, I would then be able to obtain a background that does not take away the attention from the actual data points.
DataClean-5.png
Retrieved from ‘https://wiki.smu.edu.sg/1920t1is428g1/index.php?title=IS428_AY2019-20T1_Assign_Lee_Cheng_Leng_EDA&oldid=3776’