Group table by date then sum value and count in R

Question

I am having the following dataset:

DealDate <- c("1/1/13",  "1/2/13", "1/6/13", "1/1/13",  "1/2/13", "1/6/13", "1/1/13",  "1/2/13", "1/6/13")

DealValue<- c("100", "200","300",  "400","100", "200","300",  "400","100")

ClientNAME <- c( "a", "b", "c", "a","b", "c","b", "c","e")

data<- data.frame(DealDate, DealValue, ClientName)

And from that I want to get following output:

"Date" "Unique_Client_Count" "Deal_Count" "Total_DealValue"

"2013.01" "2" "3" "800"

What I have done is that I have created a dataframe for each column and then combined them together. I've tried to use "mutate" after "group_by"

result<- data %>% group_by(Date = DealDate) %>% summarise(Total_DealValue = sum(DealValue)) %>% mutate(Deal_Count= count(DealValue))

and got below error:

Error in UseMethod("summarise_") : 

  no applicable method for 'summarise_' applied to an object of class "c('double', 'numeric')"

Please help me...

Answer 1

Let me tell you that in your code, when you are combining the data into data-frame then the test data is all converted to factors. It is better to use the stringsAsFactors = FALSE within the data.frame function call.

library(dplyr)

data %>%

  group_by(Date = DealDate) %>%

  summarise(

    Unique_Client_Count = length(unique(ClientNAME)),

    Deal_Count = n(),

    Total_DealValue = sum(as.numeric(DealValue))

  )

If you are a beginner and want to know more about R then do check out the R programming tutorial that will help you in learning R.