Count number of rows that a value stored in a list occurs in

user_11037629 0
Viewed 418

There is a DataFrame df that holds data in list of strings:

>> df
     words
0  [a,b,c]
1      [a]
2  [x,c,c]
3      [a]
...

I want to count the number of rows that each value in words occurs in. For example:

a: 3
b: 1
c: 2
x: 1

I get a list of all unique words in the DataFrame using:

>> from collections import OrderedDict #using OrderedDict to keep word order
>> l = []
>> df.words.apply(lambda x: l.append(x)) #add list of words to a list
>> l = list(OrderedDict.fromkeys([j for i in l for j in i])) #merge list of lists and remove duplicates
>> print(l)
[a,b,c,x]

From here I go through the list l and checking each row of df if the word exists, and then sum the Bool value for each word.

data = []
for w in l:
  tmp = []
  df.words.apply(lambda x: tmp.append(w in x))
  data.append(sum(tmp))

I can then create a dictionary of words and their count. This is, however, very inefficient, as it takes a long time (70,000+ words and 50,000+ rows). Is there a faster way of doing this?

pandas dataframe python-3.x python
2 Answers
user_124164530

Convert each list to a set, then back to list. Combine them using itertools and then run a collections.Counter on it to get the dictionary.

#if data is your list of lists or dataframe
import itertools
import collections

data = [list(set(i)) for i in data]
newData = list(itertools.chain.from_iterable(data))
#chain makes an iterator that returns elements from the first iterable until it is 
#exhausted, then proceeds to the next iterable, until all of the iterables are 
#exhausted.
dictVal = collections.Counter(newData)
user_153018660
Related