Converting types in large pandas dataframe

lesem 0
Viewed 24

I have a pandas DataFrame of shape (350000, 910). All of the 910 columns have type object and small values like e.g. 1.0 or 2.0. I want them as type float64. This seemed not to be a difficult problem at all. However, once trying out different solutions like

df[all_cols] = df[all_cols].astype('float64')   # or 
df[all_cols] = df[all_cols].astype(np.float64)

I found that it takes 1 hour for not even 10% of the total number of columns.

Is there any way I can make this calculation significantly faster?

Example code:

import string
import pandas as pd
import numpy as np 

all_cols = [letter + str(x) for x in range(1, 36) for letter in string.ascii_uppercase]
df = pd.DataFrame(np.random.randint(0, 5, size=(300_000, len(all_cols))), columns=all_cols)
type-conversion dataframe pandas
1 Answers
  1. try pd.to_numeric:
df = df.apply(pd.to_numeric)

#to ignore errors, will return the input:
df = df.apply(pd.to_numeric, errors='ignore')

#to return NaN if invalid parsing (converts all non-digit strings to NaN)
df = df.apply(pd.to_numeric, errors='coerce')

#if you want to convert only some columns (not all columns):
cols = ['col1', 'col2', 'col3']
df[cols] = df[cols].apply(pd.to_numeric, errors='coerce', axis=1)
  1. or .astype:
df = df.astype(float) # or df.astype(np.float64)
  1. another ways to do it:
df = pd.DataFrame(df.values.astype(np.float64)) 

#or

df.apply(lambda x: x.astype(np.float64), axis=0)

benchmarking

df = pd.DataFrame({'A':np.random.random(100000).astype(str),'B':np.random.random(100000).astype(str),'C':np.random.random(100000).astype(str),'D':np.random.random(100000).astype(str)})

%timeit df.apply(pd.to_numeric)
# 205 ms ± 54.6 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%timeit df.astype(float)
# 233 ms ± 16.3 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%timeit df = pd.DataFrame(df.values.astype(np.float64))
# 221 ms ± 37 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%timeit df.apply(lambda x: x.astype(np.float64), axis=0)
# 219 ms ± 48.8 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
khaled koubaa0
Related