How Do You Dynamically Identify Unknown Delimiters In A Data File?

I have three input data files. Each uses a different delimiter for the data contained therein. Data file one looks like this: apples | bananas | oranges | grapes data file two look

Solution 1:

How about trying Python CSV's standard: http://docs.python.org/library/csv.html#csv.Sniffer

import csv

sniffer = csv.Sniffer()
dialect = sniffer.sniff('quarter, dime, nickel, penny')
print dialect.delimiter
# returns ','

Solution 2:

If you're using python, I'd suggest just calling re.split on the line with all valid expected separators:

>>> l = "big long list of space separated words">>> re.split(r'[ ,|;"]+', l)
['big', 'long', 'list', 'of', 'space', 'separated', 'words']

The only issue would be if one of the files used a separator as part of the data.

If you must identify the separator, your best bet is to count everything excluding spaces. If there are almost no occurrences, then it's probably space, otherwise, it's the max of the mapped characters.

Unfortunately, there's really no way to be sure. You may have space separated data filled with commas, or you may have | separated data filled with semicolons. It may not always work.

Solution 3:

I ended up going with the regex, because of the problem of spaces. Here's my finished code, in case anyone's interested, or could use anything else in it. On a tangential note, it would be neat to find a way to dynamically identify column order, but I realize that's a bit more tricky. In the meantime, I'm falling back on old tricks to sort that out.

for infile in glob.glob(os.path.join(self._input_dir, self._file_mask)):
            #couldn't quite figure out a way to make this a single block #(rather than three separate if/elifs. But you can see the split is#generalized already, so if anyone can come up with a better way,#I'm all ears!! :)for row inopen(infile,'r').readlines():
                if infile.find('comma') > -1: 
                    datefmt = "%m/%d/%Y"
                    last, first, gender, color, dobraw = \
                            [x.strip() for x in re.split(r'[ ,|;"\t]+', row)]
                elif infile.find('space') > -1: 
                    datefmt = "%m-%d-%Y"
                    last, first, unused, gender, dobraw, color = \
                            [x.strip() for x in re.split(r'[ ,|;"\t]+', row)]
                elif infile.find('pipe') > -1:
                    datefmt = "%m-%d-%Y"
                    last, first, unused, gender, color, dobraw = \
                            [x.strip() for x in re.split(r'[ ,|;"\t]+', row)]
                    #There is also a way to do this with csv.Sniffer, but the #spaces around the pipe delimiter also confuse sniffer, so#I couldn't use it.else: raise ValueError(infile + "is not an acceptable input file.")
                

Solution 4:

We can determine the delimiter right most of the time based on some prior information (such as list of common delimiter) and frequency counting that all the lines give the same number of delimiter

defhead(filename: str, n: int):
    try:
        withopen(filename) as f:
            head_lines = [next(f).rstrip() for x inrange(n)]
    except StopIteration:
        withopen(filename) as f:
            head_lines = f.read().splitlines()
    return head_lines


defdetect_delimiter(filename: str, n=2):
    sample_lines = head(filename, n)
    common_delimiters= [',',';','\t',' ','|',':']
    for d in common_delimiters:
        ref = sample_lines[0].count(d)
        if ref > 0:
            ifall([ ref == sample_lines[i].count(d) for i inrange(1,n)]):
                return d
    return','

Often n=2 lines should be enough, check more lines for a more robust answers. Of course there are cases (often artificial ones) those lead to a false detection but it is unlikely happened in practice.

Here I use an efficient python implementation of head function that only read n-first line of a file. See my answer on How to read first N-lines of a file