R: find rows in data frame within range of each other across multiple columns

user_7128414 0
Viewed 427

I have a data frame that looks like this:

ID   lat   long   score
1    41.5  -62.3  22.4
2    41.0  -70.2  21.9
3    42.2  -63.0  22.7
4    36.7  -72.9  20.0
5    36.2  -62.4  24.1
6    35.8  -61.7  24.7
7    40.8  -61.9  22.1

I would like to identify rows of this data frame for which values for lat are within 1 unit of each other AND values for long are within 1 unit of each other AND values for score are within 0.7 units of each other. To indicate which rows meet these conditions, I'd like to add a new column (ID.matches) that provides values for ID for rows meeting the above conditions. The final data frame could look something like this:

ID   lat   long   score   ID.matches
1    41.5  -62.3  22.4    3, 7
2    41.0  -70.2  21.9    0
3    42.2  -63.0  22.7    1
4    36.7  -72.9  20.0    0
5    36.2  -62.4  24.1    6
6    35.8  -61.7  24.7    5
7    40.8  -61.9  22.1    1

I'm not really sure where to start with this...I think some sort of conditional function using dplyr or sapply? I'm also not sure if another data structure should be used for ID.matches, since some rows will have more than one match.

Thanks for any help!

dplyr r
2 Answers

You can check all the conditions using outer to form a logical matrix (remembering to exclude the self-matching diagonal), and apply the result to subset the ID column, pasting the result together into strings:

df$ID.matches <- apply(outer(df$lat,   df$lat,   function(x, y) abs(x - y) <   1) &
                       outer(df$lon,   df$lon,   function(x, y) abs(x - y) <   1) &
                       outer(df$score, df$score, function(x, y) abs(x - y) < 0.7) &
                       diag(nrow(df)) == 0, 
                       MARGIN = 1,
                       function(x) paste(df$ID[x], collapse = ", "))
df
#>   ID  lat  long score ID.matches
#> 1  1 41.5 -62.3  22.4       3, 7
#> 2  2 41.0 -70.2  21.9           
#> 3  3 42.2 -63.0  22.7          1
#> 4  4 36.7 -72.9  20.0           
#> 5  5 36.2 -62.4  24.1          6
#> 6  6 35.8 -61.7  24.7          5
#> 7  7 40.8 -61.9  22.1          1

Created on 2020-07-07 by the reprex package (v0.3.0)

user_125003150

A different approach using some of the tidyverse functions to filter to the rows matching the criteria and pull the IDs of the matching rows.

# Create example data
library(tidyverse)

df <- tribble(
~ID,   ~lat,   ~long,   ~score,
1,    41.5,  -62.3,  22.4,
2,    41.0,  -70.2,  21.9,
3,    42.2,  -63.0,  22.7,
4,    36.7,  -72.9,  20.0,
5,    36.2,  -62.4,  24.1,
6,    35.8,  -61.7,  24.7,
7,    40.8,  -61.9,  22.1
)


df$ID.match <- sapply(df$ID, function(x){
  
  df %>%
    filter(abs(lat- lat[ID == x]) < 1,
           abs(long - long[ID == x]) < 1,
           abs(score - score[ID == x]) < 0.7,
           ID != x) %>%
    pull(ID) %>%
    paste0(collapse = ',')
  
})


df
#> # A tibble: 7 x 5
#>      ID   lat  long score ID.match
#>   <dbl> <dbl> <dbl> <dbl> <chr>   
#> 1     1  41.5 -62.3  22.4 "3,7"   
#> 2     2  41   -70.2  21.9 ""      
#> 3     3  42.2 -63    22.7 "1"     
#> 4     4  36.7 -72.9  20   ""      
#> 5     5  36.2 -62.4  24.1 "6"     
#> 6     6  35.8 -61.7  24.7 "5"     
#> 7     7  40.8 -61.9  22.1 "1"

Edit: here is how it can be done without using sapply and $ (i.e. fully in the tidyverse framework)

df %>%
  mutate(ID.match = map_chr(ID, function(x){
    
    df %>%
      filter(abs(lat- lat[ID == x]) < 1,
             abs(long - long[ID == x]) < 1,
             abs(score - score[ID == x]) < 0.7,
             ID != x) %>%
      pull(ID) %>%
      paste0(collapse = ',')
    
  }))
user_124003850
Related