transforming messy wide data to long in R

user_12742992 0
Viewed 67

I am quite new to R and I am having some trouble trying to convert my df from wide to long. At the moment it looks like below:

ppt_num w.1.rt w.1.vrt w.2.rt w.2.vrt n.1.rt n.1.vrt n.2.rt n.2.vrt
1 0.90 2.00 1.25 2.05 1.01 1.85 2.06 1.76
2 1.02 2.04 3.02 2.45 1.07 1.95 2.54 1.60

There are two experimental conditions and two difficulty levels. For the experimental conditions "w" stands for want priming and "n" stands for need priming. For the difficulty level 1 refers to difficult trials and 2 refers to easy trials. Finally, rt (reaction time) and vrt (variance of reaction time) are my measurements of interest. I am trying to reshape my data so that it looks like below:

ppt_num type difficulty rt vrt
1 w 1 0.90 2.00
1 w 2 1.25 2.05
1 n 1 1.01 1.85
2 n 2 2.06 1.76
2 w 1 1.02 2.04
2 w 2 3.02 2.45
2 n 1 1.07 1.95
2 n 2 2.54 1.60

So far I have tried using melt() but this does not produce the desired outcome.

new_df <- melt(df, id.vars = c("ppt_num"))

Any advice on the approach to take here would be very much appreciated.

reshape2 r
4 Answers

Here is an option with pivot_longer, where we specify the names_sep to match the . after the digit in the column name, and then separate the 'grp' column into 'type' and 'difficulty'

library(dplyr)
library(tidyr)
df %>%
   pivot_longer(cols = -ppt_num, names_to = c('grp', '.value'), 
      names_sep = '(?<=\\d)\\.') %>% 
   separate(grp, into = c('type', 'difficulty'))

-output

# A tibble: 8 x 5
#  ppt_num type  difficulty    rt   vrt
#    <int> <chr> <chr>      <dbl> <dbl>
#1       1 w     1           0.9   2   
#2       1 w     2           1.25  2.05
#3       1 n     1           1.01  1.85
#4       1 n     2           2.06  1.76
#5       2 w     1           1.02  2.04
#6       2 w     2           3.02  2.45
#7       2 n     1           1.07  1.95
#8       2 n     2           2.54  1.6

The pattern used in names_sep matches the . (. is a metacharacter in regex that matches any character, so we escape to get the literal value), succeeding a digit ((?<=\\d) - regex lookaround to match for a digit) in the column name

data

df <- structure(list(ppt_num = 1:2, w.1.rt = c(0.9, 1.02), w.1.vrt = c(2, 
2.04), w.2.rt = c(1.25, 3.02), w.2.vrt = c(2.05, 2.45), n.1.rt = c(1.01, 
1.07), n.1.vrt = c(1.85, 1.95), n.2.rt = c(2.06, 2.54), n.2.vrt = c(1.76, 
1.6)), class = "data.frame", row.names = c(NA, -2L))
user_37322710

Here my version using data.table

library(data.table)
dt <- read.table(header = TRUE, text ="ppt_num  w.1.rt  w.1.vrt w.2.rt  w.2.vrt n.1.rt  n.1.vrt n.2.rt  n.2.vrt
1   0.90    2.00    1.25    2.05    1.01    1.85    2.06    1.76
2   1.02    2.04    3.02    2.45    1.07    1.95    2.54    1.60")
setDT(dt)
dt2 <- melt(dt,id.vars = "ppt_num") #First melt your initial data frame

dt2[,c("type","difficult", "varbl" ):= tstrsplit(variable, "\\.")] #Split the new column "variable" containing your ex-column names 
dt2$variable<-NULL # I don't need column variable anymore
dcast(dt2,ppt_num+type+difficult~varbl,value.var = "value") #casting your result fixing ppt_num, type, and difficult but sending varbl to columns 

   ppt_num type difficult   rt  vrt
1:       1    n         1 1.01 1.85
2:       1    n         2 2.06 1.76
3:       1    w         1 0.90 2.00
4:       1    w         2 1.25 2.05
5:       2    n         1 1.07 1.95
6:       2    n         2 2.54 1.60
7:       2    w         1 1.02 2.04
8:       2    w         2 3.02 2.45
user_43633150

Here is a base R option

reshape(
  reshape(
    setNames(df, sub("(.*)\\.(.*)", "\\2-\\1", names(df))),
    direction = "long",
    idvar = "ppt_num",
    varying = -1,
    timevar = "type"
  ),
  direction = "long",
  idvar = c("ppt_num", "type"),
  varying = -(1:2),
  timevar = "difficulty",
  sep = "-"
)

which gives

      ppt_num type difficulty   rt  vrt
1.1.w       1    1          w 0.90 2.00
2.1.w       2    1          w 1.02 2.04
1.2.w       1    2          w 1.25 2.05
2.2.w       2    2          w 3.02 2.45
1.1.n       1    1          n 1.01 1.85
2.1.n       2    1          n 1.07 1.95
1.2.n       1    2          n 2.06 1.76
2.2.n       2    2          n 2.54 1.60
user_121587570

You can do this only with pivot_longer as :

tidyr::pivot_longer(df, cols = -ppt_num, 
                    names_to = c('type', 'difficulty', '.value'), 
                    names_sep = '\\.')

#  ppt_num type  difficulty    rt   vrt
#    <int> <chr> <chr>      <dbl> <dbl>
#1       1 w     1           0.9   2   
#2       1 w     2           1.25  2.05
#3       1 n     1           1.01  1.85
#4       1 n     2           2.06  1.76
#5       2 w     1           1.02  2.04
#6       2 w     2           3.02  2.45
#7       2 n     1           1.07  1.95
#8       2 n     2           2.54  1.6
user_39629140
Related