for each row in a data frame print column name that contains value less than or equal to x and calculate remaining value until next greatest value

Question

I am looking for a way to return the first column name that is greater than or equal to x but less than the next greatest value in each row of a data frame in a new column

> df <- data.frame(Loc = c("3120", "3120", "3120"), fld = c("T1", "T2", "T3"), days = c(13, 11, 18), VE = c(10,10,10), VC = c(15,15,15), V1 = c(20,20,20)
+ )
> df
   Loc fld days VE VC V1
1 3120  T1   13 10 15 20
2 3120  T2   11 10 15 20
3 3120  T3   18 10 15 20

based on Loc and fld, I want to take the values of days and find closest value in VE:V1 and print the column name of that closest value in a new column, and then calculate remaining until next greatest value.

  Loc fld days VE VC V1 current.growth.stage days.to.next.stage
1 3120  T1   13 10 15 20                   VE                  2
2 3120  T2   11 10 15 20                   VE                  4
3 3120  T3   18 10 15 20                   VC                  2

I've seen multiple threads on using min and max values but not a list of values from a column selected in a df for reference. any help would be appreciated!

thanks.

ML

score 0 · Answer 1 · answered Mar 16 '20 at 19:51

You can use apply with the required columns like this:

df$current <- apply(df[3:6], 1, function(x) names(df)[3 + which.max(which(x[2:4] < x[1]))])
df$next_stage <- apply(df[3:6], 1, function(x) (x[2:4] - x[1])[x[2:4] - x[1] > 0][1])
df
#>    Loc fld days VE VC V1 current next_stage
#> 1 3120  T1   13 10 15 20      VE          2
#> 2 3120  T2   11 10 15 20      VE          4
#> 3 3120  T3   18 10 15 20      VC          2

^{Created on 2020-03-16 by the reprex package (v0.3.0)}

score 0 · Answer 2 · answered Mar 16 '20 at 19:53

With tidyverse you could do:

library(tidyverse)

df %>%
  pivot_longer(cols = c(VE, VC, V1), names_to = "stage", values_to = "stage_val") %>%
  group_by(Loc, fld) %>%
  mutate(current.growth.stage = stage[findInterval(days, stage_val)],
         next.stage = stage[findInterval(days, stage_val) + 1],
         days.to.next.stage = stage_val[stage == next.stage] - days) %>%
  filter(stage == current.growth.stage) %>%
  select(-c(stage, next.stage, stage_val)) %>%
  right_join(df)

Output

# A tibble: 3 x 8
# Groups:   Loc, fld [3]
  Loc   fld    days current.growth.stage days.to.next.stage    VE    VC    V1
  <fct> <fct> <dbl> <chr>                             <dbl> <dbl> <dbl> <dbl>
1 3120  T1       13 VE                                    2    10    15    20
2 3120  T2       11 VE                                    4    10    15    20
3 3120  T3       18 VC                                    2    10    15    20

for each row in a data frame print column name that contains value less than or equal to x and calculate remaining value until next greatest value

2 Answers2