代码之家 › 专栏 › 技术社区 › ShanZhengYang

用R data.table行的排列填充“计数矩阵”

data.table count matrix r

ShanZhengYang · 技术社区 · 6 年前

(下面,我可以是R data.frame或R data.table。两个都可以。)

library(data.table)

dt = data.table(V1=c("dog", "dog", "cat", "cat", "cat", "bird","bird","bird","bird"), 
                    V2=rep(42, 9), V3=c(1, 2, 4, 5, 7, 1, 2, 5, 8)) 

> print(dt)
     V1 V2 V3
1:  dog 42  1
2:  dog 42  2
3:  cat 42  4
4:  cat 42  5
5:  cat 42  7
6: bird 42  1
7: bird 42  2
8: bird 42  5
9: bird 42  8

列 V3 包含从1到8的整数。我的目标是用每个组合“对”的计数填充一个8乘8的零矩阵,给定列中的唯一类别 V1

dog , cat ,和 bird 是:

dog: (1, 2)
cat: (4, 5), (4, 7), (5, 7)
bird: (1, 2), (1, 5), (1, 8), (2, 5), (2, 8), (5, 8)

+1 到零矩阵中的相应项。对于这个矩阵, (n, m) = (m, n) . 给定的矩阵 dt 将是:

   1 2 3 4 5 6 7 8
1: 0 2 0 0 1 0 0 1
2: 2 0 0 0 1 0 0 1
3: 0 0 0 0 0 0 0 0
4: 0 0 0 0 1 0 1 0
5: 1 1 0 1 0 0 1 1
6: 0 0 0 0 0 0 0 0
7: 0 0 0 1 1 0 0 0
8: 1 1 0 0 1 0 0 0

(1,2)=(2,1) 计数为2,来自 狗 鸟 组合。

(1) 如果R data.table/data.frame列中的值在另一列中是唯一的,是否有方法计算这些值的组合?

也许输出一个带有向量“pairs”的R列表是有意义的,例如。

list(c(1, 2), c(2, 1), c(4, 5), c(4, 7), c(5, 7), c(5, 4), c(7, 4), c(7, 5),
    c(1, 2), c(1, 5), c(1, 8), c(2, 5), c(2, 8), c(5, 8), c(2, 1), c(5, 1),
    c(8, 1), c(5, 2), c(8, 2), c(8, 5))

(2) 在输入data.table/data.frame的情况下,最有效的数据结构是什么?

2 回复 | 直到 6 年前

David Arenburg Ulrik 6 年前

这里有一个data.table解决方案,看起来很有效。我们基本上是做一个自连接来创建组合然后计数。然后,类似于@coldspeed对Numpy所做的操作,我们只需根据有计数的位置更新一个零矩阵。

# a self join
tmp <- dt[dt, 
             .(V1, id = x.V3, id2 = V3), 
             on = .(V1, V3 < V3), 
             nomatch = 0L,
             allow.cartesian = TRUE
          ][, .N, by = .(id, id2)]

## Create a zero matrix and update by locations
m <- array(0L, rep(max(dt$V3), 2L))
m[cbind(tmp$id, tmp$id2)] <- tmp$N
m + t(m)

#      [,1] [,2] [,3] [,4] [,5] [,6] [,7] [,8]
# [1,]    0    2    0    0    1    0    0    1
# [2,]    2    0    0    0    1    0    0    1
# [3,]    0    0    0    0    0    0    0    0
# [4,]    0    0    0    0    1    0    1    0
# [5,]    1    1    0    1    0    0    1    1
# [6,]    0    0    0    0    0    0    0    0
# [7,]    0    0    0    1    1    0    0    0
# [8,]    1    1    0    0    1    0    0    0

或者,我们可以创造 tmp 使用 data.table::CJ

tmp <- dt[, CJ(V3, V3)[V1 < V2], by = .(g = V1)][, .N, by = .(V1, V2)]

## Then, as previously
m <- array(0L, rep(max(dt$V3), 2L))
m[cbind(tmp$V1, tmp$V2)] <- tmp$N
m + t(m)

#      [,1] [,2] [,3] [,4] [,5] [,6] [,7] [,8]
# [1,]    0    2    0    0    1    0    0    1
# [2,]    2    0    0    0    1    0    0    1
# [3,]    0    0    0    0    0    0    0    0
# [4,]    0    0    0    0    1    0    1    0
# [5,]    1    1    0    1    0    0    1    1
# [6,]    0    0    0    0    0    0    0    0
# [7,]    0    0    0    1    1    0    0    0
# [8,]    1    1    0    0    1    0    0    0

DanY 6 年前

不确定这是最优雅的方法,但它确实有效:

myfun <- function(x, matsize=8) {
    # get all (i,j) pairs but in an unfortunate text format
    pairs_all <- outer(x, x, paste)

    # "drop" all self-pairs like (1,1)
    diag(pairs_all) <- "0 0"

    # convert these text-pairs into numeric pairs and store in matrix
    ij <- do.call(rbind, lapply(strsplit(pairs_all, " "), as.numeric))

    # create "empty" matrix of zeros
    mat <- matrix(0, nrow=matsize, ncol=matsize)

    # replace each spot of empty matrix with a 1 if that pair exists
    mat[ij] <- 1

    # return 0/1 matrix
    return(mat)
}

# split your data by group
# lapply the custom function to each group
# add each group's 0/1 matrix together for final result
Reduce('+', lapply(split(dt$V3, dt$V1), myfun))

如果有人有更直接的方法来实现 myfun