CODEWITHSUNDEEP
rregexdplyr
elmaroto10
elmaroto10

Reputation: 536

Split numbers from letters in a mixed string of characters and put it into columns using Regex in R

I have a string of basketball player stats like in the example below:

stats <- c("40pt 2rb 1as 2st 2to 4trey 11-20fg 14-14ft",
           "7pt 5rb 1as 2st 1bl 3to 3-5fg 1-4ft",
           "0pt 1rb 1as 0-2fg")

Ideally I would like to transform this string into tabular format:

stats table

This is the key for each column:

Upvotes: 0

Views: 151

Answers (2)

Onyambu
Onyambu

Reputation: 79208

use dcast from reshape 2 package:

m=gsub("(\\d+)-(\\d+)(\\w+)","\\1\\3_m \\2\\3_a",stats)
n=gsub("(\\d+)(\\S*)","\\1 \\2",gsub("\\s","\n",m))
o=cbind(read.table(text=n),group=rep(1:length(n),lengths(strsplit(n,"\n"))))
dcast(o,group~V2,value.var="V1")
  group as bl fg_a fg_m ft_a ft_m pt rb st to trey
1     1  1 NA   20   11   14   14 40  2  2  2    4
2     2  1  1    5    3    4    1  7  5  2  3   NA
3     3  1 NA    2    0   NA   NA  0  1 NA NA   NA

Using base R 

> m=gsub("(\\d+)-(\\d+)(\\w+)","\\1\\3_m \\2\\3_a",stats)
> n=gsub("(\\d+)(\\S*)","\\1 \\2",gsub("\\s","\n",m))
> o=lapply(n,function(x)rev(read.table(text=x)))
> p=Reduce(function(x,y)merge(x,y,by="V2",all=T),o)
> read.table(text=do.call(paste,data.frame(t(p))),h=T)
  as fg_a fg_m ft_a ft_m pt rb st to trey bl
1  1   20   11   14   14 40  2  2  2    4 NA
2  1    5    3    4    1  7  5  2  3   NA  1
3  1    2    0   NA   NA  0  1 NA NA   NA NA

Upvotes: 0

akrun
akrun

Reputation: 886968

We split the string at the boundary between letter and digit to create the list ('lst'), loop through the list, change it to a data.frame with column names from the alternate split values, rbind the elements with rbindlist, split the elements having - to multiple columns with cSplit and convert the NA values to 0

library(data.table)
library(splitstackshape)
lst <- strsplit(stats, "(?<=[0-9])(?=[a-z])|\\s+", perl = TRUE)
lst1 <- lapply(lst, function(x) 
         as.data.frame.list(setNames(x[c(TRUE, FALSE)], x[c(FALSE, TRUE)])))
res <- cSplit(rbindlist(lst1, fill = TRUE), c('fg', 'ft'), '-')
for(nm in seq_along(res)){
    set(res, i = NULL, j = nm, value = as.numeric(as.character(res[[nm]])))
    set(res, i = which(is.na(res[[nm]])), j = nm, value = 0)
}

res
#    pt rb as st to trey bl fg_1 fg_2 ft_1 ft_2
#1: 40  2  1  2  2    4  0   11   20   14   14
#2:  7  5  1  2  3    0  1    3    5    1    4
#3:  0  1  1  0  0    0  0    0    2    0    0

Upvotes: 1

Related Questions