1=============
2Emulate Excel
3=============
4
5One of agate's most powerful assets is that instead of a wimpy "formula" language, you have the entire Python language at your disposal. Here are examples of how to translate a few common Excel operations.
6
7Simple formulas
8===============
9
10If you need to simulate a simple Excel formula you can use the :class:`.Formula` class to apply an arbitrary function.
11
12Excel:
13
14.. code::
15
16    =($A1 + $B1) / $C1
17
18agate:
19
20.. code-block:: python
21
22    def f(row):
23        return (row['a'] + row['b']) / row['c']
24
25    new_table = table.compute([
26        ('new_column', agate.Formula(agate.Number(), f))
27    ])
28
29If this still isn't enough flexibility, you can also create your own subclass of :class:`.Computation`.
30
31SUM
32===
33
34.. code-block:: python
35
36    number_type = agate.Number()
37
38    def five_year_total(row):
39        columns = ('2009', '2010', '2011', '2012', '2013')
40
41        return sum(tuple(row[c] for c in columns)]
42
43    formula = agate.Formula(number_type, five_year_total)
44
45    new_table = table.compute([
46        ('five_year_total', formula)
47    ])
48
49TRIM
50====
51
52.. code-block:: python
53
54    new_table = table.compute([
55        ('name_stripped', agate.Formula(text_type, lambda r: r['name'].strip()))
56    ])
57
58CONCATENATE
59===========
60
61.. code-block:: python
62
63    new_table = table.compute([
64        ('full_name', agate.Formula(text_type, lambda r: '%(first_name)s %(middle_name)s %(last_name)s' % r))
65    ])
66
67IF
68==
69
70.. code-block:: python
71
72    new_table = table.compute([
73        ('mvp_candidate', agate.Formula(boolean_type, lambda r: row['batting_average'] > 0.3))
74    ])
75
76
77VLOOKUP
78=======
79
80There are two ways to get the equivalent of Excel's VLOOKUP with agate. If your lookup source is another agate :class:`.Table`, then you'll want to use the :meth:`.Table.join` method:
81
82.. code-block:: python
83
84    new_table = mvp_table.join(states, 'state_abbr')
85
86This will add all the columns from the `states` table to the `mvp_table`, where their `state_abbr` columns match.
87
88If your lookup source is a Python dictionary or some other object you can implement the lookup using a :class:`.Formula` computation:
89
90.. code-block:: python
91
92    states = {
93        'AL': 'Alabama',
94        'AK': 'Alaska',
95        'AZ': 'Arizona',
96        ...
97    }
98
99    new_table = table.compute([
100        ('mvp_candidate', agate.Formula(text_type, lambda r: states[row['state_abbr']]))
101    ])
102
103Pivot tables as cross-tabulations
104=================================
105
106Pivot tables in Excel implement a tremendous range of functionality. Agate divides this functionality into a few different methods.
107
108If what you want is to convert rows to columns to create a "crosstab", then you'll want to use the :meth:`.Table.pivot` method:
109
110.. code-block:: python
111
112    jobs_by_state_and_year = employees.pivot('state', 'year')
113
114This will generate a table with a row for each value in the `state` column and a column for each value in the `year` column. The intersecting cells will contains the counts grouped by state and year. You can pass the `aggregation` keyword to aggregate some other value, such as :class:`.Mean` or :class:`.Median`.
115
116Pivot tables as summaries
117=========================
118
119On the other hand, if what you want is to summarize your table with descriptive statistics, then you'll want to use :meth:`.Table.group_by` and :meth:`.TableSet.aggregate`:
120
121.. code-block:: python
122
123    jobs = employees.group_by('job_title')
124    summary = jobs.aggregate([
125        ('employee_count', agate.Count()),
126        ('salary_mean', agate.Mean('salary')),
127        ('salary_median', agate.Median('salary'))
128    ])
129
130The resulting ``summary`` table will have four columns: ``job_title``, ``employee_count``, ``salary_mean`` and ``salary_median``.
131
132You may also want to look at the :meth:`.Table.normalize` and :meth:`.Table.denormalize` methods for examples of functionality frequently accomplished with Excel's pivot tables.
133