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
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 |