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.
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