Efficiently converting strings to appropriate numeric types in pandas DataFrame

Question

I'm dealing with pandas DataFrames in which columns may contain strings representing numbers, may contain unexpected non-numeric characters, and the numbers represented by the strings may be of float or int type. For example, the DataFrame may contain something like "$625,452,242.95" for a float of 625452242.95 or "53.000.395" for an int equal to 53000395. The DataFrames are being read from a CSV file, and may be quite large.

What is the most efficient way to convert all such strings in a DataFrame to the appropriate numeric types? Thank you.

A person could infer that (perhaps not always reliably) from looking at the rest of the values in the CSV, or by reading the associated meta data. However, I'd like an approach that doesn't go that far, and only looks at each value individually, so I'd settle for treating a rightmost period as a decimal point, and anything to the left of that being treated as a comma would be in a numeric context. — Lamps1829
– Lamps1829, Commented Jul 16, 2013 at 3:55

waitingkuo · Accepted Answer · 2013-07-16 04:04:25Z

5

You can also try to replace those symbols and separator:

In [27]: df = pd.DataFrame([['$1,111'], ['$2,222']])

In [28]: df
Out[28]: 
        0
0  $1,111
1  $2,222

In [29]: df[0] = df[0].str.replace(r'[$,]', '').astype('float')

In [30]: df
Out[30]: 
      0
0  1111
1  2222

answered Jul 16, 2013 at 4:04

waitingkuo

94.5k28 gold badges119 silver badges122 bronze badges

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

Comments

Dan Allan · Accepted Answer · 2013-07-16 12:23:02Z

2

Refer to the read_csv documentation.

If all the thousands separators are decimals, use thousands='.'.
For a column with money, write a function that chops off the $ and converts the remaining string into an integer or a float. Pass it to read_csv via converters. (Again, see docs.)

I expect any custom converters will be slow -- read_csv is ruthlessly optimized in C -- so use built-in features (e.g., the thousands keyword) wherever possible.

edited Jul 16, 2013 at 12:23

answered Jul 16, 2013 at 3:50

Dan Allan

35.5k6 gold badges72 silver badges64 bronze badges

1 Comment

Andy Hayden Over a year ago

Warning: thousands sep doesn't work in 0.12 (and possibly before) github.com/pydata/pandas/issues/4322.

Collectives™ on Stack Overflow

Efficiently converting strings to appropriate numeric types in pandas DataFrame

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related