Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
R code to split the gapminder dataset across multiple tabs in an excel file by year
gm <- gapminder %>%
# 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) %>%
data <- dirty_tables[[.x]] %>%
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)

This comment has been minimized.

Copy link

commented Oct 26, 2018

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


This comment has been minimized.

Copy link
Owner Author

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
You can’t perform that action at this time.