Taking the prior time period's and other group's data

[julesp]

Hello! I'm trying to create a table like below with the code (which is supposed to grab the prior time period's and the opposite group's, information). It seems the problem now is that it is complaining about the length of y. I tried various things to try to fix this, but I feel stuck right now.

group<-c("A", "A", "B", "B", "A", "A", "B", "B", "A", "A", "B", "B")
time<-c(1,1,1,1,2,2,2,2,3,3,3,3)
age<-c(2,3,2,3,2,3,2,3,2,3,2,3)
var<-c(2,9,3,0,0,0,3,2,6,3,4,7)
df<-data.frame(cbind(group,time,age,var))

df$newvar<-NA

df <- arrange(df, time, group)
list<-unique(age)
listtime<-unique(time)

df$time <- as.numeric(df$time)
df$y<- df$time-1

df<-df %>% mutate_all(as.character)
a <- min(df$y)
b <- max(df$y)

for (j in 1:length(list)) {
for (x in a:b) {
i<-list[j]
c<-list
a_var<-df$var[df$time==c & df$group == "A" & df$age==i]
b_var<-df$var[df$time==c & df$group == "B" & df$age==i]

df<-df %>% mutate(newvar = ifelse(group== "A" & y==c & age==i, b_var,
ifelse(group=="B" & y==c & age==i, a_var,
NA)))
}
}

I'm trying to get this table:

Any help is appreciated!

[nirgrahamuk]

library(tidyverse)
group<-c("A", "A", "B", "B", "A", "A", "B", "B", "A", "A", "B", "B")
time<-c(1,1,1,1,2,2,2,2,3,3,3,3)
age<-c(2,3,2,3,2,3,2,3,2,3,2,3)
var<-c(2,9,3,0,0,0,3,2,6,3,4,7)
(df<-data.frame(cbind(group,time,age,var)) %>% group_by(
  group,time) %>% mutate(ingrpid = row_number()))  


(dfw <- pivot_wider(df,id_cols=c(time,ingrpid),
                    names_from = c(group),
                   values_from=c(age,var)) %>%
    group_by(ingrpid) %>%
    mutate(across(c("age_A",
                "age_B",
                "var_A",
                "var_B"),list(lag=lag))))

(df2 <- left_join(df,
                  dfw,
                  by=c("time","ingrpid")) %>%
    mutate(alt_var = case_when(group=="A" ~ var_B_lag,
                               TRUE ~ var_A_lag)) %>%
    select(group,time,age,var,alt_var))

# A tibble: 12 x 5
# Groups:   group, time [6]
# group time  age   var   alt_var
# <chr> <chr> <chr> <chr> <chr>  
# 1  A     1     2     2     NA     
# 2  A     1     3     9     NA     
# 3  B     1     2     3     NA     
# 4  B     1     3     0     NA     
# 5  A     2     2     0     3      
# 6  A     2     3     0     0      
# 7  B     2     2     3     2      
# 8  B     2     3     2     9      
# 9  A     3     2     6     3      
# 10 A     3     3     3     2      
# 11 B     3     2     4     0      
# 12 B     3     3     7     0  

[FJCC]

Here is another method.

group<-c("A", "A", "B", "B", "A", "A", "B", "B", "A", "A", "B", "B")
time<-c(1,1,1,1,2,2,2,2,3,3,3,3)
age<-c(2,3,2,3,2,3,2,3,2,3,2,3)
var<-c(2,9,3,0,0,0,3,2,6,3,4,7)
DF <- data.frame(group, time, age, var, stringsAsFactors = FALSE)
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
DF <- DF %>% mutate(JoinTime = time + 1,
                    JoinGroup = ifelse(group == "A", "B", "A")) 
DF
#>    group time age var JoinTime JoinGroup
#> 1      A    1   2   2        2         B
#> 2      A    1   3   9        2         B
#> 3      B    1   2   3        2         A
#> 4      B    1   3   0        2         A
#> 5      A    2   2   0        3         B
#> 6      A    2   3   0        3         B
#> 7      B    2   2   3        3         A
#> 8      B    2   3   2        3         A
#> 9      A    3   2   6        4         B
#> 10     A    3   3   3        4         B
#> 11     B    3   2   4        4         A
#> 12     B    3   3   7        4         A
DF <- left_join(DF, DF, by = c(time = "JoinTime", group = "JoinGroup", age = "age"))
DF <- DF %>% select(group, time, age, var = var.x, newvar = var.y)
DF
#>    group time age var newvar
#> 1      A    1   2   2     NA
#> 2      A    1   3   9     NA
#> 3      B    1   2   3     NA
#> 4      B    1   3   0     NA
#> 5      A    2   2   0      3
#> 6      A    2   3   0      0
#> 7      B    2   2   3      2
#> 8      B    2   3   2      9
#> 9      A    3   2   6      3
#> 10     A    3   3   3      2
#> 11     B    3   2   4      0
#> 12     B    3   3   7      0

^{Created on 2020-08-03 by the reprex package (v0.3.0)}

[nirgrahamuk]

elegant solution, thank you.

[julesp]

Thank you both so much! I really appreciate it. EDIT: I'm a STATA user, and I think that merge type of approach would have been my impulse in STATA as well. Not sure why I didn't think of that, oops! Thanks again.