How can I "transpose" the data in this way (see the image)?

Question

I've already asked a similar question last week, but I found out that I need also the ID column and I'm not able to add multiple columns in the code that the person suggested me, so I need to ask it again.

I need to transpose the data from the left table to the right table ().

     data <- data.frame(
+   stringsAsFactors = FALSE,
+   ID = c(15262L,17382L,14892L,15262L,17382,14892L),
+   TEST = c("Pre", "Pre","Pre", "Post", "Post","Post"),
+   Q1 = c(2L, 5L,3L,3L,2L,4L),
+   Q2 = c(3L,4L,5L,3L,4L,5L),
+   Q3 = c(4L,3L,3L,2L,2L,1L))
>   
> data
     ID TEST Q1 Q2 Q3
1 15262  Pre  2  3  4
2 17382  Pre  5  4  3
3 14892  Pre  3  5  3
4 15262 Post  3  3  2
5 17382 Post  2  4  2
6 14892 Post  4  5  1

the code suggested by the person was:

library(dplyr)
library(tidyr)

data %>% 
  pivot_longer(-Test) %>% 
  pivot_wider(names_from = Test, values_from = value) %>% 
  unnest()

I tried to:

final_data<- data %>% 
  pivot_longer(cols= starts_with('Q')) %>% 
  pivot_wider(names_from = Test, values_from = value) %>% 
  unnest()

and

final_data2<- data %>% 
  pivot_longer(-Test,-ID) %>% 
  pivot_wider(names_from = Test, values_from = value) %>% 
  unnest()

and other combinations that I don't remember, but I continue to have some warning or error messages.

ID and Q1,Q2 etc are Double type and Test as Character,

Can someone help me??

Thank you very much!

Please don't add data as images, but instead as reproducible code. [See here](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) for some tips on how to do that — jpsmith, Mar 20 '23 at 12:48
The image that you posted doesn't use any of the data except for the `ID` column. I'm assuming the following does *not* solve your problem? `tidyr::crossing(ID = data$ID, Questions = paste0('Q', 1 : 3))` — Konrad Rudolph, Mar 20 '23 at 12:50
Hi, Thank you Konrad for you fast reply. This is just an example that I created (I can't publish the real data). The ID are just some random numbers (I could also write "1", "2" etc, but I didn't want that people think that are just crescent number) and in the real database I have more than 45 questions with names like Q1, Q1_1, Q#1 etc. I just need a formula to change the format of the table — Sara, Mar 20 '23 at 12:57
You are on a right track, but there is no need for unnest. We need to reshape twice, wide-to-long then long-to-wide. — zx8754, Mar 20 '23 at 13:07
We dont need you to publish the real data, just reproducible data. You could easily turn what you have in the image into code (like the answer did for you) — jpsmith, Mar 20 '23 at 13:07
ID TEST Q1 Q2 Q3 1 15262 Pre 2 3 4 2 17382 Pre 5 4 3 3 14892 Pre 3 5 3 4 15262 Post 3 3 2 5 17382 Post 2 4 2 6 14892 Post 4 5 1 — Sara, Mar 20 '23 at 13:27

score 3 · Answer 1 · answered Mar 20 '23 at 13:01

df <- data.frame(
  stringsAsFactors = FALSE,
  ID = c(1L, 2L, 1L, 2L),
  TEST = c("Pre", "Pre", "Post", "Post"),
  Q1 = c(6L, 1L, 9L, 9L),
  Q2 = c(8L, 2L, 5L, 1L),
  Q3 = c(9L, 2L, 9L, 9L)
)

library(tidyverse)

df
#>   ID TEST Q1 Q2 Q3
#> 1  1  Pre  6  8  9
#> 2  2  Pre  1  2  2
#> 3  1 Post  9  5  9
#> 4  2 Post  9  1  9

df %>% 
  pivot_longer(cols = -c(ID, TEST), names_to = "Q") %>% 
  pivot_wider(id_cols = c(ID, Q), names_from = TEST, values_from = value)
#> # A tibble: 6 x 4
#>      ID Q       Pre  Post
#>   <int> <chr> <int> <int>
#> 1     1 Q1        6     9
#> 2     1 Q2        8     5
#> 3     1 Q3        9     9
#> 4     2 Q1        1     9
#> 5     2 Q2        2     1
#> 6     2 Q3        2     9

^{Created on 2023-03-20 with reprex v2.0.2}

S-SHAAF · Accepted Answer · 2023-03-20T14:14:01.237

One option is to first convert Q1-Q3 into long format using pivot_longer() and then reshape to wide format taking TEST as target. And finally relocate the column names using select():

library(reshape2)
library(tidyverse)

df %>% pivot_longer(cols = contains("Q"), names_to = "Questions") %>% 
            dcast(ID + Questions  ~ TEST) %>% 
            select(ID, Questions, Pre, Post)

     ID Questions Pre Post
1 14892        Q1   3    4
2 14892        Q2   5    5
3 14892        Q3   3    1
4 15262        Q1   2    3
5 15262        Q2   3    3
6 15262        Q3   4    2
7 17382        Q1   5    2
8 17382        Q2   4    4
9 17382        Q3   3    2

Alternatively, we could first use pivot_longer() taking Q1-Q3 as targets and then use pivot_wider() taking TEST as target column to convert to wide format:

df %>% pivot_longer(cols = contains("Q"), names_to = "Questions") %>% 
  pivot_wider(names_from = "TEST",values_from = "value") 

     ID Questions   Pre  Post
  <int> <chr>     <int> <int>
1 15262 Q1            2     3
2 15262 Q2            3     3
3 15262 Q3            4     2
4 17382 Q1            5     2
5 17382 Q2            4     4
6 17382 Q3            3     2
7 14892 Q1            3     4
8 14892 Q2            5     5
9 14892 Q3            3     1

The data

df <- read.table(text = "ID TEST    Q1  Q2  Q3
15262   Pre 2   3   4
17382   Pre 5   4   3
14892   Pre 3   5   3
15262   Post    3   3   2
17382   Post    2   4   2
14892   Post    4   5   1
",header=T, check.names=F)

How can I "transpose" the data in this way (see the image)?

2 Answers2