聚合并获取组的最后计数

This question is partly related to earlier question here. I want to aggregate counts based on three columns and have the last count of event for the a group defined by the three variables of date, id and rdate. What I wish to have looks like this:

这个问题部分与此前的问题有关。我想基于三列聚合计数,并且具有由date,id和rdate这三个变量定义的组的最后事件计数。我希望拥有的内容如下:

         date     rdate event
1   01-jan-90 08-jan-90     3
2   01-jan-90 15-jan-90     3
3   01-jan-90 01-jan-90     3
4   01-jan-90 22-jan-90     3
5   01-jan-90 29-jan-90     3
1.1 01-jan-90 08-jan-90     2
2.1 01-jan-90 15-jan-90     2
3.1 01-jan-90 01-jan-90     2
4.1 01-jan-90 22-jan-90     2
5.1 01-jan-90 29-jan-90     2

I have tried this code, but that is only valid to obtain mean of the group

我已经尝试过这段代码,但这只能获得该组的平均值

aa<-aggregate(event ~ id+rdate+date,data = mydf,FUN=mean)

Sample data follows:

示例数据如下:

structure(list(date = c("01-jan-90", "01-jan-90", "01-jan-90", 
"01-jan-90", "01-jan-90", "01-jan-90", "01-jan-90", "01-jan-90", 
"01-jan-90", "01-jan-90", "01-jan-90", "01-jan-90", "01-jan-90", 
"01-jan-90", "01-jan-90", "02-jan-90", "02-jan-90", "02-jan-90", 
"02-jan-90", "02-jan-90", "02-jan-90", "02-jan-90", "02-jan-90", 
"02-jan-90", "02-jan-90"), id = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L), rdate = c("08-jan-90", "15-jan-90", "01-jan-90", "22-jan-90", 
"29-jan-90", "08-jan-90", "15-jan-90", "01-jan-90", "22-jan-90", 
"29-jan-90", "08-jan-90", "15-jan-90", "01-jan-90", "22-jan-90", 
"29-jan-90", "09-jan-90", "16-jan-90", "02-jan-90", "23-jan-90", 
"30-jan-90", "09-jan-90", "16-jan-90", "02-jan-90", "23-jan-90", 
"30-jan-90"), event = c(1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 
3L, 3L, 3L, 3L, 3L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L)), .Names = c("date", 
"id", "rdate", "event"), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "1.1", "2.1", "3.1", "4.1", "5.1", "1.2", 
"2.2", "3.2", "4.2", "5.2", "6", "7", "8", "9", "10", "6.1", 
"7.1", "8.1", "9.1", "10.1"))

2 个解决方案

#1

I think that this is what you are after:

我认为这就是你所追求的:

> ddply(d, .(id, date, rdate), summarise, event = tail(event, 1))
   id      date     rdate event
1   1 01-jan-90 01-jan-90     3
2   1 01-jan-90 08-jan-90     3
3   1 01-jan-90 15-jan-90     3
4   1 01-jan-90 22-jan-90     3
5   1 01-jan-90 29-jan-90     3
6   2 02-jan-90 02-jan-90     2
7   2 02-jan-90 09-jan-90     2
8   2 02-jan-90 16-jan-90     2
9   2 02-jan-90 23-jan-90     2
10  2 02-jan-90 30-jan-90     2

You can take the result and sort by date and rdate if the order is important.

您可以获取结果并按日期排序,如果订单很重要,则可以使用rdate。

#2

Not entirely sure what you're trying to do but something like this?

不完全确定你想要做什么,但这样的事情?

library(plyr)
ddply(mydf, .(id, date, rdate), summarise,
      date = tail(date, 1),
      id = tail(id, 1),
      rdate = tail(rdate, 1),
      mean = mean(event))
)

output:

> library(plyr)
> ddply(mydf, .(id, date, rdate), summarise,
+       date = tail(date, 1),
+       id = tail(id, 1),
+       rdate = tail(rdate, 1),
+       mean = mean(event))
        date id     rdate mean
1  01-jan-90  1 01-jan-90  2.0
2  01-jan-90  1 08-jan-90  2.0
3  01-jan-90  1 15-jan-90  2.0
4  01-jan-90  1 22-jan-90  2.0
5  01-jan-90  1 29-jan-90  2.0
6  02-jan-90  2 02-jan-90  1.5
7  02-jan-90  2 09-jan-90  1.5
8  02-jan-90  2 16-jan-90  1.5
9  02-jan-90  2 23-jan-90  1.5
10 02-jan-90  2 30-jan-90  1.5
>

#1