How do I apply the same action to all Excel Files in the directory?

Question

I need to shape the data stored in Excel files and save it as new .csv files. I figured out what specific actions should be done, but can't understand how to use lapply.

All Excell files have the same structure. Each of the .csv files should have the name of original files.

## the original actions successfully performed on a single file   
library(readxl)

library("reshape2")

DataSource  <- read_excel("File1.xlsx", sheet = "Sheet10")

DataShaped <- melt(subset(DataSource [-(1),], select = - c(ng)), id.vars = c ("itemname","week"))

write.csv2(DataShaped, "C:/Users/Ol/Desktop/Meta/File1.csv")

## my attempt to apply to the rest of the files in the directory
lapply(Files, function (i){write.csv2((melt(subset(read_excel(i,sheet = "Sheet10")[-(1),], select = - c(ng)), id.vars = c ("itemname","week"))))})

R returns the result to the console but doesn't create any files. The result resembles .csv structure.

Could anybody explain what I am doing wrong? I'm new to R, I would be really grateful for the help

Answer

Thanks to the prompt answer from @Parfait the code is working! So glad. Here it is:

library(readxl)
library(reshape2)

Files <- list.files(full.names = TRUE)

lapply(Files, function(i) {
  write.csv2(
    melt(subset(read_excel(i, sheet = "Decomp_Val")[-(1),],
         select = -c(ng)),id.vars = c("itemname","week")),
    file = paste0(sub(".xlsx", ".csv",i)))
  })

It reads an Excel file in the directory, drops first row (but headers) and the column named "ng", melts the data by labels "itemname" and "week", writes the result as a .csv to the working directory attributing the name of the original file. And then - rinse and repeat.

score 0 · Accepted Answer · answered Feb 01 '19 at 17:10

Simply pass an actual file path to write.csv2. Otherwise, as denoted in docs ?write.csv, the default value for file argument is empty string "" :

file: either a character string naming a file or a connection open for writing. "" indicates output to the console.

Below concatenates the Excel file stem to the specified path directory with .csv extension:

path <- "C:/Users/Ol/Desktop/Meta/"

lapply(Files, function (i){ 
      write.csv2(
                 melt(subset(read_excel(i, sheet = "Sheet10")[-(1),], 
                             select = -c(ng)), 
                       id.vars = c("itemname","week")),
                 file = paste0(path, sub(".xlsx", ".csv", i))
                )
})

How do I apply the same action to all Excel Files in the directory?

Answer

1 Answers1