0

I am trying to build a data frame from two others which monitor animal activity based on temperature. I want a dataframe where Activity =1 if the animal was active at that time and temp, and Activity = 0 if no animals were active. I have found a solution here (and its related question here) to my problem of joining my two dataframes. However when I adjusted the code to my data I get an error: "x non-numeric argument to binary operator i Input ..2 is readtimes.short$TimeStamp < (TimeStamp - 10)." How do I get it to take the TimeStamp + or - 10 minutes?

Here's the code I have so far:

ReaderTemp<- tempdata %>%
   left_join(readtimes, by="TimeStamp", copy=T) %>%
   filter(readtimes$TimeStamp >= TimeStamp, readtimes$TimeStamp < TimeStamp - 10) %>%
   select(Series, Temperature, TimeStamp,
         readtimes.short$ReaderRef, readtimes.short$Active) %>%
   mutate_all(~replace(., is.na(.), 0))

Also tried:

ReaderTemp<- tempdata %>%
   left_join(readtimes.short, by="TimeStamp", copy=T) %>%
   filter(near(readtimes.short$TimeStamp, TimeStamp, tol=600)) %>%
   select(readtimes.short$ReaderRef, TimeStamp, Series, Temperature, 
          readtimes.short$RDate, readtimes.short$RTime, readtimes.short$Active) %>%
   mutate_all(~replace(., is.na(.), 0))

I also tried (which i can't remember which question on SO I saw this answer). This it takes about 10 minutes to run and just returns values not a data frame:

ReaderTemp2<-sapply(tempdata$TimeStamp, function(TimeStamp) 
            which.min(abs(difftime(TimeStamp, readtimes.short$TimeStamp))))
ReaderTemp2<-cbind(tempdata, tempdata[ sapply(readtimes.short$TimeStamp,
                                     function(TimeStamp) 
            which.min(abs(difftime(TimeStamp, readtimes.short$TimeStamp)))),])

I tried putting (TimeStamp - 10) but that didn't work. One work around that I thought of would be to create a new column in tempdata with an end time being 9 minutes more than the time stamp. But I would like to know if it's possible without adding a column. A tidyverse solution would be helpful, but also willing to learn use in other packages (data table maybe?). Thank you in advance.

Dataframe 1: readtimes (about 1200 rows)

  ReaderRef        TimeStamp RTime      RDate Active
  1         9 2020-08-12 13:38 13:38 2020-08-12      1
  2        15 2020-08-12 13:40 13:40 2020-08-12      1
  3        16 2020-08-12 13:41 13:41 2020-08-12      1
  4        22 2020-08-12 14:14 14:14 2020-08-12      1
  5        25 2020-08-12 15:16 15:16 2020-08-12      1
  6        29 2020-08-12 15:17 15:17 2020-08-12      1

Dataframe 2: tempdata (about 28,000 rows)

Series Temperature        TimeStamp
1  shade      21.144 2020-08-12 13:30
2    sun      56.576 2020-08-12 13:30
3  shade      23.646 2020-08-12 13:40
4    sun      55.581 2020-08-12 13:40
5  shade      21.644 2020-08-12 13:50
6    sun      55.581 2020-08-12 13:50

What I'm expecting:

Series   Temperature        TimeStamp   ReaderRef     Active
1  shade      21.144 2020-08-12 13:30          9           1
2    sun      56.576 2020-08-12 13:30          9           1  
3  shade      23.646 2020-08-12 13:40         15           1
4    sun      55.581 2020-08-12 13:40         15           1
5  shade      21.644 2020-08-12 13:50         NA           0
6    sun      55.581 2020-08-12 13:50         NA           0
Kyle Finn
  • 29
  • 5
  • You could use the `lubridate` package and a function like `floor_date`, `ceiling_date`, or `round_date` to adjust all times to standardized time slots (like every 10 min) before you do the join. Use the argument `unit = "10 mins"`. – Michael Dewar Dec 01 '20 at 14:42
  • @MichaelDewar thanks for the suggestion. I had already standardized the times in ```tempdata``` but I didn't think to just do the same for ```readtimes``` – Kyle Finn Dec 01 '20 at 15:14

1 Answers1

1

Ok using @MichaelDewar's suggestion I was able to get it to work.

First I changed the time stamp in readtimes to 10 minute intervals using floor_date. Then the filter from my original code was not necessary so removed. Selected the columns I wanted and finally replaced the NAs with 0s.

library(lubridate)
readtimes<- readtimes %>%
   mutate(TimeStamp = as.POSIXct(TimeStamp, tz="UTC"),
   TimeStamp = floor_date(TimeStamp, unit='10 mins'))
ReaderTemp<- tempdata %>%
   left_join(readtimes.short, by= "TimeStamp", copy = T) %>%
   select(ReaderRef, TimeStamp, Series, Temperature, Active) %>%
   mutate_at(vars(Active), replace_na, '0')
Kyle Finn
  • 29
  • 5