condition by oldest date and id in r

user_12913640 0
Viewed 53

I would like to write a condition such as selecting V2 == "B" but only if "B" is the oldest one according to the date it belongs by id (V1).

library(data.table)
dt <- data.table(id <- c(rep(1,3),
                         rep(2,2),
                         rep(3,2)),
                 condition <- c(rep("A",2), "B",
                                "B","B",
                                "B","A"),
                 dates <- c("2018-01-20", "2018-01-03","2017-01-01",
                            "2018-05-02", "2018-04-01",
                            "2018-05-01", "2018-01-01")
                 )

# Result
#   V1 V2         V3
#1:  1  A 2018-01-20
#2:  1  A 2018-01-03
#3:  1  B 2017-01-01
#4:  2  B 2018-05-02
#5:  2  B 2018-04-01
#6:  3  B 2018-05-01
#7:  3  A 2018-01-01

The wanted results should be this one:

#   V1 V2         V3
#3:  1  B 2017-01-01
#5:  2  B 2018-04-01

As you can see, line 6 wasn't in my final wanted result since "B" is not the oldest condition but "A" is.

Thank you

data.table r
3 Answers

We can create an index and extract using the index

dt[dt[,  .(ind = .I[V2 == 'B' & V3 == min(as.Date(V3))]), V1]$ind]
#   V1 V2         V3
#1:  1  B 2017-01-01
#2:  2  B 2018-04-01

Or more compactly

 dt[, .SD[V3 == min(as.Date(V3)) & V2 == 'B'], by = V1]
user_37322710 
dt[order(dates), if(first(V2) == 'B') head(.SD, 1), V1]
#    V1 V2         V3
# 1:  1  B 2017-01-01
# 2:  2  B 2018-04-01
user_67627550

Another option without using by:

m <- c(B=1)
dt[order(dates, m[condition]), .SD[rowid(id)==1L & condition=="B"]]

output:

   id condition      dates
1:  1         B 2017-01-01
2:  2         B 2018-04-01

data:

library(data.table)
dt <- data.table(id = c(rep(1,3), rep(2,2), rep(3,2)),
    condition = c(rep("A",2), "B", "B","B", "B","A"),
    dates = c("2018-01-20", "2018-01-03","2017-01-01",
        "2018-05-02", "2018-04-01",
        "2018-05-01", "2018-01-01")
)
user_19894800
Related