CODEWITHSUNDEEP
rstringdataframematchgrepl
user9463814
user9463814

Reputation: 199

Is there a way to exactly match a string in one column with couple of strings in another column in R?

I would like to match a string in one column in R with strings separated by a "," comma in another column

I had two data frames in R:

General_df
Main_cat   gen_cat
Fruits     apple
Fruits     mango
Fruits     strawberry
Vegetable  potato
Vegetable  lettuce
Vegetable  onion
Liquids    water
Liquids    milk
Liquids    juice
Tech       app
Object     straw


My_dataframe

Days      cat
Day 1     apple, potato, milk
Day 2     onion, water
Day 3     strawberry, potato
Day 4     straw, mango

I wanted to get the Main_cat for "My_dataframe" so I managed to get this:

Days      cat                    Match_string Main_cat

Day 1     apple, potato, milk    apple        Fruits
Day 1     apple, potato, milk    potato       Vegetable
Day 1     apple, potato, milk    app          Tech
Day 1     apple, potato, milk    milk         Liquids

It matches the substring "app"as well, and I have several such substring matches for multiple rows in my dataframe

however,I just want it to exactly match the entire string in the "cat" column that is separated by a ","

Days      cat                    Match_string Main_cat
Day 1     apple, potato, milk    apple        Fruits
Day 1     apple, potato, milk    potato       Vegetable
Day 1     apple, potato, milk    milk         Liquids

Is there a way to find an exact matching string in this scenerio? Thanks!

General_df <- read.table(text='
Main_cat   gen_cat
Fruits     apple
Fruits     mango
Fruits     strawberry
Vegetable  potato
Vegetable  lettuce
Vegetable  onion
Liquids    water
Liquids    milk
Liquids    juice
Tech       app
Object     straw', header=TRUE, stringsAsFactors = FALSE)


My_dataframe <- read.table(text='
Days;    cat
Day 1;    apple, potato, milk
Day 2;    onion, water
Day 3;    strawberry, potato
Day 4 ;   straw, mango', sep=';', header=TRUE, stringsAsFactors = FALSE)

My_dataframe[] <- lapply(My_dataframe, trimws)

Upvotes: 1

Views: 111

Answers (1)

Tyler Rinker
Tyler Rinker

Reputation: 109844

I think this is what you're after:

library(dplyr); library(tidyr)

My_dataframe %>%
    ## Split cat variable up into individual strings as a list column
    mutate(Match_string = strsplit(cat, ',\\s+')) %>%
    ## unnest the list into a long/tall data frame
    unnest(Match_string) %>%
    ## Join the lookup/key onto the tall/long data on the split column
    left_join(General_df, by = c('Match_string' = 'gen_cat'))


##   Days  cat                 Match_string Main_cat 
##   <chr> <chr>               <chr>        <chr>    
## 1 Day 1 apple, potato, milk apple        Fruits   
## 2 Day 1 apple, potato, milk potato       Vegetable
## 3 Day 1 apple, potato, milk milk         Liquids  
## 4 Day 2 onion, water        onion        Vegetable
## 5 Day 2 onion, water        water        Liquids  
## 6 Day 3 strawberry, potato  strawberry   Fruits   
## 7 Day 3 strawberry, potato  potato       Vegetable
## 8 Day 4 straw, mango        straw        Object   
## 9 Day 4 straw, mango        mango        Fruits

And a base R approach to make sure I'm not getting too dependent:

Match_string <- strsplit(My_dataframe$cat, ',\\s+')
data.frame(
    My_dataframe[rep(seq_len(nrow(My_dataframe)), lengths(Match_string)),],
    Match_string = unlist(Match_string), 
    Main_cat = General_df$Main_cat[match(unlist(Match_string), General_df$gen_cat)],
    stringsAsFactors = FALSE,
    row.names = NULL
)

##    Days                 cat Match_string  Main_cat
## 1 Day 1 apple, potato, milk        apple    Fruits
## 2 Day 1 apple, potato, milk       potato Vegetable
## 3 Day 1 apple, potato, milk         milk   Liquids
## 4 Day 2        onion, water        onion Vegetable
## 5 Day 2        onion, water        water   Liquids
## 6 Day 3  strawberry, potato   strawberry    Fruits
## 7 Day 3  strawberry, potato       potato Vegetable
## 8 Day 4        straw, mango        straw    Object
## 9 Day 4        straw, mango        mango    Fruits

Or data.table if speed and memory is your thing:

library(data.table)
merge(
    data.table(My_dataframe)[, Match_string := strsplit(cat, ',\\s+')][, 
        .(Match_string =unlist(Match_string)), by = c('Days', 'cat')], 
    General_df, by.x = 'Match_string', by.y = 'gen_cat',
    all.x = TRUE
)[order(Days), .(Days, cat, Match_string, Main_cat)]

##     Days                 cat Match_string  Main_cat
## 1: Day 1 apple, potato, milk        apple    Fruits
## 2: Day 1 apple, potato, milk         milk   Liquids
## 3: Day 1 apple, potato, milk       potato Vegetable
## 4: Day 2        onion, water        onion Vegetable
## 5: Day 2        onion, water        water   Liquids
## 6: Day 3  strawberry, potato       potato Vegetable
## 7: Day 3  strawberry, potato   strawberry    Fruits
## 8: Day 4        straw, mango        mango    Fruits
## 9: Day 4        straw, mango        straw    Object

Upvotes: 2

Related Questions