How can I count how many items appear once, twice, thrice, ...?

user_4435175 0
Viewed 172

I have a dataframe df:

number 
123
123
123
456
456
789
111
111
111
...

I would like to know how many items appear X times.

Output should be something like this:

X times | count
3       | 2    (123 + 111 appear 3 times and are 2 numbers)
2       | 1    (456 appears 2 times but only 1 number)
1       | 1    (111 appear 1 time but only 1 number)
pandas count python
1 Answers

Use double Series.value_counts:

df1 = (df['number'].value_counts()
                   .value_counts()
                   .rename_axis('X times')
                   .reset_index(name='count'))
print (df1)
   X times  count
0        3      2
1        2      1
2        1      1

Alternative with double GroupBy.size:

s = df.groupby('number').size()
df1 = (s.groupby(s).size()
        .rename_axis('X times')
        .reset_index(name='count'))
print (df1)
   X times  count
0        1      1
1        2      1
2        3      2

Or combination, thank you @Ch3steR:

df1 = (df.groupby('number')
         .size()
         .value_counts()
         .rename_axis('X times')
         .reset_index(name='count'))
user_29010020
Related