Subset dataframe based on PipeYear with the closest value below PropertyYearBuilt and farthest value above the PropertyYearBuilt using the following R code:
df <- read.table(text="
PipeID PricePipe PipeYear PropertyYearBuilt Distance_to_property
a 500 2010 2013 1.5
b 600 2007 2008 2.5
c 700 2009 2008 3.0
d 800 1998 2000 4.2
e 900 2003 2000 4.5
f 200 2014 2013 5.0
g 100 2011 2013 5.5
h 850 2018 2008 7.0", header = TRUE)
Thanks!
Similar answer to one I posted here (if you have latest dplyr updates), but this time the furthest above is just the max grouped by PropertyID:
library(tidyverse)
df <- read.table(text="
PipeID PricePipe PipeYear PropertyYearBuilt Distance_to_property
a 500 2010 2013 1.5
b 600 2007 2008 2.5
c 700 2009 2008 3.0
d 800 1998 2000 4.2
e 900 2003 2000 4.5
f 200 2014 2013 5.0
g 100 2011 2013 5.5
h 850 2018 2008 7.0", header = TRUE) |>
mutate(PropertyID = as.numeric(as.factor(PropertyYearBuilt)))
bind_rows(
df |>
select(PropertyYearBuilt, PropertyID) |>
unique() |>
left_join(
df |> select(-PropertyYearBuilt),
join_by(PropertyID, closest(PropertyYearBuilt >= PipeYear))
),
df |>
group_by(PropertyYearBuilt) |>
filter(PipeYear == max(PipeYear))
) |>
arrange(PropertyID, PipeYear)
#> PropertyYearBuilt PropertyID PipeID PricePipe PipeYear Distance_to_property
#> 1 2000 1 d 800 1998 4.2
#> 2 2000 1 e 900 2003 4.5
#> 3 2008 2 b 600 2007 2.5
#> 4 2008 2 h 850 2018 7.0
#> 5 2013 3 g 100 2011 5.5
#> 6 2013 3 f 200 2014 5.0