Python Pandas Unstacking Unique Column Values to Columns Of Their Own

You can use cumcount for creating column for new index and then pivot_table with aggregating join:

df['g'] = df.groupby('Col1')['Col1'].cumcount()print (df.pivot_table(index='g', columns='Col1', values='Col2', aggfunc=''.join))Col1 label1 label2 label3 label4g                               0         a      b      c      q1         d      e      f   None

Thank you for comment Jeff L.:

df['g'] = df.groupby('Col1')['Col1'].cumcount()print (df.pivot(index='g', columns='Col1', values='Col2'))Col1 label1 label2 label3 label4g                               0         a      b      c      q1         d      e      f   None

Or:

print (pd.pivot(index=df.groupby('Col1')['Col1'].cumcount(),                columns=df['Col1'],                 values=df['Col2']))Col1 label1 label2 label3 label40         a      b      c      q1         d      e      f   None

Using set_index and unstack, you could do

In [17]: df.set_index([df.groupby('Col1')['Col1'].cumcount(), 'Col1'])['Col2'].unstack()Out[17]:Col1 label1 label2 label3 label40         a      b      c      q1         d      e      f   None

Details

In [18]: dfOut[18]:     Col1 Col20  label1    a1  label1    d2  label2    b3  label2    e4  label3    c5  label3    f6  label4    q