i have a dataframe with 2 columns id, cat_list
id cat_list
1 A
2 A|B
3 E|F|G
4 I
5 P|R|T|Z
i want to achieve the below using R code.
id cat_list1 cat_list2 cat_list3 cat_list4
1 A
2 A B
3 E F G
4 I
5 P R T Z
We can use cSplit. Here, we don't need to worry to about the number of splits as it will automatically detect it.
library(splitstackshape)
cSplit(df1, "cat_list", "|")
# id cat_list_1 cat_list_2 cat_list_3 cat_list_4
#1: 1 A NA NA NA
#2: 2 A B NA NA
#3: 3 E F G NA
#4: 4 I NA NA NA
#5: 5 P R T Z
NOTE: It may be better to fill with NA rather than ''.
tidyr::separate is handy:
library(tidyr)
df %>% separate(cat_list, into = paste0('cat_list', 1:4), fill = 'right')
## id cat_list1 cat_list2 cat_list3 cat_list4
## 1 1 A <NA> <NA> <NA>
## 2 2 A B <NA> <NA>
## 3 3 E F G <NA>
## 4 4 I <NA> <NA> <NA>
## 5 5 P R T Z
