0
$\begingroup$

How can I subset/count rows in one data frame that correspond to rows in another data frame?

I have a data frame DF1 with dates, categories and time instances for each of the date and category combinations. For example:

DF1<-data.frame("DATE"=c(as.Date("2018-12-05"),as.Date("2018-12-06"),as.Date("2018-12-07")),
            "CATEGORY"=c("cat1","cat2","cat3"),
            "TIME"=c(as.POSIXct("2018-12-05 10:05"),as.POSIXct("2018-12-06 10:20"),as.POSIXct("2018-12-07 10:40")))

that is

        DATE CATEGORY                TIME
1 2018-12-05     cat1 2018-12-05 10:05:00
2 2018-12-06     cat2 2018-12-06 10:20:00
3 2018-12-07     cat3 2018-12-07 10:40:00

Then I have another data frame DF2 with objects, categories and a time interval. For example:

DF2<-data.frame("OBJECT_ID"=1:9,
            "CATEGORY"=c("cat1","cat2","cat3","cat1","cat3","cat2","cat1","cat2","cat3"),
            "START"=c(as.POSIXct("2018-12-05 09:00"),as.POSIXct("2018-12-06 10:00"),as.POSIXct("2018-12-07 10:00"),
                      as.POSIXct("2018-12-05 09:30"),as.POSIXct("2018-12-06 08:30"),as.POSIXct("2018-12-07 10:30"),
                      as.POSIXct("2018-12-05 08:30"),as.POSIXct("2018-12-06 08:30"),as.POSIXct("2018-12-07 08:30")),
            "END"=c(as.POSIXct("2018-12-05 10:00"),as.POSIXct("2018-12-06 11:00"),as.POSIXct("2018-12-07 10:00"),
                    as.POSIXct("2018-12-05 11:30"),as.POSIXct("2018-12-06 10:30"),as.POSIXct("2018-12-07 10:30"),
                    as.POSIXct("2018-12-05 11:30"),as.POSIXct("2018-12-06 12:30"),as.POSIXct("2018-12-07 13:30"))
              )

that is

  OBJECT_ID CATEGORY               START                 END
1         1     cat1 2018-12-05 09:00:00 2018-12-05 10:00:00
2         2     cat2 2018-12-06 10:00:00 2018-12-06 11:00:00
3         3     cat3 2018-12-07 10:00:00 2018-12-07 10:00:00
4         4     cat1 2018-12-05 09:30:00 2018-12-05 11:30:00
5         5     cat3 2018-12-06 08:30:00 2018-12-06 10:30:00
6         6     cat2 2018-12-07 10:30:00 2018-12-07 10:30:00
7         7     cat1 2018-12-05 08:30:00 2018-12-05 11:30:00
8         8     cat2 2018-12-06 08:30:00 2018-12-06 12:30:00
9         9     cat3 2018-12-07 08:30:00 2018-12-07 13:30:00

I need to count the number of rows in DF2 that contain the time instance given in DF1 for each date and category. Meaning:

        DATE CATEGORY                TIME NO_OF_OBJECTS
1 2018-12-05     cat1 2018-12-05 10:05:00             2
2 2018-12-06     cat2 2018-12-06 10:20:00             2
3 2018-12-07     cat3 2018-12-07 10:40:00             1

I have a feeling that the apply family should be able to do something here, but I cannot quite grasp how this could be achieved.

$\endgroup$
1
  • $\begingroup$ I think this is a SO question $\endgroup$ – jchaykow Jun 4 '18 at 16:00
0
$\begingroup$

Here is a solution using data.table package.

DF3 <- data.table(merge(DF1, DF2, by = "CATEGORY")) # merge tables by category
DF3$TIME_OK <- (DF3$TIME >= DF3$START & DF3$TIME <= DF3$END) # create a column to check time
DF3 <- DF3[DF3$TIME_OK == T,] # subset table
DF3 <- DF3[,.(NO_OF_OBJECT = .N), by = c("CATEGORY", "DATE", "TIME")] # aggregate results

Basically, it merges both data frames and subsets only relevant time period before aggregating results.

$\endgroup$
1
  • $\begingroup$ Exactly what I was looking for, thank you! Funny how one gets stuck in a box. I tried all kinds of merges, but out of habit I always added all.x=T and failed to achieve what I needed. $\endgroup$ – Silja Jun 5 '18 at 5:58
0
$\begingroup$

One way you could do this is by replicating VLOOKUP in R, i.e. cross-check the times of DF2 against DF1 to see which ones match.

DF1<-data.frame("DATE"=c(as.Date("2018-12-05"),as.Date("2018-12-06"),as.Date("2018-12-07")),
            "CATEGORY"=c("cat1","cat2","cat3"),
            "TIME"=c(as.POSIXct("2018-12-05 10:05"),as.POSIXct("2018-12-06 10:20"),as.POSIXct("2018-12-07 10:40")))

DF2<-data.frame("OBJECT_ID"=1:9,
                "CATEGORY"=c("cat1","cat2","cat3","cat1","cat3","cat2","cat1","cat2","cat3"),
                "TIME"=c(as.POSIXct("2018-12-05 09:00"),as.POSIXct("2018-12-06 10:00"),as.POSIXct("2018-12-07 10:00"),
                          as.POSIXct("2018-12-05 09:30"),as.POSIXct("2018-12-06 08:30"),as.POSIXct("2018-12-07 10:30"),
                          as.POSIXct("2018-12-05 08:30"),as.POSIXct("2018-12-06 08:30"),as.POSIXct("2018-12-07 08:30")),
                "END"=c(as.POSIXct("2018-12-05 10:00"),as.POSIXct("2018-12-06 11:00"),as.POSIXct("2018-12-07 10:00"),
                        as.POSIXct("2018-12-05 11:30"),as.POSIXct("2018-12-06 10:30"),as.POSIXct("2018-12-07 10:30"),
                        as.POSIXct("2018-12-05 11:30"),as.POSIXct("2018-12-06 12:30"),as.POSIXct("2018-12-07 13:30"))
)

mergeinfo<-merge(DF1[, c("TIME", "CATEGORY")],DF2[, c("TIME", "CATEGORY")])

You will see that we are using the merge function in R to identify which dates and times correspond. In this case, none of them do, so upon running the above you will see that the data frame (which I have named mergeinfo) contains 0 observations.

Note that for using this method, you would need to give your TIME variable the same name across both data frames. e.g. in the below example, I renamed "START" in DF2 to "TIME", so that they are being compared. If you wished to check this across "END" times, you could rename this variable to "TIME" instead.

Hope this helps.

$\endgroup$
0
$\begingroup$

Here is a solution using the sqldf package

library(sqldf)

sqldf("select DF1.Date AS DATE, DF1.CATEGORY AS CATEGORY, DF1.TIME AS TIME, COUNT(*) AS NO_OF_OBJECTS
       from DF1 left join DF2
       on DF1.TIME >= DF2.Start
         AND DF1.TIME <= DF2.END
         AND DF1.CATEGORY = DF2.CATEGORY
       GROUP BY DF1.Date, DF1.CATEGORY, DF1.TIME")
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.