how to consolidate lots of series data? to make another dataframe. using stack function

user_14161207 0
Viewed 68

I'd like to consolidate dataframe but I don't know how to upload dataframe here so I just remain link below.
The data(original data on my spreadsheet)that I want to consolidate is form of series or dataframe.

Part#: consists of 8~13 character set (alphabet and number mixed)
Description: always right below the Part#.
Ref#: lots of Ref# separated by comma. At the end of last Ref#, there is no comma

From my last question, someone advised me to find Part# with iloc. But I will have a lot of row to be consolidated so I am not afford to designate them one by one. Is there anybody to give me achieve

With stack function, I want to stack Ref# and every different Ref# has to have its Part# and description according to its above value on original data

How I can build up python code for this?

https://docs.google.com/spreadsheets/d/10zSKfXavaXWl1MOo_ScW60rbERUDiQtDi6p-UbrUgro/edit?usp=sharing

pandas apply character stack python
1 Answers
import pandas as pd 

# create dataframe
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',
          'A2C00004540',
          'CY-AIIA 5.6K 1% 1/16W 0603',
          'R107,R124,R125,R126,R209,R214,R255,R329,',
          'R377,R404,R426',
          'A2C00000243',
          'ZENER DIODE(A/S)',
          'Z119',
          'A2C01888600',
          'R LIN,10K,5%,TK200,63mW,0402',
          'R101,R102,R106,R120,R184,R187,R289,R291',
          ',',
          'R317,R347,R400,R432,R449,R450,R464,R514',
          ',',
          'R515,R524,R615,R720,R753,R779,R780,R781',
          ',',
          'R784,R787,R788,R789,R790'
        ]       
})

df.head(100)

Output:

Output

Then we create new dataframe with "part" and "description" columns. Part column is based on regexp, which probably should be changed (i don't know format of the part name):

df1 = pd.DataFrame({
    'part': df[df['a'].str.match('A\dC\d{8}')]['a'].tolist(),
    'description': df.iloc[df[df['a'].str.match('A\dC\d{8}')].index + 1]['a'].tolist()
})
df1.head()

Output:

Output

Then we create a temporary dataframe for merging of ref rows:

df2 = pd.merge(df, df1, left_on='a', right_on='part', how='left')
df2.drop(df2[df2['a'].isin(df2['description'])].index, inplace=True)
df2.loc[df2['a'] == df2['part'], 'a'] = ''
df2['part'].fillna(method='ffill', inplace=True)
df2.head()

Output:

Output

Then we merge ref rows:

df1 = df1.merge(df2.groupby(['part'])['a'].agg([('a', ''.join)]), on='part').rename(columns={'a': 'ref'})
df1.head()

Output:

Output

And finally we explode ref column:

df1 = df1.set_index(['part', 'description']).apply(lambda x: x.str.split(',').explode()).reset_index()  
df1

Output:

Output

user_141686230
Related