Pandas read_csv ignore dollar sign when parsing numbers

Question

I have a csv file with some cells that have dollar signs (e.g. $46.5). I am forcing all the types to be numpy.float64 in the function pandas.read_csv(). It complains about ValueError: could not convert string to float: $46.5. Is there a way to handle this cleanly?

You'd have to strip and cast as a post-processing step or pass a method to `converters` param for `read_csv` — EdChum, Mar 30 '16 at 21:47
Are you trying to use floating point numbers for currency? You probably should be using `decimal`: http://stackoverflow.com/questions/3730019/why-not-use-double-or-float-to-represent-currency — Alex Taylor, Mar 30 '16 at 21:53

score 12 · Answer 1 · answered Mar 30 '16 at 23:01

12

You can add a converter for the relevant column(s):

pd.DataFrame({'col1': ['$46.51', '$38.00', 40], 
              'col2': [1, 2, 3]}).to_csv('test_df.csv', index=False)

>>> pd.read_csv('test_df.csv', converters={'col1': lambda s: float(s.replace('$', ''))})
    col1  col2
0  46.51     1
1  38.00     2
2  40.00     3

answered Mar 30 '16 at 23:01

Alexander

105,104
32
201
196

4

You'll probably need to add the same logic for commas, or it will bomb trying to replace $1,437.22 – flyingmeatball Mar 31 '16 at 13:56
2

@flyingmeatball use the `thousands=','` kwarg. – Little Bobby Tables Mar 20 '18 at 17:52

Pandas read_csv ignore dollar sign when parsing numbers

1 Answers1

Linked