ISSS608 2016-17 T3 Assign CHEN YINJUE Data Analytics
VAST Challenge 2017 : Help Rose-Crested Blue Pipit
|
|
|
|
|
|
Data Visualization & Insights
After data preparation, now it's time to draw sunburst chart in tableau. Firstly, open tableau, under ‘Connect’ click ‘Excel’ and then select the Excel file we prepared before. Noted that we should choose ‘open with legacy connection’ in this step so that custom SQL can be used later. Secondly, drag ‘New Custom SQL’ to the area with ‘Drag tables here’ and apply SQL code as below:
Besides, to lay out the path on the map later, the coordinate table should also be loaded into tableau. So, at the left pane of ‘Data source’, click ‘Add’ (to the right of ‘Connection’) and choose file ‘Coordinate table’. After that, we can select ‘Coordinate table’ under ‘Connection’ and drag ‘Coordinate table.csv’ to join with ‘Custom SQL Query’ by matching the ‘gate name’ of these two sheets. Below shows a sample of output:
Below is the sunburst diagram showing from level 1 to level 20. To make the graph readable, I purposely colored same gate type with same color:
• Entrance: Blue
• General-Gate: Yellow
• Gate: Brown
• Camp Site: Green
• Ranger-stop: Red
• Ranger-base: Purple
Now, from this sunburst chart, we can summarize several general route patterns:
1. Cars that start from entrance:
• Cars that enter by an entrance and then directly exit the park by an entrance (can be either the same entrance or another entrance):
Entrance > Entrance
• Cars that enter the park and go through one or several general-gate(s) and then exit the park:
Entrance > General-gate(s) > Entrance
• Cars that enter the park for camping (basically includes all routes with camp sites in):
Entrance > … > Camping(s) > … > Entrance
• Cars that enter the park and pass by ranger-stop(s) without going to any camp site:
Entrance > … > Ranger-stop(s) > … > Entrance
• Cars that enter the park and illegally pass by gate(s):
Entrance > … > Gate(s) > … > Entrance
2. Cars that start from ranger-base, which basically should be cars with type ‘2P’. We should pay attention to checking whether there is any other car-type that illegally has this kind of route pattern.
After grabbing an overview as above, next we can begin to divide our datasets… (steps description to be continued)
Briefly, the restructured dataset can be divided into:
• Single path & Single day
• Single path & Multiple days
• Multiple paths
As mentioned before, we have several patterns that can be viewed in sunburst chart already. Therefore, we can further divide the dataset based on route patterns. After that weight of routes can be calculated and sorted and only those records whose routes have relatively bigger weights than others will be picked up to treat as major path patterns. Next we can extract such data with bigger weights to build calendar view to show the duration (which months or what hours during a day) have heavier traffic volumes for specific path patterns, respectively.
