In this second part of the article, we will be continuing our analysis from the dataset that we cleaned and explored in the previous part. For that purpose, the end goal of this is to create an informative and interactive dashboard that can be used to derive insights from the data and hopefully help those in charge to make further decisions.
To achieve so, we will be using the quite arguably the most famous data visualization tool in the world, Tableau. Now of course we will be using the public or “free” version of the software, because it is sufficient to serve our purpose and of course, it’s free! Once that is all set we shall begin our visualization.
Now, remember on the previous part of the article we pointed out that the “Longitude” and “Latitude” columns the one that describes the approximate location of where the crime happened, is important. What better way to visualize these informations rather than a map, that way we can see where these crimes happened visually, and maybe gain new knowledge from this aspect of the data. To begin, load the dataset into tableau by just uploading the cleaned dataset .csv file we created at the end of the previous article.
To make the first visualization, the map as we described before, we can begin by dragging the longitude and latitude pair into columns and rows at tableau. If done right, the visualization should look something like this image below, for now please ignore the titles, filters, and all other details, all of it will be touched upon later.
We can see that the map above doesn’t really help us with anything right, it’s just a lot of dots scattered around the UK. We can improve this by applying a different type of mark, as you can see on the left-side of the visualization, there is a section titled marks. Now, simply put marks in tableau as I see it is in the format you want to display your data in. Because we want to plot all the cases and where it happened at the globe, we can plot the Crime ID column since it represents the individual detail of the cases or in other words, each Crime ID represents an individual row (if we look at it from the perspective of the previous article). Therefore we can drag Crime ID into the marks, and for our purpose we will be choosing density as our mark as it will results in a more descriptive and informative visualization. The results of it should look like this image below:
You can choose the color of the marks as you like it, for this purpose I’ll be going with this orange-themed color palette. With the modifications above, we can see better where these crimes happened, therefore density map it is for the first visualization.
Next up, since the previous article, we know that these cases are reported by several different polices forces that are there in the UK. Again for context as a part of building domain knowledge, there are quite a lot of different police forces throughout the UK since UK consisted of four countries. England being the largest of them geographically, and quite arguably the most famous, now England is controlled by a lot of different police forces as can be seen in full detail here, therefore it became interesting to see how the cases are spread out throughout the police forces.
For this purpose, we will be using treemaps that are provided by tableau. We can achieve this by putting the “reported by” from the tables into columns, and the count of each data into rows, now look from the show me menu that are there and select treemaps. The visualization should look like this image below:
Remember to adjust the color palette to your liking, for consistency purposes I chose this color palette. Another thing to add on top of this visualization is a tooltip, tooltip is a additional information that you can display when the user hovers on the marks of the visualization. We will be adding the count of each police force and the name of it in the tooltip, to do so simply click on the tooltip button in the marks and type in the following formula as can be seen in the image below:
Therefore we should have a hovering effect like the picture below:
That concludes the second visualization, we now know which police forces takes the most cases and that is Metropolitan Police Service, which makes sense because this force covers the most amount of neighborhood in England, and according to last year’s data has the most amount of police officers in it.
Next up, as we seen in the previous article, there are cases which outcome we marked as unresolved. Because there is no information regarding it in the dataset to further investigate these crimes. This information is quite valuable as it could be used as a parameter to measure the performance of UK police forces, and maybe UK’s law enforcement. To display it, I decided to take it simple and just display the number of cases and the ratio of the unresolved ones.
To create the number of cases or count of cases. We simply can just use the count data that was auto generated by tableau and put it into the marks section as text. Just like the image below:
Remember to format the marks to your liking, you can adjust everything including the color, the font, etc. Next up is to calculate the ratio of unresolved cases. For this we need the help of tableau’s calculated field, Since we have to manually compute the ratio. The logic is plain simple, we just need to find out the number of unresolved case divide it by the number of resolved case. To do so, create two calculated field by right-clicking the bottom section of tables on the left side of the screen. First we will create the count of resolved cases, name it Resolved_cases (or any other name that you want as long as it is descriptive). We can calculate that by using this formula as shown in the image below:
With the formula above, it only counts the data point if the “Last outcome category” column’s value does not equal to “Unresolved case”, and therefore to calculate the unresolved case, we only need to do the reverse of this formula, which can be seen in this image below:
Once both of the calculated field has been created, now we can create another calculated field for the ratio. The formula is straight forward as said before, which can be seen in this image below:
To finally create the ratio visualization, we can create it the same way as the count of cases, the difference is that now we use newly created ratio calculated field rather than the count. The visualization should now looks like this image below:
After that has been done, we will create one last visualization, and that is to map the number of cases per it’s outcome. This acts as a supporting visualization to help us know better the stage of cases that has been reported, to visualize so, I will be using a bar chart. To create the bar chart, simply drag the count of cases into the columns section, and the “Last outcome category” into the rows section, as done like the image below:
To improve readability, I added colors to differentiate each of the outcome values, and label to the count so we can see the exact count of each, and voilà, those are all of the visualizations, now is the time to put it all together and make it into a dashboard.
So as I’ve mentioned before, and probably shown to much of it without explaining it, to make these visualization even more useful and interactive, we will be adding filters to it, later on whoever views and use this dashboard can play around with the filters according to their needs. To sort of unite all of our visualizations so far, there are two filters that came to mind, that is to filter our visualizations based on the month that it happened and also the crime category. Since there are quite a lot crime category presented in the dataset.
To create those filters, we will be creating user-defined parameters or parameters for short, these parameters will take in user inputs and therefore will be used as our filters throughout all of the visualizations. To do so, right-click the bottom part of the tables section just like we created calculated field before but this time create parameter instead of calculated field.
The first one is the crime category field, since it is a list of values, and in particular of string values, we have to provide these information when creating the field, just like shown in the image below:
The same thing can be done for the month parameter. After those two has been created, now we need to create another two calculated fields, to connect those parameters with our dataset. To do so, we can repeat the step from before on how to create a calculated field, I name the fields “Crime Category” and “Month-Year” you can choose other names if you wish to do so, as long as it is descriptive. After that has been created edit the fields to add this two formula below:
Functions like LOWER(), DATENAME(), STR(), etc are just for me to format the output like how I want it, you don’t have to follow exactly as it is. The main point for the formula is that we connect the parameters’s field (colored purple in the image or the field that is on the left-hand-side of the equation) that we created earlier, to the datasets’s field that we want to make it as the filter (colored orange in the image, or the field that is on the right-hand-side of the equation). So take for example the Crime Category formula:
[Crime Type] = LOWER([Crime type])
//this means, whatever the values of the parameter's crime type
//can now be represented as the lowercased value
//of crime types from our datasetOnce that has been done, drag these two new fields to the filters section as we’ve seen many times before. The data should now be changing when the filters are changed. With all of it set we can finally now make the dashboard. As for the dashboard, you can layout it or make it however you want, as for my version you can check it out on this link.
That is it from me, I know it’s quite a long series but nevertheless, I hope you all learn something new, as I’ve certainly did while creating this project. Remember to have a good day, stay safe out there!✨