data cleaning: dealing with a large number of different formats from user inputs

Question

I have some dirty data that is in user input, so it's not consistent. They are all either a single number or a number range.

number_ranges = [
    '11.6', '665.690, 705.715', '740.54-830.18ABC;900-930ABC', '1200',
    '2100 / 2200; 2320 / 2350', '2300-2400 / 2500-2560 / 2730-2740'
]

number_ranges = ','.join(number_ranges)

number_ranges = number_ranges.replace(' ', '')

number_ranges= re.sub(r"[a-zA-Z]+", "", number_ranges)

number_ranges= re.sub(r"[;]+", ",", number_ranges)

number_ranges = str(number_ranges).split(',')

This is the resulting list:

[
    '11.6', '665.690', '705.715', '740.54-830.18', '900-930', '1200', '2100/2200',
    '2320/2350', '2300-2400/2500-2560/2730-2740'
]

I know from here that

for i in number_ranges:
    if (len(i) >5) and ('.' in i) and ('-' not in i):
        i = i.replace('.','-')

for i in number_ranges:
    if ('-' in i) and ('/' in i):
        i = i.split('/')

for i in number_ranges:
    if len(i) < 3:
        i = str(int(i) * 1000)

I've also tried this method:

for n, i in enumerate(number_ranges):
    if (len(i) >5) and ('.' in i) and ('-' not in i):
        number_ranges[n] = i.replace('.','-')

665.690 should be 665-690, 740.54-830.18ABC should be 741-830, 2100 / 2200 should be 2100-2200, 11.6 should be 11600

The end result should have the ranges in integer tuples, so:

[(11600,), (665, 690), (705, 715), (741, 830), (900, 930), (1200,), (2100, 2200), (2320, 2350), (2300, 2400), (2500, 2560), (2730, 2740)]

From there if I need them in a range I can use:

for pair in number_ranges:  
    number_ranges.append("{}-{}".format(*pair))

I know the logic, but not the implementation.

I guess what I'm trying to figure out is how to replace characters/manipulate strings based on certain conditions.

These are the most common formats, so I want to account for them. I know I'll never be able to predict what someone will put in, but I think I can account for 95% plus of cases.

My apologies in advance if I've left out any necessary information. I will provide it as soon as I can.

Thank you.

Edit: I got it to work with the below code:

number_ranges = ','.join(number_ranges)

number_ranges = number_ranges.replace(' ', '')

number_ranges= re.sub(r"[a-zA-Z]+", "", number_ranges)

number_ranges= re.sub(r"[;]+", ",", number_ranges)

number_ranges = str(number_ranges).split(',')

for n, i in enumerate(number_ranges):
    if ('-' in i) and ('/' in i):
        number_ranges[n] = i.replace('/',',')

for n, i in enumerate(number_ranges):
    if ('-' not in i) and ('/' in i):
        number_ranges[n] = i.replace('/','-')

for n, i in enumerate(number_ranges):
    if ('-' not in i) and ('.' in i) and (len(i)>4):
        number_ranges[n] = i.replace('.','-')

for n, i in enumerate(number_ranges):
    if ('.' in i) and (len(i) <= 4) and (float(i) < 30):
        number_ranges[n] = str(round(float(i) * 1000))

number_ranges = [i.split(',') for i in number_ranges]

Answer 1

I have try to find a "pythonic" way to write this set of rules. Maybe it could give you some ideas, and it could certainly been improved.

number_ranges = [
    '11.6', '665.690, 705.715', '740.54-830.18ABC;900-930ABC', '1200',
    '2100 / 2200; 2320 / 2350', '2300-2400 / 2500-2560 / 2730-2740', '433.454', '345-654'
]

import re

def outer_split(rangetext):
    '''Split the input text to individual range text.'''
    # Rule:
    # if both characters are present, use the second one to split
    # and switch the first one to '-'

    doubleseparators = ['-/', '.,', '-;', '/;'] 

    for c in doubleseparators:
        if c[0] in rangetext and c[1] in rangetext:
            outersplit = rangetext.split(c[1])
            outersplit = [s.replace(c[0], '-') for s in outersplit]
            break
    else:
            outersplit = [rangetext, ]

    return outersplit


def inner_split(rangetext):
    '''Clean the range text and Split to [left, right] boundaries.'''

    rangetext = re.sub(r'[a-zA-Z ]+', '', rangetext)

    sep = '-'
    if sep in rangetext:
        innersplit = rangetext.split(sep)
    else:
        innersplit = [rangetext,]

    # The special '.' case:
    if len(innersplit)==1 and '.' in innersplit[0]:
        l, r = innersplit[0].split('.')
        if len(l)>2 or len(r)>2:
            innersplit = [l, r]
        else:
            innersplit = [str(float(innersplit[0])*1000), ]

    return innersplit


individualinputs = [individualinput for text in number_ranges
                    for individualinput in outer_split(text)]

[inner_split(textrange) for textrange in individualinputs]

The output is:

[['11600.0'],
 ['665', '690'],
 ['705', '715'],
 ['740.54', '830.18'],
 ['900', '930'],
 ['1200'],
 ['2100', '2200'],
 ['2320', '2350'],
 ['2300', '2400'],
 ['2500', '2560'],
 ['2730', '2740'],
 ['433', '454'],
 ['345', '654']]