CODEWITHSUNDEEP
rdataframequantile
B-Z
B-Z

Reputation: 83

How to create a column with values based on specific quantiles calculated from a conditional argument?

Everything is in the title. To illustrate, I constructed the following example.

I have the following data frame:

date <- c("01.02.2011","01.02.2011","01.02.2011","01.02.2011","01.02.2011","01.02.2011",
          "01.02.2011","01.02.2011","01.02.2011","01.02.2011",
          "02.02.2011","02.02.2011","02.02.2011","02.02.2011","02.02.2011","02.02.2011",
          "02.02.2011","02.02.2011","02.02.2011","02.02.2011")
date <- as.Date(date, format="%d.%m.%Y")
ID <- c("A","B","C","D","E","F","G","H","I","J",
        "A","B","C","D","E","F","G","H","I","J")
values <- as.numeric(c("1","8","2","3","5","13","2","4","1","16",
            "4","2","12","16","8","1","7","11","2","10"))

df <- data.frame(ID, date, values)

looking like:

  ID       date    values
1   A 2011-02-01      1
2   B 2011-02-01      8
3   C 2011-02-01      2
4   D 2011-02-01      3
5   E 2011-02-01      5
6   F 2011-02-01     13
7   G 2011-02-01      2
8   H 2011-02-01      4
9   I 2011-02-01      1
10  J 2011-02-01     16
11  A 2011-02-02      4
12  B 2011-02-02      2
13  C 2011-02-02     12
14  D 2011-02-02     16
15  E 2011-02-02      8
16  F 2011-02-02      1
17  G 2011-02-02      7
18  H 2011-02-02     11
19  I 2011-02-02      2
20  J 2011-02-02     10

I would like to create a new column "QF" which takes the following values:

I would like to obtain:

ID       date      values   QF
1   A 2011-02-01      1     1
2   B 2011-02-01      8     3
3   C 2011-02-01      2     1
4   D 2011-02-01      3     2
5   E 2011-02-01      5     2
6   F 2011-02-01     13     3
7   G 2011-02-01      2     1
8   H 2011-02-01      4     2
9   I 2011-02-01      1     1
10  J 2011-02-01     16     3
11  A 2011-02-02      4     1
12  B 2011-02-02      2     1
13  C 2011-02-02     12     3
14  D 2011-02-02     16     3
15  E 2011-02-02      8     2
16  F 2011-02-02      1     1
17  G 2011-02-02      7     2
18  H 2011-02-02     11     3
19  I 2011-02-02      2     1
20  J 2011-02-02     10     2

If any editing of my question is needed, do not hesitate to let me know

Upvotes: 1

Views: 49

Answers (2)

akrun
akrun

Reputation: 886938

We can use fndInterval

library(dplyr)
df %>%
   group_by(date) %>%
    mutate(QF = findInterval(values, c(0, quantile(values, probs = c(0.4, 0.7, 1)))))

Upvotes: 0

tmfmnk
tmfmnk

Reputation: 39858

One dplyr option could be:

df %>%
 group_by(date) %>%
 mutate(QF = cut(values, c(0, quantile(values, probs = c(0.4, 0.7, 1))),
                 labels = 1:3))

   ID    date       values QF   
   <fct> <date>      <dbl> <fct>
 1 A     2011-02-01      1 1    
 2 B     2011-02-01      8 3    
 3 C     2011-02-01      2 1    
 4 D     2011-02-01      3 2    
 5 E     2011-02-01      5 2    
 6 F     2011-02-01     13 3    
 7 G     2011-02-01      2 1    
 8 H     2011-02-01      4 2    
 9 I     2011-02-01      1 1    
10 J     2011-02-01     16 3    
11 A     2011-02-02      4 1    
12 B     2011-02-02      2 1    
13 C     2011-02-02     12 3    
14 D     2011-02-02     16 3    
15 E     2011-02-02      8 2    
16 F     2011-02-02      1 1    
17 G     2011-02-02      7 2    
18 H     2011-02-02     11 3    
19 I     2011-02-02      2 1    
20 J     2011-02-02     10 2

Upvotes: 2

Related Questions