Python/Pandas: Why is float producing an extra space in my data?-CodePudding

So I'm trying to work with Pandas/excels and I'm running into an issue.

I have the following negative numbers in excel (this is how its been formatted/I just started a new job and trying to get running despite not knowing much Python). -It's listed as ($246.00) in the sheet, but shows as -246 in the box above in Excel)

Negative numbers here in Excel

Now, my code string looks like this in Jupyter notebook for this line:

apply(lambda x: float(str(x).replace(',',"").replace('(',"-").replace(')',"").replace('$',"")))

But I'm seeing the following error:
ValueError: could not convert string to float: '- 246.00'

Can anybody help me understand how to fix this? I've been drowning in code errors this week that weren't supposed to be happening on my first week.

CodePudding user response：

Here is a way you can take currency from an excel and turn it into a negative float

data = {
    'Column1' : ['($246.00)']
}
df = pd.DataFrame(data)
df['Column1'] = df['Column1'].replace( '[\$,)]','', regex=True ).replace( '[(]','-',   regex=True ).astype(float)

CodePudding user response：

Because there is a space in the result, replace that out with null. here is the solution

apply(lambda x: float(str(x).replace(',',"").replace('(',"-").replace(')',"").replace('$',"").replace(' ',"")))

CodePudding user response：

Try this:

apply(lambda x: -float(str(x).replace('($'," ").replace(')',""))

This makes the value negative after formatting the string, which is much simpler.