Column Level Formulae Example: Data Aggregation with List Comprehensions

Row and Column Level Formulae allow you to apply a formula to every row or column in a worksheet.

They avoid duplication and make it simpler to add new data. They're also surprisingly flexible. For example, one of our screencasts demonstrates how you can combine them with list comprehensions to perform data aggregation.

This article works through the example presented in the screencast:

Note

For more detail on the syntax for creating these formulae, see Row and Column Level Formulae.

The workbook

The raw data for the Column level formulae example workbook

The workbook ColumnLevelFormulae.rsl contains three worksheets:

Employees
Employees along with their age, country and job.
Countries
All the different countries where our employees work.
Jobs
The roles performed by our employees.

Our goal is to turn the Countries and Jobs worksheets into reports.

The Countries worksheet

We start off in the Countries worksheet by adding a formula to the People column...

1. Enter the column formula

  • Select the whole column by clicking on the header cell and then type the formula into the formula bar:

    =[person for person in <People>.ContentRows if person['Country'] == #Name#_]

This creates a list comprehension that filters all rows from the People worksheet (iterating over ContentRows which skips the header row) where their Country matches the one specified in the Countries Name column.

Because this formula is executed for every row, it returns a list of rows, where every row represents a person:

Aggregating rows of data from the People worksheet

Unfortunately the default representation of a row is not exactly intuitive, but we can improve that...

2. Add a custom formatter

We can use a custom formatter to improve the People column by making it show the name of the person each row represents:

  • In the Pre-formulae user code, add:
# Pre-formulae user code
def format_rows(value, defaultFormatter):
    if isinstance(value, list):
        return '[' + ', '.join('<%s>' % row['Name'] for row in value) + ']'
    return defaultFormatter(value)
workbook['Countries'].Cols['People'].Formatter = format_rows
workbook['Jobs'].Cols['People'].Formatter = format_rows

With the formatter in place we get a much friendlier representation of the rows:

Nicely present lists of rows using a custom formatter

Now that we have a list of people for each row we can do something useful with it...

3. Add statistics

In the Number of People column we simply want to know how many people are from that country, so we set the column formula to be the length of the list:

=len(#People#_)
Going from the list of people to the number of people

To work out the Average Age we can pull out the Age from each person (again using headers to keep the formula readable) and use the AVERAGE function in the column formula:

=AVERAGE(person['Age'] for person in #People#_)
Using the AVERAGE function in a column formula

The Jobs worksheet

The Jobs worksheet needs similar improvements. We are aggregating by job instead of by country, so the you need to...

  • Modify the example formula in the People column to aggregate by job instead of country
  • Reuse the example formulae in the other two columns.

Comments