Arranging the dataframe from one row to multiple columns in python

Question

I have a dataframe which contains single column but multiple values in the row . I need to transpose and append the data correctly from each row.

The dataframe is as follows:

l1     l2
0      a:1
       b:2
       c:3
1      a:11
       b:12
       c:13
2      a:21
       b:22
       c:33

The column name l1 is the index column and column named l2 contains the parameter name and value .I need to consider the l2 column and arrange the dataframe as the desired output.

The desired output is as follows:

a     b    c
1     2    3
11    12   13
21    22   33

The code which I have tried is of transposing .

df1=df.T

But It should Transpose each row value to the columns.

Answer 1

First convert index l1 to column, then replace empty strings to missing values and forward filling them, also for column l2 is used Series.str.split to new 2 columns, last use DataFrame.pivot:

df = df.reset_index()
df['l1'] = df['l1'].replace('',np.nan).ffill()
df[['l21','l22']] = df['l2'].str.split(':', expand=True)
print (df)
  l1    l2 l21 l22
0  0   a:1   a   1
1  0   b:2   b   2
2  0   c:3   c   3
3  1  a:11   a  11
4  1  b:12   b  12
5  1  c:13   c  13
6  2  a:21   a  21
7  2  b:22   b  22
8  2  c:33   c  33

df = df.pivot('l1','l21','l22')
print (df)
l21   a   b   c
l1             
0     1   2   3
1    11  12  13
2    21  22  33