Splitting columns ending with "Process" into many columns with individual values in R

Question

I have a dataframe like this

ID <- c("ID21","ID22","ID23","ID24")
STR_PL_CAN_EVOLVE_PROCESS <- c("CCP_A,CCP_B","CCQ_A,CCQ_B,CCQ_C","IOT_A,CCP_B","CCQ_B,IOT_B")
Average <- c(7.5,6.5,7.1,6.6)
STR_VD_CAN_MEASURE_PROCESS <- c("Length,Breadth","Breadth,Width","Height,Length,Width","Width,Length")
Passfail <- c("Pass","Pass","Fail","Fail")

df <- data.frame(ID,STR_PL_CAN_EVOLVE_PROCESS,Average,STR_VD_CAN_MEASURE_PROCESS,Passfail,stringsAsFactors=FALSE)

I am trying to separate the values in the columns ending with "process" into several columns using tidyverse and doing it this way.

library(tidyverse)
df1 <- df %>%
  separate(STR_PL_CAN_EVOLVE_PROCESS, 
           paste0("ST_PL_CA_EV_PR","_Path",
                  seq(1:10)),
           sep = ",") %>%
  separate(STR_VD_CAN_MEASURE_PROCESS, 
           paste0("ST_VD_CA_ME_PR","_Path",
                  seq(1:10)),
           sep = ",")

This works but I manually do a lot of things here (input the column names, new column names). Here are some things that I am trying to achieve

Automatically supply the names of the columns ending with "PROCESS" and separate those columns.
Extract the first 2 characters in the columns names (separated by underscores) as new column names. For example: STR_PL_CAN_EVOLVE_PROCESS becomes ST_PL_CA_EV_PR
Remove columns that are only NA's

My desired output is

    ID ST_PL_CA_EV_PR_Path1 ST_PL_CA_EV_PR_Path2 ST_PL_CA_EV_PR_Path3 Average ST_VD_CA_ME_PR_Path1 ST_VD_CA_ME_PR_Path2 ST_VD_CA_ME_PR_Path3 Passfail
  ID21                CCP_A                CCP_B                 <NA>     7.5               Length              Breadth                 <NA>     Pass
  ID22                CCQ_A                CCQ_B                CCQ_C     6.5              Breadth                Width                 <NA>     Pass
  ID23                IOT_A                CCP_B                 <NA>     7.1               Height               Length                Width     Fail
  ID24                CCQ_B                IOT_B                 <NA>     6.6                Width               Length                 <NA>     Fail

My actual dataset has around 35 columns ending with "PROCESS". Can someone point me in the right direction?

Possible duplicate of [Split data frame string column into multiple columns](https://stackoverflow.com/questions/4350440/split-data-frame-string-column-into-multiple-columns) — IceCreamToucan, Nov 13 '18 at 17:53

score 1 · Accepted Answer · answered Nov 13 '18 at 17:55

Here is an option with cSplit

library(splitstackshape)
library(dplyr)
df %>% 
   cSplit(c("STR_PL_CAN_EVOLVE_PROCESS", "STR_VD_CAN_MEASURE_PROCESS"), 
                     ',', drop = TRUE)
#ID Average Passfail STR_PL_CAN_EVOLVE_PROCESS_1 STR_PL_CAN_EVOLVE_PROCESS_2 STR_PL_CAN_EVOLVE_PROCESS_3
#1: ID21     7.5     Pass                       CCP_A                       CCP_B                        <NA>
#2: ID22     6.5     Pass                       CCQ_A                       CCQ_B                       CCQ_C
#3: ID23     7.1     Fail                       IOT_A                       CCP_B                        <NA>
#4: ID24     6.6     Fail                       CCQ_B                       IOT_B                        <NA>
#   STR_VD_CAN_MEASURE_PROCESS_1 STR_VD_CAN_MEASURE_PROCESS_2 STR_VD_CAN_MEASURE_PROCESS_3
#1:                       Length                      Breadth                         <NA>
#2:                      Breadth                        Width                         <NA>
#3:                       Height                       Length                        Width
#4:                        Width                       Length                         <NA>

Athanasia Mowinckel · Answer 2 · 2018-11-13T19:45:44.517

A pure tidyverse version. Many steps, but I believe it makes what you are after.

How about this:

df %>% 

# Grabs all columns ending with process
gather(key,val, ends_with("PROCESS")) %>% 

# Separate all now previous column names by "_"
separate(key, paste0("Pat",1:5)) %>% 

# Mutate all columns starting with "Pat" to just two chars
mutate_at(vars(starts_with("Pat")), substr, 1, 2) %>% 

# Separat cell values on comma
separate(val, paste0("Path",1:3)) %>% 

# Gather all Path columns into a key and value pair
gather(Path, val,starts_with("Path")) %>% 

# Unite all columns starting with "Pat" into one
unite(key, starts_with("Pat")) %>% na.omit() %>% 

# Spread the data using "key" as columns, and "val" as vell values
spread(key,val)

    ID Average Passfail ST_PL_CA_EV_PR_Path1 ST_PL_CA_EV_PR_Path2 ST_PL_CA_EV_PR_Path3 ST_VD_CA_ME_PR_Path1
1 ID21     7.5     Pass                  CCP                    A                  CCP               Length
2 ID22     6.5     Pass                  CCQ                    A                  CCQ              Breadth
3 ID23     7.1     Fail                  IOT                    A                  CCP               Height
4 ID24     6.6     Fail                  CCQ                    B                  IOT                Width
  ST_VD_CA_ME_PR_Path2 ST_VD_CA_ME_PR_Path3
1              Breadth                 <NA>
2                Width                 <NA>
3               Length                Width
4               Length                 <NA>

Splitting columns ending with "Process" into many columns with individual values in R

2 Answers2