R code to split the gapminder dataset across multiple tabs in an excel file by year

make_gapminder_messy_again.R

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)

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

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.