Pandas Move Numeric Column By Group

Question

to create test data

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'}})

gives

    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

column Amthas max 2 values ​​corresponding to the group ['Name', 'Year']. I would like to rotate / shift so that the output looks like

       Name  Year   Amt1  Amt2
0         A  2012   35    60
2         C  2012   30    NaN
3         A  2013   NaN   NaN
4         B  2013   25    NaN

I tried playing with swivel, unstack, pivot_table

what I really want to do is to ensure that there are two Amtper values ['Name', 'Year']( NAOK), which I can achieve by laying out the desired output

Answer 1

use groupbyandapply

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