Counting the frequency in a column for a multi-index pivot table using Python

user_13661145 0
Viewed 320

My data looks like this:

Company     Product_ID     Sl_Num
A           FGH123         5CG6501035
A           FGH123         5CG6501036
A           FGH123         5CG6501037
A           IJK124         5CG6501038
A           IJK124         5CG6501039
A           LMN124         5CG6501040
B           IJK124         5CG6501041
B           IJK124         5CG6501042
B           IJK124         5CG6501043
B           LMN124         5CG6501044
C           LMN124         5CG6501045
C           RST127         5CG6501046
C           RST127         5CG6501047
D           OPQ126         5CG6501048
E           RST127         5CG6501049

And I want it to look like following:

Company     Product_ID     Count
A           FGH123         3
A           IJK124         2
A           LMN124         1
B           IJK124         3
B           LMN124         1
C           LMN124         1
C           RST127         2
D           OPQ126         1
E           RST127         1

The code that I have is:

mypivot = pd.pivot_table(df_ProdImp, index=["Company", "Product_ID"], values = ["Product_ID"], aggfunc="count")

But its been giving me an error stating: ValueError: Grouper for 'Product_ID' not 1-dimensional

Please suggest how might I alter the code to achieve the desired result.

pandas pivot-table python-3.x python
3 Answers

You can write it as follows

mypivot = pd.DataFrame(df_ProdImp.groupby('Company')['Product_ID'].value_counts())
user_71861700

You can also try this:

df = df.groupby(by=['Company', 'Product_ID'], as_index=False).count().rename(columns={'Sl_Num': 'Count'})
print(df)

  Company Product_ID  Count
0       A     FGH123       3
1       A     IJK124       2
2       A     LMN124       1
3       B     IJK124       3
4       B     LMN124       1
5       C     LMN124       1
6       C     RST127       2
7       D     OPQ126       1
8       E     RST127       1
user_61683230

The code below should work. Its using groupby instead of pivot table.

df.groupby(['Company','Product_ID'])['Sl_Num'].count().reset_index()
user_138021150
Related