BUG: pandas.read_csv reads in a comma separated file when delim #54918

maxwest-uw · 2023-09-01T00:27:30Z

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

# the contents of 'testorb.csv'
"""
ObjID,FORMAT,q,e,inc,node,argPeri,t_p,epoch
S00000t,COM,0.952105479028,0.504888475701,4.899098347472,148.881068605772,39.949789586436,54486.32292808,54466.0
S000015,COM,0.154159694141,0.938877338769,48.223407545506,105.219186748093,38.658234184755,54736.8815041081,54466.0
S000021,COM,0.281032037287,0.878997705355,24.078642319571,135.007398381801,58.797679973638,54022.4542604236,54466.0
"""

import pandas as pd
df = pd.read_csv("testorb.csv", delim_whitespace=True)

"""
this should 'improperly' load the above .csv, which doesn't have any whitespace, as a single column, but instead it breaks apart the columns along the commas
"""

Issue Description

trying to load a comma separated .csv file with the delim_whitespace parameter set to True should cause the file to be loaded as a single column, but instead it reads it as if it was delimited by commas and breaks it "properly" (in our case, not how we expect, because the CI test was expecting a failure to load the required columns when we passed a separator parameter as "whitespace" with the above file ). I tested this in 2.1.0, 2.0.3, and 1.5.3 and in the older versions everything worked as expected, but 2.1.0 loaded it with comma separation.

Expected Behavior

should not breakup the csv based on a comma delimiter when delim_whitespace is set to True.

Installed Versions

INSTALLED VERSIONS

commit : ba1cccd
python : 3.10.12.final.0
python-bits : 64
OS : Darwin
OS-release : 21.6.0
Version : Darwin Kernel Version 21.6.0: Sat Jun 18 17:07:22 PDT 2022; root:xnu-8020.140.41~1/RELEASE_ARM64_T6000
machine : arm64
processor : arm
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 2.1.0
numpy : 1.24.3
pytz : 2022.7
dateutil : 2.8.2
setuptools : 68.0.0
pip : 23.2.1
Cython : 3.0.0
pytest : 7.4.0
hypothesis : None
sphinx : 6.1.3
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : 1.1
pymysql : None
psycopg2 : None
jinja2 : 3.1.2
IPython : 8.14.0
pandas_datareader : None
bs4 : 4.12.2
bottleneck : 1.3.5
dataframe-api-compat: None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : 3.5.3
numba : 0.57.1
numexpr : 2.8.4
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : 1.11.1
sqlalchemy : None
tables : 3.8.0
tabulate : None
xarray : None
xlrd : None
zstandard : None
tzdata : 2023.3
qtpy : None
pyqt5 : None

The text was updated successfully, but these errors were encountered:

denis-bz · 2023-09-01T09:14:44Z

Can you try

for engine in ["python", "c"]:
    print( "\n-- engine", engine )
    df = pd.read_csv( StringIO( s ), engine=engine )  # ...

I have another read_csv problem in 2.1.0 (lines longer than 512 ?) where engine python works, c doesn't, will post

maxwest-uw · 2023-09-01T16:42:43Z

running with

for engine in ["python", "c"]:
     print( "\n-- engine", engine )
     df = pd.read_csv(s, engine=engine, delim_whitespace=True )
     print(df)
     print(df.columns)

I got

-- engine python
         ObjID,FORMAT,q,e,inc,node,argPeri,t_p,epoch
0  S00000t,COM,0.952105479028,0.504888475701,4.89...
1  S000015,COM,0.154159694141,0.938877338769,48.2...
2  S000021,COM,0.281032037287,0.878997705355,24.0...
3  S00002b,COM,1.086765486292,0.624200488727,21.6...
4  S000044,COM,0.836323486528,0.40684598082,23.99...
Index(['ObjID,FORMAT,q,e,inc,node,argPeri,t_p,epoch'], dtype='object')

-- engine c
     ObjID FORMAT         q         e        inc        node     argPeri           t_p    epoch
0  S00000t    COM  0.952105  0.504888   4.899098  148.881069   39.949790  54486.322928  54466.0
1  S000015    COM  0.154160  0.938877  48.223408  105.219187   38.658234  54736.881504  54466.0
2  S000021    COM  0.281032  0.878998  24.078642  135.007398   58.797680  54022.454260  54466.0
3  S00002b    COM  1.086765  0.624200  21.686676  157.115013  325.254912  54590.148873  54466.0
4  S000044    COM  0.836323  0.406846  23.998135  172.289085  357.925129  54554.556372  54466.0
Index(['ObjID', 'FORMAT', 'q', 'e', 'inc', 'node', 'argPeri', 't_p', 'epoch'], dtype='object')

so it does seem like it's a problem with the c engine only

AlfredMayhew · 2023-09-01T17:03:42Z

Hi,
Just want to second this. I was having the same issue and I have been able to resolve it by changing the engine to "python".
Happy to answer any questions.

Thanks,
Alfie

rhshadrach · 2023-09-01T20:56:40Z

A git-bisect points to #52632.

cc @WillAyd

briochh · 2023-09-02T00:18:56Z

at the risk of piling in:

import pandas as pd
with open('test.csv', 'w') as fp:
    fp.write("col0      col1\n"
             "this  that\n"
             "that  and,this")
df = pd.read_csv('test.csv', delim_whitespace=True)

throws:

Traceback (most recent call last):
  File ".../lib/python3.11/site-packages/IPython/core/interactiveshell.py", line 3508, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-10-32ef6a2b53dd>", line 6, in <module>
    df = pd.read_csv('test.csv', delim_whitespace=True)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File ".../lib/python3.11/site-packages/pandas/io/parsers/readers.py", line 948, in read_csv
    return _read(filepath_or_buffer, kwds)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File ".../lib/python3.11/site-packages/pandas/io/parsers/readers.py", line 617, in _read
    return parser.read(nrows)
           ^^^^^^^^^^^^^^^^^^
  File ".../lib/python3.11/site-packages/pandas/io/parsers/readers.py", line 1748, in read
    ) = self._engine.read(  # type: ignore[attr-defined]
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File ".../lib/python3.11/site-packages/pandas/io/parsers/c_parser_wrapper.py", line 234, in read
    chunks = self._reader.read_low_memory(nrows)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "parsers.pyx", line 843, in pandas._libs.parsers.TextReader.read_low_memory
  File "parsers.pyx", line 904, in pandas._libs.parsers.TextReader._read_rows
  File "parsers.pyx", line 879, in pandas._libs.parsers.TextReader._tokenize_rows
  File "parsers.pyx", line 890, in pandas._libs.parsers.TextReader._check_tokenize_status
  File "parsers.pyx", line 2058, in pandas._libs.parsers.raise_parser_error
pandas.errors.ParserError: Error tokenizing data. C error: Expected 2 fields in line 3, saw 3

It works as expected with df = pd.read_csv('test.csv', delim_whitespace=True, engine='python')

WillAyd · 2023-09-02T03:46:53Z

A git-bisect points to #52632.

cc @WillAyd

Nice find. It looks like the new macro is missing a !delim_whitespace to start when compared to the old. Does adding that fix this?

denis-bz · 2023-09-02T13:18:53Z

Here read_csv sep=r"\s+" works with engine=python but not engine=c;
move to a separate issue if you like --

from io import StringIO
import numpy as np
import pandas as pd

print( "pandas version", pd.__version__ )

s = \
""" type    manuf   KW  rotordiam   height  iec pmax    power   windspeed
E-82/3000   Enercon 3000    82  84.00   IIA 3020    [0,0,25,82,174,321,525,800,1135,1510,1880,2200,2500,2770,2910,3000,3020,3020,3020,3020,3020,3020,3020,3020,3020.0]  [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25.0]
E-101/3050  Enercon 3050    101 149.00  IIA 3000    [0,0,0,0,3,22,49,92,155,240,339,480,628,830,1035,1292,1549,1820,2090,2350,2580,2775,2900,2980,3000,3000,3000,3000,3000,3000,3000,3000,3000,3000,3000,3000,3000,3000,3000,3000,3000,3000,3000,3000,3000,3000,3000,3000,3000,3000,3000,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.0] [0,0.5,1,1.5,2,2.5,3,3.5,4,4.5,5,5.5,6,6.5,7,7.5,8,8.5,9,9.5,10,10.5,11,11.5,12,12.5,13,13.5,14,14.5,15,15.5,16,16.5,17,17.5,18,18.5,19,19.5,20,20.5,21,21.5,22,22.5,23, 23.5,24,24.5,25,25.5,26,26.5,27,27.5,28,28.5,29,29.5,30,30.5,31,31.5,32,32.5,33,33.5,34,34.5,35.0]
"""
# Beware of cut-paste putting newlines in the 2 long data lines -- nfields should all be 9:
for line in s.split( "\n" ):
    print( "line len %d  nfields %d" % (len(line), len( line.split() )))

for engine in ["python", "c"]:
    print( "\n-- engine", engine )
    df = pd.read_csv( StringIO( s ), sep=r"\s+", comment="#", engine=engine )
    print( f"df {df.shape}: \n{df} \n" )

Logfile --

pandas version 2.1.0
line len 70  nfields 9
line len 237  nfields 9
line len 590  nfields 9
line len 0  nfields 0

-- engine python
df (2, 9): 
         type  ...                                          windspeed
0   E-82/3000  ...  [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,...
1  E-101/3050  ...  [0,0.5,1,1.5,2,2.5,3,3.5,4,4.5,5,5.5,6,6.5,7,7...

[2 rows x 9 columns] 


-- engine c
Traceback (most recent call last):
  File "/Users/bz/py/pandas/readcsv210/readcsv-parsererror.py", line 19, in <module>
    df = pd.read_csv( StringIO( s ), sep=r"\s+", comment="#", engine=engine )
  File "/opt/local/py/site/top/pandas/io/parsers/readers.py", line 948, in read_csv
    return _read(filepath_or_buffer, kwds)
  File "/opt/local/py/site/top/pandas/io/parsers/readers.py", line 617, in _read
    return parser.read(nrows)
  File "/opt/local/py/site/top/pandas/io/parsers/readers.py", line 1748, in read
    ) = self._engine.read(  # type: ignore[attr-defined]
  File "/opt/local/py/site/top/pandas/io/parsers/c_parser_wrapper.py", line 234, in read
    chunks = self._reader.read_low_memory(nrows)
  File "parsers.pyx", line 843, in pandas._libs.parsers.TextReader.read_low_memory
  File "parsers.pyx", line 904, in pandas._libs.parsers.TextReader._read_rows
  File "parsers.pyx", line 879, in pandas._libs.parsers.TextReader._tokenize_rows
  File "parsers.pyx", line 890, in pandas._libs.parsers.TextReader._check_tokenize_status
  File "parsers.pyx", line 2058, in pandas._libs.parsers.raise_parser_error
pandas.errors.ParserError: Error tokenizing data. C error: Expected 57 fields in line 3, saw 149

maxwest-uw added Bug Needs Triage Issue that has not been reviewed by a pandas team member labels Sep 1, 2023

maxwest-uw mentioned this issue Sep 1, 2023

pin pandas dirac-institute/sorcha#576

Merged

5 tasks

rhshadrach mentioned this issue Sep 1, 2023

BUG: Regression in splitting in read_table #54931

Closed

3 tasks

rhshadrach added Regression Functionality that used to work in a prior pandas version IO CSV read_csv, to_csv and removed Needs Triage Issue that has not been reviewed by a pandas team member labels Sep 1, 2023

rhshadrach added this to the 2.1.1 milestone Sep 1, 2023

briochh mentioned this issue Sep 2, 2023

feat(get-pestpp): add get-pestpp command line interface pypest/pyemu#457

Merged

phofl mentioned this issue Sep 2, 2023

REGR: read_csv splitting on comma with delim_whitespace #54954

Merged

6 tasks

phofl closed this as completed in #54954 Sep 2, 2023

This was referenced Sep 11, 2023

Pandas pinned to < 2.1.0 pypest/pyemu#461

Closed

trying to catch up with more pandas deprecation warnings pypest/pyemu#462

Merged

mschwamb mentioned this issue Sep 26, 2023

unpin pandas dirac-institute/sorcha#651

Closed

lintool mentioned this issue Sep 28, 2023

Failure with BEIR regressions castorini/pyserini#1658

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

BUG: pandas.read_csv reads in a comma separated file when delim #54918

BUG: pandas.read_csv reads in a comma separated file when delim #54918

maxwest-uw commented Sep 1, 2023

INSTALLED VERSIONS

denis-bz commented Sep 1, 2023

maxwest-uw commented Sep 1, 2023

AlfredMayhew commented Sep 1, 2023

rhshadrach commented Sep 1, 2023

briochh commented Sep 2, 2023

WillAyd commented Sep 2, 2023

denis-bz commented Sep 2, 2023

BUG: pandas.read_csv reads in a comma separated file when delim #54918

BUG: pandas.read_csv reads in a comma separated file when delim #54918

Comments

maxwest-uw commented Sep 1, 2023

Pandas version checks

Reproducible Example

Issue Description

Expected Behavior

Installed Versions

INSTALLED VERSIONS

denis-bz commented Sep 1, 2023

maxwest-uw commented Sep 1, 2023

AlfredMayhew commented Sep 1, 2023

rhshadrach commented Sep 1, 2023

briochh commented Sep 2, 2023

WillAyd commented Sep 2, 2023

denis-bz commented Sep 2, 2023