Count unique elements from all the rows of a column

Question

I have a data frame df with a column called "groups". It looks like below -

groups
[u'CN=MyUsers,OU=Groups,DC=sample,DC=com',u'CN=Sample-Users,OU=Groups,DC=sample,DC=com']
[u'CN=MyUsers,OU=Groups,DC=sample,DC=com',u'CN=Sample-Users,OU=Groups,DC=sample,DC=com',u'CN=MoreUsers,OU=Groups,DC=sample,DC=com']

First row contains 2 groups and 2nd row contains 3 groups. I want to make a count of each unique group in the whole column. So my resulting data frame should say -

Group                                         Count
u'CN=MyUsers,OU=Groups,DC=sample,DC=com'       2
u'CN=Sample-Users,OU=Groups,DC=sample,DC=com'  2
u'CN=MoreUsers,OU=Groups,DC=sample,DC=com'     1

How will I be able to achieve this task. I am trying-

res=df.groups.apply(pd.Series).stack().value_counts()

But this doesn't give me expected result. This doesn't break into counts of individual groups.

Could you provide your source code? It would be easy for us to help you. — yuhow5566, Jul 28 '17 at 01:14
Umm I actually didn't use pandas frequently. If each column is a list, you can try to use 'count' for counting unique elements. — yuhow5566, Jul 28 '17 at 01:35
I think this is helpful for you to use pandas for counting. https://stackoverflow.com/questions/22391433/count-the-frequency-that-a-value-occurs-in-a-dataframe-column — yuhow5566, Jul 28 '17 at 02:30

yuhow5566 · Answer 1 · 2017-07-28T02:09:05.560

1

For each element in your column, you maybe can do something like this:

#if column1 = [u'CN=MyUsers,OU=Groups,DC=sample,DC=com',u'CN=Sample-Users,OU=Groups,DC=sample,DC=com']
#if column2 = [u'CN=MyUsers,OU=Groups,DC=sample,DC=com',u'CN=Sample-Users,OU=Groups,DC=sample,DC=com',u'CN=MoreUsers,OU=Groups,DC=sample,DC=com']

count_dict = dict()
for column in YOUR_DATA_SHEET:
    for element in column:
        if element in count_dict.keys():
            count_dict[element] += 1
        else:
            count_dict[element] = 1

So that you can have a dictionary which will record the element and its counts.

edited Jul 28 '17 at 02:09

answered Jul 28 '17 at 01:33

yuhow5566

555
4
20

This again gives me count of each cell as a whole and not individual groups within each cell throughout the column. The output for your code is same as given by mode code. – ComplexData Jul 28 '17 at 01:43
I made some modification. Try it again. – yuhow5566 Jul 28 '17 at 01:53
same output :( :( – ComplexData Jul 28 '17 at 01:55
I think my code is not clear. Please initial 'count_dict' in the very beginning. It will record counts throughout all your columns. – yuhow5566 Jul 28 '17 at 01:59
I discard the way of using 'count' function. An just simply count the column. – yuhow5566 Jul 28 '17 at 02:11

gold_cy · Answer 2 · 2017-07-28T03:15:57.187

This should work:

from itertools import chain

pd.DataFrame(map(lambda x: (x, 1), chain.from_iterable(df.groups.values))).groupby(0, as_index=False).sum().rename(columns={0:'Group', 1:'Count'})

                                        Group  Count
0     CN=MoreUsers,OU=Groups,DC=sample,DC=com      1
1       CN=MyUsers,OU=Groups,DC=sample,DC=com      2
2  CN=Sample-Users,OU=Groups,DC=sample,DC=com      2

Also like this:

pd.DataFrame(list(chain.from_iterable(df.groups.values)), columns=['Group']).Group.value_counts()

CN=Sample-Users,OU=Groups,DC=sample,DC=com    2
CN=MyUsers,OU=Groups,DC=sample,DC=com         2
CN=MoreUsers,OU=Groups,DC=sample,DC=com       1

Time tests:

%timeit pd.DataFrame(list(chain.from_iterable(df.groups.values)), columns=['Group']).Group.value_counts()

1000 loops, best of 3: 899 µs per loop

%timeit pd.DataFrame(list(chain.from_iterable(df.groups.values))).groupby(0, as_index=False).sum().rename(columns={0:'Group', 1:'Count'})

100 loops, best of 3: 5.5 ms per loop

Count unique elements from all the rows of a column

2 Answers2