Building a Bar Chart in Tableau: Number of Grocery Trips per Hour of Day
From January 8th 2020 to February 21st 2021, I tried to register everyone of my trips to the grocery store, in an attempt to understand my own behaviour as a customer. I hope that this leads me to unearth habits that make me prone to spending more money or buying “bad” foods. By knowing them, I can avoid them and hopefully live a healthier lifestyle. I discuss more about it here.
Today, we will be working with Tableau, a popular data visualization tool. The goal? To build a bar chart to represent the number of trips made per hour of day, as well as the average amount spent per trip for each hour of day. Perhaps I unconsciously change my purchasing habits based on time.
Let’s get started.
Step 1: Loading the Data
The data is in an excel format. We can simply drag the file to Tableau and it will load the data correctly.
Tableau automatically detects the columns and data types, so nothing else should be needed to do here. Let’s rename “Sheet 1” to “trips_per_hour” and let’s click into the sheet to start building the visualization.
Step 2: Building the “Hour” Dimension
We are looking to build a bar chart where “Hour of Day” will be a core dimension. However, this specific dataset doesn’t contain an “Hour of Day” dimension, only a “Date” and a “Time” one. So, let’s extract it!
Fortunately, that is simple in Tableau.
Click on the arrow next to the Time dimension, then hover over “Create”, click “Custom Date” (see screenshot below)
Let’s name the field “Hour of Day”, and for Detail, let’s use “Hours”. Finally, select “Date Part”. Our new dimension is ready to use!
In Tableau, drag the newly created dimension “Hour of Day” to the “Columns” field. Bring the “ID” dimension to the “Rows” field. Here is the resulting output:
This doesn’t make a ton of sense, because we need to count the number of distinct ID’s. To do that, click on the ID dimension and hover over “Measure”, click on “Count (Distinct).
Now, we can see what we really want, that is, the number of trips per hour of day! Let’s just make sure we are displaying a bar chart. To do so, under “Marks”, select the “Bar” visualization.
Now, we have a bar graph, and it represent what we wanted! But it’s a little ugly, so let’s beautify it.
Step 4: Beautification of the Bar Chart
First step, in the Marks menu, adjust the size to one you like. I personally like the bars to be close together, but not exactly touching.
Second, again under the Marks menu, I selected the “Label” and checked “show mark labels” to see the count of trips above each bar directly.
Third, on the vertical axis, I right-clicked and clicked “Edit Axis”. Under that menu, I selected the “Tick Marks” sub-menu and selected “None” to Major Tick Marks. Thus, eliminating the redundant numbers on the vertical axis (they are already present at the top of the bars).
While in that menu, I went back to the “General” sub-menu to modify the title of my axis to “Number of Trips”.
Fourth, under the format menu in Tableau, I went through the “Sheet”, “Rows” and “Columns” sub-menus to set the grid lines to “None”.
And here we go! A beautiful bar cart displaying the number of trips to the grocery store by hour of day. I really like going at the grocery store after 6pm!
Extra: Average Spend per Hour of Day
We now have a beautiful bar chart, but that’s not very actionable. Sure, I go at the grocery store in the evening, but is there an hour where I spend more money than another?
First, let’s filter to food items only. Let’s drag the “Food yesno” field to the filters box, and select only “y”.
Second, create a calculated field from the “ID” dimension.
In the box that appear, enter the following code:
SUM([Price (total)])/COUNTD([ID]) That is, the sum of the price divided by the distinct count of ID’s.
I called the new measure “Avg. Spent” and dragged it beside the count of distinct IDs.
It is pretty interesting to see both charts one above the other, as the number of trips gives me an idea of the volume of data available, while the average spend per hour of day gives me an idea of y habits.
The distribution obviously lacks in volume of data, but the visualization suggests that I spend more the later in the day it is, with the notable exceptions of 6pm and 9pm.
But, it seems like I could save $4–$5 by shopping before 5pm.
Next time, I will explore how to perform “top 10's” in Tableau, to try and discover my favorite products/items or stores!
