Conditional sum grouped by date in R

user_10426650 0
Viewed 83

The data I am working with is the number of people in a group. The columns in the dataset I'm concerned with are the date (column 1) and the number of people in a group (column 3 where there is a separate row for each group on a given day). I am looking for an output spreadsheet that gives me a column for a date, one for the sum of all the groups with one person in it on a day, and a column for the sum of all the people who are in groups larger than one on a day.

For example if this was my dataset:

Date People
10/18 1
10/18 3
10/18 1
10/18 8
10/20 1
10/20 4
10/20 2

My desired output would be:

Date p=1 p>1
10/18 2 11
10/20 1 6

My data frame is "DF" and a csv with the different dates is "times". I tried to use a for loop but the output was just zeros.

Here is what I tried:

ntimes = length(times$UniTimes)
for(i in 1:ntimes)
{
 
  s<- sum(DF[which (DF[,3] > 1 & DF[,1]==i),3])
  t<- sum(DF[which (DF[,3] < 2 & DF[,1]== i),3])
  
}
ndf<-data.frame(times,s,t)
write.csv(ndf,'groups_c.csv')

Thank you for your time and help!

conditional-statements r for-loop sum
3 Answers

You can use aggregate:

aggregate(People ~ Date, x, function(x) c("p=1" = sum(x[x==1]),
  "p>1" = sum(x[x>1])))
#   Date People.p=1 People.p>1
#1 10/18          2         11
#2 10/20          1          6
user_104885040

This should work, but without data to reproduce it's difficult to say:

library(dplyr)
DF %>%
   group_by(Date) %>%
   summarise(peq1 = sum(People == 1),
             pgeq1 = sum(People[People > 1]))
user_9979790

An option with data.table

library(data.table)
setDT(DF)[, .(peq1 = sum(People == 1), pgeq1 = sum(People[People >1])), .(Date)]
user_37322710
Related