CODEWITHSUNDEEP
rtidyversepurrr
Ramiro Bentes
Ramiro Bentes

Reputation: 348

Find maximum value in list-column that is less or equal to value of another column in R

I have the following table:

tbl1 <- tibble::tribble(
~numberEvent, ~numberNovo,
"497",        "497",
"498",        "498",
"499",        "499",
"500",        "500",
"501",        "498, 506",
"502",        "502",
"503",        "503",
"504",        "504",
"505",        "505",
"506",        "506",
"507",        "498, 506")

I'm trying to create a new column with the maximum value from the column numberNovo that is less or equal to the value from the column numberEvent. Therefore, in line 5, I want to get 498 as a result, since it is the maximum value that's less than 501. In the last line, the desired result would be 506.

I have tried to turn the column numberNovo into a list column, but I have no idea how to go from there. Any help is appreciated.

Thanks in advance!

Upvotes: 1

Views: 55

Answers (2)

Ramiro Bentes
Ramiro Bentes

Reputation: 348

I figured a different way using purrr, for anyone interested:

tbl1 %>% 
     mutate(maxNovo = str_split(numberNovo, ", "),
            maxNovo = map(maxNovo , ~as.numeric(.)),
            maxNovo = map2_dbl(maxNovo , numberEvent, ~ max(.x[.x <= .y])))

Upvotes: 0

akrun
akrun

Reputation: 886938

We could use separate_rows to split up the column and then do a group by get the difference between 'numberEvent' and 'numberNovo', find the index of max difference to subset the 'numberNovo' and bind it with the original data

library(dplyr)
library(tidyr)
tbl1 %>%
    separate_rows(numberNovo, convert = TRUE) %>% 
    group_by(grp = numberEvent) %>%
    summarise(maxNovo = numberNovo[which.max(as.integer(numberEvent) - 
             numberNovo)])   %>%  
    select(maxNovo) %>% 
    bind_cols(tbl1, .)
# A tibble: 11 x 3
#   numberEvent numberNovo maxNovo
#   <chr>       <chr>        <int>
# 1 497         497            497
# 2 498         498            498
# 3 499         499            499
# 4 500         500            500
# 5 501         498, 506       498
# 6 502         502            502
# 7 503         503            503
# 8 504         504            504
# 9 505         505            505
#10 506         506            506
#11 507         498, 506       498

Upvotes: 1

Related Questions