R read excel file numeric precision problem

I have a number in an excel file that is equal to -29998,1500000003

excelfile

When I try to open it in R I get

> library(openxlsx)

> posotest <- as.character(read.xlsx("sofile.xlsx"))

> posotest

[1] "-29998.1500000004"

Any help? Desired result: -29998,1500000003

EDIT: with options(digits=13) I get -29998.150000000373 which could explain why the rounding is done, however even with options(digits=13) I get

> as.character(posotest)

[1] "-29998.1500000004"

Do you have any function that would allow me to get the full number in characters?

EDIT2 format does this but it adds artificial noise at the end.

x <- -29998.150000000373

format(x,digits=22)

[1] "-29998.15000000037252903"

How can I know how many digits to use in format since nchar will give me a wrong value?

The file is here

edited Nov 20 '18 at 19:40

asked Nov 20 '18 at 18:48

gpier

1,0861513

4

It reads in fine for me if I don't convert it to character. Once I force R to show all the digits, the value is -29998.150000000373. Which of course suggests that Excel is lying to you more than R is.

– joran
Nov 20 '18 at 18:54

1

try options(digits=13)

– jay.sf
Nov 20 '18 at 18:55

1

Your question about as.character is answered in the documentation: "as.character represents real and complex numbers to 15 significant digits (technically the compiler's setting of the ISO C constant DBL_DIG, which will be 15 on machines supporting IEC60559 arithmetic according to the C99 standard). This ensures that all the digits in the result will be reliable (and not the result of representation error), but does mean that conversion to character and back to numeric may change the number. If you want to convert numbers to character with the maximum possible precision, use format."

– joran
Nov 20 '18 at 19:35

2

@gpier "How can I know in advance how many digits are correct and display those only?" As a general matter, that is an incredibly complex question. How many digits are "reliable" will depend on all sorts of things, like what values were used as inputs and the specific sequence of arithmetic steps that led to the result. Unless you're in a highly specialized field, most people simply rely on software defaults, like R's choice to show only -29998.15, and only more when forced to.

– joran
Nov 20 '18 at 19:42

1

For instance, unless you know enough about floating point arithmetic to actually start second guessing compiler standards, a good rule of thumb might be to follow R's documentation in ?as.character and only rely on the first 15 digits.

– joran
Nov 20 '18 at 19:45

|
show 8 more comments

I have a number in an excel file that is equal to -29998,1500000003

excelfile

When I try to open it in R I get

> library(openxlsx)

> posotest <- as.character(read.xlsx("sofile.xlsx"))

> posotest

[1] "-29998.1500000004"

Any help? Desired result: -29998,1500000003

EDIT: with options(digits=13) I get -29998.150000000373 which could explain why the rounding is done, however even with options(digits=13) I get

> as.character(posotest)

[1] "-29998.1500000004"

Do you have any function that would allow me to get the full number in characters?

EDIT2 format does this but it adds artificial noise at the end.

x <- -29998.150000000373

format(x,digits=22)

[1] "-29998.15000000037252903"

How can I know how many digits to use in format since nchar will give me a wrong value?

The file is here

edited Nov 20 '18 at 19:40

asked Nov 20 '18 at 18:48

gpier

1,0861513

4

It reads in fine for me if I don't convert it to character. Once I force R to show all the digits, the value is -29998.150000000373. Which of course suggests that Excel is lying to you more than R is.

– joran
Nov 20 '18 at 18:54

1

try options(digits=13)

– jay.sf
Nov 20 '18 at 18:55

1

Your question about as.character is answered in the documentation: "as.character represents real and complex numbers to 15 significant digits (technically the compiler's setting of the ISO C constant DBL_DIG, which will be 15 on machines supporting IEC60559 arithmetic according to the C99 standard). This ensures that all the digits in the result will be reliable (and not the result of representation error), but does mean that conversion to character and back to numeric may change the number. If you want to convert numbers to character with the maximum possible precision, use format."

– joran
Nov 20 '18 at 19:35

2

@gpier "How can I know in advance how many digits are correct and display those only?" As a general matter, that is an incredibly complex question. How many digits are "reliable" will depend on all sorts of things, like what values were used as inputs and the specific sequence of arithmetic steps that led to the result. Unless you're in a highly specialized field, most people simply rely on software defaults, like R's choice to show only -29998.15, and only more when forced to.

– joran
Nov 20 '18 at 19:42

1

For instance, unless you know enough about floating point arithmetic to actually start second guessing compiler standards, a good rule of thumb might be to follow R's documentation in ?as.character and only rely on the first 15 digits.

– joran
Nov 20 '18 at 19:45

|
show 8 more comments

I have a number in an excel file that is equal to -29998,1500000003

excelfile

When I try to open it in R I get

> library(openxlsx)

> posotest <- as.character(read.xlsx("sofile.xlsx"))

> posotest

[1] "-29998.1500000004"

Any help? Desired result: -29998,1500000003

EDIT: with options(digits=13) I get -29998.150000000373 which could explain why the rounding is done, however even with options(digits=13) I get

> as.character(posotest)

[1] "-29998.1500000004"

Do you have any function that would allow me to get the full number in characters?

EDIT2 format does this but it adds artificial noise at the end.

x <- -29998.150000000373

format(x,digits=22)

[1] "-29998.15000000037252903"

How can I know how many digits to use in format since nchar will give me a wrong value?

The file is here

edited Nov 20 '18 at 19:40

asked Nov 20 '18 at 18:48

gpier

1,0861513

I have a number in an excel file that is equal to -29998,1500000003

excelfile

When I try to open it in R I get

> library(openxlsx)

> posotest <- as.character(read.xlsx("sofile.xlsx"))

> posotest

[1] "-29998.1500000004"

Any help? Desired result: -29998,1500000003

EDIT: with options(digits=13) I get -29998.150000000373 which could explain why the rounding is done, however even with options(digits=13) I get

> as.character(posotest)

[1] "-29998.1500000004"

Do you have any function that would allow me to get the full number in characters?

EDIT2 format does this but it adds artificial noise at the end.

x <- -29998.150000000373

format(x,digits=22)

[1] "-29998.15000000037252903"

How can I know how many digits to use in format since nchar will give me a wrong value?

The file is here

r excel openxlsx

edited Nov 20 '18 at 19:40

asked Nov 20 '18 at 18:48

gpier

1,0861513

edited Nov 20 '18 at 19:40

asked Nov 20 '18 at 18:48

gpier

1,0861513

edited Nov 20 '18 at 19:40

asked Nov 20 '18 at 18:48

gpier

1,0861513

asked Nov 20 '18 at 18:48

gpier

1,0861513

asked Nov 20 '18 at 18:48

gpier

1,0861513

4

It reads in fine for me if I don't convert it to character. Once I force R to show all the digits, the value is -29998.150000000373. Which of course suggests that Excel is lying to you more than R is.

– joran
Nov 20 '18 at 18:54

1

try options(digits=13)

– jay.sf
Nov 20 '18 at 18:55

1

Your question about as.character is answered in the documentation: "as.character represents real and complex numbers to 15 significant digits (technically the compiler's setting of the ISO C constant DBL_DIG, which will be 15 on machines supporting IEC60559 arithmetic according to the C99 standard). This ensures that all the digits in the result will be reliable (and not the result of representation error), but does mean that conversion to character and back to numeric may change the number. If you want to convert numbers to character with the maximum possible precision, use format."

– joran
Nov 20 '18 at 19:35

2

@gpier "How can I know in advance how many digits are correct and display those only?" As a general matter, that is an incredibly complex question. How many digits are "reliable" will depend on all sorts of things, like what values were used as inputs and the specific sequence of arithmetic steps that led to the result. Unless you're in a highly specialized field, most people simply rely on software defaults, like R's choice to show only -29998.15, and only more when forced to.

– joran
Nov 20 '18 at 19:42

1

For instance, unless you know enough about floating point arithmetic to actually start second guessing compiler standards, a good rule of thumb might be to follow R's documentation in ?as.character and only rely on the first 15 digits.

– joran
Nov 20 '18 at 19:45

|
show 8 more comments

4

It reads in fine for me if I don't convert it to character. Once I force R to show all the digits, the value is -29998.150000000373. Which of course suggests that Excel is lying to you more than R is.

– joran
Nov 20 '18 at 18:54

1

try options(digits=13)

– jay.sf
Nov 20 '18 at 18:55

1

Your question about as.character is answered in the documentation: "as.character represents real and complex numbers to 15 significant digits (technically the compiler's setting of the ISO C constant DBL_DIG, which will be 15 on machines supporting IEC60559 arithmetic according to the C99 standard). This ensures that all the digits in the result will be reliable (and not the result of representation error), but does mean that conversion to character and back to numeric may change the number. If you want to convert numbers to character with the maximum possible precision, use format."

– joran
Nov 20 '18 at 19:35

2

@gpier "How can I know in advance how many digits are correct and display those only?" As a general matter, that is an incredibly complex question. How many digits are "reliable" will depend on all sorts of things, like what values were used as inputs and the specific sequence of arithmetic steps that led to the result. Unless you're in a highly specialized field, most people simply rely on software defaults, like R's choice to show only -29998.15, and only more when forced to.

– joran
Nov 20 '18 at 19:42

1

For instance, unless you know enough about floating point arithmetic to actually start second guessing compiler standards, a good rule of thumb might be to follow R's documentation in ?as.character and only rely on the first 15 digits.

– joran
Nov 20 '18 at 19:45

It reads in fine for me if I don't convert it to character. Once I force R to show all the digits, the value is -29998.150000000373. Which of course suggests that Excel is lying to you more than R is.

– joran
Nov 20 '18 at 18:54

try options(digits=13)

– jay.sf
Nov 20 '18 at 18:55

Your question about as.character is answered in the documentation: "as.character represents real and complex numbers to 15 significant digits (technically the compiler's setting of the ISO C constant DBL_DIG, which will be 15 on machines supporting IEC60559 arithmetic according to the C99 standard). This ensures that all the digits in the result will be reliable (and not the result of representation error), but does mean that conversion to character and back to numeric may change the number. If you want to convert numbers to character with the maximum possible precision, use format."

– joran
Nov 20 '18 at 19:35

@gpier "How can I know in advance how many digits are correct and display those only?" As a general matter, that is an incredibly complex question. How many digits are "reliable" will depend on all sorts of things, like what values were used as inputs and the specific sequence of arithmetic steps that led to the result. Unless you're in a highly specialized field, most people simply rely on software defaults, like R's choice to show only -29998.15, and only more when forced to.

– joran
Nov 20 '18 at 19:42

For instance, unless you know enough about floating point arithmetic to actually start second guessing compiler standards, a good rule of thumb might be to follow R's documentation in ?as.character and only rely on the first 15 digits.

– joran
Nov 20 '18 at 19:45

|
show 8 more comments

1 Answer
1

active

oldest

votes

You can get a string with up to 22 digits of precision via format():

x <- -29998.150000000373

format(x,digits=22)

[1] "-29998.15000000037252903"

Of course, this will show you all sorts of ugliness related to trying to represent a decimal number in a binary representation with finite precision ...

answered Nov 20 '18 at 19:27

Ben Bolker

133k12224313

Ok so I am not crazy. Are you sure that the 373 part is correct and not the 37252903? How can I know in advance how many digits are correct and display those only?

– gpier
Nov 20 '18 at 19:31

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53399596%2fr-read-excel-file-numeric-precision-problem%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

You can get a string with up to 22 digits of precision via format():

x <- -29998.150000000373

format(x,digits=22)

[1] "-29998.15000000037252903"

Of course, this will show you all sorts of ugliness related to trying to represent a decimal number in a binary representation with finite precision ...

answered Nov 20 '18 at 19:27

Ben Bolker

133k12224313

Ok so I am not crazy. Are you sure that the 373 part is correct and not the 37252903? How can I know in advance how many digits are correct and display those only?

– gpier
Nov 20 '18 at 19:31

add a comment |

You can get a string with up to 22 digits of precision via format():

x <- -29998.150000000373

format(x,digits=22)

[1] "-29998.15000000037252903"

Of course, this will show you all sorts of ugliness related to trying to represent a decimal number in a binary representation with finite precision ...

answered Nov 20 '18 at 19:27

Ben Bolker

133k12224313

Ok so I am not crazy. Are you sure that the 373 part is correct and not the 37252903? How can I know in advance how many digits are correct and display those only?

– gpier
Nov 20 '18 at 19:31

add a comment |

You can get a string with up to 22 digits of precision via format():

x <- -29998.150000000373

format(x,digits=22)

[1] "-29998.15000000037252903"

Of course, this will show you all sorts of ugliness related to trying to represent a decimal number in a binary representation with finite precision ...

answered Nov 20 '18 at 19:27

Ben Bolker

133k12224313

You can get a string with up to 22 digits of precision via format():

x <- -29998.150000000373

format(x,digits=22)

[1] "-29998.15000000037252903"

Of course, this will show you all sorts of ugliness related to trying to represent a decimal number in a binary representation with finite precision ...

answered Nov 20 '18 at 19:27

Ben Bolker

133k12224313

answered Nov 20 '18 at 19:27

Ben Bolker

133k12224313

answered Nov 20 '18 at 19:27

Ben Bolker

133k12224313

answered Nov 20 '18 at 19:27

Ben Bolker

133k12224313

Ok so I am not crazy. Are you sure that the 373 part is correct and not the 37252903? How can I know in advance how many digits are correct and display those only?

– gpier
Nov 20 '18 at 19:31

add a comment |

Ok so I am not crazy. Are you sure that the 373 part is correct and not the 37252903? How can I know in advance how many digits are correct and display those only?

– gpier
Nov 20 '18 at 19:31

Ok so I am not crazy. Are you sure that the 373 part is correct and not the 37252903? How can I know in advance how many digits are correct and display those only?

– gpier
Nov 20 '18 at 19:31

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu