代码之家 › 专栏 › 技术社区 › Omry Atia

矢量查找数据帧行的邻居

vectorization dataframe r

Omry Atia · 技术社区 · 7 年前

我有两个数据帧,下面是每个数据帧的一个小示例:

df1 <- data.frame(a1= c(3,4), a2 = c(8, 8), a3 = c(4, 18), a4 = c(9,9), a5 = c(17, 30))

df2 <- data.frame(a1 = c(2,2,2,3,3,3,4,4,4), a2 = c(7,7,7,7,7,7,7,7,7), 
                 a3 = c(4,4,4,4,4,4,4,4,4), a4 = c(10,10,10, 10, 10, 10, 10,10,10), 
                 a5 = c(15,16,17, 15, 16, 17, 15, 16, 17))

df1 df2 在每列中 (绝对值)。例如,第2行 df2型 是第1排的邻居 df1型 .

sweep(as.matrix(df2), 2, as.matrix(df1[1,]), "-")

对于第1行 注意,df2和df1的行数不同。

但是,我真正希望的是避免“按行”执行此操作,因为我的数据帧有许多行。有没有一种方法可以矢量化的实现呢?

4 回复 | 直到 7 年前

Jim Chen 7 年前

您可以使用拆分您的行 df1 输入列表,然后使用 lapply 要实现矢量化:

my_list=lapply(as.list(data.frame(t(df1))),function(x) sweep(as.matrix(df2), 2, as.matrix(x), "-"))

my_list 是中每行的计算结果 df1型

my_list[[1]]
      a1 a2 a3 a4 a5
 [1,] -1 -1  0  1 -2
 [2,] -1 -1  0  1 -1
 [3,] -1 -1  0  1  0
 [4,]  0 -1  0  1 -2
 [5,]  0 -1  0  1 -1
 [6,]  0 -1  0  1  0
 [7,]  1 -1  0  1 -2
 [8,]  1 -1  0  1 -1
 [9,]  1 -1  0  1  0

parallel::mclapply 比传统的要快

chinsoon12 7 年前

这里有一个可能的 data.table 使用非等连接的方法

library(data.table)
cols <- names(df2)

#convert into data.table and add row index for clarity
setDT(df1)[, rn1 := .I]
setDT(df2)[, rn2 := .I]

#create a lower (-1) and upper (+1) bound on each column
bandsNames <- paste0(rep(cols, each=2L), "_", rep(c("lower", "upper"), length(cols)))
df2Bands <- df2[, 
    {
        ans <- do.call(cbind, lapply(.SD, function(x) outer(x, c(-1L, 1L), `+`)))
        setnames(data.table(ans), bandsNames)
    }, by=.(rn2)]

#create the non-equi join conditions
lowerLimits <- paste0(cols, "_lower<=", cols)
upperLimits <- paste0(cols, "_upper>=", cols)

#perform the non-equi join on lower and upper limits and return the count
#`:=` add a new column in df1 by reference
df1[, Count := 
        df2Bands[df1, .N, by=.EACHI, on=c(lowerLimits, upperLimits)]$N
    ]

期望输出:

   a1 a2 a3 a4 a5 rn1 Count
1:  3  8  4  9 17   1     6
2:  4  8 18  9 30   2     0

如果还要查找匹配的行:

df2Bands[df1, .(rn1=i.rn1, rn2=x.rn2), by=.EACHI, on=c(lowerLimits, upperLimits)][, 
    -(1L:length(bandsNames))]

Aaron Hayman 7 年前

我不认为有一个很好的方法来完全矢量化这个问题,(应用家庭实际上只是一个蝴蝶结循环)。但是你可以按列来做,而不是按行。如果需要进一步改进,则可以在每列之后通过删除可以从匹配中排除的行来减小问题的大小(这将导致索引问题,但相对来说是可行的)。

下面是我的尝试,它使用for循环(可以用lappy代替)。它返回一个真值矩阵,带1的行可以与带1的列相匹配,这就给出了邻域的配对。

col_comp = function(x,y)
{
    lx = length(x)
    ly = length(y)
    return(abs(rep(x,ly) - rep(y,each = lx) )<=1)
}

full_comp=function(df1,df2)
{
    rows1 = seq_len(nrow(df1))
    rows2 = seq_len(nrow(df2))
    M = matrix(1L, nrow=nrow(df1),ncol=nrow(df2))
    for(i in seq_len(ncol(df1)) )
    {
        matches = col_comp(df1[rows1,i],df2[rows2,i])

        M = M*matches
    }
    return(M)
}

krads 7 年前

library(sqldf) :

library(sqldf)

sqldf( "select df2.*, df1.rowid as df1_idx
        from df2 left join df1
           on df2.a1 between df1.a1-1 and df1.a1+1
          and df2.a2 between df1.a2-1 and df1.a2+1
          and df2.a3 between df1.a3-1 and df1.a3+1
          and df2.a4 between df1.a4-1 and df1.a4+1
          and df2.a5 between df1.a5-1 and df1.a5+1")

  a1 a2 a3 a4 a5 df1_idx
1  2  7  4 10 15     NA
2  2  7  4 10 16      1
3  2  7  4 10 17      1
4  3  7  4 10 15     NA
5  3  7  4 10 16      1
6  3  7  4 10 17      1
7  4  7  4 10 15     NA
8  4  7  4 10 16      1
9  4  7  4 10 17      1

编辑以显示任意列数的解决方案:

library(sqldf)

cnames <- colnames(df1)

# main body of your sql
sql_main <- "select df2.*, df1.rowid as df1_idx
            from df2 left join df1
            on 1=1"

# join conditions (which will be added to above)
join_conditions <- 
  paste0( ' and df2.', cnames, ' BETWEEN df1.', cnames, '-1',
                                   ' AND df1.', cnames, '+1',
          collapse = '')

sql <- paste(sql_main, join_conditions)

sqldf(sql)