Instantly share code, notes, and snippets.

Embed
What would you like to do?
R code to split the gapminder dataset across multiple tabs in an excel file by year
library(tidyverse)
library(gapminder)
library(openxlsx)
gm <- gapminder %>%
select(-continent)
# to get latest year as first tab in workbook
years <- unique(gm$year) %>% sort(decreasing = TRUE)
dirty_tables <- years %>%
map(~ gm %>%
filter(year == .x))
wb <- openxlsx::createWorkbook()
1:length(years) %>%
walk(~{
data <- dirty_tables[[.x]] %>%
select(-year)
addWorksheet(wb, years[.x])
writeData(wb, .x, x = paste("Tab Number:", .x), startCol = 1, startRow =1)
writeData(wb, .x, x = paste("Year:", years[.x]), startCol = 1, startRow = 2)
writeData(wb, .x, x = "Some more info that probably should be part of the data table", startCol = 1, startRow = 3)
writeDataTable(wb, .x, data, startRow = 5, startCol = 1, tableStyle = "TableStyleMedium16", withFilter = FALSE)
setColWidths(wb, .x, cols = 1:4, widths = c(56.43, 11.29, 10.29, 9.29))
})
saveWorkbook(wb, "gapminder_messy.xlsx", overwrite = TRUE)
@stewartli

This comment has been minimized.

Copy link

stewartli commented Oct 26, 2018

Thank you very much for sharing such a useful function.
One question. Where can I learn more about ".x"?
Cheers.
Stewart

@PaulC91

This comment has been minimized.

Copy link
Owner Author

PaulC91 commented Nov 7, 2018

Hi Stewart,

The map() and walk() functions from the purrr package apply a function to each element of a vector. If we take the walk() example above, I supply a vector of numbers 1:length(years) (the number of unique years in the data) to walk() which then iterates over each number in the vector and supplies it to the function I created to add a worksheet to an excel workbook.

The .x notation here references the supplied argument of each iteration.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment