-3

I have a data frame, which includes a continuous date&time column (column A) in hour and several parameters columns (column B to Z for example) with measured values.

My question is, if I would like to calculate the difference of values in Column B for one parameter between a specific time period, say 6 AM in the morning minus 8 PM in the previous day, how should I write the code?

An example test data using some random numbers:

hour <- seq(as.POSIXct("2014/01/01 00:00:00",tz="EST"), as.POSIXct("2014/3/31 23:00:00",tz="EST"), by="hour")
PM <- runif(2160, 0, 50)
NO <- runif (2160, 0, 200)
test <- data.frame(hour,PM,NO)

How can I calculate the difference in PM between 6 AM in the next day and 8 PM in the previous day for each night? So it's actually not calculating the difference in the same day, but between day 2 (6 AM) and day 1 (8 PM).

Vicki1227
  • 151
  • 1
  • 3
  • 10
  • 3
    Please provide a [minimum reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example), including a sample dataset and any code you've tried. – Alex A. May 13 '15 at 14:55

2 Answers2

0

Which system are you looking for a solution for?

In case of T-SQL it could be something like :

SELECT MAX(ColumnB)-MIN(ColumnB) AS Column_B_Diff 
FROM Data_table 
WHERE Date >= '2011/02/25 20:00:00.000' and Date <= '2011/02/26 06:00:00.000'

This is not tested for syntax, but could point you in the right direction.

David Arenburg
  • 91,361
  • 17
  • 137
  • 196
Stig Meyer Jensen
  • 76
  • 7
  • He is looking for `r` solution (the question has its tag). But your solution could potentially also work with the right package. – David Arenburg May 13 '15 at 15:00
0

A somewhat clumsy solution due to lack of time. If you are sure that EVERY day will have an entry at 6AM and 8PM, you can use subsets:

hour <- seq(as.POSIXct("2014/01/01 00:00:00",tz="EST"), as.POSIXct("2014/3/31 23:00:00",tz="EST"), by="hour")
PM <- runif(2160, 0, 50)
NO <- runif (2160, 0, 200)
test <- data.frame(hour,PM,NO)
test6 = subset(test,grepl("06:00:00",test$hour) == T)
test8 = subset(test,grepl("20:00:00",test$hour) == T)
diffPM = test8$PM - test6$PM

Of course, there are a lot better solutions that check that PM in difference belongs to SAME day, but as a start you might work with that

Alexey Ferapontov
  • 5,029
  • 4
  • 22
  • 39
  • Thanks for your answer. Yes, you raised a good point. For some days, I do have missing data on either or both times. Also, your method is calculating difference on the same day, but I would like to calculate the PM difference between 6 am on day 2 and 8 pm on day 1, which is actually the start amd ending time of the night period. – Vicki1227 May 13 '15 at 16:06
  • Ah! Then the solution will not work. Just saw the edit – Alexey Ferapontov May 13 '15 at 16:14
  • Define missing data as well - no row completely for say 06:00:00 or `NA` in PM and NO? – Alexey Ferapontov May 13 '15 at 16:15
  • That's easier, then, as one can always assume at least time/day for any 6AM or 8PM. If no good answer today, I'll try to come up with smth tomorrow (vacation today :)) – Alexey Ferapontov May 13 '15 at 17:12