R语言 基于年份列对数据框进行子集化,最近值位于另一年份列下方,最远值位于另一年份列上方

jdzmm42g  于 2022-12-20  发布在  其他
关注(0)|答案(1)|浏览(99)

基于PipeYear的子集 Dataframe ,最近值低于PropertyYearBuilt,最远值高于PropertyYearBuilt,使用以下R代码:

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)

谢谢!

xxls0lw8

xxls0lw81#

答案与我发布的here类似(如果你有最新的dplyr更新),但这次最上面的只是按PropertyID分组的max

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

相关问题