CODEWITHSUNDEEP
rdplyr
Parseltongue
Parseltongue

Reputation: 11697

Mutate from long to wide based on regex match in dplyr

I have a dataset that has a number of columns that are prefaced with the following convention "quiz.{number between 1-33}.{variable_name}". So, for example, "quiz.1.player.trial_counter" or "quiz.33.submitted_answer"

I'm currently grabbing these columns like so:

 dat[, c(names(data[, grep('quiz\\.[0-9]*.',  names(dat))]))]

Each row is an individual's answers. I want to have a column that extracts the number from each of the quiz prefaces, and another column describing the variable.

Let's say I have this simplified dataset:

dat <- read.table(text="ID quiz.1.player.question quiz.1.player.solution quiz.2.player.question quiz.2.player.solution 
Trx1 A Yes 4 No
Trx1 B Maybe 5 No
Trx2 E No 6 Why
Trx2 B Yes 8 Yes
Trx3 B Definitely 9 Maybe
Trx3 C Yes 8 Yes
Trx5 F No 7 Why
Trx5 D Maybe 0 Ha", header=T)

Here's a part of the desired sample output

output <- read.table(text="id quiz_number key value
Trx1 1 player.question A
Trx1 1 player.solution Yes
Trx1 2 player.question 4
Trx1 2 player.solution No
Trx2 1 player.question B
", header=T)

I have tried various things with gather and separate and extract, but cannot figure out how to achieve the desired effect.

Upvotes: 2

Views: 125

Answers (1)

Maurits Evers
Maurits Evers

Reputation: 50728

As for a tidyverse solution, we can reshape data from wide to long, and then separate entries to reproduce the expected output format.

library(tidyverse)
dat %>%
    gather(k, value, -ID) %>%
    separate(k, c("quiz_number", "key"), "\\.(?=player)") %>%
    mutate(quiz_number = str_replace(quiz_number, "quiz\\.", ""))
#     ID quiz_number             key      value
#1  Trx1           1 player.question          A
#2  Trx1           1 player.question          B
#3  Trx2           1 player.question          E
#4  Trx2           1 player.question          B
#5  Trx3           1 player.question          B
#6  Trx3           1 player.question          C
#7  Trx5           1 player.question          F
#8  Trx5           1 player.question          D
#9  Trx1           1 player.solution        Yes
#10 Trx1           1 player.solution      Maybe
#11 Trx2           1 player.solution         No
#12 Trx2           1 player.solution        Yes
#13 Trx3           1 player.solution Definitely
#14 Trx3           1 player.solution        Yes
#15 Trx5           1 player.solution         No
#16 Trx5           1 player.solution      Maybe
#17 Trx1           2 player.question          4
#18 Trx1           2 player.question          5
#19 Trx2           2 player.question          6
#20 Trx2           2 player.question          8
#21 Trx3           2 player.question          9
#22 Trx3           2 player.question          8
#23 Trx5           2 player.question          7
#24 Trx5           2 player.question          0
#25 Trx1           2 player.solution         No
#26 Trx1           2 player.solution         No
#27 Trx2           2 player.solution        Why
#28 Trx2           2 player.solution        Yes
#29 Trx3           2 player.solution      Maybe
#30 Trx3           2 player.solution        Yes
#31 Trx5           2 player.solution        Why
#32 Trx5           2 player.solution         Ha

Upvotes: 2

Related Questions