0

I want to mimic the following sql query in R:

select CAST(calltime as date), x, AVG(AAT) rev, COUNT(*) occurances from tablename group by 1,2;

result should be like this:
date                 x       rev       occurances
2016-12-30           col     56.32       3652
2016-12-30           col_NA  24.69       2006
2016-12-30           dial    32.20       4236
2016-12-29           col     54.98       3762
2016-12-29           col_NA  24.32       2105
2016-12-29           dial    31.93       4165
      .                .       .          .
      .                .       .          .
      .                .       .          .

I am using the following R code:

#dfsub is the dataframe that has the table

dfsub$aat = as.numeric(dfsub$aat)
dfsub$calltime = as.Date(dfsub$calltime)
bycalltime_x <- summarise(group_by(dfsub,calltime,x), rev = mean(aat))

this gives me the first 3 columns. I am not able to figure out a way to get the occurances. I tried aggrgate but the results were not right. guide me in this regard

  • 1
    Welcome to StackOverflow! Please read the info about [how to ask a good question](http://stackoverflow.com/help/how-to-ask) and how to give a [reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example/5963610). This will make it much easier for others to help you. – Jaap Dec 30 '16 at 15:58

1 Answers1

0

this is more of a question for stackoverflow.com...however, you want to use dplyr and can use the pipe operator %>% like this:

dfsub %>% group_by(dfsub,calltime,x) %>% summarise(avg = mean(aat))

If I udnerstand it correctly :)

Jan Sila
  • 1,554
  • 3
  • 17
  • 36