Pivot / Transpose in R

April 25, 2024

Below is a sample dataset:

data.frame(
  Index = seq(1,5),
  X_50 = sample(1:5, replace = TRUE),
  X_90 = sample(1:5, replace = TRUE),
  Y_50 = sample(1:5, replace = TRUE),
  Y_90 = sample(1:5, replace = TRUE),
  Z_50 = sample(1:5, replace = TRUE),
  Z_90 = sample(1:5, replace = TRUE)
)

Index	X_50	X_90	Y_50	Y_90	Z_50	Z_90
1	3	5	1	10	3	4
2	8	8	6	6	9	7
3	9	3	3	7	7	4
4	3	10	8	10	6	1
5	7	2	3	5	10	8

I am trying to pivot the columns for the dataset to look like this:

Index	percentile_value	X	Y	Z
1	50	3	1	3
1	90	5	10	4
2	50	8	6	9
2	90	8	6	7
3	50	9	3	7
3	90	3	7	4

and so forth…

I tried pivot_longer, but I am not getting the above result. Can someone please help me out here. Thank you.

>Solution :

You can use pivot_longer() function from the tidyr package.

library(tidyr)

df <- data.frame(
        Index = seq(1,5),
        X_50 = sample(1:5, replace = TRUE),
        X_90 = sample(1:5, replace = TRUE),
        Y_50 = sample(1:5, replace = TRUE),
        Y_90 = sample(1:5, replace = TRUE),
        Z_50 = sample(1:5, replace = TRUE),
        Z_90 = sample(1:5, replace = TRUE)
)

df
#>   Index X_50 X_90 Y_50 Y_90 Z_50 Z_90
#> 1     1    2    1    3    5    2    5
#> 2     2    4    1    3    1    4    2
#> 3     3    2    5    5    4    5    2
#> 4     4    1    5    1    5    3    3
#> 5     5    1    2    1    2    1    1

df |> pivot_longer(cols = -Index, 
                   names_to = c(".value", "percentile_value"),
                   names_sep = "_") 
#> # A tibble: 10 × 5
#>    Index percentile_value     X     Y     Z
#>    <int> <chr>            <int> <int> <int>
#>  1     1 50                   2     3     2
#>  2     1 90                   1     5     5
#>  3     2 50                   4     3     4
#>  4     2 90                   1     1     2
#>  5     3 50                   2     5     5
#>  6     3 90                   5     4     2
#>  7     4 50                   1     1     3
#>  8     4 90                   5     5     3
#>  9     5 50                   1     1     1
#> 10     5 90                   2     2     1

^{Created on 2024-04-25 with reprex v2.1.0}