merging using margin +-1

[Wkp]

Hi there,
I have a doubt in how merge the dataframe a with b apply a margin of +-1 to to merge by Price and Price.1

a<- data.frame(id.1 = c(1), Price = c(1200))

b<- data.frame(idc.1 = c(1,2,3,4), Price.1 = c(1201,1202,1200,1199))


result <-  data.frame(id.1 = c(1,1,1),
                idc.1 = c(1,3,4),
                Price = c(1200,1200,1200),
                Price.1= c(1201,1200,1199))

Can you please help on that?
Thanks

[pieterjanvc]

Hello,

Here is one solution by manipulating one data frame and then using joins

library(dplyr)

#Data frames
a<- data.frame(id.1 = c(1), Price = c(1200))
b<- data.frame(idc.1 = c(1,2,3,4), Price.1 = c(1201,1202,1200,1199))

#Expand a to include all values + 1 and -1 in price
a = bind_rows(
  a %>% mutate(Price.1 = Price),
  a %>% mutate(Price.1 = Price + 1),
  a %>% mutate(Price.1 = Price - 1)
) %>% distinct()
a 
#>   id.1 Price Price.1
#> 1    1  1200    1200
#> 2    1  1200    1201
#> 3    1  1200    1199

#Full join a and b
result = b %>% full_join(a, by = "Price.1") 
result
#>   idc.1 Price.1 id.1 Price
#> 1     1    1201    1  1200
#> 2     2    1202   NA    NA
#> 3     3    1200    1  1200
#> 4     4    1199    1  1200

#Remove rows when not complete
result = result[complete.cases(result),]
result
#>   idc.1 Price.1 id.1 Price
#> 1     1    1201    1  1200
#> 3     3    1200    1  1200
#> 4     4    1199    1  1200

^{Created on 2022-06-02 by the reprex package (v2.0.1)}

Hope this helps,
PJ