CODEWITHSUNDEEP
pandas
Tiny
Tiny

Reputation: 197

How to reorganise a pandas dataframe

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

Answers (4)

Tiny
Tiny

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

BENY
BENY

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

jezrael
jezrael

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

Or unstack + stack:

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

Bharath M Shetty
Bharath M Shetty

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

Related Questions