Convert an Object dtype column to Number Dtype in

Question

Trying to answer this question Get List of Unique String per Column we ran into a different problem from my dataset. When I import this CSV file to the dataframe every column is OBJECT type, we need to convert the columns that are just number to real (number) dtype and those that are not number to String dtype.

Is there a way to achieve this?

Download the data sample from here

I have tried following code from following article Pandas: change data type of columns but did not work.

df = pd.DataFrame(a, columns=['col1','col2','col3'])

As always thanks for your help

Answer 1

Option 1
use pd.to_numeric in an apply

df.apply(pd.to_numeric, errors='ignore')

Option 2
use pd.to_numeric on df.values.ravel

cvrtd = pd.to_numeric(df.values.ravel(), errors='coerce').reshape(-1, len(df.columns))
pd.DataFrame(np.where(np.isnan(cvrtd), df.values, cvrtd), df.index, df.columns)

---

Note
These are not exactly the same. For some column that contains mixed values, option 2 converts what it can while option 2 leaves everything in that column an object. Looking at your file, I'd choose option 1.

---

Timing

df = pd.read_csv('HistorianDataSample/HistorianDataSample.csv', skiprows=[1, 2])