pandas data extract info from strings

March 21, 2022

Suppose I have the following DataFrame:

d = {'Name': ['One Stop - Johnny MELAVO;\nN: 468 847 R457\nH: 
06H4155\n (KSMITH)',
'Inpatient - Sonny CROCKETT;\nN: 46X 847 T457\nH: 06H9155\n 
(KSMITH), of which 2 containers have been returned',
'One Stop - Muhammed John ARKANDROID;\nN: 6H1 7R5 6098\nH: 
4072W74\n (PSTAFFORDJ)',
'One Stop - Novillos CURRAN-POCO;\nN: 462 11L 2931\nH: 
F14R917\n (FSABU)'],
'Quantity': [10, 25, 12, 15]}
df = pd.DataFrame(d)

Is there a quicker (or pythonic) way to extract the following info from name, so I can have just this?:
Jonny MELAVO, 06H4155.
I know it can be done in several steps, but was wondering if there is a better way.
Thanks

>Solution :

You can use str.split:

df['Name'] = df['Name'].str.split(r'[-;:\n]', expand=True)[[1, 6]]
                       .apply(','.join, axis=1)
print(df)

# Output
                       Name  Quantity
0    Johnny MELAVO, 06H4155        10
1   Sonny Crockett, 06H9155        25

Intermediate result:

>>> df['Name'].str.split(r'[-;:\n]', expand=True)
            0                1 2  3              4  5         6                                                  7
0   One Stop     Johnny MELAVO    N   468 847 R457  H   06H4155                                           (KSMITH)
1  Inpatient    Sonny Crockett    N   46X 847 T457  H   06H9155   (KSMITH), of which 2 containers have been ret...