0

I wrote the write.xlsx command from the xlsx package when I open the excel, it shows N/A in cells. what can I do, doesn't show #N/A 

Data1 <- structure(list(PM10 = c("13-", "19", "25", "17", NA, "22"), Hourly_Average = c(NA_real_,  NA_real_, NA_real_, NA_real_, NA_real_, NA_real_)), row.names = c(NA,  6L), class = "data.frame")

write.xlsx(Data1, file = "PM10 Valid Stations.xlsx")
camille
  • 16,432
  • 18
  • 38
  • 60
  • Welcome to SO. Can you put `dput(head(Data1))` in the console and paste the results in your post? – s_baldur May 18 '21 at 13:11
  • What package are you using to get the `write.xlsx()` function? Sometimes there is an àrgument like `write.csv(..., na = "NA", ...)` that you can change to `na = "#N/A"` for example so you get `#N/A` in your final file. – Paul May 18 '21 at 13:17
  • Does this answer your question? [write.xlsx in R giving incorrect NA in cell](https://stackoverflow.com/questions/40125821/write-xlsx-in-r-giving-incorrect-na-in-cell) – Paul May 18 '21 at 13:20
  • `r dput(head(Data1)) structure(list(PM10 = c("13-", "19", "25", "17", NA, "22"), Hourly_Average = c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_)), row.names = c(NA, 6L), class = "data.frame")` @sindri_baldur –  May 18 '21 at 14:20
  • 1
    That's how Excel indicates a missing or invalid value. What exactly were you expecting to get instead? – camille May 18 '21 at 14:29
  • @Paul Package: xlsx Type: Package Version: 0.6.0 Date: 2015-11-29 License: GPL-3 –  May 18 '21 at 14:34
  • I expected that gives me the average value in excel. @camille –  May 18 '21 at 14:46
  • I run these codes `r PM10_Validation<- function() { Data = read.xlsx("pirozi95.xlsx", sheetIndex = "PM10", colIndex = 1, startRow = 1, endRow = 8785) Data[Data <=0] <- NA Data1 = Data[colMeans(is.na(Data))<0.25] Data1$Hourly_Average<-apply(Data1,1,mean,na.rm=TRUE) Hourly<-data.frame(Data1$Hourly_Average) Daily<-colMeans(matrix(Hourly[,1],nrow=24), na.rm=T) Daily<-data.frame(Daily) colnames(Daily)<-"Daily_Averages" write.xlsx(Data1, file = "PM10 Valid Stations.xlsx") }` –  May 18 '21 at 14:47
  • Within the snippet of data you posted, that column is only `NA`. Excel will denote that as `#N/A`. You should check that the data you're writing out *definitely* has values that aren't `NA` – camille May 18 '21 at 15:16

1 Answers1

0

I don't use xlsx package a lot but it seems there is a showNA argument in write.xlsx()

You can try:

write.xlsx(Data1, file = "PM10 Valid Stations.xlsx", showNA = FALSE)

I usually prefer using writexlor openxlsx packages:

writexl::write_xlsx(Data1, path = "PM10 Valid Stations.xlsx")
openxlsx::write.xlsx(Data1, file = "PM10 Valid Stations.xlsx")
jmpivette
  • 266
  • 1
  • 3
  • I wrote `showNA` and it could remove #N/A but I want to be valued in cells. may my cod be wrong? @jmpivette –  May 18 '21 at 15:02
  • Not sure I understand what you are trying to do. What value do you want to be displayed in your xlsx file? Can you maybe share an example of the expected output? – jmpivette May 18 '21 at 15:12
  • I want to give it to me hourly average of PM10 concentration in output but I don't know why it is #N/A instead of hourly average value in excel –  May 18 '21 at 15:22
  • It looks like your problem is not in the xlsx file but inside your data.frame `Data1`. There are no values in Hourly_Average only `NA` – jmpivette May 18 '21 at 15:30