How to use something similar to set (data.table) in sparklyr?

user_13373123 0
Viewed 85

I have the following spark data frame called "clients":

ID    VAR1   VAR2   VAR3
 1      A      F      A1
 2      C      M      B1
 3      E      F      C1
 4      C      0      B1

And I also have a dictionary of variables called "ref", which shows the unique values a variable can take, like this one:

Variables     Values
VAR1          A
VAR1          B
VAR1          C
VAR1          D
VAR1          E
VAR2          F
VAR2          M
VAR3          A1
VAR3          B1

I want to replace those values that are not allowed for variables, with a default value like "-1", so I can have something like this:

    ID    VAR1   VAR2   VAR3
     1      A      F      A1
     2      C      M      B1
     3      E      F      -1
     4      C     -1      B1

With data table library I would use something like this:

names <- colnames(clients)

for(var in names){

set(x = clients,
    i = which(!clients[[var]] %in% ref[Variables == var]$Values),
    j = var,
    value = "-1")

I would like to know if there exist some similar solution to use with sparklyr.

sparklyr r
2 Answers

I am not sure which functions are available for spark hence here is a base R option.

clients[-1] <- lapply(names(clients)[-1], function(x) {
                      replace(clients[[x]], 
                      !clients[[x]] %in% ref$Values[ref$Variables == x], NA)
                })

clients

#  ID VAR1 VAR2 VAR3
#1  1    A    F   A1
#2  2    C    M   B1
#3  3    E    F <NA>
#4  4    C <NA>   B1

I have replaced the values which are not allowed with NA instead of -1, you can use whichever value you want.

user_39629140

One approach, similar to your current code, is to collect the list of ref values for each VAR and apply case_when. If the value exist in the collected list, return the value of VAR, otherwise return -1.

Let's see an example. I suggest to always provide code that generates the test dataset:

clients <- data.frame(
  ID=c(1, 2, 3, 4),
  VAR1=c("A", "C", "E", "C"),
  VAR2=c("F", "M", "F", "0"),
  VAR3=c("A1", "B1", "C1", "B1"),
  stringsAsFactors = FALSE
)

ref <- data.frame(
  Variables = c("VAR1", "VAR1", "VAR1", "VAR1", "VAR1", "VAR2", "VAR2", "VAR3", "VAR3"),
  Values=c("A", "B", "C", "D", "E", "F", "M", "A1", "B1"),
  stringsAsFactors = FALSE
)

Start the session

library(sparklyr)
library(tidyverse)
sc <- spark_connect(master="local[4]", version = "3.0.1")

sref <- copy_to(sc, ref)
sclients <- copy_to(sc, clients, overwrite = T)

for(var in c("VAR1", "VAR2", "VAR3")) {
  var_values <- (sref %>% 
                   filter(Variables == var) %>% 
                   select(Values) %>% 
                   collect())$Values
  
  sclients <- sclients %>% 
    mutate(!!as.name(var) := case_when(!!as.name(var) %in% var_values ~ !!as.name(var),
                                       TRUE ~ "-1"))
}

sclients

Result

# Source: spark<?> [?? x 4]
     ID VAR1  VAR2  VAR3 
  <dbl> <chr> <chr> <chr>
1     1 A     F     A1   
2     2 C     M     B1   
3     3 E     F     -1   
4     4 C     -1    B1

Although this solution works, it may not be the best depending on how large your dataset is. For instance, if the values of ref for one VAR don't fit in memory, the collect will through an out of memory error. An alternative could be to generate an array column with the ref values and then check if the VAR value exist inside the array using a function like hof_exists()

user_3771180
Related