tidyr: Separate a column into a variable number of columns

user_7707288 0
Viewed 396

I have a variable in my data frame which contains the lengths of answering different questions in my questionnaire. The data are structured as follows:

data <- data.frame(variables = c("q1:2,q2:3,q3:4,q4:10,q5:1",
                                 "q2:3,q1:2,q3:2,q5:2,q4:9",
                                 "q1:1,q2:4,q5:8"))
        separate(variables, sep=",", into=??)

q1:2 means that this respondent needed 2 seconds in order to answer question 1 (q1).

Now, I'd like to separate() this column using the separator ",". But I don't know what the "into"-parameter should be, because not all respondents answered the same number of questions.

The goal is to have a data frame like this (it's not about the durations, just about the positions of the questions within each questionnaire):

pos_q1 pos_q2 pos_q3 pos_q4 pos_q5
----------------------------------
     1      2      3      4      5
     2      1      3      5      4
     1      2     NA     NA      3

Can someone help? Thanks!

tidyr r
1 Answers

You can first get data in long format with separate_rows, then separate into different columns, for each row create a row number column and get data in wide format.

library(dplyr)
library(tidyr)

data %>%
  mutate(id = row_number()) %>%
  separate_rows(variables, sep = ',') %>%
  separate(variables, c('question', 'time'), sep = ':') %>%
  group_by(id) %>%
  mutate(time = row_number()) %>%
  ungroup %>%
  pivot_wider(names_from = question,values_from=time, names_prefix = 'pos_') %>%
  select(-id)

# A tibble: 3 x 5
#  pos_q1 pos_q2 pos_q3 pos_q4 pos_q5
#   <int>  <int>  <int>  <int>  <int>
#1      1      2      3      4      5
#2      2      1      3      5      4
#3      1      2     NA     NA      3
user_39629140
Related