topic Re: Extracting number w/ specialcharacter from random position in string field in Python Questions

Extracting number w/ specialcharacter from random position in string field

SimonLange — Fri, 22 Jan 2021 12:23:08 GMT

Hi all.

I am struggling with extracting a number in combination with a percentage sign (e.g 40 %, 60 %; 30 %, 30 % and 40 % and so on) from a field. The issue is that there is no regular position of said number - see example below - and that there are numbers which I dont want to extract.

Any ideas on how to extract the number-character combination? Thanks for any suggestions

Example fields:

V 40 %, B112 60 %

V 30 %, B11 30 %, K 40 %

S 132 90 %, R113-GB00BK 10 %

Re: Extracting number w/ specialcharacter from random position in string field

DavidPike — Fri, 22 Jan 2021 12:05:17 GMT

Which number do you want to extract in those examples? Can you give examples along with the intended result? There may be no regular position, but is there a regular format - do you want the numbers preceding the % sign?

Re: Extracting number w/ specialcharacter from random position in string field

SimonLange — Fri, 22 Jan 2021 12:28:55 GMT

Hi David - thanks for your response. I specified my question to which numbers I want to extract!

The codes are always separated by a comma - the codes themselfes vary though

Intended result would be a column with

V, B112

V, B11, K

S132, R113-GB00BK

and one with

40, 60

30, 30, 40

90, 10

Re: Extracting number w/ specialcharacter from random position in string field

DanPatterson — Fri, 22 Jan 2021 12:37:00 GMT

This will get you thinkin about what you want.

Currently it returns a list so you have to decide whether you want a particular value from the list (eg first, last etc) or whether you want to concatenate the values together (eg ", ".join([i for i in ... )

s = "S 132 90 %, R113-GB00BK 10 %" [i.strip().split(" ")[-1] for i in s.split("%") if i] ['90', '10'] s0 = "V 30 %, B11 30 %, K 40 %" [i.strip().split(" ")[-1] for i in s0.split("%") if i] ['30', '30', '40'] s1 = "V 40 %, B112 60 %" [i.strip().split(" ")[-1] for i in s1.split("%") if i] ['40', '60']

Re: Extracting number w/ specialcharacter from random position in string field

DanPatterson — Fri, 22 Jan 2021 12:50:12 GMT

", ".join([i.strip().split(" ")[-1] for i in s0.split("%") if i])
'30, 30, 40'

like so? See my post

Re: Extracting number w/ specialcharacter from random position in string field

jcarlson — Fri, 22 Jan 2021 15:03:56 GMT

If the data are consistently formatted, you might consider looking at regular expressions to accomplish this.

Here's my code:

import re values = [ 'V 40 %, B112 60 %', 'V 30 %, B11 30 %, K 40 %', 'S 132 90 %, R113-GB00BK 10 %' ] # Regex patterns other_patt = re.compile('\S+?(?=\s[0-9]+\s%)') percent_patt = re.compile('[0-9]+(?=\s%)') for value in values: print([re.findall(other_patt, value), re.findall(percent_patt, value)])

And here's what it returns:

[['V', 'B112'], ['40', '60']] [['V', 'B11', 'K'], ['30', '30', '40']] [['132', 'R113-GB00BK'], ['90', '10']]

Breaking down the regex patterns:

\S+?(?=\s[0-9]+\s%)
- \S+?
  - \S+ matches one or more non-whitespace characters.
  - The '?' makes it non-greedy, meaning it will match as little as possible, so that we don't inadvertently grab more than one value
- (?=...) Indicates a lookahead expression, meaning it specifically looks for strings which are followed by the value in the '...', but does not include that value in the returned match
- \s looks for a whitespace character
- [0-9]+ Looks for one or more consecutive numeric characters.
- % Just a literal '%' character!
[0-9]+(?=\s%)
- Again, [0-9]+ is looking for one or more consecutive numeric characters.
- (?=\s$) A simpler lookahead expression, this time looking only for those numeric characters followed by a single whitespace character and a percent sign

Regex is quite useful for extracting text, and is a module well worth digging into.

Re: Extracting number w/ specialcharacter from random position in string field

JoshuaBixby — Fri, 22 Jan 2021 21:48:22 GMT

The pattern does seem structured and simple enough that you might not need regular expressions, but I like regular expressions like @jcarlson.

>>> import re >>> >>> rows = [ ... r"V 40 %, B112 60 %", ... r"V 30 %, B11 30 %, K 40 %", ... r"S 132 90 %, R113-GB00BK 10 %" ... ] >>> >>> pct_pttn = re.compile("(\d*) %") >>> >>> # populuate column 1 >>> for s in rows: ... pct_pttn.sub("", s).strip() ... 'V , B112' 'V , B11 , K' 'S 132 , R113-GB00BK' >>> >>> # populate column 2 >>> for s in rows: ... ", ".join(pct_pttn.findall(s)) ... '40, 60' '30, 30, 40' '90, 10' >>>

Re: Extracting number w/ specialcharacter from random position in string field

SimonLange — Mon, 25 Jan 2021 12:43:03 GMT

Thanks very much - that helped a lot!