Separate string into 3 columns: text, number, text

3
votes

I'm working with a large dataset (~1500 rows), and when I built the dataset, I didn't think ahead about separating my identifiers, so they're lumped into one long string.

The identifying string is in a column labeled "Polygon_Name". I'd like to keep this column, and split the string values in this column into 3 additional columns.

So for example, if any "Polygon_Name" cell has a number embedded in it, such as Canker14B, I'd like to end up with the following columns: (1) the original Polygon_Name, (2) all text before the number, (3) the number, (4) all text after the number.

Small subset of my data:

df <- structure(list(Bolt_ID = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L), .Label = "N1T.3.4.15.0.C", class = "factor"), 
    Polygon_Name = structure(c(10L, 1L, 9L, 6L, 3L, 7L, 2L, 8L, 
    4L, 5L), .Label = c("C", "Canker15B", "Canker15Left", "Canker15Right", 
    "Canker16", "Canker17", "CankS15B", "CankS16", "CankS17", 
    "S"), class = "factor"), Measure = c(19.342, 25.962, 0.408, 
    0.008, 0.074, 0.41, 0.011, 0.251, 0.056, 0.034)), .Names = c("Bolt_ID", 
"Polygon_Name", "Measure"), row.names = c(1L, 2L, 4L, 5L, 6L, 
7L, 8L, 9L, 10L, 11L), class = "data.frame")

Current output:

enter image description here

Ultimate output (I built this manually):

enter image description here

I've figured out how to extract the number with the following code:

library(stringr)
regexp <- "[[:digit:]]+"
df$Poly_Num <- str_extract(df$Polygon_Name, regexp)

But I'm still struggling to pull out the text before and after the number. Any thoughts would be appreciated.

2
rstringsplit

2 Answers

3
votes

An idea via tidyverse would be,

library(tidyverse)

df %>% 
 mutate(Poly_num = gsub('\\D+', '', Polygon_Name)) %>% 
 separate(Polygon_Name, into = c('Poly_type', 'Poly_letter'), sep = '[0-9]+', remove = FALSE)

#          Bolt_ID  Polygon_Name Poly_type Poly_letter Measure Poly_num
#1  N1T.3.4.15.0.C             S         S        <NA>  19.342         
#2  N1T.3.4.15.0.C             C         C        <NA>  25.962         
#3  N1T.3.4.15.0.C       CankS17     CankS               0.408       17
#4  N1T.3.4.15.0.C      Canker17    Canker               0.008       17
#5  N1T.3.4.15.0.C  Canker15Left    Canker        Left   0.074       15
#6  N1T.3.4.15.0.C      CankS15B     CankS           B   0.410       15
#7  N1T.3.4.15.0.C     Canker15B    Canker           B   0.011       15
#8  N1T.3.4.15.0.C       CankS16     CankS               0.251       16
#9  N1T.3.4.15.0.C Canker15Right    Canker       Right   0.056       15
#10 N1T.3.4.15.0.C      Canker16    Canker               0.034       16

A one-liner would be to use extract from tidyr (compliments of @docendodiscimus)

tidyr::extract(df, Polygon_Name, c("a","b","c"), "^([^0-9]+)(\\d*)([^0-9]*)$", 
                                                       remove = FALSE, convert = TRUE)
1
votes

Since you are already using stringr, you can get these using str_match

str_match(df$Polygon_Name, "([[:alpha:]]*)([[:digit:]]*)([[:alpha:]]*)")[,2:4]
      [,1]     [,2] [,3]   
 [1,] "S"      ""   ""     
 [2,] "C"      ""   ""     
 [3,] "CankS"  "17" ""     
 [4,] "Canker" "17" ""     
 [5,] "Canker" "15" "Left" 
 [6,] "CankS"  "15" "B"    
 [7,] "Canker" "15" "B"    
 [8,] "CankS"  "16" ""     
 [9,] "Canker" "15" "Right"
[10,] "Canker" "16" ""

To add this to your existing data.frame, you could use

PName = str_match(df$Polygon_Name, "([[:alpha:]]*)([[:digit:]]*)([[:alpha:]]*)")[,2:4]
df = data.frame(df, PName)
names(df)[4:6] = c("Poly_Type", "Poly_Num", "Poly_Letter")