CODEWITHSUNDEEP
pythonpandasdataframeleft-join
Darcey BM
Darcey BM

Reputation: 301

How to merge pandas on string contains, with multiple matches?

I have two Pandas dataframes, one contains keyword pairs and the other contains titles. I want to left join the titles dataframe to the keyword pairs data frame, if the title contains the keyword pair.

Titles may contain multiple keyword pairs, and multiple keyword pairs can be in each title. Is there a way to do this?

Example of keyword pair df:

import pandas as pd
pd.DataFrame({'keywords_combined': {0: 'gmo pesticide', 1: 'oil gas', 2: 'renewable energy', 3: 'eco friendly', 4: 'clean energy', 5: 'green new', 6: 'new deal', 7: 'climate change'}, 'keyword_difficulty_as_number': {0: 1, 1: 3, 2: 2, 3: 1, 4: 2, 5: 2, 6: 2, 7: 2}})

Example of titles df:

import pandas as pd
pd.DataFrame({'title': {0: 'democrat alexandria ocasio cortez provides an eco friendly green new deal', 1: ' the social with the environment has to go hand in hand for effective climate change dechel mckillian founder of galerie la', 2: 'making sustainable fashion more effective for climate change', 3: 'organic clothing the needs wants of consumers survey on sustainable fashion', 4: 'renewable energy capacity set for 50 growth over next few years iea says eco planet news', 5: 'energy transition needs staged approach to aemo clean energy eco planet news', 6: 'the short list of climate change actions that will work and more on the green new deal', 7: 'the top 5 tools for sustainable fashion shopping this fall', 8: 'article in danish about maersk narrowing down their choice of future shipping fuel for clean energy to three choices alcohols biogas and ammonia', 9: 'rome summit takes bold step toward agroecology'}, 'votes': {0: 8, 1: 12, 2: 14, 3: 1, 4: 28, 5: 5, 6: 24, 7: 0, 8: 3, 9: 15}})

Desired outcome:

enter image description here

I initially tried to use df.merge, changing the second dataframe's "title" column name to "keywords_combined" temporarily, however "on" does not seem to work with something like str.contains:

df = df.merge(df2, on='keywords_combined', how='left')

Any help would be really appreciated, thank you.

Upvotes: 2

Views: 2663

Answers (2)

Serge Ballesta
Serge Ballesta

Reputation: 149075

I would first built a cross tab to identify which combined keyword exists in which title:

#prepare a DataFrame with same index as titles
tmp = pd.DataFrame(index=titles.index)

# build a cross-tab for keywords contained in titles
for i,comb in enumerate(keywords.keywords_combined):
    tmp[i] = titles.title.str.contains(comb)

# give names to axes and stack the crosstab only keeping successfull matches
tmp = tmp.rename_axis('titles').rename_axis('keyword pair', axis=1).stack()
tmp = tmp[tmp]

# align the original dataframes on the matches
resul1 = tmp.align(keywords, 'inner', axis=0, level=1)[1]
resul2 = tmp.align(titles, 'inner', axis=0, level=0)[1]

# concat horizontaly and ensure all keywords are present
resul = keywords.merge(pd.concat([resul1, resul2], axis=1).reset_index(
    drop=True), how='left', on=keywords.columns.tolist())

With the provided sample, it gives:

   keywords_combined  keyword_difficulty_as_number                                              title  votes
0      gmo pesticide                             1                                                NaN    NaN
1            oil gas                             3                                                NaN    NaN
2   renewable energy                             2  renewable energy capacity set for 50 growth ov...   28.0
3       eco friendly                             1  democrat alexandria ocasio cortez provides an ...    8.0
4       clean energy                             2  energy transition needs staged approach to aem...    5.0
5       clean energy                             2  article in danish about maersk narrowing down ...    3.0
6          green new                             2  democrat alexandria ocasio cortez provides an ...    8.0
7          green new                             2  the short list of climate change actions that ...   24.0
8           new deal                             2  democrat alexandria ocasio cortez provides an ...    8.0
9           new deal                             2  the short list of climate change actions that ...   24.0
10    climate change                             2   the social with the environment has to go han...   12.0
11    climate change                             2  making sustainable fashion more effective for ...   14.0
12    climate change                             2  the short list of climate change actions that ...   24.0

Upvotes: 1

sammywemmy
sammywemmy

Reputation: 28709

This is one solution :

#combine words list into one string, separated by |
combo = '|'.join(keyword.keywords_combined.tolist())

#extract all words from keywords_combined found in titles' title column
common = (titles.title
          .str.extractall(fr'({combo})')
          .reset_index()
          .drop('match',axis=1)
          .set_axis(['index','keywords_combined'],axis='columns'))

#hook back our result to keyword dataframe
keyword = keyword.merge(common,on='keywords_combined',how='left')

#finally, merge with titles 
keyword.join(titles,on='index').drop('index',axis=1)

Upvotes: 2

Related Questions