How to convert datatype:object to float64 in python?
You can convert most of the columns by just calling convert_objects
:
In [36]:
df = df.convert_objects(convert_numeric=True)
df.dtypes
Out[36]:
Date object
WD int64
Manpower float64
2nd object
CTR object
2ndU float64
T1 int64
T2 int64
T3 int64
T4 float64
dtype: object
For column '2nd' and 'CTR' we can call the vectorised str
methods to replace the thousands separator and remove the '%' sign and then astype
to convert:
In [39]:
df['2nd'] = df['2nd'].str.replace(',','').astype(int)
df['CTR'] = df['CTR'].str.replace('%','').astype(np.float64)
df.dtypes
Out[39]:
Date object
WD int64
Manpower float64
2nd int32
CTR float64
2ndU float64
T1 int64
T2 int64
T3 int64
T4 object
dtype: object
In [40]:
df.head()
Out[40]:
Date WD Manpower 2nd CTR 2ndU T1 T2 T3 T4
0 2013/4/6 6 NaN 2645 5.27 0.29 407 533 454 368
1 2013/4/7 7 NaN 2118 5.89 0.31 257 659 583 369
2 2013/4/13 6 NaN 2470 5.38 0.29 354 531 473 383
3 2013/4/14 7 NaN 2033 6.77 0.37 396 748 681 458
4 2013/4/20 6 NaN 2690 5.38 0.29 361 528 541 381
Or you can do the string handling operations above without the call to astype
and then call convert_objects
to convert everything in one go.
UPDATE
Since version 0.17.0
convert_objects
is deprecated and there isn't a top-level function to do this so you need to do:
df.apply(lambda col:pd.to_numeric(col, errors='coerce'))
See the docs and this related question: pandas: to_numeric for multiple columns
convert_objects is deprecated.
For pandas >= 0.17.0, use pd.to_numeric
df["2nd"] = pd.to_numeric(df["2nd"])
I had this problem in a DataFrame (df
) created from an Excel-sheet with several internal header rows.
After cleaning out the internal header rows from df
, the columns' values were of "non-null object" type (DataFrame.info()
).
This code converted all numerical values of multiple columns to int64 and float64 in one go:
for i in range(0, len(df.columns)):
df.iloc[:,i] = pd.to_numeric(df.iloc[:,i], errors='ignore')
# errors='ignore' lets strings remain as 'non-null objects'