Visualzing SLA Performance

问题:

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

回答1:

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 :)

回答2:

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.