Python - Best way to lookup values in a table-like scheme

Question

I have a client who wants a way to calculate shipping costs. It's calculated by weight and kilometer (not linear unfortunately). It looks like this:

|           | To 50km   | To 100km  | To 150km  | To 200km  |
|-------    |---------  |---------- |---------- |---------- |
| 10kg      | 84€       | 95€       | 104.45€   | 116€      |
| 20kg      | 98€       | 108.50€   | 117.10€   | 127.20€   |
| 30kg      | 112.40€   | 121.20€   | 129.95€   | 149.30€   |

I'd love to lookup a value by calling a function like this calc_shipping_costs(range, weight), so for example calc_shipping_costs(100, 20) and then receive 108.50€

What's the best method to do so?

@Pureferret I didn't want anyone to post a function in here. It was about giving advice on what's the best way to look up these values. — Dominic, Aug 15 '16 at 10:59
have a look at [pandas](http://pandas.pydata.org/). It seems it would be a good choice for you. — desiato, Aug 15 '16 at 11:01
@Dominic I'm afraid that might make this off-topic for Stack Overflow, unfortunately — AncientSwordRage, Aug 15 '16 at 11:03
I assume you will need to interpolate somehow if the passed distance and weight values don't exactly match your table columns & rows. There are suggestions on how to do that [here](http://stackoverflow.com/questions/29974122/interpolating-data-from-a-look-up-table). BTW, `range` is a bad choice for an argument name as that masks the built-in `range` type. — PM 2Ring, Aug 15 '16 at 11:16
You'd iterate over weight; until you find a bracket where your package cost would go. Then you'd iterate over the distance, until you find the range of distances within which the distance falls in; the cost is in cell in the given row and column. — Antti Haapala -- Слава Україні, Aug 15 '16 at 11:42
The first that comes to your mind and works will be better than one from "the internet". Try to come up with several. Find out how to store the data in your script first. Write a function that accesses the data. Then get it to access the right data... — handle, Aug 15 '16 at 13:02

score 0 · Answer 1 · answered Aug 15 '16 at 13:45

So if anyone is wondering. I did it like this:

def calc_shipping_cost(weight, kilometer):

    WEIGHT_BREAKPOINTS = [10, 20, 40, 60, 80, 100, 500]
    KILOMETER_BREAKPOINTS = [50, 100, 150, 200, 999]

    prices = [
        [84.85, 95.15, 104.45, 116.70, 122.25],
        [98.65, 108.45, 117.20, 127.95, 134.60],
        [112.40, 121.70, 129.95, 149.30, 153.10],
        [139.95, 148.20, 155.45, 173.10, 177.80],
        [153.70, 167.50, 168.20, 193.20, 196.30],
        [181.25, 188.00, 193.70, 225.85, 227.15],
        [208.80, 214.50, 219.20, 281.00, 282.70],
    ]

    row = WEIGHT_BREAKPOINTS.index(weight)
    col = KILOMETER_BREAKPOINTS.index(kilometer)

    return prices[row][col]

score 0 · Answer 2 · answered Aug 15 '16 at 19:59

I agree that this question can be considered off-topic, but, for once, it's a real-world problem to solve, not a student question.

Unfortunately, the solution you gave is wrong: you only consider that you could havethe “breakpoints” values. If you give different weight (for instance 21) or kilometer (for instance 55), the function raise un exception:

>>> calc_shipping_cost(20, 50)
98.65

>>> calc_shipping_cost(21, 55)
Traceback (most recent call last):
  File "python", line 1, in <module>
  File "python", line 16, in calc_shipping_cost
ValueError: 21 is not in list

The table says “To 50km”, “To 100km”, etc. So you need a more tolerant function and consider intervals: eg.: [0, 50[, [50, 100[, etc.

To choose the index of a value in an ordered list of intervals, you can consider the Array bisection algorithm. Python has an efficient implementation of this algorithm in the bisect module. It is usually used to calculate the insertion point of an item in an ordered array.

For instance:

>>> import bisect

>>> WEIGHT_BREAKPOINTS = [10, 20, 40, 60, 80, 100, 500]
>>> bisect.bisect_left(WEIGHT_BREAKPOINTS, 10)
0
>>> bisect.bisect_left(WEIGHT_BREAKPOINTS, 40)
2
>>> bisect.bisect_left(WEIGHT_BREAKPOINTS, 25)
2

For the last example, the insertion point of 25 is index 2 (to be inserted before 40 which index is 2 also).

In case of “out of range”, you can raise your own exception or simply a ValueError.

Here is a better implementation:

import bisect

def calc_shipping_cost(weight, kilometer):

    WEIGHT_BREAKPOINTS = [10, 20, 40, 60, 80, 100, 500]
    KILOMETER_BREAKPOINTS = [50, 100, 150, 200, 999]

    prices = [
        [84.85, 95.15, 104.45, 116.70, 122.25],
        [98.65, 108.45, 117.20, 127.95, 134.60],
        [112.40, 121.70, 129.95, 149.30, 153.10],
        [139.95, 148.20, 155.45, 173.10, 177.80],
        [153.70, 167.50, 168.20, 193.20, 196.30],
        [181.25, 188.00, 193.70, 225.85, 227.15],
        [208.80, 214.50, 219.20, 281.00, 282.70],
    ]

    row = bisect.bisect_left(WEIGHT_BREAKPOINTS, weight)
    col = bisect.bisect_left(KILOMETER_BREAKPOINTS, kilometer)

    try:
        return prices[row][col]
    except IndexError:
        raise ValueError(weight, kilometer)

With the following behavior:

>>> calc_shipping_cost(10, 50)
84.85
>>> calc_shipping_cost(10.0, 50)
84.85
>>> calc_shipping_cost(20, 50)
98.65
>>> calc_shipping_cost(21, 55)
121.7
>>> calc_shipping_cost(10.0, 50)
84.85
>>> calc_shipping_cost(500, 50)
208.8
>>> calc_shipping_cost(1000, 50)
Traceback (most recent call last):
  File "python", line 1, in <module>
  File "python", line 24, in calc_shipping_cost
ValueError: (1000, 50)

Hey Laurent. Thanks for your answer. In my 'real' function, I have a "rounding-up" function which would round the value to the next known breakpoint. — Dominic, Aug 16 '16 at 08:40

Python - Best way to lookup values in a table-like scheme

2 Answers2