Convert a single excel file into multiple excel files based on the value of a column in R

Question

I am hoping to be able to break a single excel file into separate excel files based on the value of a single column in the original file (eg. State). Is there any way I can do this in R (preferable) ? Or in Excel ?

Answer 1

You could read your excel file into R, then use dplyr and a for loop: library(dplyr)

# Here, you would read in your excel file
# dt <- readxl::read_excel("your_path")

# For the purpose of this example, just using an example dataset:
dt <- PlantGrowth
head(dt)

# Identify unique group
(groups <- unique(dt$group)) # here, you would use your states

# Then filter out each group and save in a loop
for (i in seq_along(groups)) {
        dt2 <- dt %>% filter(group == groups[i])
        write.csv(dt2, paste0("subset_", groups[i], ".csv"), row.names = FALSE)
}

In my home folder, 3 files were created, one per group (see screenshot and preview for subset_trt2.csv).