R的滚动日期范围内的唯一值计数 [英] Count of unique values in a rolling date range for R
问题描述
此问题已经有答案SQL ,我能够使用 sqldf
在R中实现该解决方案。但是,我一直找不到使用 data.table
来实现它的方法。
This question already has an answer for SQL, and I was able to implement that solution in R using sqldf
. However, I've been unable to find a way to implement it using data.table
.
问题是要计算滚动日期范围内一列的不同值,例如(并直接从链接的问题中引用)数据是否如下所示:
The problem is to count the distinct values of one column within a rolling date range, e.g. (and quoting directly from the linked question) if the data looked like this:
Date | email
-------+----------------
1/1/12 | test@test.com
1/1/12 | test1@test.com
1/1/12 | test2@test.com
1/2/12 | test1@test.com
1/2/12 | test2@test.com
1/3/12 | test@test.com
1/4/12 | test@test.com
1/5/12 | test@test.com
1/5/12 | test@test.com
1/6/12 | test@test.com
1/6/12 | test@test.com
1/6/12 | test1@test.com
然后,如果我们使用日期时间段,则结果集看起来像这样3天
Then the result set would look something like this if we used a date period of 3 days
date | count(distinct email)
-------+------
1/1/12 | 3
1/2/12 | 3
1/3/12 | 3
1/4/12 | 3
1/5/12 | 2
1/6/12 | 2
以下是使用 data.table在R中创建相同数据的代码
:
date <- as.Date(c('2012-01-01','2012-01-01','2012-01-01',
'2012-01-02','2012-01-02','2012-01-03',
'2012-01-04','2012-01-05','2012-01-05',
'2012-01-06','2012-01-06','2012-01-06'))
email <- c('test@test.com', 'test1@test.com','test2@test.com',
'test1@test.com', 'test2@test.com','test@test.com',
'test@test.com','test@test.com','test@test.com',
'test@test.com','test@test.com','test1@test.com')
dt <- data.table(date, email)
任何对此的帮助将不胜感激。谢谢!
Any help on this would be much appreciated. Thanks!
编辑1:
这是一个玩具问题,我想将其应用于更大的数据集,因此使用笛卡尔积是有问题的。取而代之的是,我想要的东西等同于SQL中的相关子查询,例如我最初链接的问题的解决方案是:
This is a toy problem that I want to apply to a much larger data set, so use of Cartesian products is problematic. Instead, I'd like something equivalent to a correlated subquery in SQL, e.g. the solution from the question that I originally linked was:
SELECT day
,(SELECT count(DISTINCT email)
FROM tbl
WHERE day BETWEEN t.day - 2 AND t.day -- period of 3 days
) AS dist_emails
FROM tbl t
WHERE day BETWEEN '2012-01-01' AND '2012-01-06'
GROUP BY 1
ORDER BY 1;
编辑2:
这是根据@MichaelChirico的解决方案的一些时间,应@的要求jangorecki:
Edit 2: Here is some timing based on @MichaelChirico's solution, as requested by @jangorecki:
# The data
> dim(temp)
[1] 2627785 4
> head(temp)
date category1 category2 itemId
1: 2013-11-08 0 2 1713
2: 2013-11-08 0 2 90485
3: 2013-11-08 0 2 74249
4: 2013-11-08 0 2 2592
5: 2013-11-08 0 2 2592
6: 2013-11-08 0 2 765
> uniqueN(temp$itemId)
[1] 13510
> uniqueN(temp$date)
[1] 127
# Timing for data.table
> system.time(dtTime <- temp[,
+ .(count = temp[.(seq.Date(.BY$date - 6L, .BY$date, "day"),
+ .BY$category1, .BY$category2 ), uniqueN(itemId), nomatch = 0L]),
+ by = c("date","category1","category2")])
user system elapsed
6.913 0.130 6.940
>
# Time for sqldf
> system.time(sqlDfTime <-
+ sqldf(c("create index ldx on temp(date, category1, category2)",
+ "SELECT date, category1, category2,
+ (SELECT count(DISTINCT itemId)
+ FROM temp
+ WHERE category1 = t.category1 AND category2 = t.category2 AND
+ date BETWEEN t.date - 6 AND t.date
+ ) AS numItems
+ FROM temp t
+ GROUP BY date, category1, category2
+ ORDER BY 1;"))
user system elapsed
87.225 0.098 87.295
输出是等效的,但是使用data.table而不是sqldf导致速度提高了12.5倍
The outputs are eqivalent, but using data.table rather than sqldf resulted in a 12.5x speedup. Pretty substantial!
推荐答案
这很有效,它利用了的新非等额连接功能。 data.table
。
dt[dt[ , .(date3=date, date2 = date - 2, email)],
on = .(date >= date2, date<=date3),
allow.cartesian = TRUE
][ , .(count = uniqueN(email)),
by = .(date = date + 2)]
# date V1
# 1: 2011-12-30 3
# 2: 2011-12-31 3
# 3: 2012-01-01 3
# 4: 2012-01-02 3
# 5: 2012-01-03 1
# 6: 2012-01-04 2
说实话,我对它的工作方式有点不满意,但想法是加入 dt
在日期
自身,匹配两天前的任何日期
还有今天。我不确定为什么我们必须通过设置 date = date + 2
之后进行清理。
To be honest I'm a bit miffed on how this is working exactly, but the idea is to join dt
to itself on date
, matching any date
that is between 2 days ago and today. I'm not sure why we have to clean up by setting date = date + 2
afterwards.
以下是使用密钥的方法:
Here's an approach using keys:
setkey(dt, date)
dt[ , .(count = dt[.(seq.Date(.BY$date - 2L, .BY$date, "day")),
uniqueN(email), nomatch = 0L]), by = date]
这篇关于R的滚动日期范围内的唯一值计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!