Extract substring from a string column python

Question

I have a dataframe like this:

name      link
apple    example1.com/dsa/es?id=2812168&width=1200/web/map&resize.html
banana.  example2.com/es?id=28132908&width=1220/web/map_resize.html
orange.  example3.com/es?id=3209908&width=1120/web&map_resize.html

Each name's ID is buried in the link, which may have different structure. However, I know that the pattern is 'id=' + 'what I want' + '&'

I wonder, is there a way to extract the id from link and put it back to the dataframe to get the following:

name      link
apple    2812168
banana.  28132908
orange.  3209908

I try to use this:

df['name'] = df['name'].str.extract(r'id=\s*([^\.]*)\s*\\&', expand=False)

but it returns a column with all nan

Also, there may be more than one & in the link

Anwarvic · Accepted Answer · 2020-06-14 14:59:13Z

2

I think Ids are always numbers, so this is somewhat cleaner:

df["link"] = df['link'].str.extract(r'id=(\d+)&', expand=False)
print(df)
#     name      link
#0   apple   2812168
#1  banana  28132908
#2  orange   3209908

answered Jun 14, 2020 at 14:59

Anwarvic

13.2k5 gold badges60 silver badges72 bronze badges

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

2 Comments

IceAloe Over a year ago

Thank you! Yes ID is always a number. This works perfectly in this context!

Anwarvic Over a year ago

Glad I could help :)

BENY · Accepted Answer · 2020-06-14 15:04:47Z

2

Let tri split

df['link'].str.split('id=').str[1].str.split('&').str[0]
0     2812168
1    28132908
2     3209908
Name: link, dtype: object

answered Jun 14, 2020 at 15:04

BENY

324k22 gold badges176 silver badges250 bronze badges

1 Comment

BENY Over a year ago

@Tian yw :-) happy coding ~

Erfan · Accepted Answer · 2020-06-14 15:54:52Z

2

We can make use of positive lookbehind and positive lookahead:

df['link'] = df['link'].str.extract('(?<=id\=)(.*?)(?=\&)')


      name      link
0    apple   2812168
1  banana.  28132908
2  orange.   3209908

Details:

(?<=id\=): positive lookbehind on id=
(.*): everything
(?=\&width): positive lookahead on &width

edited Jun 14, 2020 at 15:54

answered Jun 14, 2020 at 14:58

Erfan

43.3k10 gold badges75 silver badges86 bronze badges

3 Comments

IceAloe Over a year ago

Thank you! I should have mentioned that it is not always &width after the id. It doesn't seem to work if I use df['link'] = df['link'].str.extract('(?<=id\=)(.*)(?=\&)') Is there a way to get around this?

Erfan Over a year ago

Yes, by using a so called "non greedy" operator, notice the .*?, see edit. Be aware that the accepted solution will not work if the id at one point should contain alphanumeric values.

IceAloe Over a year ago

Thank you!! This makes a lot of sense!

Collectives™ on Stack Overflow

Extract substring from a string column python

3 Answers 3

2 Comments

1 Comment

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

3 Comments

Related