0

How do I shift the cells in a data table TO THE RIGHT by the number of NA in each row in R?

Example Data:

data <- data.table(c1=c("a","e","h","j"),
                   c2=c("b","f","i",NA),
                   c3=c("c","g",NA,NA),
                   c4=c("d",NA,NA,NA), stringsAsFactors = F)
  c1   c2   c3   c4
1  a    b    c    d
2  e    f    g <NA>
3  h    i <NA> <NA>
4  j <NA> <NA> <NA>

Desired Data from example:

data.desired <- data.table(
                  c1=c("a",NA,NA,NA),
                   c2=c("b","e",NA,NA),
                   c3=c("c","f","h",NA),
                   c4=c("d","g","i","j"), stringsAsFactors = F)
    c1   c2   c3 c4
1    a    b    c  d
2 <NA>    e    f  g
3 <NA> <NA>    h  i
4 <NA> <NA> <NA>  j
Neal Barsch
  • 2,810
  • 2
  • 13
  • 39
  • This is opposite of [Shifting non-NA cells to the left](https://stackoverflow.com/questions/23285215/shifting-non-na-cells-to-the-left) but most of the answers there would work in this case as well. – Ronak Shah Nov 29 '18 at 06:21

2 Answers2

3

Here's one attempt using matrix indexing and a counter of NA values by row:

#convert back to a data.frame to take advantage of matrix indexing
setDF(data)   

arr <- which(!is.na(data), arr.ind=TRUE)
arr[,"col"] <- arr[,"col"] + rowSums(is.na(data))[arr[,"row"]]
out <- data
out[] <- NA
out[arr] <- data[!is.na(data)]

out
#    c1   c2   c3 c4
#1    a    b    c  d
#2 <NA>    e    f  g
#3 <NA> <NA>    h  i
#4 <NA> <NA> <NA>  j

#convert to data.table if necessary
setDT(out)

This option is pretty quick and from a brief test churns through 4 columns / 2 million rows in about 3-4 seconds.

thelatemail
  • 91,185
  • 12
  • 128
  • 188
2

We can use

data.table(t(apply(data, 1, function(x){ c(rep(NA, sum(is.na(x))), x[!is.na(x)])})))
#      V1   V2   V3 V4
# 1:    a    b    c  d
# 2: <NA>    e    f  g
# 3: <NA> <NA>    h  i
# 4: <NA> <NA> <NA>  j
dww
  • 30,425
  • 5
  • 68
  • 111