CODEWITHSUNDEEP
pythonpandasdataframe
Pabasara Ranathunga
Pabasara Ranathunga

Reputation: 170

python pandas count the number of occurances inside lists in a column

Say I have a dataframe like 

    A     B     
0  [1,2]  Q     

1  [1,3]  Q     

2  [4,2]  Q

I want the count of each element inside the lists in the column A

So the result I want is

    Number  Count
0     1       2

1     2       2

2     3       1

3     4       1

I am not sure how to do it. Thank you in advance

Upvotes: 1

Views: 67

Answers (3)

jpp
jpp

Reputation: 164633

You can use collections.Counter with itertools.chain:

from itertools import chain
from collections import Counter

counts = Counter(chain.from_iterable(df['A']))

res = pd.DataFrame.from_dict(counts, orient='index').reset_index()
res.columns =['Number', 'Count']

print(res)

   Number  Count
0       1      2
1       2      2
2       3      1
3       4      1

Upvotes: 2

Sociopath
Sociopath

Reputation: 13401

You need:

from collections import Counter
sl = []
_ = [sl.extend(j) for i,j in df['A'].items()]
x = Counter(sl)

new_df = pd.DataFrame({'Number': list(x.keys()), 'Count': list(x.values())})
print(new_df)

Output

    Number  Count
0   1       2
1   2       2
2   3       1
3   4       1

Upvotes: 2

Bharath M Shetty
Bharath M Shetty

Reputation: 30605

Convert the series of list to a dataframe, stack the columns and use value_counts on that series i.e 

count = pd.DataFrame(df['A'].values.tolist()).stack().value_counts()

pd.DataFrame({'Count':count.values,'Number':count.index})

  Count  Number
0      2       2
1      2       1
2      1       4
3      1       3

# demo dataframe : df = pd.DataFrame({'A': [[1,2], [1,3], [4,2]]})

Upvotes: 3

Related Questions