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
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
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))
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)]
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