Python: Counting specific occurrences in dataframe by group

Question

Assuming I have a df:

df = pd.DataFrame({'id': [12, 35, 37, 67, 99, 78],
                  'product': ['banana', 'apple', 'banana', 'pear', 'banana', 'apple'],
                  'reordered': [1, 0, 0, 1, 1, 1]})


    id     product   reordered
0   12     banana    1
1   35     apple     0
2   37     banana    0
3   67     pear      1
4   99     banana    1
5   78     apple     1

I would like to count the number of occurrences of products in 'product' column, as well as values in 'reordered' columns grouped by products. Desired outcome:

       product   count   reordered_0   reordered_1
   0   banana    3       1             2
   1   apple     2       1             1
   2   pear      1       1             0

Please advice

Answer 1

Use crosstab with DataFrame.insert for column for first position:

df = pd.crosstab(df['product'], df.reordered).add_prefix('reordered_')
df.insert(0, 'count', df.sum(axis=1))
df = df.reset_index().rename_axis(None, axis=1)
print(df)
  product  count  reordered_0  reordered_1
0   apple      2            1            1
1  banana      3            1            2
2    pear      1            0            1

Answer 2

Let's try with crosstab:

(pd.crosstab(df['product'], df['reordered'])
   .add_prefix('reordered_')
   .assign(count=lambda x: x.sum(1))
   .reset_index()
)

Output:

reordered product  reordered_0  reordered_1  count
0           apple            1            1      2
1          banana            1            2      3
2            pear            0            1      1