Myujth

I have a dataframe as below.

df = DataFrame(['B1': '1C', 'B2': '', 'B3': '', ,
 'B1': '3A', 'B2': '1A', 'B3': '',
 'B1': '41A', 'B2': '28A', 'B3': '3A'])

 B1 B2 B3 
0 1C 
1 3A 1A 
2 41A 28A 3A 

Now I extracted letters from B1-B3 and add to new columns U1-U3 get:

 B1 B2 B3 U1 U2 U3 
0 1C C 
1 3A 1A A A 
2 41A 28A 3A A A A 

and I want to let the row to explode like this:

 B1 B2 B3 U1 U2 U3 
0 1C C 
1 3A 1A A 
2 3A 1A A 
3 41A 28A 3A A 
4 41A 28A 3A A 
5 41A 28A 3A A 

Thanks in advance

I think, it needs 3 step solution of

1) extracting the Alphabates from data and creating new columns,

2) duplicating the rows w.r.t values and

3) masking with identity matrix.

df = pd.DataFrame(['B1': '1C', 'B2': '', 'B3': '', ,
 'B1': '3A', 'B2': '1A', 'B3': '',
 'B1': '41A', 'B2': '28A', 'B3': '3A'])

 B1 B2 B3
0 1C 
1 3A 1A 
2 41A 28A 3A

1) Extracting the Alphabates from the rows and assigning as columns

df = df.merge(df.apply(lambda x: x.str.extract('([A-Za-z])')).add_prefix('U_'), left_index=True,right_index=True,how='outer')

Out:

 B1 B2 B3 U_B1 U_B2 U_B3
0 1C C NaN NaN
1 3A 1A A A NaN
2 41A 28A 3A A A A

2) You can try of duplicating the rows of dataframe wherever it has more than 1 value

# Duplicating the rows of dataframe
val = df[['U_B1','U_B2','U_B3']].notnull().sum(axis=1)
df1 = df.loc[np.repeat(val.index,val)]

-> 3) then by grouping with index, pick only masked values of identity matrix(np.identity) w.r.t length of each group.

df1[['U_B1','U_B2','U_B3']] = df1.groupby(df1.index)['U_B1','U_B2','U_B3'].apply(lambda x: x.dropna(axis=1).mask(np.identity(len(x))==0))

Out:

 B1 B2 B3 U_B1 U_B2 U_B3
0 1C C 
1 3A 1A A 
1 3A 1A A 
2 41A 28A 3A A 
2 41A 28A 3A A 
2 41A 28A 3A A