CODEWITHSUNDEEP
rdataframezoo
Denis
Denis

Reputation: 12077

How do I find the 1st non-NA value in a row?

Suppose I have the following:

df <- data.frame(dt=c(as.Date('2019-02-02'), as.Date('2019-02-04'), as.Date('2019-02-05'), as.Date('2020-03-04')), v1=c(1,2,NA,NA), v2=c(NA,3,4,NA), v3=c(NA,NA,3,5), v4=c(2, 4, 6, NA))
> read.zoo(df)
           v1 v2 v3 v4
2019-02-02  1 NA NA  2
2019-02-04  2  3 NA  4
2019-02-05 NA  4  3  6
2020-03-04 NA NA  5 NA

I would like to find the first non-NA value on each row that occur after a column that had a value.

So for example, for '2019-02-02':

Therefore our 1st row will be:

c1 c2 c3 c4
2  NA NA NA

Going through all the rows in this example I am expecting the output to be:

             c1 c2 c3 c4
1 2019-02-02  2 NA NA NA
2 2019-02-04  3  4 NA NA
3 2019-02-05 NA  3  6 NA
4 2020-03-04 NA NA NA NA

It looks like all I need to do is shift the column values in each row to the left but I can't seem to figure out how to do it...

NOTE: I would prefer a base-R solution using zoo

Upvotes: 1

Views: 335

Answers (2)

akash87
akash87

Reputation: 3994

I'm not sure how to do it with base R. But in tidyverse:

df %>% 
gather(key, value, -dt) %>% 
arrange(dt, key) %>% 
mutate(key2 = as.numeric(substr(key, 2, 2))) %>% 
filter(!is.na(value)) %>% group_by(dt) %>% 
mutate(ind = lag(key2, default = NA), index = paste0("c", ind)) %>% 
ungroup() %>% 
filter(!is.na(ind)) %>% 
select(dt, index, value) %>% 
spread(index, value)

Upvotes: 1

Gregor Thomas
Gregor Thomas

Reputation: 145755

Here's a solution applying a custom function:

res = t(apply(df[-1], 1, function(x) {
     val = which(!is.na(x))
     x[val[-length(val)]] = x[val[-1]]
     x[val[length(val)]] = NA
     return(x)
     }
  ))

cbind(df[1], res)
#           dt v1 v2 v3 v4
# 1 2019-02-02  2 NA NA NA
# 2 2019-02-04  3  4 NA NA
# 3 2019-02-05 NA  3  6 NA
# 4 2020-03-04 NA NA NA NA

Upvotes: 3

Related Questions