sign "-" after the number

Question

I have a dataframe containing a column with numbers like this:

 1. 34.5
 2. 345.2
 3. 898.4-
 4. 87.4

...

 n. 847.3-

The column is str, now I have written a code that cycles the whole column (about 19 million rows). I ask if anyone knows a faster method to bring the "-" in front and change the str to number

My code:

for i, row in frame.iterrows():
    Val=row[9]
    if row[9].find("-") > 0 :
        Val="-"+row[9].replace("-","")
        frame.iloc[i,9]=Val
frame[frame.columns[9]]=pd.to_numeric(frame[frame.columns[9]]

user_3545273 · Answer

You can do it directly with the help of str:

frame.loc[frame[col_name].str[-1] == '-', col_name] = '-' + frame.loc[
                    frame[col_name].str[-1] == '-', col_name].str.replace('-', '')
frame[col_name] = frame[col_name].astype('float')

user_9134698 · Answer

Check out the 'Applying function' section of this page which explains iterating over the entire frame will be slow and to instead use the apply() function

https://www.learndatasci.com/tutorials/python-pandas-tutorial-complete-introduction-for-beginners/

It would look something like this

def remove_hyphen(x):
    return x.replace("-", "")

frame["column_name"] = frame["column_name"].apply(remove_hyphen)

user_4333359 · Answer

Let's try two calls to pd.to_numeric. The first gets all of the positive numbers correct, the second will then fill in the negative numbers that were coerced to NaN on the first try. Non-numeric values are still coerced to NaN.

import pandas as pd
s = pd.Series(['34.5', '345.2', '898.4-', '87.4', '847.3-', 'foobar', 'f-'])

m = s.str[-1].eq('-')  # True for negative numbers

(pd.to_numeric(s, errors='coerce')
   .fillna(-1*pd.to_numeric(s.loc[m].str[:-1], errors='coerce'))
)

0     34.5
1    345.2
2   -898.4
3     87.4
4   -847.3
5      NaN
6      NaN
dtype: float64

sign "-" after the number

3 Answers