How to extract specific value from excel column using python pandas dataframe

Question

Need to extract specific value from excel column using python pandas dataframe

The column Product that I am trying to extract looks the below & need to extract only Product # from it. The column also has other numbers but the Product # always comes after the term 'UK Pro' & Product # could be 3 to 4 digit number in a particular row of data.

In[1]:

df['Product'].head()

#Dataframe looks like this:

Out[1]:

Checking center : King 2000 : UK Pro 1000 : London

Checking center : Queen 321 : UK Pro 250 : Spain

CC : UK Pro 3000 : France

CC : UK Pro 810 : Poland

Expected Output:

Product #

1000

250

3000

810

Started with this:

df['Product #'] = df1['Product'].str.split(':').str[1]

But this does split only based on the first two occurrence of : operator.

Then tried this:

df1['Product #'] = df1['Product'].str.split('UK Pro', 1).str[0].str.strip()

Timeless · Accepted Answer · 2022-12-06 21:45:41Z

2

You can use pandas.Series.str.extract :

df["Product #"] = df["Product"].str.extract("UK Pro (\d+)", expand=False)

# Output :

print(df)
   Product #
0        NaN
1        NaN
2       1000
3        NaN
4        NaN
5        250
6        NaN
7       3000
8        NaN
9        810
10       NaN

answered Dec 6, 2022 at 21:45

Timeless

38.3k6 gold badges33 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to extract specific value from excel column using python pandas dataframe

1 Answer 1

# Output :

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

# Output :

Comments

Your Answer

Sign up or log in

Post as a guest

Related