如何在r中按最接近的距离合并两个数据集? [英] How to merge two Dataset by closest distance in r?

问题描述

我有两个包含值和坐标的数据集A和B

I have two dataset A and B that contains values and coordonates

A:

╔═══╦════════════╦═════════════╦═════════════╗
║   ║ name       ║ x           ║ y           ║
╠═══╬════════════╬═════════════╬═════════════╣
║ 1 ║ city       ║ 50.3        ║ 4.2         ║
║ 2 ║ farm       ║ 14.8        ║ 8.6         ║
║ 3 ║ lake       ║ 18.7        ║ 9.8         ║
║ 3 ║ Mountain   ║ 44          ║ 9.8         ║
╚═══╩════════════╩═════════════╩═════════════╝

B:

╔═══╦════════════╦═════════════╦═════════════╗
║   ║ Temp       ║ x           ║ y           ║
╠═══╬════════════╬═════════════╬═════════════╣
║ 1 ║ 18         ║ 50.7        ║ 6.2         ║
║ 2 ║ 17,3       ║ 20          ║ 11          ║
║ 3 ║ 15         ║ 15          ║ 9           ║
╚═══╩════════════╩═════════════╩═════════════╝

我想要这个，C:

╔═══╦════════════╦═════════════╦═════════════╗
║   ║ Name       ║ Temp        ║ Distance    ║
╠═══╬════════════╬═════════════╬═════════════╣
║ 1 ║ city       ║ 18          ║ 2.039608    ║
║ 2 ║ farm       ║ 15          ║ 0.447214    ║
║ 3 ║ lake       ║ 17.3        ║ 1.769181    ║
║ 4 ║ Mountain   ║ 18          ║ 7.605919    ║
╚═══╩════════════╩═════════════╩═════════════╝

我尝试过这个:

A<- read.table(header = TRUE, text = "
    Name x y 
    city 50.3 4.2
    farm 14.8 8.6
    lake 18.7 9.8
    mountain 44 9.8")
B<- read.table(header = TRUE, text = "
    Temp x y 
    18 50.7 6.2
    17.3 20 11
    15 15 9")
C<- data.frame(Name=character(),
               Temp=numeric(),
               Distance=numeric())

for(i in 1:nrow(A)) {
  x1<- A[i,]$x
  y1<- A[i,]$y
  min = 100
  index = 0
  for(j in 1:nrow(B)) {
    x2<- B[j,]$x
    y2<- B[j,]$y
    tmp = sqrt((((x2-x1)^2)+((y2-y1)^2)))
    if (tmp < min) {
      index = j
      min = tmp
    }
  }
  df <- list(Name=A[i,]$Name, Temp=B[index,]$Temp, Distance=min)
  C <- rbind(C, df)
}
print(C)

但是我的第一个数据集大约有1,500,000行，而我的第二个数据集大约有5000行，这种算法非常慢.有更好的方法吗?

But my first dataset is about 1,500,000 rows and my second one is about 5000 and this algorythm is very very slow. Is there a better way to do it ?

推荐答案

如果您想在R中进行 hack ，则可以使用R的outer函数(并且意识到R擅长矢量化)可以有效地产生所有A[, c(x,y)]中所有元素的A[, c(x,y)]，即获取A(行)中的位置与B(列)中的每个位置(例如

If you want a hack in R, you can use R's outer-function (and the awareness that R is good at vectorization) to efficiently produce the distances of all in A[, c(x,y)] from all in B[, c(x,y)], that is, obtaining a Matrix of distances of the locations in A (row) from each of the locations in B (columns) e.g.,

A<- read.table(header = TRUE, text = "
               Name x y 
               city 50.3 4.2
               farm 14.8 8.6
               lake 18.7 9.8
               mountain 44 9.8")
B<- read.table(header = TRUE, text = "
               Temp x y 
               18 50.7 6.2
               17.3 20 11
               15 15 9
               18 ")
d <- sqrt(outer(A$x, B$x, "-")^2 + outer(A$y, B$y, "-")^2)
d

##          [,1]      [,2]       [,3]
## [1,]  2.039608 31.053663 35.6248509
## [2,] 35.980133  5.727128  0.4472136
## [3,] 32.201863  1.769181  3.7854986
## [4,]  7.605919 24.029981 29.0110324

接下来，您可以有效地通过matrixStats包

Next you can efficiently obtain its value via the rowMins-method in matrixStats package

minD <- matrixStats::rowMins(d)

并假设B中有一个唯一的最近位置，可通过d与minD

And assuming there is a unique closest location in B obtain its index via (row-wise) comparison of d to minD

ind <- (d == minD) %*% 1:ncol(d)

如果B中有多个等距的位置，则无论如何您都需要某种选择规则. 最后，只需将数据堆叠在一起.

If there are multiple equaly distanced locations in B you'll anyways need some kind of rule as to which to choose. Last, just stack the data together.

C <- data.frame(Name = A$Name,
                Temp = B$Temp[ind],
                Distance = minD)

这篇关于如何在r中按最接近的距离合并两个数据集?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！