Vfrdtyky

Here is my toy data. I have val and quartile variables q0 to q4.

 df <- tibble::tribble(

      ~val, ~q0, ~q1, ~q2,  ~q3, ~q4, ~q, ~diff,

       15L, 15L, 15L, 15L,   15, 15L, 4L,     0,

       17L,  2L, 16L, 30L,   34, 54L, 2L,    13,

       29L,  2L, 16L, 30L,   34, 54L, 2L,     1,

       25L,  2L, 17L, 20L,   26, 43L, 3L,     1 )

I need to calculate the last two variables such that:

1. When val is between q1 and q2, I pick 2 (of q2) for variable q (2nd
   row)


2. If there is a tie, I pick the max of qs (eg. q = 4 in 1st row)


3. diff is the difference between q and val. So, for row 1, it's q4-val = 0 and for row 2, it's q2 - val = 30 - 17 = 13.

How can I calculate q and diff in R, preferably using tidyverse? May be we can leverage answers here: Extract column name and specific value based on a condition.

When you have more complicated logic like this, I find it's usually better to wrap it in a function. It will be easier to maintain, read, and debug in the future. I'd also be extra careful when using a lot of nested ifelse kind of statements or a big case_when type of thing. In the accepted answer q can only be 2, 3, or 4. There is no case provided for q to be 1, which you certainly want as an option in your final product.

df <- tibble::tribble(

~val, ~q0, ~q1, ~q2,  ~q3, ~q4, ~q, ~diff,

15L, 15L, 15L, 15L,   15, 15L, 4L,     0,

17L,  2L, 16L, 30L,   34, 54L, 2L,    13,

29L,  2L, 16L, 30L,   34, 54L, 2L,     1,

25L,  2L, 17L, 20L,   26, 43L, 3L,     1 )



whichQ <- function(df, qs = c('q0', 'q1', 'q2', 'q3', 'q4')) {

    # This has the flexibility of changing your column names / using more or less Q splits

    qDf <- df[, qs]

    # This finds the right quantile by finding how many you are larger than

    # It works because the q's are sequential

    whichGreater <- df$val >= qDf

    q <- apply(whichGreater, 1, sum)

    # 4 is a special case because there is no next quantile

    q <- ifelse(q == 5, 4, q)

    df$q <- q

    # Go through the Qs we found and grab the value of that column

    diff <- sapply(seq_along(q), function(x) {

        as.integer(qDf[x, q[x]+1])

    })

    # Get the difference

    df$diff <- diff - df$val

    df

}

You can still use this with tidyverse piping, but it is more clear (I think) what's happening as long as you name your function something useful.

df %>% 

    whichQ %>% 

    head(2)

Try out:

library(tidyverse)

df <- tribble(

        ~val, ~q0, ~q1, ~q2,  ~q3, ~q4,

        15L, 15L, 15L, 15L,   15, 15L,

        17L,  2L, 16L, 30L,   34, 54L,

        29L,  2L, 16L, 30L,   34, 54L,

        25L,  2L, 17L, 20L,   26, 43L)



df %>%

        mutate(q = ifelse(val > q1 & val < q2, 2,

                          ifelse(val == q0 & val == q1 & val == q2 & val == q3 & val == q4, 4,

                                 3)),

               diff = ifelse(val > q1 & val < q2, q2 - val,

                             ifelse(val == q0 & val == q1 & val == q2 & val == q3 & val == q4, q4 - val,

                                    q3 - val)))

# A tibble: 4 x 8

    val    q0    q1    q2    q3    q4     q  diff

  <int> <int> <int> <int> <dbl> <int> <dbl> <dbl>

1    15    15    15    15    15    15     4     0

2    17     2    16    30    34    54     2    13

3    29     2    16    30    34    54     2     1

4    25     2    17    20    26    43     3     1

With case_when (assuming that when val is between q2 and q3, you pick 3).

df %>%

        mutate(q = case_when(val > q1 & val < q2  ~ 2,

                             val == q0 & val == q1 & val == q2 & val == q3 & val == q4 ~ 4,

                             val > q2 & val < q3 ~ 3),

               diff = case_when(val > q1 & val < q2 ~ q2 - val,

                                val == q0 & val == q1 & val == q2 & val == q3 & val == q4 ~ q4 - val,

                                val > q2 & val < q3 ~ as.integer(q3 - val)))

# A tibble: 4 x 8

    val    q0    q1    q2    q3    q4     q  diff

  <int> <int> <int> <int> <dbl> <int> <dbl> <int>

1    15    15    15    15    15    15     4     0

2    17     2    16    30    34    54     2    13

3    29     2    16    30    34    54     2     1

4    25     2    17    20    26    43     3     1