how to consolidate series data and make new dataframe in pandas?

Question

I've got dataframe like this original data

and I hope to have new dataframe like below new data

How can I create code for this modification? It need to consolidate first series data and create new dataframe.

Answer 1

Some imports:

import pandas as pd 
import numpy as np

Here we create dataframe from data you provided:

df = pd.DataFrame({
    "a" : [
           'A2C02158300', 'D REC/BAS16-03W 100V 250mA SOD323 0s SMD', 'D201,D206,D218,D219,D222,D302,D308,D408', 'D409,D501,D502,D505,D506,D507,D508', 
           'A2C02250500', 'T BIP/PUMD3,SOT363,SMD SOLDERING', 'T209,T501,T502'
          ]
})

df.head(10)

Output:

Then we prepare dataframe with first 2 columns:

s1 = df.iloc[::4, :]
s1.reset_index(drop=True, inplace=True)

s2 = df.iloc[1::4, :]
s2.reset_index(drop=True, inplace=True)

df = pd.DataFrame({
    'a': s1['a'],
    'b': s2['a']
})

After that prepare and add third column:

s3 = df.iloc[2::4, :]
s3.reset_index(drop=True, inplace=True)
s3 = s3['a'].str.split(',').apply(pd.Series, 1).stack()
s3.index = s3.index.droplevel(-1)
s3.name = 'c'

df = df.join(s3)
df.reset_index(drop=True, inplace=True)
df

Output: