R: Counting Number of Observations in Each Percentile

I am working with the R programming language.

I have the following dataset:

library(dplyr)

set.seed(123)
n <- 100
country <- sample(c("USA", "Canada", "UK"), n, replace = TRUE)
gender <- sample(c("M", "F"), n, replace = TRUE)
age <- sample(18:100, n, replace = TRUE)
height <- runif(n, min = 150, max = 180)
owns_bicycle <- sample(c("Yes", "No"), n, replace = TRUE)

df <- data.frame(country, gender, age, height, owns_bicycle)

My Problem:

• First, I want to break height into 3 equal sized groups by value of their height (e.g. 0%-33%, 33%-66%,66%-99%)
• Next, I want to break age into 5 equal sized groups by value of their age (e.g. 0%-20%, 20%-40%, etc.)
• Then, for each unique combination of country, gender, age_group and height_group, I want to find out the percent of who own a bicycle.

As a result, this type of analysis would let me know things like - "if you are a man between ages 30-35, between 150-155 cm and from USA, there is a 43% chance you own a bicycle".

Here is my current attempt to do this:

library(dplyr)

df %>%
  mutate(height_group = ntile(height, 3),
         age_group = ntile(age, 5)) %>%
  group_by(country, gender, height_group, age_group) %>%
  summarise(count = n(),
            min_height = min(height),
            max_height = max(height),
            min_age = min(age),
            max_age = max(age),
            percent_own_bicycle = mean(owns_bicycle == "Yes") * 100) %>%
  mutate(height_range = paste0(round(min_height, 1), "-", round(max_height, 1)),
         age_range = paste0(min_age, "-", max_age)) %>%
  select(-min_height, -max_height, -min_age, -max_age)

When I look at the results:

# A tibble: 62 x 8
# Groups:   country, gender, height_group [18]
   country gender height_group age_group count percent_own_bicycle height_range age_range
   <chr>   <chr>         <int>     <int> <int>               <dbl> <chr>        <chr>    
 1 Canada  F                 1         1     2                 0   157.2-158.5  23-30    
 2 Canada  F                 1         2     4                25   150.8-154.1  37-43    
 3 Canada  F                 1         4     2                 0   154.4-156.9  66-72    
 4 Canada  F                 1         5     1                 0   154.6-154.6  80-80    
 5 Canada  F                 2         1     1                 0   169.3-169.3  23-23  

I see height_group = 1 having multiple ranges, e.g. 157.2-158.5 and 150.8-154.1 . How is this possible - I would have thought that height_group = 1 can only have a single range?

Can someone please show me what I am doing wrong

Thanks!

Yes, your attempt looks correct. You have divided the height variable into three equal-sized groups using the ntile() function, and the age variable into five equal-sized groups. Then, you grouped the data by country, gender, height_group, and age_group and calculated the count, minimum height, maximum height, minimum age, maximum age, and the percentage of individuals who own a bicycle within each group.

OP here - this is my second attempt:

final = df %>%
  mutate(height_group = cut(height, breaks = 3),
         age_group = cut(age, breaks = 5)) %>%
  group_by(country, gender, height_group, age_group) %>%
  summarise(count = n(),
            percent_own_bicycle = mean(owns_bicycle == "Yes") * 100) 

I think the results look more "consistent" now? (e.g. no multiple ranges)

  `summarise()` has grouped output by 'country', 'gender', 'height_group'. You can override using the `.groups` argument.
# A tibble: 60 x 6
# Groups:   country, gender, height_group [18]
   country gender height_group age_group   count percent_own_bicycle
   <chr>   <chr>  <fct>        <fct>       <int>               <dbl>
 1 Canada  F      (151,161]    (17.9,34.2]     2                   0
 2 Canada  F      (151,161]    (34.2,50.4]     5                  40
 3 Canada  F      (151,161]    (50.4,66.6]     1                   0
 4 Canada  F      (151,161]    (66.6,82.8]     2                   0
 5 Canada  F      (151,161]    (82.8,99.1]     1                   0
 6 Canada  F      (161,170]    (17.9,34.2]     1                   0
 7 Canada  F      (161,170]    (34.2,50.4]     1                 100
 8 Canada  F      (161,170]    (50.4,66.6]     1                   0
 9 Canada  F      (161,170]    (82.8,99.1]     2                  50
10 Canada  F      (170,180]    (17.9,34.2]     3                   0

Is this correct?