Pivot full dataframe using Pandas

Question

I have a dataset, df, where I would like to pivot the data so that the row values become columns.

Data

id  Date    consumed    available       
aa  q122    727.2       272.8       
aa  q222    975.7       24.3        
bb  q122    0           1000        
bb  q222    117.3       882.7

Desired

id      q122_consumed   q122_available  q222_consumed   q222_available  
aa      727.2           272.2           975.7           24.3    
bb      0               1000            117.3           882.7

Doing

out = df[['id', 'Date']].join(
        df.assign(consumed=df['consumed'],
                  available=df['available'])
          .groupby('id')[['consumed', 'available']].cumsum()
    ).pivot('id', 'Date', ['consumed', 'available'])

out.columns = out.columns.to_flat_index().map('_'.join)

However, I am not getting the desired output. Any suggestion is appreciated.

score 2 · Answer 1 · answered Oct 27 '21 at 05:05

2

Use pivot method:

df.pivot(index="id", columns="Date", values=["consumed", "available"])

# output
     consumed        available
Date     q122   q222      q122   q222
id
aa      727.2  975.7     272.8   24.3
bb        0.0  117.3    1000.0  882.7

answered Oct 27 '21 at 05:05

Pav3k

869
4
10

Thank you. Any way to make separate columns? consumed q122 consumed q222 available q122 available q222? – Lynn Oct 27 '21 at 05:11
2

`df.columns = ['_'.join(c) for c in df.columns]` ? – luigigi Oct 27 '21 at 05:14
I deleted my answer about columns - @luigigi did it much better :) – Pav3k Oct 27 '21 at 05:16
Thank you Pav3k. Thank you @luigigi - am I adding this line after Pav3k 's line? – Lynn Oct 27 '21 at 05:18
1

@Lynn yes, or you can use the line you used before. same thing i guess – luigigi Oct 27 '21 at 05:19

Pivot full dataframe using Pandas

1 Answers1