CODEWITHSUNDEEP
r
user11740857
user11740857

Reputation: 480

Find Max date in the column as per month

Is there a way to find the max date in the date column as per its end of week

df
ColA 
2021-03-12      
2021-03-16      
2021-03-17      
2021-03-24           
2021-03-26      

df
ColA            Max_Date
2021-03-12      2021-03-12 (## Since Mar 12 is the end of week for 2021-03-12 )
2021-03-16      2021-03-19 (## Since Mar 19 is the end of week for 2021-03-16 )
2021-03-17      2021-03-19 (## Since Mar 19 is the end of week for 2021-03-17 )
2021-03-24      2021-03-26 (## Since Mar 26 is the end of week for 2021-03-24 )
2021-03-26      2021-03-26 (## Since Mar 26 is the end of week for 2021-03-26 )

So as per end of week, the max date is populated

Upvotes: 0

Views: 403

Answers (2)

G. Grothendieck
G. Grothendieck

Reputation: 269491

1) UNIX Epoch Below nextfri is slightly modified from https://cran.r-project.org/web/packages/zoo/vignettes/zoo-quickref.pdf . It accepts a Date or yyyy-mm-dd string or a vector of those and returns the same date if it is a Friday or the next date that is Friday otherwise. No packages are used.

It makes use of the fact that the UNIX Epoch 1970-01-01 is a Thursday so the next day 1970-01-02 is a Friday and if we take the number of weeks including the possibly fractional week at the end since then, round up to an integer number of weeks, convert to days and add that to 1970-01-02 we get the next Friday or the same day if it is already Friday. If you have the zoo package loaded then the origin= argument can optionally be omitted.

nextfri <- function(x) 7 * ceiling(as.numeric(as.Date(x)-5+4) / 7) +
  as.Date(5-4, origin = "1970-01-01")

x <- c("2021-03-12", "2021-03-16", "2021-03-17", "2021-03-24", "2021-03-26")
nextfri(x)
## [1] "2021-03-12" "2021-03-19" "2021-03-19" "2021-03-26" "2021-03-26"

2) as.POSIXlt Another base solution can be constructed by considering the current day of the week which we can get from as.POSIXlt and then use that to add an appropriate offset to get to the next Friday.

We take the difference from Friday, 5 - wday, and take that modulo 7 which adds 7 if it is negative.

If it were known that all the input dates were weekdays we could optionally omit the %% 7.

as.Date(x) + ((5 - as.POSIXlt(x)$wday) %% 7)
## [1] "2021-03-12" "2021-03-19" "2021-03-19" "2021-03-26" "2021-03-26"

3) cut.Date Another base solution can be obtained using cut.Date. It will give the previous Monday so just add 4 to get to Friday. If the input date is Saturday or Sunday that would give the previous Friday so add 7. If it were known that all th input dates were weekdays (no Saturdays or Sundays) then the last term could optionally be omitted.

as.Date(cut(as.Date(x), "week")) + 4 + 7 * (as.POSIXlt(x)$wday > 5)
## [1] "2021-03-12" "2021-03-19" "2021-03-19" "2021-03-26" "2021-03-26"

Upvotes: 2

AnilGoyal
AnilGoyal

Reputation: 26218

As already suggested lubridate::ceiling_date() is perfect for this scenario

lubridate::ceiling_date(as.Date(df$ColA), unit = "week", 
                        week_start = getOption("lubridate.week.start", 5),
                        change_on_boundary = F)

[1] "2021-03-12" "2021-03-19" "2021-03-19" "2021-03-26" "2021-03-26"

You can store it directly

df$max_date <- lubridate::ceiling_date(as.Date(df$ColA), unit = "week", 
                        week_start = getOption("lubridate.week.start", 5),
                        change_on_boundary = F)

> df
        ColA   max_date
1 2021-03-12 2021-03-12
2 2021-03-16 2021-03-19
3 2021-03-17 2021-03-19
4 2021-03-24 2021-03-26
5 2021-03-26 2021-03-26

Your df

df <- read.table(text = "ColA 
2021-03-12      
2021-03-16      
2021-03-17      
2021-03-24           
2021-03-26 ", header = T)

Upvotes: 1

Related Questions