[R] read_xlsx(readxl) apparently mangling some data input
peter dalgaard
pd@|gd @end|ng |rom gm@||@com
Tue Feb 4 14:01:58 CET 2020
Excel itself will store numeric data as numeric unless you explicitly say that they are not. I.e., 9.13 gest stored in floating point, with the innate binary rounding issues which you can also see from R
> print(9.13, digits=20)
[1] 9.1300000000000007816
> print(1.14, digits=20)
[1] 1.1399999999999999023
This happens on a per-cell basis inside Excel, and the excess digits are probably retained in .xlsx files (which, as far as I recall, are text-based, XML format files). LibreOffice probably keeps fewer significant digits.
So read_xlsx just reads what is in the file, and if instructed to read as text, gets you the result that you see. There seems to be no way so set a tolerance for rounding (which would also logically be a peculiar thing to do as it requires char -> num -> char conversion in ways that may or may not be what the user wants)
Pragmatically, I think you need to jump through a few hoops, something like
x <- tbl$NOWARN
xn <- as.numeric(x)
ix <- !(is.na(xn)) & xn%%1 != 0
x[ix] <- as.character(xn[ix])
(possibly throw in a zapsmall(), caveat emptor). Then proceed as per the original plan.
-pd
> On 4 Feb 2020, at 13:07 , Chris Evans <chrishold using psyctc.org> wrote:
>
> This is a very odd error I'm hitting using read_xlsx from the readxl package (version 1.3.1) with R version 3.6.2 (2019-12-12) , platform x86_64-pc-linux-gnu (and updated Ubuntu 18.04). I have some largeish Excel spreadsheets that contain clinical data. I can't share the entire raw data but I think I can share the specific problem columns as Excel files, but not via the list as I'm sure it rightly rejects such attachments.
>
> The particular column contains entries like
> 1
> 1, 14
>
> 1.14
>
> That's to say it's a column that can have empty cells, or entries which should be integers (a limited range of them) but cells may have multiple integers and the data entry means that people use various separators, commas, full stops and occasionally semi-colons or colons and all with or without various amounts of space.
>
> I thought this would be easy to handle but this illustrates the issue I'm hitting:
>
>> unique(read_xlsx("Book1.xlsx", col_types = "text"))
> # A tibble: 18 x 1
> NOWARN
> <chr>
> 1 NA
> 2 14
> 3 8,12,14
> 4 13
> 5 58
> 6 9
> 7 9.1300000000000008
> 8 11
> 9 11.14
> 10 10
> 11 10.14
> 12 9.14
> 13 13.14
> 14 9 ,13
> 15 9.11
> 16 1
> 17 1.1399999999999999
> 18 1, 14
>
> That's reading from a single column, 981 row (including column header) Excel xlsx file in an up to date Windoze 10 Professional running in a VM on the Ubuntu machine.
>
> I created that file (which I can share) by copying the data from the full file to a new Excel spreadsheet (M$ Orifice "Professional Plus 2019" "Version 1912" "Build 12325.20344 Click-to-run" to an empty new Excel file and using the default save_as. The clinical data files were created in, and updated in, versions of Excel that I can't access but the file was certainly created first between two years and three months before now so probably with different versions of Excel and probably in a Spanish or Catalan M$ locale.
>
> The weird thing is that looking at the Excel cells that created those "9.1300000000000008" and "1.1399999999999999" entries they show "9.13" and "1.14" (respectively!). They continue to show those values plus many trailing zeroes if I use Excel formatting to ask for 20 decimal places (I get less of course, but no arbitrary terminal rounding digit).
>
> It appears to me that read_xlxs() is only applying the "col_types = "text"" argument _after_ reading the column freely, reading each cell guessing the type by its contents and so ending up with numeric values for "9.13" and "1.14" which are then picking up rounding errors and being forced to character after that. I say that the reading would appear to be free across all cells in the column as there are entries of "8, 12, 14" coming before these problem entries:
>
>> tmp <- read_xlsx("Book1.xlsx", col_types = "text")
>> grep("1.1399999999999999", tmp$NOWARN, fixed = TRUE)
> [1] 932 948 954
>> grep("9.1300000000000008", tmp$NOWARN, fixed = TRUE)
> [1] 73 189 190 271 272 390 511 645 686 710 744 830 899
>> tmp$NOWARN[20]
> [1] "8,12,14"
>
> This seems completely bizarre to me. I find it very hard to believe that read_xlsx() would guess content class (type) freely by for each individual entry and only apply the col_types argument after doing that as that would seem likely to be incredibly inefficient for really big spreadsheets. It seems equally hard to believe that it would then create rounding errors (for some guessed numerics like 9.13 and 1.14 but not for others like 11.4). However, my guess would appear to fit the results and I am only guessing because I'm sure my programming comprehension isn't good enough to read into the sources to actually work out how the function works.
>
> To make things more interesting, and to suggest that at least some of the problem is with Excel is that when I use LibreOffice (in Ubuntu) created a Excel file in the same way, i.e. open the clinical Excel file but in LibreOffice, copy and paste the same column into a new LibreOffice calc spreadsheet and save as xlsx, tmp.xlsx, I get this:
>
>> unique(read_xlsx("tmp.xlsx", col_types = "text"))
> # A tibble: 18 x 1
> NOWARN
> <chr>
> 1 NA
> 2 14
> 3 8,12,14
> 4 13
> 5 58
> 6 9
> 7 9.13
> 8 11
> 9 11.14
> 10 10
> 11 10.14
> 12 9.14
> 13 13.14
> 14 9 ,13
> 15 9.11
> 16 1
> 17 1.14
> 18 1, 14
>
> Exactly what I think I should be seeing. I was working in Rstudio but get exactly the same in a new R terminal session with only readxl loaded so I don't think this is any weird environment or other clash.
>
> Obviously I can, though not terribly easily for a fully generic fix, catch these weird rounding errors and correct them, I am sure can also report this as a suspected bug to the maintainer through the github issues system but I wanted to check here whether anyone could see something I'm missing as I'm really a (clinically retired) therapist and doctor, now full time researcher and I'm not a professional statistician or programmer.
>
> TIA,
>
> Chris
>
>
>
> --
> Chris Evans <chris using psyctc.org> Visiting Professor, University of Sheffield <chris.evans using sheffield.ac.uk>
> I do some consultation work for the University of Roehampton <chris.evans using roehampton.ac.uk> and other places
> but <chris using psyctc.org> remains my main Email address. I have a work web site at:
> https://www.psyctc.org/psyctc/
> and a site I manage for CORE and CORE system trust at:
> http://www.coresystemtrust.org.uk/
> I have "semigrated" to France, see:
> https://www.psyctc.org/pelerinage2016/semigrating-to-france/
> That page will also take you to my blog which started with earlier joys in France and Spain!
>
> If you want to book to talk, I am trying to keep that to Thursdays and my diary is at:
> https://www.psyctc.org/pelerinage2016/ceworkdiary/
> Beware: French time, generally an hour ahead of UK.
>
> ______________________________________________
> R-help using r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
--
Peter Dalgaard, Professor,
Center for Statistics, Copenhagen Business School
Solbjerg Plads 3, 2000 Frederiksberg, Denmark
Phone: (+45)38153501
Office: A 4.23
Email: pd.mes using cbs.dk Priv: PDalgd using gmail.com
More information about the R-help
mailing list