I have data of members who have transacted everyday, I need list of all the members who have transacted on the last date of each month for the whole year.
My output needs to have a list of members (with all the columns) who have transacted on 31-Jan-2019, 28-Feb-2019 and so on upto 31-Dec-2019.
month.ends <- as.Date(paste(unique(year(df$date)), unique(month(df$date)), "01", sep = "-"))-1
df %>% filter(date %in% month.ends)
If you want just the unique member names with some other columns you can use the distinct function
Okay here a pretty inefficient code (relatively new to R), but I think it works if you only want to do this for the year 2019.
#create a manual dataframe with the last days of the months in 2019
LastDays <- structure(list(Date = structure(c(7L, 2L, 10L, 4L, 11L, 5L,
12L, 13L, 6L, 8L, 3L, 1L, 9L), .Label = c("10-12-2019", "28-2-2019",
"30-11-2019", "30-4-2019", "30-6-2019", "30-9-2019", "31-1-2019",
"31-10-2019", "31-12-2019", "31-3-2019", "31-5-2019", "31-7-2019",
"31-8-2019"), class = "factor")), class = "data.frame", row.names = c(NA,
-13L))
#remove transactions on other dates in a new dataframe
df_subset <- df[which(df$Date %in% LastDays$Date),]
#find Members which did transactions on all the last days of the month
Members <- df_subset %>% group_by(Member, Date) %>% summarise_all(funs(mean)) %>% select(Member, Date) %>% filter(n() >11)
Members <- unique(Members$Member)
#The information of all the members which transacted on all last dates of the year
df[which(df$Member %in% Members),]
