lookup.rst

Lookup

Generate new columns by mapping existing data to common lookup tables.

CPI deflation

The agate-lookup extension adds a lookup method to agate's Table class.

Starting with a table that looks like this:

year	cost
1995	2.0
1997	2.2
1996	2.3
2003	4.0
2007	5.0
2005	6.0

We can map the year column to its annual CPI index in one lookup call.

import agatelookup

agatelookup.patch()

join_year_cpi = table.lookup('year', 'cpi')

The return table will have now have a new column:

year	cost	cpi
1995	2.0	152.383
1997	2.2	160.525
1996	2.3	156.858
2003	4.0	184.000
2007	5.0	207.344
2005	6.0	195.267

A simple computation tacked on to this lookup can then get the 2015 equivalent values of each cost:

cpi_2015 = Decimal(216.909)

def cpi_adjust_2015(row):
    return (row['cost'] * (cpi_2015 / row['cpi'])).quantize(Decimal('0.01'))

cost_2015 = join_year_cpi.compute([
    ('cost_2015', agate.Formula(agate.Number(), cpi_adjust_2015))
])

And the final table will look like this:

year	cost	cpi	cost_2015
1995	2.0	152.383	2.85
1997	2.2	160.525	2.97
1996	2.3	156.858	3.18
2003	4.0	184.000	4.72
2007	5.0	207.344	5.23
2005	6.0	195.267	6.66