Reshaping pre-post data

Question

I have a data set

df <- data.frame("ID" = c("sue_1","bob_2","nick_3","joe_4"),
                 "1_confidence.x" = c(3,3,1,5),
                 "2_reading.x" = c(4,3,2,5),
                 "3_maths.x" = c(3,2,4,2),
                 "1_confidence.y" = c(3,2,3,4),
                 "2_reading.y" = c(3,4,2,1),
                 "3_maths.y" = c(3,4,2,5)
)

Giving this df:

> df
ID X1_confidence.x X2_reading.x X3_maths.x X1_confidence.y X2_reading.y X3_maths.y
1  sue_1               3            4          3               3            3          3
2  bob_2               3            3          2               2            4          4
3 nick_3               1            2          4               3            2          2
4  joe_4               5            5          2               4            1          5

I would like it to get into this format:

      ID Test X1_confidence X2_reading X3_maths
1  sue_1  pre             3          4        3
2  sue_1 post             3          3        3
3  bob_2  pre             3          3        2
4  bob_2 post             2          4        4
5 nick_3  pre             1          2        4
6 nick_3 post             3          2        2
7  joe_4  pre             5          5        2
8  joe_4 post             4          1        5

I've tried reshape and gather, but just can't seem to figure it out...

With all due respect to @Jaap, but I'm so excited to try out `tidyr 1.0.0`, so here is my shot. `pivot_longer(df, -ID, names_to = c(".value","Test"), names_sep = "\\.") %>% mutate(Test=ifelse(Test=="x","pre","post"))` — A. Suliman, Sep 23 '19 at 06:20
@A.Suliman That was what I was trying for. In which case do we mention `.value` argument? — Ronak Shah, Sep 23 '19 at 06:56
@RonakShah when we need the original columns values to serve as they are in the new dataframe. from [here](https://tidyr.tidyverse.org/dev/articles/pivot.html#multiple-observations-per-row) _Note the special name .value: this tells pivot_longer() that that part of the column name specifies the “value” being measured (which will become a variable in the output)._ — A. Suliman, Sep 23 '19 at 07:00
@NelsonGon pre is the first set (.x), post the second set (.y). — Gerard, Sep 23 '19 at 21:11

score 1 · Answer 1 · answered Sep 23 '19 at 05:26

This should do the trick:

df_long <- reshape(
  data = df,
  varying = list(c("X1_confidence.x","X1_confidence.y"),
                 c("X2_reading.x","X2_reading.y"),
                 c("X3_maths.x","X3_maths.y")),
  idvar = 'ID',
  v.names = c('X1_confidence', 'X2_reading', 'X3_maths'),
  timevar = 'Test',
  times = c('pre', 'post'),
  direction = 'long'
)

Then just sort by ID:

df_long <- df_long[order(df_long$ID, decreasing = T), ]

score 1 · Accepted Answer · answered Sep 23 '19 at 05:32

There should be a more "direct" way to do this only with pivot_longer. I was not able to get the arguments correct for it. Here is one way using pivot_longer and pivot_wider together from tidyr 1.0.0

library(dplyr)
library(tidyr)

df %>%
  pivot_longer(cols = starts_with("X"), names_to = "key") %>%
  mutate(key = sub("\\.x$|\\.y$", "", key)) %>%
  group_by(ID, key) %>%
  mutate(Test =  c("pre", "post")) %>%
  pivot_wider(c(ID, Test), key)

#  ID     Test  X1_confidence X2_reading X3_maths
#  <fct>  <chr>         <dbl>      <dbl>    <dbl>
#1 sue_1  pre               3          4        3
#2 sue_1  post              3          3        3
#3 bob_2  pre               3          3        2
#4 bob_2  post              2          4        4
#5 nick_3 pre               1          2        4
#6 nick_3 post              3          2        2
#7 joe_4  pre               5          5        2
#8 joe_4  post              4          1        5

If your tidyr is not updated here is the same using gather and spread

df %>%
  gather(key, value, -ID) %>%
  mutate(key = sub("\\.x$|\\.y$", "", key)) %>%
  group_by(key) %>%
  mutate(Test =  c("pre", "post")) %>%
  spread(key, value)

Awesome! The tidyr one worked perfectly and fast! I hadn't tried the new tidyr commands, but I can follow what you did nicely, thanks! — Gerard, Sep 23 '19 at 21:10

Reshaping pre-post data

2 Answers2