R: Match character vector with another character vector

user_7976838 0
Viewed 826

Can't wrap my mind around this task

Consider a data frame "usa" with 3 columns, "title", "city" and "state" (reproducible):

    title <- c("Events in Chicago, September", "California hotels", 
               "Los Angeles, August", "Restaurant in Chicago")
    city  <- c("","", "Los Angeles", "Chicago")
    state <- c("","", "California", "IL")

    usa   <-data.frame(title, city, state)

Resulting in this:

                             title        city      state
    1 Events in Chicago, September                       
    2            California hotels                       
    3          Los Angeles, August Los Angeles California
    4        Restaurant in Chicago     Chicago         IL

Now what I am trying to do is to fill the STATE variable for the first 2 observations, which are now missing.

TITLE variable contains a clue: either a city or a state is mentioned in each of the entries.

I need to do the following:

  1. Check if any word in "title" column matches any observation found in "city" and "state" columns;
  2. If any word in "title" matches any observation in "state", paste the same state for the given title's observation;
  3. If any word in "title" matches any observation in "city", paste the matched city's state in the "state" column of the title's row.

So what I want to get eventually is this:

                             title        city      state
    1 Events in Chicago, September                     IL   
    2            California hotels             California          
    3          Los Angeles, August Los Angeles California
    4        Restaurant in Chicago     Chicago         IL

In other words, in the second row the title contained a word "California", so a matching state was found from state vector. However, in the first line, the word "Chicago" was the key, and there was another entry in the data frame (row 4), which linked Chicago to "IL" state, so "IL" has to be pasted in the first row of "state" column.

Waiting for the community's ideas :) Thanks!

match dataframe r pattern-matching
1 Answers

I would recommend you use the stringr package; specifically, a function called str_extract.

If you have a complete list of cities, e.g. city <- c("Los Angeles", "Chicago"), then you can make it into regular expression using paste(city, collapse = '|'). That will give you: 'Los Angeles|Chicago'. With str_extract, you can extract that city (will extract the first one it sees, and an NA if none appear). Here's the complete code. Note: this only works if your dataframe is a data_frame (tibble), not a data.frame (not totally sure why, haven't looked into it)

library(tidyverse)
library(stringr)

title <- c("Events in Chicago, September", "California hotels", 
           "Los Angeles, August", "Restaurant in Chicago")
city  <- c("","", "Los Angeles", "Chicago")
state <- c("","", "California", "IL")

usa   <-data_frame(title, city, state) # notice this is a data_frame not data.frame

cities <- paste(c("Los Angeles", "Chicago"), collapse = '|')
states <- paste(c("California", "IL"), collapse = '|')

usa <- usa %>% 
  mutate(city = ifelse(city == '', str_extract(title, cities), city),
         state = ifelse(state == '', str_extract(title, states), state))

This results in:

# A tibble: 4 x 3
                         title        city      state
                         <chr>       <chr>      <chr>
1 Events in Chicago, September     Chicago       <NA>
2            California hotels        <NA> California
3          Los Angeles, August Los Angeles California
4        Restaurant in Chicago     Chicago         IL
user_80077380
Related