CODEWITHSUNDEEP
regexr
amo
amo

Reputation: 3200

create new variable based on a regular expression

My question involves how to create a new variable on a data frame in R based on the result of a regular expression. Below is a minimal example of the data:

df <- data.frame(model=c("Legacy 2.0  BG5 B4 AUTO","Legacy 2.0 BH5 AT","Legacy 2.0i CVT Non Leather","Legacy 2.0i CVT","Legacy 2.0 BL5 AUTO B4",
                 "Legacy 2.0 BP5 AUTO","Legacy 2.0 BM5 AUTO CVT"), CRSP=c(3450000,3365000,4950000,5250000,4787526,3550000,5235000))

df
                        model    CRSP
1     Legacy 2.0  BG5 B4 AUTO 3450000
2           Legacy 2.0 BH5 AT 3365000
3 Legacy 2.0i CVT Non Leather 4950000
4             Legacy 2.0i CVT 5250000
5      Legacy 2.0 BL5 AUTO B4 4787526
6         Legacy 2.0 BP5 AUTO 3550000
7     Legacy 2.0 BM5 AUTO CVT 5235000

I would like to create a new variable 'chassis' whose value is the third element of the corresponding 'model' variable string, thus ending up with:

df
                        model    CRSP chassis
1     Legacy 2.0  BG5 B4 AUTO 3450000     BG5
2           Legacy 2.0 BH5 AT 3365000     BH5
3 Legacy 2.0i CVT Non Leather 4950000     CVT
4             Legacy 2.0i CVT 5250000     CVT
5      Legacy 2.0 BL5 AUTO B4 4787526     BL5
6         Legacy 2.0 BP5 AUTO 3550000     BP5
7     Legacy 2.0 BM5 AUTO CVT 5235000     BM5

I need to find a way of extracting the appropriate elements in each row and place them in the new variable. Any assistance would be greatly appreciated.

Upvotes: 16

Views: 1307

Answers (7)

moodymudskipper
moodymudskipper

Reputation: 47320

Using unglue we could do : 

# install.packages("unglue")
library(unglue)

unglue_unnest(df, model, "{=.*?} {=.*?} {chassis=[^ ]+}{=.*?}", remove = FALSE)
#>                         model    CRSP chassis
#> 1     Legacy 2.0  BG5 B4 AUTO 3450000     BG5
#> 2           Legacy 2.0 BH5 AT 3365000     BH5
#> 3 Legacy 2.0i CVT Non Leather 4950000     CVT
#> 4             Legacy 2.0i CVT 5250000     CVT
#> 5      Legacy 2.0 BL5 AUTO B4 4787526     BL5
#> 6         Legacy 2.0 BP5 AUTO 3550000     BP5
#> 7     Legacy 2.0 BM5 AUTO CVT 5235000     BM5

Upvotes: 0

Sam Firke
Sam Firke

Reputation: 23014

You can split on the space character using cSplit from the splitstackshape package:

library(splitstackshape)
df$chassis <- cSplit(df, "model", sep = " ", "wide")$model_3

This avoids the need for a regex or an apply function.

Upvotes: 2

BrodieG
BrodieG

Reputation: 52637

This can easily be done in base R:

transform(df, chassis=sub("^(\\S+\\s+){2}(\\S+).*", "\\2", model))

produces:

                        model    CRSP chassis
1     Legacy 2.0  BG5 B4 AUTO 3450000     BG5
2           Legacy 2.0 BH5 AT 3365000     BH5
3 Legacy 2.0i CVT Non Leather 4950000     CVT
4             Legacy 2.0i CVT 5250000     CVT
5      Legacy 2.0 BL5 AUTO B4 4787526     BL5
6         Legacy 2.0 BP5 AUTO 3550000     BP5
7     Legacy 2.0 BM5 AUTO CVT 5235000     BM5

Upvotes: 3

Tyler Rinker
Tyler Rinker

Reputation: 109874

I'm a big fan of tidyr for this sort of task and extracting all the pieces into separate columns:

if (!require("pacman")) install.packages("pacman")
pacman::p_load(dplyr, tidyr)

regx <- "(^[A-Za-z]+\\s+[0-9.a-z]+)\\s+([A-Z0-9]+)\\s*(.*)"

df %>%
    extract(model, c("a", "chassis", "b"), regx, remove=FALSE)

##                         model           a chassis           b    CRSP
## 1     Legacy 2.0  BG5 B4 AUTO  Legacy 2.0     BG5     B4 AUTO 3450000
## 2           Legacy 2.0 BH5 AT  Legacy 2.0     BH5          AT 3365000
## 3 Legacy 2.0i CVT Non Leather Legacy 2.0i     CVT Non Leather 4950000
## 4             Legacy 2.0i CVT Legacy 2.0i     CVT             5250000
## 5      Legacy 2.0 BL5 AUTO B4  Legacy 2.0     BL5     AUTO B4 4787526
## 6         Legacy 2.0 BP5 AUTO  Legacy 2.0     BP5        AUTO 3550000
## 7     Legacy 2.0 BM5 AUTO CVT  Legacy 2.0     BM5    AUTO CVT 5235000

You could get a bit more generic with this regex:

regx <- "(^[^ ]+\\s+[^ ]+)\\s+([^ ]+)\\s*(.*)"

Also note you can use extract to get just the column you're after by dropping the grouping parenthesis on the first and last groups as follows:

regx <- "^[A-Za-z]+\\s+[0-9.a-z]+\\s+([A-Z0-9]+)\\s*.*"

df %>% 
    extract(model, "chassis", regx, remove=FALSE)

Upvotes: 6

akrun
akrun

Reputation: 887158

We could match the character till the numeric part including the i and space, replace it with '' using sub, and then extract the first word with word.

library(stringr)
 word(sub('^\\D*[0-9.i ]*', '', df$model),1)
#[1] "BG5" "BH5" "CVT" "CVT" "BL5" "BP5" "BM5"

Or match the spaces, replace with a single space and use word

 word(gsub(' +', ' ', df$model),3)
 #[1] "BG5" "BH5" "CVT" "CVT" "BL5" "BP5" "BM5"

NOTE: Not sure the extra space in the first element of 'model' is a typo. If the original dataset do not have more than one space between words, then word(df$model, 3) would work.

Upvotes: 4

David Arenburg
David Arenburg

Reputation: 92292

Here's a possible solution using stringi

library(stringi)
df$chassis <- stri_extract_all_words(df$model, simplify = TRUE)[, 3]
df
#                         model    CRSP chassis
# 1     Legacy 2.0  BG5 B4 AUTO 3450000     BG5
# 2           Legacy 2.0 BH5 AT 3365000     BH5
# 3 Legacy 2.0i CVT Non Leather 4950000     CVT
# 4             Legacy 2.0i CVT 5250000     CVT
# 5      Legacy 2.0 BL5 AUTO B4 4787526     BL5
# 6         Legacy 2.0 BP5 AUTO 3550000     BP5
# 7     Legacy 2.0 BM5 AUTO CVT 5235000     BM5

Or similarly

df$chassis <- sapply(stri_extract_all_words(df$model), `[`, 3)

Upvotes: 10

nico
nico

Reputation: 51640

An alternative solution using strsplit

# Split each of the models using space (the + accounts for multiple spaces)
# Note that model is a factor in your data frame, so it must be cast to char
model.split <- strsplit(as.character(df$model), " +")
# Now go through each element of the splitted list and get the 3rd word
df$chassis <- sapply(model.split, function(x){x[3]})

Upvotes: 5

Related Questions