0
$\begingroup$

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='|',
                 usecols=[1,3,4,5,37,40,51,76])

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?

$\endgroup$
0
$\begingroup$

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

print(df.dtypes)
$\endgroup$
3
  • $\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 '20 at 11:00
  • $\begingroup$ @Liliana just be careful that you have errors='coerce' which could mean that you lose some data unwillingly. $\endgroup$ – Bruno Lubascher Apr 23 '20 at 11:08
  • $\begingroup$ Thanks. Good idea to compare input and output files. $\endgroup$ – Liliana Apr 28 '20 at 10:59
0
$\begingroup$

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):
    try:
        return np.float(val)
    except:
        return float(val)
    except:
        return pd.to_numeric(val)

    return val

Then:

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

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

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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