PhV
PhV

Reputation: 73

dplyr count within subgroup

I have the following data frame:

df<-data.frame(Date=rep(x=as.Date(c("2016/01/01", "2016/01/02")),each=12),
       Group=rep(x=c("G1","G2"),each=6,times=2),
       ID=rep(c("G1F1","G1F2","G1F3","G1M4","G1M5","G1M6","G2F1","G2F2","G2F3","G2M4","G2M5","G2M6"),times=2),
       Gender=rep(c("Female","Male"),each=3,times=4),
       Weight=c(c(100,100,100,100,120,140),rep(c(100,120,140,100,100,100),times=2),c(100,100,100,100,120,140)))

I would like to create a new column CountComp that displays the count of same gender same group member counts of individual whose weight is within 10 gram above or below the weight of the individual specified in each row on a given date such as to get:

df2<-data.frame(Date=rep(x=as.Date(c("2016/01/01", "2016/01/02")),each=12),
           Group=rep(x=c("G1","G2"),each=6,times=2),
           ID=rep(c("G1F1","G1F2","G1F3","G1M4","G1M5","G1M6","G2F1","G2F2","G2F3","G2M4","G2M5","G2M6"),times=2),
           Gender=rep(c("Female","Male"),each=3,times=4),
           Weight=c(c(100,100,100,100,120,140),rep(c(100,120,140,100,100,100),times=2),c(100,100,100,100,120,140)),
           CountComp=c(c(2,2,2,0,0,0),rep(c(0,0,0,2,2,2),times=2),c(2,2,2,0,0,0)))

I am very new to R and thought that dplyr could offer a solution using group_by and mutate function, but have so far failed in finding one.

Upvotes: 4

Views: 738

Answers (1)

MrFlick
MrFlick

Reputation: 206586

Something like this could work

df %>% group_by(Date, Group, Gender) %>% 
    mutate(CountComp=sapply(Weight, function(x) sum(abs(x-Weight)<10)-1))

Here we do the group_by to get the classes you want to get the counts for. Then we use mutate to get the new column. We need to compare each value against every other value in the group. The easiest way for me to do that was to use sapply to look at each weight one at a time and compare it to the others, looking for differences smaller than 10.

Upvotes: 3

Related Questions