R separate column based on digit change

Question

Using R's separate function I'm trying to seprate a column into new columns everytime a digit increases. Column is set up like so: Q1_W1 Q1_W2 Q2_W1 Q2_W2 Q3_W1 And so on until it ends with Q9. The desire is to seperate the groups splitting on Q1, Q2, Q3, and so forth.

what I have tried so far is only filling Q1.

nn3=nalhx%>%
 separate(q, into =c("Q1","Q2","Q3","Q4","Q5","Q6","Q7","Q8","Q9"), sep = "(^q+[0-9])")

Desired output: https://i.stack.imgur.com/u0r4Z.png

[See here](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) on making an R question that folks can help with. That includes a sample of data, all necessary code, and a clear explanation of what you're trying to do and what hasn't worked. — camille, Dec 29 '19 at 22:59

score 2 · Accepted Answer · answered Dec 29 '19 at 22:35

Based on the image showed, instead of separate, we can create a grouping column by taking the substring before the _ from 'q', and use pivot_wider to change from long to 'wide'

library(dplyr)
library(stringr)
library(tidyr)
nalhx %>% 
   group_by(grp = str_remove(q, '_.*')) %>%    
   mutate(rn = row_number()) %>% 
   ungroup %>%
   pivot_wider(names_from = grp, values_from = q, 
           values_fill = list(q = '')) %>%
   select(-rn)
# A tibble: 2 x 3
#  Q1    Q2    Q3   
#  <chr> <chr> <chr>
#1 Q1_W1 Q2_W1 Q3_W1
#2 Q1_W2 Q2_W2 ""

data

nalhx <- structure(list(q = c("Q1_W1", "Q1_W2", "Q2_W1", "Q2_W2", "Q3_W1"
)), class = "data.frame", row.names = c(NA, -5L))

That's a great solution. Soon as I can get pivot_longer/wider I will try it out. Thanks! — alphamelt, Dec 30 '19 at 14:44

R separate column based on digit change

1 Answers1

data