1

I want to find out deciles for each grouped variable. I am specifically looking for methods using dplyr and lapply. I'd appreciate if you can help me out.

Here's my what I tried. I don't know how to pull deciles directly other than calling dplyr::ntile() (which didn't work for me)

Attempt 1

Here's what I tried using describe() from Hmisc package:

set.seed(10)
IData <- data.frame(let = sample( x = LETTERS, size = 10000, replace=TRUE), numbers = sample(x = c(1:20000),size = 10000))

Output<-IData %>% data.table::as.data.table(.) %>% split(.,by=c("let"),drop = TRUE,sorted = TRUE) %>% purrr::map(~describe(.$numbers))

This certainly helps but there are two problems with above code:

a) The output (even the list format) is not something I am looking for.

b) I don't really know how to extract 5%, 10%...from the list above.

The bottomline is that I am stuck

Attempt 2

I tried replacing describe by ntile, but the following code gave me an output which didn't make sense to me because the number of columns aren't 10. Upon running Output[[1]], I see a vector of ~400 numbers instead of 10.

Output<-IData %>% data.table::as.data.table(.) %>% split(.,by=c("let"),drop = TRUE,sorted = TRUE) %>% purrr::map(~dplyr::ntile(.$numbers,10))

Attempt 3 = Expected Output

Finally, I tried going the old school (i.e. copy-paste) to get the expected output:

Output<-IData %>%
  dplyr::group_by(let) %>%
  dplyr::summarise( QQuantile1 = quantile(`numbers`, c(.10)),
                    QQuantile1 = quantile(`numbers`, c(.10)),
                    QQuantile2 = quantile(`numbers`, c(.20)),
                    QQuantile3 = quantile(`numbers`, c(.30)),
                    QQuantile4 = quantile(`numbers`, c(.40)),
                    QQuantile5 = quantile(`numbers`, c(.50)),
                    QQuantile6 = quantile(`numbers`, c(.60)),
                    QQuantile7 = quantile(`numbers`, c(.70)),
                    QQuantile8 = quantile(`numbers`, c(.80)),
                    QQuantile9 = quantile(`numbers`, c(.90)),
                    QQuantile10 = quantile(`numbers`, c(.100)))

Question: Can someone please help me to generate above output by using these three (not one, but preferably all the methods for learning)

1) lapply

2) dplyr

3) data.table

I looked at several threads on SO, but they all talk about a specific quantile and not all of them. E.g. Find top deciles from dataframe by group thread.

Community
  • 1
  • 1
watchtower
  • 4,140
  • 14
  • 50
  • 92
  • 2
    `library(tidyverse); IData %>% group_by(let) %>% summarise(quant_prob = list(paste0('quant', seq(.1, 1, .1))), quant_value = list(quantile(numbers, seq(.1, 1, .1)))) %>% unnest() %>% spread(quant_prob, quant_value)` maybe, though it's probably more useful to leave it in long form – alistaire Feb 10 '17 at 06:37
  • @Alistaire - Thanks for your help. Do you think you could also help me with `lapply` and `Data.Table` version so that I can learn new ways to solve the same problem? – watchtower Feb 10 '17 at 06:41
  • 1
    Hmm, apparently base R thinks the same way you do: `aggregate(numbers ~ let, IData, quantile, seq(.1, 1, .1))` – alistaire Feb 10 '17 at 06:45

2 Answers2

5

We can do this in a compact way with data.table. Convert the 'data.frame' to 'data.table' (setDT(IData)), grouped by 'let', get the quantile of 'numbers' and convert it to list (as.list)

library(data.table)
setDT(IData)[, as.list(quantile(numbers, seq(.1, 1, .1))), by = let]
akrun
  • 874,273
  • 37
  • 540
  • 662
5

To assemble my comments into an answer, base is shockingly simple:

aggregate(numbers ~ let, IData, quantile, seq(0.1, 1, 0.1))

##    let numbers.10% numbers.20% numbers.30% numbers.40% numbers.50% numbers.60% numbers.70% numbers.80% ...
## 1    A      1749.8      3847.8      5562.6      7475.2      9926.0     11758.6     13230.6     15788.8
## 2    B      2393.5      4483.6      6359.1      7708.0      9773.0     11842.8     13468.9     16266.4
## 3    C      2041.5      3682.0      5677.5      7504.0      9226.0     11470.0     13628.5     15379.0
## 4    D      1890.7      4086.8      5661.9      7526.6      9714.0     11438.8     13969.2     15967.2
## 5    E      2083.6      4107.0      6179.8      7910.8     10095.0     11692.6     13668.0     15570.2
## 6    F      1936.6      4220.2      6197.0      8791.8     10382.0     12266.4     14589.2     16407.0
## 7    G      3059.4      4884.2      6519.6      8530.0     10481.0     12469.0     14401.6     16127.8
## 8    H      2186.5      4081.0      5801.5      7206.0      9256.5     11453.0     13692.0     15471.0
## 9    I      1534.1      3793.2      5822.2      7621.4      9417.5     11737.0     14191.2     15722.4
## 10   J      1967.2      4286.6      5829.6      7664.6     10606.0     12217.4     14422.2     16628.0
## ...

with the caveat that numbers is actually a nested column that may need to be unpacked for further usage.

dplyr works if you use list columns or do and reshape:

library(tidyverse)

IData %>% group_by(let) %>% 
    summarise(quant_prob = list(paste0('quant', seq(.1, 1, .1))), 
              quant_value = list(quantile(numbers, seq(.1, 1, .1)))) %>% 
    unnest() %>% 
    spread(quant_prob, quant_value)

## # A tibble: 26 × 11
##       let quant0.1 quant0.2 quant0.3 quant0.4 quant0.5 quant0.6 quant0.7 quant0.8 quant0.9 quant1
## *  <fctr>    <dbl>    <dbl>    <dbl>    <dbl>    <dbl>    <dbl>    <dbl>    <dbl>    <dbl>  <dbl>
## 1       A   1749.8   3847.8   5562.6   7475.2   9926.0  11758.6  13230.6  15788.8  17763.0  19958
## 2       B   2393.5   4483.6   6359.1   7708.0   9773.0  11842.8  13468.9  16266.4  17877.4  19929
## 3       C   2041.5   3682.0   5677.5   7504.0   9226.0  11470.0  13628.5  15379.0  17265.0  19876
## 4       D   1890.7   4086.8   5661.9   7526.6   9714.0  11438.8  13969.2  15967.2  17961.0  19989
## 5       E   2083.6   4107.0   6179.8   7910.8  10095.0  11692.6  13668.0  15570.2  18011.4  19887
## 6       F   1936.6   4220.2   6197.0   8791.8  10382.0  12266.4  14589.2  16407.0  18345.0  19997
## 7       G   3059.4   4884.2   6519.6   8530.0  10481.0  12469.0  14401.6  16127.8  18219.2  19922
## 8       H   2186.5   4081.0   5801.5   7206.0   9256.5  11453.0  13692.0  15471.0  17331.0  19996
## 9       I   1534.1   3793.2   5822.2   7621.4   9417.5  11737.0  14191.2  15722.4  17706.6  19965
## 10      J   1967.2   4286.6   5829.6   7664.6  10606.0  12217.4  14422.2  16628.0  18091.2  19901
## # ... with 16 more rows

Another interesting option is purrrlyr::by_slice, which lets you collect the results to columns:

IData %>% group_by(let) %>% 
    by_slice(~quantile(.x$numbers, seq(0.1, 1, 0.1)), .collate = "cols")

## # A tibble: 26 × 11
##       let  .out1  .out2  .out3  .out4   .out5   .out6   .out7   .out8   .out9 .out10
##    <fctr>  <dbl>  <dbl>  <dbl>  <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>  <dbl>
## 1       A 1749.8 3847.8 5562.6 7475.2  9926.0 11758.6 13230.6 15788.8 17763.0  19958
## 2       B 2393.5 4483.6 6359.1 7708.0  9773.0 11842.8 13468.9 16266.4 17877.4  19929
## 3       C 2041.5 3682.0 5677.5 7504.0  9226.0 11470.0 13628.5 15379.0 17265.0  19876
## 4       D 1890.7 4086.8 5661.9 7526.6  9714.0 11438.8 13969.2 15967.2 17961.0  19989
## 5       E 2083.6 4107.0 6179.8 7910.8 10095.0 11692.6 13668.0 15570.2 18011.4  19887
## 6       F 1936.6 4220.2 6197.0 8791.8 10382.0 12266.4 14589.2 16407.0 18345.0  19997
## 7       G 3059.4 4884.2 6519.6 8530.0 10481.0 12469.0 14401.6 16127.8 18219.2  19922
## 8       H 2186.5 4081.0 5801.5 7206.0  9256.5 11453.0 13692.0 15471.0 17331.0  19996
## 9       I 1534.1 3793.2 5822.2 7621.4  9417.5 11737.0 14191.2 15722.4 17706.6  19965
## 10      J 1967.2 4286.6 5829.6 7664.6 10606.0 12217.4 14422.2 16628.0 18091.2  19901
## # ... with 16 more rows

though the column names are a little lousy.

alistaire
  • 42,459
  • 4
  • 77
  • 117
  • I love your deep knowledge about R. Do you think you could include an `lapply` version? I am new to R, and really want to master `lapply`. I'd sincerely appreciate your help. – watchtower Feb 10 '17 at 07:10
  • 3
    `lapply` doesn't group, so isn't really useful here. You could use `tapply`, like `do.call(rbind, tapply(IData$numbers, IData$let, quantile, seq(.1, 1, .1)))` (which returns a matrix instead of a data.frame), but really the `aggregate` version is the most apt base R approach. – alistaire Feb 10 '17 at 07:15