use multiple regex on a single column of dataframe in pandas

Question

I have a column named ingredients and it has multiple items in it. How do I seperate each of them into a different column?

Eg:    Type      Ingredients
       Hybrid    18.7% THC
                 1.62% Total Terpenes
                 0.61% Myrcene
       Indica    0.61% Myrcene
                 0.35% Ocimene
                 0.18% Limonene

I want to split ingredients column into multiple column as THC, Mycrene, Ocimene, Limonene etc with each column describing it's percentage

jezrael · Accepted Answer · 2020-05-04 07:19:10Z

2

I think you need Series.str.split by percentage with \s+ for one or more spaces, assign to new columns and then forward filling missing values for type column, last reshape by DataFrame.pivot:

df[['per','ingr']] = df['Ingredients'].str.split('%\s+', expand=True)
df['Type'] = df['Type'].replace('', np.nan).ffill()

df = df.pivot('Type','ingr','per').astype(float)
print (df)
ingr   Limonene Myrcene Ocimene   THC Total Terpenes
Type                                                
Hybrid      NaN    0.61     NaN  18.7           1.62
Indica     0.18    0.61    0.35   NaN            NaN

answered May 4, 2020 at 7:19

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

2 Comments

Helly Bhalodia Over a year ago

It didn't work for me. I think it's because it also has '\n'

jezrael Over a year ago

@HellyBhalodia - Can you create sample data by d = df.head().to_dict('list') ?

Collectives™ on Stack Overflow

use multiple regex on a single column of dataframe in pandas

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related