CODEWITHSUNDEEP
rdplyrtidyr
user3570187
user3570187

Reputation: 1773

using historical data [prior period] to create new variable

I wanted to create two columns that take into account prior history[t-1] to create new columns that specify how many new activities and old activities are repeated or carried out in the current period [see below for the data structure].For example row 5, the algorithm should compare the new event of 'think' to the prior period [read, write] and as there was no prior 'think' in t-1 it is listed as 1 [for new] and no old events have been used in the period 3 [ 5th row] and hence it is 0.

event<- c('read', 'write', 'read', 'write', 'think', 'read', 'think', 'read')
person<- c('arun', 'arun','arun','arun','arun','john','john', 'john')
time <-  c(1, 1,2,2,3,1,2,3)
df<- data.frame(event, person,time)

    event person time  new  old

    read   arun    1    .    .
    write  arun    1    .    .
    read   arun    2    0    2
    write  arun    2    0    2
    think  arun    3    1    0
    read   john    1    .    .
    think  john    2    1    0
    read   john    3    1    0

Any suggestions on how this can be in achieved?

Upvotes: 0

Views: 200

Answers (1)

AnilGoyal
AnilGoyal

Reputation: 26218

event<- c('read', 'write', 'read', 'write', 'think', 'read', 'think', 'read')
person<- c('arun', 'arun','arun','arun','arun','john','john', 'john')
time <-  c(1, 1,2,2,3,1,2,3)
df<- data.frame(event, person,time)

library(tidyverse, warn.conflicts = FALSE)

df %>% 
  group_by(person, time) %>%
  summarise(new = list(event), .groups = 'drop') %>%
  group_by(person) %>%
  mutate(old = map2_int(new, lag(new), ~ sum(.x %in% .y)),
         new = map_int(new, length) - old) %>%
  mutate(across(new:old, ~ifelse(time == 1, NA, .))) %>%
  left_join( df, ., by = c('person', 'time'))

#>   event person time new old
#> 1  read   arun    1  NA  NA
#> 2 write   arun    1  NA  NA
#> 3  read   arun    2   0   2
#> 4 write   arun    2   0   2
#> 5 think   arun    3   1   0
#> 6  read   john    1  NA  NA
#> 7 think   john    2   1   0
#> 8  read   john    3   1   0

Created on 2021-07-21 by the reprex package (v2.0.0)

Upvotes: 1

Related Questions