"Bad" lines with too few fields

[Noxville]

As per http://pandas.pydata.org/pandas-docs/stable/io.html#handling-bad-lines, records with too many fields cause (depending on error_bad_lines) an exception to be thrown, or stderr to be written to.

Would it be possible to add an option, defaulting to False, that gave similar warnings/errors if there are too few fields in a record (compared to all the other records. The current behaviour is just to insert NaN's - but there are cases where data integrity is important so knowing that some records are missing fields is important.

Cheers ^

[jreback]

If you need data validation like this, I think it is much easier / better to simply count the NaN's.

In [18]: df = pd.read_csv(StringIO("""a,b,c\n1,2\n3,4,5\n"""))

In [19]: df
Out[19]: 
   a  b   c
0  1  2 NaN
1  3  4   5

In [20]: df.count(1)
Out[20]: 
0    2
1    3
dtype: int64

In [21]: df[df.count(1)!=len(df.columns)]
Out[21]: 
   a  b   c
0  1  2 NaN

all that said, this maybe could be in incoporated in the api discussions here: #9549
cc @selasley

[Noxville]

So - I could count the NaN's but the behaviour is the same with (what I consider) valid input compared to invalid input. Here's an example:

I'd say this is a well formed CSV for tabular data - the same number of fields in each record:

a,b,c
1,2,3
4,5,6

This has a null value in the 1st (non-header) record, but given the 2nd comma it's implicit that the null is intentional:

a,b,c
1,,3
4,5,6

This has a null record in the 1st (non-header) record, but it's not implicit that there should be a NaN in the 3rd record, or if the input is just mangled:

a,b,c
1,2
4,5,6

I'm using pandas in an environment where data integrity is very important - so I'd preferably like to make the processing be a tiny bit more verbose about potentially missing data.

[glepore70]

I would also like to see error_bad_lines trigger for too few fields. Embedded line breaks are a common cause of splitting a valid row into two rows. I have many files with 80 columns for each record, but embedded line breaks give me 37 on one line and 43 on the next line.

Is there any other approach to finding these bad lines?

[kelvin22]

I would also like to have this error for too few fields.
I come across @glepore70 case of line break splitting a row into two (due to malformed quote or otherwise) a fair bit...not sure if there is a good way to detect this.

In any case, when there are missing fields, the current read_csv behaviour of adding NAs assumes that it is the last fields that are missing, which is often not the case.

Eg. if something breaks in the first field of the last line

a,b,c,d
1, '12/12/2015', 'hi', False
1, '12/12/2015', 'hi', False
'12/12/2015', 'hi', False

dataframe becomes

a,b,c,d
1, '12/12/2015', 'hi', False
1, '12/12/2015', 'hi', False
'12/12/2015', 'hi', False, NA

Which breaks everything eg. dateparse, converters, etc.
I don't believe there is any real way to handle these rows either, as the data is not yet in the dataframe.

[jreback]

@kelvin22 your example is not easy to interpret as its not very obvious where the error is (in an automatic way). However, these types of things will almost always be impossible to dtype coerce, so they will end up as object. If you are expecting float/datetime/int columns then you can at least be clued in that something is wrong.

[kelvin22]

Sorry @jreback, I'll have another crack at explaining.

Currently the behaviour works great if a cell at the end of the row is mangled/missing

1, '12/12/2015', 'hi', False
1, '12/12/2015', 'hi', False
1, '12/12/2015', 'hi', (NA)
1, '12/12/2015', 'hi', False
1, '12/12/2015', 'hi', False

However it is just as likely to happen anywhere else in the line:

'col1', 'col2', 'col3', 'col4'
1, '12/12/2015', 'hi', False
1, '12/12/2015', 'hi', False
'12/12/2015', 'hi', False, (NA)
1, '12/12/2015', 'hi', False
1, '12/12/2015', 'hi', False

In this case there's no easy way to recover, and these two parameters would both raise an error.

read_csv( dtype = { 'col3': str} , parse_dates = 'col2' )

The counting NAs workaround can't be used as the dataframe doesn't get formed.

If error_bad_lines = False also worked with too few lines, the dud line would be skipped and everything would continue working.

[kodonnell]

Just came across this, though it took a while to figure it out - as mentioned by @kelvin22, I was seeing errors about e.g. an integer field not being allowed to be NA.

As @Noxville mentions, Pandas is actually changing the information content of the data, and hence can't (easily) be used in a pipeline where data integrity is important. From that perspective, I'd call this a critical bug.

[jorisvandenbossche]

@kodonnell Note that this issue is an enhancement request, and not a bug. Pandas is only 'changing' content because of a malformed csv file (and the fact that NA's are not allowed in integer fields is a whole other issue, and with current pandas an inherent limitation, but that is being worked on).

See also issue #15122. Comments, suggestions, pull requests very welcome!

[kodonnell]

@jorisvandenbossche - I consider not failing when it should to be a bug. It can certainly be a bug for downstream users (who e.g. adhere to a tighter CSV specification).

#15122 - looks good.

[sniperyyc]

df.dropna() seems to be a simple solution to me if you just want to "skip" bad lines with too few fields -- missing fields will be parsed as NaN when calling read_csv()