tidyverse - restructure character-string in R

Question

I have the following test-dataframe. The variable times shows the number of seconds a person needed to answer different questions (e.g. f140, f130, f210, ...) in a questionnaire.

Example: Person 1 had 4 seconds for question f140, 10 seconds for question 130 etc. The questions in the questionnaire are randomised and the total number of questions can vary.

df <- data.frame(user = 1:4,
                 times = c(
                   "f140:4,f130:10,f110:3,f120:3,f210:5,f220:4,f240:5,f230:4,f300:4,f410:9,f420:4,f450:18,f500:64,",
                   "f120:4,f110:3,f140:7,f130:17,f240:10,f230:6,f220:4,f210:4,f300:6,f410:12,f420:10,f450:38,f500:42,",
                   "f130:26,f120:8,f140:5,f110:4,f220:5,f210:6,f230:9,f240:5,f300:8,f410:8,f420:5,f450:30,f500:3,",
                   "f120:9,f130:13,f110:5,f140:11,f210:8,f220:12,f240:7,f230:10,f300:8,f410:10,f420:8,f450:46,f500:111,"
  )
)

How can I get these data in a form like this:

user  |  question | time | order
1        f140        4     1
1        f130       10     2
... 
2        f120        4     1
2        f110        3     2
...

I was trying to use separate(), but failed so far. Thanks for help!

Answer 1

This can help:

library(dplyr)
library(tidyr)
#Code
new <- df %>%
  separate_rows(times,sep = ',') %>%
  separate(times,c('question','times'),sep=':') %>%
  filter(!is.na(times)) %>%
  group_by(user) %>%
  mutate(order=row_number())

Output:

# A tibble: 52 x 4
# Groups:   user [4]
    user question times order
   <int> <chr>    <chr> <int>
 1     1 f140     4         1
 2     1 f130     10        2
 3     1 f110     3         3
 4     1 f120     3         4
 5     1 f210     5         5
 6     1 f220     4         6
 7     1 f240     5         7
 8     1 f230     4         8
 9     1 f300     4         9
10     1 f410     9        10
# ... with 42 more rows