CODEWITHSUNDEEP
rdata.table
AVal
AVal

Reputation: 617

data.table way to paste value in new column conditioning on value in another column

could anyone suggest how to do that? I have this data:

ty<-data.table(id=c(1,1,1,2,2,3,3,4,4,5,5),
           date=c("11-23-2015","06-22-2015","07-22-2016","03-03-2012","11-08-2015","11-10-2012","11-23-2015","11-12-2012","12-02-2015","08-24-2016","12-25-2008"),
           event=c("open","gt","gf","gf","open","ff","open","open","close","tr","ww"))
ty$date<-as.Date(ty$date,format="%m-%d-%Y")

        id       date event
     1:  1 2015-11-23  open
     2:  1 2015-06-22    gt
     3:  1 2016-07-22    gf
     4:  2 2012-03-03    gf
     5:  2 2015-11-08  open
     6:  3 2012-11-10    ff
     7:  3 2015-11-23  open
     8:  4 2012-11-12  open
     9:  4 2015-12-02 close
    10:  5 2016-08-24    tr
    11:  5 2008-12-25    ww

I need to extract the date corresponding to the event "open" and paste it in a fourth column indexing by id, like in the following example:

        id       date event open_date
 1:  1 2015-11-23  open      2015-11-23
 2:  1 2015-06-22    gt      2015-11-23
 3:  1 2016-07-22    gf      2015-11-23
 4:  2 2012-03-03    gf      2015-11-08
 5:  2 2015-11-08  open      2015-11-08
 6:  3 2012-11-10    ff      2015-11-23
 7:  3 2015-11-23  open      2015-11-23
 8:  4 2012-11-12  open      2012-11-12
 9:  4 2015-12-02 close      2012-11-12
10:  5 2016-08-24    tr      NA
11:  5 2008-12-25    ww      NA

I used this: 

ty[, open_date := if (event == "open") paste(date), by=id]

but it pastes the date only in the reference row, whereas me I'd need to paste the date in all the id row. Might not be optimal, but this is what I need. Thanks and regards

Upvotes: 3

Views: 108

Answers (2)

h3rm4n
h3rm4n

Reputation: 4187

Converting the solutions provided in the comments by @Sotos and @DavidArenburg in a community wiki answer:

# @Sotos' solution
ty[, open_date := date[event == 'open'], by = id]
# @DavidArenburg's solution:
ty[ty[event == "open"], open_date := i.date, on = .(id)]

The result of both solutions:

#     id       date event  open_date
#  1:  1 2015-11-23  open 2015-11-23
#  2:  1 2015-06-22    gt 2015-11-23
#  3:  1 2016-07-22    gf 2015-11-23
#  4:  2 2012-03-03    gf 2015-11-08
#  5:  2 2015-11-08  open 2015-11-08
#  6:  3 2012-11-10    ff 2015-11-23
#  7:  3 2015-11-23  open 2015-11-23
#  8:  4 2012-11-12  open 2012-11-12
#  9:  4 2015-12-02 close 2012-11-12
# 10:  5 2016-08-24    tr       <NA>
# 11:  5 2008-12-25    ww       <NA>

Upvotes: 4

akrun
akrun

Reputation: 887531

We group by 'id', create a logical vector of 'event' (event=="open"), subset the 'date' corresponding to that and assign it to crate 'open_date'

ty[,open_date:= date[event=="open"][1L] , by = id]

Or another option is match

ty[, open_date := date[match('open', event)], by = id]

NOTE: Assuming that there is a only a single 'open' per each 'id'

Upvotes: 1

Related Questions