How do I convert '$ -' from a string to a float using pandas?-CodePudding

I have some financial information that I've kept on an excel document for a while and I'd like to run some python code on it, but I'm having some issues converting the objects types to floats. The problem seems to be the '$ -'

This is how the data looks when loaded in:

import pandas as pd
dfData = {'Item': ['Product 1','Product 2','Product 3'],
        'Cost': [14.87,'-9.47','$ -']
       }
df = pd.DataFrame(dfData,columns=['Item','Cost'])
df

         Item   Cost
0   Product 1   14.87
1   Product 2   -9.47
2   Product 3   $ -

I've tried:

df['Cost'] = df['Cost'].str.replace('$','').str.replace(' ','').astype('float')

...as well as other similar str.replace commands, but I keep getting the following error:

ValueError: could not convert string to float: ''

This is my first stack overflow post, so go easy on me! I have looked all over for a solution, but for some reason can't find one addressing this specific problem. I can't replace the '-' either, because row 1 has it indicating a negative value.

CodePudding user response：

You don't need to chain str.replace, you can just use replace:

df['Cost'] = df['Cost'].replace({'\$': '', '-': '-0'}, regex=True).astype(float)
print(df)

# Output
        Item   Cost
0  Product 1  14.87
1  Product 2  -9.47
2  Product 3  -0.00