Emulate Excel

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

Simple formulas

If you need to simulate a simple Excel formula you can use the Formula class to apply an arbitrary function.

Excel:

=($A1 + $B1) / $C1

agate:

def f(row):
    return (row['a'] + row['b']) / row['c']

new_table = table.compute([
    ('new_column', agate.Formula(agate.Number(), f))
])

If this still isn’t enough flexibility, you can also create your own subclass of Computation.

SUM

number_type = agate.Number()

def five_year_total(row):
    columns = ('2009', '2010', '2011', '2012', '2013')

    return sum(tuple(row[c] for c in columns)]

formula = agate.Formula(number_type, five_year_total)

new_table = table.compute([
    ('five_year_total', formula)
])

TRIM

new_table = table.compute([
    ('name_stripped', agate.Formula(text_type, lambda r: r['name'].strip()))
])

CONCATENATE

new_table = table.compute([
    ('full_name', agate.Formula(text_type, lambda r: '%(first_name)s %(middle_name)s %(last_name)s' % r))
])

IF

new_table = table.compute([
    ('mvp_candidate', agate.Formula(boolean_type, lambda r: row['batting_average'] > 0.3))
])

VLOOKUP

There are two ways to get the equivalent of Excel’s VLOOKUP with agate. If your lookup source is another agate Table, then you’ll want to use the Table.join() method:

new_table = mvp_table.join(states, 'state_abbr')

This will add all the columns from the states table to the mvp_table, where their state_abbr columns match.

If your lookup source is a Python dictionary or some other object you can implement the lookup using a Formula computation:

states = {
    'AL': 'Alabama',
    'AK': 'Alaska',
    'AZ': 'Arizona',
    ...
}

new_table = table.compute([
    ('mvp_candidate', agate.Formula(text_type, lambda r: states[row['state_abbr']]))
])

Pivot tables as cross-tabulations

Pivot tables in Excel implement a tremendous range of functionality. Agate divides this functionality into a few different methods.

If what you want is to convert rows to columns to create a “crosstab”, then you’ll want to use the Table.pivot() method:

jobs_by_state_and_year = employees.pivot('state', 'year')

This 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 Mean or Median.

Pivot tables as summaries

On the other hand, if what you want is to summarize your table with descriptive statistics, then you’ll want to use Table.group_by() and TableSet.aggregate():

jobs = employees.group_by('job_title')
summary = jobs.aggregate([
    ('employee_count', agate.Count()),
    ('salary_mean', agate.Mean('salary')),
    ('salary_median', agate.Median('salary'))
])

The resulting summary table will have four columns: job_title, employee_count, salary_mean and salary_median.

You may also want to look at the Table.normalize() and Table.denormalize() methods for examples of functionality frequently accomplished with Excel’s pivot tables.