My dataframe looks like this : enter image description here

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?

  • $\begingroup$ 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. $\endgroup$
    – Valentas
    Apr 12, 2020 at 14:15

1 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'])


    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')
        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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.