Trying to use pandas pivot like excel pivot
<p>I have a pandas data frame like this that I want to pivot using pd.pivot_table</p>

<pre><code>import pandas
df = pd.DataFrame({"Id":[1, 1, 2, 2, 3, 3, 4, 4, 5, 5, 6, 6, 7, 7, 8, 8, 9, 10],
                   "Error":[0, 99, 0, 0, 0, 98, 0, 0, 0, 0, 33, 0, 23, 0, 0, 0, 83, 0]})
</code></pre>

<p>Im trying to pivot it like this (pivot made in Excel):</p>

<p><a href="http://i.stack.imgur.com/urqnc.png" rel="nofollow"><img src="http://i.stack.imgur.com/urqnc.png" alt="enter image description here"></a></p>

<p>I tried this:</p>

<pre><code>dfPivot = pd.pivot_table(df, index = "Id", columns = df.Error.unique(), values = "Error", aggfunc="count")
</code></pre>

<p>I got following error.</p>

<pre><code>AssertionError: Grouper and axis must be same length
</code></pre>

<p>Thank you in advance.</p>

<p>IIUC you can do it this way:</p>

<pre><code>In [7]: df.pivot_table(index='Id', columns='Error', aggfunc='size', fill_value=0)
Out[7]:
Error  0   23  33  83  98  99
Id
1       1   0   0   0   0   1
2       2   0   0   0   0   0
3       1   0   0   0   1   0
4       2   0   0   0   0   0
5       2   0   0   0   0   0
6       1   0   1   0   0   0
7       1   1   0   0   0   0
8       2   0   0   0   0   0
9       0   0   0   1   0   0
10      1   0   0   0   0   0

In [8]: df.pivot_table(index='Id', columns='Error', aggfunc='size', fill_value='')
Out[8]:
Error 0  23 33 83 98 99
Id
1      1              1
2      2
3      1           1
4      2
5      2
6      1     1
7      1  1
8      2
9               1
10     1
</code></pre>

<p>If you want to have <code>Grand Total</code> - you can use <code>margins=True</code> parameter, but it'll be bit tricky:</p>

<pre><code>In [42]: df.pivot_table(index='Id', columns='Error', aggfunc='size', fill_value=0, margins=True)
...skipped...
TypeError: 'str' object is not callable
</code></pre>

<p>but this hacky variant works:</p>

<pre><code>In [43]: (df.assign(x=0)
   ....:    .pivot_table(index='Id', columns='Error', aggfunc='count',
   ....:                 fill_value=0, margins=True, margins_name='Grand Total')
   ....:    .astype(int)
   ....: )
Out[43]:
              x
Error         0 23 33 83 98 99 Grand Total
Id
1             1  0  0  0  0  1           2
2             2  0  0  0  0  0           2
3             1  0  0  0  1  0           2
4             2  0  0  0  0  0           2
5             2  0  0  0  0  0           2
6             1  0  1  0  0  0           2
7             1  1  0  0  0  0           2
8             2  0  0  0  0  0           2
9             0  0  0  1  0  0           1
10            1  0  0  0  0  0           1
Grand Total  13  1  1  1  1  1          18
</code></pre>

python excel pandas dataframe pivot