I'm trying to use stringr, specifically regex, to cut up "MA: Bristol County (25005)"

user_10421538 0
Viewed 62

I'm trying to take a variable column and cut it up into several columns. The values follow a basic pattern with the county name having a variety of lengths and formats.

State-county :
[1] "MA: Bristol County (25005)"
[2] "LA: St. Tammany Parish (22103)"
[3] "CA: Ventura County (06111)"    
[4] "CA: San Mateo County (06081)"

I need a state, county name, and county code column that I can add back into the data.frame. Been trying to figure out how to use str_extract to accomplish the task. Ideally, this is where I'd end up, but I'll take any help I can get.

  state:    county:            county code: 
[1] "MA"   Bristol County       25005
[2] "LA"   St. Tammany Parish   22103
[3] "CA"   Ventura County       06111    
[4] "CA:   San Mateo County     06081

I was able to use this code I found str_extract_all( "(?<=\\().+?(?=\\))") for the county code (thanks Nettle) and the closest I could get to the state abrev was 'str_extract_all( h,"..:") which is close but includes the ":" also tried: str_extract_all( "(?<=\\:")

Sorry if this isn't the best format, I tried to be really clear and in the style I've seen.

stringr r
3 Answers

Use str_match_all:

str_match_all(df$State_county, "([A-Z]+): ([^()]+) \\((\\d+)\\)")

as_tibble(df) %>%
 mutate(matches=str_match_all(State_county, "([A-Z]+): ([^()]+) \\((\\d+)\\)")) %>%
  unnest_wider(matches) %>%
   select(-2) %>%
    set_names("State_county", "State", "County", "ZIP")
# A tibble: 4 x 4
  State_county                   State County             ZIP  
  <fct>                          <chr> <chr>              <chr>
1 MA: Bristol County (25005)     MA    Bristol County     25005
2 LA: St. Tammany Parish (22103) LA    St. Tammany Parish 22103
3 CA: Ventura County (06111)     CA    Ventura County     06111
4 CA: San Mateo County (06081)   CA    San Mateo County   06081

### OR with str_match as we're only using a single pattern
## this saves us from the warning caused by unnest_wider
as_tibble(df)  %>%
 mutate(matches=str_match(State_county, "([A-Z]+): ([^()]+) \\((\\d+)\\)"), State=matches[,2], County=matches[,3], ZIP=matches[,4], matches=NULL)
# A tibble: 4 x 4
  State_county                   State County             ZIP  
  <fct>                          <chr> <chr>              <chr>
1 MA: Bristol County (25005)     MA    Bristol County     25005
2 LA: St. Tammany Parish (22103) LA    St. Tammany Parish 22103
3 CA: Ventura County (06111)     CA    Ventura County     06111
4 CA: San Mateo County (06081)   CA    San Mateo County   06081 
### Another way 
str_match(df$State_county, "([A-Z]+): ([^()]+) \\((\\d+)\\)") %>%
 as.data.frame %>% set_names("State_county", "State", "County", "County_code")
                    State_county State             County County_code
1     MA: Bristol County (25005)    MA     Bristol County       25005
2 LA: St. Tammany Parish (22103)    LA St. Tammany Parish       22103
3     CA: Ventura County (06111)    CA     Ventura County       06111
4   CA: San Mateo County (06081)    CA   San Mateo County       06081

Explanation:

str_match will basically return the captured groups (sub patterns written in non escaped parenthesis ([A-Z]+)) and the full string that matched the full pattern

  • [A-Z]+ : matches the state abrv.
  • [^()]+ : matches anything that's not an opening parenthesis. the county.
  • \\((\\d+)\\) : matches the open parenthesis \\( and closing one while it pulls the digits using grouping. the county code.
str_match(df$State_county, "([A-Z]+): ([^()]+) \\((\\d+)\\)")
     [,1]                             [,2] [,3]                 [,4]   
[1,] "MA: Bristol County (25005)"     "MA" "Bristol County"     "25005"
[2,] "LA: St. Tammany Parish (22103)" "LA" "St. Tammany Parish" "22103"
[3,] "CA: Ventura County (06111)"     "CA" "Ventura County"     "06111"
[4,] "CA: San Mateo County (06081)"   "CA" "San Mateo County"   "06081"
user_39808200

You can use tidyr's extract to get data into different columns specifying the regex that you want to use the divide the data.

tidyr::extract(df, col, 
               c('state', 'county', 'county_code'), 
               '(\\w+):\\s*(.*)\\((\\d+)\\)')

#  state             county  county_code
#1    MA     Bristol County        25005
#2    LA St. Tammany Parish        22103
#3    CA     Ventura County        06111
#4    CA   San Mateo County        06081

We use 3 capture groups to extract the data from col column.

data

df <- structure(list(col = c("MA: Bristol County (25005)", 
                "LA: St. Tammany Parish (22103)", 
"CA: Ventura County (06111)", "CA: San Mateo County (06081)")), 
 class = "data.frame", row.names = c(NA, -4L))
user_39629140

Here is an entirely base R approach, which uses strsplit to separate the three components:

output <- apply(df, 1, function(x) { strsplit(x, "(?:: | \\(|\\))")})
output <- unlist(output, recursive=FALSE)
names(output) <- c(1:length(output))
df <- as.data.frame(do.call(rbind, output))
names(df) <- c("state", "county", "zip")
df

  state             county   zip
1    MA     Bristol County 25005
2    LA St. Tammany Parish 22103

Data:

df <- data.frame(state=c("MA: Bristol County (25005)",
                         "LA: St. Tammany Parish (22103)"),
                 stringsAsFactors=FALSE)
user_18632290
Related