CODEWITHSUNDEEP
rdplyrtidyr
Alexander
Alexander

Reputation: 4635

Shifting rows up in columns and flush remaining ones

I have a problem with moving the rows to one upper row. When the rows become completely NA I would like to flush those rows (see the pic below). My current approach for this solution however still keeping the second rows.

Here is my approach 

  data <- data.frame(gr=c(rep(1:3,each=2)),A=c(1,NA,2,NA,4,NA), B=c(NA,1,NA,3,NA,7),C=c(1,NA,4,NA,5,NA))

> data
  gr  A  B  C
1  1  1 NA  1
2  1 NA  1 NA
3  2  2 NA  4
4  2 NA  3 NA
5  3  4 NA  5
6  3 NA  7 NA

enter image description here

so using this approach 

data.frame(apply(data,2,function(x){x[complete.cases(x)]}))

  gr A B C
1  1 1 1 1
2  1 2 3 4
3  2 4 7 5
4  2 1 1 1
5  3 2 3 4
6  3 4 7 5

As we can see still I am having the second rows in each group!

The expected output

> data
      gr  A  B  C
    1  1  1  1  1
    2  2  2  3  4
    3  3  4  7  5

thanks!

Upvotes: 1

Views: 93

Answers (3)

sm925
sm925

Reputation: 2678

One more solution using data.table:-

data <- data.frame(gr=c(rep(1:3,each=2)),A=c(1,NA,2,NA,4,NA), B=c(NA,1,NA,3,NA,7),C=c(1,NA,4,NA,5,NA))

library(data.table)
library(zoo)
setDT(data)
data[, A := na.locf(A), by = gr]
data[, B := na.locf(B), by = gr]
data[, C := na.locf(C), by = gr]
data <- unique(data)
data




 gr A B C
1:  1 1 1 1
2:  2 2 3 4
3:  3 4 7 5

Upvotes: 0

Mako212
Mako212

Reputation: 7312

You can do it with dplyr like this:

data$ind <- rep(c(1,2), replace=TRUE)

data %>% fill(A,B,C) %>% filter(ind == 2) %>% mutate(ind=NULL)

  gr A B C
1  1 1 1 1
2  2 2 3 4
3  3 4 7 5

Depending on how consistent your full data is, this may need to be adjusted.

Upvotes: 1

akuiper
akuiper

Reputation: 214987

If there's at most one valid value per gr, you can use na.omit then take the first value from it:

data %>% group_by(gr) %>% summarise_all(~ na.omit(.)[1])
# [1] is optional depending on your actual data

# A tibble: 3 x 4
#     gr     A     B     C
#  <int> <dbl> <dbl> <dbl>
#1     1     1     1     1
#2     2     2     3     4
#3     3     4     7     5

Upvotes: 1

Related Questions