Geek Answers Handbook

R openxlsx. writing NA as spaces?

I am using the openxlsx package to read and write Excel files.

I noticed that when I export a table to Excel using write.xlsx (MyData, file = "MyFile.xlsx") NA are displayed as #NUM! when a file is opened in Excel.

Is it possible to export it as spaces? I use googling and tried options like showNA or keepNA, but they don't seem to have any effect.

+5
source share
2 answers

I do not understand the downward. I thought that some people would know how to do this using only the option, without the need to manually convert all NA.

I think this can be important and useful for other users, which I also asked on the openxlsx github development page, and they answered:

https://github.com/awalker89/openxlsx/issues/108#issuecomment-125142950

If anyone is interested if you upgrade the dev version with

  devtools::install_github("awalker89/openxlsx")

You will get support for this feature.

the default behavior now converts NA to spaces. And if we want to have # N / A, we must use the keepNA = TRUE option; the keepNA = TRUE option saves # N / A

 require('openxlsx') df <- head(iris) df[2,3] <- NA df[2,5] <- NA df[3,5] <- NaN openXL(write.xlsx(df, file = tempdir()))

enter image description here

  require('openxlsx') df <- head(iris) df[2,3] <- NA df[2,5] <- NA df[3,5] <- NaN openXL(write.xlsx(df, file = tempdir(), keepNA = TRUE))

enter image description here

+2
source

Good question! I also watched. I believe that the default behavior of IS displays NA values ​​as spaces. However, as shown in your code example, you have the values NA and NaN . The presence of one or more NaN values ​​in a column will lead to all NA and NaN in this column, displayed in Excel as #NUM! .

To fix this, replace all NaN instances in your NA file before exporting the file:

df[is.na(df)] = NA

Note the use of is.na , not is.nan , when x is data.frame.

See: How to replace the value of NaN with zero in a huge data frame?

+1
source

More articles:


All Articles