1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
|
======
Lookup
======
Generate new columns by mapping existing data to common `lookup <https://github.com/wireservice/lookup>`_ tables.
CPI deflation
=============
The `agate-lookup <https://github.com/wireservice/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.
.. code-block:: python
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:
.. code-block:: python
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 |
+-------+------+---------+------------+
|
