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