Relate lat and long of both dataframes in an R code

[AntPedro]

See I have two dataframes, both with lat and long. My idea is to compare lat and long of both dataframes. If you see, for example, we have for Market1 of dataframe x, latitude equal to -22.89290 and longitude equal to -48.45048, which is similar (not equal) to the third line of dataframe y, which has a latitude of -22.89287 and a longitude of -48.45075. You can see that this happens in the other Markets. Note that the first 5 numbers are the same, in this case it was -22.892 for lat and -48.450 for long. Therefore, if they are the same, insert the names of the Marketname column of the dataframe x into the Marketname column of the dataframe y, as shown in Expected output.

x<-structure(list(Latitude = c(-22.8928950233225, -22.929618895716, 
-22.8773751675936), Longitude = c(-48.4504779883472, -48.4515412645053, 
-48.4364903609011), Marketname = c("Market1", "Market2", 
"Market3")), row.names = c(NA, 3L), class = "data.frame")

> x
   Latitude Longitude Marketname
1 -22.89290 -48.45048    Market1
2 -22.92962 -48.45154    Market2
3 -22.87738 -48.43649    Market3


y<- structure(list(lat = c(-22.8715263, -22.8774825, -22.8928723, 
-22.9295906), lng = c(-48.440335, -48.4364964, -48.4507477, -48.4516264
), Marketname = c("Market0","0", "0", "0")), row.names = c(NA, 4L), class = "data.frame")

 > y   
        lat       lng Marketname
1 -22.87153 -48.44033    Market0
2 -22.87748 -48.43650          0
3 -22.89287 -48.45075          0
4 -22.92959 -48.45163          0

Expected output

 > y   
            lat       lng Marketname
    1 -22.87153 -48.44033    Market0
    2 -22.87748 -48.43650    Market3
    3 -22.89287 -48.45075    Market1      
    4 -22.92959 -48.45163    Market2

[FJCC]

This almost gets you what you want. I rounded the latitude and longitude to three decimal places and joined the two data sets on those columns. Market1 does not appear in the final data frame because the longitude in x is -48.45048 and in y it is -48.4507 and they do not match when rounded to three decimal places. Maybe a variation on this will get you exactly what you need.

x<-structure(list(Latitude = c(-22.8928950233225, -22.929618895716, 
                               -22.8773751675936), 
                  Longitude = c(-48.4504779883472, 
                                -48.4515412645053, -48.4364903609011), 
                  Marketname = c("Market1", "Market2", "Market3")), 
             row.names = c(NA, 3L), class = "data.frame")

y<- structure(list(lat = c(-22.8715263, -22.8774825, -22.8928723, 
                           -22.9295906), 
                   lng = c(-48.440335, -48.4364964, -48.4507477, -48.4516264), 
                   Marketname = c("Market0","0", "0", "0")), 
              row.names = c(NA, 4L), class = "data.frame")

library(dplyr)

x <- x |> mutate(LatApprx = round(Latitude, 3),
                 LngApprx = round(Longitude, 3))
y <- y |> mutate(LatApprx = round(lat, 3),
                 LngApprx = round(lng, 3))
New <- left_join(y, x, by = c("LatApprx", "LngApprx"))
New
#>         lat       lng Marketname.x LatApprx LngApprx  Latitude Longitude
#> 1 -22.87153 -48.44033      Market0  -22.872  -48.440        NA        NA
#> 2 -22.87748 -48.43650            0  -22.877  -48.436 -22.87738 -48.43649
#> 3 -22.89287 -48.45075            0  -22.893  -48.451        NA        NA
#> 4 -22.92959 -48.45163            0  -22.930  -48.452 -22.92962 -48.45154
#>   Marketname.y
#> 1         <NA>
#> 2      Market3
#> 3         <NA>
#> 4      Market2
New <- New |> mutate(Marketname.x = ifelse(Marketname.x == "0", 
                                           Marketname.y, Marketname.x)) |> 
  select(lat, lng, Marketname = Marketname.x)
New
#>         lat       lng Marketname
#> 1 -22.87153 -48.44033    Market0
#> 2 -22.87748 -48.43650    Market3
#> 3 -22.89287 -48.45075       <NA>
#> 4 -22.92959 -48.45163    Market2

^{Created on 2023-02-11 with reprex v2.0.2}

[AntPedro]

Thank you so much! @FJCC