How to split col into rows?

Question

I have the following pandas Dataframe df:

ctype cdate       cval1   cval2
1     2020-01-01  34      A
2     2020-01-01  33      B
3     2020-01-01  21      A
1     2020-01-02  35      A
2     2020-01-02  30      C
3     2020-01-02  28      B

I need to convert ctype column inot rows to get the following dataframe:

cdate       cval1_1   cval1_2   cval_3    cval2_1    cval2_2     cval2_3
2020-01-01  34        33        21        A          B           A
2020-01-02  35        30        28        A          C           B

How can I get such result?

Answer 1

You can set "cdate" aside as index, set_index+unstack "ctype" and finally, merge the two levels of the resulting index into a single string:

df2 = df.set_index(['cdate', 'ctype']).unstack(level=1)
df2.columns = df2.columns.map(lambda x: x[0]+'_'+str(x[1]))

or use pivot to reshape:

df2 = df.pivot(index='cdate', columns='ctype')
df2.columns = df2.columns.map(lambda x: x[0]+'_'+str(x[1]))

output:

            cval1_1  cval1_2  cval1_3 cval2_1 cval2_2 cval2_3
cdate                                                        
2020-01-01       34       33       21       A       B       A
2020-01-02       35       30       28       A       C       B