find matches and scores between 2 csv files

user_15092553 0
Viewed 40
text                                                    number
0   very nice house, and great garden                       3
1   the book is very boring                                 4
2   it was very interesting final end                       5
3   I have no idea which    book do you prefer              4

I have 2 csv files: one text.csv and one words.csv

       word              score
0      boring           -1.0
1      very             -1.0
2      interesting       1.0
3      great             1.0
4      book              0.5

I want to count how many positive and negative words matches with text

e.g “the book is very boring” has 1 0.5 and 2 negative -1. My output should be then (positive matches and negative matches) [1,2] based on the score in words.csv matches for text.csv

I am new for pandas and do not know how to get it.

pandas csv python-3.x
1 Answers

Create a function that scores a sentence, and then apply it to the text column:

import pandas as pd

# import data
text, number = zip(
    ("very nice house, and great garden",                       3),
    ("the book is very boring",                                 4),
    ("it was very interesting final end",                       5),
    ("I have no idea which    book do you prefer",              4),
)
df = pd.DataFrame(dict(text=text, number=number))

word, score = zip(
    ("boring",           -1.0),
    ("very",             -1.0),
    ("interesting",       1.0),
    ("great",             1.0),
    ("book",              0.5),
)
df2 = pd.DataFrame(dict(word=word, score=score))

# convert score data frame to a dictionary for faster indexing
word2score = dict(zip(df2['word'], df2['score']))

def score_text(sentence):
    score = 0
    for word in sentence.split():
        token = word.strip(",.:;!?()'/") # you probably want to do a more professional tokenization here
        if token in word2score:
            score += word2score[token]
    return score

df['score'] = df['text'].apply(score_text)

print(df)

#                                          text  number  score
# 0           very nice house, and great garden       3    0.0
# 1                     the book is very boring       4   -1.5
# 2           it was very interesting final end       5    0.0
# 3  I have no idea which    book do you prefer       4    0.5

Edit:

If you want to count the number of positive and negative words, you have to make some minimal changes to the scoring function:

def score_text(sentence):
    score = [0, 0]
    for word in sentence.split():
        token = word.strip(",.:;!?()'/") # you probably want to do a more professional tokenization here
        if token in word2score:
            if word2score[token] > 0:
                score[0] += 1
            elif word2score[token] < 0:
                score[1] += 1
    return score

#                                          text  number   score
# 0           very nice house, and great garden       3  [1, 1]
# 1                     the book is very boring       4  [1, 2]
# 2           it was very interesting final end       5  [1, 1]
# 3  I have no idea which    book do you prefer       4  [1, 0]
user_29123490
Related