How to group by counted value (python, pandas)

Question

Hello I have this Dataframe:

SerialNr	Amount
1	2
2	4
3	3
4	2

And I want a table that shows me how many serial numbers in the right column have the same numbers. Here for example it would look like this

Amount	Quantity of SerialNr
1	0
2	2
3	1
4	1

It should sum up the Serialnummer with the same amount

Tried to do it with groupby but did not work

Answer 1

From your expected ouput need Quantity of Amount, so use Series.value_counts with Series.reindex by range starting by 1:

s = df['Amount'].value_counts()

df = (s.reindex(range(1, s.index.max()+1), fill_value=0)
       .rename_axis('Amount')
       .reset_index(name='Quantity of Amount'))
print (df)
   Amount  Quantity of SerialNr
0       1                     0
1       2                     2
2       3                     1
3       4                     1

Or use Categorical:

cats = range(1, df.Amount.max()+1)
df = (pd.Categorical(df['Amount'], categories=cats)
        .value_counts()
        .rename_axis('Amount')
        .reset_index(name='Quantity of Amount'))

print (df)
  Amount  Quantity of Amount
0      1                   0
1      2                   2
2      3                   1
3      4                   1

Answer 2

Another possible solution:

(pd.concat([df.set_index('SerialNr'), 
            df['Amount'].value_counts().rename('Quantity of Amount')], axis=1)
 .fillna(0, downcast='infer').reset_index()
 .drop('Amount', axis=1).rename({'index': 'Amount'}, axis=1))

Output:

   Amount  count
0       1      0
1       2      2
2       3      1
3       4      1