wide to long format R

Question

I've got a dataset like this:

dat1 <- read.table(header=TRUE, text="
ID A_1 B_1 C_1 A_2 B_2 C_2 
1 1 2 1 5 5 5
2 1 3 3 4 4 1
3 1 3 1 3 2 2
4 2 5 5 3 2 2
5 1 4 1 2 1 3
")

I would like to convert this to a long format, with one column for the ID, one for the system (1 or 2), one for the variable (A, B, or C) and one with the value. I can't figure out how to do that, I would be very grateful if somebody could help me out.

I already tried the pivot_longer command, but it only gives me three columns one for the ID one for the variables and one for the value.

Thanks!!

try `reshape(dat1, idvar=1, varying=2:7, direction="long", sep="_")` — jay.sf, Jan 02 '21 at 10:55

score 1 · Answer 1 · answered Jan 02 '21 at 10:18

You can use pivot_longer in the following way :

tidyr::pivot_longer(dat1, cols = -ID, 
                    names_to = c('variable', 'system'), names_sep = '_')

#    ID variable system value
#   <int> <chr>    <chr>  <int>
# 1     1 A        1          1
# 2     1 B        1          2
# 3     1 C        1          1
# 4     1 A        2          5
# 5     1 B        2          5
# 6     1 C        2          5
# 7     2 A        1          1
# 8     2 B        1          3
# 9     2 C        1          3
#10     2 A        2          4
# … with 20 more rows

wide to long format R

1 Answers1