BUG: read_csv, sep=None, wrong separator guessing in case of one column csv

[louisgeisler]

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 csv
import pandas as pd

one_col_csv = """HEADER
hzhrzhjtrj
rzthyy
azr231
zrhtzrh
zrhtzrh"""

with open("dummy.csv", "w") as file:
    file.write(one_col_csv)

print(pd.read_csv("dummy.csv", sep=None))
#Bad detection of the separator, but no error

# According to the doc, csv sniffer is used in backend
# So I also tested it:
with open("dummy.csv") as fp:
    print(csv.Sniffer().sniff(fp.read(5000)).delimiter)
    # Throw an error "Could not determine delimiter" (which is better)

Issue Description

When pd.reader is used with the parameter sep=None, it failed to automaticaly detect signle column csv.

I have already read this conversation (2016): #13839
They had the same problem but come to the conclusion that the error comes csv sniffer python library.

But I think this is no longer relevant as csv.sniffer now raise an error when pandas still insist on parsing it wrong.

Expected Behavior

It should automatically detect it is a single column file, or at least raise an error.

Installed Versions

INSTALLED VERSIONS

commit : 37ea63d
python : 3.8.16.final.0
python-bits : 64
OS : Linux
OS-release : 5.19.0-41-generic
Version : #42~22.04.1-Ubuntu SMP PREEMPT_DYNAMIC Tue Apr 18 17:40:00 UTC 2
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 2.0.1
numpy : 1.23.0
pytz : 2023.3
dateutil : 2.8.2
setuptools : 67.6.1
pip : 23.1
Cython : 0.29.34
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : 4.9.2
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 3.1.2
IPython : 8.12.0
pandas_datareader: None
bs4 : None
bottleneck : None
brotli : None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : 3.7.1
numba : 0.56.4
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : 1.10.0
snappy : None
sqlalchemy : 2.0.9
tables : None
tabulate : None
xarray : None
xlrd : None
zstandard : None
tzdata : 2023.3
qtpy : None
pyqt5 : None

[DeaMariaLeon]

Hi @louisgeisler, you don't see this warning?

ParserWarning: Falling back to the 'python' engine because the 'c' engine does not support sep=None with delim_whitespace=False; you can avoid this warning by specifying engine='python'.

So you need to change your print line to print(pd.read_csv("dummy.csv", engine='python')).
I did it with pandas 2.0.1, an older version, and the dev version. They all work.

Thank you for opening the issue but I will close it now.

[louisgeisler]

Hi @DeaMariaLeon, I will make the issue clearer:

When you use this:
print(pd.read_csv("dummy.csv", sep=None, engine="python"))
You get this:

            H  AD   R
0  hzhrzhjtrj NaN NaN  
1      rzthyy NaN NaN  
2      azr231 NaN NaN  
3     zrhtzrh NaN NaN  
4     zrhtzrh NaN NaN

instead of this:

       HEADER
0  hzhrzhjtrj
1      rzthyy
2      azr231
3     zrhtzrh
4     zrhtzrh

I would appreciate you reopen the issue...

[Toroi0610]

Hi, @louisgeisler,
Perhaps the csv part of the sample code you provided does not reproduce the actual behavior of pandas.read_csv. In fact, if you look at this link of python_parser.py, it seems that only the first line is read for the delimiter.

with open("dummy.csv") as fp:
    print(csv.Sniffer().sniff(fp.readline()).delimiter)
    # Throw "E". Therefore, HEADER --> H[sep]AD[sep]R

Therefore, this problem seems to be a problem with the csv library, not with pandas as in #13839.

Furthermore, I found the rules for delimiter analysis in the _guess_delimiter function of csv library. It seems that the most frequent character was selected. Therefore, "E" was output this time. The following test results in the output of "R".

print(csv.Sniffer().sniff("HREARDER").delimiter)
# output : "R"

[asishm]

The docs for read_csv does mention that it uses the builtin csv.Sniffer

If sep is None, the C engine cannot automatically detect the separator, but the Python parsing engine can, meaning the latter will be used and automatically detect the separator by Python’s builtin sniffer tool, csv.Sniffer.

I would say this behaves as expected. A line stating that only the first valid line is passed on to csv.Sniffer could probably be added to the docs.

[Toroi0610]

May I add it?

[louisgeisler]

For me the issue did come from pandas ; why are you just passing the first line of the csv to the sniffer?

From what I understand of the Sniffer example: https://docs.python.org/3/library/csv.html They passed more than the Header.

And intuitively, for me, it seems to be commom sense to detect the separator with more than just the header...

[asishm]

1. sniffing by itself is more of an art than a science; and indeed the documentation does state it's based on a rough heuristic and can generate false positives and negatives.
2. The documentation for it says to use "sample text" without any guidance of what constitutes a sample. The example only reads the first 1024 bytes. It's also possible the header line is longer than that. If your suggestion is to feed in more lines to the sample - then what is a sensible limit? one/two/the full file?

Ultimately if you are passing files with unknown delimiters, you should expect cases where the right delimiter is not detected. I'm not a pandas maintainer though - so feel free to discuss further!

[Toroi0610]

My example conforms to lines 212-219 of python_parser.py.

I understand your question. However, the issue about how to find the separator when passing the entire csv is still dependent on the csv library.

As for why only the first line is passed, I think it is an art as @asishm commented.

[louisgeisler]

Oh, ok, I understand.

But in this case, why not always passed the whole csv to the sniffer?

As my example show it, if you pass the whole csv, ot will raise an error.

So I think we can conclude that passing the whole csv, or let's say a sample of at least 100 lines (you detect '/n') will really improve the reliability on read_csv when sep=None, isn't it?

Because, I think we would agree that passing only the header to be parsed by the snifer is probably the less reliable choice isn't it? 😅