[FIXED] pandas: group or pivot table by a column horizonally rather than vertically

Issue

I have got data that looks like this

data = [['01/01/2000', 'aaa', 101, 102],
        ['01/02/2000', 'aaa', 201, 202],
        ['01/01/2000', 'bbb', 301, 302],
        ['01/02/2000', 'bbb', 401, 402],]

df = pd.DataFrame(data, columns=['date', 'id', 'val1', 'val2'])
df

date        id      val1    val2 
01/01/2000  aaa     101     102
01/02/2000  aaa     201     202
01/01/2000  bbb     301     302
01/02/2000  bbb     401     402

I would like this data to be transformed to look like this - where it's grouped horizonally by the id column

            aaa             bbb
date        val1    val2    val1    val2    
01/01/2000  101     102     301     302     
01/02/2000  201     202     401     402

Closest i have gotten so far is: df.set_index(['date', 'id']).unstack(level=1), but this does not quite do it:

val1    val2
id  aaa bbb aaa bbb
date                
01/01/2000  101 301 102 302
01/02/2000  201 401 202 402

Solution

Add DataFrame.swaplevel with DataFrame.sort_index:

out = (df.set_index(['date', 'id'])
         .unstack(level=1)
         .swaplevel(0,1, axis=1)
         .sort_index(axis=1))
print (out )
id          aaa       bbb     
           val1 val2 val1 val2
date                          
01/01/2000  101  102  301  302
01/02/2000  201  202  401  402

Or use DataFrame.melt with DataFrame.pivot and DataFrame.sort_index:

out = (df.melt(['date', 'id'])
         .pivot(index='date', columns=['id', 'variable'], values='value')
         .sort_index(axis=1))

Answered By - jezrael