Make Stefan Excel parser a bit more concise

[terrycojones]

Hi Stefan. You could use a dict to hold the conversion values for your Excel. It's less code and it's faster. E.g.:

#!/usr/bin/env python

# You'll need to pip import openpyxl
import openpyxl

excelFile = openpyxl.load_workbook('stefan-data.xlsx')
sheet1 = excelFile.active

convert = {
    0: '<10',
    0.5: 10,
    1: 20,
    1.5: 30,
    2: 40,
    2.5: 60,
    3: 80,
    3.5: 120,
    4: 160,
    4.5: 240,
    5: 320,
    5.5: 480,
    6: 640,
    6.5: 960,
    7: 1280,
    7.5: 1920,
    8.5: 2560,
}

for row in sheet1.iter_rows():
    for cell in row:
        if cell.value in convert:
            cell.value = convert[cell.value]

excelFile.save('stefan-new-data.xlsx')

And there's no value for 8?

[terrycojones]

Also, I've left the converted values (apart from <10) as integers. I don't think there's any reason to turn them into strings, is there?

[svdvliet]

Thank for improving the code Terry.
Using a dictionary makes alot of sense.
I returned strings instead of integers because I started with '<10' and just left the quotations marks there, I had noticed this too. I will add some argparse later to load the file from the command line instead of going into the script.
There is supposed to be a value of 8 in fact it should go up to 12 but I stopped at 8 to see if the code worked at all.
I'll fix this when I integrate your code.

[terrycojones]

It would be a bit more robust to write it as follows:

#!/usr/bin/env python

# You'll need to pip import openpyxl
import openpyxl

excelFile = openpyxl.load_workbook('stefan-data.xlsx')
sheet1 = excelFile.active

convert = {
    '0': '<10',
    '0.5': 10,
    '1': 20,
    '1.5': 30,
    '2': 40,
    '2.5': 60,
    '3': 80,
    '3.5': 120,
    '4': 160,
    '4.5': 240,
    '5': 320,
    '5.5': 480,
    '6': 640,
    '6.5': 960,
    '7': 1280,
    '7.5': 1920,
    '8.5': 2560,
}

for row in sheet1.iter_rows():
    for cell in row:
        value = str(cell.value)
        if value in convert:
            cell.value = convert[value]

excelFile.save('stefan-new-data.xlsx')

Here the keys of the convert dictionary are all strings, and I force the lookup to be on a string (str(cell.value)). That way the code will work whether or not the cells in the original excel are formatted as numbers or strings.

[terrycojones]

And if you want to be even more concise:

for row in sheet1.iter_rows():
    for cell in row:
        cell.value = convert.get(str(cell.value), cell.value)

The get function on a dictionary takes a key you want to retrieve and a default value in case the key isn't in the dictionary. Make sense?

[svdvliet]

and the default value its returning when not in the dict is the cell.value that was already there right?

[terrycojones]

Yes, that's right. Here's yet another way to do it:

for row in sheet1.iter_rows():
    for cell in row:
        try:
            cell.value = convert[str(cell.value)]
        except KeyError:
            pass

A KeyError exception is raised if you try to access a key that is not in a dictionary. Here I catch the exception and do nothing (pass), because in that case we don't have anything to do.

@tarfy I think it was you who asked this morning about when pass is useful? This is another (better) example.