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
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
after this, is it possible to split the dataframe into list such that the first 5 columns in one dataframe of list and next 6 to 10 columns in another dataframe of a the list and so on ?!
@Kumar I'm not entirely sure what you mean, do you mean without using the do.call(cbind) call? That will give you a list with data frames.
Yes i am looking that it gives us a list with dataframes