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