How to detect double/consecutive character as separator using csv sniffer?

user_14678459 0
Viewed 226

For instance, the following table uses double whitespaces as a separator for the table values but uses single whitespace before the opening round brackets in the header line. When a csv.Sniffer() is applied to the file, the single whitespace is returned as the delimiter, but not the double whitespace, as expected. How to get the double whitespace as the resulting delimiter?

Day (ddd)  N (d)  E (d)  X (m)  Y(s)  Z (c)  S (p)
277  40.52  -36.59  0.11  -0.50  -1.62  30.17
277  40.52  -36.49  0.18  -0.04  -1.66  30.14
277  40.51  -36.39  0.14  -0.07  -1.64  30.15
separator sniffer whitespace csv python
3 Answers

As I said in a comment the csv module doesn't generally support multi-character delimiters. However there's a workaround available in this special case because the delimiter happens to be a whitespace character.

import csv

filename = "double_whitespace.csv"

with open(filename, 'r', newline='') as file:
    reader = csv.reader(file, delimiter=' ', skipinitialspace=True)
    next(reader)  # Skip header row.
    for row in reader:
        print(row)

Output:

['277', '40.52', '-36.59', '0.11', '-0.50', '-1.62', '30.17']
['277', '40.52', '-36.49', '0.18', '-0.04', '-1.66', '30.14']
['277', '40.51', '-36.39', '0.14', '-0.07', '-1.64', '30.15']
user_3552300

This is essentially cheating - if you think you might have ' ' as delimiter, rewrite them:

with open("example.csv","w") as f:
    f.write("""Day (ddd)  N (d)  E (d)  X (m)  Y(s)  Z (c)  S (p)
277  40.52  -36.59  0.11  -0.50  -1.62  30.17
277  40.52  -36.49  0.18  -0.04  -1.66  30.14
277  40.51  -36.39  0.14  -0.07  -1.64  30.15""")

import csv
import io
import re


# keeps the whole file in memory - for big files >2G you'll need to rewrite
# to linebased parsing
stringIO = io.StringIO()
with open('example.csv') as csvfile:
    for line in csvfile:
        stringIO.write(re.sub("  ","|",line))

stringIO.seek(0)
sn = csv.Sniffer()
dialect = sn.sniff(stringIO.read(1024))

# back to start
stringIO.seek(0)
reader = csv.reader(stringIO, dialect)
for line in reader:
    print(line)

Output:

['Day (ddd)', 'N (d)', 'E (d)', 'X (m)', 'Y(s)', 'Z (c)', 'S (p)']
['277', '40.52', '-36.59', '0.11', '-0.50', '-1.62', '30.17']
['277', '40.52', '-36.49', '0.18', '-0.04', '-1.66', '30.14']
['277', '40.51', '-36.39', '0.14', '-0.07', '-1.64', '30.15']

If the file does not contain " " sniffer will work for normal delimiters, if it does, it will replace them with '|' (which is hopefully not yet used in your file, you might want to check it and use ';,#' etc. instead if used).

user_75053950

Here's another solution that uses the fact the first argument passed to csv.reader() only has to be somewhat "file-like". This fact means you to could supply an instance of a custom class which changes the double space characters in lines of the file to some other single character (such as a comma). If you do this the header row can be handled like any of the others.

Here's what I'm suggesting:

import csv

class CSV_Translater:
    """ File-like object that translates characters. """
    def __init__(self, f, old, new):
        self.f, self.old, self.new = f, old, new
    def __iter__(self):
        return self
    def __next__(self):
        return next(self.f).replace(self.old, self.new)


if __name__ == '__main__':

    DELIMITER = ','
    filename = "double_whitespace.csv"

    with open(filename, 'r', newline='') as file:
        translator = CSV_Translater(file, '  ', DELIMITER)
        for row in csv.reader(translator, delimiter=DELIMITER):
            print(row)

Output

['Day (ddd)', 'N (d)', 'E (d)', 'X (m)', 'Y(s)', 'Z (c)', 'S (p)']
['277', '40.52', '-36.59', '0.11', '-0.50', '-1.62', '30.17']
['277', '40.52', '-36.49', '0.18', '-0.04', '-1.66', '30.14']
['277', '40.51', '-36.39', '0.14', '-0.07', '-1.64', '30.15']
user_3552300
Related