r - Count of data by Sqldf -

my data looks like:

 id   category 101          101          b 101          c 102          103          b 103          c 

i result like:

 id   category count  101              3 101          b     3 101          c     3 102              1 103          b     2 103          c     2 

i have tried like:

data<-sqldf("select *,count(id) count data group id") 

it showing output as:

 id   category  count 101          c      3 102               1 103          c      2 

an option using data.table

library(data.table) setdt(df1)[, count:=.n, id] #    id category count #1: 101            3 #2: 101        b     3 #3: 101        c     3 #4: 102            1 #5: 103        b     2 #6: 103        c     2 

or using dplyr

library(dplyr) df1 %>%     group_by(id) %>%     mutate(count=n()) 

or using base r

 df1$count <- with(df1, ave(seq_along(id), id, fun=length))