Extract numerical data from product description

user_8075889 0
Viewed 32

I am working with python3.

I am trying to pull out numerical data from a product description. Sometimes however the same product has a differently worded description which results in conflicting results.

My code is:

import pandas as pd
import re
data = {'desc':['1 oz Silver Eagles Monster Box  (500 pc)', 'Silver Eagle Monster Box (500 pcs 1 oz coins)', '2021 10 oz Silver Royal Canadian Mint Bar'], 'inventory':['in stock', 'in stock', 'out of stock']}
df=pd.DataFrame(data)
df['ounces']=df['desc'].str.extract(r'(\d+ pc|\d+ oz)')
print(df)

What i get is:

                                            desc     inventory  ounces
0       1 oz Silver Eagles Monster Box  (500 pc)      in stock    1 oz
1  Silver Eagle Monster Box (500 pcs 1 oz coins)      in stock  500 pc
2      2021 10 oz Silver Royal Canadian Mint Bar  out of stock   10 oz

Clearly the first 2 items are the same. I expected regex to look for 'pc' first in the entire description and then if nothing found look for 'oz' but that is not what it does. What I need to get is:

                                            desc     inventory  ounces
0       1 oz Silver Eagles Monster Box  (500 pc)      in stock  500 pc
1  Silver Eagle Monster Box (500 pcs 1 oz coins)      in stock  500 pc
2      2021 10 oz Silver Royal Canadian Mint Bar  out of stock   10 oz

My original dataframe does not have ounces and i am trying to add that column and extract the correct data at the same time. Should I be going about this differently?

pandas python-3.x regex
1 Answers

You can use

>>> df['ounces'] = df['desc'].str.findall(r'(?:.*\D)?(\d+ pc)|(\d+ oz)').str[0].str.join('')
>>> df
                                            desc     inventory  ounces
0       1 oz Silver Eagles Monster Box  (500 pc)      in stock  500 pc
1  Silver Eagle Monster Box (500 pcs 1 oz coins)      in stock  500 pc
2      2021 10 oz Silver Royal Canadian Mint Bar  out of stock   10 oz

The (?:.*\D)?(\d+ pc)|(\d+ oz) pattern will give priority to the first capturing group that matches pc, and the oz part will only get matched if it occurs after the pc one.

See the regex demo.

Since Series.str.findall returns all pattern matches, .str[0] is required to obtain the first result only, and .str.join('') convert the tuple (as there are two groups in the pattern, findall returns list of tuples) into a string (since one of the group values will always be empty).

user_38329700
Related