Struggling in pandas pivot tables and flattening them-CodePudding

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.

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

CodePudding user response：

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)