Data cleanup with Regex in Pandas

user_11496486 0
Viewed 40

I'm trying to use regex to extract two element fields.

From this list: df=pd.DataFrame({'Score':'Touch\n4.90\nDraw\n4.30\nDown\n1.58\nOver\n2.5\n1.65\nUnder\n2.5\n2.23']})

Desired result: 1.65\n2.23

As showing I've partially attempted to remove some unwanted bits but getting lost with the other. What do I need to add to my regex solution to achieve my desired result.

import pandas as pd

df=pd.DataFrame({'Score': ['Touch\n4.90\nDraw\n4.30\nDown\n1.58\nOver\n2.5\n1.65\nUnder\n2.5\n2.23']})

df = df['Score'].str.replace(r'([^\d\.\n])', '').str.strip()

df
0    4.90\n\n4.30\n\n1.58\n\n2.5\n1.65\n\n2.5\n2.23
Name: Score, dtype: object
pandas dataframe python-3.x regex
1 Answers

You can use

>>> df['Score'].str.findall(r'(?m)^(?:Over|Under)\n.*\n(.*)').str.join("\n")
0    1.65\n2.23
Name: Score, dtype: object

See the regex demo.

The regex matches all occurrences of second line below Over or Under line, and then joins the results with a newline.

Details

  • (?m) - re.M inline option
  • ^ - start of a line
  • (?:Over|Under) - one of the two strings
  • \n.*\n - a newline, any zero or more chars other than a line break char, as many as possible and then a newline
  • (.*) - Group 1: any zero or more chars other than a line break char, as many as possible.
user_38329700
Related