Reading a CSV file using pandas
read_csv() is one of the most common operations to create a dataframe from a CSV file.
While reading a file, you may get the “Pandas Error Tokenizing Data“. This mostly occurs due to the incorrect data in the CSV file.
You can solve python pandas error tokenizing data error by ignoring the offending lines using error_bad_lines=False.
In this tutorial, you’ll learn the cause and how to solve the error tokenizing data error.
If You’re in Hurry…
You can use the below code snippet to solve the tokenizing error. You can solve the error by ignoring the offending lines and suppressing errors.
import pandas as pd df = pd.read_csv('sample.csv', error_bad_lines=False, engine ='python') df
If You Want to Understand Details, Read on…
In this tutorial, you’ll learn the causes for the exception “Error Tokenizing Data” and how it can be solved.
Cause of the Problem
- CSV file has two header lines
- Different separator is used
\r– is a new line character and it is present in column names which makes subsequent column names to be read as next line
- Lines of the CSV files have inconsistent number of columns
In the case of invalid rows which has an inconsistent number of columns, you’ll see an error as
Expected 1 field in line 12, saw m. This means it expected only 1 field in the CSV file but it saw 12 values after tokenizing it. Hence, it doesn’t know how the tokenized values need to be handled. You can solve the errors by using one of the options below.
Finding the Problematic Line (Optional)
If you want to identify the line which is creating the problem while reading, you can use the below code snippet.
It uses the CSV reader. hence it is similar to the
import csv with open("sample.csv", 'rb') as file_obj: reader = csv.reader(file_obj) line_no = 1 try: for row in reader: line_no += 1 except Exception as e: print (("Error in the line number %d: %s %s" % (line_no, str(type(e)), e.message)))
Using Err_Bad_Lines Parameter
When there is insufficient data in any of the rows, the tokenizing error will occur.
You can skip such invalid rows by using the
err_bad_line parameter within the
This parameter controls what needs to be done when a bad line occurs in the file being read.
If it’s set to,
False– Errors will be suppressed for Invalid lines
True– Errors will be thrown for invalid lines
Use the below snippet to read the CSV file and ignore the invalid lines. Only a warning will be shown with the line number when there is an invalid lie found.
import pandas as pd df = pd.read_csv('sample.csv', error_bad_lines=False) df
In this case, the offending lines will be skipped and only the valid lines will be read from CSV and a dataframe will be created.
Using Python Engine
There are two engines supported in reading a CSV file.
C engine and
- Uses C language to parse the CSV file
- Cannot automatically detect the separator
- Doesn’t support skipping footer
- Slower when compared to C engine but its feature complete
- Uses Python language to parse the CSV file
- Doesn’t support
float_precision. Not required with Python
- Can automatically detect the separator
- Supports skipping footer
Using the python engine can solve the problems faced while parsing the files.
For example, When you try to parse large CSV files, you may face the Error tokenizing data. c error out of memory. Using the python engine can solve the memory issues while parsing such big CSV files using the
Use the below snippet to use the Python engine for reading the CSV file.
import pandas as pd df = pd.read_csv('sample.csv', engine='python', error_bad_lines=False) df
This is how you can use the python engine to parse the CSV file.
Optionally, this could also solve the error
Error tokenizing data. c error out of memory when parsing the big CSV files.
Using Proper Separator
CSV files can have different separators such as tab separator or any other special character such as
;. In this case, an error will be thrown when reading the CSV file, if the default C engine is used.
You can parse the file successfully by specifying the separator explicitly using the
As an alternative, you can also use the python engine which will automatically detect the separator and parse the file accordingly.
import pandas as pd df = pd.read_csv('sample.csv', sep='\t') df
This is how you can specify the separator explicitly which can solve the tokenizing errors while reading the CSV files.
Using Line Terminator
CSV file can contain
\r carriage return for separating the lines instead of the line separator
In this case, you’ll face
CParserError: Error tokenizing data. C error: Buffer overflow caught - possible malformed input file when the line contains the
\r instead on
You can solve this error by using the line terminator explicitly using the
df = pd.read_csv('sample.csv', lineterminator='\n')
This is how you can use the line terminator to parse the files with the terminator
CSV files can have incomplete headers which can cause tokenizing errors while parsing the file.
You can use
header=None to ignore the first line headers while reading the CSV files.
This will parse the CSV file without headers and create a data frame. You can also add headers to column names by adding
columns attribute to the
import pandas as pd df = pd.read_csv('sample.csv', header=None, error_bad_lines=False) df
This is how you can ignore the headers which are incomplete and cause problems while reading the file.
CSV files can have headers in more than one row. This can happen when data is grouped into different sections and each group is having a name and has columns in each section.
In this case, you can ignore such rows by using the
skiprows parameter. You can pass the number of rows to be skipped and the data will be read after skipping those number of rows.
Use the below snippet to skip the first two rows while reading the CSV file.
import pandas as pd df = pd.read_csv('sample.csv', header=None, skiprows=2, error_bad_lines=False) df
This is how you can skip or ignore the erroneous headers while reading the CSV file.
Reading As Lines and Separating
In a CSV file, you may have a different number of columns in each row. This can occur when some of the columns in the row are considered optional. You may need to parse such files without any problems during tokenizing.
In this case, you can read the file as lines and separate it later using the delimiter and create a dataframe out of it. This is helpful when you have varying lengths of rows.
In the below example, the file is read as lines by specifying the separator as a new line using
sep='\n'. Now the file will be tokenized on each new line and a single column will be available in the dataframe.
Next, you can split the lines using the separator or regex and create different columns out of it.
expand=True expands the split string into multiple columns.
Use the below snippet to read the file as lines and separate it using the separator.
import pandas as pd df = pd.read_csv('sample.csv', header=None, sep='\n') df = df.str.split('\s\|\s', expand=True) df
This is how you can read the file as lines and later separate it to avoid problems while parsing the lines with an inconsistent number of columns.
To summarize, you’ve learned the causes of the Python Pandas Error tokenizing data and the different methods to solve it in different scenarios.
Different Errors while tokenizing data are,
Error tokenizing data. C error: Buffer overflow caught - possible malformed input file
ParserError: Expected n fields in line x, saw m
Error tokenizing data. c error out of memory
Also learned the different engines available in the
read_csv() method to parse the CSV file and the advantages and disadvantages of it.
You’ve also learned when to use the different methods appropriately.
If you have any questions, comment below.
You May Also Like
- How to write pandas dataframe to CSV
- How To Read An Excel File In Pandas – With Examples
- How To Read Excel With Multiple Sheets In Pandas?
- How To Solve Truth Value Of A Series Is Ambiguous. Use A.Empty, A.Bool(), A.Item(), A.Any() Or A.All()?
- How to solve xlrd.biffh.XLRDError: Excel xlsx file; not supported Error?