CODEWITHSUNDEEP
raggregate
BigKage
BigKage

Reputation: 63

Counting Records Based on Unique Date

I have a frame with a column of dates (some dates with multiple records) and a numeric column. I want a frame that lists one date per record, the sum of the numbers for each date, and the number of occurrences of records for each date.

Starting frame:

SomeDate    SomeNum
10/1/2013   2
10/1/2013   3
10/2/2013   5
10/3/2013   4
10/3/2013   1
10/3/2013   1

I can get the sum of SomeNum per unique date with the following:

newDF<-unique(within(df, {
    SumOfSomeNums <- ave(SomeNum, SomeDate, FUN = sum) 
}))

But I can't figure out how to get a count of the number of times each unique SomeDate occurs.

I want:

SomeDate   SumOfSomeNums  CountOfSomeDate
10/1/2013   5             2
10/2/2013   5             1
10/3/2013   6             3

What would get me the CountOfSomeDate data? Thx

Upvotes: 2

Views: 123

Answers (1)

A5C1D2H2I1M1N2O1R2T1
A5C1D2H2I1M1N2O1R2T1

Reputation: 193517

Continuing with your approach, use length as your aggregation function:

unique(within(mydf, {
  SumOfSomeNums <- ave(SomeNum, SomeDate, FUN = sum)
  CountOfSomeDate <- ave(SomeDate, SomeDate, FUN = length)
  rm(SomeNum)
}))
#    SomeDate CountOfSomeDate SumOfSomeNums
# 1 10/1/2013               2             5
# 3 10/2/2013               1             5
# 4 10/3/2013               3             6

However, there are many alternative ways to get here.

Here's an aggregate approach:

do.call(data.frame, aggregate(SomeNum ~ SomeDate, mydf, function(x) c(sum(x), length(x))))
#    SomeDate SomeNum.1 SomeNum.2
# 1 10/1/2013         5         2
# 2 10/2/2013         5         1
# 3 10/3/2013         6         3

And a data.table approach:

library(data.table)
DT <- data.table(mydf)

DT[, list(Count = length(SomeNum), Sum = sum(SomeNum)), by = SomeDate]
#     SomeDate Count Sum
# 1: 10/1/2013     2   5
# 2: 10/2/2013     1   5
# 3: 10/3/2013     3   6

Upvotes: 2

Related Questions