Making PowerPoint Slides with R

This guide is directed at what I referred to as the “third audience” in an article I published in 2016 (https://www.linkedin.com/pulse/people-analytics-show-me-dont-tell-ben-teusch) - those who have a data background, but little HR expertise, and those in HR with a willingness to work with data, but not much of a quant background. There are already plenty of tutorials to show you how to run a regression or other kinds of predictive models, and I’m not sure that’s what this audience will find most helpful. Please give me feedback on that.

What I’m going to do is focus on showing you how to do things that don’t require too much of a data background, but would be really helpful for the HR folks I know. Those with a stronger data background can use this as a starting point to add in more complex analyses, if they want.

Making HR Reports

No matter how mature people analytics is at your organization, at some point you’re going to need to make some reports. This is especially true if you’re an HRBP with limited technical support – you might even be making these reports yourself! These reports, often done monthly, can take hours of VLOOKUPs and copy-pasting in Excel, then another while to put the right numbers into a PowerPoint template. Today I’m going to show you how to automate those reports using R, so you can save a bunch of time down the road. Andrew Marritt recently wrote about his process for automating quality reports (https://www.linkedin.com/pulse/manager-level-reporting-how-we-automate-process-andrew-marritt), and his advice is great. I’ll be showing you less strategy and more tactics about how to actually make this report (http://www.slideshare.net/BenTeusch/automated-attrition-report-using-r) in R.

Getting Started with R

If you’ve never used R before, check out Richard Rosenow’s Intro to R post (https://www.linkedin.com/pulse/hr-analytics-starter-kit-part-2-intro-r-richard-rosenow-pmp), and specifically you can download R (https://mirror.las.iastate.edu/CRAN/) and RStudio (https://www.rstudio.com/products/rstudio/download3/). There are lots of helpful tips online about getting started with R, and I’m writing this post with the assumption that you have RStudio installed and you’re able to run code that I provide.

Code for this article

The R notebook used to produce this article is found on my GitHub repository at https://github.com/teuschb/hr_data/blob/master/analyses/automating_report_slides.Rmd. If you just want the script with code only, that’s found at https://github.com/teuschb/hr_data/blob/master/analyses/automating_report_slides.R. There is also a simplified version of this code at https://github.com/teuschb/hr_data/blob/master/analyses/automating_report_slides_simple.Rmd which produces a PowerPoint report using much simpler code, but doesn’t look quite as nice.

Throughout this example, I’ve used mostly base R functions to manipulate data. However, I highly recommend dplyr, and the tidyverse in general. Check that out here: http://tidyverse.org/

Dataset

The example dataset is based on the HR Employee Attrition and Performance dataset from IBM Watson Analytics (https://www.ibm.com/communities/analytics/watson-analytics-blog/hr-employee-attrition/). I’ve modified it slightly and saved it to my GitHub repository (https://raw.githubusercontent.com/teuschb/hr_data/master/datasets/modified_watson_dataset.csv), which allows me to read in the .csv file directly from the Internet, without needing to download a file.

In practice, your dataset will be your standard HRIS report of headcount, attrition, performance, or whatever data you want to use for a report. That way, each month, quarter, or year, you’ll be able to run this same line of code, replacing the previous file with the name of the most recent dataset that you want to work with. If all the needed data fields have the same names each time, the rest of the code will run without you having to make changes each month. You can even use R to get several standard reports and merge them together quickly, again in an automated way, but here I use just one data file.

This code loads the data from GitHub into R.

mydata <- read.csv("https://raw.githubusercontent.com/teuschb/hr_data/master/datasets/modified_watson_dataset.csv", stringsAsFactors = FALSE)

Set Report Parameters

The one thing I do manually update each month is the date of the report. By setting it once at the top, I can reference it throughout the script without worrying about all the places I may have used it.

month = "May"
date = "May 2017"

The ReporteRs package

R isn’t able to write .pptx files by default. What makes this possible is the ReporteRs package. To learn a lot more about what the package can do, check out http://davidgohel.github.io/ReporteRs/. It allows you to use R to run your analysis, print plots, and export everything to a PowerPoint document in a repeatable, automated way.

ReporteRs requires rJava and for Java to be installed on your computer. This code will make sure it’s installed, and will show you the version of Java (it should be at least 1.6).

require(rJava)
system("java -version")

java version "1.6.0_65"
Java(TM) SE Runtime Environment (build 1.6.0_65-b14-468-11M4833)
Java HotSpot(TM) 64-Bit Server VM (build 20.65-b04-468, mixed mode)

The first time you use ReporteRs, you’ll need to install the package from CRAN, the R package repository. This code checks whether it’s already installed, and installs it if it isn’t.

if (!require('ReporteRs')) {
  install.packages("ReporteRs")
}

Libraries

Before we can get started, we have to load in a few other packages I like to use. You’ll see that ReporteRs is on the list - running install.packages('ReporteRs') gets the package onto our computer, and library(ReporteRs) tells R that we want to use that package right now.

If you see a package name you don’t recognize, make sure you run install.packages for it. For example, install.packages('ggplot2').

library(ReporteRs)
library(ggplot2)     # for plotting graphs
library(scales)      # for formatting numbers
library(magrittr)    # for the %>% operator

Analysis

What analysis needs to be done for the report? HR is often guilty of producing reports and dashboards on HR processes that don’t directly help business leaders make better decisions. Good reports should always be produced for a specific purpose. Since I don’t have a specific business problem I’m trying to solve in this example, and because HR is used to reporting on attrition, I’ll prepare some data for a basic attrition report with a few bar charts to visualize the data.

Just know that R is able to produce various types of charts and produce whatever analysis is necessary to help your business. If you have any specific examples in mind you’d like to see, let me know.

Attrition Overview Table

For this example, the first slide will be a table with an overview of attrition.

This code takes the data and uses aggregate to extract the total number of employees, the number of employees who left, and the attrition rate for this month (calculated as # of employees gone/# of employees at the start of the month), all by department.

overall_attrition <- as.data.frame(
  as.list(aggregate(Attrition ~ Department,
                    data = mydata,
                    FUN = function(x) c(
                      n = length(x),
                      s = sum(x, na.rm = T),
                      mn = mean(x, na.rm = T))
                    )
          ), stringsAsFactors = F)

Next, I add a row of totals using rbind.

overall_attrition <- rbind(overall_attrition, c(
                             0, # to be replaced with the word "Total"
                             sum(overall_attrition$Attrition.n),
                             sum(overall_attrition$Attrition.s),
                             mean(overall_attrition$Attrition.mn))
                           )
overall_attrition[nrow(overall_attrition),1] <- 'Total'

Before I put the data into a table, I want to add % symbols and to make the column names more presentable. I also change the font size before putting it into a special kind of ReporteRs table called a vanilla.table.

Once I make the table, I also format it. I use setZebraStyle to add some shading, and setFlexTableWidths to adjust the column widths to fit the text. The final block of code justifies the text to the left or center, and makes some text bold.

# format as percentages, rename columns
overall_attrition$Attrition.mn <- percent(as.numeric(overall_attrition$Attrition.mn))
names(overall_attrition) <- c('Department', 'Headcount', 'Attrition', 'Attrition %')
# Put data into a table
options("ReporteRs-fontsize"=16) #font size for tables
overall_attrition_table <- vanilla.table(overall_attrition) %>%
  setZebraStyle(odd = '#eeeeee', even = 'white' ) %>%
  setFlexTableWidths(widths = c(4.5, 2.5, 2, 2.5)) 
overall_attrition_table[,1] = parLeft()
overall_attrition_table[,2:4] = parCenter()
overall_attrition_table[,,to='header'] = parCenter()
overall_attrition_table[4,] = textProperties(font.weight = 'bold' )
overall_attrition_table

Department	Headcount	Attrition	Attrition %
Human Resources	63	12	19.05%
Research & Development	961	133	13.84%
Sales	446	92	20.63%
Total	1470	237	17.84%

Following that overview, I report attrition by job role, performance, and hire source (or recruiting channel). For each one I’m going to make a plot that shows the turnover, and then create a table that resembles the overall attrition table.

Attrition by job role

This code should look familiar – I’m extracting the total number of employees, the number of employees who left, and the attrition rate for this month by job role.

jobrole_attrition <- as.data.frame(
  as.list(aggregate(Attrition ~ JobRole,
                    data = mydata,
                    FUN = function(x) c(
                      n = length(x),
                      s = sum(x, na.rm = T),
                      mn = mean(x, na.rm = T))
                    )
          ))
names(jobrole_attrition) = c('JobRole', 'Headcount', 'Attrition', 'AttritionRate')

For making graphs, I use the popular ggplot2 package. It’s a really powerful package that allows you to visualize all kinds of data, and customize the resulting plots.

Here, I make a bar chart where each bar represents a JobRole and the height of the bar represents the attrition rate. I save the graph as jobrole_attrition_plot so I can use it later.

I do a bit of customization here. I use scale_fill_manual to make the bars blue. Theme_minimal removes a lot of extra chart lines that I prefer not be there. I also change the axis labels into percentages with labels=percent, flip the graph so it’s horizontal with coord_flip, and add custom labels with labs.

(jobrole_attrition_plot <- ggplot(jobrole_attrition,
                                  aes(reorder(JobRole, AttritionRate), AttritionRate)) +
  geom_col(aes(fill = '')) +
  scale_fill_manual(values = c("#1D243C"),guide=FALSE) +
  theme_minimal() +
  scale_y_continuous(labels=percent) +
  labs(list(y = paste("Attrition in ",month,sep = ""), x = "Job Role",
            title = paste("Attrition by Job Role, ", date, sep = ""))) +
  theme(panel.grid.minor = element_blank()) +
  coord_flip())

I want my table to be sorted from highest to lowest, so first I sort my dataset.

jobrole_attrition <- jobrole_attrition[order(jobrole_attrition$AttritionRate, decreasing = TRUE),]

Although I won’t include it in this table, I can use this information to get the impact of reducing attrition for the job role with the highest attrition. I’ll use this in the recommendations section, and it’s easier to calculate it now.

reduction = .05            # how much could we reduce attrition?
replacement_mult = 1.5       # how much does it cost to replace an employee, as a multiplier of their salary?
top_role <- as.character(jobrole_attrition$JobRole[1])   # job role with highest attrition
top_role_attrition <- jobrole_attrition$AttritionRate[1] # attrition for that role
left_from_top_role <- mydata$JobRole == top_role & mydata$Attrition == 1 
salary_lost = 
  sum(mydata$MonthlyIncome[left_from_top_role]) * 12 *     # total annual salary lost 
  replacement_mult                                         # times replacement cost
impact = salary_lost * reduction # amount we could save

Formatting the table should look familiar.

# format as percentages, rename columns
jobrole_attrition$AttritionRate <- percent(jobrole_attrition$AttritionRate)
names(jobrole_attrition) = c('Job Role', 'Headcount', 'Attrition', 'Attrition %')
# put data into a table
options("ReporteRs-fontsize"=16) #font size for tables
table_widths = c(2.8, 1.2, 1, 1.6) 
jobrole_attrition_table <- vanilla.table(jobrole_attrition) %>%
  setZebraStyle(odd = '#eeeeee', even = 'white' ) %>%
  setFlexTableWidths(widths = table_widths) 
jobrole_attrition_table[,2:4] = parCenter()
jobrole_attrition_table[,1] = parLeft()
jobrole_attrition_table[,,to='header'] = parCenter()
jobrole_attrition_table

Job Role	Headcount	Attrition	Attrition %
Sales Representative	83	33	39.8%
Laboratory Technician	259	62	23.9%
Human Resources	52	12	23.1%
Sales Executive	326	57	17.5%
Research Scientist	292	47	16.1%
Manufacturing Director	145	10	6.9%
Healthcare Representative	131	9	6.9%
Manager	102	5	4.9%
Research Director	80	2	2.5%

Now I’m going to make the same graph and table for performance and hire source. The only difference is I use scale_x_reverse on performance rating so that the ‘1’ rating is at the top of the graph. I’m also calculating the hire sources that have the highest and lowest attrition rates, for use in the recommendations section.

Attrition by Performance

performance_attrition <- as.data.frame(
  as.list(aggregate(Attrition ~ PerformanceRating,
                    data = mydata, FUN = function(x) c(
                      n = length(x),
                      s = sum(x, na.rm = T),
                      mn = mean(x, na.rm = T))
                    )
          ))
names(performance_attrition) = c('PerformanceRating', 'Headcount', 'Attrition', 'AttritionRate')
(performance_attrition_plot <- ggplot(performance_attrition, aes(PerformanceRating, AttritionRate)) +
  geom_bar(stat = "identity", aes(fill = '')) +
  scale_fill_manual(values = c("#1D243C"),guide=FALSE) +
  theme_minimal() +
  scale_y_continuous(labels=percent) +
  scale_x_reverse() +
  labs(list(y = paste("Attrition in ",month,sep = ""), x = "Performance Rating",
            title = paste("Attrition by Performance Rating, ", date, sep = ""))) +
  theme(panel.grid.minor = element_blank()) +
  coord_flip())

# format as percentages, rename columns
performance_attrition$AttritionRate <- percent(performance_attrition$AttritionRate)
names(performance_attrition) = c('Performance Rating', 'Headcount', 'Attrition', 'Attrition %')
# put data into a table
performance_attrition_table <- vanilla.table(performance_attrition) %>%
  setZebraStyle(odd = '#eeeeee', even = 'white' ) %>%
  setFlexTableWidths(widths = table_widths) 
performance_attrition_table[,1:4] = parCenter()
performance_attrition_table[,,to='header'] = parCenter()
performance_attrition_table

Performance Rating	Headcount	Attrition	Attrition %
1	16	5	31.2%
2	359	56	15.6%
3	841	137	16.3%
4	248	38	15.3%
5	6	1	16.7%

Attrition by Recruiting Channel

hiresource_attrition <- as.data.frame(
  as.list(aggregate(Attrition ~ HireSource, data = mydata,
                    FUN = function(x) c(
                      n = length(x),
                      s = sum(x, na.rm = T),
                      mn = mean(x, na.rm = T))
                    )
          ))
names(hiresource_attrition) = c('HireSource', 'Headcount', 'Attrition', 'AttritionRate')
(hiresource_attrition_plot <- ggplot(hiresource_attrition, aes(reorder(HireSource, AttritionRate), AttritionRate)) +
  geom_bar(stat = "identity", aes(fill = '')) +
  scale_fill_manual(values = c("#1D243C"),guide=FALSE) +
  theme_minimal() +
  scale_y_continuous(labels=percent) +
  labs(list(y = paste("Attrition in ",month,sep = ""), x = "Job Level",
            title = paste("Attrition by Recruiting Channel, ", date, sep = ""))) +
  theme(panel.grid.minor = element_blank()) +
  coord_flip())

# sort by attrition rate, format as percentages, rename columns
hiresource_attrition <- hiresource_attrition[order(hiresource_attrition$AttritionRate, decreasing = TRUE),]
hiresource_attrition$AttritionRate <- percent(hiresource_attrition$AttritionRate)
names(hiresource_attrition) = c('Recruiting Channel', 'Headcount', 'Attrition', 'Attrition %')
# put data into a table 
hiresource_attrition_table <- vanilla.table(hiresource_attrition) %>%
  setZebraStyle(odd = '#eeeeee', even = 'white' ) %>%
  setFlexTableWidths(widths = table_widths) 
hiresource_attrition_table[,1] = parLeft()
hiresource_attrition_table[,2:4] = parCenter()
hiresource_attrition_table[,,to='header'] = parCenter()
hiresource_attrition_table

Recruiting Channel	Headcount	Attrition	Attrition %
Search Firm	32	8	25.00%
Applied Online	385	81	21.04%
Campus	257	51	19.84%
Referral	178	31	17.42%

# save hire sources with highest and lowest levels of attrition
top_hire <- hiresource_attrition$`Recruiting Channel`[1]
bottom_hire <- hiresource_attrition$`Recruiting Channel`[nrow(hiresource_attrition)]

What’s great about using R to do the graphs is that whenever I need to update this report with new data, all I have to do is change the date, and the rest happens automatically. It takes a little more investment at first, but now all that dreaded Excel work will take almost no time at all!

Creating the Report

Explanation

Once the charts and tables are finished, I need to put them into PowerPoint. I’ve done this many times by copy-pasting the graphs from R into the report, but it’s tedious and hard to be consistent with their size and placement.

Again, for more details about how this works, the guide is on the author’s github page (http://davidgohel.github.io/ReporteRs/articles/powerpoint.html).

First I create a new pptx document and call it report. I set the font size to 24, and I like to use slide.layouts(report) to see which PowerPoint slide layouts are available. The basic idea is to add a slide (addSlide), choose a layout, and then fill that layout with a title (addTitle) and text (addParagraph), a table (addFlexTable), or a graph (addPlot). Finally, I use writeDoc to save the file. I’m saving it into the hr_data folder. You’ll need to make sure that path matches where you want to save it to.

You’ll also see an argument for template - I’m using a PowerPoint template for this report. This means you can build these reports directly on your corporate report template. I have the template saved in the hr_data folder on my machine. You’ll need to update that path with wherever your template is stored.

When you see code like this: par.properties = parProperties(list.style = "unordered", level = 1), That’s how ReporteRs can produce indented bullet point lists. Level = 2 would indent the bullets one level, level = 3 would be another level.

If you’ve never seen the %>% operator, you can read it like, “then”. So the following code:

report <- report %>%
  addSlide(slide.layout = "Title Slide") %>%
  addTitle(paste(date, "Acme Co. Attrition Report"))

can be read, “take report, then add a title slide, then add a title, and save it as report.”

Code to produce and publish the report

report <- pptx(title = paste(date, "Acme Co. \nAttrition Report"),
              template = '~/hr_data/example_corporate_template.pptx')
options("ReporteRs-fontsize"=24) #font size for text
slide.layouts(report)

 [1] "Picture with Caption"           "Alternate Section Header"       "Two Content"                   
 [4] "Alternate Content with Caption" "Comparison"                     "Title Slide"                   
 [7] "Title and Content"              "Title and Vertical Text"        "Section Header"                
[10] "Vertical Title and Text"        "Content with Caption"           "Title Only"                    
[13] "Blank"                         

report <- report %>%
  addSlide(slide.layout = "Title Slide") %>%
  addTitle(paste(date, "Acme Co. Attrition Report"))
report <- report %>%
  addSlide(slide.layout = "Title and Content") %>%
  addTitle("Table of Contents") %>%
  addParagraph( c("Overall Attrition Statistics"), 
                par.properties = parProperties(list.style = "unordered", level = 1)) %>%
  addParagraph( c("Attrition by Subgroups"), append = T,
                par.properties = parProperties(list.style = "unordered", level = 1)) %>%
  addParagraph( set_of_paragraphs("by Job Role",
                                  "by Performance Rating",
                                  "by Job Level"),
                append = T,
                par.properties = parProperties(list.style = "unordered", level = 2) )%>%
  addParagraph( c("Recommendations"), append = T,
                par.properties = parProperties(list.style = "unordered", level = 1))
report <- report %>%
  addSlide(slide.layout = "Section Header") %>%
  addTitle("Overall Attrition Statistics")
report <- report %>%
  addSlide(slide.layout = "Title and Content") %>%
  addTitle(paste0("Summary of Attrition in ", date)) %>%
  addFlexTable(overall_attrition_table)
report <- report %>%
  addSlide(slide.layout = "Section Header") %>%
  addTitle("Attrition by Subgroup")
report <- report %>%
  addSlide(slide.layout = "Two Content") %>%
  addTitle("Which Jobs have the Highest Attrition?") %>%
  addPlot(function() print(jobrole_attrition_plot)) %>%
  addFlexTable(jobrole_attrition_table)

System font `Calibri` not found. Closest match: `Verdana`

report <- report %>%
  addSlide(slide.layout = "Two Content") %>%
  addTitle("Are We Keeping Our Top Performers?") %>%
  addPlot(function() print(performance_attrition_plot)) %>%
  addFlexTable(performance_attrition_table) 
report <- report %>%
  addSlide(slide.layout = "Two Content") %>%
  addTitle("Which Hiring Channels have High Turnover?") %>%
  addPlot(function() print(hiresource_attrition_plot)) %>%
  addFlexTable(hiresource_attrition_table)
report <- report %>%
  addSlide(slide.layout = "Title and Content") %>%
  addTitle("Key Insights") %>%
  addParagraph( c(paste0("The job group with the highest attrition was ", top_role,
                        " with an attrition rate of ",as.character(jobrole_attrition$`Attrition %`[1]),".")), 
                par.properties = parProperties(list.style = "unordered", level = 1)) %>%
  addParagraph( c(paste0("Additional focus should be placed on retaining ", top_role,
                        "s.")),
                append = T,
                par.properties = parProperties(list.style = "unordered", level = 2)) %>%
  addParagraph( c(paste0("We estimate that replacing an employee costs ", replacement_mult,
                         "x their annual salary. Reducing attrition by ", percent(reduction),
                         " in ", month, " could have saved ", dollar(round(impact,0)), ".")),
                append = T,
                par.properties = parProperties(list.style = "unordered", level = 2)) %>%
  addParagraph( c(paste0("Our ", hiresource_attrition$`Recruiting Channel`[1],
                         " hires have the highest attrition rate. The channel with the lowest attrition rate in ",
                         month, " was the ",
                         hiresource_attrition$`Recruiting Channel`[nrow(hiresource_attrition)], " channel.")), 
                append = T,
                par.properties = parProperties(list.style = "unordered", level = 1)) 
writeDoc(report, paste0("~/hr_data/examples/",date," Attrition Report.pptx"))

And there you have it! The final report can be seen on SlideShare (http://www.slideshare.net/BenTeusch/automated-attrition-report-using-r). Again, if you’re interested in starting with a simple version of this code, check out http://rpubs.com/teuschb/making_ppt_slides_simple.

I hope this guide can be helpful for analytics teams, and especially people analytics teams or HR reporting teams, who would like to reduce the amount of time they spend on routine reports. Well-considered reporting is still a pillar of effective people analytics, but the best teams reduce the time they spend on it as much as possible, so they can work on more advanced analytics. If you find other helpful guides for people analytics, or would like to see something specific, please let me know in the comments!