CODEWITHSUNDEEP
rdataframefor-loopdplyr
ldarc
ldarc

Reputation: 11

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

I have a dataframe containing daily counts of number group members seen present. I am wanting to get a monthly mean of the number of group members seen (produced in a data frame). I've been trying to use dplyr as it is much simpler than creating a new data frame and filling it using a for loop. I'm very new to coding and would like to be able to do this for multiple groups. My dataframe looks like this:

data.frame':    148 obs. of  7 variables:
 $ Date                     : Date, format: "2013-05-01" "2013-05-02" ...
 $ Group                    : chr  "WK" "WK" "WK" "WK" ...
 $ Session                  : Factor w/ 12 levels "AM","AM1","AM2",..: 9 1 9 9 1 9 9 1 1 1 ...
 $ Group.Members.Seen       : num  7 6 8 9 9 6 8 9 4 9 ...
 $ Roving.Males             : num  NA NA NA NA NA NA NA NA NA NA ...
 $ Undyed.Group.Members.Seen: num  NA NA NA NA NA NA NA NA NA NA ...
 $ Non.group.Other          : num  NA NA NA NA NA NA NA NA NA NA ..

I don't have an observation for every day, and sometimes have multiple observations for a day. In this particular instance, there is only data in the Group.members.seen column, however in other datasets i do have numbers in roving.males, undyed.group.members.seen, and non.group.other columns.

For this particular dataset, I am only wanting to work with the Date and Group.Members.seen columns, as I only have data in those columns. I've used select to select those columns, then have tried to use mutate, group_by, and summarise to get what I want. However, I think the problem is with the dates. Have also tried aggregate but i don't think that is the best.

test <- WK.2013 %>% 
  select(Date, Group.Members.Seen) %>% 
  mutate(mo = Date(format="%m"), mean.num.members = mean(Group.Members.Seen)) %>% 
  group_by(Date(format="%m")) %>% 
  summarise(mean = mean(Group.Members.Seen))

Error message is saying it cannot find the function "Date", which is probably the beginning of a long string of problems with that code.

Upvotes: 1

Views: 264

Answers (2)

RaphaelS
RaphaelS

Reputation: 869

It's hard to say for sure if this will work without seeing the actual data but could you try the apply.monthly function from the xts package?

Upvotes: 0

Yifu Yan
Yifu Yan

Reputation: 6106

You can try lubridate package and round dates to month or year or other units. 

library(lubridate)

mydate <- today()

> floor_date(today(),unit = "month")
[1] "2019-07-01"
> floor_date(mydate,unit = "month")
[1] "2019-07-01"
> round_date(mydate,unit = "month")
[1] "2019-08-01"

Upvotes: 2

Related Questions