How to split one column into multiple columns based on value (normaliztion in R)

Question

I have a column of values in an R dataframe that contains an code for a qualification in the format Faculty-Program-Campus-YearLevel. Example below:

Qualification_Code
200-N137-P-2
200-N136-P-3
200-N136-P-2
200-N135-V-1
300-G112-M-2

What I want is to split Qualification_Code into separate Faculty/Program/Campus/Level columns using R, i.e. ideally I'd like the following result:

Faculty Program Campus YearLevel
200 N137 P 2
200 N136 P 3
200 N136 P 2
200 N135 V 1
300 G112 M 2

Is there some built-in function or a package that allows this functionality? I've attempted this using RSQLite but my SQL skills are fairly underdeveloped (I'm a data science freshman with very little coding experiencing). Thanks for the help.

score 0 · Answer 1 · answered Mar 16 '20 at 07:29

Using strsplit.

res <- setNames(do.call(rbind.data.frame, 
        strsplit(dat$Qualification_Code, "-")),
        c("Faculty", "Program", "Campus", "YearLevel"))
res
#   Faculty Program Campus YearLevel
# 1     200    N137      P         2
# 2     200    N136      P         3
# 3     200    N136      P         2
# 4     200    N135      V         1
# 5     300    G112      M         2

Data

dat <- structure(list(Qualification_Code = c("200-N137-P-2", "200-N136-P-3", 
"200-N136-P-2", "200-N135-V-1", "300-G112-M-2")), row.names = c(NA, 
-5L), class = "data.frame")

score 0 · Answer 2 · answered Mar 16 '20 at 07:32

Use read.table

read.table(text = df$Qualification_Code, sep = "-",
           col.names = c("Faculty", "Program", "Campus", "YearLevel"))

#   Faculty Program Campus YearLevel
# 1     200    N137      P         2
# 2     200    N136      P         3
# 3     200    N136      P         2
# 4     200    N135      V         1
# 5     300    G112      M         2

How to split one column into multiple columns based on value (normaliztion in R)

2 Answers2