Difference in values between different columns satisfying a given condition

user_6762788 0
Viewed 87

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.

tidyverse r
2 Answers

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
user_85800930

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)
user_58317310
Related