convert wide format to long format

Question

At the moment, my dataset is in a wide format, I would like to change it to a different format I have a data frame:

df <- structure(list(Pat = c(88L, 89L, 90L, 91L, 96L, 105L), res1 = c(63L, 
77L, 73L, 61L, 101L, 86L), res2 = c(173L, 187L, 179L, 171L, 174L, 
184L), res3 = c(369L, 519L, 669L, 719L, 619L, 419L), res4 = c(37L, 
32L, 34L, 35L, 30L, 33L), res5 = c(79L, 61L, 89L, 93L, 46L, 77L
), res6 = c(204L, 180L, 190L, 184L, 149L, 180L), res1.1 = c(495L, 
113L, 109L, 97L, 137L, 122L), res2.1 = c(209L, 209L, 209L, 209L, 
209L, 209L), res3.1 = c(405L, 555L, 705L, 755L, 655L, 455L), 
    res4.1 = c(73L, 68L, 70L, 71L, 66L, 69L), res5.1 = c(115L, 
    97L, 125L, 129L, 82L, 113L), res6.1 = c(240L, 216L, 226L, 
    220L, 185L, 216L), res1.2 = c(413L, 563L, 713L, 763L, 663L, 
    463L), res2.2 = c(81L, 76L, 78L, 79L, 74L, 77L), res3.2 = c(123L, 
    105L, 133L, 137L, 90L, 121L), res4.2 = c(248L, 224L, 234L, 
    228L, 193L, 224L), res5.2 = c(539L, 157L, 153L, 141L, 181L, 
    166L), res6.2 = c(253L, 253L, 253L, 253L, 253L, 253L)), class = "data.frame", row.names = c(NA, 
-6L))

I would like to bring it to this form:

data <- structure(list(Pat = c(88L, 89L, 90L, 91L, 96L, 105L, 88L, 89L, 
90L, 91L, 96L, 105L, 88L, 89L, 90L, 91L, 96L, 105L), time = c(1L, 
1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 
3L), res1 = c(63L, 77L, 73L, 61L, 101L, 86L, 495L, 113L, 109L, 
97L, 137L, 122L, 413L, 563L, 713L, 763L, 663L, 463L), res2 = c(173L, 
187L, 179L, 171L, 174L, 184L, 209L, 209L, 209L, 209L, 209L, 209L, 
81L, 76L, 78L, 79L, 74L, 77L), res3 = c(369L, 519L, 669L, 719L, 
619L, 419L, 405L, 555L, 705L, 755L, 655L, 455L, 123L, 105L, 133L, 
137L, 90L, 121L), res4 = c(37L, 32L, 34L, 35L, 30L, 33L, 73L, 
68L, 70L, 71L, 66L, 69L, 248L, 224L, 234L, 228L, 193L, 224L), 
    res5 = c(79L, 61L, 89L, 93L, 46L, 77L, 115L, 97L, 125L, 129L, 
    82L, 113L, 539L, 157L, 153L, 141L, 181L, 166L), res6 = c(204L, 
    180L, 190L, 184L, 149L, 180L, 240L, 216L, 226L, 220L, 185L, 
    216L, 253L, 253L, 253L, 253L, 253L, 253L)), class = "data.frame", row.names = c(NA, 
-18L))

I tried to do this with:

library(dplyr)
library(tidyr)
z <- gather(data, Var, Score, -1) %>%
     separate(Var, into = c("time", "Var2"), sep = 3) %>%
     group_by(time = match(time, unique(time))) %>%
     mutate(Question = row_number()) %>%
     select(-Var2)

But it does not give out what I need

Ronak Shah · Accepted Answer · 2021-07-26T03:45:29.657

Not sure if it is a mistake but the 1st set of res values don't have '.number' in them. We may adjust the columns using rename_with and then use pivot_longer.

library(dplyr)
library(tidyr)

df %>%
  rename_with(~paste0(., '.3'), matches('^res\\d+$')) %>%
  pivot_longer(cols = -Pat, 
               names_to = c('.value', 'time'), 
               names_sep = '\\.') 

#     Pat time   res1  res2  res3  res4  res5  res6
#   <int> <chr> <int> <int> <int> <int> <int> <int>
# 1    88 3        63   173   369    37    79   204
# 2    88 1       495   209   405    73   115   240
# 3    88 2       413    81   123   248   539   253
# 4    89 3        77   187   519    32    61   180
# 5    89 1       113   209   555    68    97   216
# 6    89 2       563    76   105   224   157   253
# 7    90 3        73   179   669    34    89   190
# 8    90 1       109   209   705    70   125   226
# 9    90 2       713    78   133   234   153   253
#10    91 3        61   171   719    35    93   184
#11    91 1        97   209   755    71   129   220
#12    91 2       763    79   137   228   141   253
#13    96 3       101   174   619    30    46   149
#14    96 1       137   209   655    66    82   185
#15    96 2       663    74    90   193   181   253
#16   105 3        86   184   419    33    77   180
#17   105 1       122   209   455    69   113   216
#18   105 2       463    77   121   224   166   253

If you don't want to hardcode the column suffix use -

df %>%
  pivot_longer(cols = -Pat, 
               names_to = c('.value', 'time'), 
               names_sep = '\\.') %>%
  group_by(Pat) %>%
  mutate(time = row_number()) %>%
  ungroup

This returns a warning since there is no '.' in certain column names but the warning is safe to ignore.

Thank you, but you can do something with ".3". do not enter it yourself, but get it with a certain shift? — GOGA GOGA, Jul 26 '21 at 03:42

convert wide format to long format

1 Answers1