Rearrange a matrix: collapsing columns to remove NAs

Question

After careful searches I still can't solve my problem, here it is. I have a matrix with many NAs like this one:

matrix(c(NA,NA,1,NA,NA,-3,  -1,NA,NA,NA,NA,NA,  NA,2,NA,NA,NA,NA,  NA,3,NA,NA,-2,5,  NA,NA,NA,NA,NA,NA,  NA,NA,NA,6,-7,NA),ncol=6,nrow = 6)

which produces the following

     [,1] [,2] [,3] [,4] [,5] [,6]
[1,]   NA   -1   NA   NA   NA   NA
[2,]   NA   NA    2    3   NA   NA
[3,]    1   NA   NA   NA   NA   NA
[4,]   NA   NA   NA   NA   NA    6
[5,]   NA   NA   NA   -2   NA   -7
[6,]   -3   NA   NA    5   NA   NA

My objective is to collapse the matrix to have: 1) the number of rows resulting from shifting all the values upwards to get rid of the NAs; 2) same number of columns. In this case the resulting matrix would be 3X6, as in no case there are more than 3 values for each column. This is the matrix I am trying to obtain:

     [,1] [,2] [,3] [,4] [,5] [,6]
[1,]    1   -1    2    3   NA    6
[2,]   -3   -1    2   -2   NA   -7
[3,]    1   -1    2    5   NA    6

1) the columns with a single value repeat it; 2) the columns with two values have the second value the second time they appear, while when they appear the third they assume the first value again (and eventually maintain it for additional rows); 3) the column with three values change it every time. The column with all NAs can remain the same.

A complete solution would be great, but I am fine with getting hints about the way to proceed with this type of complex restructuring.

G. Grothendieck G. Grothendieck · Accepted Answer · 2016-01-17T20:35:18

Assuming that m is the input matrix, find the max number of non-NAs in any column and then apply rep_len to the non-NAs in each column to that length:

mx <- max(colSums(!is.na(m)))
apply(m, 2, function(x) rep_len(na.omit(x), mx))

giving:

     [,1] [,2] [,3] [,4] [,5] [,6]
[1,]    1   -1    2    3   NA    6
[2,]   -3   -1    2   -2   NA   -7
[3,]    1   -1    2    5   NA    6

Rearrange a matrix: collapsing columns to remove NAs

1 Answers