I have a data set that amoungst other columns has date, sequence and low
columns, please see df
below.
The sequence from 1-to-9
is considered one block or one full cycle in the sequence
column
The data set has several such complete block/cycles and partially completed ones, eg: 1-to-4
This is what I'm trying to solve:
- Remove the partly completed cycles and then group the full cycles (see
df1
) - For each block/cycle (i.e. sequence from 1-to-9), I want to find the low of the block along with the day the low was happened.
If there are two lows with same value but on different dates, then it should only output the latest date (see the 3rd block in output)
library(lubridate) library(tidyverse) ### Sample data df <- data.frame(stringsAsFactors=FALSE, date = c("1/01/2019", "2/01/2019", "3/01/2019", "4/01/2019", "5/01/2019", "6/01/2019", "7/01/2019", "8/01/2019", "9/01/2019", "10/01/2019", "11/01/2019", "12/01/2019", "13/01/2019", "14/01/2019", "15/01/2019", "16/01/2019", "17/01/2019", "18/01/2019", "19/01/2019", "20/01/2019", "21/01/2019", "22/01/2019", "23/01/2019", "24/01/2019", "25/01/2019", "26/01/2019", "27/01/2019", "28/01/2019", "29/01/2019", "30/01/2019", "31/01/2019", "1/02/2019", "2/02/2019", "3/02/2019", "4/02/2019"), sequence = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 1, 2, 3, 4, 1, 2, 3, 4, 5, 6, 7, 8, 9, 1, 2, 3, 4, 1, 2, 3, 4, 5, 6, 7, 8, 9), low = c(96, 81, 43, 18, 43, 65, 48, 90, 69, 50, 41, 73, 1, 1, 7, 49, 16, 79, 2, 74, 8, 88, 56, 57, 66, 29, 79, 51, 52, 47, 42, 9, 41, 9, 50)) %>% mutate(date = dmy(date))
Data grouped by cycle/block
df1 <- data.frame(stringsAsFactors=FALSE, date = c("1/01/2019", "2/01/2019", "3/01/2019", "4/01/2019", "5/01/2019", "6/01/2019", "7/01/2019", "8/01/2019", "9/01/2019", "14/01/2019", "15/01/2019", "16/01/2019", "17/01/2019", "18/01/2019", "19/01/2019", "20/01/2019", "21/01/2019", "22/01/2019", "27/01/2019", "28/01/2019", "29/01/2019", "30/01/2019", "31/01/2019", "1/02/2019", "2/02/2019", "3/02/2019", "4/02/2019"), sequence = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 1, 2, 3, 4, 5, 6, 7, 8, 9, 1, 2, 3, 4, 5, 6, 7, 8, 9), low = c(96, 81, 43, 18, 43, 65, 48, 90, 69, 1, 7, 49, 16, 79, 2, 74, 8, 88, 79, 51, 52, 47, 42, 9, 41, 9, 50), group = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 3, 3)) %>% mutate(date = dmy(date))
Final output I am after
df_final <- data.frame(stringsAsFactors=FALSE,
date = c("4/01/2019", "14/01/2019", "3/02/2019"),
low = c(18, 1, 9)) %>% mutate(date = dmy(date))
Any ideas?
Ps. I ran into some problems formatting this question, hence the untidiness.