0

I'm trying to in put multiple sheets of excel using for loop in R and do some impuations. The code which i have written is below,

sheet <- getSheetNames("market_latest.xlsx")
for (i in 2:length(sheet)-1){
data[i] <- read.xlsx("market_latest.xlsx",sheet = sheet[i],fillMergedCells = TRUE)
col[i] <- colnames(data[i])
colnames(data[i]) <- as.character(unlist(data[i][1,]))
col[i+1] <- colnames(data[i])
colnames(data[i]) <- as.character(unlist(data[i][1,]))
data[i] <- data[i][, !duplicated(colnames(data[i]))] 
write.xlsx(data[i],file="sampleop.xlsx",sheetName="Sheet1", 
           col.names=TRUE, row.names=FALSE, append=FALSE)
}

The imputation part is working so i have no problem with it. But when i ran the code its throwing me warnings but no output. I want to use 2 sheet to n-1 sheet of excel, please do let me know where im wrong. The error pic is added below. enter image description here

Aditya hande
  • 1
  • 1
  • Welcome to SO! This question is lacking a *lot* of information, so it's unlikely to get anything fruitful for you, Aditya hande. You said: *"it's throwing me warnings but no output"* ... perhaps you can *share* those warnings with us? They almost certainly indicative or related to whatever problem you might be having. Lacking that, we have no warnings, no errors, and no sample data, so it's unlikely that anybody will be able to give you much. Refs: https://stackoverflow.com/q/5963269, [mcve], and https://stackoverflow.com/tags/r/info. – r2evans Sep 18 '20 at 03:21
  • `:` takes precedence over `-`. Change `for (i in 2:length(sheet)-1)` to `for (i in 2:(length(sheet)-1))`. Check the difference between `2:5 - 1` and `2:(5 - 1)` for an illustration. – Gregor Thomas Sep 18 '20 at 03:22
  • @GregorThomas Hi thanks for the input, i have tried it but its throwing error. The error is ``` object of type 'closure' is not subsettable. please see the error i have added in the question. – Aditya hande Sep 19 '20 at 07:37
  • That's a common error when you use a function name like a variable. Like, `mean` is a function name, and if I do `mean[1]` that error will show up because I can't su bset a function. But if I say `mean = 1:3` first, so there is a non-function mean, then `mean[1]` works. So probably when you do `data[i]`, you don't have an object named `data`, so R tries to use the built-in function `data()` instead. Maybe put a `data = list()` before the start of your loop to make a data object. You also should change `data[i]` to `data[[i]]` - use double brackets to access single elements of lists. – Gregor Thomas Sep 19 '20 at 16:28
  • @GregorThomas Thanks alot it did help :). I have added my answer too. – Aditya hande Sep 20 '20 at 08:46

1 Answers1

0

#import all the excel sheets into a list

lst <- lapply(2:10, function(i) read.xlsx("file.xlsx", sheet = i, fillMergedCells = TRUE))

#Use for loop to remove/impute the data in required format

for(i in 1:9){
  col <- colnames(lst[[i]])
  colnames(lst[[i]]) <- as.character(unlist(lst[[i]][1,]))
  assign(paste0("head_",i),colnames(lst[[i]]))
  lst[[i]] <- lst[[i]][-1,]
  colnames(lst[[i]]) <- as.character(unlist(lst[[i]][1,]))
  lst[[i]] <- lst[[i]][, !duplicated(colnames(lst[[i]]))]
}
Aditya hande
  • 1
  • 1