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

I discovered this beautiful library “CleverCSV” that may solve our(my) problem: https://github.com/alan-turing-institute/CleverCSV

It may be interesting to integrate it in pandas directly or using it as backend to read csv instead of the build-in csv library…

I think the best compromise may be something like passing the 30 first line of the csv file to the sniffer. Something like this:

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)

def read_csv(path: str, sep: str, **kwargs) -> pd.DataFrame:
    
    N_LINE_FOR_SNIFFER = 30 # We take the 3àyh first lines to sniff
    
    if sep is None:
        with open(path) as file:
            line_samples = "\n".join(file.readlines(N_LINE_FOR_SNIFFER))
            try:
                sep = csv.Sniffer().sniff(line_samples).delimiter
            except Exception as e:
                if str(e)=="Could not determine delimiter":
                    sep = "," # Presume it is a single column file, so define a random separator
                else:
                    raise Exception(e)
            finally:
                return pd.read_csv(path, sep=sep, **kwargs)
    else:
        return pd.read_csv(path, sep=sep, **kwargs)
    
#read_csv("dummy.csv", sep=None)

print(pd.read_csv("dummy.csv", sep=None))

It will hugely increase the reliability of the sniffer, while still being fast as we didn’t read all the file… What do you think about this alternative?

(I really think that sep=None is a very convenient feature that should be improve instead of deleted…)

@DeaMariaLeon yes, I do.

Not a fan of the sniffing,

that said exploring passing the first header + 1 lines might be interesting. Should definitely clarify the docs

Passing in the entire csv is also not performant. Also it’s very easy to come up with a csv that would still cause the sniffer to give you the current results.

For example, for the below contrived example - it still outputs 'E' as the delimiter:

In [1]: s = '''HEADER
   ...: THEREWAS'''

In [2]: import csv

In [3]: csv.Sniffer().sniff(s).delimiter
Out[3]: 'E'

Hence my suggestion to either

1. explicitly document the fact that only the first valid line of the csv is passed to csv.Sniffer to determine the delimiter, or
2. deprecate and eventually remove sep=None as an option - but I don’t know if pandas uses this internally anywhere else.

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.