0

I am trying to convert a string to a float in multiple columns. The string is enclosed in parentheses but I need to convert to a negative float in order to do some operations. 

Fund            Open            Activity    Gain (Loss) Close
Manager 1   10049217.19 -784717.34  237452.94   9501952.79
Manager 2   11035433.41 0           171126.7    11206560.11
Manager 3   26117736    0           326914.02   26444650.02
Manager 4   3682923.8   -260472.43  33999.86    3456451.23
Manager 5   0           17696136.53 143115.18   17839251.71
Manager 6   20036136.53 -20036136.53    0           0

'Open', 'Activity', and 'Close' are all objects

I have tried stack overflow and reg expressions but I still only get NaNs at the string I am trying to convert.

Here is the code I tried

cols = df1[['Open', 'Activity', 'Gain (Loss)', 'Close']] 
    for col in cols:
        df1[col].replace( '[)]','', regex=True ).replace( '[(]','-',   
        regex=True ).astype(float)
        df1[col] = pd.to_numeric(df1[col], errors='coerce')
print(df1)

When i run this code I still get NaN and it messes up my operations.

ncica
  • 7,015
  • 1
  • 15
  • 37
gaels67
  • 3
  • 2
  • what's your'e required output? – dondapati Jun 26 '19 at 13:40
  • Well, its probably not the problem but there's no need to use regex expressions, so you might as well simplify it and just replace '(' with '-' and ')' with '' – jwimberley Jun 26 '19 at 13:40
  • 2
    Your example input dataset does not appear to have a string with parentheses around it (other than a part of a column name), so I'm not sure where the actual problem is. – 9769953 Jun 26 '19 at 13:43
  • Could you share the exactly dataset? Would be easier to understand the problem. – Larissa Teixeira Jun 26 '19 at 13:46
  • Sorry, I am new here. How do I share the data set? I am reading into my program through pd.read_csv – gaels67 Jun 26 '19 at 13:53

1 Answers1

0

Apply a function to each element of the dataframe:

df = pd.DataFrame({'Open': ['12.34', '(56.78)']}) 
df.applymap(lambda x: -float(x.strip('()')) if '(' in x else float(x))
    Open
0  12.34
1 -56.78
Stef
  • 28,728
  • 2
  • 24
  • 52
  • I used this code df1.applymap(lambda x: -float(x.strip('()')) if '(' in x else float(x)) but I received the following error ("argument of type 'float' is not iterable", 'occurred at index Code') – gaels67 Jun 26 '19 at 16:46
  • I'm afraid I can't help you here without knowing your data, as others already mentioned in their comments above. [This answer](https://stackoverflow.com/a/20159305/3944322) explains how to do it. I gave you a minimal workable example as a starting point. – Stef Jun 26 '19 at 19:50