Get count of values across columns-Pandas DataFrame

Question

I have a Pandas DataFrame like following:

               A              B              C
0   192.168.2.85   192.168.2.85  124.43.113.22
1  192.248.8.183  192.248.8.183   192.168.2.85
2  192.168.2.161            NaN  192.248.8.183
3   66.249.74.52            NaN  192.168.2.161
4            NaN            NaN   66.249.74.52

I want to get the count of a certain values across columns. So my expected output is something like:

IP          Count
192.168.2.85 3 #Since this value is there in all coulmns
192.248.8.183 3
192.168.2.161 2
66.249.74.52 2
124.43.113.22 1

I know how to this across rows, but doing this for columns is bit strange?Help me to solve this? Thanks.

@OP, would you consider accepting a more performant answer if it was given? — cs95, Jan 23 '19 at 10:39

score 37 · Accepted Answer · answered Jul 17 '13 at 05:58

37

stack it first and then use value_counts:

In [14]: df.stack().value_counts()
Out[14]: 
192.248.8.183    3   
192.168.2.85     3   
66.249.74.52     2   
192.168.2.161    2   
124.43.113.22    1   
dtype: int64

answered Jul 17 '13 at 05:58

waitingkuo

89,478
28
112
118

2

Only pandas Series can use value_counts(). By using df.stack(), you transformed his DataFrame into a Series. Your solution is correct, but knowing why it worked helped me out a lot. – user3155053 Mar 31 '16 at 16:33

score 2 · Answer 2 · edited Jul 06 '15 at 19:40

2

df['Counts'] = df[['col1','col2','col3']].groupby(['col1','col2','col3']).transform('count')

edited Jul 06 '15 at 19:40

o11c

15,265
4
50
75

answered Jul 06 '15 at 18:29

Vamshi G

377
3
5

Get count of values across columns-Pandas DataFrame

2 Answers2

Linked