Warm tip: This article is reproduced from stackoverflow.com, please click
dataframe r data-manipulation

how to subset the rows in a data frame with coordinates based on closest location in R

发布于 2020-03-28 23:14:10

I have a large data frame (150000 rows) with X and Y as coordinates like df1 as follows:

df1 <- data.frame(X = c(7.48, 7.82, 8.15, 8.47, 8.80, 9.20, 9.51, 9.83, 10.13, 10.59, 7.59, 8.06, 8.39, 8.87, 9.26, 9.64, 10.09, 10.48, 10.88, 11.45), 
              Y = c(49.16, 48.78, 48.40, 48.03, 47.65, 47.24, 46.87, 46.51, 46.15, 45.73, 48.70, 48.18, 47.72, 47.20, 46.71, 46.23, 45.72, 45.24, 44.77, 44.23), 
              ID = c("B_1", "B_1", "B_1", "B_1", "B_1", "B_1", "B_1", "B_1", "B_1", "B_1", "B_1_2", "B_1_2", "B_1_2", "B_1_2", "B_1_2", "B_1_2", "B_1_2", "B_1_2", "B_1_2", "B_1_2"), 
              TI = c(191.31, 191.35, 191.39, 191.44, 191.48, 191.52, 191.56, 191.60, 191.64, 191.69, 1349.93, 1349.97, 1350.01, 1350.05, 1350.09, 1350.14, 1350.18, 1350.22, 1350.26, 1350.30))

in ID column, i have some 100-200 unique ID's and in each unique ID, i have 200-300 data points (rows)

i have another data frame like df2 as follows:

df2 <- data.frame(X = c(7.62,  8.25,  8.95,  9.71,  10.23), 
              Y = c(49.06,  48.30,  47.55,  46.77,  46.25))

now, based on each row in df2 i.e. x1 and y1, I would like to find out nearest XY in df1 with respect to a unique ID shown as:

 df3 <- 

 X1    Y1   ID1     TI1     X2     Y2    ID2     TI2     X3     Y3    ID3     TI3    X4     Y4    ID4     TI4     X5      Y5       ID5      TI5
7.48 49.16  B_1    191.31  8.15  48.40   B_1    191.39  8.80  47.65   B_1    191.48  9.51  46.87  B_1    191.56   10.13   46.15   B_1     191.64
7.59 48.70  B_1_2  1349.93 8.06  48.18   B_1_2  1349.97 8.87  47.20   B_1_2  1350.05 9.26  46.71  B_1_2  1350.09  10.09   45.72   B_1_2   1350.18

i have tried with the following code:

dist12 <- function(row){
dists <- (row[["X"]] - df2$X)^2 + (row[["Y"]]- df2$Y)^2
return(cbind(df2[which.min(dists),], distance = min(dists)))
}
df3 <- cbind(df1, do.call(rbind, lapply(1:nrow(df1), function(x) dist12(df1[x,])))) 

the code is finding the minimum distance between rows by calculating the distance between rows in df1 and df2 dataframes and combining both df1 and df2. from this code, it is assigning a single XY of df2 to multiple rows in df1. but a single row (XY) in df2 can be assigned only to one of the rows in a unique ID.

looking for the code to get the expected output (df3) as presented above

thanks in advance

Questioner
Kumar
Viewed
17
user2974951 2020-01-31 18:01

Here is a taste with data.table for performance.

    library(data.table)
    df1=as.data.table(df1)
    do.call(cbind,
      apply(df2,1,function(i){
        df1[,d:=(df1$X-i[1])^2+(df1$Y-i[2])^2]
        df1[df1[,.I[d==min(d)],by=ID]$V1]
      })
    )

      X     Y    ID      TI      d    X     Y    ID      TI      d    X     Y    ID      TI      d
1: 7.48 49.16   B_1  191.31 0.0296 8.15 48.40   B_1  191.39 0.0200 8.80 47.65   B_1  191.48 0.0325
2: 7.59 48.70 B_1_2 1349.93 0.1305 8.06 48.18 B_1_2 1349.97 0.0505 8.87 47.20 B_1_2 1350.05 0.1289
      X     Y    ID      TI      d     X     Y    ID      TI      d
1: 9.51 46.87   B_1  191.56 0.0500 10.13 46.15   B_1  191.64 0.0200
2: 9.26 46.71 B_1_2 1350.09 0.2061 10.09 45.72 B_1_2 1350.18 0.3005