R - converting some dataframe columns to rows

Question

So let's say I have a data frame with X and Y variables and Z1, Z2 and Z3 subjects like this:

> df <- data.frame(X=c(0,0,1,1), Y=c(0,1,0,1), Z1=c(4,8,1,2), Z2=c(7,2,4,1), Z3=c(5,2,0,1))
> df
  X Y Z1 Z2 Z3
1 0 0  4  7  5
2 0 1  8  2  2
3 1 0  1  4  0
4 1 1  2  1  1

What I want to do is to put all results in one column Z and therefore have a dataframe that looks like this:

   X Y Z
1  0 0 4
2  0 1 8
3  1 0 1
4  1 1 2
5  0 0 7
6  0 1 2
7  1 0 4
8  1 1 1
9  0 0 5
10 0 1 2
11 1 0 0
12 1 1 1

What is the easiest way to do this? Note that there may be more than 3 subjects.

Answer 1

using tidyverse

library(tidyverse)
df <- data.frame(X=c(0,0,1,1), Y=c(0,1,0,1), Z1=c(4,8,1,2), Z2=c(7,2,4,1), Z3=c(5,2,0,1))
pivot_longer(df, cols = -c(X,Y), names_to = c(".value", NA), names_pattern = "(.)(.)")
#> # A tibble: 12 x 3
#>        X     Y     Z
#>    <dbl> <dbl> <dbl>
#>  1     0     0     4
#>  2     0     0     7
#>  3     0     0     5
#>  4     0     1     8
#>  5     0     1     2
#>  6     0     1     2
#>  7     1     0     1
#>  8     1     0     4
#>  9     1     0     0
#> 10     1     1     2
#> 11     1     1     1
#> 12     1     1     1

^{Created on 2021-01-11 by the reprex package (v0.3.0)}

using data.table

library(data.table)
df <- data.frame(X=c(0,0,1,1), Y=c(0,1,0,1), Z1=c(4,8,1,2), Z2=c(7,2,4,1), Z3=c(5,2,0,1))
dt <- as.data.table(df)
out <- melt(dt, id.vars = c("X", "Y"), value.name = "Z")[, variable := NULL]
head(out, n = 12)
#>     X Y Z
#>  1: 0 0 4
#>  2: 0 1 8
#>  3: 1 0 1
#>  4: 1 1 2
#>  5: 0 0 7
#>  6: 0 1 2
#>  7: 1 0 4
#>  8: 1 1 1
#>  9: 0 0 5
#> 10: 0 1 2
#> 11: 1 0 0
#> 12: 1 1 1

^{Created on 2021-01-11 by the reprex package (v0.3.0)}