CODEWITHSUNDEEP
r
Juppes
Juppes

Reputation: 169

Subset of data frame according to status in separate data frame

I hope that anyone can help me with this issue. I have two sample data frames:

mystatusdate <- as.POSIXct(c("2016-02-01 08:05:16",
                             "2016-02-01 08:12:24",
                             "2016-02-01 08:20:16",
                             "2016-02-01 08:25:09",
                             "2016-02-01 08:36:22",
                             "2016-02-01 08:44:53",
                             "2016-02-01 08:50:25"),
                           tz="Europe/Berlin",
                           format = '%Y-%m-%d %H:%M:%S')
mystatus <- c(0, 1, 0, 1, 0, 1, 0)
mydf.status <- data.frame(mystatusdate, mystatus)

mytempdate <- as.POSIXct(c("2016-02-01 08:05:35",
                           "2016-02-01 08:09:43",
                           "2016-02-01 08:13:15",
                           "2016-02-01 08:15:16",
                           "2016-02-01 08:17:59",
                           "2016-02-01 08:22:09",
                           "2016-02-01 08:25:17",
                           "2016-02-01 08:28:02",
                           "2016-02-01 08:35:55",
                           "2016-02-01 08:38:32",
                           "2016-02-01 08:41:45",
                           "2016-02-01 08:43:11",
                           "2016-02-01 08:46:27",
                           "2016-02-01 08:48:47",
                           "2016-02-01 08:51:25"),
                         tz="Europe/Berlin",
                         format = '%Y-%m-%d %H:%M:%S')
mytemp <- c(11.4, 11.5, 14.3, 15.1, 15.0, 11.9, 14.1, 15.0, 15.3, 12.1, 12.3, 14.5, 15.1, 14.9, 12.8)
mydf.temp <- data.frame(mytempdate, mytemp)

Can be plotted with this code:

library(ggplot2)
ggplot() + 
  geom_step(data=mydf.status, aes(x=mystatusdate, y=mystatus), direction = "hv") +
  geom_line(data=mydf.temp, aes(x=mytempdate, y=mytemp), colour = "red")

Above code creates mydf.status which is an irregular time series with a status which is either '1' or '0' and mydf.temp which contains temperature values also with irregular time series. The two time series are different.

I now want to create a new data frame out of this in which I have a subset of the mydf.temp data frame but only with the rows that are in time ranges where the mydf.status shows the status = '1'. So the result should be this data frame:

myresultdate <- as.POSIXct(c("2016-02-01 08:13:15",
                             "2016-02-01 08:15:16",
                             "2016-02-01 08:17:59",
                             "2016-02-01 08:25:17",
                             "2016-02-01 08:28:02",
                             "2016-02-01 08:35:55",
                             "2016-02-01 08:46:27",
                             "2016-02-01 08:48:47"),
                           tz="Europe/Berlin",
                           format = '%Y-%m-%d %H:%M:%S')
myresulttemp <- c(14.3, 15.1, 15.0, 14.1, 15.0, 15.3, 15.1, 14.9)
mydf.resulttemp <- data.frame(myresultdate, myresulttemp)

Maybe with the following plot you will better see what I mean: Only the blue points should remain in the result data frame.

ggplot() + 
geom_step(data=mydf.status, aes(x=mystatusdate, y=mystatus), direction = "hv") +
geom_line(data=mydf.temp, aes(x=mytempdate, y=mytemp), colour = "red") +
geom_point(data=mydf.resulttemp, aes(x=myresultdate, y=myresulttemp), colour = "blue")

Any help is very appreciated!

Upvotes: 1

Views: 58

Answers (1)

bergant
bergant

Reputation: 7232

You can use dplyr to filter temp time series with intervals:

library(dplyr)

mydf.temp$mystatus <- 1

mydf.status %>% 
mutate(dateend = lead(mystatusdate)) %>% 
inner_join(mydf.temp, by = "mystatus") %>% 
filter(mytempdate > mystatusdate & mytempdate <= dateend) %>% 
select(mytempdate, mytemp)

#>            mytempdate mytemp
#> 1 2016-02-01 08:13:15   14.3
#> 2 2016-02-01 08:15:16   15.1
#> 3 2016-02-01 08:17:59   15.0
#> 4 2016-02-01 08:25:17   14.1
#> 5 2016-02-01 08:28:02   15.0
#> 6 2016-02-01 08:35:55   15.3
#> 7 2016-02-01 08:46:27   15.1
#> 8 2016-02-01 08:48:47   14.9

Upvotes: 1

Related Questions