Creating Excel Workbooks with multiple sheets in R
[This article was first published on Data Science Using R – FinderDing, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
Create Excel Workbooks
Generally, when doing anything in R I typically work with .csv files, their fast and straightforward to use. However, I find times, where I need to create a bunch of them to output and having to go and open each one individually, can be a pain for anyone. In this case, it’s much better to create a workbook where each of the .csv files you would have created will now be a separate sheet.
Below is a simple script I use frequently that gets the job done. Also included is the initial process of creating dummy data to outline the process.
EXAMPLE CODE:
Libraries used
library(tidyverse) library(openxlsx)
Creating example files to work with
products <- c("Monitor", "Laptop", "Keyboards", "Mice")
Stock <- c(20,10,25,50)
Computer_Supplies <- cbind(products,Stock)
products <- c("Packs of Paper", "Staples")
Stock <- c(100,35)
Office_Supplies <- cbind(products,Stock)
# Write the files to our directory
write.csv(Computer_Supplies, "Data/ComputerSupplies.csv", row.names = FALSE)
write.csv(Office_Supplies, "Data/OfficeSupplies.csv", row.names = FALSE)
Point to directory your files are located in (.csv here) and read each in as a list
# Get the file name read in as a column
read_filename <- function(fname) {
read_csv(fname, col_names = TRUE) %>%
mutate(filename = fname)
}
tbl <-
list.files(path = "Data/",
pattern ="*.csv",
full.names = TRUE) %>%
map_df(~read_filename(.))
Removing path from the file names
*Note: Max length of a Workbook’s name is 31 characters
tbl$filename <- gsub("Data/", "", tbl$filename)
tbl$filename <- gsub(".csv", "", tbl$filename)
Split the “tbl” object into individual lists
mylist <- tbl %>% split(.$filename) names(mylist) ## [1] "/ComputerSupplies" "/OfficeSupplies"
Creating an Excel workbook and having each CSV file be a separate sheet
wb <- createWorkbook()
lapply(seq_along(mylist), function(i){
addWorksheet(wb=wb, sheetName = names(mylist[i]))
writeData(wb, sheet = i, mylist[[i]][-length(mylist[[i]])])
})
#Save Workbook
saveWorkbook(wb, "test.xlsx", overwrite = TRUE
Reading in sheets from an Excel file
(The one we just created)
df_ComputerSupplies <- read.xlsx("test.xlsx", sheet = 1)
Loading and adding a new sheet to an already existing Excel workbook
wb <- loadWorkbook("test.xlsx")
names(wb)
## [1] "/ComputerSupplies" "/OfficeSupplies"
addWorksheet(wb, "News Sheet Name")
names(wb)
## [1] "/ComputerSupplies" "/OfficeSupplies" "News Sheet Name"
The post Creating Excel Workbooks with multiple sheets in R appeared first on FinderDing.
To leave a comment for the author, please follow the link and comment on their blog: Data Science Using R – FinderDing.
R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.