pythonpandaspivot-tablemargins

Python Pandas Pivot Table issue with margins : got "Grouper for 'something' not 1-dimensional"

I'm working with a df and a simple pivot table my purpose is to add the margins. Everything works fine until I add the arg "margins=True".

here is my code :

df1=pd.DataFrame({'brand':['A','A','A','A','A','B','A','B','A','B','B','A','A'],
'type':['C','C','C','C','C','C','C','C','D','D','C','C','C'],
'Year':[2022,2022,2022,2022,2022,2022,2022,2022,2022,2022,2022,2022,2022],
'Month':[9,9,9,8,9,9,9,9,8,10,9,10,10]})
table_1 = pd.pivot_table(df1, values = 'type', index = ['brand','type'],
columns = ['Year','Month'], aggfunc = {'type':len}, fill_value = '0', margins=True)

print(table_1)

And I got the error : "ValueError: Grouper for 'type' not 1-dimensional"

Do you have any ideas to make it works ?

Thank you

I tried to change the parameters for aggfunc, I don't see what I'm missing here... Without the margins the outcome is fine. I just need the sum for each rows and columns which is what margins should do...

Solution

  • I think it doesn't like that you use both type as index and value. A workaround would be to use a dummy column:

    table_1 = pd.pivot_table(df1.assign(val=1), 
                         values='val', index=['brand','type'],
                         columns=['Year','Month'], aggfunc={'val':len},
                         fill_value=0, margins=True)

print(table_1)

    Output:

    Year       2022       All
Month         8  9 10    
brand type               
A     C       1  5  2   8
      D       1  0  0   1
B     C       0  3  0   3
      D       0  0  1   1
All           2  8  3  13

    NB. Use fill_value=0 to avoid having an object dtype.