## 7.3 常用函数

### 7.3.1 特殊符号

.SD,.BY,.N,.I,.NGRP和.GRP,.SDcols等,只能用在 j 的位置,.N 可以用在 i 的位置。

DT = data.table(x=rep(c("b","a","c"),each=3), v=c(1,1,1,2,2,1,1,2,2), y=c(1,3,6), a=1:9, b=9:1)
DT
#>    x v y a b
#> 1: b 1 1 1 9
#> 2: b 1 3 2 8
#> 3: b 1 6 3 7
#> 4: a 2 1 4 6
#> 5: a 2 3 5 5
#> 6: a 1 6 6 4
#> 7: c 1 1 7 3
#> 8: c 2 3 8 2
#> 9: c 2 6 9 1
X = data.table(x=c("c","b"), v=8:7, foo=c(4,2))
X
#>    x v foo
#> 1: c 8   4
#> 2: b 7   2

# 用在i的位置
DT[.N] #取DT最后一行,.N 计数函数
#>    x v y a b
#> 1: c 2 6 9 1
DT[,.N] #DT 共有多少行记录 返回一个整数
#> [1] 9
DT[, .N, by=x]  #分组计数
#>    x N
#> 1: b 3
#> 2: a 3
#> 3: c 3
DT[, .SD, .SDcols=x:y]  # 选择x 到y 列
#>    x v y
#> 1: b 1 1
#> 2: b 1 3
#> 3: b 1 6
#> 4: a 2 1
#> 5: a 2 3
#> 6: a 1 6
#> 7: c 1 1
#> 8: c 2 3
#> 9: c 2 6
#DT[, .SD, .SDcols=c("x","y")] 与上面不一样

DT[, .SD[1]] #取第一行
#>    x v y a b
#> 1: b 1 1 1 9
DT[, .SD[1], by=x] #按x列分组后
#>    x v y a b
#> 1: b 1 1 1 9
#> 2: a 2 1 4 6
#> 3: c 1 1 7 3
DT[, c(.N, lapply(.SD, sum)), by=x] #按照x分组后 行数计数和每列求和
#>    x N v  y  a  b
#> 1: b 3 3 10  6 24
#> 2: a 3 5 10 15 15
#> 3: c 3 5 10 24  6

### 7.3.2 排序函数

frankfrankv函数参数如下：

frank(x, ..., na.last=TRUE, ties.method=c("average",
"first", "last", "random", "max", "min", "dense"))

frankv(x, cols=seq_along(x), order=1L, na.last=TRUE,
ties.method=c("average", "first", "random",
"max", "min", "dense"))

# on vectors
x = c(4, 1, 4, NA, 1, NA, 4)
# NAs are considered identical (unlike base R)
# default is average
frankv(x) # na.last=TRUE
#> [1] 4.0 1.5 4.0 6.5 1.5 6.5 4.0
frankv(x, na.last=FALSE)
#> [1] 6.0 3.5 6.0 1.5 3.5 1.5 6.0

# on data.table
DT = data.table(x, y=c(1, 1, 1, 0, NA, 0, 2))
frankv(DT, cols="x") # same as frankv(x) from before
#> [1] 4.0 1.5 4.0 6.5 1.5 6.5 4.0
frankv(DT, cols="x", na.last="keep")
#> [1] 4.0 1.5 4.0  NA 1.5  NA 4.0
frankv(DT, cols="x", ties.method="dense", na.last=NA)
#> [1] 2 1 2 1 2
frank(DT, x, ties.method="dense", na.last=NA) # equivalent of above using frank
#> [1] 2 1 2 1 2
• frankv在排序时,NA被认为是一样的,基础base R 中认为不一样.
x <-  c(4, 1, 4, NA, 1, NA, 4)
frankv(x)
#> [1] 4.0 1.5 4.0 6.5 1.5 6.5 4.0
rank(x)
#> [1] 4.0 1.5 4.0 6.0 1.5 7.0 4.0
• 升序降序选择

order参数只能为1或者-1.默认为1代表升序

frankv(x,order = 1L)
#> [1] 4.0 1.5 4.0 6.5 1.5 6.5 4.0
frankv(x,order = -1L)
#> [1] 2.0 4.5 2.0 6.5 4.5 6.5 2.0
• 排序方式选择

x <- c(1,1,1,2,3)
frankv(x)  #大小相同 排名相同,下一位排名除以2
frankv(x,ties.method = 'min')  #大小相同 排名相同,取最小排名
frankv(x,ties.method = 'max')  #大小相同 排名相同,取最大排名
frankv(x,ties.method = 'first') #相同大小排名以后往后递增 根据实际情况决定
frankv(x,ties.method = 'dense')
frankv(x,ties.method = 'random')
• NA处理

na.last参数等于TRUE时，缺失值被排最后；如果等于FALSE,放在前面；如果等于NA，将被移除；如果等于“keep,”将会保留NA.

frankv(c(NA,NA,1,2,3), na.last = TRUE,ties.method = 'first')
#> [1] 4 5 1 2 3
frankv(c(NA,NA,1,2,3), na.last = FALSE,ties.method = 'first')
#> [1] 1 2 3 4 5
frankv(c(NA,NA,1,2,3), na.last = NA,ties.method = 'first')
#> [1] 1 2 3
frankv(c(NA,NA,1,2,3), na.last = 'keep',ties.method = 'first')
#> [1] NA NA  1  2  3

### 7.3.3 非重复计数

uniqueN相当于length(unique(x)),但是计算更快，内存效率更高。

x <-sample(1:10,50,replace = TRUE)
uniqueN(x)
#> [1] 10

DT <- data.table(A = rep(1:3, each=4), B = rep(1:4, each=3),
C = rep(1:2, 6), key = "A,B")

uniqueN(DT, by = key(DT))
#> [1] 6
uniqueN(DT)
#> [1] 10

### 7.3.4 判断函数

• fifelse

fifelse()类似dplyr::if_else()函数,相比base::ifelse() 更快。

x <-  c(1:4, 3:2, 1:4,5)
fifelse(x > 2L, x, x - 1L)
#>  [1] 0 1 3 4 3 1 0 1 3 4 5

fifelse(x > 2L,fifelse(x >= 4L,x + 1L,x),x-1L)
#>  [1] 0 1 3 5 3 1 0 1 3 5 6
• fcase

x = 1:10
fcase(
x < 5L, 1L,
x > 5L, 3L
)
#>  [1]  1  1  1  1 NA  3  3  3  3  3

# not run 两种函数实现方式
fifelse(x > 5,fifelse(x >8,2,1),0)
#>  [1] 0 0 0 0 0 1 1 1 2 2
fcase(
x > 8,2,
x > 5,1,
default = 0
)
#>  [1] 0 0 0 0 0 1 1 1 2 2

### 7.3.5 交集 差集 合并


fintersect(x, y, all = FALSE)
fsetdiff(x, y, all = FALSE)
funion(x, y, all = FALSE)
fsetequal(x, y, all = TRUE)

x <-  data.table(c(1,2,2,2,3,4,4))
x2 <-  data.table(c(1,2,3,4)) # same set of rows as x
y <-  data.table(c(2,3,4,4,4,5))

fintersect(x, y)            # intersect
fintersect(x, y, all=TRUE)  # intersect all

fsetdiff(x, y)              # except
fsetdiff(x, y, all=TRUE)    # except all
funion(x, y)                # union
funion(x, y, all=TRUE)      # union all
fsetequal(x, x2, all=FALSE) # setequal
fsetequal(x, x2)            # setequal all

### 7.3.6 长宽转换

• dcast函数能实现长转宽

dcast(data, formula, fun.aggregate = NULL, sep = "_",
..., margins = NULL, subset = NULL, fill = NULL,
drop = TRUE, value.var = guess(data),
verbose = getOption("datatable.verbose"))

dt <- data.table(分公司=rep(c('华东','华南','华西','华北'),1000),
季度=rep(c('一季度','二季度','三季度','四季度'),1000),
销售额=sample(100:200,4000,replace = TRUE))
dcast(dt,分公司~季度,value.var = "销售额",fun.aggregate = sum)
#>    分公司 一季度 三季度 二季度 四季度
#> 1:   华东 149470      0      0      0
#> 2:   华北      0      0      0 149343
#> 3:   华南      0      0 150489      0
#> 4:   华西      0 150698      0      0

fun参数即 fun.aggregate的简写，可以是自定义的函数。

dt <-  data.table(x=sample(5,20,TRUE), y=sample(2,20,TRUE),
z=sample(letters[1:2], 20,TRUE), d1 = runif(20), d2=1L)
dcast(dt, x + y ~ z, fun=list(sum,mean), value.var=c("d1","d2"))
#>     x y d1_sum_a d1_sum_b d2_sum_a d2_sum_b d1_mean_a d1_mean_b d2_mean_a
#>  1: 1 1   0.8454   0.3717        1        2    0.8454    0.1858         1
#>  2: 1 2   0.3769   0.0000        1        0    0.3769       NaN         1
#>  3: 2 1   1.3419   0.4810        2        1    0.6709    0.4810         1
#>  4: 2 2   0.0000   0.6666        0        1       NaN    0.6666       NaN
#>  5: 3 1   0.8703   0.1866        1        1    0.8703    0.1866         1
#>  6: 3 2   0.0000   0.9074        0        2       NaN    0.4537       NaN
#>  7: 4 1   0.0957   0.0000        1        0    0.0957       NaN         1
#>  8: 4 2   0.1103   0.1867        1        1    0.1103    0.1867         1
#>  9: 5 1   1.1344   0.0000        2        0    0.5672       NaN         1
#> 10: 5 2   1.0839   0.0707        2        1    0.5419    0.0707         1
#>     d2_mean_b
#>  1:         1
#>  2:       NaN
#>  3:         1
#>  4:         1
#>  5:         1
#>  6:         1
#>  7:       NaN
#>  8:         1
#>  9:       NaN
#> 10:         1
dcast(dt, x + y ~ z, fun=list(sum,mean), value.var=list("d1","d2")) #注意value.var是向量和列表时的区别
#>     x y d1_sum_a d1_sum_b d2_mean_a d2_mean_b
#>  1: 1 1   0.8454   0.3717         1         1
#>  2: 1 2   0.3769   0.0000         1       NaN
#>  3: 2 1   1.3419   0.4810         1         1
#>  4: 2 2   0.0000   0.6666       NaN         1
#>  5: 3 1   0.8703   0.1866         1         1
#>  6: 3 2   0.0000   0.9074       NaN         1
#>  7: 4 1   0.0957   0.0000         1       NaN
#>  8: 4 2   0.1103   0.1867         1         1
#>  9: 5 1   1.1344   0.0000         1       NaN
#> 10: 5 2   1.0839   0.0707         1         1
• melt函数实现宽转长
melt(data, id.vars, measure.vars,
variable.name = "variable", value.name = "value",
..., na.rm = FALSE, variable.factor = TRUE,
value.factor = FALSE,
verbose = getOption("datatable.verbose"))

ChickWeight = as.data.table(ChickWeight)
setnames(ChickWeight, tolower(names(ChickWeight)))
DT <- melt(as.data.table(ChickWeight), id=2:4) # calls melt.data.table
DT
#>      time chick diet variable value
#>   1:    0     1    1   weight    42
#>   2:    2     1    1   weight    51
#>   3:    4     1    1   weight    59
#>   4:    6     1    1   weight    64
#>   5:    8     1    1   weight    76
#>  ---
#> 574:   14    50    4   weight   175
#> 575:   16    50    4   weight   205
#> 576:   18    50    4   weight   234
#> 577:   20    50    4   weight   264
#> 578:   21    50    4   weight   264

### 7.3.7 表连接

?merge()
merge(x, y, by = NULL, by.x = NULL, by.y = NULL, all = FALSE,
all.x = all, all.y = all, sort = TRUE, suffixes = c(".x", ".y"), no.dups = TRUE,
allow.cartesian=getOption("datatable.allow.cartesian"),  # default FALSE
...)

x.y为连个数据框,当两个数据框连接字段相同时,用by=c(’‘,’’)连接,不同时采用,by.x=,by.y= ,all,all.x,all.y等参数决定连接方式,sort 默认为排序,当不需要排序时更改参数,allow.cartesian=是否允许笛卡尔,默认不允许,当需要时设置为TURE.