split rows into columns in pandas dataframe

Question

I have table similar to this table:

     id      val  
0   abc_1    5
1   abc_1    3
2   abc_1    7
3   abc_2    12
4   abc_2    6
5   abc_2    9
...

I want to "split" the rows into columns based on the id, so the result should be:

     id      val1   val2   val3  
0   abc_1    5       3       7
1   abc_2    12      6       9

I was trying to do it by creating groups and then use pivot :

df['group'] = (df.index // 3) + 1

>>>
     id      val    group  
0   abc_1    5       1
1   abc_1    3       1
2   abc_1    7       1
3   abc_2    12      2
4   abc_2    6       2
5   abc_2    9       2

but then then when I was using pivot I got many new columns al with null values.

My goal is to get this desired output , open to other solutions as well.

...

     id      val1   val2   val3  
0   abc_1    5       3       7
1   abc_2    12      6       9

Answer 1

A possible solution:

(df.assign(val_names = [f'val{i}' for i in df.groupby('id').cumcount()+1])
 .pivot(index='id', columns='val_names', values='val')
 .rename_axis(None, axis=1)
 .reset_index())

Output:

      id  val1  val2  val3
0  abc_1     5     3     7
1  abc_2    12     6     9