Converting rows into columns based on the values in the rows, in R

Question

This is the table I am working with. I want to convert reservation into separate columns.

I want it to be transformed into something like this. I have been trying to do this using reshape2 and dplyr's separate but I didn't find a solution.

Answer 1

You may try

library(tidyverse)

df %>%
  rowwise %>%
  mutate(reservation_main = str_split(reservation,'_' ,simplify = T)[1],
         reservation_no = paste0('_',str_split(reservation,'_' ,simplify = T)[2])) %>%
  select(id, response_id, reservation_main, reservation_no) %>%
  pivot_wider(names_from = reservation_no, values_from = response_id)


     id reservation_main  `_1`  `_2`  `_3`
  <dbl> <chr>            <dbl> <dbl> <dbl>
1 31100 A                    1     1     0
2 31100 B                    1     1     0
3 31100 C                    1     0     0