DougKruger DougKruger - 4 months ago 24
Python Question

Pandas pivot table arrangement no aggregation

I wanted to pivot pandas dataframe without aggregation, and instead of presenting the pivot index column vertically, I want to present horizontally. I tried with

pd.pivot_table
but not getting exactly what I wanted.

data = {'year': [2011, 2011, 2012, 2013, 2013],
'A': [10, 21, 20, 10, 39],
'B': [12, 45, 19, 10, 39]}

df = pd.DataFrame(data)
print df
A B year
0 10 12 2011
1 21 45 2011
2 20 19 2012
3 10 10 2013
4 39 39 2013

But I wish to present as:

year 2011 2012 2013
cols A B A B A B
0 10 12 20 19 10 10
1 21 45 NaN NaN 39 39

Answer

You can first create column for new index by cumcount, then stack with unstack:

df['g'] = df.groupby('year')['year'].cumcount()
df1 = df.set_index(['g','year']).stack().unstack([1,2])
print (df1)

year  2011        2012        2013      
         A     B     A     B     A     B
g                                       
0     10.0  12.0  20.0  19.0  10.0  10.0
1     21.0  45.0   NaN   NaN  39.0  39.0

If need set columns names use rename_axis (new in pandas 0.18.0):

df['g'] = df.groupby('year')['year'].cumcount()
df1 = df.set_index(['g','year'])
        .stack()
        .unstack([1,2])
        .rename_axis(None)
        .rename_axis(('year','cols'), axis=1)
print (df1)
year  2011        2012        2013      
cols     A     B     A     B     A     B
0     10.0  12.0  20.0  19.0  10.0  10.0
1     21.0  45.0   NaN   NaN  39.0  39.0

Another solution with pivot, but you need swap first and second level of Multiindex in columns by swaplevel and then sort it by sort_index:

df['g'] = df.groupby('year')['year'].cumcount()
df1 = df.pivot(index='g', columns='year')
df1 = df1.swaplevel(0,1, axis=1).sort_index(axis=1)
print (df1)
year  2011        2012        2013      
         A     B     A     B     A     B
g                                       
0     10.0  12.0  20.0  19.0  10.0  10.0
1     21.0  45.0   NaN   NaN  39.0  39.0
print (df1)

year  2011        2012        2013      
         A     B     A     B     A     B
g                                       
0     10.0  12.0  20.0  19.0  10.0  10.0
1     21.0  45.0   NaN   NaN  39.0  39.0
Comments