这是从r
组中连续记录的问题的延续在我发布的示例中,答案适用于数据集,但我意识到我的实际数据集有问题,并且出现了一个错误: Error: incompatible size (0), expecting 1 (the group size) or 1
下面是数据集和可再现示例出现。有人知道为什么会发生这种情况吗?
DATE <- as.Date(c('2016-10-26', '2016-10-30', '2016-10-26', '2016-10-20', '2016-10-21', '2016-10-17', '2016-10-26', '2016-10-17', '2016-10-18', '2016-10-20', '2016-10-17', '2016-10-18', '2016-10-17', '2016-10-18', '2016-10-19','2016-10-18', '2016-10-19','2016-10-17','2016-10-17','2016-10-19','2016-10-19','2016-10-20','2016-10-19','2016-10-20','2016-10-30'))
`Parent` <- c('A','A','A','A','A','A','A','B', 'B', 'B', 'C', 'C', 'D', 'D', 'D', 'D', 'D', 'E', 'E', 'F', 'G', 'G', 'G', 'G', 'G')
Child <- c('ab', 'ac', 'ad', 'ae', 'ae','af', 'af','ba', 'ba', 'ba', 'ca', 'cb', 'da', 'da', 'da', 'db', 'db', 'ea', 'eb', 'fa', 'ga', 'ga', 'gb', 'gb', 'gb')
salary <- c(290.45, 0.00, 336.51, 2238.56, 2256.75, 725.73, 319.69, 46.48, 42.13, 43.22, 0.41, 865.20, 1889.80, 2691.97, 3016.80, 8636.18, 8540.24, 1587.21, 1416.63, 79.62,1967.95,1947.35,34925.58,31158.51,6973.54)
avg_child_salary <- c(500.29, 526.27, 492.00, 1197.25, 1197.25, 474.10, 474.10, 21.68, 21.68, 21.68, 0.05, 199.90, 575.31, 575.31, 575.31, 1701.82, 1701.82, 495.48, 316.93, 26.16, 582.66, 582.66, 18089.83, 18089.83, 18089.83)
Callout <- c('LOW', 'LOW', 'LOW', 'HIGH', 'HIGH', 'HIGH', 'LOW', 'HIGH', 'HIGH', 'HIGH', 'HIGH', 'HIGH', 'HIGH', 'HIGH', 'HIGH', 'HIGH', 'HIGH', 'HIGH', 'HIGH', 'HIGH', 'HIGH', 'HIGH', 'HIGH', 'HIGH', 'LOW')
employ.data <- data.frame(DATE, Parent, Child, avg_child_salary, salary, Callout)
employ.data
DATE Parent Child avg_child_salary salary Callout
1 2016-10-26 A ab 500.29 290.45 LOW
2 2016-10-30 A ac 526.27 0.00 LOW
3 2016-10-26 A ad 492.00 336.51 LOW
4 2016-10-20 A ae 1197.25 2238.56 HIGH
5 2016-10-21 A ae 1197.25 2256.75 HIGH
6 2016-10-17 A af 474.10 725.73 HIGH
7 2016-10-26 A af 474.10 319.69 LOW
8 2016-10-17 B ba 21.68 46.48 HIGH
9 2016-10-18 B ba 21.68 42.13 HIGH
10 2016-10-20 B ba 21.68 43.22 HIGH
11 2016-10-17 C ca 0.05 0.41 HIGH
12 2016-10-18 C cb 199.90 865.20 HIGH
13 2016-10-17 D da 575.31 1889.80 HIGH
14 2016-10-18 D da 575.31 2691.97 HIGH
15 2016-10-19 D da 575.31 3016.80 HIGH
16 2016-10-18 D db 1701.82 8636.18 HIGH
17 2016-10-19 D db 1701.82 8540.24 HIGH
18 2016-10-17 E ea 495.48 1587.21 HIGH
19 2016-10-17 E eb 316.93 1416.63 HIGH
20 2016-10-19 F fa 26.16 79.62 HIGH
21 2016-10-19 G ga 582.66 1967.95 HIGH
22 2016-10-20 G ga 582.66 1947.35 HIGH
23 2016-10-19 G gb 18089.83 34925.58 HIGH
24 2016-10-20 G gb 18089.83 31158.51 HIGH
25 2016-10-30 G gb 18089.83 6973.54 LOW
然后,从该数据集中,我想收集包含2016-10-30
的所有行,然后在单独的列中收集连续的天数,并根据lasse.data dataframe的 LOW
或 HIGH
呼叫。连续的天数需要在供应标注旁边的新列中。这是在应用错误的脚本之前:
yesterday <- as.Date(Sys.Date()-37)
df2<-filter(employ.data, DATE == yesterday)
df2
DATE Parent Child avg_child_salary salary Callout
2 2016-10-30 A ac 526.27 0.00 LOW
25 2016-10-30 G gb 18089.83 6973.54 LOW
尝试的代码如下:
library(dplyr)
yesterday <- as.Date(Sys.Date()-37) ##because today is 12/6/16
df2 <- employ.data %>% group_by(Child) %>%
mutate(`Consec. Days with Callout`=cumsum(rev(cumprod(rev((yesterday-DATE)==(which(DATE == yesterday)-row_number()) & Callout==Callout[DATE == yesterday]))))) %>% filter(DATE == yesterday)
最终需要看起来像这样的特定示例:
DATE Parent Child avg_child_salary salary Callout Consec. Days with Callout
2 2016-10-30 A ac 526.27 0.00 LOW 1
25 2016-10-30 G gb 18089.83 6973.54 LOW 1
然后出现错误:
Error: incompatible size (0), expecting 1 (the group size) or 1
问题是对于某些组,找不到yesterday
的行。可以通过定义检查该功能而不是在mutate
中插入功能来解决这一点:
library(dplyr)
compute.consec.days <- function(date, callout, yesterday, rown) {
j <- which(date == yesterday)
if (length(j)==0) NA else cumsum(rev(cumprod(rev((yesterday-date)==(j-rown) & callout==callout[date == yesterday]))))
}
此功能检查which
DATE
是yesterday
。如果该组未找到,则将返回integer(0)
。我们通过返回值j
的length
进行检查。如果这是TRUE
,我们连续返回NA
,这无关紧要,因为以下filter
将删除该组(即找不到yesterday
);否则,我们像以前一样计算连续的几天。这避免了错误。现在,使用此功能和您新发布的数据:
yesterday <- as.Date("2016-10-30")
out <- employ.data %>% group_by(Child) %>%
mutate(`Consec. Days with Callout`=compute.consec.days(DATE,Callout,yesterday,row_number())) %>%
filter(DATE == yesterday)
##Source: local data frame [2 x 7]
##Groups: Child [2]
##
## DATE Parent Child avg_child_salary salary Callout Consec. Days with Callout
## <date> <fctr> <fctr> <dbl> <dbl> <fctr> <dbl>
##1 2016-10-30 A ac 526.27 0.00 LOW 1
##2 2016-10-30 G gb 18089.83 6973.54 LOW 1
更新以支持昨天不是 last 的案例
如果yesterday
的查询不是任何Child
组的最后一天,那么我们需要对compute.consec.days
的函数进行修改:
compute.consec.days <- function(date, callout, yesterday, rown) {
j <- which(date == yesterday)
if (length(j)==0) NA else {
## first compute the condition
cond <- (yesterday-date)==(j-rown) & callout==callout[date == yesterday]
## then evaluate consecutive days only with this vector up to
## the row corresponding to yesterday. Then add the result with NAs
## because mutate is a windowing function
c(cumsum(rev(cumprod(rev(cond[1:j[1]])))),rep(NA,length(date)-j[1]))
}
}
例如,如果昨天的查询是"2016-10-20"
给定新发布的数据,则会导致:
yesterday <- as.Date("2016-10-20")
out <- employ.data %>% group_by(Child) %>%
mutate(`Consec. Days with Callout`=compute.consec.days(DATE,Callout,yesterday,row_number())) %>%
filter(DATE == yesterday)
##Source: local data frame [4 x 7]
##Groups: Child [4]
##
## DATE Parent Child avg_child_salary salary Callout Consec. Days with Callout
## <date> <fctr> <fctr> <dbl> <dbl> <fctr> <dbl>
##1 2016-10-20 A ae 1197.25 2238.56 HIGH 1
##2 2016-10-20 B ba 21.68 43.22 HIGH 1
##3 2016-10-20 G ga 582.66 1947.35 HIGH 2
##4 2016-10-20 G gb 18089.83 31158.51 HIGH 2
使用"2016-10-30"
的原始查询,我们仍然得到原始结果:
yesterday <- as.Date("2016-10-30")
out <- employ.data %>% group_by(Child) %>%
mutate(`Consec. Days with Callout`=compute.consec.days(DATE,Callout,yesterday,row_number())) %>%
filter(DATE == yesterday)
##Source: local data frame [2 x 7]
##Groups: Child [2]
##
## DATE Parent Child avg_child_salary salary Callout Consec. Days with Callout
## <date> <fctr> <fctr> <dbl> <dbl> <fctr> <dbl>
##1 2016-10-30 A ac 526.27 0.00 LOW 1
##2 2016-10-30 G gb 18089.83 6973.54 LOW 1