How to split a data frame by row into multiple data frames and then export them to excel? R

user_12512712 0
Viewed 556

This is the data I am working with

Sales <- data.frame(State  = c("New York", "New Jersey", "Texas","New Mexico","California",
                                "Kansas","Florida","Alaska","Montana", "Maine"),
                     
                  Units = c(455,453,125,135,135,568,451,125,215,314),
                  
                  Sales = c("20000","12530","51110","54110","65000",
                            "58220","54612","45102","45896","12510"),
                             
                  ManagerName = c("Chip","Kelly","Steve","Young","Troy",
                                  "Aikman", "Chad","Pennington","Mike","Vick")
                  
)

My end goal is to have 10 different excel files for each "State".

I know I can do something like

#Seperate by State
library(dplyr)
NewYork <- Sales %>% filter(State == "New York") 

#Save to excel
library(xlsx)
write.xlsx(NewYork, file="NewYork-Sales-November2020.xlsx", 
           sheetName="NewYork")

But the problem is that I have over 50+ rows and do not want to have 50 commands to filter by state and then another 50 commands to write an excel file.

Is there a more efficient way to save each row "State" into an different excel files?

dplyr r
4 Answers

The following does what the question asks for, I have tested it with the posted data.

  1. split the data by State;
  2. create a workbook;
  3. add work sheets to the workbook;
  4. write each sub-data.frame to the workbook;
  5. save everything to disk.

The sheets are named by State.

library(openxlsx)

sp <- split(Sales, Sales$State)
names(sp) <- ifelse(nchar(names(sp)) > 31, substr(names(sp), 1, 31), names(sp))
wb <- createWorkbook(creator = "FruityPebblePug")
lapply(seq_along(sp), function(i){
  addWorksheet(wb, sheetName = names(sp)[[i]])
})
lapply(seq_along(sp), function(i){
  writeData(wb, sheet = names(sp)[[i]], sp[[i]])
})
saveWorkbook(wb, "Sales-November2020.xlsx")

As noted in comments, if the sheets names have too many characters, exceding Excel's 31 characters limit, the following will name the sheets "State 1", "State 2", etc.

sp <- split(Sales, Sales$State)
wb <- createWorkbook(creator = "FruityPebblePug")
lapply(seq_along(sp), function(i){
  addWorksheet(wb, sheetName = paste("State", i))
})
lapply(seq_along(sp), function(i){
  writeData(wb, sheet = paste("State", i), sp[[i]])
})
saveWorkbook(wb, "Sales-November2020_b.xlsx")
user_82454060

A short for() loop example

for (i in Sales$State){
  print(i)
  FilterData <- Sales %>% filter(State == i) 
  filename<-paste0(i, "-Sales-Nov2020.xlsx")
  write.xlsx(FilterData, file=filename, sheetName=i)
}
user_102760920

Perhaps try this

ls_of_dfs <- split(Sales, Sales$State)
mapply(write.xlsx, ls_of_dfs, sheetName = names(ls_of_dfs), MoreArgs = list(file = "NewYork-Sales-November2020.xlsx", append = TRUE))

Remember to delete any file named "NewYork-Sales-November2020.xlsx" in your working directory before trying the code above. You need to start with a clean slate.

user_108024990

I usually prefer to write all of them in one file in separate sheets. Otherwise you can use purrr::iwalk() function to write each of them into a separate file.

library(rlang)
library(tidyverse)
library(writexl)

Split by group and give the name for each list

# https://github.com/tidyverse/dplyr/issues/4223
named_group_split <- function(.tbl, mysep = "", ...) {
  grouped <- group_by(.tbl, ...)
  names <- rlang::eval_bare(rlang::expr(paste(!!!group_keys(grouped), sep = mysep)))
  
  grouped %>%
    group_split(., .keep = FALSE) %>%
    rlang::set_names(names)
}

Sales_list <- Sales %>% 
  named_group_split(., mysep = "", State)
Sales_list

# make temporary directory
tmp_dir <- tempdir()

Write each list to each sheet in one file

write_xlsx(Sales_list, file.path(tmp_dir, "Sales_by_state_all.xlsx"))

Write each list to a separate file

iwalk(Sales_list, ~ write_xlsx(.x, file.path(tmp_dir, paste0("Sales_", .y, ".xlsx"))))
user_7865420
Related