Convert string to logical in R with sparklyr

Question

I have 100 million lines stored in many CSV files in a distributed file system. I use spark_read_csv () to load data without any problems. Many of my columns are stored in the form of symbolic logic values: "true", "false", "<na>". I do not control this.

When I try to convert values ​​to logical, the values ​​are "<na>"converted to FALSEwith values "false". Any thoughts on how to overcome this?

test_lgl <- 
  tibble(a = c(TRUE, TRUE, NA, NA, FALSE, FALSE),
         b = c("true", "true", "na", "<na>", "false", "f"))

test_lgl %>% mutate_if(is.character, as.logical)

# this works
   a     b
  <lgl> <lgl>
1  TRUE  TRUE
2  TRUE  TRUE
3    NA    NA
4    NA    NA
5 FALSE FALSE
6 FALSE    NA

sc <- spark_connect(master = "local")
spark_lgl <- copy_to(sc, test_lgl)

spark_lgl %>% mutate_if(is.character, as.logical)

# this does not
      a     b
  <lgl> <lgl>
1  TRUE  TRUE
2  TRUE  TRUE
3 FALSE FALSE
4 FALSE FALSE
5 FALSE FALSE
6 FALSE FALSE

Answer 1

When I try to convert values ​​to logical, the values ​​are "<na>"converted toFALSE

Surprisingly, no. If you additionally check the result:

spark_lgl_boolean <- spark_lgl %>% mutate_if(is.character, as.logical)
spark_lgl_boolean %>% mutate_all(is.na)

Applying predicate on the first 100 rows
# Source:   lazy query [?? x 2]
# Database: spark_connection
      a     b
  <lgl> <lgl>
1 FALSE FALSE
2 FALSE FALSE
3  TRUE  TRUE
4  TRUE  TRUE
5 FALSE FALSE
6 FALSE FALSE

NA count:

spark_lgl_boolean %>%
  mutate_all(is.na) %>% 
  mutate_all(as.numeric) %>%
  summarize_all(sum)

# Source:   lazy query [?? x 2]
# Database: spark_connection
      a     b
  <dbl> <dbl>
1     2     2

:

spark_lgl %>% mutate_if(is.character, as.logical) %>% optimizedPlan

Applying predicate on the first 100 rows
<jobj[1074]>
  org.apache.spark.sql.catalyst.plans.logical.Project
  Project [a#10, cast(b#11 as boolean) AS b#2037]
+- InMemoryRelation [a#10, b#11], true, 10000, StorageLevel(disk, memory, deserialized, 1 replicas), `test_lgl`
      +- *FileScan csv [a#10,b#11] Batched: false, Format: CSV, Location: InMemoryFileIndex[file:/tmp/..., PartitionFilters: [], PushedFilters: [], ReadSchema: struct<a:boolean,b:string>

StringType → BooleanType :

• TRUE/T ( ) 1 TRUE .
• FALSE/F ( ) 0 FALSE .
• , , NULL (~ NA).

scala> Seq("tRUE", "FALSE", "f", "<na>", "NA", "1", "0").toDF("x").select($"x".cast("boolean")).show
+-----+
|    x|
+-----+
| true|
|false|
|false|
| null|
| null|
| true|
|false|
+-----+

, , sparklyr. . GitHub ( kevinykuo ).

, , , R, (, ).

spark_read_csv()

nullValue nanValue options

spark_read_csv(..., options=list(nanValue="<na>"))

, Spark NULL/NaN R NA/NaN.