How to edit a column of data in CSV file using Pandas?

Question

In my CSV file, the % Return column is currently all strings. How can I remove the % symbol and make all the values a float?

Example of CSV file:

Date,Cash,Cash Interest,Margin Cost,Net Worth,% Return
9/16/2019,"$45.96 ",N/A,N/A,"$36.94 ",38.94%
9/13/2019,"$27.50 ",N/A,N/A,"$39.88 ",39.02%
9/12/2019,"$27.50 ",N/A,N/A,"$38.77 ",39.56%
9/11/2019,"$27.50 ",N/A,N/A,"$33.06 ",39.13%
9/10/2019,"$27.50 ",N/A,N/A,"$34.78 ",35.47%
9/9/2019,"$27.50 ",N/A,N/A,"$35.81 ",34.69%
9/6/2019,"$27.50 ",N/A,N/A,"$32.31 ",33.55%
9/5/2019,"$27.50 ",N/A,N/A,"$35.24 ",33.61%
9/4/2019,"$11.07 ",N/A,N/A,"$30.04 ",30.91%

please, share what you have tried - ![df['% Return'] = df['% Return'].str.extract('\d+.\d+').astype(float)] — Naga kiran
– Naga kiran, Commented Aug 6, 2020 at 8:44

Costa · Accepted Answer · 2020-08-06 08:46:22Z

1

You can define a function to convert percent values to float. Like this:

import pandas as pd

# define your function to convert
def percent_to_float(x):
    return float(x.strip('%'))/100

#read your csv file, 'col' is your column with percent values
df = pd.read_csv('data.csv', converters={'col':percent_to_float})

edited Aug 6, 2020 at 8:46

answered Aug 6, 2020 at 8:39

Costa

5024 silver badges6 bronze badges

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

3 Comments

Bruh Key Over a year ago

What is the purpose of 'col'?

Costa Over a year ago

Hey, I edited the answer. col is your column with percent values.

Bruh Key Over a year ago

Thank you so much, this worked. For some reason, the apply function wasn't working. Do you know why?

Collectives™ on Stack Overflow

How to edit a column of data in CSV file using Pandas?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related