How shift data in all columns in R data frame?-CodePudding

I have the following R data.frame

df <- data.table(
  id = c(1, 2, 3),
  x = c(1, NA, NA),
  y = c(NA, 2, NA),
  z = c(NA, NA, 3))

And, I'd like to shift all data up from the "y" and all next columns without changing values order. The number of columns is variable. The final data set should be the following:

df_result <- data.table(
  id = c(1, 2, 3),
  x = c(1, NA, NA),
  y = c(2, NA, NA),
  z = c(3, NA, NA))

Please, help if you can!

Thanks

CodePudding user response：

You can use order to move the non-NA values up without altering the order:

sapply(df, \(x) x[order(!is.na(x), decreasing = T)])
     id  x  y  z
[1,]  1  1  3  3
[2,]  2 NA  2 NA
[3,]  3 NA NA NA

data:

df <- data.frame(
  id = c(1, 2, 3),
  x = c(1, NA, NA),
  y = c(3, 2, NA),
  z = c(NA, NA, 3))

CodePudding user response：

One option is to order on the NA elements - convert columns to logical with is.na - returns TRUE where there are NAs and FALSE where it is non-NA, when we order on that T comes after F in alphabetic order and will be last, use that index to reorder

library(data.table)
df[, (names(df)[-1]) := lapply(.SD, function(x)
     x[order(is.na(x))]), .SDcols = -1]

-output

> df
      id     x     y     z
   <num> <num> <num> <num>
1:     1     1     2     3
2:     2    NA    NA    NA
3:     3    NA    NA    NA

CodePudding user response：

This will move the values up, without changing the order

# Helper function
f <- function(x)  c(x[!is.na(x)], x[is.na(x)])

# Apply to each column
df[,(names(df)):=lapply(.SD,f)]

@Maël shows that a better approach is calling is.na() only once, like this:

f <- function(x)  x[order(!is.na(x),decreasing=T)]

CodePudding user response：

Another way to solve your probelm:

library(data.table)

df[, Map(`[`, .SD, lapply(.SD,  \(x) order(as.logical(x))))]

      id     x     y     z
   <num> <num> <num> <num>
1:     1     1     2     3
2:     2    NA    NA    NA
3:     3    NA    NA    NA