Python pandas dataframe reshape value of col to new col-CodePudding

I have a dataframe that looks like this:

    col1    col2    col3    col4    col5    col6    
0   1.1     a       29      b       c       d
1   2.3     a       29      b       c       d
2   10.3    a       29      b       c       d
3   6.5     a       29      b       c       d
4   34.7    a       29      b       c       d

5   6.3     e       25      f       g       h
6   7.1     e       25      f       g       h
7   36.0    e       25      f       g       h
8   74.2    e       25      f       g       h
9   64.7    e       25      f       g       h

and I want to reshape it so it looks like:

    col1    col2    col3    col4    col5    col6    col7    col8    col9    col10
0   a       29      b       c       d       1.1     2.3     10.3    6.5     34.7
1   e       25      f       g       h       6.3     7.1     36.0    74.2    64.7

I tried it with

df = df.groupby('col2')['col1'].apply(list)

col2 
a    [1.1, 2.3, 10.3, 6.5, 34.7]
b    [6.3, 7.1, 36.0, 74.2, 64.7]

but then I don´t know how to get each value from the list to a new col.

CodePudding user response：

Use GroupBy.cumcount with DataFrame.pivot:

df['new'] = df.groupby(['col2','col3','col4','col5','col6']).cumcount()

df = df.pivot(index=['col2','col3','col4','col5','col6'], columns='new', values='col1').reset_index()
df.columns = [f'col{i   1}' for i in range(len(df.columns))]
print (df)
  col1  col2 col3 col4 col5  col6  col7  col8  col9  col10
0    a    29    b    c    d   1.1   2.3  10.3   6.5   34.7
1    e    25    f    g    h   6.3   7.1  36.0  74.2   64.7