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