Long-formatting multiple wide-format data frames-CodePudding

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

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)

time1and2 =read.table(text="

class id   order ex1S ex2S ex1C ex2C  k1   k2   t1  t2
1     1    s-c   8    5    6     1    400  600  30  50

",h=TRUE)

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

CodePudding user response：

First, make them long format:

time1 <- pivot_longer(time1, c(ac, bc), names_to = "DV", values_to = "score") %>% mutate(time = 1) 
time2 <- pivot_longer(time2, c(ac, bc), names_to = "DV", values_to = "score") %>% mutate(time = 2) 
time1and2 <- pivot_longer(time1and2, c(k1:t2), names_to = c(".value", "time"), names_sep = "(\\d)") %>% 
  mutate(time = 1:2)

Then, unite:

bind_rows(time1, time2) %>% left_join(time1and2) %>% 
  mutate(ave = case_when(
    time == 1 ~ mean(c(ex1S, ex2S)),
    time == 2 ~ mean(c(ex1C, ex2C))
  )) %>%
  select(-c(ex1S:ex2C))

#Joining, by = c("class", "id", "order", "time")
## A tibble: 4 × 9
#  class    id order DV    score  time     k     t   ave
#  <int> <int> <chr> <chr> <int> <dbl> <int> <int> <dbl>
#1     1     1 s-c   ac        1     1   400    30   6.5
#2     1     1 s-c   bc        2     1   400    30   6.5
#3     1     1 s-c   ac        3     2   600    50   3.5
#4     1     1 s-c   bc        4     2   600    50   3.5

CodePudding user response：

In time1and2 (see the df2 part), you need to rename ex1S, ex2S, ex1C, ex2C to ex11, ex21, ex12, ex22 at first (here I use chartr() to achieve it), so that you can stack the pairs of

(ex11, ex12) to ex1
(ex21, ex22) to ex2
(k1, k2) to k
(t1, t2) to t

at the same time.

library(tidyverse)

df1 <- list(time1, time2) %>%
  map_dfr(~ pivot_longer(.x, ac:bc, names_to = "DV", values_to = "score"), .id = "time")

df2 <- time1and2 %>%
  rename_with(~ chartr("SC", "12", .x), starts_with("ex")) %>%
  pivot_longer(
    ex11:t2,
    names_to = c(".value", "time"),
    names_pattern = "(. )(.)"
  )

left_join(df1, df2) %>%
  mutate(ave_ex = (ex1   ex2) / 2)

# # A tibble: 4 × 11
#   time  class    id order DV    score   ex1   ex2     k     t ave_ex
#   <chr> <int> <int> <chr> <chr> <int> <int> <int> <int> <int>  <dbl>
# 1 1         1     1 s-c   ac        1     8     5   400    30    6.5
# 2 1         1     1 s-c   bc        2     8     5   400    30    6.5
# 3 2         1     1 s-c   ac        3     6     1   600    50    3.5
# 4 2         1     1 s-c   bc        4     6     1   600    50    3.5