Visualzing SLA Performance

Question

I author a report for our senior leadership each month - it shows I.T. performance. Things such as Incident resolution, system availability and so forth. I have been poking around for ways to visualize some of the data I have and found a beautiful representation, which is as follows:

I love this layout. All of my reporting is in RMarkdown with some mixed LaTex. I have been contemplating how to replicate something like this in Rmarkdown or maybe even just use raw LaTex embedded in my markdown files... I already know I can probably use the sparklines package to get the sparklines, the values and titles can be fed from the data. My only trip up, is the whole thing in its entirety.

Can I do something like this in ggplot? Or maybe using lattice... I'm lost on how to put all of this together.

Some basic information - my data sets are an R Dataframe. Each line contains a different system or metric. There would be a series of columns containing the performance (both counts and percentages). I envision some sort of loop that would build each box and then put it all in a grid somehow.

I could provide a sample dataset if needed, but they are very basic. The fields/columns would be something like: name, target, Jan2018, Feb2018, etc.. If I need both counts and percentages for some metrics, I might have columns for each month that have both counts and percentages.

Any ideas on how to reproduce this?

Sample Data:

Here is a sample data set. I'd like the sparkline to be the percentage, but I also have the hours per month. The number displayed can be the YTD hours and YTD percentage. Sorry for the late data set - I had to sanitize this to take out confidential information. I have added both CSV and RData formats. Thanks again!

Data CSV File

Data RData File

at least give us some sample dataset, I believe "they are very basic" applies to yourself but not to others. — TC Zhang, Jul 12 '18 at 03:09
This can certainly be done with R, but exactly how it is best to do it depends on a bit more about your dissemination. How frequently is it updated (monthly, or every second); how does the data get to R; how does the dashboard get to the users. Is any interaction required, or is a static image all that is needed? Is the layout known in advance (eg always the same 12 tiles needed) or is it dynamic? If you only need the static image, I would do it all in R (no markdown or LaTeX, just to avoid complications). — Peter Ellis, Jul 12 '18 at 03:09
See [How to make a reproducible example in R](https://stackoverflow.com/q/5963269/903061) if you need pointers on sharing data reproducibly. We don't need all your data, but a big enough sample to try out approaches and demonstrate possible solutions. You'll get more help if the data you share is copy/pasteable - using `dput()` is good for this. — Gregor Thomas, Jul 12 '18 at 03:31
I will get a sample data set and upload it before the end of the day. Thank you all so far with your help!! — azdatasci, Jul 12 '18 at 17:50
All - added links to the data if this helps. Each system should have it's own box and the numbers shown on the boxes should be the current YTD % and YTD count. The sparklines should be the month to month %. Thanks! — azdatasci, Jul 16 '18 at 18:34
Just a bump on this - I am almost done modifying the code below to what I need for my data set. Once I am done, I will post that. Just wanted to provide a follow up for this. @mikey-harper - the code below was exactly what I needed to get me started. — azdatasci, Jul 20 '18 at 15:31
You'd be better off posting your update as a separate answer to your problem, and would be good to include an image of the output :) — Michael Harper, Jul 23 '18 at 22:36

score 2 · Accepted Answer · answered Jul 13 '18 at 18:28

This question has quite a lot of parts to it, and I agree with the comments that the exact solution will vary depending on a lot of the details. But assuming that you are looking for a solution which creates some form of static dashboard you could build something similar to this using a heavily edited ggplot.

I have written a function metricplot which makes it easy to create lots of these smaller charts. It has the following variables:

df: a dataframe containing the data
x & y: the columns to use for the x and y axes
title: the title of the plot
colour: the colour of the widget

Here is the function:

#' Make a small metric plot
#' 
#' 
metricplot <- function(df, x, y, title, colour){

  # Find the change in values
  start <- df[[y]][1]
  end <- df[[y]][length(df)]
  change <- scales::percent((end - start)/start)


  plot <- 
    ggplot(df) +
    annotate("rect", xmin = -Inf, xmax = Inf, ymax = max(df[[y]] - 1), 
             ymin = min(df[[y]]), fill = "white", alpha = 0.5) +
    geom_line(aes_string(x, y), colour = "white", size = 2) +
    labs(title = title,
         subtitle = paste0(end, " / ", change)) +
    theme(axis.line=element_blank(),
          axis.text.x=element_blank(),
          axis.text.y=element_blank(),
          axis.ticks=element_blank(),
          axis.title.x=element_blank(),
          axis.title.y=element_blank(),legend.position="none",
          panel.background=element_blank(),
          panel.border=element_blank(),
          panel.grid.major=element_blank(),
          panel.grid.minor=element_blank(),
          plot.background = element_rect(fill = colour),
          plot.title = element_text(size = 20, colour = "white", face = "plain"),
          plot.subtitle = element_text(size = 40, colour = "white", face = "bold")) 

  return(plot)

}

Using this function with an example dataset:

 set.seed(123)
df2 <- data.frame(x = 1:20,
                  y = c(9, rep(10, 17), 12, 14),
                  z = c(14, rep(10, 17), 12, 11))

library(ggplot2)
library(ggthemes)

grid.arrange(metricplot(df2, "x", "y", "Metric 1", "#fc8d59"),
             metricplot(df2, "x", "y", "Metric 1", "#91cf60"),
             metricplot(df2, "x", "z", "Something Else", "#999999"),
             metricplot(df2, "x", "z", "One More", "#fc8d59"), ncol=4)

Clearly, this has made a few assumptions about the format of the data but hopefully it can set you off in the right direction :)

Something else i see that I love is the fact your calculating the percentages on the fly. This makes generating the data set much easier, so I don't have to predetermine the % before I make the chart. Good stuff. I could adjust this code to accept a 3rd argument to the function to include the %, but why since you've done it inside the function already. I love this.... You can see what I mean with my posted data sets above. I am going to attempt to modify this code to my data I will re-post what I end up with just in case anyone has an interest. Thanks again! — azdatasci, Jul 16 '18 at 21:10
If you have any suggestion on how to get the 2nd number after the "/" in a lower font for the subtitle (like the the original example), let me know. Otherwise, for now, this seems to be working great. — azdatasci, Jul 23 '18 at 20:45
I'm not entirely sure but you should avoid wrapping up multiple questions into a single post. "multiple font sizes in ggplot text" would be a valid separate question — Michael Harper, Jul 23 '18 at 22:34
I will post a different question for multiple sizes on subtitles... Thanks! — azdatasci, Jul 25 '18 at 21:52

score 1 · Answer 2 · answered Jul 25 '18 at 21:51

I have completed the first part of the code rewrite from the example code below in the answer from @mikey-harper. This modifies his code to work with my data set format. There still needs to be a bit of tweaking to get the format just like I want it, but for now, its a good start. Just wanted to post my progress.

# Needed Libraries

library(Hmisc)
library(zoo)
library(lubridate)
library(ggplot2)
library(ggthemes)
library(grid)
library(gridExtra)

# Plot Function

metricplot <- function(data = criticalSystemAvailabilityFullDetail, row = 1) {

  # Since data is organized by row, I need to pull only the columns I need 
  # for the particular row (system) specificied. Then turn it into columns
  # since ggplot works best this way.
  ytdMonths <- as.data.frame(names(data)[4:((month(Sys.Date())-1)+3)])
  ytdValue <- t(as.data.frame(data[row,((month(Sys.Date()))+3):(ncol(data)-2)][1,]))
  ytdData <- cbind(ytdMonths, ytdValue)
  names(ytdData)[1] <- "Month"
  names(ytdData)[2] <- "Value"

  # Since I need red, yellow and green for my thresholds, I already have my 
  # target. My rules for this are basically, green until it exceeds 50%
  # of the target, then it turns yellow. Once it exceeds the Target, it turns
  # red. This function is called when the plot is made to determine the background
  # color.
  colour <- function (system = data[row,]) {
    if(data[row,ncol(data)] < as.numeric(strsplit(data[row,2], "%")[[1]][1]) ) {
      return("#fc5e58")
    } else if((data[row,ncol(data)] > as.numeric(strsplit(data[row,2], "%")[[1]][1])) == TRUE & (data[row,ncol(data)] < ((as.numeric(strsplit(data[row,2], "%")[[1]][1]) + 100.00) / 2)) == TRUE) {
      return("#ebc944")
    } else {
      return("#8BC34A")
    }
  }

  # Now for the plot. I have made some slight modifications to this. For example, in the white area that 
  # represents the high and low - I have used 100% for the max and the target for the low. I do this dynamically
  # by using the target from the row (system) I am currently plotting. I adjusted the line size down to 1, since 
  # the preivous value made the line a little too big. 
  plot <- 
    ggplot(ytdData) +
    annotate("rect", xmin = -Inf, xmax = Inf, ymax = 100.000, ymin = as.numeric(strsplit(data[row,2], "%")[[1]][1]), fill = "white", alpha = 0.6) + # Create the plot
    geom_line(aes(x = as.yearmon(Month), y = Value), colour = "white", size = 1) +
    labs(title = data[row,1], subtitle = paste0(data[row,ncol(data)], "% / ", data[row,(ncol(data)-1)], " hours")) + # Add title and subtitle
    theme(axis.line=element_blank(), # Remove X-axis title
          axis.text.x=element_blank(), # Remove X-Xais Text
          axis.text.y=element_blank(), # Remove Y-Axis Text - Comment this whole line out if you want a scale on the y-axis.
          axis.ticks=element_blank(), # Remove X-Axis
          axis.title.x=element_blank(), # Remove X-Axis Titlke
          axis.title.y=element_blank(),legend.position="none", # Remove legend and Y-axis title
          panel.background=element_blank(), # Remove bland gray background
          panel.border=element_blank(), # Remove border
          panel.grid.major=element_blank(), # Remove Grid
          panel.grid.minor=element_blank(), # Remove Grid
          plot.background = element_rect(fill = colour()), # Red, Green, Yellow
          plot.title = element_text(size = 10, colour = "white", face = "plain"), # Main Title
          plot.subtitle = element_text(size = 15, colour = "white", face = "bold"))

  return(plot) # Return the plot.
}



# Now we build the the grid by calling each row. Depending on the size of the canvas, 
# you might want to break up how many rows on the grid you do. In my case, this 
# is going on an A4 size peice of paper, so I will probably limit it to about 5-6 rows 
# in order to provide a readable page. Squeezing 5 columns in could get you more
# on a page, too. 

grid.arrange(metricplot2(data = criticalSystemAvailabilityFullDetail, row=1),
             metricplot2(data = criticalSystemAvailabilityFullDetail, row=2),
             metricplot2(data = criticalSystemAvailabilityFullDetail, row=3),
             metricplot2(data = criticalSystemAvailabilityFullDetail, row=4),
             metricplot2(data = criticalSystemAvailabilityFullDetail, row=5),
             metricplot2(data = criticalSystemAvailabilityFullDetail, row=5),
             metricplot2(data = criticalSystemAvailabilityFullDetail, row=7),
             metricplot2(data = criticalSystemAvailabilityFullDetail, row=8),
             metricplot2(data = criticalSystemAvailabilityFullDetail, row=9),
             metricplot2(data = criticalSystemAvailabilityFullDetail, row=10),
             metricplot2(data = criticalSystemAvailabilityFullDetail, row=11),
             metricplot2(data = criticalSystemAvailabilityFullDetail, row=12),
             metricplot2(data = criticalSystemAvailabilityFullDetail, row=13),
             metricplot2(data = criticalSystemAvailabilityFullDetail, row=14),
             metricplot2(data = criticalSystemAvailabilityFullDetail, row=15),
             metricplot2(data = criticalSystemAvailabilityFullDetail, row=16),
             metricplot2(data = criticalSystemAvailabilityFullDetail, row=17),
             metricplot2(data = criticalSystemAvailabilityFullDetail, row=18),
             metricplot2(data = criticalSystemAvailabilityFullDetail, row=19),
             metricplot2(data = criticalSystemAvailabilityFullDetail, row=20),
             metricplot2(data = criticalSystemAvailabilityFullDetail, row=21),
             metricplot2(data = criticalSystemAvailabilityFullDetail, row=22),
             metricplot2(data = criticalSystemAvailabilityFullDetail, row=23),
             metricplot2(data = criticalSystemAvailabilityFullDetail, row=24), ncol=4)

The only thing I am missing is how to make the hours (the 2nd portion of the subtitle) smaller. I don't know if I can use a 3rd subtitle or not, but need to try that out. Otherwise, I need to figure out how to use different sizes within a subtitle. Otherwise, this seems to work perfectly.

Visualzing SLA Performance

2 Answers2

Linked