Extracting a ration in two column from a text column.

Question

I have a dataframe as:

df = pd.DataFrame({"id": [1,2,3,4,5],
                "text": ["This is a ratio of 13.4/10","Favorate rate of this id is 11/9","It may not be a good looking person. But he is vary popular (15/10)","Ratio is 12/10","very popular 17/10"],
                "name":["Joe","Adam","Sara","Jose","Bob"]})

and I want to extract the numbers into two columns to have below outcome:

df = pd.DataFrame({"id": [1,2,3,4,5],
                "text": ["This is a ratio of 13.4/10","Favorate rate of this id is 11/9","It may not be a good looking person. But he is vary popular (15/10)","Ratio is 12/10","very popular 17/10"],
                "name":["Joe","Adam","Sara","Jose","Bob"],
                "rating_nominator":[13.4,11,15,12,17],
                "rating_denominator":[10,9,10,10,10]})

Any help is appreciated.

user3483203 · Answer 1 · 2018-12-06T19:26:18.207

The general pattern you are looking to match is (some number)/(other number). Matching floating point numbers is not a simple task, and there are plenty of answers on SO that answer that question, so you can make use of that here.

A fairly robust expression, adapted from this question is ([+-]?(?:[0-9]*[.])?[0-9]+). You can use this along with Series.str.extract and f-strings:

fpr = r'([+-]?(?:[0-9]*[.])?[0-9]+)'

res = df.text.str.extract(fr'{fpr}\/{fpr}').astype(float)

      0     1
0  13.4  10.0
1  11.0   9.0
2  15.0  10.0
3  12.0  10.0
4  17.0  10.0

To assign this to your DataFrame:

df[['rating_nominator', 'rating_denominator']] = res

   id                                               text  name  rating_nominator  rating_denominator
0   1                         This is a ratio of 13.4/10   Joe              13.4                10.0
1   2                   Favorate rate of this id is 11/9  Adam              11.0                 9.0
2   3  It may not be a good looking person. But he is...  Sara              15.0                10.0
3   4                                     Ratio is 12/10  Jose              12.0                10.0
4   5                                 very popular 17/10   Bob              17.0                10.0

timgeb · Accepted Answer · 2018-12-07T06:19:01.793

You can use

df[['rating_nominator', 'rating_denominator']] = df['text'].str.extract('(-?\d+(?:\.\d+)?)/(-?\d+(?:\.\d+)?)').astype(float)

The regex (-?\d+(?:\.\d+)?)/(-?\d+(?:\.\d+)?) will capture integers or floating point numbers as the nominator or denominator.

(edit: the regex in this answer covers more cases. I made some assumptions, for example that you won't find unary + signs in your numbers.)

Demo:

>>> df
   id                  text
0   1  foo 14.12/10.123 bar
1   2                 10/12
2   3             13.4/14.5
3   4          -12.24/-13.5
4   5                1/-1.2
>>>
>>> df[['rating_nominator', 'rating_denominator']] = df['text'].str.extract('(-?\d+(?:\.\d+)?)/(-?\d+(?:\.\d+)?)').astype(float)
>>> df
   id                  text  rating_nominator  rating_denominator
0   1  foo 14.12/10.123 bar               14.12            10.123
1   2                 10/12               10.00            12.000
2   3             13.4/14.5               13.40            14.500
3   4          -12.24/-13.5              -12.24           -13.500
4   5                1/-1.2                1.00            -1.20

Extracting a ration in two column from a text column.

2 Answers2