r expand dataset with filled in data

Question

I have a dataset with 4 columns. This can take 2 values for now.(1 or 0). There are three columns (X1, X2, X3) with missing data.

Row#    X1   X2   X3    Y
1       1    0    0     1
2       0    1    1     0 
3       NA   0    0     0
4       1    1    1     0
5       1    NA   NA    1
6       1    0    0     1
7       NA   NA   NA    0
8       0    1    0     1
9       NA   NA   1     0
10      0    0    1     1
11      NA   NA   0     0
12      0    0    0     0
13      0    0    1     1
14      NA   0    NA    0

What i am interested in creating an filled in dataset for possible values of NA. What i mean by this is creating possible rows of data like this below

Row#   X1  X2  X3  Y  Probability  Comments
1      1   0   0   1  1            No missing
2      0   1   1   0  1            No missing

3      1   0   0   0  0.5          X1 Missing
4      0   0   0   0  0.5          X1 Missing

5      1   1   1   0  1            No missing

6      1   1   0   1  0.25         X1, X2 missing 
7      1   0   0   1  0.25         X1, X2 missing 
8      1   1   1   1  0.25         X1, X2 missing 
9      1   0   1   1  0.25         X1, X2 missing 

10     1   0   0   1  1            No missing 

11     0   0   0   0  0.125        X1, X2, x3 missing 
12     1   0   0   0  0.125        X1, X2, x3 missing
13     0   1   0   0  0.125        X1, X2, x3 missing
14     0   0   1   0  0.125        X1, X2, x3 missing
15     1   1   0   0  0.125        X1, X2, x3 missing
16     1   0   1   0  0.125        X1, X2, x3 missing
17     0   1   1   0  0.125        X1, X2, x3 missing
18     1   1   1   0  0.125        X1, X2, x3 missing
19     .   .   .   .  .            ......
20     .   .   .   .  .            ......
21     .   .   .   .  .            ......
22     .   .   .   .  .            ......

Please note the final dataset will contain 5 columns (X1,X2,X3,Y,Possibility)

The column Probability is calculated based on this logic.

Let us start with Row1&2 in 1st dataset . The first two rows(1,2) dont have any missing data so in the expected output the same two rows are generated and the probability is 1.
Let us look at Row3 in the original dataset. This has missing value in column X1. So two rows 3,4 are generated in the expected output. Hence probability 0.5, 0.5. 1/2=0.5
Let us look at the 5th rows in the original dataset. This contains missing data in column X2 and X3. So this will generate 4 rows in Expected data, row# 6,7,8.9. So probability is 0.25, 0.25,0.25, 0.25, 1/4 = 0.25
Row7 of the original dataset has missing values for x1,x2,x3. So this scenario will generate 8 rows, row 11- row 18 in the expected output dataset. Hence the probability 0.125 , 1/8 = 0.125

I could do this using 8 ifelse statements and for loops. But I am wondering if there is any esier and less messy way to achieve this. Thanks.

user_1910845 · Answer

I defined a function for combinations of {0,1} for any numbers using expand.grid. For n equal to 0, I used a data.frame with 1 dimension to avoid complications for rows without NA.

comb <- function(n) { 
    if(n==0) return(data.frame(Var1 = c(1)))
    expand.grid(rep(list(0:1),n)) 
}

Now I am using apply and replace function to create list of rows. I have used mutate from dplyr to create probability column.

df = apply(df, 1, function(v){
  NA_count = length(which(is.na(v)))
  apply(comb(NA_count) , 1 ,  FUN = replace , x = v, list = 
  which(is.na(v))) %>% 
  t %>% as.data.frame() %>%
  mutate( Probability = (1/2)^length(which(is.na(v))))
})

Finally, I put all the lists together with do.call.

do.call(rbind,df)

user_3460670 · Answer

This could be simplified - but let me know if this is closer to what you need.

There are 2 functions here:

One function make_mat(x, k) creates a vector containing 0 and 1 (e.g., make_mat(7,4) is 0 1 1 1 which is 4 bits long and the binary is equal to 7)
The second function sub_mat will create a matrix of 2^n_repl rows, where n_repl is the number of NA to replace.

A list is created for each row in the original data frame. Right now, the number of rows num_row is computed for the list, and the probability is set to 1/num_row.

make_mat <- function(x, k) {
  return(rev(as.integer(intToBits(x))[1:k]))
}

sub_mat <- function(x) {
  n_repl <- sum(+(is.na(x)))
  mat_repl <- t(sapply(1:2^n_repl-1, make_mat, k = n_repl))
  new_mat <- matrix(rep(x, 2^n_repl), ncol = length(x), byrow = T)
  new_mat[is.na(new_mat)] <- mat_repl
  new_mat
}

lst <- apply(df, 1, sub_mat)
num_row <- sapply(lst, nrow, simplify = T)
result <- as.data.frame(Reduce(rbind, Map(cbind, lst, 1/num_row)))
names(result) <- c(names(df), "probability")
result

Output

   X1 X2 X3 Y probability
1   1  0  0 1       1.000
2   0  1  1 0       1.000
3   0  0  0 0       0.500
4   1  0  0 0       0.500
5   1  1  1 0       1.000
6   1  0  0 1       0.250
7   1  0  1 1       0.250
8   1  1  0 1       0.250
9   1  1  1 1       0.250
10  1  0  0 1       1.000
11  0  0  0 0       0.125
12  0  0  1 0       0.125
13  0  1  0 0       0.125
14  0  1  1 0       0.125
15  1  0  0 0       0.125
16  1  0  1 0       0.125
17  1  1  0 0       0.125
18  1  1  1 0       0.125
19  0  1  0 1       1.000
20  0  0  1 0       0.250
21  0  1  1 0       0.250
22  1  0  1 0       0.250
23  1  1  1 0       0.250
24  0  0  1 1       1.000
25  0  0  0 0       0.250
26  0  1  0 0       0.250
27  1  0  0 0       0.250
28  1  1  0 0       0.250
29  0  0  0 0       1.000
30  0  0  1 1       1.000
31  0  0  0 0       0.250
32  0  0  1 0       0.250
33  1  0  0 0       0.250
34  1  0  1 0       0.250

r expand dataset with filled in data

2 Answers