Joyplot in Excel

Posted on July 12th, 2017 in Charts and Graphs , Cool Infographics & Data Visualizations - 18 comments

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.

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.

joyplot-in-excel

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.

joyplot-data

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:
    joyplot-data-extra-columns
  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:
      rearranged-data-with-pivot-joyplot
  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:
    normalized-values
  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:
    gaps-joyplot
  7. Now, finally calculate activity + gap values by adding up respective cells in each of the 28×288 ranges. We get this:
    actual-plus-gap-values-joyplot

 

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:

sample-data-for-chart-tutorial-joyplot

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

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

joyplot-in-excel

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-sparklines

Joyplot alternative – Conditional Formatting Heatmap 

joyplot-alternative-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.

Introducing our Online Power BI Class:

Introducing Online Power BI Training from chandoo.org - check it out today

Would you like to join me on a date with Power BI? In this comprehensive online class, learn all about Power BI so you can create beautiful, insightful & interactive reports. Join me and rest of the play mates for our first ever Power BI Play Date.

Click here to know more and join us.

Written by Chandoo
Tags: , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

18 Responses to “Joyplot in Excel”

  1. Abbas says:

    Hi Chandoo,
    Good work, as always. Thanks for getting this done...this is awesome 🙂

  2. Rudra says:

    My mouth is wide open ever since I saw this...Can anyone help me?

  3. Jeff Weir says:

    Where's "Making love"? 🙂
    Awesome, dude.

  4. MW says:

    Impressive, but feels a bit like back-converting Qlik into Access.

  5. Lino Wchima says:

    Hi Chandoo,
    Beautiful, beautiful post. As always.
    Thanks a lot!

  6. Mark says:

    It's a beautiful chart and I admire your Excel Greekery for making it.

    As some values are obscured by others i think your 'simpler' alternatives are possibly more usable.

  7. GraH says:

    If you happen to find a doctor for that Ruda, please let me know 🙂

    To Jeff, it is indeed an activity that also fits the criteria of not lasting long enough to be taken into account.

    Totally agreeing with Marc. You must be Chandoo not only to wonder if you can create such a chart in Excel, but also for finding out the how to do it. (What would Jon Peltier say about it?).

    In Power BI one can create R-scripts as well. A re-challenge for Chandoo?

  8. Achyutanand says:

    awesome admirable charts i want to try this chart making too

  9. Ashutosh Pathak says:

    Awesome work

  10. Ghazanfar J. says:

    Fantastic!

  11. Jason Morin says:

    I would place this chart in "looks-cool-but-what-does-it-tell-me?" category. Can't really glean any valuable information from it, but if you focus one activity, say "doing aerobics" (which is partially blocked by the "walking" chart), then this chart could be the beginning of an in-depth analysis to study consumer behavior. If I owned a chain of workout centers and this data reflected behaviors of people that lived in my region/state/local, then it would definitely interest me.

  12. Hare ram says:

    Very nice!!!! it's awesome...

  13. Sumit Mishra says:

    Nice post and i try it l.....i think..... You Write a Big Post In This Day!... So I Say Awesome...

  14. Jun says:

    Hi chandoo!
    I studied the Joyplot Excel workbook. and had a question of Modified time. I think the formula should be:
    =IF([@time]>1260,[@time]-1260,[@time]+180)
    (Henrik’s chart is plotted from 3AM to 3AM) so time=0 ? modified time=180

  15. Kirstin Larson says:

    I've been saving the email I received on this posting until I had time to read it. Just finished, and wow! do I love it! I'm not sure I'd ever go through the process of creating it, but just walking through the process with you inspires me with creative ideas. Which is exactly why I enjoy your posts and your emails so much! Thanks!!

  16. MANOLITO S DIMAUNAHAN says:

    can you help to have employee database using excel with nice in mtg presentation

  17. Vinay says:

    Hi Chandoo,

    i would like to count number of days i spent in Hotel (i have start and End date) and spill over days should be factored into subsequent Financial year.
    I have done using if function and range, unfortunately if the start and end date changes, spillover is not factored into subsequent FY coz range is not dynamic

Leave a Reply