Adding particular rows and changing their positions

Question

I have an example(left one in image description).

There are several indexes in the first column. However, the third (not just for the third one because I have data from more than a thousand repeating intervals) of the repeating characters is missing-data which is 'GG'.

Question: I want to add particular rows (like 'GG') with the value 'NaN'

I want to display its values ​​in different columns based on the characters of the repeated section(from 'II' to '//\n').

Is there any way I can do in this situation?.

Answer 1

Assuming your data is a dataframe with all data as columns (if not, you first need to reset_index):

(df
   #.reset_index() # uncomment if first column is index
   .assign(cols=df.groupby('col1').cumcount())
   .pivot(index='col1', columns='cols', values='col2')
)

output:

cols    0    1     2
col1                
A     0.0  4.0   7.0
B     1.0  5.0   8.0
C     2.0  9.0   NaN
D     3.0  6.0  10.0

input:

   col1  col2
0     A     0
1     B     1
2     C     2
3     D     3
4     A     4
5     B     5
6     D     6
7     A     7
8     B     8
9     C     9
10    D    10