I have a dataset and most of the data is cleaned. However, I am having some trouble with extracting a portion of a string.

I am trying to access the first number in a string (which is a sales rank), however, the number includes commas and the number of digits varies. In addition, there are usually some characters before the number, but not always. Here is an example of the string:

[>#1,434,846 in Tools & Home Improvement #1,999,999 in electronics]

Sometimes the "[>#" does not appear in the front of the string though. Can anyone help with some quick code to extract the number?

Also, in the case where there are multiple numbers, I only want the first number, the "1,434,846" in this case.

CodePudding user response：

import pandas as pd
df['Col1'] = df['Col1'].astype(str).str.extract(r'([\d,] )')  # need to cast all types to str, then enclose regex pattern in capture group 

CodePudding user response：

I think this will answer your question and as Barmar points out [\d,] is the regexp you want.