get all decimal places using readxlsb and cellranger::cell_limits()

user_1305688 0
Viewed 140

I’m importing some messy data from a range of Excel Binary Workbooks (.xlsb) using readxlsb and cell_limits(), from cellranger. I'm struggling to get enough (all) decimal places.

This can be illustrated with the dataset that is supplied with the readxlsb package. In the example data, TestBook.xlsb, in sheet Sheet3.1.1, cell E5. This cell contains e^1, with a range of underlying decimal places (2,71828182845905), but is only imported with six decimal places (2.718282).

In my real life data I have text in a lot of the top lines, which convert the data to charters, like column.4 below, where E5 resides, and raw data with ~16 decimal places. Is there a way I can tweak the code (below) to get all the decimal places without loosing cellranger::cell_limits()?

# install.packages(c("readxlsb", "tidyverse"), dependencies = TRUE) 
library(readxlsb); library(tidyverse)

as_tibble(
  read_xlsb(path = system.file("extdata", "TestBook.xlsb", package = "readxlsb"), 
            sheet = "Sheet3.1.1",range = cellranger::cell_limits())
)

# A tibble: 5 x 7
  Some       column.2 column.3 column.4   column.5 column.6 column.7
  <date>     <chr>    <chr>    <chr>      <chr>    <chr>       <dbl>
1 NA         "data"   ""       "2.718282" ""       ""           3.14
2 NA         ""       "in"     ""         ""       ""          NA   
3 2021-05-21 ""       ""       "a"        ""       ""          NA   
4 NA         ""       ""       ""         "third"  ""       43972   
5 NA         ""       ""       ""         ""       "sheet"     NA
tidyverse import r excel
1 Answers

A simple solution could be to force column types to double when importing, i.e. col_types = c("double").

Beginning by adjusting the shown digits in your tibble,

options(pillar.sigfig = 20)

Now, you get cell E5 with all the digits from Excel.

library(tidyverse); library(readxlsb)
as_tibble(
  read_xlsb(path = system.file("extdata", "TestBook.xlsb", package = "readxlsb"), 
            sheet = "Sheet3.1.1",col_types = c("double"), 
            range = cellranger::cell_limits())
           )
# A tibble: 5 x 7
   Some column.2 column.3             column.4 column.5 column.6             column.7
  <dbl>    <dbl>    <dbl>                <dbl>    <dbl>    <dbl>                <dbl>
1    NA       NA       NA  2.718281828459045e0       NA       NA  3.141592653589793e0
2    NA       NA       NA NA                         NA       NA NA                  
3 44337       NA       NA NA                         NA       NA NA                  
4    NA       NA       NA NA                         NA       NA  4.3972           e4
5    NA       NA       NA NA

According to read_xlsb's vignette the default is set to guessed col type, from the underlying data according to:

When implying types from the underlying spreadsheet data, the resultant type is the regarded as the ‘least fragile’.

Effectively the order is logical – datetime – integer – double – string

If 99 rows are of type ‘integer’ and 1 row is of type ‘double’, then all cells are regarded as ‘double’ in that column.
If 99 rows are of type ‘date’ and 1 row is of type ‘string’, then all cells are promoted to ‘string’

A possible tweak (circumventing the auto guessed col types), cave: will treat everything as characters

library(tidyverse)
library(readxlsb)

# read everything as character
as_tibble(
  read_xlsb(path = system.file("extdata", "TestBook.xlsb", package = "readxlsb"), 
            sheet = "Sheet3.1.1",col_types = c("character"), cellranger::cell_limits())
) ->test.char


# read everything as double
as_tibble(
  read_xlsb(path = system.file("extdata", "TestBook.xlsb", package = "readxlsb"), 
            sheet = "Sheet3.1.1",col_types = c("double"), cellranger::cell_limits())
) ->test.dbl

# make a function that checks if a string is a date
is.date <- function(x) inherits(x, 'Date')

# combine character and double, has to be adjusted according to your real data
cbind(test.char %>%
        gather(key.character,value=character),
      test.dbl %>%
        gather(key=key.numeric,value=numeric)) %>% 
  tibble() %>% 
  rowwise() %>% 
  mutate(numeric=case_when(is.date(try(as.Date(character),silent=TRUE))==TRUE ~ NA_real_, TRUE ~ numeric)) %>% #set double to NA if character is date
  mutate(character=case_when(!is.na(numeric)~as.character(numeric), TRUE ~ character)) %>% #keep all remaining double
  select(key.character,character) %>%
  pivot_wider(names_from = key.character, values_from = character) %>%
  unnest(cols = c(Some, column.2, column.3, column.4, column.5, column.6, column.7))
#> # A tibble: 5 x 7
#>   Some       column.2 column.3 column.4        column.5 column.6 column.7       
#>   <chr>      <chr>    <chr>    <chr>           <chr>    <chr>    <chr>          
#> 1 ""         "data"   ""       "2.71828182845~ ""       ""       "3.14159265358~
#> 2 ""         ""       "in"     ""              ""       ""       ""             
#> 3 "2019-08-~ ""       ""       "a"             ""       ""       ""             
#> 4 ""         ""       ""       ""              "third"  ""       "2018-08-25"   
#> 5 ""         ""       ""       ""              ""       "sheet"  ""

Created on 2021-05-24 by the reprex package (v2.0.0)

user_80615520
Related