Hello,
Here is a dplyr solution. arrange() sorts by name and desc(ddate) and
top_n keeps the first 2 after grouping by ddate. Then it's a matter of
being careful with diff() in the summarise instruction.
library(dplyr)
DF1 %>%
mutate(ddate = as.Date(ddate)) %>%
arrange(name, desc(ddate)) %>%
group_by(name) %>%
top_n(2) %>%
summarise(diff = ifelse(n() > 1, diff(rev(ddate)), 0))
#Selecting by ddate
## A tibble: 3 x 2
# name diff
# <chr> <dbl>
#1 A 76
#2 B 305
#3 c 0
Hope this helps,
Rui Barradas
Às 03:58 de 15/05/20, Val escreveu:
HI All,
I have a sample of data frame
DF1<-read.table(text="name ddate
A 2019-10-28
A 2018-01-25
A 2020-01-12
A 2017-10-20
B 2020-11-20
B 2019-10-20
B 2017-05-20
B 2020-01-20
c 2009-10-01 ",header=TRUE)
1. I want sort by name and ddate on decreasing order and the output
should like as follow
A 2020-01-12
A 2019-01-12
A 2018-01-25
A 2017-10-20
B 2020-11-21
B 2020-11-01
B 2019-10-20
B 2017-05-20
c 2009-10-01
2. Take the top two rows by group( names) and the out put should like
A 2020-01-12
A 2019-01-12
B 2020-11-21
B 2020-11-01
c 2009-10-01
3. Within each group (name) get the date difference between the
first and second rows dates. If a group has only one row then the
difference should be 0
The final out put is
Name diff
A 365
B 20
C 0
Here is my attempt and have an issue at the sorting
DF1$DTime <- as.POSIXct(DF1$ddate , format = "%Y-%m-%d")
DF2 <- DF1[order(DF1$name, ((as.Date(DF1$DTime, decreasing = TRUE)))), ]
not working
Any help?
Thank you
______________________________________________
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
______________________________________________
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
