r - 如果开始和结束时间可用,R 每天汇总数据
问题描述
我有以下问题。我有以下结构的数据框:
startdatetime enddatetime type amount
1 2019-02-01 03:35:00 2019-02-03 06:35:00 prod1 1e+03
2 2019-02-03 06:35:00 2019-02-05 09:35:00 prod1 5e+03
3 2019-02-05 09:35:00 2019-02-06 01:35:00 prod2 3e+07
4 2019-02-06 01:35:00 2019-02-06 03:35:00 prod1 1e+02
表示在特定时间跨度(开始日期时间和结束日期时间)内生产的数量。现在我想每天汇总这些数据。让我们忽略不完整的一天 2019-02-01,从 2019-02-02 开始。第一个产品 1 在 2019-02-01 03:35:00 和 2019-02-03 06:35:00 之间生产,总共生产了 1000 公斤。因此,例如,在 2019 年 2 月 2 日:24/51*1000
= 生产了 prod 1 的 470.58 个,因为24h + 21h + 6h = 51h
. 到目前为止,我的解决方案是基于一个 for 和一个 while 循环,但我想有一个基于包“lubridate”的更快的解决方案,或者我没有找到。有什么建议吗?在我的代码下面
#create test data set
mydata <- data.frame(startdatetime=c(as.POSIXct("2019-02-01 03:35:00"), as.POSIXct("2019-02-03 06:35:00"),as.POSIXct("2019-02-05 09:35:00"),as.POSIXct("2019-02-06 01:35:00")),
enddatetime =c(as.POSIXct("2019-02-03 06:35:00"), as.POSIXct("2019-02-05 09:35:00"),as.POSIXct("2019-02-06 01:35:00"),as.POSIXct("2019-02-06 03:35:00")),
type=c("prod1","prod1","prod2","prod1"),
amount=c(1000,5000,30000000,100))
# take only full days into account and ignore the first and the last day
minstartday = min(mydata$startdatetime)+24*60*60
maxendday = max(mydata$enddatetime)-24*60*60
#create a day index
timesindex <- seq(from = as.Date(format(minstartday, format = "%Y/%m/%d")),
to = as.Date(format(maxendday, format = "%Y/%m/%d")), by = "day")
# create an empty dataframe which will be filled with the production data for each day
prodperday <- data.frame(Date=as.Date(timesindex),
prod1=replicate(length(timesindex),0),
prod2=replicate(length(timesindex),0),
stringsAsFactors=FALSE)
# loop over all entries and separate them into produced fractions per day
for (irow in 1:dim(mydata)[1]){
timestart = mydata[irow,"startdatetime"]
datestart = as.Date(format(timestart, format = "%Y/%m/%d"))
timeend = timestart
tota_run_time_in_h = (as.numeric((mydata[irow,"enddatetime"]-mydata[irow,"startdatetime"])))*24.
while (timeend < mydata[irow,"enddatetime"]){
timeend = min (as.POSIXct(datestart, format = "%Y/%m/%d %H:%M:%S")+23*60*60-1,
mydata[irow,"enddatetime"])
tdiff = as.numeric(timeend-timestart)
fraction_prod = (tdiff/tota_run_time_in_h)*mydata[irow,"amount"]
if (datestart %in% prodperday$Date){
prodperday[prodperday$Date == datestart,as.character(mydata[irow,"type"])] =
prodperday[prodperday$Date == datestart,as.character(mydata[irow,"type"])] + fraction_prod
}
timestart = timeend+1
datestart = as.Date(format(timestart, format = "%Y/%m/%d"))
timeend = timestart
}
}
结果:
Date prod1 prod2
1 2019-02-02 470.5828 0
2 2019-02-03 1836.5741 0
3 2019-02-04 2352.9139 0
4 2019-02-05 939.5425 1126280
解决方案
我提出的解决方案并不完美,因为边界存在问题,但按小时转换生产数据并按天汇总后的想法可能是个好主意。
我随意使用这两个库:
library(lubridate)
library(dplyr)
参考时间:
ref.times <- seq(from = min(mydata$startdatetime),
to = max(mydata$enddatetime),
by = "hour")
构建按小时生产的数据库:
newdata <- data.frame(hour = floor_date(ref.times, unit = "hour"),
prod1 = 0,
prod2 = 0,
day = floor_date(newdata$hour, unit= "day"))
for(i in 1:nrow(mydata)){
ref.times <- seq(from = mydata$startdatetime[i],
to = mydata$enddatetime[i],
by = "hour")
n <- length(floor_date(ref.times, "hour"))
if(mydata[i, 3] == "prod1"){
newdata[newdata$hour %in% floor_date(ref.times, unit = "hour"), 2] <-
rep(mydata[i, 4] / n, n)
}else{
newdata[newdata$hour %in% floor_date(ref.times, unit = "hour"), 3] <-
rep(mydata[i, 4] / n, n)
}
}
按天汇总:
newdata %>% group_by(day) %>% summarise(prod1 = sum(prod1),
prod2 = sum(prod2))
推荐阅读
- google-apps-script - Using authorization token from G Suite add-on for use on my api service
- c - can't figure out the sizeof(long double) in C is 16 bytes or 10 bytes
- amazon-web-services - S3 GetObjectTagging error for only subset of similar files
- android - Android FastAdapter ClickEventHook isn't working properly
- c# - HTTP Error 500.0 - ANCM In-Process Handler Load Failure
- r - R重新排列数据框尺寸
- javascript - VueJS - is there a way to force a mixin reload?
- android-fragments - Overlapping of fragments when transiting on clicking on an item in RecyclerView of one fragment to another new fragment
- python - How to calculate x*x.T in python
- python - pandas matrix calculation till the diagonal