31 August 2017 | by Tim Bock

Create and Update PowerPoint Reports using R

In my sordid past, I was a data science consultant. One thing about data science that they don’t teach you at school is that senior managers in most large companies require reports to be in PowerPoint. Yet, I like to do my more complex data science in R – PowerPoint and R are not natural allies. As a result, creating an updating PowerPoint reports using R can be painful.

In this post, I discuss how to make R and PowerPoint work efficiently together. The underlying assumption is that R is your computational engine and that you are trying to get outputs into PowerPoint. I compare and contrast three tools for creating and updating PowerPoint reports using R: free ReporteRs package with two commercial products, Displayr and Q.

Update: Some extra approaches have been pointed out by kind readers, so please read the comments to this post as well.


Option 1: ReporteRs

The first approach to getting R and PowerPoint to work together is to use David Gohel’s ReporteRs. To my mind, this is the most “pure” of the approaches from an R perspective. If you are an experienced R user, this approach works in pretty much the way that you will expect it to work.

The code below creates 250 crosstabs, conducts significance tests, and, if the p-value is less than 0.05, presents a slide containing each. And, yes, I know this is p-hacking, but this post is about how to use PowerPoint and R, not how to do statistics…

 
library(devtools)
devtools::install_github('davidgohel/ReporteRsjars')
devtools::install_github('davidgohel/ReporteRs')
install.packages(c('haven', 'vcd', 'ggplot2', 'reshape2'))
library(ReporteRs)
library(haven)
library(vcd)
library(ggplot2)
library(reshape2)
dat = read_spss("http://wiki.q-researchsoftware.com/images/9/94/GSSforDIYsegmentation.sav")
filename = "c://delete//Significant crosstabs.pptx" # the document to produce
document = pptx(title = "My significant crosstabs!")
alpha = 0.05 # The level at which the statistical testing is to be done.
dependent.variable.names = c("wrkstat", "marital", "sibs", "age", "educ")
all.names = names(dat)[6:55] # The first 50 variables int the file.
counter = 0
for (nm in all.names)
    for (dp in dependent.variable.names)
    {
        if (nm != dp)
        {
            v1 = dat[[nm]]
            if (is.labelled(v1))
                v1 = as_factor(v1)
            v2 = dat[[dp]]
            l1 = attr(v1, "label")
            l2 = attr(v2, "label")
            if (is.labelled(v2))
                v2 = as_factor(v2)
            if (length(unique(v1)) <= 10 && length(unique(v2)) <= 10) # Only performing tests if 10 or fewer rows and columns. 
            { 
                x = xtabs(~v1 + v2) 
                x = x[rowSums(x) > 0, colSums(x) > 0]
                ch = chisq.test(x)
                p = ch$p.value
                if (!is.na(p) && p <= alpha)
                {
                    counter = counter + 1
                    # Creating the outputs.
                    crosstab = prop.table(x, 2) * 100
                    melted = melt(crosstab)
                    melted$position = 100 - as.numeric(apply(crosstab, 2, cumsum) - 0.5 * crosstab)
                    p = ggplot(melted, aes(x = v2, y = value,fill = v1)) + geom_bar(stat='identity')
                    p = p + geom_text(data = melted, aes(x = v2, y = position, label = paste0(round(value, 0),"%")), size=4)
                    p = p + labs(x = l2, y = l1)
                    colnames(crosstab) = paste0(colnames(crosstab), "%")
                    #bar = ggplot() + geom_bar(aes(y = v1, x = v2), data = data.frame(v1, v2), stat="identity")
                    # Writing them to the PowerPoint document.
                    document = addSlide(document, slide.layout = "Title and Content" )
                    document = addTitle(document, paste0("Standardized residuals and chart: ", l1, " by ", l2))
                    document = addPlot(doc = document, fun = print, x = p, offx = 3, offy = 1, width = 6, height = 5 )
                    document = addFlexTable(doc = document, FlexTable(round(ch$stdres, 1), add.rownames = TRUE),offx = 8, offy = 2, width = 4.5, height = 3 )
                }
            }
            
       }
    }
writeDoc(document, file = filename )        
cat(paste0(counter, " tables and charts exported to ", filename, "."))

Please see the technical notes below before running this example code.

Below we see one of the admittedly ugly slides created using this code. With more time and expertise, I am sure I could have done something prettier. A cool aspect of the ReporteRs package is that you can then edit the file in PowerPoint. You can then get R to update any charts and other outputs originally created in R.

 

Chart created in PowerPoint using R

 


Option 2: Displayr

A completely different approach is to author the report in Displayr, and then export the resulting report from Displayr to PowerPoint.

This has advantages and disadvantages relative to using ReporteRs. First, I will start with the big disadvantage, in the hope of persuading you of my objectivity (disclaimer: I have no objectivity, I work at Displayr).

Each page of a Displayr report is created interactively, using a mouse and clicking and dragging things. In my earlier example using ReporteRs, I only created pages where there was a statistically significant association. Currently, there is no way of doing such a thing in Displayr.

The flipside of using the graphical user interface like Displayr is that it is a lot easier to create attractive visualizations. As a result, the user has much greater control over the look and feel of the report. For example, the screenshot below shows a PowerPoint document created by Displayr. All but one of the charts has been created using R, and the first two are based on a moderately complicated statistical model (latent class rank-ordered logit model).

You can access the document used to create the PowerPoint report with R here (just sign in to Displayr first) – you can poke around and see how it all works.

A benefit of authoring a report using Displayr is that the user can access the report online, interact with it (e.g., filter the data), and then export precisely what they want. You can see this document as it is viewed by a user of the online report here.

 

Demographics dashboard example from Displayr

 


Option 3: Q

A third approach for authoring and updating PowerPoint reports using R is to use Q, which is a Windows program designed for survey reporting (same disclaimer as with Displayr). It works by exporting and updating results to a PowerPoint document.

Q has two different mechanisms for exporting R analyses to PowerPoint. First, you can export R outputs, including HTMLwidgets, created in Q directly to PowerPoint as images. Second, you can create tables using R and then have these exported as native PowerPoint objects, such as Excel charts and PowerPoint tables.

In Q, a Report contains a series of analyses. Analyses can either be created using R, or, using Q’s own internal calculation engine, which is designed for producing tables from survey data.

The map above (in the Displayr report) is an HTMLwidget created using the plotly R package. It draws data from a table called Region, which would also be shown in the report. (The same R code in the Displayr example can be used in an R object within Q). So when exported into PowerPoint, it creates a page, using the PowerPoint template, where the title is Responses by region and the map appears in the middle of the page.

The screenshot below is showing another R chart created in PowerPoint. The data has been extracted from Google Trends using the gtrendsR R package. However, the chart itself is a standard Excel chart, attached to a spreadsheet containing the data. These slides can then be customized using all the normal PowerPoint tools and can be automatically updated when the data is revised.

 


Explore the Displayr example

You can access the Displayr document used to create and update the PowerPoint report with R here (just sign in to Displayr first). Here, you can poke around and see how it all works or create your own document.


Technical notes

Since this post was originally published, one of the packages used by the example code from the section Option 1: ReporteRs above has changed, preventing the code from running as described. In order to run the code above, you will need to install the previous version of the package gdtools (which is a dependency of the main package ReporteRs). You can do this with the following code:

packageurl = "https://cran.r-project.org/src/contrib/Archive/gdtools/gdtools_0.1.5.tar.gz"
install.packages(packageurl, repos=NULL, type="source") 

We will get in touch with the package authors and update this post again when the issue is resolved.

Additionally, there are a couple of things to keep in mind when running this example:

  • ReporteRs makes use of rJava, and you should make sure that you have the latest version of Java installed on your system.
  • You should create the folder c:\delete\ on your PC, or modify the path used in the example code.

Author: Tim Bock

Tim Bock is the founder of Displayr. Tim is a data scientist, who has consulted, published academic papers, and won awards, for problems/techniques as diverse as neural networks, mixture models, data fusion, market segmentation, IPO pricing, small sample research, and data visualization. He has conducted data science projects for numerous companies, including Pfizer, Coca Cola, ACNielsen, KFC, Weight Watchers, Unilever, and Nestle. He is also the founder of Q www.qresearchsoftware.com, a data science product designed for survey research, which is used by all the world’s seven largest market research consultancies. He studied econometrics, maths, and marketing, and has a University Medal and PhD from the University of New South Wales (Australia’s leading research university), where he was an adjunct member of staff for 15 years.


Tags

Explore

Access the Displayr document used to create and update the sexy PowerPoint report in this post (just sign in to Displayr first).

TRY IT OUT



15 Comments. Share your thoughts.

  1. Jim Hunter

    Unfortunately your code does not run as posted here. You have a typo on line 30 (“<= 10" twice) and an extra close bracket on line 56. Also there seems to be a problem with the Java interface in ReporteRsJars with the following report. The problem may be the original author's, not yours. My rJava package is functioning normally.

    Error : .onLoad failed in loadNamespace() for 'rJava', details:
    call: dyn.load(file, DLLpath = DLLpath, …)
    error: unable to load shared object '/Library/Frameworks/R.framework/Versions/3.4/Resources/library/rJava/libs/rJava.so':


    • Chris Facer

      Hi Jim,

      Sorry for the errors in the code and the delay in getting back to you! I am not sure about the error that you have encountered around rJava. I’ve found in the past that I needed to updated my Java install when encountering errors in rJava, but am not sure if that is the case here.

      I’ve fixed the code now, but encountered some further issues to do with some package dependencies which changed in the short time since we published the post originally. I’ve added some technical notes to the bottom of the article to explain in more detail.

      Kind regards,
      Chris


  2. donald trunp

    another software? another program? R is sufficiently, and free, and please do not make spam of your software


    • Gaurav Jain

      Sorry to have upset you! I don’t think we spammed you though; presumably you clicked on the blog post? Didn’t mean to trick you.


  3. Andrew

    There appears to be a couple of errors in line 30 of the code for option 1:

    <= 10 is duplicated
    { has been comment out


    • Chris Facer

      Hi Andrew,

      My apologies for the errors! I’ve fixed the code now, but encountered some further issues to do with some package dependencies which changed in the short time since we published the post originally. I’ve added some technical notes to the bottom of the article to explain in more detail.

      Kind regards,
      Chris


  4. Alexey Antonitsin

    Great post. What we currently do is:
    1) Run R to get the data, plot it and save as .bmp charts
    2) Insert in PP deck as linked object
    3) Run R and then simple macro in PP to update all charts


  5. C. Ritter

    Just for completeness, there is the package R2PPT by Wayne Jones. It uses the COM interface to PowerPoint. There are two COM clients, one in the Duncan Temple Lang’s package RDCOMClient and the other as a part of the StatconnDCOM Connector by Thomas Baier.
    Here is a code fragment to try it out:

    myPres<-PPT.Init(method="RDCOMClient",visible=TRUE)
    myPres<-PPT.AddTitleSlide(myPres,title="How to add R graphics to PowerPoint!")

    ## Example adding graphic from file
    myPres<-PPT.AddBlankSlide(myPres)
    jpeg(file="testRplot1.jpeg",quality=100,height=960,width=960)
    plot(rnorm(100))
    dev.off()
    # You must give full file path
    myPres<-PPT.AddGraphicstoSlide(myPres,file=paste(getwd(),"testRplot1.jpeg",sep="/"))
    unlink(paste(getwd(),"testRplot1.jpeg",sep="/"))
    ## Example adding graphic from current graphics device
    myPres<-PPT.AddBlankSlide(myPres)
    hist(rnorm(100))
    myPres<-PPT.AddGraphicstoSlide(myPres,size= c(10, 10, 300, 500))
    hist(runif(100))
    myPres<-PPT.AddGraphicstoSlide(myPres,size= c(350, 10, 300, 500))


  6. Norberto

    I tried installing all the packages, but your ReporteRs did not work for me.


  7. signs

    hi Tim , i used your code but it gave me several error. hope you would reply my comment to resolve my issues . thanks for the post


    • Tim Bock

      Hi media, What was the problem? If it is an error in my code, happy to help, but if it relates to how Java has been setup on your machine, that is well beyond my skills.


  8. Jon

    Great sharing! I could ran it. Just had to install “R.methodsS3”, “R.oo”, and “rJava” separately. Excellent solution to automate repetitive reports.


Leave a Reply

Your email address will not be published. Required fields are marked *

Human? *

Keep updated with the latest in data science.