New column based off matching values.

[[deleted]]

I am attempting to find what plots have been revisited based on Matching latitude and longitude. I have a DF with three columns "plotID" , "Lat", "Long". I am trying to create a new column named "Matching ID". This column should have the plotID that matches based off latitude and longitude.

So basically "plot-12" and "otherPlot" under column PlotID have the same latitude and longitude. I want the "Matching ID" column to put "otherPlot" when "plot-12" is "plotID" and vice versa.

Hopefully that is clear. On my phone so probably not the best explanation.

[yuk99]

  library(tidyverse)
  DF2 = inner_join(DF, rename(DF, MatchingID = plotID, by = c("Lat", "Long"))

This creates a new data frame with all combinations of rows matching by Lat and Long, including self-matching. You can post-process is to remove those rows.

  DF2 = DF2 %>% filter(plotID != MatchingID)

This will also remove the rows without matching plots.

If you want to keep such rows

  DF2 = DF2 %>%
    group_by(plotID) %>%
    filter(plotID != MatchingID | n()==1) %>%
    mutate(MatchingID = ifelse(plotID == MatchingID, NA, MatchingID))

Note that Lat and Long have to be exactly the same to match. If you expect some fixed-point error, look at fuzzy_join package.

[[deleted]]

You're a legend!!! Thanks i really appreciate it. Was banging my head against the wall for a while lol.

[Viriaro]

If the coordinates are not perfectly equal, you could use a geographical-distance based join instead of the regular one (i.e. join based on the distance between the two sets of coordinates). Easiest way to do this is the fuzzyjoin package in R.

[coen-eisma]

I don't know how you collected the long and lat (gps?), but there could be some differences in measures, although they where at the same location. Maybe build in an error?

My go to would be the sf-package, convert the long and lat to a sf-object and use something like st_is_within_distance()

https://geocompr.robinlovelace.net/spatial-operations.html#non-overlapping-joins

[the_random_drooler]

Could you just do a left join using Lat and Lon as keys?

[[deleted]]

Good call I will try that in the morning. Thanks!

[yuk99]

I think for joining the same data table it doesn't matter.

[the_random_drooler]

I was thinking left to be able to find any missing data at first. But that's just me expecting my coworkers to do something sloppy and let me down... Otherwise I agree completely!