How to group daily data into months in a dataframe using dplyr part 2

Question

This is part 2 to this question...How to group daily data into months in a dataframe using dplyr

I managed to use floor_date to assign months to the dates. However, since i want to average (mean()) the numbers from daily observations to make a per month average for each month...it isn't working using group_by and summarise, and i'm assuming that's because the vectors would have different lengths? For example, i have 148 daily observations from the year, which I want to turn into 12 mean monthly observations. Since the dataframe is 148 observations long, i'm assuming R doesn't like that I am trying to insert another column with a vector length of only 12.

Essentially i have a dataframe for 148 observations of daily data, and i want to turn it into a dataframe of 12 observations, one column the month, and the other column the mean of the daily observations for that month. I will need to be able to run this code over thousands of observations eventually.

So far I have:

WK.2013.edit <- WK.2013 %>% 
  select(dat_col, Group.Members.Seen) %>% 
  mutate(month.name=floor_date(WK.2013$dat_col, unit = "month"))

This gives me an output of:

str(WK.2013.edit)
'data.frame':   148 obs. of  3 variables:
 $ dat_col           : Date, format: "2013-05-01" "2013-05-02" ...
 $ Group.Members.Seen: num  7 6 8 9 9 6 8 9 4 9 ...
 $ month.name        : Date, format: "2013-05-01" "2013-05-01" ...

When i use mutate to add a column trying to get the monthly means, the code runs and then i get a dataframe with only one value (which I'm assuming it ignored my floor_date and ran the mean of all the daily values)

So then i tried:

test <- WK.2013.edit %>% 
  group_by(unique(month.name)) %>% 
  summarise(mean.mem=mean(Group.Members.Seen, na.rm=TRUE))

However, when i run the summarise code, i get the error of "Error: Column unique(month.name) must be length 148 (the number of rows) or one, not 8"

Tanner33 Tanner33 · Accepted Answer · 2019-08-01T23:09:57

You could, create another dataframe with your monthly averages and then add them back to the main dataframe with something like left_join.

MonthlyAverage <- ddply(WK.2013.edit,~month.name, summarise, mean.mem=mean(Group.Members.Seen, na.rm=TRUE))


WK.2013.edit <- left_join(WK.2013.edit, MonthlyAverage, by month.name)

How to group daily data into months in a dataframe using dplyr part 2

1 Answers