Python - Pivot Table : Count the Occurrence of Value based on the Last Index

Cutie 0
Viewed 14

could you help me how I can count the occurence of the last index in pivot table?

Raw data enter image description here

Here is my code -- but the last column is returning me the Grand Total - based on the 1st index (A)

df.pivot_table(index=['A','B','C','D,'E','F','G'] 
                        , aggfunc={'G' : ['count',len]})

This should be the result (last column) once pivoted enter image description here

pivot count pivot-table indexing python
1 Answers

To get the expected count for column 'G', I included columns 'A'-'D' as indices and count of 'G' as follows:

pd.pivot_table(df, index=['A','B','C','D'],values='G',aggfunc={'G': ['count']})

Here is the resulting pivot table, where the expected count is shown:

pivot table

If however we include all columns as indices, the count of 'G' stays at 1.

Creating a similar pivot table with all columns in Excel shows an identical behaviour with only count of 1's:

pivot table in Excel

Heelara0
Related