R: Summarizing Data At Multiple Levels

I am working with the R programming language.

I have the following dataset about people with their weights and asthma (1 = yes, 0 = no):

library(dplyr)
library(purrr)
library(ggplot2)
set.seed(123)

my_data1 = data.frame(Weight =  rnorm(500,100,100), asthma = sample(c(0,1), prob = c(0.7,0.3), replace=TRUE, size= 500))
my_data2 = data.frame(Weight = rnorm(500, 200, 50),  asthma = sample(c(0,1), prob = c(0.3,0.7), replace=TRUE, size= 500))
my_data_a = rbind(my_data1, my_data2)
my_data_a$gender =  "male"


my_data1 = data.frame(Weight =  rnorm(500,100,100), asthma = sample(c(0,1), prob = c(0.7,0.3), replace=TRUE, size= 500))
my_data2 = data.frame(Weight = rnorm(500, 200, 50),  asthma = sample(c(0,1), prob = c(0.3,0.7), replace=TRUE, size= 500))
my_data_b = rbind(my_data1, my_data2)
my_data_b$gender =  "female"


my_data = rbind(my_data_a, my_data_b)
my_data$id = 1:2000

My Question: For both genders, I would like to "bin" people in this dataset into "n" bins (e.g. n = 30) in ascending order based on the available weight ranges (e.g. min_weight_men : min_weight_men+ 30 = bin_1_men, min_weight_women : min_weight_women+ 30 = bin_1_women, min_weight_men+ 30 : min_weight_men+ 60 = bin_2_men, etc.) – and then find out how many people in each bin, as well as the min weight and max weight for each bin.

My Attempt: I tried to do this with the following code:

Part_1 = my_data %>% group_by(gender) %>%
    mutate(bins = cut(Weight , breaks = pretty(Weight , n = (max(Weight)-min(Weight))/30), include.lowest = TRUE)) %>% 
 mutate(rank = dense_rank(bins)) %>% 
mutate(new_bins = paste(rank,"_", gender, sep=""))

Part_2 = Part_1 %>% group_by(gender, bins) %>% 
    summarize(min_weight = min(Weight), max_weight = max(Weight), count = n())

Part_3 = merge(x=Part_1,y=Part_2, by.x=c("gender","bins"), by.y=c("gender","bins"))

While the result are in the format that I want – I am not sure if I have performed the calculations correctly:

> head(Part_3)
  gender       bins    Weight asthma   id rank new_bins min_weight max_weight count
1 female (-100,-50] -75.13021      0 1192    4 4_female  -99.91774  -51.53241    23
2 female (-100,-50] -55.78222      0 1382    4 4_female  -99.91774  -51.53241    23
3 female (-100,-50] -51.53241      0 1232    4 4_female  -99.91774  -51.53241    23
4 female (-100,-50] -71.44877      1 1484    4 4_female  -99.91774  -51.53241    23
5 female (-100,-50] -93.99402      1 1160    4 4_female  -99.91774  -51.53241    23
6 female (-100,-50] -96.49823      0 1378    4 4_female  -99.91774  -51.53241    23

Can someone please help me understand if I have done this correctly?

Thanks!

Note: Just to clarify – suppose weights for men are from 70kg to 150kg. I want bins such as bin_1_men = 70-100kg, bin_2_men = 100-130kg, etc. I am aware that this could result in some bins having significantly different counts.

>Solution :

Instead of doing this in 3 steps, could be done in a single pipe with mutate after grouping

library(dplyr)
my_data %>% 
 group_by(gender) %>%
  mutate(bins = cut(Weight , breaks = pretty(Weight , 
   n = (max(Weight)-min(Weight))/30), include.lowest = TRUE),
  rank = dense_rank(bins),
 new_bins = paste(rank,"_", gender, sep="")) %>% 
 group_by(gender, bins) %>% 
 mutate(min_weight = min(Weight), max_weight = max(Weight), 
   count = n()) %>% 
 ungroup

Leave a Reply