Search code examples
rdataframereshape

Convert data from wide to long format by checking the values of a row

I have the data below

data<-structure(list(id = c("R_88j7lG37gLfxk22", "R_6DK8lERVf8lSQf4"
), t1_choice = c("2", "3"), t2_choice = c("1", "3"), t3_choice = c("1", 
"2"), t4_choice = c("2", "1")), row.names = c(NA, -2L), class = c("tbl_df", 
"tbl", "data.frame"))

this is the first row my data:

enter image description here

and I want to convert it to a long format like below with this logic.For every participant there are 12 rows, because there are 4 tasks (4 't's) and 3 profiles in each task (3 'p's). The choice column is a binary where it is 1 if the profile in that row was chosen in that task and 0 if it was not, which is information contained in the 'tN_choice' columns.

enter image description here

my method is wrong

tasks<-4
profiles<-3
#column position of first task
cpft<-2

#column position of last task
cplt<-5

# Extracting choices
choices <- as.numeric(unlist(long[, cpft:cplt]))

# Create the new dataframe with id and choice columns
new_df <- data.frame(
  id = rep(data$id, each = tasks*profiles),
  choice = rep(0, times = length(id))
)

# Replacing values based on original choices
for (i in 1:(tasks*profiles)) {
  idx <- (i - 1) * profiles + choices[i]
  new_df$choice[idx] <- 1
}
Solution

  • Probably you can try this

    data %>%
    pivot_longer(-id) %>%
    summarise(choice = list(+(seq(3) == value)), .by = c(id, name)) %>%
    unnest(choice) %>%
    print(n = 1000000L) # just to print more rows

    which gives

       id                name      choice
   <chr>             <chr>      <int>
 1 R_88j7lG37gLfxk22 t1_choice      0
 2 R_88j7lG37gLfxk22 t1_choice      1
 3 R_88j7lG37gLfxk22 t1_choice      0
 4 R_88j7lG37gLfxk22 t2_choice      1
 5 R_88j7lG37gLfxk22 t2_choice      0
 6 R_88j7lG37gLfxk22 t2_choice      0
 7 R_88j7lG37gLfxk22 t3_choice      1
 8 R_88j7lG37gLfxk22 t3_choice      0
 9 R_88j7lG37gLfxk22 t3_choice      0
10 R_88j7lG37gLfxk22 t4_choice      0
11 R_88j7lG37gLfxk22 t4_choice      1
12 R_88j7lG37gLfxk22 t4_choice      0
13 R_6DK8lERVf8lSQf4 t1_choice      0
14 R_6DK8lERVf8lSQf4 t1_choice      0
15 R_6DK8lERVf8lSQf4 t1_choice      1
16 R_6DK8lERVf8lSQf4 t2_choice      0
17 R_6DK8lERVf8lSQf4 t2_choice      0
18 R_6DK8lERVf8lSQf4 t2_choice      1
19 R_6DK8lERVf8lSQf4 t3_choice      0
20 R_6DK8lERVf8lSQf4 t3_choice      1
21 R_6DK8lERVf8lSQf4 t3_choice      0
22 R_6DK8lERVf8lSQf4 t4_choice      1
23 R_6DK8lERVf8lSQf4 t4_choice      0
24 R_6DK8lERVf8lSQf4 t4_choice      0