Handling rows with 2 lines of data

Question

My dataframe looks like this :

there are some rows ( example : 297) where the "Price" column has two values ( Plugs and Quarts) , I have filled the Nans with the previous row since it belongs to the same Latin Name. However I was thinking of splitting the Price Column further into two columns with Names "Quarts" and "Plugs" and fill the amount, 0 if there is no Plugs found and same with Quarts.

Example :

Plugs | Quarts
0 | 2
2 | 3
4 | 0

Can someone help me with this?

Answer 1

The code below should achieve the desired results:

import pandas as pd
import numpy as np
import re

df2 = pd.DataFrame([[1, 'plugs: $3.00'], 
                    [4, np.NaN], 
                    [7, 'quarts: $3.00']], 
                    columns=['name', 'price'])

df2

    name          price
 0     1   plugs: $3.00
 1     4            NaN
 2     7  quarts: $3.00

def price(x):
    rprice = re.search('(plugs:|quarts:)\s*\$([\d\.]*)', x)
    if rprice == None:
        return ('','0')
    else:
        return rprice.groups()

df2.fillna("", inplace=True)
df2['price'].map(lambda x: price(x))
df2['Plugs'] = df2['price'].map(lambda  x: float(price(x)[1]) if price(x)[0] == 'plugs:' else 0)
df2['Quart'] = df2['price'].map(lambda  x: float(price(x)[1]) if price(x)[0] == 'quarts:' else 0)

df2 (with new columns below)

   name          price  Plugs  Quart
0     1   plugs: $3.00    3.0    0.0
1     4                   0.0    0.0
2     7  quarts: $3.00    0.0    3.0

Some Notes: I used regular expressions to extract the type and cost, and I replaced the NA with blank text.