I have a column that contains string like
'ID=1,Name=GDNDL380F4,Type=FTT-87, Value=GDNDL380F4,Registration Info: Sn = GDNDL380F4, Service Level=Service Level Disabled' |Column |'ID=1,Name=GDNDL380F4,Type=FTT-87, Value=GDNDL380F4,Registration Info: Sn = GDNDL380F4, Service Level=Service Level Disabled'| |'ID=1,Name=GDNDL4040F4,Type=FTT-87, Value=GDNDL4040F4,Registration Info: Sn = GDNDL380F4, Service Level=Service Level Disabled'| |'ID=1,Name=GDNDL380F5,Type=FTT-87, Value=GDNDL380F5,Registration Info: Sn = GDNDL380F4, Service Level=Service Level Disabled'|
Expected Output:
Name | Type | GDNDL380F4 |FTT-87 | GDNDL4040F4|FTT-87 | GDNDL380F5 |FTT-87 |
What I need is to get the substring (GDNDL380F4) after Name= to column Name and substring (FTT-87) after Type= to column Type.
I tried different approaches with str.extract, find and re.find but for some reason I get wrong result (nan or an empty array). So help is needed.
Advertisement
Answer
Make a dictionary from the string and get the items by key
s = 'ID=1,Name=GDNDL380F4,Type=FTT-87, Value=GDNDL380F4,Registration Info: Sn = GDNDL380F4, Service Level=Service Level Disabled' d = dict(item.split("=") for item in s.split(",")) print(d['Name']) # GDNDL380F4 print(d['Type']) # FTT-87
Pandas solution:
df1=pd.DataFrame([dict(y.split("=") for y in x.strip("' ").split(",")) for x in df['Column']], index=df.index) print (df1) ID Name Type Value Registration Info: Sn 0 1 GDNDL380F4 FTT-87 GDNDL380F4 GDNDL380F4 1 1 GDNDL4040F4 FTT-87 GDNDL4040F4 GDNDL380F4 2 1 GDNDL380F5 FTT-87 GDNDL380F5 GDNDL380F4 Service Level 0 Service Level Disabled 1 Service Level Disabled 2 Service Level Disabled