Creating a tenure column in Days in R

问题:

*Edited

I am trying to create a column in a df that shows me the day number in a client's tenure. Here is the code to create a mock df for this:-

     Date<-c("20/07/2018", "21/07/2018", "25/07/2018", "02/08/2018", "05/08/2018", "10/08/2018")
     ClientId<-c("aaa", "bbb", "ccc", "aaa", "bbb", "ccc")
     EventId<-c("klk109", "rrt234", "hjk786", "yyu777", "tyw909", "nnl991")
     dateclient<-cbind(Date, ClientId)
     LoginDates<-cbind(dateclient, EventId)
     View(LoginDates)

which should give you something like this:-

   head(LoginDates)

      Date     ClientId  EventId 
  "20/07/2018" "aaa"    "klk109"
  "21/07/2018" "bbb"    "rrt234"
  "25/07/2018" "ccc"    "hjk786"
  "02/08/2018" "aaa"    "yyu777"
  "05/08/2018" "bbb"    "tyw909"
  "10/08/2018" "ccc"    "nnl991"

Essentialy, I want to create a column to add onto the end like this

     Date    ClientId  EventId   tenureDay
 "20/07/2018" "aaa"    "klk109"      1
 "21/07/2018" "bbb"    "rrt234"      1
 "25/07/2018" "ccc"    "hjk786"      1
 "02/08/2018" "aaa"    "yyu777"     13
 "05/08/2018" "bbb"    "tyw909"     15
 "10/08/2018" "ccc"    "nnl991"     16

However, my main issue in my dataset (the above being a mock df), some Clients have had more than one interaction per day (some have had 10, 20 and so on). The code I wrote (a "for" loop and some data.table code) has returned the number of interactions (or EventIds), and not the day number in tenure. If a client has been in the service for 10 days and has had, say 4 interactions during that time, I want the tenureDay column to represent the day in their tenure that particular interaction took place on.

Hope this makes sense, thank you massively in advance! :)

回答1:

Thank you for amending the question!

For reproductivity:

LoginDates <- fread("Date     ClientId  EventId 
                    2018-07-20 aaa    klk109
                    2018-07-21 bbb    rrt234
                    2018-07-25 ccc    hjk786
                    2018-08-02 aaa    yyu777
                    2018-08-05 bbb    tyw909
                    2018-08-10 ccc    nnl991")

Using dplyr, you could try this:

LoginDates %>%
  group_by(ClientId) %>%
  mutate(tenureDay = as.Date(Date) - head(as.Date(Date),1))

I really hope this solves your problem!

EDIT:

If you do not want your result to appear like x Days then try:

LoginDates %>%
  group_by(ClientId) %>%
  mutate(tenureDay = as.numeric(as.Date(Date) - head(as.Date(Date),1)))