Extras

11

# Extras

---

# <center>RMarkdown to R and back again</center>

1

---

# <center>RMarkdown to R and back again</center>

When you have RMarkdown scripts you want to make to R

```r
knitr::purl("01-bakeoff-report.Rmd", 
            documentation = 1)
```

]

???
`documentation =`
An integer specifying the level of documentation to add to the tangled script. 
0 means to output pure code, discarding all text chunks); 
1 (the default) means to add the chunk headers to the code; 
2 means to add all text chunks to code as roxygen comments. roxygen is used in packages

When you have R script you want to make to RMarkdown

❗ Note this will over-write scripts if they have the same name.

```r

knitr::spin("spin-bakeoff-report.R", 
            knit = FALSE, 
            format = "Rmd")
```

]

???
Special hash and quote used from royxgen. Shortcuts need to be added manually but
can be search and replaced from an R document.

https://github.com/yihui/knitr/blob/master/inst/examples/knitr-spin.R

Also shortcut for comment and uncomment is the same `Ctrl + Shift + c`

---

# <center>Knitting many documents</center>

2

---

# <center>Knitting many documents</center>

```r
files <- list.files(pattern = "[.]Rmd$")
```

Or list the Rmd files in a folder:

```r
files <- list.files(path = "03-images/", 
 pattern = "[.]Rmd$")
```

]

```r
purrr::map(
  .x = files,  
  .f = ~ rmarkdown::render(glue::glue(.x))
)
```

loop in a subfolder

```r
purrr::map(
  .x = files, 
  .f = ~ rmarkdown::render(glue::glue("03-images/", .x))
)
```

]

---

# <center>Source files</center>

3

---

# <center>Source files</center>

Instead of using import data code in the RMarkdown it can be "sourced" from another script.

```r

source("R/source_file_csv.R")

```

???
To show this use the `01-bakeoff-report.Rmd` and replace the `import-data` section with this code. The params$series don't need to be removed as this will still work.

---

# <center>Using SQL chunks</center>

4

---
class: center

# Using SQL chunks

![Screenshot of the add chunk short menu from the Editor bar](images/add-chunk-menu.PNG)

---
class: live-code

# Using SQL chunks

Show and tell for this SQL code in an RMarkdown

* Open file `sql-chunks.Rmd`.

* This file will knit but will just return code - there is no connection.

* Add the code `echo = FALSE` to the first chunk under `Default connection`.

SQL chunks options are supported the same options as R and can also be set globally.

---

# SQL Summary points

✔️ **It's not just R** RMarkdown combines text and other code (like SQL)

✔️ **Temporary tables - but in R** Creating tables of data in R is like creating temporary tables in SQL

✔️ **Use SQL power** R uses RAM so make use of the power of SQL servers

❗ **Each temporary table is an object** Unlike in SQL you cannot create a temporary table and then refer to it in later code. You have to use CTEs

❗ **nvarchar(max)** Some tables in SQL use nvarchar(max), varchar(max) or binary which cause your code to fail.  
The solution is to do a SELECT statement with the column at the end

❗ **Debugging is hard** Errors don't come through from SQL so can be hard to debug from RStudio

❗ **Case sensitivity** Microsoft SQL is not case sensitive and R is.

---

# <center>And that's the end...</center>

Special thanks to Alison Hill, Emi Tanaka and Desirée De Leon for the [original slides and content](https://ysc-rmarkdown.netlify.app/)

More special thanks to NHS-R Community and Hansel Palencia for the RMarkdown workshop run at the [2021 Conference](https://www.youtube.com/watch?v=RaM6fgwMZIs)