Columns (0,1,3) have mixed types.Specify dtype option on import or set low_memory=False. When importing csv File

user_14605345 0
Viewed 2787

I m importing a csv file into python dataframe using pandas. PFA my code below:

import pandas as pd
df=pd.read_csv('C:/Users/Administrator/Desktop/NSE_Normalize.csv')

When I import using the code above no error is given but there is a warning as shown:

Columns (0,1,3) have mixed types.Specify dtype option on import or set low_memory=False.

By referring the answer Pandas read_csv low_memory and dtype options I came to know why this happens and that my file had ambiguous dtypes.

I was able to solve this by using:

df = pd.read_csv("C:/Users/Administrator/Desktop/NSE_Normalize.csv",sep=',', error_bad_lines=False, index_col=False, dtype='unicode') as mentioned on [Specify dtype option on import or set low_memory=False][2]

But when I import the same file as an excel workbook(.xlsx file). This error does not happen. Sure takes a larger time to get imported as compared to its csv counterpart but the error is not shown.

Hence from the above discussion may I know why the time to load an .xlsx file in python is larger than its .csv counterpart? Also when to use a .csv import and a .xlsx import?

Here is the file I import:

Excel File In both csv and xlsx format

pandas excel python
1 Answers

An excel file ".xlsx" file has all sorts of formatting / xml code that pandas has to "mince" through to get the data (consider all of the features that are available to transform and visualize data in excel that are not available to be saved as .csv which drops all features automatically upon saving). A ".csv" file on the other hand is extremely raw (like a .txt file), so pandas doesn't have to mince through all of this extra crazy stuff to get the data.

From this helpful link: see what the code for an "xml" file looks like (which is what ".xlsx" format is based off)

enter image description here

Look at what pandas has to go through just to get the data "A1", "B1", etc. As such, you should always strive to pull data from a .csv file if it meets all of your requirements. Any data type formatting calculations, etc. should try to be handled in pandas. I am specifically talking about reading in data here. enter image description here

In terms of why you were having issues, it is not possible to tell from your screenshots. A couple of things that can help in addition to trying to specify dtypes, low_memory, or parse_dates when reading:

df['numcol'] = pd.to_numeric(df['numcol'], errors='coerce')
df['datecol'] = pd.to_datetime(df['datecol'], errors='coerce')
df['datecol'] = pd.to_datetime(df['datecol'], dayfirst=True, errors='coerce') #UK / European dates
user_63667700
Related