This week, we'll be shifting our focus from football to cricket, much like I've been switching between channels to follow both the ongoing T20 Cricket World Cup and the Euros. While reading through some comments on BBC Sport, I came across a popular visualisation known as the worm chart. It's not a new or innovative chart; it has been used in cricket for ages. Then I thought to myself, can I create this in Tableau? Well, the answer was, let's give it a go.
Did I manage to build one in Tableau? Well, spoiler alert: it turns out I did. Therefore, in this blog post, I'll take you through the process so you can build one too. The dashboard image below shows how my version turned out.
Data Collection
Firstly, let's talk about how our prepped data looks. We have the following required fields for our visualization:
Ball_Id: Unique identifiers for each ball bowled, ranging from 1 (the first ball of the game) to 120 (the final ball in a T20 match).
Over: An identifier for each over, with 0 being the first over and 19 being the final over of a T20 game.
Ball: An identifier for each ball within an over, from 1 to 6.
Team: The names of the teams playing the match.
Runs: Runs scored on that particular ball, including extras.
Wickets: Wickets taken on that particular ball, indicated by 1 (wicket taken) or 0 (no wicket).
For demonstration purposes, I will be using data extracted from a game played last year in the Vitality Blast between Lancashire Lightning and Nottinghamshire Outlaws at Emirates Old Trafford.
Vizarding with Tableau
Once we have our data prepped and ready to be visualised, we load it into Tableau and let the 'Vizardry' begin!
The idea behind this graph is to show a ball-by-ball comparison of where the two teams stood at any given point in the match in terms of runs and wickets. The runs are plotted on the x-axis, cumulatively adding to the team totals with each ball. Wickets are represented as filled circular dots in the respective team colors, labeled with a 'W' to indicate a fall of a wicket on that ball.
We will start by building the two lines for the runs scored by the two teams. First, place 'Runs' on the Rows and 'Ball Id' on the Columns. Then, add 'Team' to the Color mark to distinguish between the two lines. Next, left-click on the SUM(Runs) green pill on the Rows tab, select 'Quick Table Calculation' from the dropdown menu, and then choose 'Running Total' from the secondary dropdown.
Now that we have the runs sorted, let's focus on placing the wicket circles. It's crucial to note that we need the wicket circles to align with the runs scored on the specific ball when the wicket was taken. Understanding this alignment is essential for how we'll approach this task.
Before we proceed to build the visualisation, we will create a calculation in Tableau one and only that we would need in this case. A dimension to identify fall of wicket.
// Wicket_YN
if [Wickets]=1 THEN
"W"
ENDBuilding this would involve the following steps in order:
Duplicate the Running Total of Runs pills from above (following same steps) and place the exact same Running Total of Runs next to each other.
Create a Dual Axis and synchronise the two axes.
Change the Mark Type for the new duplicated Mark to a Shape. Change the Shape to a Filled Circle.
Drag the new calculated field Wicket_YN to Shape on the Marks card.
Click on the shape pill for the Wicket_YN and convert it to a measure using Minimum.
Hide the MIN(Wicket_YN)= NULL Values from Shape Legend.
Adjust the size for the shapes from the marks card accordingly and bring the MIN(Wicket_YN) from the Label shelf.
Align the 'W' labels to the centre of the circle.
Now, the video above demonstrates the step-by-step process to build the final worm chart, showcasing both the runs scored and the fall of wickets for both teams and providing a comprehensive match summary.
Next, the task is to format the chart according to desired specifications. This involves hiding the headers on the secondary axis, incorporating shaded reference bands to denote phases such as the Batting Powerplay (1-30 balls), Middle Overs (31-90 balls), and Death Overs (91-120 balls). Additionally, enhancing tooltips to provide insightful details is beneficial.
There you have it! I hope this has been helpful. Please feel free to comment below if there are any other cricket charts you'd like me to demonstrate using Tableau.
Signing Off,
Yash
This is a brilliant explanation to understand how to do a worm graph in tableau. Getting the wickets dots on the line graph, is really tricky. Well done Yash for working it out! Cheers