Python Pandas Error tokenizing data

Question

I'm trying to use pandas to manipulate a .csv file but I get this error:

pandas.parser.CParserError: Error tokenizing data. C error: Expected 2 fields in line 3, saw 12

I have tried to read the pandas docs, but found nothing.

My code is simple:

path = 'GOOG Key Ratios.csv'
#print(open(path).read())
data = pd.read_csv(path)

How can I resolve this? Should I use the csv module or another language ?

File is from Morningstar

If this error arises when reading a file written by `pandas.to_csv()`, it MIGHT be because there is a '\r' in a column names, in which case to_csv() will actually write the subsequent column names into the first column of the data frame, causing a difference between the number of columns in the first X rows. This difference is one cause of the C error. — Sam Weisenthal, Jan 23 '17 at 00:56
Sometime just explicitly giving the "sep" parameter helps. Seems to be a parser issue. — gilgamash, May 23 '18 at 12:30
This error may arise also when you're using comma as a delimiter and you have more commas then expected (more fields in the error row then defined in the header). So you need to either remove the additional field or remove the extra comma if it's there by mistake. You can fix this manually and then you don't need to skip the error lines. — tsveti_iko, Aug 22 '18 at 09:44
Comment from gilgamash helped me. Open csv file in a text editor (like the windows editor or notepad++) so see which character is used for separation. If it's a semicolon e.g. try `pd.read_csv("", sep=";")`. Do not use Excel for checking as it sometimes puts the data into columns by default and therefore removes the separator. — Julian, Jun 19 '19 at 13:05
Regarding comment by @gilgamash -- this sent me in the right direction, however in my case it was resolved by explicitly *not* giving the "sep" parameter. — TheLoneDeranger, Aug 05 '19 at 23:41
Try using explicitly `lineterminator='\r'` or the right line terminator. — negas, Mar 04 '20 at 19:48
@tsveti_iko hey, how do you check if csv file has more than one comma as a delimiter? — haneulkim, Apr 17 '20 at 04:18
@Ambleu you can do it manually or read the file as normal txt file and print the line where the error it - something like this: ```f = open("your_file.csv", "r") print(f.readline())``` — tsveti_iko, Apr 18 '20 at 10:26
If the separator does not work, I also recommend you trying the parameter engine='python', which worked for me. The C parser had some kind of trouble with the type of report I was analyzing. — jasper, Jun 17 '20 at 13:15
had a similar issue. Realized it was due to my csv file having a value with a comma in it. Had to encapsulate it with " " — Josh Pachner, Apr 19 '21 at 19:54

score 980 · Accepted Answer · edited Apr 17 '22 at 22:48

980

you could also try;

data = pd.read_csv('file1.csv', on_bad_lines='skip')

Do note that this will cause the offending lines to be skipped.

Edit

For Pandas < 1.3.0 try

data = pd.read_csv("file1.csv", error_bad_lines=False)

as per pandas API reference.

edited Apr 17 '22 at 22:48

constantstranger

9,176
2
5
19

answered Aug 08 '13 at 14:47

richie

17,568
19
51
70

18

Stumbled on this answer, is there a way to fill missing columns on lines that outputs something like `expected 8 fields, saw 9`? – Petra Barus Sep 24 '14 at 10:11
43

The better solution is to investigate the offending file and to correct the bad lines so that they can be read by `read_csv`. @PetraBarus, why not just add columns to the CSV files that are missing them (with null values as needed)? – abcd Oct 06 '14 at 22:57
6

Yes, I just did that. It's much easier by adding columns. Opening CSV in a spreadsheet does this. – Petra Barus Oct 07 '14 at 02:17
2

There is a chance to get this error: CParserError: Error tokenizing data. C error: Buffer overflow caught - possible malformed input file. – MTT May 15 '17 at 02:48
It worked for me after I changed my engine from C to python. Any specific reason for that? – Aklank Jain May 18 '18 at 03:17
18

Passing in `names=["col1", "col2", ...]` for the max number of expected columns also works, and this is how I solved this issue when I came across it. See: https://stackoverflow.com/questions/18039057/python-pandas-error-tokenizing-data/54098017#54098017 – Steven Rouk Jan 08 '19 at 18:58
What if all the lines are the offending ones ? like if they have /n ? couldn't they be corrected before or while reading ? – FabioSpaghetti Jan 30 '19 at 13:21
In my case the file was .csv but the content was in html. LOL. So having a look in your file in first place as others pointed out is a good option to go. – Mr. Suryaa Jha Feb 21 '19 at 01:17
34

This should not be the accepted answer, lines will be skipped and you don't know why... – PV8 Dec 05 '19 at 13:44
I got to this question due to my header being on row 5. It appears 'header=' is part of this API and takes a list to handle multiple tables. – charo Feb 22 '21 at 23:17
1

@PV8, this is a perfectly fine answer for situations in which the file is malformed. Manually coercing the separator won't always work. – Tom Renish May 28 '21 at 14:57
3

with Python 3.9.7 and Pandas 1.2.0 this approach gives an error -> TypeError: read_csv() got an unexpected keyword argument 'on_bad_lines' – Robert Alexander Dec 19 '21 at 10:16
3

could also use on_bad_lines='warn' if you want to investigate the bad lines. it'll still skip them for processing like 'skip', but will allow you to see the bad lines. – kg1313 Jan 24 '22 at 19:47

score 206 · Answer 2 · edited Oct 05 '21 at 20:05

It might be an issue with

the delimiters in your data
the first row, as @TomAugspurger noted

To solve it, try specifying the sep and/or header arguments when calling read_csv. For instance,

df = pandas.read_csv(filepath, sep='delimiter', header=None)

In the code above, sep defines your delimiter and header=None tells pandas that your source data has no row for headers / column titles. Thus saith the docs: "If file contains no header row, then you should explicitly pass header=None". In this instance, pandas automatically creates whole-number indices for each field {0,1,2,...}.

According to the docs, the delimiter thing should not be an issue. The docs say that "if sep is None [not specified], will try to automatically determine this." I however have not had good luck with this, including instances with obvious delimiters.

Another solution may be to try auto detect the delimiter

# use the first 2 lines of the file to detect separator
temp_lines = csv_file.readline() + '\n' + csv_file.readline()
dialect = csv.Sniffer().sniff(temp_lines, delimiters=';,')

# remember to go back to the start of the file for the next time it's read
csv_file.seek(0) 

df = pd.read_csv(csv_file, sep=dialect.delimiter)

I experienced the same issue as OP and solved it by specifying `delimiter=";"` — KasperGL, Aug 25 '22 at 14:40
@KasperGL yup, `delimiter` is an alias for `sep` (source: https://pandas.pydata.org/pandas-docs/version/1.4/reference/api/pandas.read_csv.html) — william_grisaitis, Aug 27 '22 at 21:29

score 69 · Answer 3 · answered Aug 04 '13 at 02:24

The parser is getting confused by the header of the file. It reads the first row and infers the number of columns from that row. But the first two rows aren't representative of the actual data in the file.

Try it with data = pd.read_csv(path, skiprows=2)

score 68 · Answer 4 · edited Jun 01 '17 at 13:31

68

This is definitely an issue of delimiter, as most of the csv CSV are got create using sep='/t' so try to read_csv using the tab character (\t) using separator /t. so, try to open using following code line.

data=pd.read_csv("File_path", sep='\t')

edited Jun 01 '17 at 13:31

Lucas

6,869
5
29
44

answered Apr 01 '15 at 05:42

Piyush S. Wanare

4,703
6
37
54

6

@MichaelQueue : This is incorrect. A CSV, although commonly delimited by a comma, may be delimited by other characters as well. See [CSV specifications](https://en.wikipedia.org/wiki/Comma-separated_values#Specification). It may be a comma, a tab ('\t'), semicolon, and possibly additional spaces. :) – DJGrandpaJ Apr 13 '16 at 19:54
1

in my case it was a separator issue. read_csv apparently defaults to commas, and i have text fields which include commas (and the data was stored with a different separator anyway) – user108569 Jul 17 '18 at 16:41
If commas are used in the values but tab is the delimiter and sep is not used (or as suggested above the delimiters whatever it is assumed to be occurs in the values) then this error will arise. Make sure that the delimiter does not occur in any of the values else some rows will appear to have the incorrect number of columns – demongolem Mar 11 '20 at 11:10
1

I'm using excel 2016 while creating the CSV, and using sep=';' work for me – greendino Mar 20 '20 at 06:34

score 46 · Answer 5 · answered Jan 08 '19 at 18:57

46

I had this problem, where I was trying to read in a CSV without passing in column names.

df = pd.read_csv(filename, header=None)

I specified the column names in a list beforehand and then pass them into names, and it solved it immediately. If you don't have set column names, you could just create as many placeholder names as the maximum number of columns that might be in your data.

col_names = ["col1", "col2", "col3", ...]
df = pd.read_csv(filename, names=col_names)

answered Jan 08 '19 at 18:57

Steven Rouk

893
7
9

2

This answer better because the row doesn't get deleted compared to if using the error_bad_line=False. Additionally, you can easily figure out which lines were the problem ones once making a dataframe from this solution. – zipline86 Mar 27 '20 at 21:47
I agree with @zipline86. This answer is safe and intelligent. – Monica Heddneck Apr 23 '20 at 00:09
this solution its too hackish to me, but it works. I solved my issue passing engine='python' in read_csv to deal with variable columns size – Savrige Sep 18 '20 at 15:49
1

What if you had fewer column names than what the row has? (e.g. the row has 10 columns but you wrote three column names? How would you automatically add more columns in addition to the already specified column names? – GrrHackPrecioussss Dec 27 '22 at 21:31

score 42 · Answer 6 · edited Sep 20 '17 at 00:53

42

Your CSV file might have variable number of columns and read_csv inferred the number of columns from the first few rows. Two ways to solve it in this case:

1) Change the CSV file to have a dummy first line with max number of columns (and specify header=[0])

2) Or use names = list(range(0,N)) where N is the max number of columns.

edited Sep 20 '17 at 00:53

Ajean

5,528
14
46
69

answered Mar 31 '17 at 16:29

computerist

872
8
9

`names=range(N)` should suffice (using `pandas=1.1.2` here) – rvf Dec 03 '20 at 09:11

score 28 · Answer 7 · answered Feb 04 '16 at 22:16

I had this problem as well but perhaps for a different reason. I had some trailing commas in my CSV that were adding an additional column that pandas was attempting to read. Using the following works but it simply ignores the bad lines:

data = pd.read_csv('file1.csv', error_bad_lines=False)

If you want to keep the lines an ugly kind of hack for handling the errors is to do something like the following:

line     = []
expected = []
saw      = []     
cont     = True 

while cont == True:     
    try:
        data = pd.read_csv('file1.csv',skiprows=line)
        cont = False
    except Exception as e:    
        errortype = e.message.split('.')[0].strip()                                
        if errortype == 'Error tokenizing data':                        
           cerror      = e.message.split(':')[1].strip().replace(',','')
           nums        = [n for n in cerror.split(' ') if str.isdigit(n)]
           expected.append(int(nums[0]))
           saw.append(int(nums[2]))
           line.append(int(nums[1])-1)
         else:
           cerror      = 'Unknown'
           print 'Unknown Error - 222'

if line != []:
    # Handle the errors however you want

I proceeded to write a script to reinsert the lines into the DataFrame since the bad lines will be given by the variable 'line' in the above code. This can all be avoided by simply using the csv reader. Hopefully the pandas developers can make it easier to deal with this situation in the future.

Thank you for this solution !! It's a very useful tip. – WangSung Aug 09 '21 at 10:12 — WangSung, Aug 09 '21 at 10:12

score 18 · Answer 8 · edited Sep 08 '20 at 18:03

18

You can try;

data = pd.read_csv('file1.csv', sep='\t')

edited Sep 08 '20 at 18:03

double-beep

5,031
17
33
41

answered Sep 08 '20 at 15:58

Manodhya Opallage

301
2
9

3

While this code may solve the question, [including an explanation](//meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply. [From Review](/review/late-answers/27117963) – double-beep Sep 08 '20 at 18:04

score 17 · Answer 9 · answered Aug 20 '19 at 09:37

17

The following worked for me (I posted this answer, because I specifically had this problem in a Google Colaboratory Notebook):

df = pd.read_csv("/path/foo.csv", delimiter=';', skiprows=0, low_memory=False)

answered Aug 20 '19 at 09:37

d_-

1,391
2
19
37

1

I experimented problems when not setting `|` as the delimiter for my .csv. I rather to try this approach first, instead of skipping lines, or bad lines. – ivanleoncz Sep 02 '19 at 16:16
I also had the same problem, I assumed "\t" would be detected as a delimiter by default. It worked when I explicitly set the delimiter to "\t". – Rahul Jha Sep 20 '19 at 17:39
I had the same problem for a large .csv file (~250MB), with some corrupted lines spanning less columns than the data frame actually has. I was able to avoid the exception in two ways: 1) By modifying (for example deleting) a couple of *unrelated* rows far away from the line causing the exception. 2) By setting `low_memory=False`. In other .csv files with the same type of mal-formatted lines, I don't observe any problems. In summary, this indicates that the handling of large-file by `pandas.read_csv()` somehow is flawed. – normanius Mar 23 '21 at 13:11
I filed a [bug report](https://github.com/pandas-dev/pandas/issues/40587) related to my previous comment. – normanius Mar 23 '21 at 15:28

Legend_Ari · Answer 10 · 2017-07-07T09:32:01.953

I came across the same issue. Using pd.read_table() on the same source file seemed to work. I could not trace the reason for this but it was a useful workaround for my case. Perhaps someone more knowledgeable can shed more light on why it worked.

Edit: I found that this error creeps up when you have some text in your file that does not have the same format as the actual data. This is usually header or footer information (greater than one line, so skip_header doesn't work) which will not be separated by the same number of commas as your actual data (when using read_csv). Using read_table uses a tab as the delimiter which could circumvent the users current error but introduce others.

I usually get around this by reading the extra data into a file then use the read_csv() method.

The exact solution might differ depending on your actual file, but this approach has worked for me in several cases

score 13 · Answer 11 · answered Jul 07 '16 at 17:22

I've had this problem a few times myself. Almost every time, the reason is that the file I was attempting to open was not a properly saved CSV to begin with. And by "properly", I mean each row had the same number of separators or columns.

Typically it happened because I had opened the CSV in Excel then improperly saved it. Even though the file extension was still .csv, the pure CSV format had been altered.

Any file saved with pandas to_csv will be properly formatted and shouldn't have that issue. But if you open it with another program, it may change the structure.

Hope that helps.

score 9 · Answer 12 · answered Jun 26 '19 at 19:09

9

The dataset that I used had a lot of quote marks (") used extraneous of the formatting. I was able to fix the error by including this parameter for read_csv():

quoting=3 # 3 correlates to csv.QUOTE_NONE for pandas

answered Jun 26 '19 at 19:09

3

stumbled across the exact same thing. As far as I'm concerned, this is the correct answer. The accepted one just hides the error. – lhk Aug 18 '19 at 13:40
1

Correct answer for me too. +1 – Taha Jirjees Jun 04 '20 at 11:11
This solved my issue too, I had to fight with it for a long time, all the other answers did not help at all – Orsinus Jul 11 '23 at 12:53

score 9 · Answer 13 · answered Feb 16 '20 at 09:58

Error tokenizing data. C error: Expected 2 fields in line 3, saw 12

The error gives a clue to solve the problem " Expected 2 fields in line 3, saw 12", saw 12 means length of the second row is 12 and first row is 2.

When you have data like the one shown below, if you skip rows then most of the data will be skipped

data = """1,2,3
1,2,3,4
1,2,3,4,5
1,2
1,2,3,4"""

If you dont want to skip any rows do the following

#First lets find the maximum column for all the rows
with open("file_name.csv", 'r') as temp_f:
    # get No of columns in each line
    col_count = [ len(l.split(",")) for l in temp_f.readlines() ]

### Generate column names  (names will be 0, 1, 2, ..., maximum columns - 1)
column_names = [i for i in range(max(col_count))] 

import pandas as pd
# inside range set the maximum value you can see in "Expected 4 fields in line 2, saw 8"
# here will be 8 
data = pd.read_csv("file_name.csv",header = None,names=column_names )

Use range instead of manually setting names as it will be cumbersome when you have many columns.

Additionally you can fill up the NaN values with 0, if you need to use even data length. Eg. for clustering (k-means)

new_data = data.fillna(0)

lotrus28 · Answer 14 · 2017-04-25T15:00:01.940

I've had a similar problem while trying to read a tab-delimited table with spaces, commas and quotes:

1115794 4218    "k__Bacteria", "p__Firmicutes", "c__Bacilli", "o__Bacillales", "f__Bacillaceae", ""
1144102 3180    "k__Bacteria", "p__Firmicutes", "c__Bacilli", "o__Bacillales", "f__Bacillaceae", "g__Bacillus", ""
368444  2328    "k__Bacteria", "p__Bacteroidetes", "c__Bacteroidia", "o__Bacteroidales", "f__Bacteroidaceae", "g__Bacteroides", ""



import pandas as pd
# Same error for read_table
counts = pd.read_csv(path_counts, sep='\t', index_col=2, header=None, engine = 'c')

pandas.io.common.CParserError: Error tokenizing data. C error: out of memory

This says it has something to do with C parsing engine (which is the default one). Maybe changing to a python one will change anything

counts = pd.read_table(path_counts, sep='\t', index_col=2, header=None, engine='python')

Segmentation fault (core dumped)

Now that is a different error.
If we go ahead and try to remove spaces from the table, the error from python-engine changes once again:

1115794 4218    "k__Bacteria","p__Firmicutes","c__Bacilli","o__Bacillales","f__Bacillaceae",""
1144102 3180    "k__Bacteria","p__Firmicutes","c__Bacilli","o__Bacillales","f__Bacillaceae","g__Bacillus",""
368444  2328    "k__Bacteria","p__Bacteroidetes","c__Bacteroidia","o__Bacteroidales","f__Bacteroidaceae","g__Bacteroides",""


_csv.Error: '   ' expected after '"'

And it gets clear that pandas was having problems parsing our rows. To parse a table with python engine I needed to remove all spaces and quotes from the table beforehand. Meanwhile C-engine kept crashing even with commas in rows.

To avoid creating a new file with replacements I did this, as my tables are small:

from io import StringIO
with open(path_counts) as f:
    input = StringIO(f.read().replace('", ""', '').replace('"', '').replace(', ', ',').replace('\0',''))
    counts = pd.read_table(input, sep='\t', index_col=2, header=None, engine='python')

tl;dr
Change parsing engine, try to avoid any non-delimiting quotes/commas/spaces in your data.

score 8 · Answer 15 · answered Nov 21 '18 at 13:03

8

Use delimiter in parameter

pd.read_csv(filename, delimiter=",", encoding='utf-8')

It will read.

answered Nov 21 '18 at 13:03

Bhavesh Kumar

116
1
9

KareemJ · Answer 16 · 2019-11-03T10:10:42.047

As far as I can tell, and after taking a look at your file, the problem is that the csv file you're trying to load has multiple tables. There are empty lines, or lines that contain table titles. Try to have a look at this Stackoverflow answer. It shows how to achieve that programmatically.

Another dynamic approach to do that would be to use the csv module, read every single row at a time and make sanity checks/regular expressions, to infer if the row is (title/header/values/blank). You have one more advantage with this approach, that you can split/append/collect your data in python objects as desired.

The easiest of all would be to use pandas function pd.read_clipboard() after manually selecting and copying the table to the clipboard, in case you can open the csv in excel or something.

Irrelevant:

Additionally, irrelevant to your problem, but because no one made mention of this: I had this same issue when loading some datasets such as seeds_dataset.txt from UCI. In my case, the error was occurring because some separators had more whitespaces than a true tab \t. See line 3 in the following for instance

14.38   14.21   0.8951  5.386   3.312   2.462   4.956   1
14.69   14.49   0.8799  5.563   3.259   3.586   5.219   1
14.11   14.1    0.8911  5.42    3.302   2.7     5       1

Therefore, use \t+ in the separator pattern instead of \t.

data = pd.read_csv(path, sep='\t+`, header=None)

Thanks, delimiter="\t+" solved the error for me! – Sachin G Dec 09 '21 at 03:37 — Sachin G, Dec 09 '21 at 03:37

score 6 · Answer 17 · edited Oct 14 '19 at 15:11

6

For those who are having similar issue with Python 3 on linux OS.

pandas.errors.ParserError: Error tokenizing data. C error: Calling
read(nbytes) on source failed. Try engine='python'.

Try:

df.read_csv('file.csv', encoding='utf8', engine='python')

edited Oct 14 '19 at 15:11

double-beep

5,031
17
33
41

answered Oct 14 '19 at 14:54

Zstack

4,046
1
19
22

1

I had a file where there were commas in some certain fields/columns and while trying to read through pandas read_csv() it was failing, but after specifying engine="python" within read_csv() as a parameter it worked - Thanks for this! – Mohamed Niyaz Sirajudeen Jan 09 '22 at 19:30
This results in more rows than intended for me.. – Matt Yoon Sep 05 '22 at 19:08

Mihai.Mehe · Answer 18 · 2020-05-04T18:37:17.450

5

In my case the separator was not the default "," but Tab.

pd.read_csv(file_name.csv, sep='\\t',lineterminator='\\r', engine='python', header='infer')

Note: "\t" did not work as suggested by some sources. "\\t" was required.

edited May 04 '20 at 18:37

answered May 04 '20 at 18:27

Mihai.Mehe

448
8
13

score 5 · Answer 19 · answered May 13 '20 at 06:41

I believe the solutions,

,engine='python'
, error_bad_lines = False

will be good if it is dummy columns and you want to delete it. In my case, the second row really had more columns and I wanted those columns to be integrated and to have the number of columns = MAX(columns).

Please refer to the solution below that I could not read anywhere:

try:
    df_data = pd.read_csv(PATH, header = bl_header, sep = str_sep)
except pd.errors.ParserError as err:
    str_find = 'saw '
    int_position = int(str(err).find(str_find)) + len(str_find)
    str_nbCol = str(err)[int_position:]
    l_col = range(int(str_nbCol))
    df_data = pd.read_csv(PATH, header = bl_header, sep = str_sep, names = l_col)

I will take any better way to find the number of columns in the error message than what i just did — Laurent T, May 13 '20 at 06:45

score 4 · Answer 20 · answered Oct 03 '16 at 15:45

4

Although not the case for this question, this error may also appear with compressed data. Explicitly setting the value for kwarg compression resolved my problem.

result = pandas.read_csv(data_source, compression='gzip')

answered Oct 03 '16 at 15:45

RegularlyScheduledProgramming

1,347
1
11
27

score 4 · Answer 21 · answered Sep 07 '19 at 01:53

4

Simple resolution: Open the csv file in excel & save it with different name file of csv format. Again try importing it spyder, Your problem will be resolved!

answered Sep 07 '19 at 01:53

Naseer

127
10

1

Dude! Thank you. Your solution worked like a light switch. – Jon Fillip Sep 30 '21 at 19:23

score 4 · Answer 22 · answered Dec 14 '21 at 06:25

I came across multiple solutions for this issue. Lot's of folks have given the best explanation for the answers also. But for the beginners I think below two methods will be enough :

import pandas as pd

#Method 1

data = pd.read_csv('file1.csv', error_bad_lines=False)
#Note that this will cause the offending lines to be skipped.

#Method 2 using sep

data = pd.read_csv('file1.csv', sep='\t')

score 3 · Answer 23 · edited Nov 15 '17 at 12:13

3

Sometimes the problem is not how to use python, but with the raw data.
I got this error message

Error tokenizing data. C error: Expected 18 fields in line 72, saw 19.

It turned out that in the column description there were sometimes commas. This means that the CSV file needs to be cleaned up or another separator used.

edited Nov 15 '17 at 12:13

Aks4125

4,522
4
32
48

answered Nov 15 '17 at 10:59

Kims Sifers

31
1

score 3 · Answer 24 · answered Jan 26 '18 at 20:54

An alternative that I have found to be useful in dealing with similar parsing errors uses the CSV module to re-route data into a pandas df. For example:

import csv
import pandas as pd
path = 'C:/FileLocation/'
file = 'filename.csv'
f = open(path+file,'rt')
reader = csv.reader(f)

#once contents are available, I then put them in a list
csv_list = []
for l in reader:
    csv_list.append(l)
f.close()
#now pandas has no problem getting into a df
df = pd.DataFrame(csv_list)

I find the CSV module to be a bit more robust to poorly formatted comma separated files and so have had success with this route to address issues like these.

score 3 · Answer 25 · answered May 23 '18 at 11:45

following sequence of commands works (I lose the first line of the data -no header=None present-, but at least it loads):

df = pd.read_csv(filename, usecols=range(0, 42)) df.columns = ['YR', 'MO', 'DAY', 'HR', 'MIN', 'SEC', 'HUND', 'ERROR', 'RECTYPE', 'LANE', 'SPEED', 'CLASS', 'LENGTH', 'GVW', 'ESAL', 'W1', 'S1', 'W2', 'S2', 'W3', 'S3', 'W4', 'S4', 'W5', 'S5', 'W6', 'S6', 'W7', 'S7', 'W8', 'S8', 'W9', 'S9', 'W10', 'S10', 'W11', 'S11', 'W12', 'S12', 'W13', 'S13', 'W14']

Following does NOT work:

df = pd.read_csv(filename, names=['YR', 'MO', 'DAY', 'HR', 'MIN', 'SEC', 'HUND', 'ERROR', 'RECTYPE', 'LANE', 'SPEED', 'CLASS', 'LENGTH', 'GVW', 'ESAL', 'W1', 'S1', 'W2', 'S2', 'W3', 'S3', 'W4', 'S4', 'W5', 'S5', 'W6', 'S6', 'W7', 'S7', 'W8', 'S8', 'W9', 'S9', 'W10', 'S10', 'W11', 'S11', 'W12', 'S12', 'W13', 'S13', 'W14'], usecols=range(0, 42))

CParserError: Error tokenizing data. C error: Expected 53 fields in line 1605634, saw 54 Following does NOT work:

df = pd.read_csv(filename, header=None)

CParserError: Error tokenizing data. C error: Expected 53 fields in line 1605634, saw 54

Hence, in your problem you have to pass usecols=range(0, 2)

score 3 · Answer 26 · answered Nov 10 '21 at 07:02

3

The issue is with the delimiter. Find what kind of delimiter is used in your data and specify it like below:

data = pd.read_csv('some_data.csv', sep='\t')

answered Nov 10 '21 at 07:02

Abu Bakar Siddik

369
3
7

score 2 · Answer 27 · answered Jan 02 '18 at 09:56

use pandas.read_csv('CSVFILENAME',header=None,sep=', ')

when trying to read csv data from the link

http://archive.ics.uci.edu/ml/machine-learning-databases/adult/adult.data

I copied the data from the site into my csvfile. It had extra spaces so used sep =', ' and it worked :)

score 2 · Answer 28 · answered Nov 20 '18 at 02:08

2

I had a similar case as this and setting

train = pd.read_csv('input.csv' , encoding='latin1',engine='python')

worked

answered Nov 20 '18 at 02:08

Adewole Adesola

179
2
3

Scott Skiles · Answer 29 · 2019-03-01T16:33:48.637

The issue for me was that a new column was appended to my CSV intraday. The accepted answer solution would not work as every future row would be discarded if I used error_bad_lines=False.

The solution in this case was to use the usecols parameter in pd.read_csv(). This way I can specify only the columns that I need to read into the CSV and my Python code will remain resilient to future CSV changes so long as a header column exists (and the column names do not change).

usecols : list-like or callable, optional 

Return a subset of the columns. If list-like, all elements must either
be positional (i.e. integer indices into the document columns) or
strings that correspond to column names provided either by the user in
names or inferred from the document header row(s). For example, a
valid list-like usecols parameter would be [0, 1, 2] or ['foo', 'bar',
'baz']. Element order is ignored, so usecols=[0, 1] is the same as [1,
0]. To instantiate a DataFrame from data with element order preserved
use pd.read_csv(data, usecols=['foo', 'bar'])[['foo', 'bar']] for
columns in ['foo', 'bar'] order or pd.read_csv(data, usecols=['foo',
'bar'])[['bar', 'foo']] for ['bar', 'foo'] order.

Example

my_columns = ['foo', 'bar', 'bob']
df = pd.read_csv(file_path, usecols=my_columns)

Another benefit of this is that I can load way less data into memory if I am only using 3-4 columns of a CSV that has 18-20 columns.

score 2 · Answer 30 · answered Jun 01 '21 at 14:51

2

Sometimes in a cell there is a comma ",". Due to that pandas can' t read it. Try delimiter with ";"

df = pd.read_csv(r'yourpath', delimiter=";")

answered Jun 01 '21 at 14:51

piseynir

237
1
4
14

score 2 · Answer 31 · answered Sep 23 '22 at 10:29

You can use :

pd.read_csv("mycsv.csv", delimiter=";")

Pandas 1.4.4

It can be the delimiter of your file, open it as a text file, lookup for the delimiter. Then you will have columns that can be empty and unamed because of the rows that contains way too many delimiters.

Therefore, you can handle them with pandas and checking for values. For me, it's better than skipping lines in my case.

score 2 · Answer 32 · answered Nov 08 '22 at 12:57

2

Check if you are loading the csv with the correct separator.

df = pd.read_csv(csvname, header=0, sep=",")

answered Nov 08 '22 at 12:57

reggie

3,523
14
62
97

score 1 · Answer 33 · answered Jun 20 '17 at 05:28

1

I had a dataset with prexisting row numbers, I used index_col:

pd.read_csv('train.csv', index_col=0)

answered Jun 20 '17 at 05:28

gogasca

9,283
6
80
125

score 1 · Answer 34 · edited Oct 21 '18 at 15:54

1

This is what I did.

sep='::' solved my issue:

data=pd.read_csv('C:\\Users\\HP\\Downloads\\NPL ASSINGMENT 2 imdb_labelled\\imdb_labelled.txt',engine='python',header=None,sep='::')

edited Oct 21 '18 at 15:54

ssuperczynski

3,190
3
44
61

answered Oct 21 '18 at 13:04

Saurabh Tripathi

47
1
3

score 1 · Answer 35 · answered Nov 26 '18 at 13:32

1

I have the same problem when read_csv: ParserError: Error tokenizing data. I just saved the old csv file to a new csv file. The problem is solved!

answered Nov 26 '18 at 13:32

Simin Zuo

21
3

fact_finder · Answer 36 · 2019-10-31T15:59:32.727

I have encountered this error with a stray quotation mark. I use mapping software which will put quotation marks around text items when exporting comma-delimited files. Text which uses quote marks (e.g. ' = feet and " = inches) can be problematic when then induce delimiter collisions. Consider this example which notes that a 5-inch well log print is poor:

UWI_key,Latitude,Longitude,Remark US42051316890000,30.4386484,-96.4330734,"poor 5""

Using 5" as shorthand for 5 inch ends up throwing a wrench in the works. Excel will simply strip off the extra quote mark, but Pandas breaks down without the error_bad_lines=False argument mentioned above.

score 1 · Answer 37 · answered Jun 11 '19 at 09:47

1

Most of the useful answers are already mentioned, however I suggest saving the pandas dataframes as parquet file. Parquet files don't have this problem and they are memory efficient at the same time.

answered Jun 11 '19 at 09:47

Bikash Joshi

103
2
9

I concur with this answer. Writing the Pandas DataFrame as a parquet file solved my related problem. – Evan Oct 08 '21 at 06:00

score 1 · Answer 38 · answered Nov 27 '19 at 01:13

In my case, it is because the format of the first and last two lines of the csv file is different from the middle content of the file.

So what I do is open the csv file as a string, parse the content of the string, then use read_csv to get a dataframe.

import io
import pandas as pd

file = open(f'{file_path}/{file_name}', 'r')
content = file.read()

# change new line character from '\r\n' to '\n'
lines = content.replace('\r', '').split('\n')

# Remove the first and last 2 lines of the file
# StringIO can be considered as a file stored in memory
df = pd.read_csv(StringIO("\n".join(lines[2:-2])), header=None)

score 1 · Answer 39 · answered Aug 26 '21 at 20:17

This looks ugly but you will have your dataframe

import re
path = 'GOOG Key Ratios.csv'

try:
    data = pd.read_csv(path)
except Exception as e:
    val = re.findall('tokenizing.{1,100}\s*Expected\s*(\d{1,2})\s*',str(e),re.I)
    data = pd.read_csv(path, skiprows=int(val[0])-1)

score 0 · Answer 40 · answered Dec 12 '17 at 11:43

0

I had a similar error and the issue was that I had some escaped quotes in my csv file and needed to set the escapechar parameter appropriately.

answered Dec 12 '17 at 11:43

jvvw

506
5
6

score 0 · Answer 41 · answered Jul 13 '18 at 17:31

I had received a .csv from a coworker and when I tried to read the csv using pd.read_csv(), I received a similar error. It was apparently attempting to use the first row to generate the columns for the dataframe, but there were many rows which contained more columns than the first row would imply. I ended up fixing this problem by simply opening and re-saving the file as .csv and using pd.read_csv() again.

score 0 · Answer 42 · edited Aug 19 '18 at 07:27

0

You can do this step to avoid the problem -

train = pd.read_csv('/home/Project/output.csv' , header=None)

just add - header=None

Hope this helps!!

edited Aug 19 '18 at 07:27

LuFFy

8,799
10
41
59

answered Aug 19 '18 at 06:59

rahul ranjan

9
5

score 0 · Answer 43 · answered Oct 28 '18 at 12:46

0

Issue could be with file Issues, In my case, Issue was solved after renaming the file. yet to figure out the reason..

answered Oct 28 '18 at 12:46

SQA_LEARN

161
1
1
8

score 0 · Answer 44 · answered Apr 30 '19 at 02:20

I have encountered this error with a stray quotation mark. I use mapping software which will put quotation marks around text items when exporting comma-delimited files. Text which uses quote marks (e.g. ' = feet and " = inches) can be problematic. Consider this example which notes that a 5-inch well log print is poor:

UWI_key,Latitude,Longitude,Remark US42051316890000,30.4386484,-96.4330734,"poor 5""

Using 5" as shorthand for 5 inch ends up throwing a wrench in the works. Excel will simply strip off the extra quote mark, but Pandas breaks down without the error_bad_lines=False argument mentioned above.

Once you know the nature of your error, it may be easiest to do a Find-Replace from a text editor (e.g., Sublime Text 3 or Notepad++) prior to import.

score 0 · Answer 45 · answered Oct 29 '22 at 15:06

0

In my case the problem was a pandas version, so pandas 1.3.5 works like a charm.

answered Oct 29 '22 at 15:06

Yassin El Jakani

44
2

score 0 · Answer 46 · answered Feb 22 '23 at 12:54

0

I was just missing a comma after the last column title. When I added one in it fixed the issue. Hope it helps someone.

original:

col1, col2, col3

fixed version:

col1, col2, col3,

answered Feb 22 '23 at 12:54

Toms Code

1,439
3
15
34

score 0 · Answer 47 · answered May 13 '23 at 14:50

0

Use first dos2unix to clean the file. after that optional you can add the colNames

answered May 13 '23 at 14:50

Espinola Francisco javier

23
5

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 13 '23 at 16:43

score 0 · Answer 48 · answered Jun 07 '23 at 08:28

Let's imagine, that we have some crappy CSV file and we do not want to drop error lines, because we have plenty of them. In that case, we can drop unnecessary columns, which causes error and load CSV after that:

# in that case, some rows in crappy.csv have more than 3 columns
# and we whant only 3 of them
cat crappy.csv | cut -d',' --fields=1,2,3 > good.csv

score -1 · Answer 49 · answered Oct 10 '17 at 08:40

-1

try: pandas.read_csv(path, sep = ',' ,header=None)

answered Oct 10 '17 at 08:40

THE2ndMOUSE

52
8

Python Pandas Error tokenizing data

49 Answers49

Example

Linked

Related