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.
我有一个数据框,其中包括一个小时的连续日期和时间列(列A)和几个参数列(例如列B到Z)和测量值。
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?
我的问题是,如果我想计算一个参数在特定时间段(例如早上6点到前一天晚上8点)之间B列中值的差异,我该如何编写代码?
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).
如何计算第二天早上6点到前一天晚上8点的每晚PM的差异?所以它实际上并没有在同一天计算差异,而是在第2天(早上6点)和第1天(晚上8点)之间。
2 个解决方案
#1
Which system are you looking for a solution for?
您正在寻找哪种系统解决方案?
In case of T-SQL it could be something like :
在T-SQL的情况下,它可能是这样的:
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.
这不是针对语法测试的,但可以指向正确的方向。
#2
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:
由于时间不够,有点笨拙的解决方案。如果您确定每天早上6点和晚上8点都有一个条目,您可以使用子集:
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
当然,有很多更好的解决方案可以检查差异中的PM是否属于SAME日,但作为一个开始你可能会使用它
#1
Which system are you looking for a solution for?
您正在寻找哪种系统解决方案?
In case of T-SQL it could be something like :
在T-SQL的情况下,它可能是这样的:
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.
这不是针对语法测试的,但可以指向正确的方向。
#2
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:
由于时间不够,有点笨拙的解决方案。如果您确定每天早上6点和晚上8点都有一个条目,您可以使用子集:
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
当然,有很多更好的解决方案可以检查差异中的PM是否属于SAME日,但作为一个开始你可能会使用它