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

Question

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:

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.

kindly post data and not pics. you can achieve this by running df.head().to_dict(), and paste the outcome in ur question. same goes for ur expected output. You can view this [link](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) for more/better ways to present ur question. It would also help if u state ur attempts and where u got stuck. — sammywemmy, Mar 25 '20 at 13:08
If you gave *copy/pasteable* samples instead of images, maybe we could reproduce and propose solutions... — Serge Ballesta, Mar 25 '20 at 13:09
@sammywemmy thank you for the useful guidance, I am adding these edits now — Darcey BM, Mar 25 '20 at 13:19
great. could you also post ur expected output based on the data u just shared. — sammywemmy, Mar 25 '20 at 13:30
Hi @sammywemmy, I'm not sure how to post that in Python as I don't now how to do the required merge. Will the excel mockup above work for guidance? If a Python version is needed I can try to create it manually — Darcey BM, Mar 25 '20 at 13:53
the data u shared is different from the mockup. the data you shared, I could only find 'new deal' string in one of the rows, which seems kinda odd. possibly have a look at the data again — sammywemmy, Mar 25 '20 at 13:56
@sammywemmy I've changed the expected output table to reflect the input data — Darcey BM, Mar 25 '20 at 14:24

score 2 · Accepted Answer · answered Mar 25 '20 at 14:53

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)

score 1 · Answer 2 · answered Mar 25 '20 at 16:13

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

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

2 Answers2

Linked