Reshaping Wide to Long but not reshaping all variables on R

Question

Sorry to bother you. I have a longitudinal dataset but it is in wide format.

There are 380 variables and over 3000 id's.

I am trying to turn the data into a long format so there is a time variable column.

The time variable is indicated by a suffix infront of each column variable so V0 is the Time at baseline and V01 is the time at 1 year. V02 is the time at second year. V03 is the time at 3 years etc.

Most of the variables are categorised using this time variable but not all. So v0pase is the pase at baseline. V01pase is the pase at 1 year.

Not all the variables have a time variable. Some are categorical e.g gender, depression etc.

I am trying to reshape it into a long format in R however my code does not appear to work:

The database is called "TrialData"

TrialDataLong <- reshape(TrialData, varying = c((grepl("v",))), direction = "long", 
                                              timevar = "visit/year", idvar = 
                                             "id", sep = "v")

I have linked an image of a snapshot of the old data to what I am aiming for.

I would be grateful if someone can be kind enough to assist in this. I am fairly newish to R.

Many thanks in advance.

Best wishes James

Answer 1

Here is another solution you could use. Here are some notes with regard to the solution:

• Except for variables ID and sex other variables of the original data set has a distinct name pattern. It starts with a non-digit character followed by a digit and again a couple non-digit character
• According to the names of the second data set we are only interested to capture the digit part of the names and the following couple non-digits, discarding the starting non-digit character
• So I had to define 2 capturing groups, the first is only one digit represented by (\\d) in the regex and the second is at least 3 non-digit represented by ([a-z]{3,}). You may pay attention to {3,} that I left the other end open for example {3,4} means between 3 and 4 characters
• In this regex "a-z([a-z]{3,})" the first part [a-z] represents any non-digit character corresponding the the starting names of the first data set but as you noticed it is not put in a parenthesis as we don't need to capture them
• As for names_to argument there are also 2 groups defined corresponding to two capturing groups in names_pattern the first group will be put in Time variable and here is the more interesting .value values which indicates that part of the name (the second group) are actually the values we are trying to measure which are pase and dep

There are other ways of achieving this and that was one way to go about it.

library(dplyr)
library(tidyr)

TrialDataLong %>%
  pivot_longer(starts_with("v"), 
               names_to = c("Time", ".value"), 
               names_pattern = "[a-z](\\d)([A-Za-z]{3,})")

# A tibble: 8 x 5
     id sex   Time   pase dep  
  <dbl> <chr> <chr> <dbl> <chr>
1     1 M     0        10 Yes  
2     1 M     1        12 Yes  
3     1 M     2        14 Yes  
4     1 M     3        13 Yes  
5     2 F     0        15 No   
6     2 F     1        15 Yes  
7     2 F     2        16 Yes  
8     2 F     3        16 Yes  

Answer 2

For the sake of completeness, here is a solution using melt() and the new measure() function:

library(data.table) # development version 1.14.1 used here
TrialDataLong <- 
  melt(setDT(TrialData), measure.vars = measure(time, value.name, pattern = "v(\\d)(\\w+)"))
TrialDataLong 

   id sex time pase dep
1:  1   M    0   10 Yes
2:  2   F    0   15  No
3:  1   M    1   12 Yes
4:  2   F    1   15 Yes
5:  1   M    2   14 Yes
6:  2   F    2   16 Yes
7:  1   M    3   13 Yes
8:  2   F    3   16 Yes

Reordered for better comparison with OP's expected result:

TrialDataLong[order(id, time)]

   id sex time pase dep
1:  1   M    0   10 Yes
2:  1   M    1   12 Yes
3:  1   M    2   14 Yes
4:  1   M    3   13 Yes
5:  2   F    0   15  No
6:  2   F    1   15 Yes
7:  2   F    2   16 Yes
8:  2   F    3   16 Yes

Data

library(data.table)
TrialData <- fread("
id sex v0pase v1pase v0dep v1dep v2pase v3pase v2dep v3dep
 1   M     10     12   Yes   Yes     14     13   Yes   Yes
 2   F     15     15    No   Yes     16     16   Yes   Yes")

Answer 3

You can use tidyr::pivot_longer with names_to and names_pattern argument.

tidyr::pivot_longer(TrialData, 
                    cols = -c(id, sex),
                    names_to = c('Time', '.value'), 
                    names_pattern = 'v(\\d+)([a-z]+)')

#     id sex   Time   pase dep  
#  <dbl> <chr> <chr> <dbl> <chr>
#1     1 M     0        10 Yes  
#2     1 M     1        12 Yes  
#3     1 M     2        14 Yes  
#4     1 M     3        13 Yes  
#5     2 F     0        15 No   
#6     2 F     1        15 Yes  
#7     2 F     2        16 Yes  
#8     2 F     3        16 Yes