3
$\begingroup$

My core problem is trying to visualize the workload of a customer support team throughout the week. Specifically, I want to see what the load looks like hour by hour on each day for each agent throughout the week.

The best source of data that I can think of is our ticketing system, which can tell me when an agent replies to a case. Thus, with each reply, I get a point in time that work was done (I understand this doesn't accurately reflect the time it took to work on the case). My source data might look like this:

Agent   Day     Hour
Ben     Monday  0.2
Ben     Monday  0.4
Ben     Monday  1
Ben     Monday  1.2
Ben     Monday  2
Ashley  Monday  1
Ashley  Monday  3
Ashley  Monday  3.7
Ashley  Monday  6
Ben     Tuesday 0.5
Ben     Tuesday 1.4
etc...

I was trying to mock this up in Excel and couldn't figure out how to do it (or even if it is possible). This is a crude drawing I made to demonstrate:

crude attempt at a chart

My first problem is that I'm not even sure what kind of chart this is, and along with that, is this what I ought to use to visually represent this data?

Looking around on the internet, I came across this Super User question, which suggests this wouldn't be possible in Excel, but may be possible in PyPlot (or is it matplotlib?). I'm comfortable in Python and would like to try it out, but I'm stuck in that I don't know the proper name of the visualization I'm trying to make.

$\endgroup$
5
$\begingroup$

The plot you are describing is just a scatter plot with day-person pairs on x-axis. Below I provide an example code in R with ggplot2 library and the plot.

library(ggplot2)

set.seed(123)

data <- data.frame(
  person = sample(c("Alice", "Bob"), 100, replace = TRUE),
  day = sample(c("Monday", "Thuesday"), 100, replace = TRUE),
  hour = runif(100, 0, 24)
)

ggplot(data, aes(x = paste(day, person), y = hour, color = person)) +
  geom_point()

enter image description here

$\endgroup$
  • $\begingroup$ This is excellent! I don't recognize the code you are using. Mind pointing me in a direction where I can learn more? $\endgroup$ – Ben McCormack Oct 22 '15 at 18:47
  • $\begingroup$ (specifically, I don't recognize the language) $\endgroup$ – Ben McCormack Oct 22 '15 at 18:47
  • $\begingroup$ Quite correct; but we can add that names like dot plot, strip plot, and at least twenty others are often used for similar if not identical plots. The design above starts to break down with multiple tied values. Jittering or stacking values and/or binning them mildly are standard devices. $\endgroup$ – Nick Cox Oct 24 '15 at 12:02
4
$\begingroup$

Many thanks to Tim's answer, as this pointed me in the right direction to look for a scatter plot.

I started going down the route of learning R or using Python's matplotlib, but I think I'm going to settle on just using Highcharts with plain old HTML and javascript. I was hoping my end result would be on an internal website anyway, so this is probably closer to what I need.

Here's a screenshot of a demo I came up by modifying Highchart's Scatter plot demo:

Scatter Plot Demo

The code for that is:

<script src="//code.jquery.com/jquery-1.11.3.min.js"></script>
<script src="http://code.highcharts.com/highcharts.js"></script>
<script src="http://code.highcharts.com/modules/exporting.js"></script>

<div id="container" style="min-width: 310px; height: 400px; max-width: 800px; margin: 0 auto"></div>

<script type="text/javascript">
$(function () {
    $('#container').highcharts({
        chart: {
            type: 'scatter',
            zoomType: 'xy'
        },
        title: {
            text: 'Email Queue Throughput by Agent Per Weekday'
        },
        subtitle: {
            text: 'Help Scout Cases 9/22 - 9/26'
        },
        xAxis: {
            title: {
                enabled: true,
                text: 'Day of Week'
            },
            startOnTick: true,
            endOnTick: false,
            showLastLabel: true,
            allowDecimals: false,
            categories: ['Sunday','Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday']
        },
        yAxis: {
            title: {
                text: 'Hour of day'
            }
        },
        legend: {
            layout: 'vertical',
            align: 'left',
            verticalAlign: 'top',
            floating: false,
            backgroundColor: (Highcharts.theme && Highcharts.theme.legendBackgroundColor) || '#FFFFFF',
            borderWidth: 1
        },
        plotOptions: {
            scatter: {
                marker: {
                    radius: 5,
                    states: {
                        hover: {
                            enabled: true,
                            lineColor: 'rgb(100,100,100)'
                        }
                    },
                    symbol: "circle"
                },
                states: {
                    hover: {
                        marker: {
                            enabled: false
                        }
                    }
                },
                tooltip: {
                    headerFormat: '<b>{series.name}</b><br>',
                    pointFormat: 'hour: {point.y}'
                }
            }
        },
        series: [{
            name: 'Ben',
            color: 'rgba(223, 83, 83, .5)',
            data: [[1,1], [1,2], [1,3],
                   [2,1.5], [2,2.7], [2,3.8],
                   [3,4], [3,3.5], [3,3.6],
                   [4,2], [4,5], [4,7],
                   [5,1], [5,2], [5,5]]

        }, {
            name: 'Ashley',
            color: 'rgba(119, 152, 191, .5)',
            data: [[1.1,1],   [1.1,2],   [1.1,3],
                   [2.1,1.5], [2.1,2.7], [2.1,3.8],
                   [3.1,4],   [3.1,3.5], [3.1,3.6],
                   [4.1,2],   [4.1,5],   [4.1,7],
                   [5.1,1],   [5.1,2],   [5.1,5]]
        }]
    });
});
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.