BUG: read_csv handling of bad lines differs depending on first line

[vnlitvinov]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd
import io

a1 = '''col1,col2,col3,col4,col5,col6
0,1,2,3,4,5,6,7
1960,2000-01-09,1960,04:00:00,ZZsumAqXtD,4322.0735711085945
'''

a2 = '''col1,col2,col3,col4,col5,col6
1960,2000-01-09,1960,04:00:00,ZZsumAqXtD,4322.0735711085945
0,1,2,3,4,5,6,7
'''

print('bad first line')
df1 = pd.read_csv(io.StringIO(a1), on_bad_lines='warn')
print(df1)
print(df1.columns)
print(df1.index)
print(df1.iloc[-1])

print('good first line')
df2 = pd.read_csv(io.StringIO(a2), on_bad_lines='warn')
print(df2)
print(df2.columns)
print(df2.index)
print(df2.iloc[-1])

Issue Description

When a longer-than-header line is first, Pandas thinks extra fields are for index, but when it isn't first then it's treated as a bad line.
I wasn't able to find a way to force Pandas to treat those lines as bad from the get go.

The output of the script above:

bad first line
                 col1      col2        col3         col4  col5  col6
0    1              2         3           4     5.000000   6.0   7.0
1960 2000-01-09  1960  04:00:00  ZZsumAqXtD  4322.073571   NaN   NaN
Index(['col1', 'col2', 'col3', 'col4', 'col5', 'col6'], dtype='object')
MultiIndex([(   0,          '1'),
            (1960, '2000-01-09')],
           )
col1           1960
col2       04:00:00
col3     ZZsumAqXtD
col4    4322.073571
col5            NaN
col6            NaN
Name: (1960, 2000-01-09), dtype: object
good first line
b'Skipping line 3: expected 6 fields, saw 8\n'
   col1        col2  col3      col4        col5         col6
0  1960  2000-01-09  1960  04:00:00  ZZsumAqXtD  4322.073571
Index(['col1', 'col2', 'col3', 'col4', 'col5', 'col6'], dtype='object')
RangeIndex(start=0, stop=1, step=1)
col1           1960
col2     2000-01-09
col3           1960
col4       04:00:00
col5     ZZsumAqXtD
col6    4322.073571
Name: 0, dtype: object

Expected Behavior

I would expect both "good" and "bad" csv-s to be parsed equally, maybe with a flag or option.

Installed Versions

INSTALLED VERSIONS

commit : 4bfe3d0
python : 3.8.13.final.0
python-bits : 64
OS : Windows
OS-release : 10
Version : 10.0.19044
machine : AMD64
processor : Intel64 Family 6 Model 165 Stepping 2, GenuineIntel
byteorder : little
LC_ALL : None
LANG : None
LOCALE : Russian_Russia.1252

pandas : 1.4.2
numpy : 1.22.4
pytz : 2022.1
dateutil : 2.8.2
pip : 22.1.2
setuptools : 62.3.2
Cython : None
pytest : 7.1.2
hypothesis : None
sphinx : 5.0.1
blosc : None
feather : 0.4.1
xlsxwriter : None
lxml.etree : 4.9.0
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 3.1.2
IPython : 8.4.0
pandas_datareader: None
bs4 : 4.11.1
bottleneck : None
brotli :
fastparquet : None
fsspec : 2022.5.0
gcsfs : None
markupsafe : 2.1.1
matplotlib : 3.5.2
numba : None
numexpr : 2.7.3
odfpy : None
openpyxl : 3.0.9
pandas_gbq : None
pyarrow : 8.0.0
pyreadstat : None
pyxlsb : None
s3fs : 2022.5.0
scipy : 1.8.1
snappy : None
sqlalchemy : 1.4.37
tables : 3.7.0
tabulate : None
xarray : 2022.3.0
xlrd : None
xlwt : None
zstandard : None

[phofl]

Hi,

thanks for your report.

could you narrow your example down? I think you are using more fields than necessary.

that said, this behavior is deliberate.
You should be able to overwrite this with index_col=False.

This has some bugs though, which were fixed recently and will be in 1.5

[phofl]

Turns out this does work with index_col=False

[vnlitvinov]

Stripped down example with index_col=False:

import pandas as pd
import io

a1 = '''col1,col2,col3,col4,col5,col6
0,1,2,3,4,5,6,7
1960,2000-01-09,1960,04:00:00,ZZsumAqXtD,4322.0735711085945
'''

a2 = '''col1,col2,col3,col4,col5,col6
1960,2000-01-09,1960,04:00:00,ZZsumAqXtD,4322.0735711085945
0,1,2,3,4,5,6,7
'''

print('bad first line')
df1 = pd.read_csv(io.StringIO(a1), on_bad_lines='warn', index_col=False)
print(df1)

print('good first line')
df2 = pd.read_csv(io.StringIO(a2), on_bad_lines='warn', index_col=False)
print(df2)

outputs:

bad first line
a.py:15: ParserWarning: Length of header or names does not match length of data. This leads to a loss of data with index_col=False.
  df1 = pd.read_csv(io.StringIO(a1), on_bad_lines='warn', index_col=False)
   col1        col2  col3      col4        col5         col6
0     0           1     2         3           4     5.000000
1  1960  2000-01-09  1960  04:00:00  ZZsumAqXtD  4322.073571
good first line
b'Skipping line 3: expected 6 fields, saw 8\n'
   col1        col2  col3      col4        col5         col6
0  1960  2000-01-09  1960  04:00:00  ZZsumAqXtD  4322.073571

Are you saying this is going to be fixed in 1.5? As right now I don't have a way to drop this extra-field line if it just happens to be the first in the dataset.

[phofl]

I meant removing the extra fields. You have 7 columns, 2 should be enough.

This case is not fixed I think. Have to look where this happens

[chubutin]

Hello, any update on this bug?

I'm having a similar issue with on_bad_lines working together with index_col.

I'm using

python 3.11
pandas version 2.1.1

Test Case 1

import unittest
import pandas as pd
from io import StringIO

class TestReadCSV(unittest.TestCase):
    def test_error_bad_lines(self):
        # Crear un CSV con líneas inválidas (diferente número de columnas)
        csv_data = StringIO("""col1,col2,col3
                             1,2,3
                             4,5
                             6,7,8,
                             10,11,12,11
                             """)

        # Leer el CSV con error_bad_lines=False
        data = pd.read_csv(csv_data,
                           sep=',',
                           on_bad_lines='warn',
                           engine='python',
                           dtype='str',
                           index_col=False
                           )

        # Comprobar que el DataFrame contiene solo las filas válidas
        self.assertEqual(len(data), 3)  # Debería haber 3 filas válidas

Outcome

FAILED                         [100%]
tests/unit/asd.py:5 (TestReadCSV.test_error_bad_lines)
3 != 4

Expected :4
Actual   :3

Test Case 2

class TestReadCSV(unittest.TestCase):
    def test_error_bad_lines(self):
        # Crear un CSV con líneas inválidas (diferente número de columnas)
        csv_data = StringIO("""col1,col2,col3
                             1,2,3,,,
                             4,5
                             6,7,8,
                             10,11,12,11
                             """)

        # Leer el CSV con error_bad_lines=False
        data = pd.read_csv(csv_data,
                           sep=',',
                           on_bad_lines='warn',
                           engine='python',
                           dtype='str',
                           )

        # Comprobar que el DataFrame contiene solo las filas válidas
        self.assertEqual(len(data), 2)  # Debería haber 2 filas válidas

Outcome

FAILED                         [100%]
tests/unit/asd.py:5 (TestReadCSV.test_error_bad_lines)
2 != 4

Expected :4
Actual   :2

Test Case 3

import unittest
import pandas as pd
from io import StringIO

class TestReadCSV(unittest.TestCase):
    def test_error_bad_lines(self):
        # Crear un CSV con líneas inválidas (diferente número de columnas)
        csv_data = StringIO("""col1,col2,col3
                             4,5
                             1,2,3,,,
                             6,7,8,
                             10,11,12,11
                             """)

        # Leer el CSV con error_bad_lines=False
        data = pd.read_csv(csv_data,
                           sep=',',
                           on_bad_lines='warn',
                           engine='python',
                           dtype='str',
                           )

        # Comprobar que el DataFrame contiene solo las filas válidas
        self.assertEqual(len(data), 2)  # Debería haber 2 filas válidas

Outcome

[100%]
Skipping line 4: Expected 3 fields in line 4, saw 6
Skipping line 5: Expected 3 fields in line 5, saw 4
Skipping line 6: Expected 3 fields in line 6, saw 4

tests/unit/asd.py:5 (TestReadCSV.test_error_bad_lines)
2 != 1

Test case 4

import unittest
import pandas as pd
from io import StringIO

class TestReadCSV(unittest.TestCase):
    def test_error_bad_lines(self):
        # Crear un CSV con líneas inválidas (diferente número de columnas)
        csv_data = StringIO("""col1,col2,col3
                             1,2,3
                             4,5
                             6,7,8,
                             10,11,12,11
                             """)

        # Leer el CSV con error_bad_lines=False
        data = pd.read_csv(csv_data,
                           sep=',',
                           on_bad_lines='warn',
                           engine='python',
                           dtype='str'
                           )

        # Comprobar que el DataFrame contiene solo las filas válidas
        self.assertEqual(len(data), 2)  # Debería haber 3 filas válidasbería haber 2 filas válidas

Outcome

PASSED                         [100%]
Skipping line 5: Expected 3 fields in line 5, saw 4
Skipping line 6: Expected 3 fields in line 6, saw 4