CODEWITHSUNDEEP
rtidyrreadxl
buggaby
buggaby

Reputation: 441

How to set tibble column types programmatically

I have a tibble from reading a tall XLSX file using:

> file = readxl::read_xlsx(filename, "sheetname")

An toy example avoiding an actual XLSX file:

> file = tibble(
+     names = c("name1", "name2", "name3"),
+     values = c(TRUE, 1, "chr")
+ )
> file
# A tibble: 3 x 2
  names values
  <chr> <chr> 
1 name1 TRUE  
2 name2 1     
3 name3 chr

I want to convert it into this:

# A tibble: 1 x 3
  name1 name2 name3
  <dbl> <lgl> <chr>
1     1 TRUE  chr

but because pivot_wider() determines that the values column is of type <chr>, pivot_wider() keeps that type for all widened columns.

> file %>% pivot_wider(names_from = names, values_from = values)
# A tibble: 1 x 3
  name1 name2 name3
  <chr> <chr> <chr>
1 TRUE  1     chr

This requires me to manually set each column type again. Is there another (automated) way? The format of this file is fixed, but the contents might change so I can't rely on hardcoded type setting. Ideally for me, we could do something like

readxl::read_xlsx(filename, "sheetname") %>%
  pivot_wider(names_from = column1, values_from = column2, col_types = NULL)

Upvotes: 3

Views: 595

Answers (1)

akrun
akrun

Reputation: 887148

If we wrap with type.convert, it would automatically change the type

library(dplyr)
library(tidyr)
file %>% 
  pivot_wider(names_from = names, values_from = values) %>% 
  type.convert(as.is = TRUE)
# A tibble: 1 x 3
#  name1 name2 name3
#  <lgl> <int> <chr>
#1 TRUE      1 chr

or use deframe/as_tibble_row, convert to tibble

library(tibble)
deframe(file) %>% 
   as_tibble_row %>% 
   type.convert(as.is = TRUE)

Another option is data.table::transpose

type.convert(data.table::transpose(file, make.names = 'names'), as.is = TRUE)
#  name1 name2 name3
#1  TRUE     1   chr

data

file <- structure(list(names = c("name1", "name2", "name3"), values = c("TRUE", 
"1", "chr")), row.names = c(NA, -3L), class = c("tbl_df", "tbl", 
"data.frame"))

Upvotes: 2

Related Questions