Learn about R and do useful day-job stuff with it

Sub-objectives

• Install R
• Connect to database using RODBC
• Manipulate and summarise data using data.table
• Visualise your data using ggplot2
• Present your data using rmarkdown and knitr
• Produce a report using shiny

• BI & Credit Risk analyst for Optimum Credit Ltd
• 7 years of analysis & BI
• Presenter (obviously!) on R, and other BI topics
• User group leader: SQL Cardiff and CaRdiff R User Group
• Co-author of Tribal SQL

itsalocke.com | github.com/StephLocke | @SteffLocke

I Google frantically and find out…

• R (birthed out of S) has been around for ages
• Has massively increased in popularity
• Lots of big players like SAP, TIBCO, and Oracle have implemented it
• It's statistician heaven!
• It produces very pretty charts
• Huh, you can make XKCD cartoons in it?

Eep, but I'm not a stats bod or a programmer, what will I do? Do my usual stuff, but swisher!

OK, I need to get this thing called R installed on my laptop!

• Install R from r-project.org myself
• Jump on IT's desk until they install Rstudio via Rstudio.com for me
• Use R to nab some useful packages

install.packages(c(
  "RODBC",      # Database access
  "data.table", # Table manipulation
  "ggplot2",    # Charting
  "scales",     # Scale manipulations
  "rmarkdown",  # Presentation writing
  "knitr",      # Presentation production
  "shiny"       # Reporting
  ))

I'll report on global bike sales from our company's database

• Database provided by Redgate, hosted on Azure
• Use your relevant driver (often the one you'll use in Excel) data
• You can also connect to hundreds of other data stores like Excel

Establishing a connection

# Getting data
library("RODBC")

# Manipulating data
library("data.table")

# Build a connection to the DB for reuse
# Driver names can vary eg 
# {ODBC Driver 11 for SQL Server}
azure <- odbcDriverConnect(
          "Driver={SQL Server Native Client 11.0};
          Server=mhknbn2kdz.database.windows.net;
          Database=AdventureWorks2012;
          Uid=sqlfamily;
          Pwd=sqlf@m1ly;")

Getting our starting data

Order    <- data.table( sqlQuery( azure, 
         "SELECT * FROM [Sales].[SalesOrderHeader]"))

Territory<- data.table( sqlQuery( azure, 
         "SELECT * FROM [Sales].[SalesTerritory]"))

Region   <- data.table( sqlQuery( azure, 
         "SELECT * FROM [Person].[CountryRegion]"))

Hmm, I need to put all the data into a "flat-file" view

• Set "primary keys" with setkey
• Join in format Y[X] to join Y on to X
• Equivalent of LEFT JOINs in SQL
• Equivalent of extensive VLOOKUPs in Excel

Join Territory to the Order

# Specify the columns to join by
setkey(Order     , TerritoryID)
setkey(Territory , TerritoryID)

# Reads as join Territory to the Order table
# Seperate table so we don't have to start again
OrderTerritory <- Territory[Order]

Join Region to the OrderTerritory

# Specify the columns to join by
setkey(OrderTerritory , CountryRegionCode)
setkey(Region         , CountryRegionCode)

# Reads as join Region to the OrderTerritory table
# Will overwrite as it's our desired state
OrderTerritory <- Region[OrderTerritory]

OK, let's produce some tables!

• Lots of functionality, but kept quite simple here
• Read data.table intro for a great in-depth introduction

Basic summary

OrderTerritory[  , #Nothing in 1st position
               .( Value  = sum(TotalDue) ,
                  Volume = .N ), # Aggregations
               by = Name # Group by, no alias
               ]

Basic summary

Top 5 sales ever

OrderTerritory[order(-TotalDue)[1:5],
               # Picks first 5 rows of TotalDue desc
               .( SalesOrderNumber, # multi-select
                  AccountNumber,
                  Region=Name, # Aliasing colum
                  TotalDue,
                  OrderDate
                  )
               ]

Top 5 sales ever

Top sale ever for each region

OrderTerritory[  ,
               .SD[which.max(TotalDue),
               # .SD is subset of rows within group
               
               # which.max() returns row 
               # no of largest val
                   .( SalesOrderNumber,
                      AccountNumber,
                      TotalDue,
                      OrderDate
                    )
                   ],
               by=.(Region=Name)
               #Aliasing in groupung
               ]

Top sale ever for each region

I need to make some pretties!

• Using ggplot2 as much easier than base graphics
• Simple structure = chart(data, axis & series) + chart types + formatting
• No secondary axes as considered bad visualisation, use optiRum::multiplot()
• Easy trellis / small multiple charts

Basic chart – volumes

library(ggplot2)

ggplot(OrderTerritory                 # dataset
       ,aes(x=Name, y=..count..))+    # values
  geom_bar()+                         # chart type
  theme_minimal()+                    # themeing
  labs(x="Region",title="All time sales volumes")

Basic chart – volumes

Basic chart – value

library(scales)

ggplot(OrderTerritory               
       ,aes(x=Name, y=TotalDue))+    
  geom_bar(stat="identity")+         # use value
  theme_minimal()+                   
  scale_y_continuous(label=dollar)+  # customisation
  labs(x="Region",title="All time sales value")

Basic chart – value

Trellis chart – value

ggplot(OrderTerritory
       ,aes(x=year(OrderDate), y=TotalDue, 
            colour=Name))+ 
  geom_bar(stat="identity")+
  theme_minimal()+                  
  scale_y_continuous(label=dollar)+ 
  facet_wrap(~Name,ncol = 2) +   # trellis
  xlab("Order Year")

Trellis chart – value

I need a slide deck for the boss to take use!

• Use rmarkdown to produce SamplePresentation.Rmd
• Really light syntax, same as Stack Overflow
• Customisable via CSS
• Create slides (html or PDF) or docs (html, PDF, Word)
• Use a modular design
• Re-knit to get latest data

I need a report for people to play with

• Use shiny to produce interactive online reports
• Can host locally (for other R users)
• Can use a server (for internal users, uses Linux)
• Can use a hosting platform like shinyapps.io

library("shiny")

#Run the shiny app (app.R) in the current folder
runApp(".")

This presentation is available on github.com/stephlocke/Rintro. All the code is available for you to take a copy and play with to help you learn on the go.

If you have any questions, contact me!

itsalocke.com | github.com/StephLocke | @SteffLocke