4

I have 2 dataframes in my jupyter-notebook. In the first one I have a series with lists of words and in the second I have a series with words. I need to iterate over each list of words from the first dataframe, to check if the word is on the other dataframe, and make the cumulative sum.

    a = pd.DataFrame({'text': [['one', 'two', 'three'], ['two', 'four'], ['five', 'one']], 'pos': [21,22,23], 'neg': [0,0,0]})

        text               pos   neg
    0   [one, two, three]   21    0
    1   [two, four]         22    0
    2   [five, one]         23    0

    b = pd.DataFrame({'word': ['two', 'three', 'four'], 'pos': [100,200,300], 'neg': [1,2,3]})

        word     pos    neg
    0   two      100     1   
    1   three    200     2
    2   four     300     3

So i would like to obtain this:

        text               pos   neg
    0   [one, two, three]  321    3
    1   [two, four]        422    4
    2   [five, one]         23    0

Thank you.

presidentpt
  • 51
  • 4

3 Answers3

3

Here is one possible solution using Series.apply and Series.map:

def maplist(lst):
    s = pd.Series(lst)
    return pd.Series(
        {'pos': s.map(mappings['pos']).sum(), 'neg': s.map(mappings['neg']).sum()})

mappings = b.set_index('word')
a[['pos', 'neg']] += a['text'].apply(maplist)

# print(a)
                    text    pos  neg

0      [one, two, three]  321.0  3.0
1            [two, four]  422.0  4.0
2            [five, one]   23.0  0.0
Shubham Sharma
  • 68,127
  • 6
  • 24
  • 53
1

You could do it this way:

c = a.explode('text').reset_index()\
     .merge(b, how='left', left_on='text', right_on='word', suffixes=('','_y'))
c = c.groupby('index').agg({'text':list,
                            'pos':'first',
                            'pos_y':'sum',
                            'neg':'first',
                            'neg_y':'sum'})
c['pos'] = c['pos'] + c['pos_y']
c['neg'] = c['neg'] + c['neg_y']
c = c.drop(['pos_y', 'neg_y'], axis=1)
c

Output:

                   text    pos  neg
index                               
0      [one, two, three]  321.0  4.0
1            [two, four]  422.0  6.0
2            [five, one]   23.0  3.0
Scott Boston
  • 147,308
  • 15
  • 139
  • 187
0
pos_lst = [] 
neg_lst = [] 
for index, rows in a.iterrows():
    pos = rows['pos']
    neg = rows['neg']
    for lst in rows['text']:
        pos = pos + b[b['word'] == lst].pos.sum()
        neg = neg + b[b['word'] == lst].neg.sum()
    pos_lst.append(pos)
    neg_lst.append(neg)
temp = pd.DataFrame() 
temp['text'] = a['text'] 
temp['pos_lst'] = pos_lst
temp['neg_lst'] = neg_lst 
temp`
Deepak
  • 667
  • 8
  • 16