input data frame below
data1 = {
'IDs': ['A1','A10','A11','A12','A13','A14','A17'],
'S_S': ['G001','' ,'' ,'' ,'' ,'' ,'' ,],
'St_s': ['Pa', '','' ,'' ,'' ,'' ,'' ,],
'SsFlag': ['Pr','' ,'' ,'' , '', '', '',],
'id' : [32,10,11,12,11,12,17],
'flag': [[ '68'],[ '68'],['12','34','6'],['24'],['20','21','34'],['14', '20'],['10', '68']]
}
df = pd.DataFrame.from_dict(data1)
Inital output
df
Out[131]:
IDs S_S St_s SsFlag id flag
0 A1 G001 Pa Pr 32 [68]
1 A10 10 [68]
2 A11 11 [12, 34, 6]
3 A12 12 [24]
4 A13 11 [20, 21, 34]
5 A14 12 [14, 20]
6 A17 17 [10, 68]
Required output
IDs S_S St_s SsFlag id flag
A1 G001 Pa Pr 32 [68]
A10 10 [68]
A11 11 [12]
A11 11 [34]
A11 11 [6]
A12 12 [24]
A13 11 [20]
A13 11 [21]
A13 11 [34]
A14 12 [14]
A14 12 [20]
A17 17 [10]
A17 17 [68]
The the flag column needs to rearrange and other columns gets filled up automatically I am not sure if a pd.wide_to_long or pd.melt or any apply function will do the job
I tried this from the below link as mentioned in the comments, getting the following output,
How to unnest (explode) a column in a pandas DataFrame? s=pd.DataFrame([[x] + [z] for x, y in zip(df.index,df.flag) for z in y]) ss = s.merge(df,left_on=0,right_index=True)
ss
Out[141]:
0 1 ID S_S St_s SsFlag id flag
0 0 68 A1 G001 Pa Pr 32 [68]
1 1 68 A10 10 [68]
2 2 12 A11 11 [12, 34, 6]
3 2 34 A11 11 [12, 34, 6]
4 2 6 A11 11 [12, 34, 6]
