DougKruger - 1 year ago 57

Python Question

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`

`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 Source

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
```