CODEWITHSUNDEEP
pythonpandaspivot-table
user17357338
user17357338

Reputation:

Struggling in pandas pivot tables and flattening them

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

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)

python result

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

Upvotes: 1

Views: 335

Answers (1)

Andrej Kesely
Andrej Kesely

Reputation: 195438

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)

Upvotes: 1

Related Questions