I am trying to merge two data frames (B to A) by Latitude and Longitude.
B has more coordinates than A, but not always the same. I want to merge Var2 (Variable in B) to the closest coordinates of A.
here you can download the two examples of data frames A and B
A
Lat Lon Var1
1 51.0433 7.00550 4.50390015
2 51.0433 7.00550 4.50390015
3 51.0433 7.00550 4.50390015
4 51.0433 7.00550 4.50390015
5 51.0433 7.00550 4.50390015
6 51.0433 7.00550 4.50390015
7 49.0003 9.00310 5.49312744
8 49.0003 9.00310 5.49312744
9 49.0003 9.00310 5.49312744
10 49.0003 9.00310 5.49312744
11 49.0003 9.00310 5.49312744
12 49.0003 9.00310 5.49312744
13 49.0003 9.00310 5.49312744
14 49.0003 9.00310 5.49312744
15 43.9953 14.07490 11.73342285
16 43.9953 14.07490 11.73342285
17 43.9953 14.07490 11.73342285
18 43.9953 14.07490 11.73342285
19 43.9953 14.07490 11.73342285
20 43.9953 14.07490 11.73342285
21 50.9870 72.02920 4.85796509
22 50.9870 72.02920 4.85796509
23 50.9870 72.02920 4.85796509
24 50.9870 72.02920 4.85796509
25 50.9870 72.02920 4.85796509
26 50.9870 72.02920 4.85796509
27 50.9870 72.02920 4.85796509
28 50.9870 72.02920 4.85796509
29 50.9870 72.02920 4.85796509
30 53.1688 81.57360 3.12514648
31 53.1688 81.57360 3.12514648
32 53.1688 81.57360 3.12514648
33 53.1688 81.57360 3.12514648
34 53.1688 81.57360 3.12514648
35 53.1688 81.57360 3.12514648
36 53.1688 81.57360 3.12514648
37 53.1688 81.57360 3.12514648
38 53.1688 81.57360 3.12514648
39 46.9196 38.11720 7.18578491
40 46.9196 38.11720 7.18578491
41 46.9196 38.11720 7.18578491
42 46.9196 38.11720 7.18578491
43 46.9196 38.11720 7.18578491
44 46.9196 38.11720 7.18578491
45 46.9196 38.11720 7.18578491
46 46.9196 38.11720 7.18578491
47 46.9196 38.11720 7.18578491
48 46.9196 38.11720 7.18578491
49 54.8519 95.76970 5.39217529
50 54.8519 95.76970 5.39217529
51 54.8519 95.76970 5.39217529
52 54.8519 95.76970 5.39217529
53 54.8519 95.76970 5.39217529
54 54.8519 95.76970 5.39217529
55 54.8519 95.76970 5.39217529
56 54.8519 95.76970 5.39217529
57 54.8519 95.76970 5.39217529
58 54.8519 95.76970 5.39217529
59 53.1975 118.12050 6.26061401
60 53.1975 118.12050 6.26061401
61 53.1975 118.12050 6.26061401
62 53.1975 118.12050 6.26061401
63 53.1975 118.12050 6.26061401
64 53.1975 118.12050 6.26061401
65 53.1975 118.12050 6.26061401
66 53.1975 118.12050 6.26061401
67 53.1975 118.12050 6.26061401
68 53.1975 118.12050 6.26061401
69 46.9097 141.92540 12.63280640
70 46.9097 141.92540 12.63280640
71 46.9097 141.92540 12.63280640
72 46.9097 141.92540 12.63280640
73 46.9097 141.92540 12.63280640
74 46.9097 141.92540 12.63280640
75 46.9097 141.92540 12.63280640
76 46.9097 141.92540 12.63280640
77 46.9097 141.92540 12.63280640
78 46.3989 150.40660 13.14498291
79 46.3989 150.40660 13.14498291
80 46.3989 150.40660 13.14498291
81 46.3989 150.40660 13.14498291
82 46.3989 150.40660 13.14498291
83 46.3989 150.40660 13.14498291
84 46.3989 150.40660 13.14498291
85 46.3989 150.40660 13.14498291
86 46.3989 150.40660 13.14498291
87 53.5626 149.25570 9.39232788
88 53.5626 149.25570 9.39232788
89 53.5626 149.25570 9.39232788
90 53.5626 149.25570 9.39232788
91 53.5626 149.25570 9.39232788
92 53.5626 149.25570 9.39232788
93 53.5626 149.25570 9.39232788
94 53.5626 149.25570 9.39232788
95 59.6004 148.57150 4.83889160
96 59.6004 148.57150 4.83889160
97 59.6004 148.57150 4.83889160
98 59.6004 148.57150 4.83889160
99 59.6004 148.57150 4.83889160
100 59.6004 148.57150 4.83889160
101 59.6004 148.57150 4.83889160
102 59.6004 148.57150 4.83889160
103 59.6004 148.57150 4.83889160
104 67.1029 144.93680 -0.09119263
105 67.1029 144.93680 -0.09119263
106 67.1029 144.93680 -0.09119263
107 67.1029 144.93680 -0.09119263
108 67.1029 144.93680 -0.09119263
109 67.1758 145.73110 -0.13779297
110 67.1758 145.73110 -0.13779297
111 67.1758 145.73110 -0.13779297
112 67.1758 145.73110 -0.13779297
113 67.1758 145.73110 -0.13779297
114 67.1758 145.73110 -0.13779297
115 67.1758 145.73110 -0.13779297
116 67.1758 145.73110 -0.13779297
117 66.0010 159.00300 0.89107666
118 66.0010 159.00300 0.89107666
119 66.0010 159.00300 0.89107666
120 66.0010 159.00300 0.89107666
121 66.0010 159.00300 0.89107666
122 66.0010 159.00300 0.89107666
123 66.0010 159.00300 0.89107666
124 66.0010 159.00300 0.89107666
125 66.0010 159.00300 0.89107666
126 67.2913 163.54500 0.99764404
127 67.2913 163.54500 0.99764404
128 67.2913 163.54500 0.99764404
129 67.2913 163.54500 0.99764404
130 67.2913 163.54500 0.99764404
131 67.1004 167.39210 1.38265381
132 67.1004 167.39210 1.38265381
133 67.1004 167.39210 1.38265381
134 67.1004 167.39210 1.38265381
135 67.1004 167.39210 1.38265381
136 67.1004 167.39210 1.38265381
137 67.1004 167.39210 1.38265381
138 67.1004 167.39210 1.38265381
139 67.1004 167.39210 1.38265381
140 69.7430 -165.01980 0.48250732
141 69.7430 -165.01980 0.48250732
142 69.7430 -165.01980 0.48250732
143 69.7430 -165.01980 0.48250732
144 69.7430 -165.01980 0.48250732
145 69.7430 -165.01980 0.48250732
146 70.1790 -159.07960 0.37203369
147 70.1790 -159.07960 0.37203369
148 70.1790 -159.07960 0.37203369
149 70.1790 -159.07960 0.37203369
150 70.1790 -159.07960 0.37203369
151 70.1790 -159.07960 0.37203369
152 71.6929 -143.72450 0.49062500
153 71.6929 -143.72450 0.49062500
154 71.6929 -143.72450 0.49062500
155 71.6929 -143.72450 0.49062500
156 71.6929 -143.72450 0.49062500
157 71.6929 -143.72450 0.49062500
158 71.6929 -143.72450 0.49062500
159 71.6929 -143.72450 0.49062500
160 71.6929 -143.72450 0.49062500
161 70.0017 -115.50930 1.58559570
162 70.0017 -115.50930 1.58559570
163 70.0017 -115.50930 1.58559570
164 70.0017 -115.50930 1.58559570
165 70.0017 -115.50930 1.58559570
166 70.0017 -115.50930 1.58559570
167 70.0017 -115.50930 1.58559570
168 68.7398 -99.99770 1.71126709
169 68.7398 -99.99770 1.71126709
170 68.7398 -99.99770 1.71126709
171 68.7398 -99.99770 1.71126709
172 68.7398 -99.99770 1.71126709
173 68.7398 -99.99770 1.71126709
174 68.7398 -99.99770 1.71126709
175 63.9696 -66.26970 3.79689941
176 63.9696 -66.26970 3.79689941
177 63.9696 -66.26970 3.79689941
178 63.9696 -66.26970 3.79689941
179 63.9696 -66.26970 3.79689941
180 63.9696 -66.26970 3.79689941
181 62.5028 -67.99870 4.08593140
182 62.5028 -67.99870 4.08593140
183 62.5028 -67.99870 4.08593140
184 62.5028 -67.99870 4.08593140
185 62.5028 -67.99870 4.08593140
186 62.5028 -67.99870 4.08593140
187 59.5945 -67.91980 6.22908936
188 59.5945 -67.91980 6.22908936
189 59.5945 -67.91980 6.22908936
190 59.5945 -67.91980 6.22908936
191 59.5945 -67.91980 6.22908936
192 59.5945 -67.91980 6.22908936
193 54.9994 -55.01030 6.70095215
194 54.9994 -55.01030 6.70095215
195 54.9994 -55.01030 6.70095215
196 54.9994 -55.01030 6.70095215
197 54.9994 -55.01030 6.70095215
198 54.9994 -55.01030 6.70095215
199 54.9994 -55.01030 6.70095215
200 54.9994 -55.01030 6.70095215
201 46.1788 50.99740 7.05062256
202 46.1788 50.99740 7.05062256
203 46.1788 50.99740 7.05062256
204 46.1788 50.99740 7.05062256
205 46.1788 50.99740 7.05062256
206 46.1788 50.99740 7.05062256
207 46.1788 50.99740 7.05062256
208 46.1788 50.99740 7.05062256
209 46.1788 50.99740 7.05062256
210 57.0016 -27.98490 2.51125488
211 57.0016 -27.98490 2.51125488
212 57.0016 -27.98490 2.51125488
213 57.0016 -27.98490 2.51125488
214 57.0016 -27.98490 2.51125488
215 57.0016 -27.98490 2.51125488
216 57.0016 -27.98490 2.51125488
217 59.5050 -21.01120 2.34374390
218 59.5050 -21.01120 2.34374390
219 59.5050 -21.01120 2.34374390
220 59.5047 -21.01120 2.34374390
221 59.5047 -21.01120 2.34374390
222 59.5047 -21.01120 2.34374390
223 58.6653 -14.01210 2.32515869
224 58.6653 -14.01210 2.32515869
225 58.6653 -14.01210 2.32515869
226 58.6653 -14.01210 2.32515869
227 58.6653 -14.01210 2.32515869
228 58.6653 -14.01210 2.32515869
229 58.6653 -14.01210 2.32515869
230 58.6653 -14.01210 2.32515869
231 57.5052 -7.01200 2.48827515
232 57.5052 -7.01200 2.48827515
233 57.5052 -7.01200 2.48827515
234 57.5052 -7.01200 2.48827515
235 57.5052 -7.01200 2.48827515
236 57.5052 -7.01200 2.48827515
237 54.6662 0.95060 2.67751465
238 54.6662 0.95060 2.67751465
239 54.6662 0.95060 2.67751465
240 54.6662 0.95060 2.67751465
241 54.6662 0.95060 2.67751465
242 54.3613 -44.05360 5.01952515
243 54.3613 -44.05360 5.01952515
244 54.0752 -41.84980 4.65157471
245 54.0752 -41.84980 4.65157471
246 53.8058 -39.71940 4.19265137
247 53.8058 -39.71940 4.19265137
248 53.5361 -37.62340 4.25060425
249 53.5361 -37.62340 4.25060425
250 56.3940 -32.75140 3.11434326
251 56.3940 -32.75140 3.11434326
252 59.1970 -27.45530 2.05141602
253 59.1970 -27.45530 2.05141602
254 59.4700 -27.29500 1.94997559
255 59.4700 -27.29500 1.94997559
256 57.1560 -26.76000 2.27858887
257 57.1560 -26.76000 2.27858887
258 59.4990 -26.75560 1.92077026
259 59.4990 -26.75560 1.92077026
260 57.1480 -26.74740 2.27858887
261 57.1480 -26.74740 2.27858887
262 57.8740 -26.70370 2.22969971
263 57.8740 -26.70370 2.22969971
265 44.5039 34.72199 10.37688599
266 54.5159 105.32550 4.65828857
267 54.4008 106.48450 4.68609009
268 54.3210 107.26310 4.89766846
269 54.2017 108.43780 4.93593750
270 54.0817 109.56160 4.96218262
271 53.9754 110.65200 4.91951904
272 53.6460 113.82950 5.53890381
273 53.7653 112.67000 5.28905640
274 53.6561 113.73430 5.53890381
275 53.4512 115.71390 5.91890869
276 44.5497 35.46233 10.32637939
277 53.2351 117.78610 6.21013794
278 53.2356 118.21320 6.31453857
279 52.9897 119.00230 6.52407227
280 53.1567 81.63600 3.13704834
281 52.4607 121.02230 6.73201904
282 52.2818 121.71480 6.90029297
283 52.0148 122.74850 7.31701050
284 51.7518 123.77290 7.96468506
285 51.4832 124.78950 8.55153809
286 51.2135 125.81960 8.87984619
287 45.0477 36.36336 9.43953857
288 50.9435 126.80600 9.26345215
289 50.6638 127.89210 9.72094116
290 50.3760 128.96440 9.91079102
291 50.0950 130.01550 10.24404297
292 49.7960 131.12780 10.44576416
293 49.4981 132.20310 10.62108765
294 49.2081 133.24690 10.71358643
295 48.9837 134.08710 10.88967285
296 48.7092 134.97090 11.15889893
297 48.4701 135.93450 11.47109375
298 45.1132 37.25443 9.05965576
299 48.1951 136.93140 11.81875000
300 48.0098 137.57770 12.05001221
301 47.7432 138.52710 12.29247437
302 47.4967 139.48660 12.44701538
303 47.2811 140.47880 12.45589600
304 47.0598 141.43180 12.61531982
305 45.7843 149.85110 13.94515381
306 46.3961 150.40280 13.14498291
307 46.7305 150.39080 12.99843750
308 47.4459 150.39500 12.56594238
309 44.9662 38.18105 9.08168945
310 48.1570 150.39880 12.16719971
311 49.5258 150.18710 11.80562744
312 54.1705 149.27390 8.51641235
313 54.9380 149.14110 7.76015625
314 56.3637 148.99270 6.85482178
315 57.0661 148.92290 6.29439697
316 57.7748 148.86270 5.55141602
317 58.4625 148.75670 5.02999268
318 59.1610 148.65910 4.77373657
319 60.2970 148.32540 5.04390869
320 45.1395 38.27609 8.70797119
321 60.9713 148.03510 5.03670654
322 61.6502 147.72010 4.70717773
323 62.3239 147.41620 4.37139282
324 62.9970 147.10720 3.94497070
325 63.6637 146.78900 3.51986084
326 64.3335 146.45450 3.18468628
327 65.0054 146.10420 2.22799072
328 67.1678 144.89610 -0.12323608
329 67.1286 145.04800 -0.12323608
330 67.0928 145.01310 -0.09119263
331 45.4809 37.79397 8.40371094
332 65.1426 148.38680 1.23732910
333 64.9449 149.21520 1.49562988
B
Lat Lon Var2
1: 45.0999 36.7765 7.3705
2: 45.0999 36.7869 7.3597
3: 45.0998 36.7920 7.3597
4: 45.0998 36.7972 7.3597
5: 45.0997 36.8024 7.3597
---
70800: 36.1172 16.8092 22.7769
70801: 36.1141 16.8115 22.7696
70802: 36.1111 16.8139 22.7527
70803: 36.1079 16.8161 22.7527
70804: 36.1048 16.8184 22.7431
What I have tried to do is:
temp <- merge(A,
B,
by = c("Lat","Lon"),
all.x = T,
)
However, some coordinates of A do not match those of B. How can I match the closest coordinates?
I checked this post, trying as follow:
library(geosphere)
mat <- distm(A[,c('Lon','Lat')], B[,c('Lon','Lat')], fun=distVincentyEllipsoid)
A$VarB <- B$Var2[apply(mat, 1, which.min)]
but it's taking too much time and RStudio crashes. How can I do it?
CodePudding user response：
You have duplicate entries in A. Considering you need it for the unique Lat Lon pair of A. You can do this:
B %>%
rename(Lat_B=Lat, Lon_B=Lon) %>%
crossing(A) %>%
mutate(dist=sqrt((Lat-Lat_B)^2 (Lon-Lon_B)^2)) %>%
group_by(Lat, Lon) %>%
filter(dist==min(dist)) %>%
ungroup() %>%
select(Lat, Lon, Var1, Var2)
This output will have 379 rows which is the number of unique Lat Lon pairs in the data frame A
CodePudding user response：
A possible solution, based on power_left_join:
We can play with
tolto define an upper bound to the Euclidean distance between the pairs of latitude and longitude.Another kind of distance measure can obviously be used.
library(powerjoin)
tol <- 0.1
power_left_join(df1, df2,
by = c(~ sqrt((.x$Lat - .y$Lat)^2 (.x$Lon - .y$Lon)^2) < tol))
#> Lat.x Lon.x Var1 Lat.y Lon.y Var2
#> 1 51.0433 7.0055 4.503900 51.1097 6.9338 3.7854
#> 2 51.0433 7.0055 4.503900 51.1063 6.9374 3.7905
#> 3 51.0433 7.0055 4.503900 51.1030 6.9409 3.7874
#> 4 51.0433 7.0055 4.503900 51.0997 6.9445 3.7926