Count the sum of the columns according to their month of the year-CodePudding

How can I group my database by month of the year and get the value of that grouping for each column I have ? in R

Here is a pic of my dataframe:

enter image description here

I tried to do that but it is not working:

df_publications <- df_publications %>% group_by(publication_date) 
                   %>% count()

CodePudding user response：

Dplyr summarize across everything method. Surely this can be coded more concisely (i.e. without the list() and .names= arguments)

df<-data.frame(publication_date=c("2015 Jul","2015 Jul","2015 Aug","2015 Aug"),
               Asym=c(3,5,1,2),
               Auth=c(5,7,2,3),
               Cert=c(1,2,3,4))

library(tidyverse)

df %>% 
  group_by(publication_date) %>% 
  summarize(across(everything(), list(sum = sum), .names = "{.col}"))

#  publication_date  Asym  Auth  Cert
#1 2015 Aug             3     5     7
#2 2015 Jul             8    12     3

Base, xtabs() method, requires naming all the columns:

xtabs(cbind(Auth, Asym, Cert)~., data=df)
#publication_date Auth Asym Cert
#        2015 Aug    5    3    7
#        2015 Jul   12    8    3

CodePudding user response：

You could just use a subquery:

SELECT Year_, Month_, SUM(Counts) FROM ( SELECT YEAR(DATEADD(MM,DATEDIFF(MM,0,StartTime),0))'Year_' ,DATENAME(MONTH,DATEADD(MM,DATEDIFF(MM,0,StartTime),0))'Month_' ,TestName ,CASE WHEN Testname = 'POE Business Rules' THEN (count(TestName)*36) WHEN TestName = 'Submit' THEN (COUNT(TestName)*6) ELSE 0 END 'Counts' FROM VExecutionGlobalHistory GROUP BY YEAR(DATEADD(MM,DATEDIFF(MM,0,StartTime),0)) ,DATENAME(MONTH,DATEADD(MM,DATEDIFF(MM,0,StartTime),0)) ,TestName )sub GROUP BY Year_, Month_ ORDER BY CAST(CAST(Year_ AS CHAR(4)) Month_ '01' AS DATETIME) Update: Added ORDER BY to sort by YEAR/MONTH oldest first.