How to do a transpose a dataframe group by key on pandas?

Question

I have this table from my database and I need a transpose group by survey_id

id  answer  survey_id   question_number questionid 
216     0.0         69               3         2.0   
217     3.0         69               4         3.0   
218     0.0         69               5         4.0   
219     0.0         69               6         5.0   
221     0.0         69               8         7.0

Like this:

Survey P01  P02 P03 P04 P05
69     1    1   2   2   1

The cell is the answer and the column is format "P{question_number}"

I'm using pandas 0.18.1.

How can I do that?

Do you need `print (df.pivot(index='survey_id', columns='question_number', values='answer').add_prefix('P'))` ? — jezrael, Aug 23 '16 at 17:49
Thanks! Almost there.. But how can I add a Survey_id as the first column? I tried to stack, but is not exactly I need. The big deal it's use survey_id to merge with another table. — Murilo Azevedo, Aug 23 '16 at 18:07

score 18 · Accepted Answer · answered Aug 23 '16 at 18:11

You can use pivot, add_prefix and reset_index:

print (df.pivot(index='survey_id', columns='question_number', values='answer')
         .add_prefix('P')
         .reset_index())

question_number  survey_id   P3   P4   P5   P6   P8
0                       69  0.0  3.0  0.0  0.0  0.0

How to do a transpose a dataframe group by key on pandas?

1 Answers1

Linked