Struggling in pandas pivot tables and flattening them

Question

I'm trying to recreate in python the following pivot table.

In Excel, everything is working fine. 48 rows as expected x 68 columns. The values are the "count" for the items with those specific row/column.

In pandas, with the same data, I have a pivot table of 48 x 962 columns. Moreover, I've tried multiple ways to get a flattened dataframe (no multiindex), without success.

raw csv here

pivot = pd.pivot_table(dataframe, 
                         index = 'customer_IDprovince', 
                         columns = 'category', 
                         aggfunc = len, 
                         fill_value = 0)

Moreover, I tried to flatten it using pivot to record, get level values, rename axis and reset index. No way to make it flat. Could you help me, thanks. Vincenzo

Answer 1

Use aggfunc="size" instead of len:

pivot = pd.pivot_table(
    df,
    index="customer_IDprovince",
    columns="category",
    aggfunc="size",
    fill_value=0,
)

print(pivot.shape)

Prints:

(48, 68)