77

I want to count number of times each values is appearing in dataframe.

Here is my dataframe - df:

    status
1     N
2     N
3     C
4     N
5     S
6     N
7     N
8     S
9     N
10    N
11    N
12    S
13    N
14    C
15    N
16    N
17    N
18    N
19    S
20    N

I want to dictionary of counts:

ex. counts = {N: 14, C:2, S:4}

I have tried df['status']['N'] but it gives keyError and also df['status'].value_counts but no use.

jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
Kishan Mehta
  • 2,598
  • 5
  • 39
  • 61
  • I wonder why I can't directly use df['status'].value_counts()[my_key] and must use the to_dict() first? – Egret Sep 08 '22 at 09:11

5 Answers5

150

You can use value_counts and to_dict:

print df['status'].value_counts()
N    14
S     4
C     2
Name: status, dtype: int64

counts = df['status'].value_counts().to_dict()
print counts
{'S': 4, 'C': 2, 'N': 14}
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • 1
    Nice solution - using only pandas – Say OL Oct 19 '21 at 16:02
  • Nice solution. I can then treat it as a normal dictionary, and use it this way - counts[my_key]. However I wonder why I can't directly use df['status'].value_counts()[my_key] and must use the to_dict() first? – Egret Sep 08 '22 at 09:08
14

An alternative one liner using underdog Counter:

In [3]: from collections import Counter

In [4]: dict(Counter(df.status))
Out[4]: {'C': 2, 'N': 14, 'S': 4}
Colonel Beauvel
  • 30,423
  • 11
  • 47
  • 87
9

You can try this way.

df.stack().value_counts().to_dict()
su79eu7k
  • 7,031
  • 3
  • 34
  • 40
2

Can you convert df into a list?

If so:

a = ['a', 'a', 'a', 'b', 'b', 'c']
c = dict()
for i in set(a):
    c[i] = a.count(i)

Using a dict comprehension:

c = {i: a.count(i) for i in set(a)}
Chuck
  • 866
  • 6
  • 17
1

See my response in this thread for a Pandas DataFrame output,

count the frequency that a value occurs in a dataframe column

For dictionary output, you can modify as follows:

def column_list_dict(x):
    column_list_df = []
    for col_name in x.columns:        
        y = col_name, len(x[col_name].unique())
        column_list_df.append(y)
    return dict(column_list_df)
djoguns
  • 96
  • 4