CODEWITHSUNDEEP
pythonpandaspivotvisualization
galih
galih

Reputation: 511

Python pivoting table from dataframe

i have a problem to aggregating and pivoting table from pandas dataframe, here is my result table:

Customer dataframe:

    CustomerID  X_value         Y_value     Z_value
0   12346       1               1           1
1   12747       5               1           5
2   12748       5               5           5
3   12749       5               1           5
4   12820       5               1           4
5   12821       2               1           1

What i want is pivoting into:

enter image description here

how can i accomplished this?

thank you

Upvotes: 0

Views: 52

Answers (1)

Zero
Zero

Reputation: 76917

Using pivot_table, use fill_value=0 to fill NaNs with 0

In [2198]: df.pivot_table(index=['X_value', 'Y_value'], columns=['Z_value'], 
                          values='CustomerID', aggfunc='count', fill_value=0)
Out[2198]:
Z_value          1  4  5
X_value Y_value
1       1        1  0  0
2       1        1  0  0
5       1        0  1  2
        5        0  0  1

Or, using groupby and unstack

In [2199]: df.groupby(['X_value', 'Y_value', 'Z_value']).size().unstack(fill_value=0)
Out[2199]:
Z_value          1  4  5
X_value Y_value
1       1        1  0  0
2       1        1  0  0
5       1        0  1  2
        5        0  0  1

Hacky way to get missing X_value

In [2218]: (df.groupby(['X_value', 'Y_value', 'Z_value']).size().unstack(fill_value=0)
              .unstack(level=0, fill_value=0).stack()
              .reorder_levels(['X_value', 'Y_value'], axis=0))
Out[2218]:
Z_value          1  4  5
X_value Y_value
1       1        1  0  0
2       1        1  0  0
5       1        0  1  2
1       5        0  0  0
2       5        0  0  0
5       5        0  0  1

Upvotes: 1

Related Questions