Replace non-numeric column values to float using pandas

Question

I am trying to remove all non-number values from a specific column using pandas:

(a) I want to change all the last column values to float. There are some non-zero numeric values in the column that I want to preserve as floats.

(b) If non-numeric values exist, I want to replace them all to 0.0 (float). For example, in line 247, the last column has "a", I would like to change it to 0.0.

I have tried:

def isnumber(x):
    try:
        float(x)
        return True
    except:
        return False

df = pd.read_csv(filename)
df = df[clean_up.applymap(isnumber)]

This however changes every column:

Corralien · Accepted Answer · 2021-11-21 14:27:52Z

2

First of all, you should read your csv file with header=None, skipinitialspace=True and quoting=1 as arguments of read_csv because your header looks like a normal row, you have space after each field separator and you have some values enclosed by double quotes.

To convert your last column, use pd.numeric:

df = pd.read_csv('tubedata.csv', header=None, quoting=1, skipinitialspace=True)
df[5] = pd.to_numeric(df[5], errors='coerce').fillna(0)

Output:

# Before conversion
>>> df[5].unique()
array(['0', '3', '2', '4', '6', 'a'], dtype=object)

# After conversion
>>> df[5].unique()
array([0., 3., 2., 4., 6.])

edited Nov 21, 2021 at 14:27

answered Nov 21, 2021 at 13:59

Corralien

121k8 gold badges44 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Corralien Over a year ago

Please share your csv file (or a sample), it will be simpler

user17014529 Over a year ago

because it needs iloc[:, -1] not iloc[-1]

Corralien Over a year ago

@MugheesAsif. Thanks to accepted my answer. Next time share your data sooner, it will be easier for everyone

Hirak Sarkar Over a year ago

Is it possible to do this to the entire dataframe?

Corralien Over a year ago

@HirakSarkar. df.apply(lambda x: pd.to_numeric(x, errors='coerce'))

rudolfovic · Accepted Answer · 2021-11-21 13:56:44Z

1

You can apply pd.to_numeric to every column by mapping it over the DataFrame. Using errors='coerce' turns columns that cannot be converted to NaN so they can be easily dropped in the following step:

df.apply(pd.to_numeric, errors='coerce').dropna(axis=1)

To allow for bad values (such as "a") within columns that are otherwise numeric, use fillna instead:

pd.to_numeric(df['some column'], errors='coerce').fillna(0.0)

edited Nov 21, 2021 at 13:56

answered Nov 21, 2021 at 13:46

rudolfovic

3,3162 gold badges17 silver badges44 bronze badges

2 Comments

Mughees Asif Over a year ago

This changes the full dataframe to one column? I only want to change the last column

Mughees Asif Over a year ago

I do not want to drop any columns, I am only interested in changing the last column to (a) all float, and (b) all non-numeric values to 0.0.

Collectives™ on Stack Overflow

Replace non-numeric column values to float using pandas

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related