R read excel file numeric precision problem
0
I have a number in an excel file that is equal to -29998,1500000003
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
asked Nov 20 '18 at 18:48
gpiergpier
1,0871513
|
show 8 more comments
0
I have a number in an excel file that is equal to -29998,1500000003
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
asked Nov 20 '18 at 18:48
gpiergpier
1,0871513
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
tryoptions(digits=13)
– jay.sf
Nov 20 '18 at 18:55
1
Your question aboutas.characteris 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.characterand only rely on the first 15 digits.
– joran
Nov 20 '18 at 19:45
|
show 8 more comments
0
0
0
I have a number in an excel file that is equal to -29998,1500000003
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
asked Nov 20 '18 at 18:48
gpiergpier
1,0871513
I have a number in an excel file that is equal to -29998,1500000003
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
r excel openxlsx
asked Nov 20 '18 at 18:48
gpiergpier
1,0871513
asked Nov 20 '18 at 18:48
gpiergpier
1,0871513
edited Nov 20 '18 at 19:40
gpier
asked Nov 20 '18 at 18:48
gpiergpier
1,0871513
asked Nov 20 '18 at 18:48
gpiergpier
1,0871513
asked Nov 20 '18 at 18:48
gpiergpier
1,0871513
1,0871513
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
tryoptions(digits=13)
– jay.sf
Nov 20 '18 at 18:55
1
Your question aboutas.characteris 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.characterand 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
tryoptions(digits=13)
– jay.sf
Nov 20 '18 at 18:55
1
Your question aboutas.characteris 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.characterand only rely on the first 15 digits.
– joran
Nov 20 '18 at 19:45
4
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
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
1
try
options(digits=13)– jay.sf
Nov 20 '18 at 18:55
try
options(digits=13)– jay.sf
Nov 20 '18 at 18:55
1
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
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
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
@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
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
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
2
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 BolkerBen Bolker
133k11223311
Ok so I am not crazy. Are you sure that the373part is correct and not the37252903? 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');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
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 BolkerBen Bolker
133k11223311
Ok so I am not crazy. Are you sure that the373part is correct and not the37252903? 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 |
2
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 BolkerBen Bolker
133k11223311
Ok so I am not crazy. Are you sure that the373part is correct and not the37252903? 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 |
2
2
2
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 BolkerBen Bolker
133k11223311
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 BolkerBen Bolker
133k11223311
answered Nov 20 '18 at 19:27
Ben BolkerBen Bolker
133k11223311
answered Nov 20 '18 at 19:27
Ben BolkerBen Bolker
133k11223311
answered Nov 20 '18 at 19:27
Ben BolkerBen Bolker
133k11223311
133k11223311
Ok so I am not crazy. Are you sure that the373part is correct and not the37252903? 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 the373part is correct and not the37252903? 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
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');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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.characteris 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.characterand only rely on the first 15 digits.– joran
Nov 20 '18 at 19:45