How to extract a part of the string to another column

Question

I have a column that contains data like

Dummy data:

df = pd.DataFrame(["Lyreco A-Type small 2i",
"Lyreco C-Type small 4i",
"Lyreco N-Part medium", 
"Lyreco AKG MT 4i small",
"Lyreco AKG/ N-Type medium 4i",
"Lyreco C-Type medium 2i",
"Lyreco C-Type/ SNU medium 2i",
"Lyreco K-part small 4i",
"Lyreco K-Part medium", 
"Lyreco SNU small 2i",
"Lyreco C-Part large 2i",
"Lyreco N-Type large 4i"])

I want to create an extra column that strips the data and gives you the required part of the string(see below) in each row. The extracted column should look like this

Column_1                      Column_2
Lyreco A-Type small 2i         A-Type
Lyreco C-Type small 4i         C-Type
Lyreco N-Part medium           N-Part
Lyreco STU MT 4i small         STU MT
Lyreco AKG/ N-Type medium 4i   AKG/ N-Type
Lyreco C-Type medium 2i        C-Type
Lyreco C-Type/ SNU medium 2i   C-Type/ SNU
Lyreco K-part small 4i         K-part
Lyreco K-Part medium           K-Part
Lyreco SNU small 2i            SNU
Lyreco C-Part large 2i         C-Part
Lyreco N-Type large 4i         N-Type

How can I extract column 2 from the first column?

Answer 1

You might find that the following logic works with your data:

df["Column_2"] = df["Column_1"].str.extract(r'\w+ (\S+(?: \S+)*) \b(?:small|medium|large)\b')

The above pattern matches from the second term until reaching small, medium, or large keywords. Here is a working regex demo.