Matching two identical groups of characters in pandas with some number of characters in between

user_10969548 0
Viewed 57

I'm trying to extract two numbers of interest from a string of docket text in a pandas dataframe. Here's an example with a couple of the idiosyncrasies that exist in the data

import pandas as pd

df = pd.DataFrame(["Fee: $ 15,732, and Expenses: $1,520.62."])

I used regexr to test some ideas and the closest I've been able to come up with is something along the lines of

df[0].str.extract("(\${0,2}\s*(\d+[,\.]*){1,5})")

Which returns:

           0    1
0   $15,732,,   732,,

The problems I'm running into are making characters optional while capturing the groups (i.e. I don't know how to get rid of the inner parenthesis because if I make it brackets then I get an error). And then ideally I'd be able to match the other set of numbers too.

I used regexr and while I can make regular expressions that match what I want, I'm struggling with the grouping part so that I can capture both while not needing to use a cumbersome function like apply with re.

There are sometimes numbers that show up again later in the report that include dates, other numbers, etc... So I'm trying to find a pretty controlled sequence (Can't get too liberal with the .*'s haha)

The string I ended up writing after the hint provided in the comments is:

\$((?:\d+(?:[,\.])*)+).*?\$((?:\d+(?:[,\.])*)+). The non-matching groups is what I hadn't understood before. I thought non-matching groups meant that it would somehow remove the parts that matched from the group but really what it means is that it's a group of characters that don't count as a group (not that they'll be removed from a group).

I appreciate the feedback I got this post!

pandas python regex
2 Answers

I am not sure if the text stays the same across all of the values but you can use the following regex:

r'Fee: \$\s?([\d,.]+), and Expenses:\s*\$\s?([\d,.]+)\.'

returning two matching groups:

15,732
1,520.62

You can also abstract the text:

r'\w+:\s*\$\s?([\d,.]+),(\s*\w+)+:\s*\$\s?([\d,.]+)\.'

with the same result.

user_65739020

You can use

df[0].str.extract(r"(\$\s*\d+(?:[,.]\d+)*)")    # To get the first value
df[0].str.extractall(r"(\$\s*\d+(?:[,.]\d+)*)") # To get all values
df[0].str.findall(r"\$\s*\d+(?:[,.]\d+)*")      # To get all values

The str.extract pattern is wrapped with a capturing group so that the method could return any value, it requires at least one capturing group in the regex pattern.

The regex matches

  • \$ - a $ char
  • \s* - zero or more whitespaces
  • \d+ - one or more digits
  • (?:[,.]\d+)* - a non-capturing group matching zero or more repetitions of a comma/dot and then one or more digits.

See the regex demo.

user_38329700
Related