Murilo Azevedo Murilo Azevedo - 3 months ago 27
Python Question

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

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?

Answer

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