Reputation: 197
I'm trying to reorganise a pandas dataframe, which looks like this
t p q r s
1 a 1 14 26 38
1 b 2 15 27 39
1 c 4 16 28 40
2 a 6 18 30 42
2 b 7 19 31 43
2 c 8 20 32 44
3 a 10 22 34 46
3 b 11 23 35 47
3 c 12 24 36 48
To one that looks like this:
a b c
p q r s p q r s p q r s
1 1 14 26 38 2 15 27 39 4 16 28 40
2 6 18 30 42 7 19 31 43 8 20 32 44
3 10 22 34 46 11 23 35 47 12 24 36 48
Any ideas?
Thanks!
Upvotes: 2
Views: 209
Reputation: 197
And the winner is...
(My starting df is [29196708 rows x 5 columns], PC is an I7)
df = df.set_index('t', append=True).unstack().swaplevel(0,1, axis=1).sort_index(axis=1)
...32.5 seconds
Shortly followed by
df.pivot(columns='t').swaplevel(0,1, axis=1).sort_index(axis=1)
33.6 seconds
df = df.set_index('t', append=True).stack().unstack([1,2])
.. took a whopping 71.1 seconds
(Didnt try the one using melt on account of not have a column named variable)
Thanks all for your help
Upvotes: 0
Reputation: 323396
By using melt
and MultiIndex
df1=df.reset_index().melt(['index','t'])
df1['new']=list(zip(df1.t,df1.variable))
df1=df1.pivot('index','new','value')
df1.columns=pd.MultiIndex.from_tuples(df1.columns)
df1
Out[259]:
a b c
p q r s p q r s p q r s
index
1 1 14 26 38 2 15 27 39 4 16 28 40
2 6 18 30 42 7 19 31 43 8 20 32 44
3 10 22 34 46 11 23 35 47 12 24 36 48
Upvotes: 0
Reputation: 863791
You can use unstack
:
df = df.set_index('t', append=True).unstack().swaplevel(0,1, axis=1).sort_index(axis=1)
print (df)
t a b c
p q r s p q r s p q r s
1 1 14 26 38 2 15 27 39 4 16 28 40
2 6 18 30 42 7 19 31 43 8 20 32 44
3 10 22 34 46 11 23 35 47 12 24 36 48
df = df.set_index('t', append=True).stack().unstack([1,2])
print (df)
t a b c
p q r s p q r s p q r s
1 1 14 26 38 2 15 27 39 4 16 28 40
2 6 18 30 42 7 19 31 43 8 20 32 44
3 10 22 34 46 11 23 35 47 12 24 36 48
Upvotes: 2
Reputation: 30605
Use pivot with swap level i.e
df.pivot(columns='t').swaplevel(0,1, axis=1).sort_index(axis=1)
Output:
t a b c p q r s p q r s p q r s 1 1 14 26 38 2 15 27 39 4 16 28 40 2 6 18 30 42 7 19 31 43 8 20 32 44 3 10 22 34 46 11 23 35 47 12 24 36 48
Upvotes: 1