CODEWITHSUNDEEP
rdataframedplyr
user6452857
user6452857

Reputation: 117

R: How to create a new column for 90th quantile based off previous rows in a data frame

data.frame(c = c(1,7,11,4,5,5))

   c
1  1
2  7
3 11
4  4
5  5
6  5

desired dataframe

   c c.90th
1  1   NA
2  7   1
3 11   6.4
4  4   10.2
5  5   9.8
6  5   9.4

For the first row, I want it to look at the previous rows, none and get the 90th quantile, NA. For the second row, I want it to look at the previous rows, 1 and get the 90th quantile, 1. For the third row, I want it to look at the previous rows, 1, 7 and get the 90th quantile, 6.4. etc.

Upvotes: 0

Views: 130

Answers (2)

paljenczy
paljenczy

Reputation: 4899

A solution using data.table that also works by groups:

library(data.table)

dt <- data.table(c = c(1,7,11,4,5,5),
                 group = c(1, 1, 1, 2, 2, 2))

cumquantile <- function(y, prob) {
    sapply(seq_along(y), function(x) quantile(y[0:(x - 1)], prob))
}

dt[, c90 := cumquantile(c, 0.9)]
dt[, c90_by_group := cumquantile(c, 0.9), by = group]

> dt
    c group  c90 c90_by_group
1:  1     1   NA           NA
2:  7     1  1.0          1.0
3: 11     1  6.4          6.4
4:  4     2 10.2           NA
5:  5     2  9.8          4.0
6:  5     2  9.4          4.9

Upvotes: 1

Abdou
Abdou

Reputation: 13274

Try:

dff <- data.frame(c = c(1,7,11,4,5,5))

dff$c.90th <- sapply(1:nrow(dff),function(x) quantile(dff$c[0:(x-1)],0.9,names=F))

Output:

 c  c.90th
  1   NA  
  7  1.0  
 11  6.4  
  4 10.2  
  5  9.8  
  5  9.4

Upvotes: 0

Related Questions