Suppose I have a workbook with some NAs in it:
df <- data.frame(a = 1:3, b = c(1, NA, NA), c = c(NA, 2, 3))
xlsx::write.xlsx(df, 'df.xlsx')
wb <- xlsx::loadWorkbook('df.xlsx')
I want the data in a workbook format so I can use xlsx's other functions to apply styling. However, when I write it out it shows #N/A in all the blank cells.
xlsx::saveWorkbook(wb, file = 'df_out.xlsx')
Most functions for writing out data let you disable this somehow, but xlsx::saveWorkbook doesn't have such an argument. Is there some other way to do hide NAs in the output?
All you need to do is add the option showNA = FALSE to your write.xlsx statement:
# Make dummy data frame
df <- data.frame(a = 1:3, b = c(1, NA, NA), c = c(NA, 2, 3))
# Save without NA's
xlsx::write.xlsx(df, 'df.xlsx', showNA = FALSE)
# Load as a workbook
wb <- xlsx::loadWorkbook('df.xlsx')
# Save as a workbook
xlsx::saveWorkbook(wb, file = 'df_out.xlsx')
HOWEVER, please read below before proceeding:
I understand how annoying NA's can be, but please be aware that they are intentionally annoying for a reason.
NA means missing, so, depending on what you are analyzing, saving them as empty values is not always the best idea.
If, for example, for a particular site your monthly temperature values for July every year are missing (e.g., because someone is away for vacation each year), then calculating an average yearly temperature by simply ignoring July would be incorrect.