Moving NA in data frame in R

Question

I have a data frame like this:

df <- structure(list(a = c(NA, NA, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L), b = c(NA, NA, NA, 1L, 2L, 3L, 4L, 5L, 6L, 7L), d = c(NA, NA, NA, NA, 1L, 2L, 3L, 4L, 5L, 6L)), .Names = c("a", "b", "d"), row.names = c(NA, -10L), class = "data.frame") > df abd 1 NA NA NA 2 NA NA NA 3 1 NA NA 4 2 1 NA 5 3 2 1 6 4 3 2 7 5 4 3 8 6 5 4 9 7 6 5 10 8 7 6 

I would like to move each column and move NA to the bottom of the data frame:

 > df.out abd 1 1 1 1 2 2 2 2 3 3 3 3 4 4 4 4 5 5 5 5 6 6 6 6 7 7 7 NA 8 8 NA NA 9 NA NA NA 10 NA NA NA 

Refresh to make my questions clearer.

 df <- structure(list(a = c(NA, NA, 1, 5, 34, 7, 3, 5, 8, 4), b = c(NA, NA, NA, 57, 2, 7, 9, 5, 12, 100), d = c(NA, NA, NA, NA, 5, 7, 2, 8, 2, 5)), .Names = c("a", "b", "d"), row.names = c(NA, -10L ), class = "data.frame") > df abd 1 NA NA NA 2 NA NA NA 3 1 NA NA 4 5 57 NA 5 34 2 5 6 7 7 7 7 3 9 2 8 5 5 8 9 8 12 2 10 4 100 5 

should get:

  abd 1 1 57 5 2 5 2 7 3 34 7 2 4 7 9 8 5 3 5 2 6 5 12 5 7 8 100 NA 8 4 NA NA 9 NA NA NA 10 NA NA NA 

Sounds like an easy task, but I'm stuck on where to start .. Can you help?

Answer 1

After a complete misunderstanding of the question, here is my final answer:

 # named after beetroot for being the first to ever need this functionality beetroot <- function(x) { # count NA num.na <- sum(is.na(x)) # remove NA x <- x[!is.na(x)] # glue the number of NAs at the end x <- c(x, rep(NA, num.na)) return(x) } # apply beetroot over each column in the dataframe as.data.frame(lapply(df, beetroot)) 

It will count NA, remove NA, and glue NAs at the bottom for each column in the data frame.

Answer 2

Another solution using lapply (without sorting / reordering data for your comments)

 df[] <- lapply(df, function(x) c(x[!is.na(x)], x[is.na(x)])) df # abd # 1 1 57 5 # 2 5 2 7 # 3 34 7 2 # 4 7 9 8 # 5 3 5 2 # 6 5 12 5 # 7 8 100 NA # 8 4 NA NA # 9 NA NA NA # 10 NA NA NA 

Or using data.table to update df by reference, and not create a copy of it (this solution will not sort your data)

 library(data.table) setDT(df)[, names(df) := lapply(.SD, function(x) c(x[!is.na(x)], x[is.na(x)]))] df # abd # 1: 1 57 5 # 2: 5 2 7 # 3: 34 7 2 # 4: 7 9 8 # 5: 3 5 2 # 6: 5 12 5 # 7: 8 100 NA # 8: 4 NA NA # 9: NA NA NA # 10: NA NA NA 

In some tests, the basic solution is the fastest:

 library("microbenchmark") david <- function() lapply(df, function(x) c(x[!is.na(x)], x[is.na(x)])) dt <- setDT(df) david.dt <- function() dt[, names(dt) := lapply(.SD, function(x) c(x[!is.na(x)], x[is.na(x)]))] microbenchmark(as.data.frame(lapply(df, beetroot)), david(), david.dt()) # Unit: microseconds # expr min lq median uq max neval # as.data.frame(lapply(df, beetroot)) 1145.224 1215.253 1274.417 1334.7870 4028.507 100 # david() 116.515 127.382 140.965 149.7185 308.493 100 # david.dt() 3087.335 3247.920 3330.627 3415.1460 6464.447 100 

Answer 3

For fun, you can also use length<- and na.omit .

Here is what this combination will do:

 x <- c(NA, 1, 2, 3) x # [1] NA 1 2 3 `length<-`(na.omit(x), length(x)) # [1] 1 2 3 NA 

For your problem, the solution would look like this:

 df[] <- lapply(df, function(x) `length<-`(na.omit(x), nrow(df))) df # abd # 1 1 57 5 # 2 5 2 7 # 3 34 7 2 # 4 7 9 8 # 5 3 5 2 # 6 5 12 5 # 7 8 100 NA # 8 4 NA NA # 9 NA NA NA # 10 NA NA NA 

Answer 4

If you have a small number of columns, I suggest:

 data.frame( a=sort(example$a, na.last=T), b=sort(example$b, na.last=T), d=sort(example$d, na.last=T)) 

Best, Adii _