pandas transpose numeric column by group
<p>code to make test data</p>

<pre><code>import pandas as pd

dftest = pd.DataFrame({'Amt': {0: 60, 1: 35.0, 2: 30.0, 3: np.nan, 4: 25},
                       'Year': {0: 2012.0, 1: 2012.0, 2: 2012.0, 3: 2013.0, 4: 2013.0},
                       'Name': {0: 'A', 1: 'A', 2: 'C', 3: 'A', 4: 'B'}})
</code></pre>

<p>gives</p>

<pre><code>    Amt     Name    Year
0   60        A   2012.0
1   35.0      A   2012.0
2   30.0      C   2012.0
3   NaN       A   2013.0
4   25        B   2013.0
</code></pre>

<p>column <code>Amt</code> has max 2 values corresponding to group <code>['Name', 'Year']</code>. I would like to pivot/transpose such that output is of the form</p>

<pre><code>       Name  Year   Amt1  Amt2
0         A  2012   35    60
2         C  2012   30    NaN
3         A  2013   NaN   NaN
4         B  2013   25    NaN
</code></pre>

<p>I have tried playing with pivot, unstack, pivot_table </p>

<p>what I really want to do is to ensure there are two values of <code>Amt</code> per <code>['Name', 'Year']</code> (<code>NA</code>'s are OK), which I can achieve by stacking the desired output</p>

<p>use <code>groupby</code> and <code>apply</code></p>

<pre><code>f = lambda x: x.sort_values(ascending=True).reset_index(drop=True)
dftest.groupby(['Name', 'Year']).Amt.apply(f).unstack()
</code></pre>

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

python pandas pivot pivot-table transpose