[python] Pandas read_csv low_memory and dtype options

When calling

df = pd.read_csv('somefile.csv')

I get:

/Users/josh/anaconda/envs/py27/lib/python2.7/site-packages/pandas/io/parsers.py:1130: DtypeWarning: Columns (4,5,7,16) have mixed types. Specify dtype option on import or set low_memory=False.

Why is the dtype option related to low_memory, and why would making it False help with this problem?

I had a similar issue with a ~400MB file. Setting low_memory=False did the trick for me. Do the simple things first,I would check that your dataframe isn't bigger than your system memory, reboot, clear the RAM before proceeding. If you're still running into errors, its worth making sure your .csv file is ok, take a quick look in Excel and make sure there's no obvious corruption. Broken original data can wreak havoc...

As mentioned earlier by firelynx if dtype is explicitly specified and there is mixed data that is not compatible with that dtype then loading will crash. I used a converter like this as a workaround to change the values with incompatible data type so that the data could still be loaded.

def conv(val):
    if not val:
        return 0    
    try:
        return np.float64(val)
    except:        
        return np.float64(0)

df = pd.read_csv(csv_file,converters={'COL_A':conv,'COL_B':conv})

I was facing a similar issue when processing a huge csv file (6 million rows). I had three issues:

1. the file contained strange characters (fixed using encoding)
2. the datatype was not specified (fixed using dtype property)
3. Using the above I still faced an issue which was related with the file_format that could not be defined based on the filename (fixed using try .. except..)

    df = pd.read_csv(csv_file,sep=';', encoding = 'ISO-8859-1',
                     names=['permission','owner_name','group_name','size','ctime','mtime','atime','filename','full_filename'],
                     dtype={'permission':str,'owner_name':str,'group_name':str,'size':str,'ctime':object,'mtime':object,'atime':object,'filename':str,'full_filename':str,'first_date':object,'last_date':object})
    
    try:
        df['file_format'] = [Path(f).suffix[1:] for f in df.filename.tolist()]
    except:
        df['file_format'] = ''

df = pd.read_csv('somefile.csv', low_memory=False)

This should solve the issue. I got exactly the same error, when reading 1.8M rows from a CSV.

As the error says, you should specify the datatypes when using the read_csv() method. So, you should write

file = pd.read_csv('example.csv', dtype='unicode')

Try:

dashboard_df = pd.read_csv(p_file, sep=',', error_bad_lines=False, index_col=False, dtype='unicode')

According to the pandas documentation:

dtype : Type name or dict of column -> type

As for low_memory, it's True by default and isn't yet documented. I don't think its relevant though. The error message is generic, so you shouldn't need to mess with low_memory anyway. Hope this helps and let me know if you have further problems

Sometimes, when all else fails, you just want to tell pandas to shut up about it:

# Ignore DtypeWarnings from pandas' read_csv                                                                                                                                                                                            
warnings.filterwarnings('ignore', message="^Columns.*")

According to the pandas documentation, specifying low_memory=False as long as the engine='c' (which is the default) is a reasonable solution to this problem.

If low_memory=False, then whole columns will be read in first, and then the proper types determined. For example, the column will be kept as objects (strings) as needed to preserve information.

If low_memory=True (the default), then pandas reads in the data in chunks of rows, then appends them together. Then some of the columns might look like chunks of integers and strings mixed up, depending on whether during the chunk pandas encountered anything that couldn't be cast to integer (say). This could cause problems later. The warning is telling you that this happened at least once in the read in, so you should be careful. Setting low_memory=False will use more memory but will avoid the problem.

Personally, I think low_memory=True is a bad default, but I work in an area that uses many more small datasets than large ones and so convenience is more important than efficiency.

The following code illustrates an example where low_memory=True is set and a column comes in with mixed types. It builds off the answer by @firelynx

import pandas as pd
try:
    from StringIO import StringIO
except ImportError:
    from io import StringIO

# make a big csv data file, following earlier approach by @firelynx
csvdata = """1,Alice
2,Bob
3,Caesar
"""

# we have to replicate the "integer column" user_id many many times to get
# pd.read_csv to actually chunk read. otherwise it just reads 
# the whole thing in one chunk, because it's faster, and we don't get any 
# "mixed dtype" issue. the 100000 below was chosen by experimentation.
csvdatafull = ""
for i in range(100000):
    csvdatafull = csvdatafull + csvdata
csvdatafull =  csvdatafull + "foobar,Cthlulu\n"
csvdatafull = "user_id,username\n" + csvdatafull

sio = StringIO(csvdatafull)
# the following line gives me the warning:
    # C:\Users\rdisa\anaconda3\lib\site-packages\IPython\core\interactiveshell.py:3072: DtypeWarning: Columns (0) have mixed types.Specify dtype option on import or set low_memory=False.
    # interactivity=interactivity, compiler=compiler, result=result)
# but it does not always give me the warning, so i guess the internal workings of read_csv depend on background factors
x = pd.read_csv(sio, low_memory=True) #, dtype={"user_id": int, "username": "string"})

x.dtypes
# this gives:
# Out[69]: 
# user_id     object
# username    object
# dtype: object

type(x['user_id'].iloc[0]) # int
type(x['user_id'].iloc[1]) # int
type(x['user_id'].iloc[2]) # int
type(x['user_id'].iloc[10000]) # int
type(x['user_id'].iloc[299999]) # str !!!! (even though it's a number! so this chunk must have been read in as strings)
type(x['user_id'].iloc[300000]) # str !!!!!

Aside: To give an example where this is a problem (and where I first encountered this as a serious issue), imagine you ran pd.read_csv() on a file then wanted to drop duplicates based on an identifier. Say the identifier is sometimes numeric, sometimes string. One row might be "81287", another might be "97324-32". Still, they are unique identifiers.

With low_memory=True, pandas might read in the identifier column like this:

81287
81287
81287
81287
81287
"81287"
"81287"
"81287"
"81287"
"97324-32"
"97324-32"
"97324-32"
"97324-32"
"97324-32"

Just because it chunks things and so, sometimes the identifier 81287 is a number, sometimes a string. When I try to drop duplicates based on this, well,

81287 == "81287"
Out[98]: False

It worked for me with low_memory = False while importing a DataFrame. That is all the change that worked for me:

df = pd.read_csv('export4_16.csv',low_memory=False)