我有看起来像这样的数据(注意日期采用DD-MM-YYYY格式):
ID date drug score
A 28/08/2016 2 3
A 29/08/2016 1 4
A 30/08/2016 2 4
A 2/09/2016 2 4
A 3/09/2016 1 4
A 4/09/2016 2 4
B 8/08/2016 1 3
B 9/08/2016 2 4
B 10/08/2016 2 3
B 11/08/2016 1 3
C 30/11/2016 2 4
C 2/12/2016 1 5
C 3/12/2016 2 1
C 5/12/2016 1 4
C 6/12/2016 2 4
C 8/12/2016 1 2
C 9/12/2016 1 2
对于“药物”:1 =服用药物，2 =不服用药物。
我需要总结每个ID:
如果药物连续两天服用(例如，示例的最后两行)，则这些分数不应计入-1天或+1天的计算中(即，最后两行中的每一个都将构成0天分数)但不会对其他指标有所帮助)。
因此，对于此示例数据，我将需要一个如下的输出表:
-1day 0day +1day
A 3.5 4 4
B 3 3 4
C 3.25 2.5
请注意，没有所有日期的记录，并且-1day和+ 1day的计算需要基于实际日期，而不仅仅是数据集中的记录。
我不知道该怎么做。
我还有两个额外的奖励问题:
这是使用此示例数据生成数据框的代码:
data<-data.frame(ID=c("A","A","A","A","A","A","B","B","B","B","C","C","C","C","C","C","C"),
date=as.Date(c("28/08/2016","29/08/2016","30/08/2016","2/09/2016","3/09/2016","4/09/2016","8/08/2016","9/08/2016","10/08/2016","11/08/2016","30/11/2016","2/12/2016","3/12/2016","5/12/2016","6/12/2016","8/12/2016","9/12/2016"),format= "%d/%m/%Y"),
drug=c(2,1,2,2,1,2,1,2,2,1,2,1,2,1,2,1,1),
score=c(3,4,4,4,4,4,3,4,3,3,4,5,1,4,4,2,2))
最佳答案
您可以使用dplyr来获得此信息:
建立资料
df <- data.frame(
ID=c("A","A","A","A","A","A","B","B","B","B","C","C","C","C","C","C","C"),
date=as.Date(c("28/08/2016","29/08/2016","30/08/2016","2/09/2016","3/09/2016","4/09/2016","8/08/2016","9/08/2016","10/08/2016","11/08/2016","30/11/2016","2/12/2016","3/12/2016","5/12/2016","6/12/2016","8/12/2016","9/12/2016"),format= "%d/%m/%Y"),
drug=c(2,1,2,2,1,2,1,2,2,1,2,1,2,1,2,1,1),
score=c(3,4,4,4,4,4,3,4,3,3,4,5,1,4,4,2,2)
)
df
#> ID date drug score
#> 1 A 2016-08-28 2 3
#> 2 A 2016-08-29 1 4
#> 3 A 2016-08-30 2 4
#> 4 A 2016-09-02 2 4
#> 5 A 2016-09-03 1 4
#> 6 A 2016-09-04 2 4
#> 7 B 2016-08-08 1 3
#> 8 B 2016-08-09 2 4
#> 9 B 2016-08-10 2 3
#> 10 B 2016-08-11 1 3
#> 11 C 2016-11-30 2 4
#> 12 C 2016-12-02 1 5
#> 13 C 2016-12-03 2 1
#> 14 C 2016-12-05 1 4
#> 15 C 2016-12-06 2 4
#> 16 C 2016-12-08 1 2
#> 17 C 2016-12-09 1 2
填写缺少的行(天)
解决这类问题的一种好方法是使用
tidyr::complete ，使行隐式丢失观察值而显式丢失。library(dplyr)
library(tidyr)
df1 <- df %>%
group_by(ID) %>%
complete(date = seq(min(date), max(date), by = "day"))
df1
#> Source: local data frame [22 x 4]
#> Groups: ID [3]
#>
#> # A tibble: 22 x 4
#> ID date drug score
#> <fctr> <date> <dbl> <dbl>
#> 1 A 2016-08-28 2 3
#> 2 A 2016-08-29 1 4
#> 3 A 2016-08-30 2 4
#> 4 A 2016-08-31 NA NA
#> 5 A 2016-09-01 NA NA
#> 6 A 2016-09-02 2 4
#> 7 A 2016-09-03 1 4
#> 8 A 2016-09-04 2 4
#> 9 B 2016-08-08 1 3
#> 10 B 2016-08-09 2 4
#> # ... with 12 more rows
分类天数
df2 <- df1 %>%
group_by(ID) %>%
mutate(day_of = drug == 1,
day_before = (lead(drug) == 1 & day_of == FALSE),
day_after = (lag(drug) == 1 & day_of == FALSE))
df2
#> Source: local data frame [22 x 7]
#> Groups: ID [3]
#>
#> # A tibble: 22 x 7
#> ID date drug score day_of day_before day_after
#> <fctr> <date> <dbl> <dbl> <lgl> <lgl> <lgl>
#> 1 A 2016-08-28 2 3 FALSE TRUE NA
#> 2 A 2016-08-29 1 4 TRUE FALSE FALSE
#> 3 A 2016-08-30 2 4 FALSE NA TRUE
#> 4 A 2016-08-31 NA NA NA NA FALSE
#> 5 A 2016-09-01 NA NA NA FALSE NA
#> 6 A 2016-09-02 2 4 FALSE TRUE NA
#> 7 A 2016-09-03 1 4 TRUE FALSE FALSE
#> 8 A 2016-09-04 2 4 FALSE NA TRUE
#> 9 B 2016-08-08 1 3 TRUE FALSE FALSE
#> 10 B 2016-08-09 2 4 FALSE FALSE TRUE
#> # ... with 12 more rows
按日期类型汇总
dplyr::mutate_at将一个函数(在funs()中)应用于vars()中选择的所有列。 summarise_at在某些选定的列上进行操作的方式相同，但是不更改完整数据集的值，而是将其减少为每组一行。可以阅读有关m mutate ， summarise 和特殊 *_at 版本的更多信息。df3 <- df2 %>%
mutate_at(vars(starts_with("day_")), funs(if_else(. == TRUE, score, NA_real_))) %>%
summarise_at(vars(starts_with("day_")), mean, na.rm = TRUE)
df3
#> # A tibble: 3 x 4
#> ID day_of day_before day_after
#> <fctr> <dbl> <dbl> <dbl>
#> 1 A 4.00 3.5 4.0
#> 2 B 3.00 3.0 4.0
#> 3 C 3.25 NaN 2.5