I'm trying to get the top row by a group of three variables using a data.table.
我试图通过使用data.table的三组变量来获取第一行。
I have a working solution:
我有一个可行的解决方案:
col1 <- c(1,1,1,1,2,2,2,2,3,3,3,3)
col2 <- c(2000,2000,2001,2001,2000,2000,2001,2001,2000,2000,2001,2001)
col4 <- c(1,2,3,4,5,6,7,8,9,10,11,12)
data <- data.frame(store=col1,year=col2,month=12,sales=col4)
solution1 <- data.table(data)[,.SD[1,],by="store,year,month"]
I used the slower approach suggested by Matthew Dowle in the following link:
我在下面的链接中使用了Matthew Dowle建议的更慢的方法:
https://stats.stackexchange.com/questions/7884/fast-ways-in-r-to-get-the-first-row-of-a-data-frame-grouped-by-an-identifier
I'm trying to implement the faster self join but cannot get it to work.
我正在尝试实现更快的self join，但是无法让它工作。
Does anyone have any suggestions?
有人有什么建议吗?
2 个解决方案
#1
19
option 1 (using keys)
Set the key to be store, year, month
设置密钥存储，年，月
DT <- data.table(data, key = c('store','year','month'))
Then you can use unique to create a data.table containing the unique values of the key columns. By default this will take the first entry
然后可以使用unique创建数据。包含键列的唯一值的表。默认情况下，这将取第一个条目
unique(DT)
store year month sales
1: 1 2000 12 1
2: 1 2001 12 3
3: 2 2000 12 5
4: 2 2001 12 7
5: 3 2000 12 9
6: 3 2001 12 11
But, to be sure, you could use a self-join with mult='first'. (other options are 'all' or 'last')
但是，可以肯定的是，你可以使用一个self-join with mult='first'。(其他选项是“全部”或“最后”)
# the key(DT) subsets the key columns only, so you don't end up with two
# sales columns
DT[unique(DT[,key(DT), with = FALSE]), mult = 'first']
Option 2 (No keys)
Without setting the key, it would be faster to use .I not .SD
如果不设置键，就可以更快地使用
DTb <- data.table(data)
DTb[DTb[,list(row1 = .I[1]), by = list(store, year, month)][,row1]]
#2
2
What about:
是什么:
solution2 <- data.table(data)[ , sales[1], by="store,year,month"]
> solution2
store year month V1
1: 1 2000 12 1
2: 1 2001 12 3
3: 2 2000 12 5
4: 2 2001 12 7
5: 3 2000 12 9
6: 3 2001 12 11
I suppose you could rename that column:
我想你可以重命名这一栏:
data.table(data)[,fsales := sales[1],by="store,year,month"]
#1
19
option 1 (using keys)
Set the key to be store, year, month
设置密钥存储，年，月
DT <- data.table(data, key = c('store','year','month'))
Then you can use unique to create a data.table containing the unique values of the key columns. By default this will take the first entry
然后可以使用unique创建数据。包含键列的唯一值的表。默认情况下，这将取第一个条目
unique(DT)
store year month sales
1: 1 2000 12 1
2: 1 2001 12 3
3: 2 2000 12 5
4: 2 2001 12 7
5: 3 2000 12 9
6: 3 2001 12 11
But, to be sure, you could use a self-join with mult='first'. (other options are 'all' or 'last')
但是，可以肯定的是，你可以使用一个self-join with mult='first'。(其他选项是“全部”或“最后”)
# the key(DT) subsets the key columns only, so you don't end up with two
# sales columns
DT[unique(DT[,key(DT), with = FALSE]), mult = 'first']
Option 2 (No keys)
Without setting the key, it would be faster to use .I not .SD
如果不设置键，就可以更快地使用
DTb <- data.table(data)
DTb[DTb[,list(row1 = .I[1]), by = list(store, year, month)][,row1]]
#2
2
What about:
是什么:
solution2 <- data.table(data)[ , sales[1], by="store,year,month"]
> solution2
store year month V1
1: 1 2000 12 1
2: 1 2001 12 3
3: 2 2000 12 5
4: 2 2001 12 7
5: 3 2000 12 9
6: 3 2001 12 11
I suppose you could rename that column:
我想你可以重命名这一栏:
data.table(data)[,fsales := sales[1],by="store,year,month"]