Pandas: Take all characters before the first digit appears

user_9403338 0
Viewed 121

Considering the dataframe below:

import pandas as pd

df = pd.DataFrame({'Ticker': ['EWZ US 05/29/20 P27', 'HSI US 12/30/20 C24800', 'TLT US 06/19/20 C225'],
                   'Market': ['US NYSE', 'US NYSE', 'HK HKSE']})
df

I need to create a new column with all the characters that appear before the first digit in df['Ticker']. I'm try the following:

numbers = [0,1,2,3,4,5,6,7,8,9]
f = lambda x: len(df['Ticker'].split(numbers)) -1
df['Reduced_Ticker'] = df.apply(f, axis=1)

but I receive the following error: 'Series' object has no attribute 'split'. Can anyone help? Output expected:

        Ticker         |  Market | Reduced_Ticker
EWZ US 05/29/20 P27    | US NYSE | EWZ US
HSI US 12/30/20 C24800 | US NYSE | HSI US
TLT US 06/19/20 C225   | HK HKSE | HK HKSE
pandas python
2 Answers

Try:

df['Reduced_Ticker'] = df['Ticker'].str.extract('^(\D+)')

Output (extra space at the end):

                   Ticker   Market Reduced_Ticker
0     EWZ US 05/29/20 P27  US NYSE        EWZ US 
1  HSI US 12/30/20 C24800  US NYSE        HSI US 
2    TLT US 06/19/20 C225  HK HKSE        TLT US

To remove the trailing space, chain with str.strip():

df['Ticker'].str.extract('^(\D+)')[0].str.strip()
user_42384080

Or, assuming what you provided is a full representation of your dataframe, you can do something with split() (on 2nd space) and join():

df['Reduced_Ticker'] = df['Ticker'].apply(lambda a :" ".join(a.split(" ", 2)[:2]))

                   Ticker   Market     new Reduced_Ticker
0     EWZ US 05/29/20 P27  US NYSE  EWZ US         EWZ US
1  HSI US 12/30/20 C24800  US NYSE  HSI US         HSI US
2    TLT US 06/19/20 C225  HK HKSE  TLT US         TLT US
user_91673820
Related