18

I read data stored in the format DECIMAL from a MySQL-Table. I want to do calculations on those numbers within R.

I used to cast them to a numeri represaentation using as.numeric(), but the documentation says:

numeric is identical to double (and real).

But is there also a datatype Decimal in R? (Datatype without rounding errors,...)

Here a simple example for the problem with rounding errors:

numbersStrings = c("0.1", "0.9")
numbersNumeric = as.numeric(numbersStrings)
numbersMirror  = c(numbersNumeric, 1-numbersNumeric)

str(numbersMirror)

numbersMirror
unique(numbersMirror)  # has two times 0.1 ...

sprintf("%.25f", numbersMirror)
sprintf("%.25f", unique(numbersMirror))  # ... because there was a rounding error
R_User
  • 10,682
  • 25
  • 79
  • 120
  • This may be useful: http://cran.r-project.org/web/packages/Rmpfr/index.html it's a library to perform arbitrary-precision floating point operations in R. – Ferdinand.kraft Jun 06 '13 at 20:30
  • The MySQL documentation makes clear that DECIMAL is not arbitrary precision. It does allow more digits (64) than can be represented in a 'double' (53). It also makes clear that truncation is expected if the digits to the right of the decimal point exceed the chosen limit. I suspect that truncation to a particular limit on doubles could meet most needs. – IRTFM Jun 07 '13 at 02:32
  • Nice example. I solved a similar problem by using `table()` (here you could use `table(numbersMirror)`). table seems to round the numbers. I have no details on how it rounds, but for your example it seems to work. Anyway: a decimal datatype should be more precise. (of course only for -/+ operations). @DWin: I think you meant `round()` instead of truncate. – John Garreth Jun 10 '13 at 10:48
  • @DWin, `Rmpfr` can be set to use 64 bits. I wrote "arbitrary precision" but I meant "multiple precision". :-) There is no way to achieve that in "plain R" using `double`s only. – Ferdinand.kraft Jun 10 '13 at 18:57
  • It's going to be a PITA to manage since you will need to bring the "numbers" in as character representation from MySQL and then convert to Rmpfr and then convert back to character and then write code in MySQL to convert the characters to DECIMAL. – IRTFM Jun 10 '13 at 19:17

2 Answers2

4

You could create your own:

d <- structure( list(i=589L,exp=2L), class="decimal" )
print.decimal <- function( x, ...) print( x$i * 10^(-x$exp) )
> d
[1] 5.89

Actually, some of the big number packages may work for this as well, since they use a similar representation....

Ari B. Friedman
  • 71,271
  • 35
  • 175
  • 235
  • and then you only need to write methods for the basic arithmetic operations and the non-basic ones you need... (but, great answer) – Henrik Jun 06 '13 at 12:34
  • 1
    @Henrik I agree. It's a pain, for sure. The precision payoff had better be worth it for your particular application :-) – Ari B. Friedman Jun 06 '13 at 13:38
4

Similar approach to Ari answer but using integer64 class from bit64 package. Using big int as underlying data type for decimal is common practice in various applications that doesn't support decimal type natively. 

library(bit64)

as.decimal = function(x, p=2L) structure(as.integer64(x*10^p), class="decimal", precision=p)
print.decimal = function(x) print(as.double(x))
as.integer64.decimal = function(x) structure(x, class="integer64", precision=NULL) # this should not be exported
as.double.decimal = function(x) as.integer64(x)/(10^attr(x, "precision"))
is.decimal = function(x) inherits(x, "decimal")
"==.decimal" = function(e1, e2) `==`(as.integer64(e1), as.integer64(e2))
"+.decimal" = function(e1, e2) `+`(as.integer64(e1), as.integer64(e2))

d = as.decimal(12.69)
is.decimal(d)
#[1] TRUE
print(d)
#[1] 12.69
as.double(d)
#[1] 12.69
d + as.decimal(0.9)
#[1] 13.59
0.1 + 0.2 == 0.3
#[1] FALSE
as.decimal(0.1) + as.decimal(0.2) == as.decimal(0.3)
#[1] TRUE
jangorecki
  • 16,384
  • 4
  • 79
  • 160
  • Thanks for providing this template! I've used it to create a makeshift decimal data type that I used for some simple calculations in a report (where someone might check by hand). Very useful :) The package can be found here [fixeddecimal](https://github.com/micabo/fixeddecimal) if anyone needs a little more inspiration. – micabo Dec 03 '22 at 12:55