I have a table in Excel that looks like this:
| Country | X | Y |
|---|---|---|
| US | 01 | A |
| UK | 02 | B |
| FR | 03 | C |
Using R (I imported the excel using "readxl" package) and Rmarkdown, I would like to create, for each country, a word file showing only the X and Y values related to that country. So for example, the word file generated for the US would have "US" as title, and simply a table showing:
| X | Y |
|---|---|
| 01 | A |
Can anybody help? Many thanks in advance!
Create a rmarkdown document:
---
title: "`r Country`"
output: word_document
---
```{r results='asis', echo=FALSE}
knitr::kable(df_xy, row.names = FALSE)
```
In a separate .R file set up a loop to render the rmarkdown files with required data.
In your use case just substitute df1 with the output from readxl::read_xlsx().
df1 <- data.frame(Country = rep(c("US", "UK", "FR"), each = 3),
X = rep(c("01", "02", "03"), each = 3),
Y = LETTERS[1:9])
for (Country in unique(df1$Country)) {
df_xy <- df1[df1$Country == Country, c("X", "Y")]
rmarkdown::render(
'country_xy.Rmd',
output_file = paste0(Country, '.docx')
)
}
You should end up with, in this case, three files:
Which results in a WORD file as so:
A really useful resource for rmarkdown is: https://bookdown.org/yihui/rmarkdown-cookbook/rmarkdown-render.html