2

openxlsx in its current version 4.2.4 overwrites digits precision from default 7 to 22. This results in unexpected behavior, if you try to write a lazy evaluating data.frame using writeData.

library(openxlsx)
library(magrittr)
library(dplyr)

wb <- createWorkbook()
sheet <- addWorksheet(wb,"Sheet")
data.frame(a=55.7) %>% mutate(a=a%>%format(nsmall=1)) %>% writeData(wb,sheet,.)
saveWorkbook(wb,"test.xlsx")

You may see, that the workbook contains not the expected string "55.7", but more decimals. This is because in global environment format works with 7 digits, so format(55.7)=="55.7", but within writeData it evaluates as

format(55.7)=="55.700000000000003"

This is not a issue of floating-point math, but an issue of changing environments. format uses as a default value "digits=getOption("digits")". Default is 7, within writeData it's 22. That's all there is, nothin special about that.

I wanted to know, why format(55.7) does not get evaluated within the context of global environment.

Cody Gray - on strike
  • 239,200
  • 50
  • 490
  • 574
Tobias
  • 141
  • 12
  • 1
    This is not an issue of displaying or formatting. I looked into the XML of these files and the numbers `55.70000000000000284217` and `57.7` were written by the R function. – danlooo Sep 29 '21 at 14:43
  • 1
    this is almost-certainly a case of IEE 754 floating point aliasing; see [Is floating point math broken?](https://stackoverflow.com/questions/588004/is-floating-point-math-broken) – ti7 Sep 29 '21 at 15:01
  • Yeah, obviously, but this is not the point. My question is, how/why/where the character gets casted to a floating point again. Please note the edit and see the correct string "55.7" in the data.frame. – Tobias Sep 29 '21 at 15:19

2 Answers2

3

I guess it's a feature (or bug) of openxlsx:

  • The R objects were identical
  • This is not an issue of displaying or formatting. I looked into the XML of these files and the numbers 55.70000000000000284217 and 57.7 were written by the R function
  • Replacing openxlsx with writexl eliminates this discrepancy
danlooo
  • 10,067
  • 2
  • 8
  • 22
  • 1
    Is openxlsx deprecated? Switching to another library will be a lot of work. – Tobias Sep 29 '21 at 16:05
  • @TobiasHofmann No it isn't, you may want to [address the author](https://github.com/ycphs/openxlsx/issues). – jay.sf Sep 29 '21 at 16:30
-1

The expression is lazily evaluated with the digits options in the current environment. openxlsx::writeData somewhere uses 17 digits, whereas the default value is 7.

library(magrittr)

f <- function(x) {
  rlang::scoped_options(digits=17)
  x$a
}
data.frame(a=format(1234.1)) %>% f()

> 1234.0999999999999

df <- data.frame(a=format(1234.1))
df %>% f()

> 1234.1

Edit: Developers from openxlsx confirmed the behaviour. They solved it by forcing the evaluation of their input data.frame before changing any environment variables in their fix.

Here is the link to my next question which shows the exact same unexpected behavior, but without using numbers.

Tobias
  • 141
  • 12