Pandas: pivot a dataframe

Question

Apologies in advance for the super-newbie question.

I'm learning to use pandas, and have this simple operation that I can't figure out how to perform:

I have the following data frame:

print df 
Out[19]: 
USERNAME  REQUEST_TYPE   STATUS  LATENCY
0      foo             1  SUCCESS        7
1      foo             2  SUCCESS       17
2      bar             1  SUCCESS       10
3      bar             2  FAILURE       12

I would like to have one row for each USERNAME, which is the concatenation of the STATUS and LATENCY columns per REQUEST_TYPE. The output should look like this:

USERNAME    STATUS_1  LATENCY_1     STATUS_2    LATENCY_2
0      foo  SUCCESS     7           SUCCESS        17
1      bar  SUCCESS     10          FAILURE        12

I thought of something starting with pandas.groupby(df,['USERNAME', 'REQUEST_TYPE']), but I am not sure how to concatenate the rows back, and whether there is any method which would create new column names.

Thanks!

score 5 · Accepted Answer · answered Jul 25 '12 at 13:43

b = df.pivot('USERNAME', 'REQUEST_TYPE')
b.columns = ['{0}_{1}'.format(*col) for col in b.columns]

b is now:

         LATENCY_1 LATENCY_2 STATUS_1 STATUS_2
USERNAME                                      
bar             10        12  SUCCESS  FAILURE
foo              7        17  SUCCESS  SUCCESS

Pandas: pivot a dataframe

1 Answers1