Handling rows with 2 lines of data

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?

• Your price column format is not very convenient to work with, so you have to fix this as well. There are two common methods: either you repeat all other variables (not NaNs) in a new row, and use two separate columns, e.g., Type(='plugs' or 'quarts') and Price (only the number), or you introduce columns 'Price Plugs' (only the number) and 'Price Quarts' (only the number) as you proposed. If you can have different currencies you can add one more column for it. Apr 12 '20 at 14:15

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.