0

I am trying to make a count summary based on months. My data does not have some months represented in the data, but do not have nulls. So, I am trying to make a cross tabulation table but it is not working because of the different sizes.

This is my code:

Df1<-DF2 %>% filter(Type == "Color")%>% group_by(MONTH) %>% 
summarise(Count=n())

And I am getting

DF1
Month    Count
1        100
2        200
3        300   
4        400
5        500
6        600
7        700
8        800
9        900

And that is it. So I cannot use the table function with another summarized dataframe with all 12 months. Thank you

Reagan
  • 49
  • 1
  • 5

1 Answers1

0

You could first create a data frame with column of all months and then join your actual data to force the existence of all months (it will force NAs but then just take the extra step to replace those with 0s if that's relevant here):

df3 <- data_frame(Month=1:12)
Df1<-DF2 %>% filter(Type == "Color")%>% group_by(MONTH) %>% 
summarise(Count=n()) %>%
right_join(df3,by='Month') %>%
replace_na(list(Count=0))
c.custer
  • 407
  • 5
  • 13
  • Im getting this error: Error: `by` can't contain join column `Month` which is missing from LHS – Reagan Jul 26 '18 at 15:00
  • Hmmm, probably the result of me not actually having DF2 to work with and based on your code, it may be 'MONTH' instead of both being 'Month'. Here is exactly what I did to replicate your example: `DF2 <- data_frame(Month=1:9,Count=seq(100,900,100)) df3 <- data_frame(Month=1:12) Df1<-DF2 %>% right_join(df3,by='Month') %>% replace_na(list(Count=0))` – c.custer Jul 27 '18 at 15:31
  • If it is just a matter of MONTH and Month you could change the line of code in my first example to `by=c('MONTH'='Month')` – c.custer Jul 27 '18 at 15:33