2

I need to get the row of the first and last day of each month in a big data frame where I need to apply operations that cover accurately each month, using a for loop. Unfortunately, the data frame is not very homogeneous. Here a reproducible example to work upon: 

dataframe <- data.frame(Date=c(seq.Date(as.Date("2020-01-01"),as.Date("2020-01-31"),by="day"),
    seq.Date(as.Date("2020-02-01"),as.Date("2020-02-28"),by="day"),seq.Date(as.Date("2020-03-02"),
    as.Date("2020-03-31"),by="day")))
Mr Frog
  • 296
  • 2
  • 16

1 Answers1

3

We can create a grouping column by converting to yearmon and then get the first and last

library(zoo)
library(dplyr)
dataframe %>% 
   group_by(yearMon = as.yearmon(Date)) %>%
   summarise(FirstDay = first(Date), LastDay = last(Date))
# A tibble: 3 x 3
#  yearMon   First      Last      
#* <yearmon> <date>     <date>    
#1 Jan 2020  2020-01-01 2020-01-31
#2 Feb 2020  2020-02-01 2020-02-28
#3 Mar 2020  2020-03-02 2020-03-31

If it the first and last day irrespective of the data 

library(lubridate)
dataframe %>% 
   group_by(yearMon = as.yearmon(Date)) %>%
   summarise(First = floor_date(first(Date), 'month'), 
             Last = ceiling_date(last(Date), 'month')-1)
akrun
  • 874,273
  • 37
  • 540
  • 662
  • Thank you! Would you also be able to tell me how to apply a loop that starts at each FirstDay and after applying at LastDay repeat itself?? – Mr Frog May 26 '20 at 19:41
  • @MrFrog From the summarise, you can use `purrr::map2` i.e. `%>% mutate(new = map2(FirstDay, LastDay, ~ yourfun(.x, .y))` – akrun May 26 '20 at 19:47
  • Unfortunately, I need a loop because every month it changes a criterion according to which the function is applied – Mr Frog May 26 '20 at 19:49
  • 1
    @MrFrog in that case if the output of the summarise is. `out <- dataframe %>% .. %>% summarise(...)`, then `lst1 <- vector('list', nrow(out)); for(i in seq_along(lst1)) lst1[[i]] <- yourfun(out$FirstDay[i], out$LastDay[i])` – akrun May 26 '20 at 19:51
  • The criterion comes from another data frame with same column names and Dates but different content. However I am elaborating on that. Ty! – Mr Frog May 26 '20 at 19:54
  • @MrFrog. Perhaps you need a `merge` or join with 'Dates' on that dataset. Can you please post as a new question as those info is not clear – akrun May 26 '20 at 19:55
  • @MrFrog have you deleted that post – akrun May 26 '20 at 21:10
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/214696/discussion-between-mr-frog-and-akrun). – Mr Frog May 26 '20 at 21:23