Python - Assign a value from one pandas df to another based on a string appearing in text

user_7202229 0
Viewed 177

Here is an example of my data:

import pandas as pd

data = {'Text':['This is an example,', 
                'Another sentence is here.', 
                'Lets have fun.', 
                'this happened weeks ago.',
                'I am not sure what to put here.',
                'Another fake sentence.'], 
        'Score':[20, 21, 19, 18, 16, 12]} 
  
# Create DataFrame 
df = pd.DataFrame(data) 


data_words = {'words':['is', 
                'fun', 
                'happened', 
                'example'], 
        'frequency':[127, 112, 1234, 32]} 
  
# Create DataFrame 
df2 = pd.DataFrame(data_words) 



#Final Result:

data_result = {'words':['is', 
                'fun', 
                'happened', 
                'example'], 
        'frequency':[127, 112, 1234, 32],
            'Text': ['This is an example,',
                    'Lets have fun.',
                    'this happened weeks ago.',
                    'This is an example,']} 

df_final = pd.DataFrame(data_result)

I am trying to match the df['text'] with the df2['words'] based on whether the word appears in the text. I just need one text per word, and ideally it would be based on "Score" but it's not completely necessary.

So, the final df would have columns: "Text", "Score", "words", and "frequency"

pandas python
1 Answers

Simple list comprehension between the two dataframes and take the first occurence with [0]

df2['Text'] = df2['words'].apply(lambda x: [y for y in df['Text'] if x in y][0])

output:

    words       frequency   Text
0   is          127         This is an example,
1   fun         112         Lets have fun.
2   happened    1234        this happened weeks ago.
3   example     32          This is an example,

Explaining the list comprehension, I am returning the value "y" while searching for "x" in "y" where x is each row for words and y is each row for text. This returns a list of all matches per row. Some rows had multiple values in the list, since multiple matches, so per your expected output I added a [0] to the end in order to take the first value that was returned in each list for the list comprehension that was applied row-by-row with lambda x. Otherwise, without the [0], a list of all matches would be returned.

user_63667700
Related