Pandas pivot_table include empty identities

Admin 1
Viewed 243

Dataset

x   y   a
1   3   0
1   1   0
1   2   0
3   6   0
5   3   1
1   5   0
1   7   0
1   6   0
1   4   0

Visualization

grid size= 8 x 12

![enter image description here

Pivot_table

X = df.pivot(index='x',columns='y',values='a').values
X[np.isnan(X)] = 0

array([[0., 0., 1., 0., 1., 0., 0., 0.],
       [0., 1., 0., 0., 1., 0., 1., 0.],
       [0., 0., 0., 0., 1., 0., 0., 0.],
       [0., 1., 1., 1., 0., 1., 0., 0.],
       [0., 0., 0., 1., 0., 0., 0., 0.],
       [0., 0., 0., 0., 0., 1., 0., 0.],
       [0., 0., 0., 0., 0., 0., 0., 1.],
       [0., 0., 0., 0., 1., 0., 0., 0.]])

here pivot table size is (8, 8) but I want to have (8 , 12) the empty rows are excluded in the pivot table.

pandas pivot numpy arrays python
4 Answers

Try with reindex:

X = (df.pivot(index='x',columns='y', values='a')
   .fillna(0)
   .reindex(np.arange(12), axis=1, fill_value=0)
   .reindex(np.arange(8), fill_value=0)
)

Output:

y  0    1    2    3    4    5    6    7   8   9   10  11
x                                                       
0   0  0.0  0.0  0.0  0.0  0.0  0.0  0.0   0   0   0   0
1   0  0.0  0.0  0.0  0.0  0.0  0.0  0.0   0   0   0   0
2   0  0.0  0.0  0.0  0.0  0.0  0.0  0.0   0   0   0   0
3   0  0.0  0.0  0.0  0.0  0.0  0.0  0.0   0   0   0   0
4   0  0.0  0.0  0.0  0.0  0.0  0.0  0.0   0   0   0   0
5   0  0.0  0.0  1.0  0.0  0.0  0.0  0.0   0   0   0   0
6   0  0.0  0.0  0.0  0.0  0.0  0.0  0.0   0   0   0   0
7   0  0.0  0.0  0.0  0.0  0.0  0.0  0.0   0   0   0   0

Also consider set_index().unstack() instead of pivot:

X = (df.set_index(['x','y'])
       ['a'].unstack(fill_value=0)
       .reindex(np.arange(12), axis=1, fill_value=0)
       .reindex(np.arange(8), fill_value=0)
    )

which gives you a better looking data:

y  0   1   2   3   4   5   6   7   8   9   10  11
x                                                
0   0   0   0   0   0   0   0   0   0   0   0   0
1   0   0   0   0   0   0   0   0   0   0   0   0
2   0   0   0   0   0   0   0   0   0   0   0   0
3   0   0   0   0   0   0   0   0   0   0   0   0
4   0   0   0   0   0   0   0   0   0   0   0   0
5   0   0   0   1   0   0   0   0   0   0   0   0
6   0   0   0   0   0   0   0   0   0   0   0   0
7   0   0   0   0   0   0   0   0   0   0   0   0
user_42384080

Numpy assignment

x = pd.Categorical(df.x, range(1, 9))
y = pd.Categorical(df.y, range(1, 13))

b = np.zeros((8, 12), int)

b[x.codes, y.codes] = df.a

pd.DataFrame(b, x.categories, y.categories)

   1   2   3   4   5   6   7   8   9   10  11  12
1   0   0   0   0   0   0   0   0   0   0   0   0
2   0   0   0   0   0   0   0   0   0   0   0   0
3   0   0   0   0   0   0   0   0   0   0   0   0
4   0   0   0   0   0   0   0   0   0   0   0   0
5   0   0   1   0   0   0   0   0   0   0   0   0
6   0   0   0   0   0   0   0   0   0   0   0   0
7   0   0   0   0   0   0   0   0   0   0   0   0
8   0   0   0   0   0   0   0   0   0   0   0   0
user_23366540

You can create the 0 DataFrame with all rows and columns and update with the pivot.

import pandas as pd

res = pd.DataFrame(index=range(1, 9), columns=range(1, 13), data=0)
res.update(df.pivot('x', 'y', 'a'))

     1    2    3    4    5    6    7   8   9   10  11  12
1  0.0  0.0  0.0  0.0  0.0  0.0  0.0   0   0   0   0    0
2  0.0  0.0  0.0  0.0  0.0  0.0  0.0   0   0   0   0    0
3  0.0  0.0  0.0  0.0  0.0  0.0  0.0   0   0   0   0    0
4  0.0  0.0  0.0  0.0  0.0  0.0  0.0   0   0   0   0    0
5  0.0  0.0  1.0  0.0  0.0  0.0  0.0   0   0   0   0    0
6  0.0  0.0  0.0  0.0  0.0  0.0  0.0   0   0   0   0    0
7  0.0  0.0  0.0  0.0  0.0  0.0  0.0   0   0   0   0    0
8  0.0  0.0  0.0  0.0  0.0  0.0  0.0   0   0   0   0    0
user_43333590

try using a tensor matrix

import torch
tensor_matrix = torch.zeros(8, 12)
tensor_matrix=tensor_matrix.reshape(8,12)

data=[(1,   3,   0),
(1,   1,   0),
(1,   2,   0),
(3,   6,   0),
(5,   3,   1),
(1,   5,   0),
(1,   7,   0),
(1,   6,   0),
(1,   4,   0)]
df=pd.DataFrame(data,columns=['x','y','a'])
for key,row in df.iterrows():
    tensor_matrix[row.x-1,row.y-1]=row.a

df2=pd.DataFrame(np.array(tensor_matrix),columns=[str(i) for i in range(1,13)])

df2.reset_index()
df2.index = df2.index + 1
print (df2)

output:

     1    2    3    4    5    6    7    8    9   10   11   12
1  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0
2  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0
3  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0
4  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0
5  0.0  0.0  1.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0
6  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0
7  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0
8  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0
user_40011770
Related