pandas data extract info from strings-CodePudding

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

CodePudding user response：

Update

Use str.extract

df['Name'] = df['Name'].str.extract('-\s (.*);[^:] :[^:] :\s (.*)\s \(') \
                       .apply(', '.join, axis=1).values
print(df)

# Output
                                Name  Quantity
0             Johnny MELAVO, 06H4155        10
1            Sonny CROCKETT, 06H9155        25
2  Muhammed John ARKANDROID, 4072W74        12
3      Novillos CURRAN-POCO, F14R917        15

Intermediate result:

>>> df['Name'].str.extract('-\s (.*);[^:] :[^:] :\s (.*)\s \(')
                          0        1
0             Johnny MELAVO  06H4155
1            Sonny CROCKETT  06H9155
2  Muhammed John ARKANDROID  4072W74
3      Novillos CURRAN-POCO  F14R917

Old answer

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...