Joyplot in Excel

Over on Twitter, I came across this beautiful chart, aptly titled – Joyplot. It is the kind of chart that makes you all curious and awed. So I did what any Excel nerd would do. Recreated it in Excel of course. This post takes you thru the process.

Peak time for sports and leisure #dataviz. About time for a joyplot; might do a write-up on them. #rstats code at https://t.co/Q2AgW068Wa pic.twitter.com/SVT6pkB2hB

— Henrik Lindberg (@hnrklndbrg) July 8, 2017

First let me share the final outcome.

Joyplot in Excel – Peak time of the day for sports and leisure

Here is the final overlapped area chart with a bit of formatting thrown in. It is a pretty close imitation of Henrik’s original chart. Click on it to enlarge.

Creating Joyplot in Excel – Tutorial

As you can guess, the chart is a just an overlapped area chart (ie each area sits behind another, unlike stacked area chart where they are umm, well, stacked!)

Let’s start with a look at data. Henrik’s original data has 10,656 rows, each row containing activity name, time and p value – how much survey respondents enjoyed [@activity] at that time.

Here is a snapshot of first few rows.

Scrubbing and re-arranging the data

As you can see, while this format is excellent for storing, it is very tedious if we want to make one chart with all series. So let’s scrub.

1. We need to figure out if an activity should be included or not. I am using the same criteria as Henrik’s. Exclude activities with p value less than 0.003 or activity title “Playing sports n.e.c. *”  (not elsewhere classified)
   1. To do this, we first pivot the data on activity and max(p). Then filter this pivot two ways – max(p) >=0.003 and label not equal  Playing sports n.e.c. *
      Tip: You may need to enable multiple filters per field in the field settings of row labels.
   2. We will end up with 28 activities.
   3. Then add a helper column to original table that looks up the pivot and tells if an activity should be included or not
2. Add two more columns to original table to tell peak time and modified time. This will help us in rearranging and sorting the data. Modified time just moves time by 3 hours (Henrik’s chart is plotted from 3AM to 3AM). At this stage our data looks like this:
   
3. Now, pivot the data once again. This time,
   1. exclude activities by using report filter on include? column.
   2. Set up peak and activity in row labels area, modified time in column labels area and p in values area.
   3. Arrange the report in tabular format, turn off sub-totals.
   4. We get this:
      
4. Calculate normalized values by dividing each p value with maximum p value for that activity. We can use another range of 28×288 cells to do this. We get this:
   
5. The next 2 steps may seem confusing. It will become clear once you look at the charts.
6. Define an offset value. Start with 0.5. You can change this later. In a separate 28×288 cell range, calculate gaps by multiplying offset with position of an activity. Something like this:
   
7. Now, finally calculate activity + gap values by adding up respective cells in each of the 28×288 ranges. We get this:
   

At this stage, our data is a shape ready for visualizing.

Creating and formatting overlapped area chart

The chart creation process has 5 steps.

1. Select the 28×288 range of cells created in step 7 and insert an overlapped area chart.
2. Now, copy the gaps range (created in step 6 above) and paste them on to area chart as new series (just ctrl+c your data and select the chart, press ctrl+v)
3. Adjust the order of series so that each activity is sandwiched by appropriately named gap series
   1. Tip: adjusting 56 series is painful with the chart select data > move series up/down buttons. Instead, just select the series, look at formula bar. The SERIES formula has last parameter as order. Change this number. It is easy to figure out the number once you try a few.
4. Change all gap series fill color to white. This instantly creates the floating area chart effect.
5. Change the colors of activity series. Apply white / off-white border to these series. Your joyplot is ready.

Quick overview of the chart creation process:

Let’s examine the result of each those 5 steps with a smaller dataset so you can see how everything fits together. Here is the data for this example:

1. Create an overlapped area chart with activity+gaps data. We get this:
   
2. Add gaps as new series to chart. You get this:
   
3. Move the gap series so that they sandwich activity series. Use Chart Data > Move series up/down buttons or SERIES formula
   
4. Apply white color fill formatting for gap series. This creates floating area chart effect as below:
   
5. Finally, format the chart by apply some colors and border formatting etc.
   

So there you go. The final outcome does look joyful.

Alternatives to Joyplot

While joyplot is awesome, it is not easy to make. Fortunately, there are a few simpler alternatives that we can whip up in Excel as soon as you have either the pivot or normalized values.  Below I have shown two such examples. Read about sparklines or conditional formatting heatmaps for more.

Joyplot alternative – using sparklines:

Tip: to get axis on your sparkline, just type the times separated by a single space. Then go to format cell (ctrl+1) and set horizontal alignment to distributed. Viola, Excel will fill the cell by adjusting spaces.

Joyplot alternative – Conditional Formatting Heatmap 

Download Joyplot Workbook

Click here to download Joyplot Excel workbook. Examine the data scrubbing formulas, pivot and chart settings to learn how this is created.

If you are familiar with R, then go thru Henrik’s R code. It is much shorter than the Excel gymnastics we did with circular pivot table referencing. That said, some of the data re-arrangement could be done with same ease in Power Query too.

Your thoughts on Joyplot?

The only step we missed in Excel implementation is moving average smoothing of the area charts. It can be easily added as a step between 3 and 4 in data stage.

How do you like Joyplot? Would you create something like this for your business / personal data? Share your stories and thoughts in the comments section.

More joy for you…

If you love this, you are going to enjoy these charts too.