separate column with unknown name

Question

I have a data frame like this:

structure(list(header = 1:10, ST.adk.fumC.gyrB.icd.mdh.purA.recA = c(" 10 10 11 4 8 8 8 2", 
" 48 6 11 4 8 8 8 2", " 58 6 4 4 16 24 8 14", " 88* 6* 4 12 1 20 12 7", 
" 117 20 45 41 43 5 32 2", " 7036 526 7 1 1 8 71 6", " 101 43 41 15 18 11 7 6", 
" 3595 112 11 5 12 8 88 86", " 117 20 45 41 43 5 32 2", " 744 10 11 135 8 8 8 2"
)), row.names = c(NA, -10L), class = c("tbl_df", "tbl", "data.frame"
))

What I want to do is to split the second column into separate columns, separated by the "." in the column name. However, it's not always known what the name of the column is, which is why I cannot use the name of the column in dplyr' "separate" function.

I tried the following:

library(dplyr)
library(stringr)
library(tidyr)

# get new column names
ids <- unlist(strsplit(names(df)[-1],
                              split = ".",
                              fixed = TRUE))

# get name of column to split
split_column <- names(df)[-1]

df %>%
separate(split_column, into = ids, extra = "merge")

This works within the script file I am using, but when i source the script I get the following error:

Error: `var` must evaluate to a single number or a column name, not a character vector

Why does this work when I run it like normal in RStudio, but when I source the script it throws this error? Also, is this the optimal way of actually splitting a column of unknown name into new columns with unknown names?

I source the script with the following code, in another script file:

system(paste("Rscript script.R", opt$m, opt$o))

Where opt$m and opt$o are directory paths. This works fine with a similar script I have, but with the script above it throws an error.

I was hoping for some kind of function like separate_at, but that doesn't exist as of yet..

Answer 1

You could use strsplit().

split <- do.call(rbind, strsplit(gsub("\\*", "", df[, -1]), " "))[, -1]
df1 <- data.frame(df[, 1], split)
df1[] <- lapply(df1, function(x) as.numeric(as.character(x)))
names(df1) <- unlist(strsplit(names(df), split = ".", fixed=TRUE))

> df1
   header   ST adk fumC gyrB icd mdh purA recA
1       1   10  10   11    4   8   8    8    2
2       2   48   6   11    4   8   8    8    2
3       3   58   6    4    4  16  24    8   14
4       4   88   6    4   12   1  20   12    7
5       5  117  20   45   41  43   5   32    2
6       6 7036 526    7    1   1   8   71    6
7       7  101  43   41   15  18  11    7    6
8       8 3595 112   11    5  12   8   88   86
9       9  117  20   45   41  43   5   32    2
10     10  744  10   11  135   8   8    8    2

Data

df <-structure(list(header = 1:10, ST.adk.fumC.gyrB.icd.mdh.purA.recA = c(" 10 10 11 4 8 8 8 2", 
                                                                     " 48 6 11 4 8 8 8 2", " 58 6 4 4 16 24 8 14", " 88* 6* 4 12 1 20 12 7", 
                                                                     " 117 20 45 41 43 5 32 2", " 7036 526 7 1 1 8 71 6", " 101 43 41 15 18 11 7 6", 
                                                                     " 3595 112 11 5 12 8 88 86", " 117 20 45 41 43 5 32 2", " 744 10 11 135 8 8 8 2"
)), row.names = c(NA, -10L), class = c("tbl_df", "tbl", "data.frame"
))