0

I expected to get an output of cleaned data in 4 sheets and each with 9 columns. Original data looks like this:

enter image description here

but only output the first column in the first sheet:

enter image description here

my codes are as below:

library(readxl)
library(xlsx)
library(triangle)
library(dplyr)
library(Matrix)
library(qpcR)
library(ggplot2)

sheetlist <- as.list(1,2,3,4)
columnlist <- as.list(1,2,3,4,11,12,13,14,15)
output <- data.frame(matrix(nrow = 800))
for (i in sheetlist){
    page <- read.xlsx(
        "C:\\Users\\WangShiqin\\Desktop\\data all required.xlsx", 
        i, header = TRUE)
    for (j in columnlist){
        variable <- page %>%  dplyr::select(all_of(j))
        outliers <- boxplot(variable, plot = FALSE)$out
        cleaned <- variable[-c(which(variable[,1] %in% outliers)), ]
        output <- qpcR:::cbind.na(output, cleaned)
    }
    if (i<2){
        write.xlsx(output, 
"C:\\Users\\WangShiqin\\desktop\\cleaned.xlsx")
    }
    else{
        write.xlsx(output, 
"C:\\Users\\WangShiqin\\desktop\\cleaned.xlsx",
        sheetindex = i, append = TRUE)
    }
}

but it only generates the cleaned result of the first column in first sheet

Phil
  • 7,287
  • 3
  • 36
  • 66
Kira
  • 1
  • 1
  • It doesn't look like the `write.xlsx` function has a `sheetindex=` property https://www.rdocumentation.org/packages/xlsx/versions/0.6.5/topics/write.xlsx. Try `sheetName=as.character(i)` – MrFlick Mar 01 '23 at 04:34
  • my bad. change as.list() to list() everything is normal again – Kira Mar 03 '23 at 11:06

0 Answers0