difftime with previous non-NA value from other columns

Question

I've got a dataframe of 3 variables: POSIXct object - time, numeric - RRR and factor - he. Where RRR is an amount of liquid precipitation and he is the hydrological event number, here its time corresponds to the beginning of the flood event.

df <- structure(list(time = structure(c(1396879200, 1396922400, 1396976400, 
                                        1397008800, 1397095200, 1397332800, 1397354400, 1397397600, 1397451600, 
                                        1397484000, 1397527200, 1397786400, 1397959200, 1398002400, 1398024000, 
                                        1398132000, 1398175200, 1398218400, 1398261600, 1398369600, 1398466800, 
                                        1398477600, 1398520800, 1398564000, 1398607200, 1398747600, 1398780000, 
                                        1398909600, 1398952800, 1398974400, 1398996000),
                                      class = c("POSIXct", "POSIXt"),
                                      tzone = ""),
                     RRR = c(NA, 2, NA, 4, NA, NA, 0.9, 3, 
                             NA, 0.4, 11, NA, 0.5, 1, NA, 13, 4, 0.8, 0.3, NA, NA, 8, 4, 11, 
                             1, NA, 7, 1, 0.4, NA, 4),
                     he = c(1, NA, 2, NA, 3, 4, NA, NA, 
                            5, NA, NA, 6, NA, NA, 7, NA, NA, NA, NA, 8, 9, NA, NA, NA, NA, 
                            10, NA, NA, NA, 11, NA)), 
                class = "data.frame", 
                row.names = c(NA, -31L))

Head of my dataframe look as follows:

> df
                  time  RRR he
1  2014-04-07 18:00:00   NA  1
2  2014-04-08 06:00:00  2.0 NA
3  2014-04-08 21:00:00   NA  2
4  2014-04-09 06:00:00  4.0 NA
5  2014-04-10 06:00:00   NA  3
6  2014-04-13 00:00:00   NA  4
7  2014-04-13 06:00:00  0.9 NA
8  2014-04-13 18:00:00  3.0 NA
9  2014-04-14 09:00:00   NA  5

I need to calculate the time difference between time of every he value and last non-NA RRR value. For example, for he = 2 the desired difference would be difftime(df$time[3], df$time[2]), while for he = 4 the time difference should be difftime(df$time[6], df$time[4]). So in the end I want to get a dataframe like this, where 'diff' is the time difference in hours.

> df
                  time  RRR he  diff
1  2014-04-07 18:00:00   NA  1  NA
2  2014-04-08 06:00:00  2.0 NA  NA
3  2014-04-08 21:00:00   NA  2  15
4  2014-04-09 06:00:00  4.0 NA  NA
5  2014-04-10 06:00:00   NA  3  24
6  2014-04-13 00:00:00   NA  4  90
7  2014-04-13 06:00:00  0.9 NA  NA
8  2014-04-13 18:00:00  3.0 NA  NA
9  2014-04-14 09:00:00   NA  5  15

tmfmnk · Accepted Answer · 2018-12-14T12:23:45.540

I'm sure that there must be easier ways, but using tidyverse and data.table you can do:

df %>%
 mutate(time = as.POSIXct(time, format = "%Y-%m-%d %H:%M:%S")) %>% #Transforming "time" into a datetime object
 fill(RRR) %>% #Filling the NA values in "RRR" with tha last non-NA value
 group_by(temp = rleid(RRR)) %>% #Grouping by run length of "RRR"
 mutate(temp2 = seq_along(temp)) %>% #Sequencing around the run length of "RRR"
 group_by(RRR, temp) %>% #Group by "RRR" and run length of "RRR"
 mutate(diff = ifelse(!is.na(he), difftime(time, time[temp2 == 1], units="hours"), NA)) %>% #Computing the difference in hours between the first occurrence of a non-NA "RRR" value and the non-NA "he" values
 ungroup() %>%
 select(-temp, -temp2, -RRR) %>% #Removing the redundant variables
 rowid_to_column() %>% #Creating unique row IDs
 left_join(df %>% 
            rowid_to_column() %>%
            select(RRR, rowid), by = c("rowid" = "rowid")) %>% #Merging with the original df to get the original values of "RRR"
 select(-rowid) #Removing the redundant variables

   time                   he  diff    RRR
   <dttm>              <dbl> <dbl>  <dbl>
 1 2014-04-07 16:00:00    1.    0. NA    
 2 2014-04-08 04:00:00   NA    NA   2.00 
 3 2014-04-08 19:00:00    2.   15. NA    
 4 2014-04-09 04:00:00   NA    NA   4.00 
 5 2014-04-10 04:00:00    3.   24. NA    
 6 2014-04-12 22:00:00    4.   90. NA    
 7 2014-04-13 04:00:00   NA    NA   0.900
 8 2014-04-13 16:00:00   NA    NA   3.00 
 9 2014-04-14 07:00:00    5.   15. NA    
10 2014-04-14 16:00:00   NA    NA   0.400

score 1 · Answer 2 · answered Dec 14 '18 at 12:33

Here's a data.table approach making use of its non-equi join capabilities:

library(data.table)
setDT(df)

df[df[!is.na(he)][df[!is.na(RRR)], on = .(time>time), rrr_time := i.time], 
    on = .(time, he), rrr_time := i.rrr_time][, diff := difftime(time, rrr_time)]

The result is:

#                     time   RRR    he            rrr_time       diff
#                  <POSc> <num> <num>              <POSc> <difftime>
# 1: 2014-04-07 16:00:00    NA     1                <NA>   NA hours
# 2: 2014-04-08 04:00:00   2.0    NA                <NA>   NA hours
# 3: 2014-04-08 19:00:00    NA     2 2014-04-08 04:00:00   15 hours
# 4: 2014-04-09 04:00:00   4.0    NA                <NA>   NA hours
# 5: 2014-04-10 04:00:00    NA     3 2014-04-09 04:00:00   24 hours
# 6: 2014-04-12 22:00:00    NA     4 2014-04-09 04:00:00   90 hours
# 7: 2014-04-13 04:00:00   0.9    NA                <NA>   NA hours
# 8: 2014-04-13 16:00:00   3.0    NA                <NA>   NA hours
# 9: 2014-04-14 07:00:00    NA     5 2014-04-13 16:00:00   15 hours
# 10: 2014-04-14 16:00:00   0.4    NA                <NA>   NA hours
# 11: 2014-04-15 04:00:00  11.0    NA                <NA>   NA hours
# 12: 2014-04-18 04:00:00    NA     6 2014-04-15 04:00:00   72 hours
# 13: 2014-04-20 04:00:00   0.5    NA                <NA>   NA hours
# 14: 2014-04-20 16:00:00   1.0    NA                <NA>   NA hours
# 15: 2014-04-20 22:00:00    NA     7 2014-04-20 16:00:00    6 hours
# 16: 2014-04-22 04:00:00  13.0    NA                <NA>   NA hours
# 17: 2014-04-22 16:00:00   4.0    NA                <NA>   NA hours
# 18: 2014-04-23 04:00:00   0.8    NA                <NA>   NA hours
# 19: 2014-04-23 16:00:00   0.3    NA                <NA>   NA hours
# 20: 2014-04-24 22:00:00    NA     8 2014-04-23 16:00:00   30 hours
# 21: 2014-04-26 01:00:00    NA     9 2014-04-23 16:00:00   57 hours
# 22: 2014-04-26 04:00:00   8.0    NA                <NA>   NA hours
# 23: 2014-04-26 16:00:00   4.0    NA                <NA>   NA hours
# 24: 2014-04-27 04:00:00  11.0    NA                <NA>   NA hours
# 25: 2014-04-27 16:00:00   1.0    NA                <NA>   NA hours
# 26: 2014-04-29 07:00:00    NA    10 2014-04-27 16:00:00   39 hours
# 27: 2014-04-29 16:00:00   7.0    NA                <NA>   NA hours
# 28: 2014-05-01 04:00:00   1.0    NA                <NA>   NA hours
# 29: 2014-05-01 16:00:00   0.4    NA                <NA>   NA hours
# 30: 2014-05-01 22:00:00    NA    11 2014-05-01 16:00:00    6 hours
# 31: 2014-05-02 04:00:00   4.0    NA                <NA>   NA hours
# time   RRR    he            rrr_time       diff

score 1 · Answer 3 · answered Dec 14 '18 at 13:33

A base alternative with findInterval:

t_he <- d$time[!is.na(d$he)]
t_r <- d$time[!is.na(d$RRR)]
i <- findInterval(t_he, t_r)
d[!is.na(d$he), "diff"] <- t_he - t_r[replace(i, i == 0, NA)]

#                  time RRR he     diff
# 1 2014-04-07 16:00:00  NA  1 NA hours
# 2 2014-04-08 04:00:00 2.0 NA NA hours
# 3 2014-04-08 19:00:00  NA  2 15 hours
# 4 2014-04-09 04:00:00 4.0 NA NA hours
# 5 2014-04-10 04:00:00  NA  3 24 hours
# 6 2014-04-12 22:00:00  NA  4 90 hours
# 7 2014-04-13 04:00:00 0.9 NA NA hours
# 8 2014-04-13 16:00:00 3.0 NA NA hours
# 9 2014-04-14 07:00:00  NA  5 15 hours

difftime with previous non-NA value from other columns

3 Answers3