How to apply for loop inside a function which has filter function using dplyr?

Question

I would like to apply for loop on a filter function using dplyr in R.

sumstats<-function(x) {
    Countries <- c("abc","def","ghi")
    for (val in Countries) {
        result <- df %>% filter(COUNTRY.NAME == val)
        write.xlsx(result, "result.xlsx")
    }
}

However this function overwrites the existing sheet on excel, I would like the data to be written on separate excel sheets on a single workbook.

Answer 1

You have to create sheet name using sheetName = val and append that sheet into you excel file.

sumstats<-function(x) {
    Countries <- c("abc","def","ghi")
    for (val in Countries) {
        result <- df %>% filter(COUNTRY.NAME == val)
        write.xlsx(result, 
                   file = "result.xlsx",
                   sheetName = val, 
                   append = TRUE)
    }
}

If you prefer using pipes you can connect filter and write.xlsx like this:

df %>% 
    filter(COUNTRY.NAME == val) %>%
    write.xlsx(file = "result.xlsx", sheetName = val, append = TRUE)

---

Added append = TRUE (appended to an existing file).

Answer 2

Easily done tith library(XLConnect)

## load the libraries
library(XLConnect)
library(dplyr)

## constract a dummy data.frame
CountryNames <- c('abc','def','ghi')
SomeData <- c(21000, 23400, 26800)
SomeDate <- as.Date(c('2010-11-1','2008-3-25','2007-3-14'))
df <- data.frame(CountryNames, SomeData, SomeDate)


# Create Excel File
MyExcelFile <- XLConnect::loadWorkbook("Countries_Data.xlsx", create = TRUE)


sumstats<-function(x) {
  Countries <- c("abc","def","ghi")
  for (val in Countries) {
    createSheet(MyExcelFile, val) #create a sheet and name it with country name 
    result <- df %>% filter(CountryNames == val) # filter results
    writeWorksheet(MyExcelFile, result, sheet = val, startRow = 1, startCol = 1) # write results

  }
  saveWorkbook(MyExcelFile) # close/write the Excel after finishing the loop
}

# run the function to check
sumstats()