How to combine multiple lists of tuples from dataframe into one dictionary?

Question

I've looked around and couldn't really place together the pieces of information in different partial solutions I found, so here's the problem:

While analyzing Amazon reviews, I organized the data into a dataframe and created a column with the count of each word used in each review. So I have a column where each row contains a list of tuples.

I'm looking for an effective way (my dataset contains millions of reviews) to merge all those lists of tuples into a single dictionary. Ideally, this dictionary would already contain the weight of each word (which is the number of votes of their respective review), though I can figure that out later, if it's too much to ask.

Here's an example:

df['words'] = [('thank', 2),('you',2),('this',5)],
              [('interesting',1),('this',3)],
              [('thank,3),('me',2),('later',2)],
              [('me',2),('interesting',1)],
              [('thank',2),('you',1),('again',1)]
df['votes'] = 10
               5
               2
               1
               1

Desired output (or as nested dict) - the 1st number is the sum of the frequency, present in the tuples, while the 2nd is the sum of the weight, located in the column 'votes':

top_words = {'this':(8,15),'thank':(7,13),'me':(4,3),'you':(3,11),'interesting':(2,6),'later':(2,2),'again':(1,1)}

I've tried dict(zip(*df[words]) and some other similar methods, but always getting errors (the added weighted info would be awesome but is not strictly necessary yet). I have the feeling the answer is fairly simple, but it's escaping me.

Suggestions?

Answer 1

You can use reduce function and numpy for this.

df = {}
df['words'] = [[('thank', 2),('you',2),('this',5)],
              [('interesting',1),('this',3)],
              [('thank',3),('me',2),('later',2)],
              [('me',2),('interesting',1)],
              [('thank',2),('you',1),('again',1)]]
df['votes'] = [10,5,2,1,1]

from functools import reduce
import numpy as np

data = dict(zip(df['votes'], df['words']))
'''
{
 1: [('thank', 2), ('you', 1), ('again', 1)],
 2: [('thank', 3), ('me', 2), ('later', 2)],
 5: [('interesting', 1), ('this', 3)],
 10: [('thank', 2), ('you', 2), ('this', 5)]
}
'''

def add(a, x, data):
  for word in data[x]:
    if word[0] not in list(a.keys()):
      a[word[0]] = (0, 0)
    a[word[0]] = tuple(np.add(a[word[0]], (word[1], x)))
  return a

output = reduce(lambda a, x: add(a, x, data), data, {})

'''
{
 'again': (1, 1),
 'interesting': (1, 5),
 'later': (2, 2),
 'me': (2, 2),
 'thank': (7, 13),
 'this': (8, 15),
 'you': (3, 11)
}
'''

I used dict(zip(df['votes'], df['words'])) because reduce function needs input to be the same type as output.

Answer 2

Try it:

import numpy as np

top_words = {}
for ind, row in df.iterrows():
    for word in row["words"]:
        top_words[word[0]] = (sum(j[1] for i in df["words"]  for j in i if j[0] == word[0]), 
                              sum(i["votes"] for ind, i in df.iterrows() if word[0] in np.array(i["words"])))