[FIXED] Comma separated thousands and comma seperated prices

Issue

I have a column in my dataframe with 2 prices, one is the original price and the other is the discounted price and the 2 prices are separated by a comma and the pric itself has comma as a thousand separator. I want to split these 2 columns. Please, help.

Here is a sample data:

'$1,149.99,$1,249.99',
 '$124.99',
 '$549.95',
 '$149.00,$159.99'.

i tried this code:

data_phones[['actual_price', 'installment_price']] = data_phones['prices'].str.split(',', n=1, expand=True)```

And the result split every comma like: 1|149.99$1|249.99 Any help or suggestion?

Solution

Example

import pandas as pd
data = ['$1,149.99,$1,249.99', '$124.99', '$549.95', '$149.00,$159.99']
df = pd.DataFrame(data, columns=['colA'])

Code

use regex

out = df['colA'].str.split(",(?=\$)", expand=True)

out:

    0           1
0   $1,149.99   $1,249.99
1   $124.99     None
2   $549.95     None
3   $149.00     $159.99

Answered By - Panda Kim