Purpose

This page demonstrates the ease of automating a powerpoint out of a big dataset. The dataset used in this case has been extracted from the Fingertips API, which is demonstrated at the following link: <rpubs.com/sebsfox/fingertipsAPI>.

Libraries

First, you need to install the libraries that you will be using:

library(DT)
library(dplyr)
library(R2PPT) #for ppt production
library(RDCOMClient) #for ppt production
library(ggplot2)
library(stringr)

Dataset

This is the dataset that I am going to produce the ppt from:

This is the structure of the data:

str(df)
## 'data.frame':    34046 obs. of  8 variables:
##  $ .id      : int  90630 90630 90630 90630 90630 90630 90630 90630 90630 10101 ...
##  $ AgeId    : int  199 199 199 199 199 199 199 199 199 169 ...
##  $ SexId    : int  4 4 4 4 4 4 4 4 4 4 ...
##  $ AreaCode : Factor w/ 152 levels "E06000001","E06000002",..: 15 16 17 18 129 139 140 142 144 15 ...
##  $ Val      : num  22.1 25.5 7.1 31.6 15.1 10.6 15.7 14.7 15.9 22.9 ...
##  $ Sex      : Factor w/ 4 levels "Female","Male",..: 4 4 4 4 4 4 4 4 4 4 ...
##  $ Age      : Factor w/ 50 levels "< 1 yr","<15 yrs",..: 7 7 7 7 7 7 7 7 7 3 ...
##  $ Indicator: Factor w/ 152 levels "0.1i - Healthy life expectancy at birth",..: 7 7 7 7 7 7 7 7 7 8 ...

Adding deprivation

To make the graphs a little more interesting, I join deprivation decile onto each upper tier local authority record. Deprivation comes from the original dataset extracted from the Fingertips API above, and is indicator id 338. I have created a lookup by filtering for that indicator

head(deprivation)
##    AreaCode decile
## 1 E06000015      6
## 2 E06000016      9
## 3 E06000017      1
## 4 E06000018      9
## 5 E10000007      4
## 6 E10000018      1
df <- left_join(df,deprivation)
head(df)
##     .id AgeId SexId  AreaCode  Val     Sex      Age
## 1 90630   199     4 E06000015 22.1 Persons 0-19 yrs
## 2 90630   199     4 E06000016 25.5 Persons 0-19 yrs
## 3 90630   199     4 E06000017  7.1 Persons 0-19 yrs
## 4 90630   199     4 E06000018 31.6 Persons 0-19 yrs
## 5 90630   199     4 E10000007 15.1 Persons 0-19 yrs
## 6 90630   199     4 E10000018 10.6 Persons 0-19 yrs
##                                                                   Indicator
## 1 1.01i - Children in low income families (all dependent children under 20)
## 2 1.01i - Children in low income families (all dependent children under 20)
## 3 1.01i - Children in low income families (all dependent children under 20)
## 4 1.01i - Children in low income families (all dependent children under 20)
## 5 1.01i - Children in low income families (all dependent children under 20)
## 6 1.01i - Children in low income families (all dependent children under 20)
##   decile
## 1      6
## 2      9
## 3      1
## 4      9
## 5      4
## 6      1

Create the presentation

The principle of creating the presentation is to loop through all of the different indicators in the dataset. During the loop, you need to filter for each indicator into a separate dataset, and then use that dataset to produce your graph. Each graph you create, you write to a temporary file, which you then import into a presentation…simple!

mypres <- PPT.Init(method="RDCOMClient") #this initiates the presentation
mypres <- PPT.AddTitleSlide(mypres,title="Automated ppt from R",
                            subtitle="...using the Fingertips API")

#This demonstration is for the first 50 indicators in PHOF (see line below)
for (i in levels(df$Indicator)[1:50]){
        
        dfFin <- filter(df,Indicator == i & Val != -1) #filter for the indicator and remove records with value = -1 (eg, not to be displayed)
        
        p <- ggplot(dfFin,aes(x=decile,y=Val))
        p <- p + geom_boxplot() +
                facet_grid(Age ~ Sex) +
                labs(title=str_wrap(i,60),
                     y="Area value",
                     x="Deprivation decile") +
                theme_bw()
        
        ggsave(my_temp_file<-paste(tempfile(),".png",sep=""), plot=p,width=20,height=12,units="cm")
        mypres <- PPT.AddBlankSlide(mypres)
        mypres <- PPT.AddGraphicstoSlide(mypres,file=my_temp_file)
        unlink(my_temp_file)
}
#mypres<-PPT.ApplyTemplate(mypres,file=[INSERT LOCATION OF .potx file here])
PPT.SaveAs(mypres,file="PHOF boxplots.pptx")

This produces nice charts in a powerpoint presentation such as these:

dfFin <- filter(df,Indicator == levels(df$Indicator)[1] & Val != -1)
p <- ggplot(dfFin,aes(x=decile,y=Val))
p + geom_boxplot() +
        facet_grid(Age ~ Sex) +
        labs(title=str_wrap(levels(df$Indicator)[1],60),
             y="Area value",
             x="Deprivation decile") +
        theme_bw()