How to extract certain string out of long url kind of string in python using pandas

Question

How to use regex in pandas to extract below field. the below is one of my pandas dataframe column value, but i wanted to only extract 'eastus' and keep it as value for this field. how to filter this. this position is always fixed

Sample df:

                          correlationId                                                 id                                                                level   ...      status.value status.localizedValue            tag
0    xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx  /subscriptions/xxxxxxxxxxxxxxxxxxxxx/resourcegroups/xxxxxxxxxxxx/providers/Microsoft.RecoveryServices/locations/eastus/events/xxxxxxxxxxxx/ticks/xxxxxxxx  Informational  ...    Succeeded             Succeeded  Managed by IT

command i tried:

if not df.empty:
        columns = ["correlationId","eventName.value","id","resourceGroupName","resourceProviderName.value","operationName.value","status.value","eventTimestamp","submissionTimestamp"]        
        df.columns = df.columns.to_series().apply(lambda x: x.strip())
        #print(df.columns)    
        df.fillna('Missing', inplace=True)
        drop_these = ['correlationId']
        df['Location'] = df.id.str.split("/")[8]

but its not working

Error:

 df['Location'] = df.id.split("/")[8]
  File "C:\Python37\lib\site-packages\pandas\core\generic.py", line 5274, in __getattr__
    return object.__getattribute__(self, name)
AttributeError: 'Series' object has no attribute 'split'

any suggestion please

please share a sample of the dataframe, you are trying to set one value to the whole dataframe, is that what you are trying to achieve ? also what do you mean by not working ? what is happening exactly ? stackoverflow.com/help/how-to-ask — Jimmar
– Jimmar, Commented Aug 13, 2020 at 10:32
As it is, it should work. So maybe is a problem with your dataframe — Let's try
– Let's try, Commented Aug 13, 2020 at 10:34

user8560167 · Accepted Answer · 2020-08-13 10:57:05Z

1

id = '/subscriptions/xxxxxxxx/resourcegroups/xxxxxxxx/providers/Microsoft.RecoveryServices/' \
     'locations/eastus/events/xxxxxxx/ticks/xxxxx'
df = pd.DataFrame({
    'sample':[id]
})
df['Location'] = df['sample'].str.split("/",expand=True)[8]

print(df)
    sample                                                            Location
0  /subscriptions/xxxxxxxx/resourcegroups/xxxxxxxx/providers/Microsoft.RecoveryServices/locations/eastus/events/xxxxxxx/ticks/xxxxx   eastus

answered Aug 13, 2020 at 10:57

user8560167

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to extract certain string out of long url kind of string in python using pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related