How to split a column into multiple columns data

Question

I have list of TCGA Tumor sample ids as follows:

SAMPLE_ID
TCGA.13.1407.01A.01R.1565.13
TCGA.24.2254.01A.01R.1568.13
TCGA.24.0982.01A.01R.1565.13
TCGA.24.1847.01A.01R.1566.13
TCGA.24.2289.01A.01R.1568.13
TCGA.31.1959.01A.01R.1568.13

I want to split the sample id into: Project, TSS, Participant, Sample, Vial, Portion, Analyte, Plate, Center For example for first row it will be :

SAMPLE_ID                       Project TSS Participant Sample  Vial    Portion Analyte Plate   Center
TCGA.13.1407.01A.01R.1565.13    TCGA    13  1407        01      A       01      R       0182    13

I tried as below:

library(tidyr)
library(dplyr)
df = data.frame(SAMPLE_ID = c("TCGA.13.1407.01A.01R.1565.13", "TCGA.24.2254.01A.01R.1568.13", "TCGA.24.0982.01A.01R.1565.13",
                                "TCGA.24.1847.01A.01R.1566.13", "TCGA.24.2289.01A.01R.1568.13", "TCGA.31.1959.01A.01R.1568.13"))

then,

result = data %>% separate(SAMPLE_ID,
                            into = c("Project", "TSS", "Participant", "Sample", "Vial",
                                     "Portion", "Analyte", "Plate", "Center"),
                            sep = "\\.")

but it gave me:

Project TSS Participant Sample  Vial    Portion Analyte Plate   Center
<chr>   <chr>   <chr>   <chr>   <chr>   <chr>   <chr>   <chr>   <chr>
TCGA    13  1407    01A 01R 1565    13  NA  NA
TCGA    24  2254    01A 01R 1568    13  NA  NA
TCGA    24  0982    01A 01R 1565    13  NA  NA
TCGA    24  1847    01A 01R 1566    13  NA  NA
TCGA    24  2289    01A 01R 1568    13  NA  NA
TCGA    31  1959    01A 01R 1568    13  NA  NA

Answer 1

Using read.fwf (as @IRTFM suggests) is probably a good solution. First we could remove the periods and save to a tempfile.

> write.table(as.matrix(dat$SAMPLE_ID |> gsub('\\.', '', x=_)), tmp <- tempfile(),
+             col.names=FALSE, row.names=FALSE, quote=FALSE)
> 
> read.fwf(tmp, widths=c(4, 2, 4, 2, 1, 2, 1, 4, 2), header=FALSE, 
+          col.names=c("Project", "TSS", "Participant", "Sample", "Vial",
+                      "Portion", "Analyte", "Plate", "Center"))
  Project TSS Participant Sample Vial Portion Analyte Plate Center
1    TCGA  13        1407      1    A       1       R  1565     13
2    TCGA  24        2254      1    A       1       R  1568     13
3    TCGA  24         982      1    A       1       R  1565     13
4    TCGA  24        1847      1    A       1       R  1566     13
5    TCGA  24        2289      1    A       1       R  1568     13
6    TCGA  31        1959      1    A       1       R  1568     13

You probably can read in the file directly and skip the write.table step.

You can add some cleaning stuff if you depend on character columns and leading zeroes.

> read.fwf(tmp, widths=c(4, 2, 4, 2, 1, 2, 1, 4, 2), header=FALSE, 
+          col.names=c("Project", "TSS", "Participant", "Sample", "Vial",
+                      "Portion", "Analyte", "Plate", "Center")) |>
+   transform(TSS=sprintf('%02d', TSS), 
+             Participant=sprintf('%04d', Participant), 
+             Sample=sprintf('%02d', Sample),
+             Portion=sprintf('%02d', Portion),
+             Plate=as.character(Plate),
+             Center=as.character(Center))
  Project TSS Participant Sample Vial Portion Analyte Plate Center
1    TCGA  13        1407     01    A      01       R  1565     13
2    TCGA  24        2254     01    A      01       R  1568     13
3    TCGA  24        0982     01    A      01       R  1565     13
4    TCGA  24        1847     01    A      01       R  1566     13
5    TCGA  24        2289     01    A      01       R  1568     13
6    TCGA  31        1959     01    A      01       R  1568     13


> unlink(tmp) ## unlink if no longer needed

---

Data:

> dput(dat)
structure(list(SAMPLE_ID = c("TCGA.13.1407.01A.01R.1565.13", 
"TCGA.24.2254.01A.01R.1568.13", "TCGA.24.0982.01A.01R.1565.13", 
"TCGA.24.1847.01A.01R.1566.13", "TCGA.24.2289.01A.01R.1568.13", 
"TCGA.31.1959.01A.01R.1568.13")), class = "data.frame", row.names = c(NA, 
-6L))

Answer 2

Using separate from tidyr like @jynxmaze but with other separator sep = "\\.|(?<=\\d)(?=\\D)" and using extra and remove argument:

library(dplyr)
library(tidyr)

df %>%
  separate(SAMPLE_ID, 
           into = c("Project", "TSS", "Participant", "Sample", "Vial", "Portion", "Analyte", "Plate", "Center"),
           sep = "\\.|(?<=\\d)(?=\\D)", extra = "merge", remove = FALSE)

    SAMPLE_ID Project TSS Participant Sample Vial Portion Analyte Plate Center
1 TCGA.13.1407.01A.01R.1565.13    TCGA  13        1407     01    A      01       R  1565     13
2 TCGA.24.2254.01A.01R.1568.13    TCGA  24        2254     01    A      01       R  1568     13
3 TCGA.24.0982.01A.01R.1565.13    TCGA  24        0982     01    A      01       R  1565     13
4 TCGA.24.1847.01A.01R.1566.13    TCGA  24        1847     01    A      01       R  1566     13
5 TCGA.24.2289.01A.01R.1568.13    TCGA  24        2289     01    A      01       R  1568     13
6 TCGA.31.1959.01A.01R.1568.13    TCGA  31        1959     01    A      01       R  1568     13