Count number of values in R [duplicate]

Question

I have below dataset:

    ClaimType ClaimDay ClaimCost   dates    month      day
1         1        1     10811 1970-01-01     1 1970-01-01
2         1        1     18078 1970-01-01     1 1970-01-01
3         1        2     44579 1970-01-01     1 1970-01-02
4         1        3     23710 1970-01-01     1 1970-01-03
5         1        4     29580 1970-01-01     1 1970-01-04
6         1        4     36208 1970-01-01     1 1970-01-04

I would like to create a new dataset with the columns "claim day" and "day". Claim day should be counted per value. So for example since we have two ones, a two, a three and then two fours I would like the new dataset to be like:

ClaimDay   day
2         1970-01-01
1         1970-01-02
1         1970-01-03
2         1970-01-04

As you can see the Claimday and day are related.

I have tried

mydata <- aggregate(ClaimDay~Day,FUN=sum,data=mydata)$ClaimDay

But the problem is, then it counts the summary when aggregate.

Can anyone help me with my issue?

Answer 1

You can try any of the following:

with base R

aggregate(ClaimDay~day,FUN=length,data=mydata)

with tapply

as.data.frame(tapply(mydata$ClaimDay, mydata$day, length), responseName='ClaimDay')

with by

by(mydata$ClaimDay, mydata$day, length, simplify = TRUE)

with dplyr

library(dplyr)
mydata %>% count(day)

with data.table

library(data.table)
data.table(mydata)[,(ClaimDay=length(ClaimDay)),by=day]

with plyr

library(plyr)
ddply(mydata,~day,summarise,ClaimDay=length(day))

with sqldf

library(sqldf)
sqldf('select count(ClaimDay) as ClaimDay, day from mydata group by day')

#  ClaimDay        day
#1        2 1970-01-01
#2        1 1970-01-02
#3        1 1970-01-03
#4        2 1970-01-04

And benchmark results:

library('microbenchmark')
microbenchmark(agg=aggregate(ClaimDay~day,FUN=length,data=mydata), 
               dplyr=mydata %>% dplyr:::count(day), 
               data.table=data.table(mydata)[,(ClaimDay=length(ClaimDay)),by=day], 
               plyr=ddply(mydata,~day,summarise,ClaimDay=length(day)),
               tapply=as.data.frame(tapply(mydata$ClaimDay, mydata$day, length), responseName='ClaimDay'),
               sqldf=sqldf('select count(ClaimDay) as ClaimDay, day from mydata group by day'),
               by=by(mydata$ClaimDay, mydata$day, length, simplify = TRUE),
               times=500)

Unit: microseconds
       expr      min        lq       mean    median        uq       max neval    cld
        agg 1280.399 1408.2675  1655.8207 1458.9445  1845.331  7732.426   500   c   
      dplyr 1019.102 1177.3345  1350.3923 1220.0995  1356.736  3835.208   500  b    
 data.table 1690.092 1883.8190  2208.6055 1957.1630  2234.283  5493.653   500    d  
       plyr 2334.995 2482.7495  2847.0871 2554.5960  2944.404  6620.096   500     e 
     tapply  226.658  273.0580   342.0902  304.0635   353.244  2748.965   500 a     
      sqldf 8395.718 9057.0870 10458.0976 9440.2650 11389.515 61480.071   500      f
         by  353.243  415.0395   492.2115  449.2520   509.765  4331.287   500 a