Follow-up: Merging multiple wide-format data.frames into a single long-format data.frame-CodePudding

Following up on this answer, I was wondering if there is a way to merge wide-format data.frames (time1,time2,time3,time4,time1and2,time3and4) into a single long-format data.frame to achieve my Desired_output below?

I tried the following without success:

library(tidyverse)
rbind(cbind(time1, time = 1),cbind(time2, time = 2),
      cbind(time3, time = 3),cbind(time4, time = 4)) %>%
  pivot_longer(ac:bc,names_to = "DV", values_to = "score") %>%
  right_join(rbind(time1and2,time3and4))

time1 =read.table(text="
class id   order ac bc
1     1    s-c   1  2
",h=TRUE)

time2 =read.table(text="
class id   order ac bc
1     1    s-c   3  4
",h=TRUE)

time3 =read.table(text="

class id   order ac bc
1     1    s-c   5  6

",h=TRUE)

time4 =read.table(text="

class id   order ac bc
1     1    s-c   7  8

",h=TRUE)

time1and2 =read.table(text="
class id   order ex1S ex2S ex1C ex2C
1     1    s-c   8    5    6     1
",h=TRUE)

time3and4 =read.table(text="

class id   order ex1S ex2S ex1C ex2C
1     1    s-c   7    9    2     6

",h=TRUE)

Desired_output="
class id   order time DV score ave_ex
1     1    s-c   1    ac 1     (8 5)/2 =6.5  # ave_ex = average of `ex`
1     1    s-c   1    bc 2     (8 5)/2 =6.5
1     1    s-c   2    ac 3     (6 1)/2 =3.5
1     1    s-c   2    bc 4     (6 1)/2 =3.5

1     1    s-c   3    ac 5     (7 9)/2 =8 
1     1    s-c   3    bc 6     (7 9)/2 =8
1     1    s-c   4    ac 7     (2 6)/2 =4
1     1    s-c   4    bc 8     (2 6)/2 =4
"

CodePudding user response：

We could do this separately and bind

library(dplyr)
library(tidyr)
tbl1 <- rbind(cbind(time1, time = 1), cbind(time2, time = 2)) %>%
  pivot_longer(ac:bc,names_to = "DV", values_to = "score") %>%
  right_join(time1and2) %>%
  mutate(ave = case_when(
    time == 1 ~ mean(c(ex1S, ex2S)),
    time == 2 ~ mean(c(ex1C, ex2C))
  )) %>%
  select(-c(ex1S:ex2C))
tbl2 <- rbind(cbind(time3, time = 3), cbind(time4, time = 4)) %>%
  pivot_longer(ac:bc,names_to = "DV", values_to = "score") %>%
  right_join(time3and4) %>%
  mutate(ave = case_when(
    time == 3 ~ mean(c(ex1S, ex2S)),
    time == 4 ~ mean(c(ex1C, ex2C))
  )) %>%
  select(-c(ex1S:ex2C))
 rbind(tbl1, tbl2)
# A tibble: 8 × 7
  class    id order  time DV    score   ave
  <int> <int> <chr> <dbl> <chr> <int> <dbl>
1     1     1 s-c       1 ac        1   6.5
2     1     1 s-c       1 bc        2   6.5
3     1     1 s-c       2 ac        3   3.5
4     1     1 s-c       2 bc        4   3.5
5     1     1 s-c       3 ac        5   8  
6     1     1 s-c       3 bc        6   8  
7     1     1 s-c       4 ac        7   4  
8     1     1 s-c       4 bc        8   4

Or using a loop

library(purrr)
 imap_dfr(lst(time1and2, time3and4), ~
    mget(strsplit(str_replace(.y, 'and', ',time'), ',')[[1]], 
         inherit = TRUE) %>% 
   bind_rows(.id = 'time') %>%  
   pivot_longer(ac:bc,names_to = "DV", values_to = "score") %>% 
   right_join(.x) %>% 
  mutate(ave = case_when(time %in% c('time1', 'time3') ~ 
      mean(c(ex1S, ex2S)), TRUE ~ mean(c(ex1C, ex2C)))) %>% 
  select(-c(ex1S:ex2C)) )

-output

# A tibble: 8 × 7
  time  class    id order DV    score   ave
  <chr> <int> <int> <chr> <chr> <int> <dbl>
1 time1     1     1 s-c   ac        1   6.5
2 time1     1     1 s-c   bc        2   6.5
3 time2     1     1 s-c   ac        3   3.5
4 time2     1     1 s-c   bc        4   3.5
5 time3     1     1 s-c   ac        5   8  
6 time3     1     1 s-c   bc        6   8  
7 time4     1     1 s-c   ac        7   4  
8 time4     1     1 s-c   bc        8   4