[R] how to enforce type conversion in open_dataset()

[asfimport]

Here is a small example:

``

library(arrow)
df_numbers <- tibble::tibble(number = c(1,2,3,"error", 4, 5, NA, 6))
str(df_numbers)
#> tibble [8 x 1] (S3: tbl_df/tbl/data.frame)
#>  $ number: chr [1:8] "1" "2" "3" "error" ...
write_parquet(df_numbers, "numbers.parquet")
open_dataset("numbers.parquet") 
#> FileSystemDataset with 1 Parquet file
#> number: string
open_dataset("numbers.parquet", schema(number = int8())) |> dplyr::collect()
#> Error in `dplyr::collect()`:
#> ! Invalid: Failed to parse string: 'error' as a scalar of type int8

The expected result is having an input column of integers; where the non-integer values are converted to NAs.

How this type conversion can be enforced using schema definition in in the  {}open_dataset(){}? 

Rationale: I would like to include this in a code chunk  which imports a csv dataset and saves to parquet dataset (open_dataset -> write_dataset); where the type conversion based on a preset schema would be done at the same time.  And all these steps without loading all the data in memory.

Reporter: Zsolt Kegyes-Brassai / @kbzsl

_{Note: This issue was originally created as ARROW-16833. Please see the migration documentation for further details.}

[asfimport]

Nicola Crane / @thisisnic:
Hi @kbzsl.  In Arrow, strings and ints are not compatible types to cast between, which is why the example you give there won't work.  However, given the aim is to read from a CSV file and output as Parquet, you can provide the schema and the "null" specifier when you read in the CSV like so:

 

library(arrow)

df_numbers <- tibble::tibble(number = c(1,2,3,"error", 4, 5, NA, 6))

write_csv_arrow(df_numbers, "numbers.csv")

open_dataset("numbers.csv", format = "csv") |> 
  dplyr::collect()
#> # A tibble: 7 × 1
#>   number
#>   <chr> 
#> 1 1     
#> 2 2     
#> 3 3     
#> 4 error 
#> 5 4     
#> 6 5     
#> 7 6

open_dataset(
  "numbers.csv",
  format = "csv",
  null_values = c(NA, "error"),
  schema = schema(number = int8()),
  skip = 1
  ) |> 
  dplyr::collect()
#> # A tibble: 7 × 1
#>   number
#>    <int>
#> 1      1
#> 2      2
#> 3      3
#> 4     NA
#> 5      4
#> 6      5
#> 7      6

 

[asfimport]

Zsolt Kegyes-Brassai / @kbzsl:
Hi @thisisnic, thank you for the quick answer.

I spot a strange behavior in the results of your code: the NA value was dropped silently. In my view this is a totally wrong. Do you agree? Shall I register a new ticket?

I am afraid that the convert_options won’t be the right solution, because it is not feasible to list all the erroneous items from a large and real dataset. 

I tried to solve with a mutate() function, but it’s not working either.

 

library(arrow)
df_numbers <- tibble::tibble(number = c(1,2,3,"error", 4, 5, NA, 6))
readr::write_csv(df_numbers, "/temp/test/numbers.csv")
open_dataset("/temp/test", format = "csv") |> 
  dplyr::mutate(number =  as.integer(number)) |> 
  write_dataset(here::here("/temp/test_ds"), format = "parquet")
#> Error: Invalid: Failed to parse string: 'NA' as a scalar of type int32

 

``

Let’s share my view, ideas for improvement. The arrow package has the very powerful promise that much larger data sizes can processed on an ordinary computer/laptop by using the datasets. Because the real data is usually messy, some flexible tools/options would  be desired which can deal with data cleaning (= type conversion) and column selection/renaming inside an open_dataset() -> write_dataset() code chunk. 

[asfimport]

Nicola Crane / @thisisnic:
@kbzsl Yeah, please do open a ticket for that one.

I don't disagree, have opened a ticket to enable this at the C++ layer: ARROW-16862

[github-actions]

This issue has been marked as stale because it has had no activity in the past 365 days. Please remove the stale label or comment below, or this issue will be closed in 14 days. If this improvement is still desired but has no current owner, please add the 'Status: needs champion' label.