Aggregating Values

This sample is saved as Aggregating values in the Samples subfolder of the Resolver One folder on your Start Menu.

The Aggregate function is a powerful way of aggregating data in worksheets. For full details of how to use the Aggregate function, see the Functions module page in the Library documentation.

Aggregate provides an easy way of summarising data from one worksheet into another worksheet. It is similar to, but more powerful than, the pivot tables you might be familiar with from other spreadsheets.

The example takes a set of transactions in the 'Data' worksheets and puts several different summaries into the other worksheets.

The sample data represents a set of transactions, where each transaction has an 'ID'. We want to count the number of transactions for each ID, the sum of all transactions for each ID and the total (sells minus buys) for each ID.

Aggregate takes the worksheet to aggregate from and the worksheet to aggregate to, plus the column we are aggregating on ('ID').

The final argument is a list of tuples. Each tuple has a column name, plus the function to generate this column in the worksheet that is aggregated ''into''.

# You can pass many functions at one time
columns = [("Occurences", len), ("Sum", SumUp), ("Totals", CountTotals)]
Aggregate(workbook["Data"],
    workbook["Double"],
    "ID",
    columns)

This aggregates the results into the 'Double' worksheet. Aggregate goes through all the entries in the 'Data' worksheet and collects all the entries with the same ID.

Each of the functions in the list is then called, for every ID, to produce the columns in 'Data'.

Let's look at one of these, the middle function 'SumUp':

# Summing up some values
def SumUp(rows):
    ret = 0
    for row in rows:
        ret += row["Amount"]
    return ret

This produces the "Sum" column in Data. This function is called with lists of rows. Every time it is called, each row in the list will have the same ID. To calculate the sum we need to add up the "Amount" entry for each row.

You can see in the 'Data' worksheet that it has a header row, plus one row per ID. The sum for each ID has been calulcated and places in the 'Sum' column.

Back to Sample Spreadsheets.