Shift values in columns up if there are missing values above

  • A+
Category:Languages

I have a data frame like this:

df <- data.frame(id = c("A", "A", "A", "A", "A", "A", "A", "A",                      "B", "B", "B", "B", "B", "B"),              var1 = c("100", "200", "300", NA, NA, NA, NA, NA,                       "100", "200", "300", NA, NA, NA),               var2 = c("100", NA, NA, "400", "500", "600", NA, NA,                       NA, NA, NA, "400", NA, NA),              var3 = c("200", NA, NA, NA, NA, NA, "700", "800",                       "500", NA, NA, NA, "500", "600")) 

which looks like this:

  id var1 var2 var3    A  100  100  200    A  200 <NA> <NA>    A  300 <NA> <NA>    A <NA>  400 <NA>    A <NA>  500 <NA>    A <NA>  600 <NA>    A <NA> <NA>  700    A <NA> <NA>  800    B  100 <NA>  500    B  200 <NA> <NA>    B  300 <NA> <NA>    B <NA>  400 <NA>    B <NA> <NA>  500    B <NA> <NA>  600 

I would like to shift values in columns up if there are missing values above (by group). The result should look like this:

  id var1 var2 var3    A  100  100  200    A  200  400  700    A  300  500  800    A <NA>  600 <NA>    B  100  400  500    B  200 <NA>  500    B  300 <NA>  600 

I have no idea how to do this. Any thoughts?

 


Here is a rough concept using data.table that can be refined:

library(data.table) # Helper function: shift_up <- function(x) {   n <- length(x)   x <- x[!is.na(x)]   length(x) <- n   x }  setDT(df) df[, lapply(.SD, shift_up), id][!(is.na(var1) & is.na(var2) & is.na(var3))]     id var1 var2 var3 1:  A  100  100  200 2:  A  200  400  700 3:  A  300  500  800 4:  A <NA>  600 <NA> 5:  B  100  400  500 6:  B  200 <NA>  500 7:  B  300 <NA>  600 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: