I have a table with 118,000 rows and 80 columns. I would like to select 8 columns from the table. I am reading the file using the pandas function pd.read_csv command as:

df = pd.read_csv(filename, header=None, sep='|',

I would like to change the data type of each column inside of read_csv using dtype={'5': np.float, '37': np.float, ....}, but this does not work.

There is a message that column 5 has mixed types. The command print(df.dtypes) shows all columns of the type object. When I examine the column 5, I cannot see any problems. I have to change the data type for each column separately using pd.to_numeric.

My question is: Is there a way of setting data types inside read_csv and what is the problem in this case?


2 Answers 2


If you see the warning that your column has mixed types, but you only see numbers there, it could be that missing values are causing the problem.

In Pandas 1.0.0, a new function has been introduced to try to solve that problem. Namely, the Dataframe.convert_dtypes (docs).

You can use it like this:

df = pd.read_csv(filename, header=None, sep='|', usecols=[1,3,4,5,37,40,51,76])
df = df.convert_dtypes()

then check the type of the columns

  • $\begingroup$ Thanks for the new function. I solved this problem by converting columns datatype after importing them into df. I passed a list of columns I needed to convert into for loop. That worked. For example: col_list=['col1', 'col2', 'col5', etc...] and than using for loop for col in col_list: df[col]=pd.to_numeric(df[col], errors='coerce') Thanks $\endgroup$
    – Liliana
    Apr 23, 2020 at 11:00
  • $\begingroup$ @Liliana just be careful that you have errors='coerce' which could mean that you lose some data unwillingly. $\endgroup$ Apr 23, 2020 at 11:08
  • $\begingroup$ Thanks. Good idea to compare input and output files. $\endgroup$
    – Liliana
    Apr 28, 2020 at 10:59

You could try just using your own solution, replacing np.float:

dtype={'5': pd.to_numeric, '37': np.float, ....}

Or make a function that does what you want:

def convert(val):
        return np.float(val)
        return float(val)
        return pd.to_numeric(val)

    return val


dtype={'5': convert, '37': np.float, ....}

That is a bit exaggerated, but you get the idea :)


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.