Recognizing Patterns with Regex in R-CodePudding

I am using the R programming language.

I have a dataset that looks like this:

id = 1:5
col1 = c("john", "henry", "adam", "jenna", "peter")
col2 = c("river B8C 9L4", "Field U9H 5E2 PP", "NA", "ocean A1B 5H1 dd", "dave")
col3 = c("matt", "steve", "forest K0Y 1U9 hu2", "NA", "NA")
col4 = c("Phone: 111 1111 111", "Phone: 222 2222", "Phone: 333 333 1113", "Phone: 444 111 1153", "Phone: 111 111 1121")
my_data = data.frame(id, col1, col2, col3, col4)

id  col1             col2               col3                col4
1  1  john    river B8C 9L4               matt Phone: 111 1111 111
2  2 henry Field U9H 5E2 PP              steve     Phone: 222 2222
3  3  adam               NA forest K0Y 1U9 hu2 Phone: 333 333 1113
4  4 jenna ocean A1B 5H1 dd                 NA Phone: 444 111 1153
5  5 peter             dave                 NA Phone: 111 111 1121

For this dataset, I would like to:

Always keep the id column and the first column
And keep the first column with the following pattern: LETTER NUMBER LETTER NUMBER LETTER NUMBER
Always keep the column with the phone number

This would looks something like this:

  id  col1            new_col                col4
1  1  john      river B8C 9L4 Phone: 111 1111 111
2  2 henry  Field U9H 5E2 PP      Phone: 222 2222
3  3  adam forest K0Y 1U9 hu2 Phone: 333 333 1113
4  4 jenna      ocean A1B 5H1 Phone: 444 111 1153

I found this REGEX code online that can recognize the desired pattern:

> apply(my_data, 1, function(x) gsub('(([A-Z] ?[0-9]){3})|.', '\\1', toString(x)))
[1] "B8C 9L4" "U9H 5E2" "K0Y 1U9" "A1B 5H1" ""

But can someone please show me how I can use this REGEX code in R to accomplish my desired result?

Thanks!

CodePudding user response：

library(tidyverse)

my_data%>%
  pivot_longer(-c(id, col1))%>%
  filter(str_detect(value, "([A-Z] ?[0-9]){3}|Phone:[0-9 ] "))%>%
  mutate(name = ifelse(str_detect(value,"Phone"),name, "new_col"))%>%
  pivot_wider(values_fn = 'first')


# A tibble: 5 × 4
     id col1  new_col            col4               
  <int> <chr> <chr>              <chr>              
1     1 john  river B8C 9L4      Phone: 111 1111 111
2     2 henry Field U9H 5E2 PP   Phone: 222 2222    
3     3 adam  forest K0Y 1U9 hu2 Phone: 333 333 1113
4     4 jenna ocean A1B 5H1 dd   Phone: 444 111 1153
5     5 peter NA                 Phone: 111 111 1121