Search code examples
rgroupingbinning

Binning by Subgroup in R

I have a dataframe with Markets, Retailers and Sales. I need to bin the Retailers within each Market into 5 quantiles.

Example:

Grouped

dataframe <- structure(list(Market = c(1, 1, 1, 2, 2, 2), Retailer = c(1, 
2, 3, 4, 5, 6), Sales = c(5, 10, 25, 5, 10, 25), Quantile = c(1, 
2, 3, 1, 2, 3)), class = "data.frame", row.names = c(NA, -6L))
Solution

  • One approach is using group_by and ntile from dplyr:

    library(dplyr)
dataframe %>%
  group_by(Market) %>%
  mutate(Quantile = ntile(Sales, 4))
# A tibble: 150 x 4
# Groups:   Market [3]
   Market Retailer Sales Quantile
    <int>    <int> <dbl>    <int>
 1      1        1 16804        1
 2      1        2 80752        4
 3      1        3 38494        2
 4      1        4 32773        2
 5      1        5 60210        3
# … with 145 more rows

    Data

    set.seed(3)
dataframe <- data.frame(Market = rep(1:3, each = 50), 
                        Retailer = rep(1:50, times = 3), 
                        Sales = round(runif(150,0,100000),0))