This is orgtbl-aggregate.info, produced by makeinfo version 6.8 from
orgtbl-aggregate.texi.


INFO-DIR-SECTION Emacs
START-INFO-DIR-ENTRY
* Orgtbl Aggregate: (orgtbl-aggregate).     Create an aggregated Org table from another one
END-INFO-DIR-ENTRY

INFO-DIR-SECTION Misc
START-INFO-DIR-ENTRY
* (orgtbl-aggregate).   Aggregate Values in a Table.
END-INFO-DIR-ENTRY


File: orgtbl-aggregate.info,  Node: Top,  Next: New,  Up: (dir)

Aggregate Values in a Table
***************************

Aggregating a table is creating a new table by computing sums, averages,
and so on, out of material from the first table.

* Menu:

* New::
* Examples::
* Stop reading here! 80/20::
* Equivalent in SQL, R, Datamash, el-tblfn, Awk, C++: Equivalent in SQL R Datamash el-tblfn Awk C++.
* Wizards::
* Thecols parameter: The cols parameter.
* Column names::
* Formatters::
* Sorting::
* hlines in the output table::
* Cells processing::
* Wide variety of inputs::
* Post-processing::
* Pull & Push::
* Debugging::
* Tricks::
* Installation::
* Authors, contributors: Authors contributors.
* Changes::
* GPL 3 License::

— The Detailed Node Listing —

Examples

* A very simple example::
* Demonstrate sum and average computing::
* Example without days::
* Example of counting each combination::

Stop reading here! 80/20

* Name your input table::
* Create an aggregation block::
* Refresh the aggregation::

Equivalent in SQL, R, Datamash, el-tblfn, Awk, C++

* SQL equivalent::
* R equivalent::
* Datamash equivalent::
* el-tblfn::
* Awk equivalent::
* C++ equivalent::

Wizards

* Guiding (traditional) wizard::
* Experimental free form wizard::

The :cols parameter

* Names of input columns::
* Grouping specifications incols: Grouping specifications in cols.
* The hline column::
* The @# column::
* Aggregation formulas incols: Aggregation formulas in cols.
* Correlation of two columns::
* (Almost) any expression can be specified: [Almost) any expression can be specified.

Column names

* Input table with or without a header::
* Column names of the input table::
* Multiple lines header::
* Custom column names::

Formatters

* Org Mode compatible formatters::
* Debugging formatters::
* Alignment cookies::
* Discarding an output column::

Sorting

* Example with one sorting column::
* Several sorting columns::

hlines in the output table

* Output hlines depends on sorting columns::
* Example with hline 2::

Cells processing

* Where Calc interpretation happens?::
* Dates::
* Durations::
* Empty and malformed input cells::
* Symbolic computation::
* Intervals::
* Error or precision forms::

Wide variety of inputs

* Standard Org Mode input::
* Virtual input table from Babel::
* An Org ID::
* CSV input::
* JSON input::
* Input slicing::
* Malformed input tables::
* Thecond filter: The cond filter.
* Virtual input columns with precompute::

Post-processing

* Spreadsheet formulas::
* Algorithm post processing::
* Grand total::
* Chaining::

Pull & Push

* Pull mode::
* Push mode::
* Pull or push ?::

Debugging

* Seeing the $ forms::
* Seeing Calc formulas before evaluation::
* Seeing Lisp internal form of Calc formulas::
* Example of debugging vsum(nn^2)::
* Summary of debugging formatters::

Tricks

* Sorting: Sorting (1).
* A few lowest or highest values::
* Span of values::
* No aggregation::
* Look at the first rows of a large table::



File: orgtbl-aggregate.info,  Node: New,  Next: Examples,  Prev: Top,  Up: Top

1 New
*****

Output columns can now be aligned with the ‘<c>’, ‘<l>’, ‘<r>’ cookies.
They have the same meaning as in Org Mode.

   Previously, only the ‘<>’ cookie was available to make a column
invisible.

   See *note Alignment cookies::.


File: orgtbl-aggregate.info,  Node: Examples,  Next: Stop reading here! 80/20,  Prev: New,  Up: Top

2 Examples
**********

* Menu:

* A very simple example::
* Demonstrate sum and average computing::
* Example without days::
* Example of counting each combination::


File: orgtbl-aggregate.info,  Node: A very simple example,  Next: Demonstrate sum and average computing,  Up: Examples

2.1 A very simple example
=========================

We have a table of activities and quantities (whatever they are) over
several days.

     #+name: original
     | Day       | Color | Level | Quantity |
     |-----------+-------+-------+----------|
     | Monday    | Red   |    30 |       11 |
     | Monday    | Blue  |    25 |        3 |
     | Tuesday   | Red   |    51 |       12 |
     | Tuesday   | Red   |    45 |       15 |
     | Tuesday   | Blue  |    33 |       18 |
     | Wednesday | Red   |    27 |       23 |
     | Wednesday | Blue  |    12 |       16 |
     | Wednesday | Blue  |    15 |       15 |
     | Thursday  | Red   |    39 |       24 |
     | Thursday  | Red   |    41 |       29 |
     | Thursday  | Red   |    49 |       30 |
     | Friday    | Blue  |     7 |        5 |
     | Friday    | Blue  |     6 |        8 |
     | Friday    | Blue  |    11 |        9 |

   To begin with we want to gather all colors and count how many times
they appear.  We are interested only in the second column named ‘Color’

   First we give a name to the table through the ‘#+NAME:’ or
‘#+TBLNAME:’ tags, just above the table.  Then we create a _dynamic
block_ to receive the aggregation:

     desired output columns╶──────────────────────────╮
     the input table╶──────────────╮                  │
     type of processing╶─╮         │                  │
                 ╭───────╯         │                  │
                 ▼             ╭───┴────╮       ╭─────┴───────╮
     #+begin: aggregate :table "original" :cols "Color count()"
     #+end:

   Now typing ‘C-c C-c’ in the dynamic block counts the colors in the
original table:

     C-c C-c here to refresh╶╮
                  ╭──────────╯
                  ▼
     #+begin: aggregate :table "original" :cols "Color count()"
     | Color | count() |
     |-------+---------|
     | Red   |       7 |
     | Blue  |       7 |
     #+end:

   OrgAggregate found two colors, ‘Red’ and ‘Blue’.  It found 7
occurrences for each.


File: orgtbl-aggregate.info,  Node: Demonstrate sum and average computing,  Next: Example without days,  Prev: A very simple example,  Up: Examples

2.2 Demonstrate sum and average computing
=========================================

Now we want to aggregate this table for each day (because several rows
exist for each day).  We want the average value of the ‘Level’ column
for each day, and the sum of the ‘Quantity’ column.  We write down the
block specifying that (later we will see how to automate the creation of
such a block with a *note wizard: Wizards.):

     sum aggregation╶─────────────────────────────────────────────────╮
     average aggregation╶───────────────────────────────╮             │
     key grouping column╶───────────────────────╮       │             │
                                               ╭┴╮ ╭────┴─────╮ ╭─────┴──────╮
     #+begin: aggregate :table original :cols "Day vmean(Level) vsum(Quantity)"
     #+end

   Typing ‘C-c C-c’ in the dynamic block computes the aggregation:

     C-c C-c here to refresh╶╮
                  ╭──────────╯
                  ▼
     #+begin: aggregate :table original :cols "Day vmean(Level) vsum(Quantity)"
                                               ╰┬╯ ╰────┬─────╯ ╰──────┬─────╯
        ╭───────────────────────────────────────╯       │              │
        │               ╭───────────────────────────────╯              │
        │               │               ╭──────────────────────────────╯
       ╭┴╮         ╭────┴─────╮   ╭─────┴──────╮
     | Day       | vmean(Level) | vsum(Quantity) |
     |-----------+--------------+----------------|
     | Monday    |         27.5 |             14 |
     | Tuesday   |           43 |             45 |
     | Wednesday |           18 |             54 |
     | Thursday  |           43 |             83 |
     | Friday    |            8 |             22 |
     #+end

   The source table is not changed in any way.

   To get this result, we specified columns in this way, after the
‘:cols’ parameter:

   • ‘Day’ : we got the same column as in the source table, except
     entries are not duplicated.  Here ‘Day’ acts as a _key grouping
     column_.  We may specify as many key columns as we want just by
     naming them.  We get only one aggregated row for each different
     combination of values of key grouping columns.

   • ‘vmean(Level)’ : this instructs OrgAggregate to compute the average
     of values found in the ‘Level’ column, grouped by the same ‘Day’.

   • ‘vsum(Quantity)’: OrgAggregate computes the sum of values found in
     the ‘Quantity’ column, one sum for each ‘Day’.


File: orgtbl-aggregate.info,  Node: Example without days,  Next: Example of counting each combination,  Prev: Demonstrate sum and average computing,  Up: Examples

2.3 Example without days
========================

Maybe we are just interested in the sum of ‘Quantities’, regardless of
‘Days’.  We just type:

     C-c C-c here to refresh╶╮
                  ╭──────────╯
                  ▼
     #+begin: aggregate :table "original" :cols "vsum(Quantity)"
                                                 ╰─────┬──────╯
            ╭──────────────────────────────────────────╯
       ╭────┴───────╮
     | vsum(Quantity) |
     |----------------|
     |            218 |
     #+end


File: orgtbl-aggregate.info,  Node: Example of counting each combination,  Prev: Example without days,  Up: Examples

2.4 Example of counting each combination
========================================

we may want to count the number of rows for each combination of ‘Day’
and ‘Color’:

     C-c C-c here to refresh╶╮
                  ╭──────────╯
                  ▼
     #+BEGIN: aggregate :table "original" :cols "count() Day Color"
                                                 ╰──┬──╯ ╰┬╯ ╰─┬─╯
          ╭─────────────────────────────────────────╯     │    │
          │       ╭───────────────────────────────────────╯    │
          │       │            ╭───────────────────────────────╯
       ╭──┴──╮   ╭┴╮         ╭─┴─╮
     | count() | Day       | Color |
     |---------+-----------+-------|
     |       1 | Monday    | Red   |
     |       1 | Monday    | Blue  |
     |       2 | Tuesday   | Red   |
     |       1 | Tuesday   | Blue  |
     |       1 | Wednesday | Red   |
     |       2 | Wednesday | Blue  |
     |       3 | Thursday  | Red   |
     |       3 | Friday    | Blue  |
     #+END

   If we want to get measurements for ‘Colors’ rather than ‘Days’, we
type:

     C-c C-c here to refresh╶╮
                  ╭──────────╯
                  ▼
     #+begin: aggregate :table "original" :cols "Color vmean(Level) vsum(Quantity)"
                                                 ╰─┬─╯ ╰────┬─────╯ ╰─────┬──────╯
         ╭─────────────────────────────────────────╯        │             │
         │           ╭──────────────────────────────────────╯             │
         │           │               ╭────────────────────────────────────╯
       ╭─┴─╮    ╭────┴─────╮   ╭─────┴──────╮
     | Color |  vmean(Level) | vsum(Quantity) |
     |-------+---------------+----------------|
     | Red   | 40.2857142857 |            144 |
     | Blue  | 15.5714285714 |             74 |
     #+end


File: orgtbl-aggregate.info,  Node: Stop reading here! 80/20,  Next: Equivalent in SQL R Datamash el-tblfn Awk C++,  Prev: Examples,  Up: Top

3 Stop reading here! 80/20
**************************

If you managed to get here, you are at 80/20 (thanks Pareto!).  You
grasped only 20% of the OrgAggregate features, but those 20% cover 80%
of the use cases.

   To summarize the 20%:

* Menu:

* Name your input table::
* Create an aggregation block::
* Refresh the aggregation::


File: orgtbl-aggregate.info,  Node: Name your input table,  Next: Create an aggregation block,  Up: Stop reading here! 80/20

3.1 Name your input table
=========================

   • Select one of your Org table, and be ready to aggregate values from
     it right in the same file.

   • Give a name to your table with a special line just above it.

     name here╶───╮
             ╭────╯
             ▼
     #+name: original
     | Day       | Color | Level | Quantity |
     |-----------+-------+-------+----------|
     | Monday    | Red   |    30 |       11 |
     | Monday    | Blue  |    25 |        3 |
     …


File: orgtbl-aggregate.info,  Node: Create an aggregation block,  Next: Refresh the aggregation,  Prev: Name your input table,  Up: Stop reading here! 80/20

3.2 Create an aggregation block
===============================

     #+begin: aggregate
     #+end:

   • *Input:* specify a ‘:table’ parameter.
   • *Output:* specify the desired output columns with the ‘:cols’
     parameter.

     output╶───────────────────────────────────────────╮
     input╶───────────────────────╮                    │
                                  │                    │
                               ╭──┴───╮       ╭────────┴─────────╮
     #+begin: aggregate :table original :cols "Day vsum(Quantity)"
     #+end:


File: orgtbl-aggregate.info,  Node: Refresh the aggregation,  Prev: Create an aggregation block,  Up: Stop reading here! 80/20

3.3 Refresh the aggregation
===========================

   • Type ‘C-c C-c’ on the ‘#+begin:’ line now and whenever you want to
     refresh the aggregation.

     C-c C-c here╶─╮
                   │
                   ▼
     #+begin: aggregate :table original :cols "Day vsum(Quantity)"
     | Day       | vsum(Quantity) |
     |-----------+----------------|
     | Monday    |             14 |
     | Tuesday   |             45 |
     …
     #+end:

   The block may contain additional information beside the old table.
Example:

     #+begin: aggregate :table original :cols "Day vsum(Quantity)"
     note: my table should include the "Year" column.
     #+name: my_aggregated-table
     | Day     | vsum(Quantity) |      |
     |---------+----------------+------|
     | Monday  |             14 | 1400 |
     | Tuesday |             45 | 4500 |
     #+tblfm: $3=$2*100
     #+plot: …
     Note: change the style of the plot.
     #+end:

   In this example, the block contains tags like ‘#+name:’ and free text
like "note: my table…".  All that is preserved upon refresh (‘C-c C-c’)
or wizard update (‘C-c C-x x’) (see *note Guiding (traditional)
wizard::).  Blank lines are removed thought.

   The ‘#+tblfm:’ tag is special.  It is preserved of course.  It may
also be enriched by a possible ‘:formula’ parameter (see *note
Spreadsheet formulas: Spreadsheet formulas.).  The new table computed by
aggregation is then updated by the spreadsheet formulas.


File: orgtbl-aggregate.info,  Node: Equivalent in SQL R Datamash el-tblfn Awk C++,  Next: Wizards,  Prev: Stop reading here! 80/20,  Up: Top

4 Equivalent in SQL, R, Datamash, el-tblfn, Awk, C++
****************************************************

Aggregation is a widely used method to get insights in tabular data.
Use whatever environment best suits your needs.

   OrgAggregate is great when you want to output an Org Mode table.
Also, OrgAggregate has no dependency other than Emacs, not even other
Lisp packages.

* Menu:

* SQL equivalent::
* R equivalent::
* Datamash equivalent::
* el-tblfn::
* Awk equivalent::
* C++ equivalent::


File: orgtbl-aggregate.info,  Node: SQL equivalent,  Next: R equivalent,  Up: Equivalent in SQL R Datamash el-tblfn Awk C++

4.1 SQL equivalent
==================

If you are familiar with SQL, you would get a similar result with the
‘GROUP BY’ statement:

     select Day, mean(Level), sum(Quantity)
     from original
     group by Day;


File: orgtbl-aggregate.info,  Node: R equivalent,  Next: Datamash equivalent,  Prev: SQL equivalent,  Up: Equivalent in SQL R Datamash el-tblfn Awk C++

4.2 R equivalent
================

If you are familiar with the R statistical language, you would get a
similar result with ‘factor’ and ‘aggregate’ functions:

     original <- the table as a data.frame
     day_factor <- factor(original$Day)
     aggregate (original$Level   , list(Day=day_factor), mean)
     aggregate (original$Quantity, list(Day=day_factor), sum )


File: orgtbl-aggregate.info,  Node: Datamash equivalent,  Next: el-tblfn,  Prev: R equivalent,  Up: Equivalent in SQL R Datamash el-tblfn Awk C++

4.3 Datamash equivalent
=======================

The command-line Datamash software operates on CSV files and can achieve
a similar result:

     datamash -H -g Day mean Level sum Quantity <original.csv
     GroupBy(Day)  mean(Level)  sum(Quantity)
     Monday        27.5         14
     Tuesday       43           45
     Wednesday     18           54
     Thursday      43           83
     Friday         8           22


File: orgtbl-aggregate.info,  Node: el-tblfn,  Next: Awk equivalent,  Prev: Datamash equivalent,  Up: Equivalent in SQL R Datamash el-tblfn Awk C++

4.4 el-tblfn
============

The Misohena’s ‘el-tblfn’ package aggregates Org Mode tables through
Lisp scripts.  See <https://github.com/misohena/el-tblfn>.  Example:

     #+begin_src elisp :var original=original :colnames no :hlines yes
     (require 'tblfn)
     (tblfn-join
      (tblfn-aggregate original "Day" "Level" "vmean")
      (tblfn-aggregate original "Day" "Quantity")
      "Day")
     #+end_src

   Two separate aggregations are performed, then they are joined
together.  As the ‘el-tblfn’ package is quite young, this is subject to
changes in the future.


File: orgtbl-aggregate.info,  Node: Awk equivalent,  Next: C++ equivalent,  Prev: el-tblfn,  Up: Equivalent in SQL R Datamash el-tblfn Awk C++

4.5 Awk equivalent
==================

Awk is a line-oriented filter which has been arround in Unix for
decades.  Here we use the standard Org Mode support for Awk.

     #+begin_src awk :stdin original
     NR>1 {
          Day         =$1
          Color       =$2
          Level       =$3
          Quantity    =$4
          SumLevel[Day]    += Level
          SumQuantity[Day] += Quantity
          Count[Day]       ++
     }
     END {
         for (d in SumQuantity) {
             printf "%s %s %s\n", d, SumLevel[d]/Count[d], SumQuantity[d]
         }
     }
     #+end_src


File: orgtbl-aggregate.info,  Node: C++ equivalent,  Prev: Awk equivalent,  Up: Equivalent in SQL R Datamash el-tblfn Awk C++

4.6 C++ equivalent
==================

C++ has hash-maps in its standard template library.  And Org Mode
provides support for C++ Babel blocks.  Thus, it is quite
straigthforward to aggegrate in this language.

   (Don’t forget to customize ‘org-src-lang-modes’ to activate C++
support in Org Mode).

     #+begin_src C++ :var original=original :includes '(<iostream> <unordered_map>)
     using namespace std;
     unordered_map<string,double> SumLevel;
     unordered_map<string,double> SumQuantity;
     unordered_map<string,double> Count;
     for (auto row : original) {
       auto Day      = row[0];
       auto Color    = row[1];
       auto Level    = stod(row[2]);
       auto Quantity = stod(row[3]);
       SumLevel[Day]    += Level;
       SumQuantity[Day] += Quantity;
       Count[Day]       ++;
     }
     for (auto it : SumQuantity)
       cout<<it.first<<" "
           <<SumLevel[it.first]/Count[it.first]<<" "
           <<SumQuantity[it.first]<<"\n";
     #+end_src


File: orgtbl-aggregate.info,  Node: Wizards,  Next: The cols parameter,  Prev: Equivalent in SQL R Datamash el-tblfn Awk C++,  Up: Top

5 Wizards
*********

There are now 2 wizards.  Use whichever is easier for you.

* Menu:

* Guiding (traditional) wizard::
* Experimental free form wizard::


File: orgtbl-aggregate.info,  Node: Guiding (traditional) wizard,  Next: Experimental free form wizard,  Up: Wizards

5.1 Guiding (traditional) wizard
================================

Type ‘C-c C-x x’ to launch a wizard for creating new dynamic blocks, or
amending existing ones.  Then answer ‘aggregate’ for the type of block,
and follow the instructions.  (There are several other _dynamic blocks_
that can be built this way: ‘columnview’, ‘clocktable’, ‘propview’,
‘invoice’, ‘transpose’, and any future block).

   There are two modes:
   • basic, ‘C-c C-x x’, ‘M-x orgtbl-aggregate-insert-dblock-aggregate’
   • expert, ‘C-u C-c C-x x’, ‘C-u M-x
     orgtbl-aggregate-insert-dblock-aggregate’

   The basic mode only queries for an input table in the current buffer
and the output columns.  Of course, if the wizard updates an existing
block with exotic parameters, those parameters will be queried even in
basic mode.

   The expert mode queries every parameter.

   The wizard has been enhanced & extended.

   It can update an existing block.  Put the cursor on a line beginning
with ‘#+begin: aggregate …’ and type ‘C-c C-x x’.  The wizard will use
values picked from this line as defaults for user queries.

   The wizard now takes into account the multiple forms of input tables.
Tables may lie in distant files as well as in the current buffer.  They
may be computed on the fly by Babel blocks as well as regular Org Mode
tables.  They may also be pointed to by Org IDs.

   Regular Org Mode table slicing is now recognized.

   Pre & post processors are now handled by the wizard.

   The ‘:hline’ parameter is taken into account.

   Most of the wizard’s queries may be answered by just hitting RETURN.
This is because many parameters are optional.  And because when amending
an existing block, the wizard suggests the old values as the default
answers.

   When invoking the wizard, a small window opens to display help
relative to each field the wizard queries.  This help window is in Org
Mode for an easy reading.  The window will be closed when the wizard is
done or when aborting.


File: orgtbl-aggregate.info,  Node: Experimental free form wizard,  Prev: Guiding (traditional) wizard,  Up: Wizards

5.2 Experimental free form wizard
=================================

Suppose we have an aggregate block like this one:

     the 2 main parameters╶────────╮────────────────────╮
                                   │                    │
                               ╭───┴────╮       ╭───────┴───────╮
     #+begin: aggregate :table "my_table" :cols "vsum(b) count()" :hline "1"
     #+end:

   Then, hitting the ‘TAB’ key anywhere on the ‘#+begin:’ line (but not
at the beginning) unfolds it.  The result is this one:

     #+begin: aggregate                   ╭─╴the 2 main parameters
     #+aggregate: :file                   │
     #+aggregate: :name my_table        ◀─╯
     #+aggregate: :orgid                  │
     #+aggregate: :params                 │
     #+aggregate: :slice                  │
     #+aggregate: :precompute             │
     #+aggregate: :cols vsum(b) count() ◀─╯
     #+aggregate: :cond
     #+aggregate: :hline 1
     #+aggregate: :post
     #+end:

   This unfolded form is easier to read.  Furthermore, help and
completion is available on each line, by hitting the ‘TAB’ key.

   Hit ‘TAB’ again on the ‘#+begin:’ line (not at the beginning) to fold
back the form.  Then hit ‘C-c C-c’ as usual to refresh the aggregation.

   The usual behaviour of Org Mode ‘TAB’ is preserved when the ‘TAB’ key
is pressed at the beginning of line.

   All fields recognized by OrgAggregate are displayed, even when they
are empty.  The empty fields will be ignored when folding back.  This
allows to grasp all the parameters in a single eyesight.

   All in all, this new wizard covers the same features as the
traditional, guiding wizard.  Except, one can fill the fields in any
order, and just ignore the unnecessary ones.


File: orgtbl-aggregate.info,  Node: The cols parameter,  Next: Column names,  Prev: Wizards,  Up: Top

6 The :cols parameter
*********************

The ‘:cols’ parameter lists the columns of the resulting table.  It
contains in any order, grouping key columns and aggregation formulas.
The choosen order will be reflected in the output table.

* Menu:

* Names of input columns::
* Grouping specifications incols: Grouping specifications in cols.
* The hline column::
* The @# column::
* Aggregation formulas incols: Aggregation formulas in cols.
* Correlation of two columns::
* (Almost) any expression can be specified: [Almost) any expression can be specified.


File: orgtbl-aggregate.info,  Node: Names of input columns,  Next: Grouping specifications in cols,  Up: The cols parameter

6.1 Names of input columns
==========================

The names of the columns in the original table may be:
   • the names as they appear in the header of the source table,
   • or ‘$1’, ‘$2’, ‘$3’ and so on (as in spreadsheet formulas),
   • additionally, the special column ‘hline’ is used to group parts of
     the source table separated by horizontal lines.
   • the special ‘@#’ column, not in the input table, is the row number.

   The ‘:cols’ parameter may be a string or a list of strings.
Examples:

     :cols "Day vmean(Level);f3 vsum(Quantity);f2"
     :cols ("Day" "vmean(Level);f3" "vsum(Quantity);f2")

   If a single string is used, it is split by spaces.  Thus, a given
formula, including its semicolon and modifiers, must not contain any
space.  If spaces are required within a formula, then use the
parenthesis list.  If a column name has spaces, quote it like this:

     'yellow submarine'


File: orgtbl-aggregate.info,  Node: Grouping specifications in cols,  Next: The hline column,  Prev: Names of input columns,  Up: The cols parameter

6.2 Grouping specifications in :cols
====================================

Grouping is done on columns of the source table acting as key columns.
Just name the key columns.

   Additionally, the ‘hline’ specification means that rows between two
horizontal lines should be grouped.

   Key columns and ‘hline’ are used to group rows of the source table
with unique combinations of those columns.


File: orgtbl-aggregate.info,  Node: The hline column,  Next: The @# column,  Prev: Grouping specifications in cols,  Up: The cols parameter

6.3 The hline column
====================

hline = "horizontal line"

   The special column named ‘hline’ gives the block number in the source
table.  A block is the rows separated by horizontal lines in the input
table.  The first block is numbered ‘0’.  It is a virtual column in the
sense that it is not a column in the input table.  Other than that, it
is usable as any real column.

   Here is a source table containing 3 blocks separated by horizontal
lines:

     #+name: originalhl
     | Color | Level | Quantity |
     |-------+-------+----------| ╶╮
     | Red   |    30 |       11 |  │
     | Blue  |    25 |        3 |  ├─first block, n° 0
     | Red   |    51 |       12 |  │
     | Red   |    45 |       15 |  │
     | Blue  |    33 |       18 | ╶╯
     |-------+-------+----------| ╶╮
     | Red   |    27 |       23 |  │
     | Blue  |    12 |       16 |  ├─second block, n° 1
     | Blue  |    15 |       15 |  │
     | Red   |    39 |       24 |  │
     | Red   |    41 |       29 | ╶╯
     |-------+-------+----------| ╶╮
     | Red   |    49 |       30 |  │
     | Blue  |     7 |        5 |  ├─third block, n° 2
     | Blue  |     6 |        8 |  │
     | Blue  |    11 |        9 | ╶╯

   And here is the aggregation by those 3 blocks:

     #+BEGIN: aggregate :table originalhl :cols "hline vmean(Level) vsum(Quantity)"
     | hline | vmean(Level) | vsum(Quantity) |
     |-------+--------------+----------------|
     |     0 |         36.8 |             59 | ◀─╴first block , n° 0
     |     1 |         26.8 |            107 | ◀─╴second block, n° 1
     |     2 |        18.25 |             52 | ◀─╴third block , n° 2
     #+END:

   If we want additional details with the ‘Color’ column, we just name
it:

     #+begin: aggregate :table originalhl :cols "hline Color vmean(Level) vsum(Quantity)"
     | hline | Color |  vmean(Level) | vsum(Quantity) |
     |-------+-------+---------------+----------------|
     |     0 | Red   |            42 |             38 | ╶┬──╴first block , n° 0
     |     0 | Blue  |            29 |             21 | ╶╯
     |     1 | Red   | 35.6666666667 |             76 | ╶┬──╴second block, n° 1
     |     1 | Blue  |          13.5 |             31 | ╶╯
     |     2 | Red   |            49 |             30 | ╶┬──╴third block , n° 2
     |     2 | Blue  |             8 |             22 | ╶╯
     #+end:

   There is an ugly value, ‘35.6666666667’, in the middle of the table.
See later how to format it (*note Org Mode compatible formatters::).


File: orgtbl-aggregate.info,  Node: The @# column,  Next: Aggregation formulas in cols,  Prev: The hline column,  Up: The cols parameter

6.4 The @# column
=================

The special column named ‘@#’ gives the row number in the source table.
The ‘@#’ name is the same as in the Org table spreadsheet formulas.  It
is a virtual column in the sense that it does not appear in the input
table.  Other than that, it is usable as any real column.

   Example: we compute the average of the row number for colors ‘Red’ &
‘Blue’.  This gives a sense of whether each colors appears rather at the
beginning or the end of the table.

     #+begin: aggregate :table "original" :cols "Color vmean(@#)"
     | Color | vmean(@#) |
     |-------+-----------|
     | Red   | 6.2857143 |
     | Blue  | 8.7142857 |
     #+end:

   Example: we recover the input table in reverse order.  We use ‘@#’ as
a key column sorted in decreasing order with ‘;^N’.  Then we make it
invisible with ‘;<>’ so that it does not appear in the output.

                             invisible╶─────────────────────────────────────────╮
                             sorted numerically decreasing╶───────────────────╮ │
                             row numbers of input table╶──────────────────╮   │ │
                                                                          │   │ │
                                                                          ▼   ▼ ▼
     #+begin: aggregate :table "original" :cols "Day Color Level Quantity @#;^N;<>"
     | Day       | Color | Level | Quantity |
     |-----------+-------+-------+----------|
     | Friday    | Blue  |    11 |        9 |
     | Friday    | Blue  |     6 |        8 |
     | Friday    | Blue  |     7 |        5 |
     | Thursday  | Red   |    49 |       30 |
     | Thursday  | Red   |    41 |       29 |
     | Thursday  | Red   |    39 |       24 |
     | Wednesday | Blue  |    15 |       15 |
     | Wednesday | Blue  |    12 |       16 |
     | Wednesday | Red   |    27 |       23 |
     | Tuesday   | Blue  |    33 |       18 |
     | Tuesday   | Red   |    45 |       15 |
     | Tuesday   | Red   |    51 |       12 |
     | Monday    | Blue  |    25 |        3 |
     | Monday    | Red   |    30 |       11 |
     #+end:


File: orgtbl-aggregate.info,  Node: Aggregation formulas in cols,  Next: Correlation of two columns,  Prev: The @# column,  Up: The cols parameter

6.5 Aggregation formulas in :cols
=================================

Aggregation formulas are applied for each of the groupings, on the
specified columns.

   We saw examples with ‘sum’, ‘mean’, ‘count’ aggregations.  There are
many other aggregations.  They are based on functions provided by Calc
(Calc is the powerful Emacs calculator):

   • ‘count()’ or ‘vcount()’
        • in Calc: ‘`u #' (`calc-vector-count') [`vcount'])’
        • gives the number of elements in the group being aggregated;
          this function may or may not take a column parameter; with a
          parameter, empty cells are not counted (except with the ‘E’
          modifier)..

   • ‘sum(X)’ or ‘vsum(X)’
        • in Calc: ‘`u +' (`calc-vector-sum') [`vsum']’
        • computes the sum of elements being aggregated

   • ‘cnorm(X)’
        • in Calc: ‘`v N' (calc-cnorm') [`cnorm']’
        • like ‘vsum(X)’, compute the sum of values, but first replacing
          negative values by their opposite

   • ‘max(X)’ or ‘vmax(X)’
        • in Calc: ‘`u X' (`calc-vector-max') [`vmax']’
        • gives the largest of the elements being aggregated

   • ‘min(X)’ or ‘vmin(X)’
        • in Calc: ‘`u N' (`calc-vector-min') [`vmin']’
        • gives the smallest of the elements being aggregated

   • ‘span(X)’ or ‘vspan(X)’
        • in Calc: ‘`v :' (`calc-set-span') [`vspan']’
        • summarizes values to be aggregated into an interval
          ‘[MIN..MAX]’ where ‘MIN’ and ‘MAX’ are the minimal and maximal
          values to be aggregated

   • ‘rnorm(X)’
        • in Calc: ‘`v n' (`calc-rnorm) [`rnorm']’
        • like ‘vmax(X)’, gives the maximum of values, but first
          replacing negative values by their opposite

   • ‘mean(X)’ or ‘vmean(X)’
        • in Calc: ‘`u M' (`calc-vector-mean') [`vmean']’
        • computes the average (arithmetic mean) of elements being
          aggregated

   • ‘meane(X)’ or ‘vmeane(X)’
        • in Calc: ‘`I u M' (`calc-vector-mean-error') [`vmeane']’
        • computes the average (as mean) along with the estimated error
          of elements being aggregated

   • ‘median(X)’ or ‘vmedian(X)’
        • in Calc: ‘`H u M' (`calc-vector-median') [`vmedian']’
        • computes the median of elements being aggregated, by taking
          the middle element after sorting them

   • ‘hmean(X)’ or ‘vhmean(X)’
        • in Calc: ‘`H I u M' (`calc-vector-harmonic-mean') [`vhmean']’
        • computes the harmonic mean of elements being aggregated

   • ‘gmean(X)’ or ‘vgmean(X)’
        • in Calc: ‘`u G' (`calc-vector-geometric-mean') [`vgmean']’
        • computes the geometric mean of elements being aggregated

   • ‘sdev(X)’ or ‘vsdev(X)’
        • in Calc: ‘`u S' (`calc-vector-sdev') [`vsdev']’
        • computes the standard deviation of elements being aggregated

   • ‘psdev(X)’ or ‘vpsdev(X)’
        • in Calc: ‘`I u S' (`calc-vector-pop-sdev') [`vpsdev']’
        • computes the population standard deviation (divide by N
          instead of N-1)

   • ‘var(X)’ or ‘vvar(X)’
        • in Calc: ‘`H u S' (`calc-vector-variance') [`vvar']’
        • computes the variance of elements being aggregated

   • ‘pvar(X)’ or ‘vpvar(X)’
        • in Calc: ‘`H u S' (`calc-vector-variance') [`vpvar']’
        • computes the population variance of elements being aggregated

   • ‘pcov(X,Y)’ or ‘vpcov(X,Y)’
        • in Calc: ‘`I u C' (`calc-vector-pop-covariance') [`vpcov']’
        • computes the population covariance of elements being
          aggregated from two columns (divides by N)

   • ‘cov(X,Y)’ or ‘vcov(X,Y)’
        • in Calc: ‘`u C' (`calc-vector-covariance') [`vcov']’
        • computes the sample covariance of elements being aggregated
          from two columns (divides by N-1)

   • ‘corr(X,Y)’ or ‘vcorr(X,Y)’
        • in Calc: ‘`H u C' (`calc-vector-correlation') [`vcorr']’
        • computes the linear correlation coefficient of elements being
          aggregated in two columns

   • ‘prod(X)’ or ‘vprod(X)’
        • in Calc: ‘`u *' (`calc-vector-product') [`vprod']’
        • computes the product of elements being aggregated

   • ‘vlist(X)’ or ‘list(X)’
        • gives the list of ‘X’ being aggregated, verbatim, without
          aggregation.

   • ‘(X)’ or ‘X’ in a formula
        • returns the list of ‘X’ being aggregated, without aggregation,
          passed through Calc interpretation.

   • ‘sort(X)’
        • in Calc: ‘`v S' (`calc-sort') [`sort']’
        • sorts elements to be aggregated in ascending order; only works
          on numerical values

   • ‘rsort(X)’
        • in Calc: ‘`I v S' (`calc-sort') [`sort']’
        • sorts elements to be aggregated in descending order; only
          works on numerical values

   • ‘rev(X)’
        • in Calc: ‘`' (`calc-reverse-vector') [`rev']’
        • returns the list of values to be aggregated in reverse order

   • ‘subvec(X,from)’, ‘subvec(X,from,to)’
        • in Calc: ‘`v s' (`calcFunc-subvec') [`subvec']’
        • extracts a sub-list from ‘X’ starting at ‘from’ and ending at
          ‘to’ excluded (or up to the end if ‘to’ is not given).  The
          first value is numbered ‘1’.  So for instance ‘subvec(X,1,3)’
          extracts the first two values

   • ‘vmask(M,X)’
        • in Calc: ‘`v m' (`calcFunc-vmask') [`vmask']’
        • extracts a sub-list from ‘X’, keeping only values for which
          corresponding values in ‘M’ (the mask) are not zero

   • ‘head(X)’
        • in Calc: ‘`v h' (`calc-head') [`head']’
        • returns the first value to be aggregated

   • ‘rtail(X)’
        • in Calc: ‘`H I v h' (`calc-head') [`rtail']’
        • returns the last value to be aggregated

   • ‘find(X,val)’
        • in Calc: ‘`v f' (`calc-vector-find') [`find']’
        • returns the index of ‘val’ in the list of values to be
          aggregated, or ‘0’ if ‘val’ is not found.  Index starts from
          ‘1’

   • ‘rdup(X)’
        • in Calc: ‘`v +' (`calc-remove-duplicates') [`rdup']’
        • remove duplicates from ‘X’ and returns remaining values sorted
          in ascending order

   • ‘grade(X)’
        • in Calc: ‘`v G' (`calc-grade') [`grade']’
        • returns a list of index of values to be aggregated: the index
          of the lowest value, then the second lowest value, and so on
          up to the index of the highest value.  Indexes start from ‘1’

   • ‘rgrade(X)’
        • in Calc: ‘`I v G' (`calc-grade') [`rgrade']’
        • Like ‘grade’ in reverse order

   The aggregation functions may be written with or without a leading
‘v’.  ‘sum’ and ‘vsum’ are equivalent.  The ‘v’ form should be
preferred, as it is the one used in the Org table spreadsheet, and in
Calc.  The non-v names may be dropped in the future.


File: orgtbl-aggregate.info,  Node: Correlation of two columns,  Next: [Almost) any expression can be specified,  Prev: Aggregation formulas in cols,  Up: The cols parameter

6.6 Correlation of two columns
==============================

Some aggregations work on two columns (rather than one column for
‘vsum()’, ‘vmean()’).  Those aggregations are ‘vcov(,)’, ‘vpcov(,)’,
‘vcorr(,)’.
   • ‘vcorr(,)’ computes the linear correlation between two columns.
   • ‘vcov(,)’ and ‘vpcov(,)’ compute the covariance of two columns.

   Example.  We create a table where column ‘y’ is a noisy version of
column ‘x’:

     #+tblname: noisydata
     | bin   |  x |       y |
     |-------+----+---------|
     | small |  1 |  10.454 |
     | small |  2 |  21.856 |
     | small |  3 |  30.678 |
     | small |  4 |  41.392 |
     | small |  5 |  51.554 |
     | large |  6 |  61.824 |
     | large |  7 |  71.538 |
     | large |  8 |  80.476 |
     | large |  9 |  90.066 |
     | large | 10 | 101.070 |
     | large | 11 | 111.748 |
     | large | 12 | 121.084 |
     #+tblfm: $3=$2*10+random(1000)/500;%.3f

     #+BEGIN: aggregate :table noisydata :cols "bin vcorr(x,y) vcov(x,y) vpcov(x,y)"
     | bin   |     vcorr(x,y) |     vcov(x,y) |    vpcov(x,y) |
     |-------+----------------+---------------+---------------|
     | small | 0.999459736649 |        25.434 |       20.3472 |
     | large | 0.999542438688 | 46.4656666667 | 39.8277142857 |
     #+END

   We see that the correlation between ‘x’ and ‘y’ is very close to ‘1’,
meaning that both columns are correlated.  Indeed they are, as the ‘y’
is computed from ‘x’ with the formula
     y = 10*x + noise_between_0_and_2


File: orgtbl-aggregate.info,  Node: [Almost) any expression can be specified,  Prev: Correlation of two columns,  Up: The cols parameter

6.7 (Almost) any expression can be specified
============================================

Virtually any Calc formula can be specified as an aggregation formula.

   Single column name (as they appear in the header of the source table,
or in the form of ‘$1’, ‘$2’, ..., or the virtual columns ‘hline’ and
‘@#’) are key columns.  Everything else is given to Calc, to be computed
as an aggregation.

   For instance:
     (3)                        ;; a constant
     vmean(2*X+1)               ;; aggregate an expression
     exp(vmean(map(log,N)))     ;; the exponential average
     vsum((X-vmean(X))^2)       ;; X-vmean(X) centers the sample on zero

   Arguably, the first expression is useless, but legal.  The
aggregation can be applied to a computed list of values.  The result of
an aggregation can be further processed in a formula.  An aggregation
can even be applied to an expression containing another aggregation.

   In an expression, if a variable has the name of a column, then it is
replaced by a Calc vector containing values from this column.

   The special expression ‘(C)’ (a column name within parenthesis)
yields a list of values to be aggregated from this column, except they
are not aggregated.  Note that parenthesis are required, otherwise, ‘C’
would act as a key grouping column.


File: orgtbl-aggregate.info,  Node: Column names,  Next: Formatters,  Prev: The cols parameter,  Up: Top

7 Column names
**************

* Menu:

* Input table with or without a header::
* Column names of the input table::
* Multiple lines header::
* Custom column names::


File: orgtbl-aggregate.info,  Node: Input table with or without a header,  Next: Column names of the input table,  Up: Column names

7.1 Input table with or without a header
========================================

The header of a table gives names to its columns.  It is separated from
data with an horizontal line.

     column name is "quantity" or "$2"╶╮
     column name is "day" or "$1"╶╮    │
        ╭─────────────────────────╯    │
        │              ╭───────────────╯
        ▼              ▼
     | day       | quantity |
     |-----------+----------|
     | monday    |     12.3 |
     | monday    |      5.9 |
     | thursday  |     41.1 |
     | wednesday |     16.8 |

   In this example, the input columns may be referred to as ‘day’ and
‘quantity’.

   Tables without a header are handled by OrgAggregate with _"dollar
names"_.  Example of a table without a header:

     column name is "$2"╶──╮
     column name is "$1"╶╮ │
         ╭───────────────╯ │
         │               ╭─╯
         ▼               ▼
     | monday    |     12.3 |
     | monday    |      5.9 |
     | thursday  |     41.1 |
     | wednesday |     16.8 |

   Then columns may be refereed to as ‘$1’ and ‘$2’.


File: orgtbl-aggregate.info,  Node: Column names of the input table,  Next: Multiple lines header,  Prev: Input table with or without a header,  Up: Column names

7.2 Column names of the input table
===================================

Column names are not necessarily alphanumeric words.  They may contain
any characters, including spaces, quotes, +, -, whatever.  They must not
extend on several lines thought.

   Those names need to be protected with quotes (single or double
quotes) within formulas.

   Examples:
   • ‘:cols’ "‘mean('estimated value')’"
   • ‘:cond (equal "true color" "Red")’

   Quoting is not required for
   • ASCII letters
   • numbers
   • underscore _, dollar $, dot .
   • accented letters like à é
   • Greek letters like α, Ω
   • northern letters like ø
   • Russian letters like й
   • Esperanto letters like ŭ
   • Japanese ideograms like 量

   Note that in ‘:cond’ Lisp expression, only double quotes work.  This
is because single quotes in Lisp have a very special meaning.

   ‘Ubuntu Mono’ font can be used for displaying aligned Japanese
characters, although not perfectly.


File: orgtbl-aggregate.info,  Node: Multiple lines header,  Next: Custom column names,  Prev: Column names of the input table,  Up: Column names

7.3 Multiple lines header
=========================

The header of the source table may be more than one row tall.  Only the
first header row is used to match column names between the source table
and the ‘:cols’ specifications.

   Best effort is made to propagate additional header rows to the
aggregated table.  This happens when the aggregated column refers to a
single source column, either as a key column or a formula involving a
single column.

     #+name: tall-header

                    ╭──────────────────────────────────────────────╮
                ╭───┴──╮                                           │
     | color  | quantity |  level | ╶╮                             │
     | <l>    |     <r7> |    <3> |  ├─╴header is 3 rows tall      │
     | kolor  |     kiom | nivelo | ╶╯                             │
     |--------+----------+--------|                    ╭───────────╯───────────────╮
     | yellow |       72 |      3 |                    │ only the first header row │
     | green  |       55 |      5 |                    │ is used in formulas       │
     | <c>    |          |        |                    ╰───────────╭───────────────╯
     | orange |       80 |      2 |                                │
     | yellow |       13 |      1 |                                │
                                                               ╭───┴──╮
     #+BEGIN: aggregate :table "tall-header" :cols "color vsum(quantity);'sum' count();'nb' vsum(quantity)/vmean(level);'leveled'"
     | color  |  sum | nb | leveled | ╶╮
     | <l>    | <r7> |    |         |  ├──╮
     | kolor  | kiom |    |         | ╶╯  │  ╭────────────────────────╮
     |--------+------+----+---------|     │  │ best attempt to recover│
     | yellow |   85 |  2 |    42.5 |     ╰──┤ the three header rows  │
     | green  |   55 |  1 |      11 |        │ in the output          │
     | orange |   80 |  1 |      40 |        ╰────────────────────────╯
     #+END:

   Note that the last aggregated column has just ‘leveled’ in its
header.  This is because this column refers to more than one source
columns, namely ‘quantity’ and ‘level’.

   Note that in this example, there are formatting cookies:
     <> <l> <c> <r> <7> <l7> <c7> <r7>

   Data rows containing at least one cookie are ignored.  They are not
ignored in the header.


File: orgtbl-aggregate.info,  Node: Custom column names,  Prev: Multiple lines header,  Up: Column names

7.4 Custom column names
=======================

In this example, output column have names which are difficult to handle:

     #+BEGIN: aggregate :table original :cols "Day vmean(Level*2) vsum(Quantity^2)"
                                                   ╰─────┬──────╯ ╰──────┬───────╯
                         ╭───────────────────────────────╯               │
                         │                 ╭─────────────────────────────╯
                   ╭─────┴──────╮   ╭──────┴───────╮
     | Day       | vmean(Level*2) | vsum(Quantity^2) |
     |-----------+----------------+------------------|
     | Monday    |             55 |              130 |
     | Tuesday   |             86 |              693 |
     | Wednesday |             36 |             1010 |
     | Thursday  |             86 |             2317 |
     | Friday    |             16 |              170 |
     #+END

   We can give them custom names with the ‘;'custom name'’ decoration:

     #+BEGIN: aggregate :table original :cols "Day vmean(Level*2);'mean2' vsum(Quantity^2);'sum_squares'"
                                                                  ╰──┬──╯                  ╰─────┬─────╯
                     ╭───────────────────────────────────────────────╯                           │
                     │         ╭─────────────────────────────────────────────────────────────────╯
                   ╭─┴─╮   ╭───┴─────╮
     | Day       | mean2 | sum_squares |
     |-----------+-------+-------------|
     | Monday    |    55 |         130 |
     | Tuesday   |    86 |         693 |
     | Wednesday |    36 |        1010 |
     | Thursday  |    86 |        2317 |
     | Friday    |    16 |         170 |
     #+END

   Decorators are optional.


File: orgtbl-aggregate.info,  Node: Formatters,  Next: Sorting,  Prev: Column names,  Up: Top

8 Formatters
************

* Menu:

* Org Mode compatible formatters::
* Debugging formatters::
* Alignment cookies::
* Discarding an output column::


File: orgtbl-aggregate.info,  Node: Org Mode compatible formatters,  Next: Debugging formatters,  Up: Formatters

8.1 Org Mode compatible formatters
==================================

An expression may optionally be followed by modifiers and formatters,
after a semicolon.  Examples:

     vsum(X);p20    ;; increase Calc internal precision to 20 digits
     vsum(X);f3     ;; output the result with 3 digits after the decimal dot
     vsum(X);%.3f   ;; output the result with 3 digits after the decimal dot

   The modifiers and formatters are fully compatible with those of the
Org Mode spreadsheet.

   • ‘p12’ change the precision to 12 decimal digits.
   • ‘n7’ output as floating point number with 7 decimal digits.
   • ‘f4’ output number with 4 decimal places after dot.
   • ‘s5’ output number in "scientific" mode (with exponent of 10) with
     5 decimal digits.
   • ‘e6’ output number in "engineering" mode (with exponent of 10
     multiple of 3) with 6 decimal digits.
   • ‘t’ output duration in decimal hours; input is supposed to be
     either a duration like ‘"2:37"’ meaning 2 hours and 37 minutes, or
     a number of seconds like ‘"1234’" which is approximately ‘0.34’
     hours.  The output is controlled by the
     ‘org-table-duration-custom-format’ variable.
   • ‘T’ output duration in an hours-minutes-seconds format like
     ‘"01:20:34"’ meaning 1 hour, 20 minutes, and 34 seconds.
   • ‘U’ like ‘t’, but disregard the ‘org-table-duration-custom-format’
     variable and use ‘hh:mm’ in place.
   • ‘N’ output number: remove any non-numeric output.
   • ‘E’ keep empty input cells.  The result is often ‘nan’.  Without
     ‘E’, empty input cells are ignored as if they did not exist.
   • ‘D’ angles are in degrees.
   • ‘R’ angles are in radians.
   • ‘F’ output is presented as a fraction of integers if it actually
     is.  The format is the Calc one, for example ‘"2:3"’ means ‘2/3’.
   • ‘S’ symbolic mode.  When an input cell is, for instance ‘sqrt(2)’,
     it it kept as-is rather than being replaced by ‘1.41421’.


File: orgtbl-aggregate.info,  Node: Debugging formatters,  Next: Alignment cookies,  Prev: Org Mode compatible formatters,  Up: Formatters

8.2 Debugging formatters
========================

Additionally, a few formatters are dedicated to debugging:

   • ‘c’ output the Calc expression before substitution by actual input
     cells values.
   • ‘q’ output the Lisp expression before substitution by actual input
     cells values.
   • ‘C’ output the Calc expression before it gets simplified and
     folded.
   • ‘Q’ output the Lisp expression before it gets simplified and
     folded.

   See *note Debugging:: for a detailed explanation.


File: orgtbl-aggregate.info,  Node: Alignment cookies,  Next: Discarding an output column,  Prev: Debugging formatters,  Up: Formatters

8.3 Alignment cookies
=====================

The cookies are the same as in Org Mode:
   • ‘<c>’ to center the content of each cell
   • ‘<l>’ to left align
   • ‘<r>’ to right align

   Example:

       we decided to center the names
       of planets in the first column╶─────────────────╮
                                                       │
       the input table is not shown                    │
       in this example╶───────────╮                    │
                                  ▼                    ▼
     #+BEGIN: aggregate :table "planet" :cols "planet;<c> 'mass kg';^n"
     | planet  |   mass kg |
     |---------+-----------|
     |  Pluto  |   1.25e22 |
     | Mercury | 3.3022e23 |
     |  Mars   | 6.4185e23 |
     |  Venus  | 4.8685e24 |
     |  Earth  | 5.9736e24 |
     | Uranus  | 8.6810e25 |
     | Neptune | 10.243e25 |
     | Saturn  | 5.6846e26 |
     | Jupiter | 1.8986e27 |
     |   Sun   | 1.9891e30 |
     #+END:

   So, just add a semicolon and a cookie after a column specification,
and OrgAggregate will happily comply, just for this column.

   If no alignment is specified for a column, then OrgAggregate decides
in the same way as the Org Mode spreadsheet.  If the column is mostly
numeric, then it is right-aligned.  Otherwise it is left aligned.  How
much "mostly numeric" is, is given by the ‘org-table-number-fraction’
variable.  Its default value is ‘0.5’, and it can be customized.

   In Org Mode, a width can also be specified, for instance:

   ‘<l12>’ means: "align the content of each cell to the left, and make
it 12 characters wide".

   The width specification is not yet available in OrgAggregate, but
coming soon.


File: orgtbl-aggregate.info,  Node: Discarding an output column,  Prev: Alignment cookies,  Up: Formatters

8.4 Discarding an output column
===============================

Why would anyone specify a column just to discard it in the output?  For
its side effects.  For sorting the output table or for adding hlines to
it.

   To discard a column, add a ‘;<>’ modifier to the column description.
This syntax is reminiscent of the ‘<n>’ cookies in Org Mode tables,
which instructs to shorten a column width to only ‘n’ characters.

   In this example, input hlines create a ‘hline’ column which is used
to add hlines to the output.  Then this ‘hline’ column is discarded with
‘<>’.

     invisible╶────────────────────────────────────────────╮
     sorted numerically increasing╶──────────────────────╮ │
                                                         │ │
                                                         ▼ ▼
     #+BEGIN: aggregate :table "withhline" :cols "hline;^n;<> cölØr vsum(vâluε)" :hline 1
     | cölØr  | vsum(vâluε) |
     |--------+-------------|
     | Red    |         7.4 |
     | Yellow |         9.1 |
     |--------+-------------|
     | Blue   |        15.7 |
     | Yellow |         5.4 |
     |--------+-------------|
     | Blue   |         4.9 |
     | Red    |         3.9 |
     | Yellow |          9. |
     |--------+-------------|
     | Red    |         1.1 |
     | Yellow |         3.4 |
     #+END:

   Here is an example where rows are sorted on the ‘cölØr’ column, but
without displaying this column:

     invisible╶────────────────────────────────────────────╮
     sorted alphabetically╶──────────────────────────────╮ │
                                                         │ │
                                                         ▼ ▼
     #+BEGIN: aggregate :table "withhline" :cols "cölØr;^a;<> vâluε;^n" :hline 1
     | vâluε |                                                       ▲
     |-------|                                                       │
     |   4.9 |           within the same cölØr bucket,               │
     |   7.0 |           sort vâluε numerically╶─────────────────────╯
     |   8.7 |
     |-------|
     |   1.1 |
     |   1.3 |
     |   2.6 |
     |   3.5 |
     |   3.9 |
     |-------|
     |   2.4 |
     |   3.4 |
     |   5.4 |
     |   6.6 |
     |   9.1 |
     #+END:


File: orgtbl-aggregate.info,  Node: Sorting,  Next: hlines in the output table,  Prev: Formatters,  Up: Top

9 Sorting
*********

* Menu:

* Example with one sorting column::
* Several sorting columns::


File: orgtbl-aggregate.info,  Node: Example with one sorting column,  Next: Several sorting columns,  Up: Sorting

9.1 Example with one sorting column
===================================

In this example, the output table is sorted numerically on its second
column (look at the ‘^n’ specification):

     #+begin: aggregate :table "original" :cols "Day vsum(Quantity);^n"
     | Day       | vsum(Quantity) |
     |-----------+----------------|
     | Monday    |             14 |
     | Friday    |             22 |
     | Tuesday   |             45 |
     | Wednesday |             54 |
     | Thursday  |             83 |
     #+end:

   By default, no sorting is done.  The output rows follows the ordering
of the input rows.

   Any column specification in the ‘:cols’ parameter may be followed by
a semicolon and caret characters, and an ordering.

   The specification for the ordering are the same as in Org Mode:
   • ‘a’: ascending alphabetical sort
   • ‘A’: descending alphabetical sort
   • ‘n’: ascending numerical sort
   • ‘N’: descending numerical sort
   • ‘t’: ascending date, time, or duration sort
   • ‘T’: descending date, time, or duration sort
   • ‘f’ & ‘F’ specifications are not (yet) implemented


File: orgtbl-aggregate.info,  Node: Several sorting columns,  Prev: Example with one sorting column,  Up: Sorting

9.2 Several sorting columns
===========================

The rows of the resulting table may be sorted on any combination of its
columns.

   Several columns may get a sorting specification.  The major column is
used for sorting.  Only when two rows are equal regarding the major
column, the second major column is compared.  And if the two rows are
still equal on this second column, the third is used, and so on.

   The first sorted column in the ‘:cols’ parameter is the major one.
To declare another one as the major, follow it with a number, for
instance ‘1’.  Columns without a number are minor ones.

   Example:
     :cols "AAA;^a BBB;^N2 CCC DDD;^t1"
     ╭────╮      ▲╷     ▲▲          ▲▲ ╭──────────────╮
     │sort╰──────╯╰─╮   ││          │╰─╯first priority│
     │alphabetically│   ││          ╰──╮sort by date  │
     │third priority│   ││             ╰──────────────╯
     ╰──────────────╯   ││  ╭────────────────╮
                        │╰──╯second priority │
                        ╰───╮sort numerically│
                            │decreasing      │
                            ╰────────────────╯

   • Column ‘DDD’ is sorted in ascending dates or times (‘t’
     specification).  It is the major sorting column (because of its ‘1’
     numbering).
   • Column ‘BBB’ sorts rows which compare equal on column ‘DDD’
     (because of its ‘2’ numbering).  This column is assumed to contain
     numerical values, and it is sorted in descending order (‘N’
     specification).
   • Column ‘AAA’ is used to sort rows which compare equal regarding
     ‘DDD’ and ‘BBB’.  It is sorted in ascending alphabetical order (‘a’
     specification).

   Both a format and a sorting instruction may be given.  Example:
     :cols "EXPR:f3:^n"

   The ‘EXPR’ column is
   • formatted with 3 digits after dot (‘f3’)
   • sorted numerically in ascending order (‘^n’).


File: orgtbl-aggregate.info,  Node: hlines in the output table,  Next: Cells processing,  Prev: Sorting,  Up: Top

10 hlines in the output table
*****************************

The ‘:hline N’ parameter controls horizontal lines in the output table.
It may or may not be related to horizontal lines in the input.

* Menu:

* Output hlines depends on sorting columns::
* Example with hline 2::


File: orgtbl-aggregate.info,  Node: Output hlines depends on sorting columns,  Next: Example with hline 2,  Up: hlines in the output table

10.1 Output hlines depends on sorting columns
=============================================

Example of an input table:

     #+name: withouthline
     | cölØr  | vâluε | ra;han |
     |--------+-------+--------|
     | Red    |   1.3 |     41 |
     | Red    |   3.5 |     35 |
     | Yellow |   9.1 |     95 |
     | Red    |   2.6 |     84 |
     | Blue   |   8.7 |     52 |
     | Blue   |   7.0 |     29 |
     | Yellow |   5.4 |     17 |
     | Blue   |   4.9 |     64 |
     | Red    |   3.9 |     51 |
     | Yellow |   2.4 |     55 |
     | Yellow |   6.6 |     34 |
     | Red    |   1.1 |     58 |
     | Yellow |   3.4 |     51 |

   Horizontal lines appear on the sorted column, which in this example
is the ‘cölØr’ column.

   We require output hlines with ‘:hline 1’.  The ‘1’ value here says
that only one sorted column should be considered when drawing output
horizontal lines.  A value of ‘2’ would mean to consider two sorted
columns.

   Horizontal lines will separate blocks of identical ‘cölØr’ rows:

     #+BEGIN: aggregate :table "withouthline" :cols "cölØr;^a vâluε 'ra;han'" :hline 1
     | cölØr  | vâluε | 'ra;han' |                   ╰─┬─╯  ▲                  ╰─┬─╯
     |--------+-------+----------|                     │    │                    │
     | Blue   |   8.7 |       52 |╶╮                   │    │╭──────╮  ╭─────────┴─────╮
     | Blue   |   7.0 |       29 | ├─╴Blue bucket      │    ╰╯sort  │  │ separate      │
     | Blue   |   4.9 |       64 |╶╯                   ╰─────╮cölØr │  │ cölØr buckets │
     |--------+-------+----------| ◀────────────────╮        ╰──────╯  │ with hlines   │
     | Red    |   1.3 |       41 |╶╮                │                  ╰──┬┬───────────╯
     | Red    |   3.5 |       35 | │                ╰─────────────────────╯│
     | Red    |   2.6 |       84 | ├─╴Red bucket                           │
     | Red    |   3.9 |       51 | │                                       │
     | Red    |   1.1 |       58 |╶╯                                       │
     |--------+-------+----------| ◀───────────────────────────────────────╯
     | Yellow |   9.1 |       95 |╶╮
     | Yellow |   5.4 |       17 | │
     | Yellow |   2.4 |       55 | ├─╴Yellow bucket
     | Yellow |   6.6 |       34 | │
     | Yellow |   3.4 |       51 |╶╯
     #+END:


File: orgtbl-aggregate.info,  Node: Example with hline 2,  Prev: Output hlines depends on sorting columns,  Up: hlines in the output table

10.2 Example with hline 2
=========================

In the following example, we specify ‘:hline 2’.

   First, the input table now have horizontal lines.  We want to
propagate them to the output.

     #+name: withhline
     | cölØr  | vâluε | ra;han |
     |--------+-------+--------|
     | Red    |   1.3 |     41 |
     | Red    |   3.5 |     35 |
     | Yellow |   9.1 |     95 |
     | Red    |   2.6 |     84 |
     |--------+-------+--------|
     | Blue   |   8.7 |     52 |
     | Blue   |   7.0 |     29 |
     | Yellow |   5.4 |     17 |
     |--------+-------+--------|
     | Blue   |   4.9 |     64 |
     | Red    |   3.9 |     51 |
     | Yellow |   2.4 |     55 |
     | Yellow |   6.6 |     34 |
     |--------+-------+--------|
     | Red    |   1.1 |     58 |
     | Yellow |   3.4 |     51 |

   The two sorted columns are ‘hline’ and ‘cölØr’.  Therefore output
horizontal lines separate blocks of identical ‘hline’ and ‘cölØr’:

     #+begin: aggregate :table "withhline" :cols "hline;^n cölØr;^a vâluε 'ra;han'" :hline 2
     | hline | cölØr  | vâluε | 'ra;han' |               ▲        ▲                        ▲
     |-------+--------+-------+----------|               │        │                        │
     |     0 | Red    |   1.3 |       41 |        ╭──────╯        ╰───────────╮            │
     |     0 | Red    |   3.5 |       35 |        │ two sorted output columns │            │
     |     0 | Red    |   2.6 |       84 |        ╰───────────────────────────╯            │
     |-------+--------+-------+----------|                                                 │
     |     0 | Yellow |   9.1 |       95 |                                                 │
     |-------+--------+-------+----------|             ╭─────────────────────────────╮     │
     |     1 | Blue   |   8.7 |       52 |             │ 2 means: create hlines      ├─────╯
     |     1 | Blue   |   7.0 |       29 |             │ for buckets and sub-buckets │
     |-------+--------+-------+----------|             ╰─────────────────────────────╯
     |     1 | Yellow |   5.4 |       17 |
     |-------+--------+-------+----------|╶─╮
     |     2 | Blue   |   4.9 |       64 |  │   ╭──────────────────────────╮
     |-------+--------+-------+----------| ╶┤   │ bucket hline=2           │
     |     2 | Red    |   3.9 |       51 |  ├───┤ divided in 3 sub-buckets │
     |-------+--------+-------+----------| ╶┤   │ Blue, Red, Yellow        │
     |     2 | Yellow |   2.4 |       55 |  │   ╰──────────────────────────╯
     |     2 | Yellow |   6.6 |       34 |  │
     |-------+--------+-------+----------|╶─╯
     |     3 | Red    |   1.1 |       58 |
     |-------+--------+-------+----------|
     |     3 | Yellow |   3.4 |       51 |
     #+end:

   And the ‘hline’ column may be discarded (but its side effect
remains).  To do so use the ‘;<>’ specifier:

     #+begin: aggregate :table "withhline" :cols "hline;^n;<> cölØr;^a vâluε 'ra;han'" :hline 2
     | cölØr  | vâluε | 'ra;han' |
     |--------+-------+----------|
     | Red    |   1.3 |       41 |
     | Red    |   3.5 |       35 |
     | Red    |   2.6 |       84 |
     |--------+-------+----------|
     | Yellow |   9.1 |       95 |
     |--------+-------+----------|
     | Blue   |   8.7 |       52 |
     | Blue   |   7.0 |       29 |
     |--------+-------+----------|
     | Yellow |   5.4 |       17 |
     |--------+-------+----------|
     | Blue   |   4.9 |       64 |
     |--------+-------+----------|
     | Red    |   3.9 |       51 |
     |--------+-------+----------|
     | Yellow |   2.4 |       55 |
     | Yellow |   6.6 |       34 |
     |--------+-------+----------|
     | Red    |   1.1 |       58 |
     |--------+-------+----------|
     | Yellow |   3.4 |       51 |
     #+end:

   The ‘:hline’ parameter accepts a number:
   • ‘:hline 0’, ‘:hline no’, ‘:hline nil’, or no ‘:hline’ mean that
     there will be no hlines in the output.
   • ‘:hline 1’, ‘:hline yes’, ‘:hline t’ mean that hlines will separate
     blocks of identical rows regarding the major sorted column.  In
     case no column is sorted, then output hlines will reflect input
     ones.
   • ‘:hline 2’ means that the major and the next major sorted columns
     will be used to separate identical rows regarding those two
     columns.
   • ‘:hline 3’, ‘:hline 4’, ... may be specified, but they may result
     in too much hlines.


File: orgtbl-aggregate.info,  Node: Cells processing,  Next: Wide variety of inputs,  Prev: hlines in the output table,  Up: Top

11 Cells processing
*******************

*Calc* is the standard Emacs desktop calculator.  Actual mathematical
computations are handled through Calc.  This offers a lot of
flexibility.

* Menu:

* Where Calc interpretation happens?::
* Dates::
* Durations::
* Empty and malformed input cells::
* Symbolic computation::
* Intervals::
* Error or precision forms::


File: orgtbl-aggregate.info,  Node: Where Calc interpretation happens?,  Next: Dates,  Up: Cells processing

11.1 Where Calc interpretation happens?
=======================================

Example of input table.  Besides numbers, there are cells with
mathematical expressions like ‘20*30’, or just labels as ‘Red&Green’
without any mathematical meaning.

     #+name: to_Calc_or_not_to_Calc
     | Day       | Color      | Level  |
     |-----------+------------+--------|
     | Monday    | Red        | 20*30  |
     | Monday    | Blue       | 55+45  |
     | Tuesday   | Red        | 1      |
     | Tuesday   | Red&Green  | 2      |
     | Tuesday   | Blue+Green | 3      |
     | Wednesday | Red        | (27)   |
     | Wednesday | Red        | (12+1) |
     | Wednesday | Green      | [15]   |

   Basically, Calc operates twice.  For example in the formula
‘vsum(Level)’:
   • Calc computes ‘Level’ for every input cell in the ‘Level’ column,
   • then Calc computes ‘vsum()’ applied to the resulting list.

     #+BEGIN: aggregate :table "to_Calc_or_not_to_Calc" :cols "Day vsum(Level)"
     | Day       | vsum(Level) |
     |-----------+-------------|
     | Monday    |         700 |
     | Tuesday   |           6 |
     | Wednesday |          55 |
     #+END:

   There are a few occasions were Calc computation does not happen:
‘vcount()’ and ‘vlist(X)’.

   The ‘vcount()’ sub-formula is evaluated as the number of input rows
in each group, without Calc intervention.  However, later on Calc can
handle this number in a formula as this one: ‘vsum(Level)/vcount()’

     #+BEGIN: aggregate :table "to_Calc_or_not_to_Calc" :cols "Day vcount() vsum(Level)/vcount()"
     | Day       | vcount() | vsum(Level)/vcount() |
     |-----------+----------+----------------------|
     | Monday    |        2 |                  350 |
     | Tuesday   |        3 |                    2 |
     | Wednesday |        3 |            18.333333 |
     #+END:

   And of course when input cells do not have a mathematical meaning,
the result may be non-sens:

     #+BEGIN: aggregate :table "to_Calc_or_not_to_Calc" :cols "Day vsum(Color)"
     | Day       | vsum(Color)                                    |
     |-----------+------------------------------------------------|
     | Monday    | Red + Blue                                     |
     | Tuesday   | Red + error(3, '"Syntax error") + Blue + Green |
     | Wednesday | 2 Red + Green                                  |
     #+END:

   But it can also make sens.  The last row, which aggregate ‘Wednesday’
rows, is computed as ‘2 Red + Green’.  This is correct.  This symbolic
result (as opposed to numerical result) shows the power of Calc as a
symbolic calculator.

   The ‘vlist(X)’ formula is not handled by Calc at all.  This formula
must appear alone (not embedded as part of a bigger formula).  The cells
‘X’ are not interpreted by Calc.  As a result, ‘vlist(X)’ produces a
cell which concatenates input cells verbatim.  For instance, the input
cell ‘20*30’ is left as-is.

     #+BEGIN: aggregate :table "to_Calc_or_not_to_Calc" :cols "Day vlist(Color) vlist(Level)"
     | Day       | vlist(Color)               | vlist(Level)       |
     |-----------+----------------------------+--------------------|
     | Monday    | Red, Blue                  | 20*30, 55+45       |
     | Tuesday   | Red, Red&Green, Blue+Green | 1, 2, 3            |
     | Wednesday | Red, Red, Green            | (27), (12+1), [15] |
     #+END:

   As a contrast, the formula ‘(Level)’ yields a list processed through
Calc.  For instance, the ‘20*30’ formula is replaced by ‘600’.

     #+BEGIN: aggregate :table "to_Calc_or_not_to_Calc" :cols "Day (Color) (Level)"
     | Day       | (Color)                                        | (Level)        |
     |-----------+------------------------------------------------+----------------|
     | Monday    | [Red, Blue]                                    | [600, 100]     |
     | Tuesday   | [Red, error(3, '"Syntax error"), Blue + Green] | [1, 2, 3]      |
     | Wednesday | [Red, Red, Green]                              | [27, 13, [15]] |
     #+END:

   Here we used parenthesis in ‘(Color)’ and ‘(Level)’ because otherwise
they would have been _key columns_.  Instead of parenthesis, we can
embed such expressions in formulas, like ‘Level+1’:

     #+BEGIN: aggregate :table "to_Calc_or_not_to_Calc" :cols "Day Level+1"
     | Day       | Level+1        |
     |-----------+----------------|
     | Monday    | [601, 101]     |
     | Tuesday   | [2, 3, 4]      |
     | Wednesday | [28, 14, [16]] |
     #+END:

   To summarize, a column name embedded in a formula is evaluated as the
list of input cells, processed by Calc.  Except for the ‘vlist(Column)’
formula where input cells are kept verbatim.

   By the way, what is the meaning of the expression ‘Level*Level’?  For
‘Monday’, it is ‘[600,100]*[600,100]’.  Then Calc simplifies that as a
_vector product_: sum of individual products.  ‘600^2+100^2’

     #+BEGIN: aggregate :table "to_Calc_or_not_to_Calc" :cols "Day Level*Level Level+Level"
     | Day       | Level*Level | Level+Level    |
     |-----------+-------------+----------------|
     | Monday    |      370000 | [1200, 200]    |
     | Tuesday   |          14 | [2, 4, 6]      |
     | Wednesday |        1123 | [54, 26, [30]] |
     #+END:


File: orgtbl-aggregate.info,  Node: Dates,  Next: Durations,  Prev: Where Calc interpretation happens?,  Up: Cells processing

11.2 Dates
==========

Some aggregations are possible on dates.  Example.  Here is a source
table containing dates:

     #+tblname: datetable
     | Date                   |
     |------------------------|
     | [2035-12-22 Sat 09:01] |
     | [2034-11-24 Fri 13:04] |
     | [2030-09-24 Tue 13:54] |
     | [2027-09-25 Sat 03:54] |
     | [2023-02-26 Sun 16:11] |
     | [2020-03-17 Tue 03:51] |
     | [2018-08-21 Tue 00:00] |
     | [2012-12-25 Tue 00:00] |

   Here are the earliest and the latest dates, along with the average of
all input dates:

     #+BEGIN: aggregate :table datetable :cols "vmin(Date) vmax(Date) vmean(Date)"
     | vmin(Date)             | vmax(Date)             | vmean(Date) |
     |------------------------+------------------------+-------------|
     | <2012-12-25 Tue 00:00> | <2035-12-22 Sat 09:01> |   739448.44 |
     #+END:

   The average of all dates is a number?  Actually, it is a date
expressed as the number of days since ‘[0000-12-31 Sun 00:00]’.  To
force a number of days to be interpreted as a date, use the ‘date()’
function:

     #+BEGIN: aggregate :table datetable :cols "date(vmean(Date))"
     | date(vmean(Date))      |
     |------------------------|
     | <2025-07-16 Wed 10:29> |
     #+END:

   With the ‘date()’ function in mind, all kinds of dates handling can
be done.  Example: the average of earliest and the latest dates is
different from the average of all dates:

     #+BEGIN: aggregate :table datetable :cols "date(vmean(vmin(Date),vmax(Date))) date(vmean(Date))"
     | date(vmean(vmin(Date),vmax(Date))) | date(vmean(Date))      |
     |------------------------------------+------------------------|
     | <2024-06-23 Sun 16:30>             | <2025-07-16 Wed 10:29> |
     #+END:

   Note that ‘date()’ is not special to OrgAggregate.  It can be used in
Org Mode spreadsheet formulas.


File: orgtbl-aggregate.info,  Node: Durations,  Next: Empty and malformed input cells,  Prev: Dates,  Up: Cells processing

11.3 Durations
==============

In Org Mode spreadsheet, durations have the forms ‘HH:MM’ or ‘HH:MM:SS’.
In OrgAggregate, when an input cell have one of those two forms, it is
converted into a number of seconds.  For instance, ‘01:00’ is converted
into ‘3600’ and ‘00:00:07’ is converted into ‘7’.

   There may be a single digit for hours, as in ‘7:12’ or more than two
as in ‘1255:45:00’.

   To output such a form, use a formatter: ‘;T’; ‘;t’, ‘;U’.  For
example, we have 3 durations as input, and we want the average of them:

     #+name: some_durations
     |      dur |
     |----------|
     | 07:45:30 |
     |    13:55 |
     |    17:12 |

     #+BEGIN: aggregate :table "some_durations" :cols "vmean(dur) vmean(dur);T vmean(dur);t vmean(dur);U"
     | vmean(dur) | vmean(dur) | vmean(dur) | vmean(dur) |
     |------------+------------+------------+------------|
     |      46650 |   12:57:30 |      12.96 |      12:57 |
     #+END:

   • With no formatter, we get a number of seconds
   • The ‘T’ formatter outputs the result as ‘HH:MM:SS’
   • The ‘U’ formatter outputs the result as ‘HH:MM’
   • The ‘t’ formatter converts the result into a number of hours (it
     divides the number of seconds by 3600, and displays only two digits
     after dot)

   The Calc syntax for durations is also recognized:
     HH@ MM'
     HH@ MM' SS"
   Example:

     #+name: calc_durations
     | dur        |
     |------------|
     | 07@ 45' 30 |
     | 13@ 55'    |
     | 17@ 12'    |

     #+BEGIN: aggregate :table "calc_durations" :cols "vmean(dur)"
     | vmean(dur)   |
     |--------------|
     | 12@ 57' 30." |
     #+END:


File: orgtbl-aggregate.info,  Node: Empty and malformed input cells,  Next: Symbolic computation,  Prev: Durations,  Up: Cells processing

11.4 Empty and malformed input cells
====================================

The input table may contain malformed mathematical text.  For instance,
a cell containing ‘5+’ is malformed, because an expression is missing
after the ‘+’ symbol.  In this case, the value will be replaced by
‘error(2, '"Expected a number")’ which will appear in the aggregated
table, signaling the problem.

   An input cell may be empty.  In this case, it may be ignored or
converted to zero, depending on modifier flags ‘E’ and ‘N’.

   The empty cells treatment
   • makes no difference for ‘vsum’ and ‘count’.
   • may result in zero for ‘prod’,
   • change ‘vmean’ result,
   • change ‘vmin’ and ‘vmax’, a possibly empty list of values resulting
     in ‘inf’ or ‘-inf’

   Some aggregation functions operate on two columns.  If the two
columns have empty values at different locations, then they should be
interpreted as zero with the ‘NE’ modifier, otherwise the result will be
inconsistent.

   Sometimes an input table may be malformed, with incomplete rows, like
this one:

     | Color | Level | Quantity | Day       |
     |-------+-------+----------+-----------|
     | Red   |    30 |       11 | Monday    |
     | Blue  |    25 |        3 | Monday    |
     |
     | Blue  |    33 |       18 | Tuesday   |
     | Red   |    27 |
     | Blue  |    12 |       16 | Wednesday |
     | Blue  |    15 |       15 |
     |

   Missing cells are handled as though they were empty.


File: orgtbl-aggregate.info,  Node: Symbolic computation,  Next: Intervals,  Prev: Empty and malformed input cells,  Up: Cells processing

11.5 Symbolic computation
=========================

The computations are based on Calc, which is a symbolic calculator.
Thus, symbolic computations are built-in.  Example:

   This is the source table:

     #+NAME: symtable
     | Day       | Color |  Level | Quantity |
     |-----------+-------+--------+----------|
     | Monday    | Red   |   30+x |     11+a |
     | Monday    | Blue  | 25+3*x |        3 |
     | Tuesday   | Red   | 51+2*x |       12 |
     | Tuesday   | Red   |   45-x |       15 |
     | Tuesday   | Blue  |     33 |       18 |
     | Wednesday | Red   |     27 |       23 |
     | Wednesday | Blue  |   12+x |       16 |
     | Wednesday | Blue  |     15 |   15-6*a |
     | Thursday  | Red   |     39 |   24-5*a |
     | Thursday  | Red   |     41 |       29 |
     | Thursday  | Red   |   49+x |   30+9*a |
     | Friday    | Blue  |      7 |      5+a |
     | Friday    | Blue  |      6 |        8 |
     | Friday    | Blue  |     11 |        9 |

   And here is the aggregated, symbolic result:

     #+BEGIN: aggregate :table "symtable" :cols "Day vmean(Level) vsum(Quantity)"
     | Day       | vmean(Level)          | vsum(Quantity) |
     |-----------+-----------------------+----------------|
     | Monday    | 2. x + 27.5           | a + 14         |
     | Tuesday   | 0.333333333334 x + 43 | 45             |
     | Wednesday | x / 3 + 18            | 54 - 6 a       |
     | Thursday  | x / 3 + 43.           | 4 a + 83       |
     | Friday    | 8                     | a + 22         |
     #+END

   Symbolic calculations are correctly performed on ‘x’ and ‘a’, which
are symbolic (as opposed to numeric) expressions.

   Note that if there are empty cells in the input, they will be changed
to ‘nan’ _not a number_, and the whole aggregation will yield ‘nan’.
This is probably not the expected result.

   The ‘N’ modifier (see *note Org Mode compatible formatters::) won’t
help, because even though it will replace empty cells with zero, it will
do the same for anything which does not look like a number.  The best is
to just avoid empty cells when dealing with symbolic calculations.


File: orgtbl-aggregate.info,  Node: Intervals,  Next: Error or precision forms,  Prev: Symbolic computation,  Up: Cells processing

11.6 Intervals
==============

Org Mode tables and OrgAggregate backend engine being Emacs Calc,
intervals are seamlessly handled.  An interval is made of two numerical
values separated by two dots.

   Example of a spreadsheet.  The third column is computed by
multiplying the first two:

     #+name: int
     | 3..5 |    10 | (30 .. 50) |
     | 3..5 | -1..2 | (-5 .. 10) |
     |    5 |     2 | 10         |
     #+TBLFM: $3=$1*$2

   Example of an aggregation.  The sum of the third column is computed,
resulting in an interval:

     #+BEGIN: aggregate :table "int" :cols "vsum($3)"
     | vsum($3)   |
     |------------|
     | (35 .. 70) |
     #+END:


File: orgtbl-aggregate.info,  Node: Error or precision forms,  Prev: Intervals,  Up: Cells processing

11.7 Error or precision forms
=============================

In Emacs Calc, an error form is a numerical value along with its
precision, both values separated by ‘+/-’.

   The separation may also be the Unicode character ‘±’.  In this
spreadsheet example, the second column is 10 times the first:

     #+name: cert
     | 12±2 | 120 +/- 20 |
     | 55±3 | 550 +/- 30 |
     | 9±0  | 90         |
     | 15±1 | 150 +/- 10 |
     | 21±1 | 210 +/- 10 |
     #+TBLFM: $2=10*$1

   Now, in the following example, OrgAggregate computes the sum of the
second column:

     #+BEGIN: aggregate :table "cert" :cols "vsum($2);f2"
     | vsum($2)       |
     |----------------|
     | 1120 +/- 38.73 |
     #+END:

   The computation made by Emacs Calc assumes that all input values
follow a Gaussian distribution, and are independent variables.  Then it
applies the textbook formula for combining Gaussian distributions.
Beware that your input values may not be independent with each other.
In this case, the resulting error will be slightly off (too small).


File: orgtbl-aggregate.info,  Node: Wide variety of inputs,  Next: Post-processing,  Prev: Cells processing,  Up: Top

12 Wide variety of inputs
*************************

As in any other Org Mode source block, the input table may come from
several places.  OrgAggregate adds even more kinds of input.

   Here we discus the ‘:table’ parameter.

* Menu:

* Standard Org Mode input::
* Virtual input table from Babel::
* An Org ID::
* CSV input::
* JSON input::
* Input slicing::
* Malformed input tables::
* Thecond filter: The cond filter.
* Virtual input columns with precompute::


File: orgtbl-aggregate.info,  Node: Standard Org Mode input,  Next: Virtual input table from Babel,  Up: Wide variety of inputs

12.1 Standard Org Mode input
============================

The parameter after ‘:table’ may be:

   • ‘mytable’: an ordinary Org Mode table in the same buffer, named
     ‘mytable’.

   • ‘/some/dir/file.org:mytable’: an ordinary Org Mode table named
     ‘mytable’, in a distant Org file named ‘/some/dir/file.org’.


File: orgtbl-aggregate.info,  Node: Virtual input table from Babel,  Next: An Org ID,  Prev: Standard Org Mode input,  Up: Wide variety of inputs

12.2 Virtual input table from Babel
===================================

   • ‘mybabel’: an Org Mode Babel block named ‘mybabel’ in the current
     buffer, generating a table as its output, written in any language.

   • ‘mybabel(param1=123,param2=456)’: passing parameters to an Org Mode
     Babel block named ‘mybabel’ in the current buffer, generating a
     table as its output, written in any language.

   • ‘/some/dir/file.org:mybabel(param1=123,param2=456)’: an Org Mode
     Babel block named ‘mybabel’ in a distant org file named
     ‘/some/dir/file.org’, called with parameters.

   The input table may be the result of executing a Babel script.  In
this case, the table is virtual in the sense that is appears nowhere.

   (Babel is the Org Mode infrastructure to run scripts in any language,
like Python, R, C++, Java, D, Rust, shell, whatever, with inputs and
outputs connected to Org Mode).

   Example:

   Here is a script in Emacs Lisp which creates an Org Mode table.

     #+name: ascript
     #+begin_src elisp :colnames yes
     `(
       ("label" value)                       ; cells are symbols or strings
       hline
       ,@(cl-loop
          for i from 10 to 20
          collect
          (list
           (format "lbl-%c" (+ ?A (% i 3)))  ; cell is a string
           i)))                              ; cell is a number
     #+end_src

   If executed, the script would output this table:

     #+RESULTS: ascript
     | label | value |
     |-------+-------|
     | lbl-B |    10 |
     | lbl-C |    11 |
     | lbl-A |    12 |
     | lbl-B |    13 |
     | lbl-C |    14 |
     | lbl-A |    15 |
     | lbl-B |    16 |
     | lbl-C |    17 |
     | lbl-A |    18 |
     | lbl-B |    19 |
     | lbl-C |    20 |

   But instead, OrgAggregate will execute the script and consume its
output:

     #+BEGIN: aggregate :table "ascript" :cols "label vsum(value)"
     | label | vsum(value) |
     |-------+-------------|
     | lbl-B |          58 |
     | lbl-C |          62 |
     | lbl-A |          45 |
     #+END:

   Here the parameter ‘:table’ specifies the name of the script to be
executed.

   *Beware* of the ‘:results code’ parameter.  It does not work as an
input for OrgAggregate.  This is because in this case the Babel script
returns a string, not a table.  Example:

     '((a b c)
       hline
       (1 2 3))

   Use ‘:results table’ or nothing instead.


File: orgtbl-aggregate.info,  Node: An Org ID,  Next: CSV input,  Prev: Virtual input table from Babel,  Up: Wide variety of inputs

12.3 An Org ID
==============

   • ‘34cbc63a-c664-471e-a620-d654b26ffa31’: an identifier of an Org
     Mode sub-tree.  The sub-tree is supposed to contain an Org table
     (which is retrieved) or a Babel script (which is executed).

   Those Org Mode identifiers span all known Org Mode files.  (Therefore
there is no need to specify a file).  To add such an identifier, put the
cursor on the heading of the sub-tree, and type ‘M-x org-id-get-create’.


File: orgtbl-aggregate.info,  Node: CSV input,  Next: JSON input,  Prev: An Org ID,  Up: Wide variety of inputs

12.4 CSV input
==============

CVS input is specific to OrgAggregate.  (Native Org Mode does not offers
those formats).

   • ‘/some/dir/file.csv:(csv params…)’: a comma-separated-values file
     in the CSV format, in the file ‘/some/dir/file.csv’.

   • ‘name(csv params…)’: CSV formatted data within an Org block named
     ‘"name"’, in the current file.

   • ‘/some/dir/file.org:name(csv params…)’: CSV formatted data within
     an Org block named ‘"name"’, in a distant Org file.

   The cells separators in the CSV files may be TAB, comma, or
semicolon, they are guessed and different separators may be mixed.

   Any empty row in the CSV file is interpreted as an horizontal
separator (‘hline’ in Org table parlance).

   Parameters may be:
   • ‘header’: the first row in the CSV file is interpreted as a header
     containing the column names.
   • ‘colnames (column1 column2 column3 …)’: the column names are given
     explicitly, in case the CSV file contains only data, no header.
   In any case, the columns may be referenced as ‘$1, $2, $3, …’ as
usual.

   When data is in an Org Mode file, it is supposed to be within a named
block of any kind.  Example with a "quote" block:

     #+name: mycsvdata
     #+begin_quote
     label,quantity
     yellow,27
     red,-61
     yellow,41
     red,-29
     red,-17
     #+end_quote


File: orgtbl-aggregate.info,  Node: JSON input,  Next: Input slicing,  Prev: CSV input,  Up: Wide variety of inputs

12.5 JSON input
===============

   • ‘/some/dir/file.json:(json params…)’: a file containing a JSON
     formatted table, in the file ‘/some/dir/file.csv’.

   • ‘name(json params…)’: JSON formatted data within an Org block named
     ‘"name"’, in the current file.

   • ‘/some/dir/file.org:name(json params…)’: JSON formatted data within
     an Org block named ‘"name"’, in a distant Org file.

   The accepted formats are a vector of vectors, or a vector of
hash-objects.

   Currently, no ‘params’ are recognized.

   Example of a vector of vectors:

     [
       ["mon",12,"red"  ],
       ["thu",34,"blue" ],
       ["wed",27,"green"],
       ["wed",21,"red"  ],
       ["mon", 7,"blue" ],
       …
     ]

   Example of a vector of hash-objects:

     [
       {"day":"mon", "quty":12, "color":"red"  },
       {"day":"thu", "quty":34, "color":"blue" },
       {"quty":27, "day":"wed", "color":"green"},
       {"quty":21, "color":"red", "day":"wed"  },
       {"day":"mon", "quty": 7, "color":"blue" },
       …
     ]

   In the case of hash-objects, the keys are collected as the header of
the resulting table, in the order seen in the JSON file.  In each
hash-object, the order of key-value pairs is irrelevant.

   A header may be specified in the JSON file as a first vector,
followed by an hline (horizontal line).  Example:

     [
       ["day","quty","color"],
       "hline",
       ["mon",12,"red"  ],
       ["thu",34,"blue" ],
       …
     ]

   Horizontal lines may be ‘"hline"’, ‘[]’, ‘{}’, or ‘null’.

   It is possible to mix both formats: vectors and hash-objects.
Example:

     [
       ["quty","color"],                         // incomplete header
       null,                                     // horizontal line
       {"day":"mon", "quty":12, "color":"red" }, // an hash-object
       [34, "blue", "thu" ],                     // a vector
       …
     ]

   When data is in an Org Mode file, it is supposed to be within a named
block of any kind.  Example with a "src" block for JavaScript:

     #+name: myjsondata
     #+begin_src js
     [
       ["day","quty","color"],   // names of columns in the header
       [],                       // an horizontal line
       ["mon",12,"red"  ],
       ["thu",34,"blue" ],
       …
     ]
     #+end_src


File: orgtbl-aggregate.info,  Node: Input slicing,  Next: Malformed input tables,  Prev: JSON input,  Up: Wide variety of inputs

12.6 Input slicing
==================

Org Mode also provides for table slicing.  All of the previous
references may be followed by an optional slicing.  Examples:

   • ‘mytable[0:5]’: retain only the first 6 rows of the input table; if
     the table has a header, then it counts as 2 rows (the header and
     the separation line).  In this example, it would retain rows 0 and
     1 for the header, and rows 2,3,4,5 for the content.

   • ‘mytable[*,0:1]’: retain only the first 2 columns.

   • ‘mytable[0:5,0:1]’: retain only the first 6 rows and the first 2
     columns.


File: orgtbl-aggregate.info,  Node: Malformed input tables,  Next: The cond filter,  Prev: Input slicing,  Up: Wide variety of inputs

12.7 Malformed input tables
===========================

Sometimes an input table may be unaligned or malformed, with incomplete
rows, like those ones:

     | type     | Fiber | Sugar |      | Carb |
     |----------+-------+-------+------+------|
     | eggplant |   2.5 |   3.2 |  0.8 |  8.6 |
     | tomato   |   0.6 |   2.1 |  0.8 |  3.4 |
     | onion    |   1.3 |   4.4 |  1.3 |  9.0 |
         | egg      |     0 |  18.3 | 31.9 | 18.3 |
     | rice     |   0.2 |     0 |  1.5 | 16.0 |
     | tofu     |  0.7
     | nut      |   2.6 |   1.3 |  4.9 |  7.2 |

     | type     | quty |
     |----------+------|
     | onion    |   70 |
     | tomato   |
     | eggplant |  300 |
       | tofu     |  100 |

   Missing cells are handled as though they were empty.


File: orgtbl-aggregate.info,  Node: The cond filter,  Next: Virtual input columns with precompute,  Prev: Malformed input tables,  Up: Wide variety of inputs

12.8 The :cond filter
=====================

This parameter is optional.  If present, it specifies a Lisp expression
which tells whether or not a row should be kept.  When the expression
evaluates to nil, the row is discarded.

   Examples of useful expressions includes:
   • ‘:cond (equal Color "Red")’
        • to keep only rows where ‘Color’ is ‘Red’
   • ‘:cond (> (string-to-number Quantity) 19)’
        • to keep only rows for which ‘Quantity’ is more than ‘19’
        • note the call to ‘string-to-number’; without this call,
          ‘Quantity’ would be used as a string
   • ‘:cond (> (* (string-to-number Level) 2.5) (string-to-number
     Quantity))’
        • to keep only rows for which ‘2.5*Level > Quantity’

   Beware with this example: ‘:cond (equal Color "Red")’.  The input
table should not have a column named ‘Red’, otherwise the condition will
mean: _keep only rows with the same value in columns Color and Red_

   As a special case, when ‘:cols’ parameter is not given, the result is
the same as ‘:cols "COL1 COL2 COL3..."’.  All columns in the input table
are specified as key columns, and output in the resulting table.

   This is useful when just filtering.  But be aware that aggregation
still occurs.  So duplicate input rows appear only once in the result.


File: orgtbl-aggregate.info,  Node: Virtual input columns with precompute,  Prev: The cond filter,  Up: Wide variety of inputs

12.9 Virtual input columns with ‘:precompute’
=============================================

What if we want to aggregate on months?  But the input table contains
only plain dates.  Example:

     #+name: without-months
     | Date             | Quantity |
     |------------------+----------|
     | [2037-03-12 thu] |    56.93 |
     | [2037-03-25 wed] |    20.99 |
     | [2037-04-07 tue] |    80.81 |
     | [2037-04-20 mon] |    22.26 |
     | [2037-05-03 sun] |    69.75 |
     | [2037-05-16 sat] |    39.91 |
     | [2037-05-29 fri] |    93.13 |
     | [2037-06-11 thu] |    17.11 |
     | [2037-06-24 wed] |    24.21 |
     | [2037-07-07 tue] |    82.38 |
     | [2037-07-20 mon] |    39.94 |
     | [2037-08-02 sun] |    81.90 |
     | [2037-08-15 sat] |    71.67 |
     | [2037-08-28 fri] |    82.81 |
     | [2037-09-10 thu] |    42.50 |
     | [2037-09-23 wed] |    62.52 |
     | [2037-10-06 tue] |     5.13 |

   We would like to specify the aggregation over ‘month(Date)’.  But
only plain columns may be used as aggregating keys.

   One way is to add a ‘Month’ column to the input table.  The modified
table looks like:

     #+name: with-months
     | Date             | Quantity | Month |
     |------------------+----------+-------|
     | [2037-03-12 thu] |    56.93 |     3 |
     | [2037-03-25 wed] |    20.99 |     3 |
     | [2037-04-07 tue] |    80.81 |     4 |
     | [2037-04-20 mon] |    22.26 |     4 |
     | [2037-05-03 sun] |    69.75 |     5 |
     | [2037-05-16 sat] |    39.91 |     5 |
     | [2037-05-29 fri] |    93.13 |     5 |
     | [2037-06-11 thu] |    17.11 |     6 |
     | [2037-06-24 wed] |    24.21 |     6 |
     | [2037-07-07 tue] |    82.38 |     7 |
     | [2037-07-20 mon] |    39.94 |     7 |
     | [2037-08-02 sun] |    81.90 |     8 |
     | [2037-08-15 sat] |    71.67 |     8 |
     | [2037-08-28 fri] |    82.81 |     8 |
     | [2037-09-10 thu] |    42.50 |     9 |
     | [2037-09-23 wed] |    62.52 |     9 |
     | [2037-10-06 tue] |     5.13 |    10 |
     #+TBLFM: $3=month($1)

   OrgAggregate allows adding input columns like this computed ‘Month’
column, without modifying the input table.  The ‘:precompute’ parameter
does that.  Example:

     #+BEGIN: aggregate :table "without-months" :cols "Month vsum(Quantity)" :precompute ("month(Date);'Month'")
     | Month | vsum(Quantity) |
     |-------+----------------|
     |     3 |          77.92 |
     |     4 |         103.07 |
     |     5 |         202.79 |
     |     6 |          41.32 |
     |     7 |         122.32 |
     |     8 |         236.38 |
     |     9 |         105.02 |
     |    10 |           5.13 |
     #+END:

   The specification ‘month(Date);'Month'’ means:
   • add a third column to the input table,
   • fill it with the formula ‘month(Date)’, which is a Calc formula,
   • give this new column the ‘Month’ title,
   • make this new column available for aggregation, as any other
     column.
   All this process is virtual.  The input table is not modified in any
way.

   If the title ‘Month’ is not specified, then the new virtual column
will be referred to as ‘$3’.

   Note that here the title was specified with single quotes.  This is
required to disambiguate with the format.  The syntax is consistent with
the one used in the ‘:cols’ parameter and the one used by Org Mode
spreadsheet formulas.

   The pre-computations may also be Lisp expressions, exactly like in
the usual Org table spreadsheet.  In this example, we want to aggregate
on coarse bins.  Bins are just hundredths of the first column:

     #+name: want-bins
     | 109 | 41.24 |
     | 140 | 40.60 |
     | 174 |  7.68 |
     | 288 | 33.96 |
     | 334 | 21.42 |
     | 418 | 74.73 |
     | 455 | 79.62 |
     | 479 | 22.23 |
     | 554 | 28.03 |
     | 678 | 64.12 |
     | 797 | 70.91 |
     | 947 | 93.48 |

     #+BEGIN: aggregate :table "want-bins" :cols "$3 vmean($2)" :precompute ("'(floor (/ (string-to-number $1) 100))")
     | $3 | vmean($2) |
     |----+-----------|
     |  1 |     29.84 |
     |  2 |     33.96 |
     |  3 |     21.42 |
     |  4 |     58.86 |
     |  5 |     28.03 |
     |  6 |     64.12 |
     |  7 |     70.91 |
     |  9 |     93.48 |
     #+END:

   Here ‘:precompute’ added a third column names ‘$3’.  It is computed
with a Lisp expression which:
   • ‘(string-to-number $1)’: converts the first column to a Lisp
     number,
   • ‘(/ … 100)’: divides it by 100,
   • ‘(floor (/ …))’: rounds the result to an integer.

   Note the single quote before the Lisp expression.  This is the way to
specify a Lisp expression in Org-Mode spreadsheet.

   Virtual columns may be formatted as any other column, with the same
syntax as in ‘:cols’ or in the Org table spreadsheet.  For example here
we give it 2 digits after dot:

     #+BEGIN: aggregate :table "want-bins" :cols "$3" :precompute "floor($1/100);%.2f"
     |   $3 |
     |------|
     | 1.00 |
     | 2.00 |
     | 3.00 |
     | 4.00 |
     | 5.00 |
     | 6.00 |
     | 7.00 |
     | 9.00 |
     #+END:

   Of course, those additional virtual input columns may be used for
other purposes than key columns.  They may enter in aggregating
formulas.  Or they may be used by the optional row filter (the ‘:cond’
parameter).  There is no difference between actual and virtual columns.

   The ‘:precompute’ parameter may be:

   • a list of strings, example:
     ("month(Date);'Month'" "day(Date);'Day'")

   • a single string with fields separated by ‘::’, like in the
     ‘#+tblfm:’ tags of a spreadsheet.  Example:
     "month(Date);'Month' :: day(Date);'Day'"

   • a string containing a single formula (actually this is a special
     case of the previous one).  Example:
     "month(Date);'Month'"

   *Warning!*

   The ‘:precompute’ feature is by far the slowest in OrgAggregate.  For
instance, a 3820 lines CSV file gets aggregated in less than 0.2
seconds.  But with a ‘:precompute’ expression adding a new column, time
goes up to 57 seconds (a ×285 slowdown).

   This is because ‘:precompute’ delegates the computing to Org-Mode
spreadsheet.  Behind the scene,
   • the input table is inserted as a temporary Org-Mode table,
   • the ‘:precompute’ formula is added,
   • Org-Mode spreadsheet refreshes the table,
   • then this temporary table becomes the new input before being
     erased.

   OrgAggregate has been designed & optimized for speed.  Thus it can
exhibit response times of fractions of second, even for tables with
thousands of rows.


File: orgtbl-aggregate.info,  Node: Post-processing,  Next: Pull & Push,  Prev: Wide variety of inputs,  Up: Top

13 Post-processing
******************

After OrgAggregate has generated the output table, it can be further
processed:
   • additional columns may be added with the standard Org Mode
     spreadsheet formulas.
   • any algorithm in an exotic language (Python, R, C++, Emacs Lisp,
     and so on) can be applied to the output.

* Menu:

* Spreadsheet formulas::
* Algorithm post processing::
* Grand total::
* Chaining::


File: orgtbl-aggregate.info,  Node: Spreadsheet formulas,  Next: Algorithm post processing,  Up: Post-processing

13.1 Spreadsheet formulas
=========================

Additional columns can be specified for the resulting table.  With a
previous example, adding a ‘:formula’ parameter, we specify a new column
‘$4’ which uses the aggregated columns.  It is translated into a usual
‘#+TBLFM:’ spreadsheet line.

     #+BEGIN: aggregate :table original :cols "Day vmean(Level) vsum(Quantity)" :formula "$4=$2*$3"
     | Day       | vmean(Level) | vsum(Quantity) |      |
     |-----------+--------------+----------------+------|
     | Monday    |         27.5 |             14 | 385. |
     | Tuesday   |           43 |             45 | 1935 |
     | Wednesday |           18 |             54 |  972 |
     | Thursday  |           43 |             83 | 3569 |
     | Friday    |            8 |             22 |  176 |
     #+TBLFM: $4=$2*$3
     #+END:

   Moreover, if a ‘#+TBLFM:’ was already there, it survives aggregation
re-computations.  And it may be enriched by a ‘:formula’ parameter as in
the previous example.

   The spreadsheet application is delegated to Org Mode, ensuring that
everything works reliably.  However many side processings done by Org
Mode are bypassed, mainly because the table handled to Org Mode is a
fresh one.  The alignment of columns is done by OrgAggregate, which is
way faster than the Org Mode one.  The result is about 5× faster than
the stock Org Mode one.

   This happens in _pull mode_ only.


File: orgtbl-aggregate.info,  Node: Algorithm post processing,  Next: Grand total,  Prev: Spreadsheet formulas,  Up: Post-processing

13.2 Algorithm post processing
==============================

The aggregated table can be post-processed with the ‘:post’ parameter.
It accepts a Lisp ‘lambda’, a Lisp function, or a Babel block in any
exotic language (R, Python, C++, Emacs Lisp and so on).

   The process receives the aggregated table as parameter in the form of
a Lisp expression.  It can process it in any way it wants, provided it
returns a valid Lisp table.

   A Lisp table is a list of rows.  Each row is either a list of cells,
or the special symbol ‘hline’.

   In this example, a ‘lambda’ expression adds a ‘hline’ and a row for
_Sunday_.

     #+BEGIN: aggregate :table original :cols "Day vsum(Quantity)" :post (lambda (table) (append table '(hline (Sunday "0.0"))))
     | Day       | vsum(Quantity) |
     |-----------+----------------|
     | Monday    |             14 |
     | Tuesday   |             45 |
     | Wednesday |             54 |
     | Thursday  |             83 |
     | Friday    |             22 |
     |-----------+----------------|
     | Sunday    |            0.0 |
     #+END:

   The ‘lambda’ can be moved to a ‘defun’.  The function is then passed
to the ‘:post’ parameter:

     #+begin_src elisp
     (defun my-function (table)
       (append table
               '(hline (Sunday "0.0"))))
     #+end_src

     ... :post my-function

   The ‘:post’ parameter can also refer to a Babel Block.  Example:

     #+BEGIN: aggregate :table original :cols "Day vsum(Quantity)" :post "my-babel-block(tbl=*this*)"
     ...
     #+END:

     #+name: my-babel-block
     #+begin_src elisp :var tbl=""
     (append tbl
             '(hline (Sunday "0.0")))
     #+end_src

   *Beware!*  You may want to add ‘:colnames t’ to your Babel block.
Otherwise the table’s header will be lost.


File: orgtbl-aggregate.info,  Node: Grand total,  Next: Chaining,  Prev: Algorithm post processing,  Up: Post-processing

13.3 Grand total
================

She (the user) may be tempted to add the grand total at the bottom of
the aggregation.  Example of such a temptation:

     #+begin: aggregate :table original :cols "Day vmean(Level) vsum(Quantity)"
     | Day       | vmean(Level) | vsum(Quantity) |
     |-----------+--------------+----------------|
     | Monday    |         27.5 |             14 |
     | Tuesday   |           43 |             45 |
     | Wednesday |           18 |             54 |
     | Thursday  |           43 |             83 |
     | Friday    |            8 |             22 |
     |-----------+--------------+----------------|
     | Total     |              |            218 |
     #+TBLFM: @7$3=vsum(@I..@II)
     #+end

   With OrgAggreagate post-processing, it is easy.
  1. Just put in place a small algorithm to add two lines.

     :post (append *this* '(hline ("Total" "" "")))
                     ▲        ▲               ▲
                     ╰─────╮  │               │
     the aggregated table╶─╯  │               │
     one horizontal line╶─────╯               │
     an empty cell to recieve the total╶──────╯

   The ‘*this*’ Lisp variable contains the aggregated table, just while
the post-processing takes place.  The ‘append’ Lisp function adds two
rows to the aggregated table, and returns the amended table.

   Note that the ‘:post’ parameter may be:
   • a small Lisp expression, as in this example,
   • a lambda expression, which parameter is the aggregated table,
   • the name of a Lisp function, which is passed the aggregate table,
   • the name of a Babel block, written in any supported language.

   • Fill the additional cell with a formula for the total.

     @>$2=vsum(@I..@II)
     ▲ ▲   ▲   ▲   ▲
     │ │   │   │   ╰──────────────╮
     │ │   │   ╰────────────────╮ │
     │ │   ╰─────────────────╮  │ │
     │ ╰────────────╮        │  │ │
     ╰──────────╮   │        │  │ │
     last line╶─╯   │        │  │ │
     second column╶─╯        │  │ │
     sum all values between╶─╯  │ │
     the first horizontal line╶─╯ │
     and the second one╶──────────╯

   In this way, the grand-total will be recomputed each time the
aggregation is refreshed (‘C-c C-c’).  Note the use of ‘@>$2’ for the
coordinates of the cell receiving the total, instead of, for instance
‘@7$3’.  This ensures that the formula will continue to be applied on
the last row, even if the aggregated table grows later on.  The same
idea applies for the ‘@I..@II’ range, instead of, for instance ‘@2..@6’.

   Even though OrgAggregate offers the user a versatil post-processing
to add a grand total, there are many reasons not to.  If she does, she
is quietly entering the same nightmare which plagues spreadsheets.
Everything will become harder and harder to maintain.

   It seems natural to add a grand total right below the column.  But
suppose that now she also want the standard deviation of this same
column.  Where to put it?  There is a blank cell just left of the grand
total.  She puts the standard deviation there:

     #+begin: aggregate :table original :cols "Day vmean(Level) vsum(Quantity)"
     | Day       | vmean(Level) | vsum(Quantity) |
     |-----------+--------------+----------------|
     | Monday    |         27.5 |             14 |
     | Tuesday   |           43 |             45 |
     | Wednesday |           18 |             54 |
     | Thursday  |           43 |             83 |
     | Friday    |            8 |             22 |
     |-----------+--------------+----------------|
     | Total     |    27.409852 |            218 |
     #+TBLFM: @7$3=vsum(@I..@II)::@7$2=vsdev(@I$3..@II$3)
     #+end

   But then this ‘27.409852’ looks like the grand total of the second
column, but it is not.

   Later on, she may want to further process this aggregated table, for
example to plot it.  The grand total row will be an annoyance.  It will
be tedious to get ride of it.

   She should instead consider creating a second aggregation with the
grand total:

     #+begin: aggregate :table original :cols "vsdev(Level) vsum(Quantity)"
     | vsdev(Level) | vsum(Quantity) |
     |--------------+----------------|
     |    27.409852 |            218 |
     #+end

   Easy, maintainable, no awkward decisions to remember and document.
She keeps it simple.


File: orgtbl-aggregate.info,  Node: Chaining,  Prev: Grand total,  Up: Post-processing

13.4 Chaining
=============

The result of an aggregation may become the source of further
processing.  To do that, just add a ‘#+NAME:’ or ‘#+TBLNAME:’ line just
above the aggregated table.  Here is an example of a double aggregation:

     #+NAME: squantity
     #+BEGIN: aggregate :table original :cols "Day vsum(Quantity)"
     | Day       | SQuantity |
     |-----------+-----------|
     | Monday    |        14 |
     | Tuesday   |        45 |
     | Wednesday |        54 |
     | Thursday  |        83 |
     | Friday    |        22 |
     #+TBLFM: @1$2=SQuantity
     #+END:

     #+BEGIN: aggregate :table "squantity" :cols "vsum(SQuantity)"
     | vsum(SQuantity) |
     |-----------------|
     |             218 |
     #+END:

   Note the spreadsheet cell formula ‘@1$2=SQuantity’, which changes the
column heading from it default ‘vsum(Quantity)’ to ‘SQuantity’.  This
new heading will survive any refresh.

   Sometimes the name of the aggregated table is not found by some babel
block referencing it (Gnuplot blocks are among them).  To fix that, just
exchange the ‘#+NAME:’ and ‘#+BEGIN:’ lines:

     #+BEGIN: aggregate :table original :cols "Day vsum(Quantity)"
     #+NAME: squantity
     | Day       | SQuantity |
     |-----------+-----------|
     | Monday    |        14 |
     | Tuesday   |        45 |
     | Wednesday |        54 |
     | Thursday  |        83 |
     | Friday    |        22 |
     #+TBLFM: @1$2=SQuantity
     #+END:

   The ‘#.NAME:’ line will survive when recomputing the aggregation (as
‘#.TBLFM:’ line survives)


File: orgtbl-aggregate.info,  Node: Pull & Push,  Next: Debugging,  Prev: Post-processing,  Up: Top

14 Pull & Push
**************

Two modes are available: _pull_ & _push_.

* Menu:

* Pull mode::
* Push mode::
* Pull or push ?::


File: orgtbl-aggregate.info,  Node: Pull mode,  Next: Push mode,  Up: Pull & Push

14.1 Pull mode
==============

In the _pull_ mode, we use so called _"dynamic blocks"_.  The resulting
table knows how to build itself.

   Example:

   We have a source table which is unaware that it will be derived in an
aggregated table:

     #+NAME: source1
     | Day       | Color | Level | Quantity |
     |-----------+-------+-------+----------|
     | Monday    | Red   |    30 |       11 |
     | Monday    | Blue  |    25 |        3 |
     | Tuesday   | Red   |    51 |       12 |
     | Tuesday   | Red   |    45 |       15 |
     | Tuesday   | Blue  |    33 |       18 |
     | Wednesday | Red   |    27 |       23 |
     | Wednesday | Blue  |    12 |       16 |
     | Wednesday | Blue  |    15 |       15 |
     | Thursday  | Red   |    39 |       24 |
     | Thursday  | Red   |    41 |       29 |
     | Thursday  | Red   |    49 |       30 |
     | Friday    | Blue  |     7 |        5 |
     | Friday    | Blue  |     6 |        8 |
     | Friday    | Blue  |    11 |        9 |

   We create somewhere else a _dynamic block_ which carries the
specification of the aggregation:

     #+BEGIN: aggregate :table "source1" :cols "Day vmean(Level) vsum(Quantity)"
     | Day       | vmean(Level) | vsum(Quantity) |
     |-----------+--------------+----------------|
     | Monday    |         27.5 |             14 |
     | Tuesday   |           43 |             45 |
     | Wednesday |           18 |             54 |
     | Thursday  |           43 |             83 |
     | Friday    |            8 |             22 |
     #+END

   Typing ‘C-c C-c’ in the dynamic block recomputes it freshly.


File: orgtbl-aggregate.info,  Node: Push mode,  Next: Pull or push ?,  Prev: Pull mode,  Up: Pull & Push

14.2 Push mode
==============

In _push_ mode, the source table drives the creation of derived tables.
We specify the wanted results in ‘#+ORGTBL: SEND’ directives (as many as
desired):

     #+ORGTBL: SEND derived1 orgtbl-to-aggregated-table :cols "vmean(Level) vsum(Quantity)"
     #+ORGTBL: SEND derived2 orgtbl-to-aggregated-table :cols "Day vmean(Level) vsum(Quantity)"
     | Day       | Color | Level | Quantity |
     |-----------+-------+-------+----------|
     | Monday    | Red   |    30 |       11 |
     | Monday    | Blue  |    25 |        3 |
     | Tuesday   | Red   |    51 |       12 |
     | Tuesday   | Red   |    45 |       15 |
     | Tuesday   | Blue  |    33 |       18 |
     | Wednesday | Red   |    27 |       23 |
     | Wednesday | Blue  |    12 |       16 |
     | Wednesday | Blue  |    15 |       15 |
     | Thursday  | Red   |    39 |       24 |
     | Thursday  | Red   |    41 |       29 |
     | Thursday  | Red   |    49 |       30 |
     | Friday    | Blue  |     7 |        5 |
     | Friday    | Blue  |     6 |        8 |
     | Friday    | Blue  |    11 |        9 |

   We must create the receiving blocks somewhere else in the same file:

     #+BEGIN RECEIVE ORGTBL derived1
     #+END RECEIVE ORGTBL derived1

     #+BEGIN RECEIVE ORGTBL derived2
     #+END RECEIVE ORGTBL derived2

   Then we come back to the source table and type ‘C-c C-c’ with the
cursor on the 1st pipe of the table, to refresh the derived tables:

     #+BEGIN RECEIVE ORGTBL derived1
     |  vmean(Level) | vsum(Quantity) |
     |---------------+----------------|
     | 27.9285714286 |            218 |
     #+END RECEIVE ORGTBL derived1

     #+BEGIN RECEIVE ORGTBL derived2
     | Day       | vmean(Level) | vsum(Quantity) |
     |-----------+--------------+----------------|
     | Monday    |         27.5 |             14 |
     | Tuesday   |           43 |             45 |
     | Wednesday |           18 |             54 |
     | Thursday  |           43 |             83 |
     | Friday    |            8 |             22 |
     #+END RECEIVE ORGTBL derived2


File: orgtbl-aggregate.info,  Node: Pull or push ?,  Prev: Push mode,  Up: Pull & Push

14.3 Pull or push ?
===================

Pull & push modes use the same engine in the background.  Thus, using
either is just a matter of convenience.

   Pull mode is the most straightforward.  Also the wizard operates on
the pull mode only.  Almost all examples in this documentation are in
pull mode.  If you cannot decide, just use the pull mode.

   Glitch: in push mode you may see strange output like ‘\_{}’.  This is
an escape generated by Org Mode (nothing to do with OrgAggregate).  It
happens for the following characters: ‘&%#_^’ To disable that, in the
‘#+ORGTBL: SEND’ line, add this parameter: ‘:no-escape true’


File: orgtbl-aggregate.info,  Node: Debugging,  Next: Tricks,  Prev: Pull & Push,  Up: Top

15 Debugging
************

The work of OrgAggregate is to hand out pieces of the input table to
Calc (the Emacs calculator).

   Is some intricate cases, it may be useful to see what is going on.
The debugging formatters come handy.

* Menu:

* Seeing the $ forms::
* Seeing Calc formulas before evaluation::
* Seeing Lisp internal form of Calc formulas::
* Example of debugging vsum(nn^2)::
* Summary of debugging formatters::


File: orgtbl-aggregate.info,  Node: Seeing the $ forms,  Next: Seeing Calc formulas before evaluation,  Up: Debugging

15.1 Seeing the $ forms
=======================

Here is an example input table:

     #+name: inputdebug
     |   nn | aa |
     |------+----|
     | 1.23 | a  |
     | 7.65 | b  |
     | 8.46 | c  |
     |------+----|
     | 2.44 | d  |
     | 6.81 | e  |

   And here is an aggregation to debug:

     #+BEGIN: aggregate :table "inputdebug" :cols "hline vsum(nn*10) vsum(aa+7)"
     | hline | vsum(nn*10) | vsum(aa+7)     |
     |-------+-------------+----------------|
     |     0 |       173.4 | a + b + c + 21 |
     |     1 |        92.5 | d + e + 14     |
     #+END:

   So far so good.  But we would like to know what Calc did.  To do so
let us add the ‘c’ formatter.

     #+BEGIN: aggregate :table "inputdebug" :cols "hline vsum(nn*10);c vsum(aa+7);c"
     | hline | vsum(nn*10) | vsum(aa+7) |
     |-------+-------------+------------|
     |     0 | vsum($1*10) | vsum($2+7) |
     |     1 | vsum($1*10) | vsum($2+7) |
     #+END:

   Each output cell now contains the formula, with column names replaced
by dollar equivalent forms.  But without any further processing.


File: orgtbl-aggregate.info,  Node: Seeing Calc formulas before evaluation,  Next: Seeing Lisp internal form of Calc formulas,  Prev: Seeing the $ forms,  Up: Debugging

15.2 Seeing Calc formulas before evaluation
===========================================

Let us go one step further with the ‘C’ formatter:

     #+BEGIN: aggregate :table "inputdebug" :cols "hline vsum(nn*10);C vsum(aa+7);C"
     | hline | vsum(nn*10)                 | vsum(aa+7)          |
     |-------+-----------------------------+---------------------|
     |     0 | vsum([1.23, 7.65, 8.46] 10) | vsum([a, b, c] + 7) |
     |     1 | vsum([2.44, 6.81] 10)       | vsum([d, e] + 7)    |
     #+END:

   The dollar forms were replaced by Calc vectors made of input cells.
No foldings or simplifications went on.  The vectors are slices of
columns, selected by OrgAggregate in response of the ‘hline’
aggregation.

   We see that multiplying by ‘10’ or adding ‘7’ is done on a Calc
vector.  It happens that Calc knows how to multiply or add something to
a vector.  OrgAggregate does not perform those operations, it delegates
them to Calc.


File: orgtbl-aggregate.info,  Node: Seeing Lisp internal form of Calc formulas,  Next: Example of debugging vsum(nn^2),  Prev: Seeing Calc formulas before evaluation,  Up: Debugging

15.3 Seeing Lisp internal form of Calc formulas
===============================================

We can also view the same results, formatted as Lisp forms (rather than
Calc forms) with the ‘Q’ formatter:

     #+BEGIN: aggregate :table "inputdebug" :cols "hline vsum(nn*10);Q vsum(aa+7);Q"
     | hline | vsum(nn*10)                                                               | vsum(aa+7)                                                            |
     |-------+---------------------------------------------------------------------------+-----------------------------------------------------------------------|
     |     0 | (calcFunc-vsum (* (vec (float 123 -2) (float 765 -2) (float 846 -2)) 10)) | (calcFunc-vsum (+ (vec (var a var-a) (var b var-b) (var c var-c)) 7)) |
     |     1 | (calcFunc-vsum (* (vec (float 244 -2) (float 681 -2)) 10))                | (calcFunc-vsum (+ (vec (var d var-d) (var e var-e)) 7))               |
     #+END:

   This is the internal, Lisp representation of Calc formulas.


File: orgtbl-aggregate.info,  Node: Example of debugging vsum(nn^2),  Next: Summary of debugging formatters,  Prev: Seeing Lisp internal form of Calc formulas,  Up: Debugging

15.4 Example of debugging vsum(nn^2)
====================================

Beware of a formula like ‘vsum(nn^2)’.  This gives the expected result,
although not in the obvious way.  Let us see what happens, thanks to the
‘C’ debugging formatter:

     #+BEGIN: aggregate :table "inputdebug" :cols "hline vsum(nn^2);C"
     | hline | vsum(nn^2)                 |
     |-------+----------------------------|
     |     0 | vsum([1.23, 7.65, 8.46]^2) |
     |     1 | vsum([2.44, 6.81]^2)       |
     #+END:

   We are not summing squares.  We are squaring Calc vectors.  Calc
being a mathematical tool, it interprets the product of two vectors as
the sum of the products element-wise, as a mathematician would do.  Then
‘vsum’ is applied on a single resulting value.  So ‘vsum’ is useless in
this case.  That can be confirmed:

     #+BEGIN: aggregate :table "inputdebug" :cols "hline vsum(nn^2) nn^2 vprod(nn^2)"
     | hline | vsum(nn^2) |    nn^2 | vprod(nn^2) |
     |-------+------------+---------+-------------|
     |     0 |    131.607 | 131.607 |     131.607 |
     |     1 |    52.3297 | 52.3297 |     52.3297 |
     #+END:

   Therefore, changing ‘vsum’ to ‘vprod’ does not change the result.
This can be unexpected.


File: orgtbl-aggregate.info,  Node: Summary of debugging formatters,  Prev: Example of debugging vsum(nn^2),  Up: Debugging

15.5 Summary of debugging formatters
====================================

To summarize the debugging settings:
   • ‘c’: output Calc formula
   • ‘C’: output Calc formula with dollar forms substituted by actual
     input data
   • ‘q’: output Lisp formula
   • ‘Q’: output Lisp formula with column forms substituted by actual
     input data


File: orgtbl-aggregate.info,  Node: Tricks,  Next: Installation,  Prev: Debugging,  Up: Top

16 Tricks
*********

This chapter collects some tricks that may be useful.

* Menu:

* Sorting: Sorting (1).
* A few lowest or highest values::
* Span of values::
* No aggregation::
* Look at the first rows of a large table::


File: orgtbl-aggregate.info,  Node: Sorting (1),  Next: A few lowest or highest values,  Up: Tricks

16.1 Sorting
============

     #+name: trick_table_1
     | column |
     |--------|
     |    677 |
     |    713 |
     |    459 |
     |    537 |
     |    881 |

   When several cells of a column need to be sorted, the Calc
‘calc-sort()’ function is handy:

     #+BEGIN: aggregate :table "trick_table_1" :cols "(column) sort(column)"
     | (column)                  | sort(column)              |
     |---------------------------+---------------------------|
     | [677, 713, 459, 537, 881] | [459, 537, 677, 713, 881] |
     #+END:

   • ‘(column)’ gives the list of values to aggregate, without
     aggregating them.
   • ‘sort(column)’ gives the same list sorted in ascending order.


File: orgtbl-aggregate.info,  Node: A few lowest or highest values,  Next: Span of values,  Prev: Sorting (1),  Up: Tricks

16.2 A few lowest or highest values
===================================

Used with ‘subvec()’, ‘sort()’ can retrieve the two lowest or the two
highest values:

     #+BEGIN: aggregate :table "trick_table_1" :cols "subvec(sort(column),1,3) subvec(sort(column),count()-1)"
     | subvec(sort(column),1,3) | subvec(sort(column),count()-1) |
     |--------------------------+--------------------------------|
     | [459, 537]               | [713, 881]                     |
     #+END:

   • ‘subvec(...,1,3)’ extracts the two first values: from ‘1’ to ‘3’
     excluded.
   • ‘subvec(...,count()-1)’ extracts the two last values, numbered
     ‘count()-1’ and ‘count()’

   And of course we may retrieve the average of the two first and the
two last values:

     #+BEGIN: aggregate :table "trick_table_1" :cols "vmean(subvec(sort(column),1,3)) vmean(subvec(sort(column),count()-1))"
     | vmean(subvec(sort(column),1,3)) | vmean(subvec(sort(column),count()-1)) |
     |---------------------------------+---------------------------------------|
     |                             498 |                                   797 |
     #+END:


File: orgtbl-aggregate.info,  Node: Span of values,  Next: No aggregation,  Prev: A few lowest or highest values,  Up: Tricks

16.3 Span of values
===================

‘vmin()’ and ‘vmax()’ can compute the span of aggregated values:

     #+BEGIN: aggregate :table "trick_table_1" :cols "vmin(column) vmax(column) vmax(column)-vmin(column)"
     | vmin(column) | vmax(column) | vmax(column)-vmin(column) |
     |--------------+--------------+---------------------------|
     |          459 |          881 |                       422 |
     #+END:


File: orgtbl-aggregate.info,  Node: No aggregation,  Next: Look at the first rows of a large table,  Prev: Span of values,  Up: Tricks

16.4 No aggregation
===================

Why would one want to use OrgAggregate while not aggregating?  To
benefit from the other features of OrgAggregate:
   • column rearrangement
   • sorting
   • formatting
   • ‘#+TBLFM’ survival
   • row filtering
   • preprocess
   • postprocess

   To do so, mention the virtual column ‘@#’ in ‘:cols’ and make it
invisible with ‘;<>’.  As ‘@#’ is different for each row, the
aggregation will consider each row as a separate group.  Therefore, no
aggregation on another column will do anything more.

   For example, here we:
   • put ‘Color’ as the first column (it is the second in the input),
   • ignore the ‘Day’ column,
   • sort by ‘Level’,
   • compute ‘Quantity/7’,
   • format it with 2 digits after dot.

     #+BEGIN: aggregate :table "original" :cols "@#;<> Color Level;^n vmax(Quantity/7);'Q10';f2"
     | Color | Level |  Q10 |
     |-------+-------+------|
     | Blue  |     6 | 1.14 |
     | Blue  |     7 | 0.71 |
     | Blue  |    11 | 1.29 |
     | Blue  |    12 | 2.29 |
     | Blue  |    15 | 2.14 |
     | Blue  |    25 | 0.43 |
     | Red   |    27 | 3.29 |
     | Red   |    30 | 1.57 |
     | Blue  |    33 | 2.57 |
     | Red   |    39 | 3.43 |
     | Red   |    41 | 4.14 |
     | Red   |    45 | 2.14 |
     | Red   |    49 | 4.29 |
     | Red   |    51 | 1.71 |
     #+END:

   We used the ‘vmax()’ aggregating function on ‘Quantity/7’, because
otherwise we would get a vector with a single value.  As there is a
single value, any aggregating function will do the trick: ‘vmin()’,
‘head()’, ‘rtail()’, ‘vsum()’, ‘vprod()’, ‘vmean()’, ‘vgmean()’,
‘vhmean()’, ‘vspan()’, ‘vmedian()’.


File: orgtbl-aggregate.info,  Node: Look at the first rows of a large table,  Prev: No aggregation,  Up: Tricks

16.5 Look at the first rows of a large table
============================================

We have a table with thousands of rows.  We are interested just in its
header, and maybe the first few rows.  OrgAggregate can provide that:

     #+BEGIN: aggregate :table "largetable.csv:(csv header)[0:3]"
     | day | color  | quantity |
     |-----+--------+----------|
     | thu | red    |      115 |
     | fri | yellow |       78 |
     #+END:

   The trick is to use slicing.  Here we specified ‘[0:3]’ to retrieve
only the 4 first rows of the huge input.  We omitted the ‘:cols’
parameters, therefore asking OrgAggregate to provide all input columns.


File: orgtbl-aggregate.info,  Node: Installation,  Next: Authors contributors,  Prev: Tricks,  Up: Top

17 Installation
***************

Emacs package on Melpa: add the following lines to your ‘.emacs’ file,
and reload it.

     (add-to-list 'package-archives '("melpa" . "http://melpa.org/packages/") t)
     (package-initialize)

   You may also customize this variable:
     M-x customize-variable package-archives

   Then browse the list of available packages and install
‘orgtbl-aggregate’
     M-x package-list-packages

   Alternatively, you can download the lisp file, and load it:

     (load-file "orgtbl-aggregate.el")


File: orgtbl-aggregate.info,  Node: Authors contributors,  Next: Changes,  Prev: Installation,  Up: Top

18 Authors, contributors
************************

Authors
   • Thierry Banel, tbanelwebmin at free dot fr, inception &
     implementation.
   • Michael Brand, Calc unleashed, ‘#+TBLFM’ survival, empty input
     cells, formatters.

   Contributors
   • Eric Abrahamsen, non-ASCII column names
   • Alejandro Erickson, quoting non alphanumeric column names
   • Uwe Brauer, simpler example in documentation, take
     org-calc-default-modes preferences into account
   • Peking Duck, fixed obsolete letf function
   • Bill Hunker, discovered ‘\_{}’ escape
   • Dirk Schmitt, surviving ‘#.NAME:’ line
   • Dale Sedivec, case insensitive ‘#+NAME:’ tags
   • falloutphil, underscore in column names
   • Baudilio Tejerina, t, T, U formatters
   • Marco Pas, bug comparing empty string
   • wuqui, sorting output table, filtering only
   • Nicolas Viviani, output hlines
   • Nils Lehmann, support old versions of the rx library
   • Shankar Rao, ‘:post’ post-processing
   • Misohena (<https://misohena.jp/blog/author/misohena>), double width
     Japanese characters (string-width vs.  length)
   • Kevin Brubeck Unhammer, ignore formatting cookies
   • Tilmann Singer, more flexibility in duration format
   • Piotr Panasiuk, ‘#+CAPTION:’ and any tags survive
   • Luis Miguel Hernanz, fix regex bug
   • Jason Hemann, output column names no longer have quotes
   • Tilmann Singer, computed aggregating bins, ‘"month(Date)"’ in his
     use case
   • Misohena (on GitHub this time) gave feedback on the ‘el-tblfn’
     example.


File: orgtbl-aggregate.info,  Node: Changes,  Next: GPL 3 License,  Prev: Authors contributors,  Up: Top

19 Changes
**********

Top: earliest change.  Bottom: latest change.

   • Wizard now correctly asks for columns with ‘$1, $2...’ names when
     table header is missing
   • Handle tables beginning with hlines
   • Handle non-ASCII column names
   • ‘:formula’ parameter and ‘#+TBLFM’ survival
   • Empty cells are ignored.
   • Empty output upon too small input set
   • Fix ordering of output values
   • Aggregations formulas may now be arbitrary expressions
   • Table headers (and the lack of) are better handled
   • Modifiers and formatters can now be specified as in the spreadsheet
   • Aggregation function names can optionally have a leading ‘v’, like
     ‘sum’ & ‘vsum’
   • Increased performance on large data sets
   • Tables can be named with ‘#+NAME:’ besides ‘#+TBLNAME:’
   • Document Melpa installation
   • Support quoting of column names, like "a.b" or ’c/d’
   • Disable ‘\_{}’ escape
   • ‘#+NAME:’ inside ‘#+BEGIN:’ survives
   • Missing input cells handled as empty ones
   • Back-port Org Mode ‘9.4’ speed up
   • Increase performance when inserting result into the buffer
   • Aligned output in push mode
   • Added a hash-table to speedup aggregation
   • Back-port org-table-to-lisp which is now much faster
   • ‘vlist(X)’ now yields input cells verbatim were ‘(X)’ yields Calc
     processed input cells
   • Document dates handling and the ‘date()’ function
   • Implement ‘HH:MM:SS’ durations and ‘T’, ‘t’, ‘U’ formatters
   • Sort output
   • Create hlines in the output
   • Missing :cond parameter means all columns
   • Remove ‘C-c C-x i’, use standard ‘C-c C-x x’ instead
   • Avoid name collision between Calc functions and columns
   • More readable & faster code
   • Support for old versions of the rx library
   • ‘:post’ post-processing
   • Propagate multiple rows source header to the aggregated header
   • Ignore data rows containing formatting cookies
   • Follow Org Mode way of handling Calc settings in Lisp code
   • Hours in durations are no longer restricted to 2 digits
   • 3x speedup ‘org-table-to-lisp’ and avoid Emacs 27 to 30
     incompatibilities
   • ‘#+CAPTION:’ and any other tag survive inside ‘#+BEGIN:’
   • Output column names are now stripped from quotes, better reflecting
     input names.
   • Table-of-contents in README.org (thanks org-make-toc)
   • Add formatters ‘c’ ‘C’ ‘q’ ‘Q’ (useful for debugging or
     understanding OrgAggregate)
   • Formulas involving ‘hline’ like ‘vmean(hline*10)’ are now taken
     into account
   • Documentation is now integrated right into Emacs in the ‘info’
     format.  Type ‘M-: (info "orgtbl-aggregate")’
   • Input table may now be the result of a Babel script (virtual
     table).
   • Better handling of user errors in the ‘:post’ directive.
   • Speedup of resulting table recalculation when there are formulas in
     ‘#+tblfm:’ or in ‘:formula’.  The overall aggregation may be up to
     x6 faster and ÷5 less memory hungry.
   • Circumvent an Org Mode bug in case there are a column-formula along
     with a cell-formula, the cell-one not being calculated.  (Bonus:
     15% speedup).
   • Fix issue #24: bug in date parsing.
   • Virtual pre-computed input columns.
   • Better explanation of the input table reference syntax, including
     distant tables and virtual table produced by Babel blocks.
   • Support for CSV and JSON formatted input tables.
   • New ‘@#’ virtual column giving the number of each row, pretty much
     like the Org table spreadsheet ‘@#’ virtual column.
   • Header and column names can be specified for CSV input tables, as
     well as horizontal separators (‘hline’).
   • Aggregation of the titles of this README.
   • New free-form wizard.
   • Illustrate README with Uniline graphics.
   • JSON and CSV input tables can now live inside Org Mode blocks.
   • Example for computing a refreshable grand total.
   • Document intervals and error-forms handling.
   • Special columns ‘@#’ and ‘hline’ are handled by transpose.
   • Preserve every side lines in ‘#+begin:’ … ‘#+end:’ blocks upon
     refresh or wizard update.
   • Simplify directory part when querying a file name.
   • Centralize cell→string in ‘-cell-to-string’ which is the fastest.
   • Column alignment with the ‘<c>’, ‘<l>’, ‘<r>’ cookies.


File: orgtbl-aggregate.info,  Node: GPL 3 License,  Prev: Changes,  Up: Top

20 GPL 3 License
****************

Copyright (C) 2013-2026 Thierry Banel

   orgtbl-aggregate is free software: you can redistribute it and/or
modify it under the terms of the GNU General Public License as published
by the Free Software Foundation, either version 3 of the License, or (at
your option) any later version.

   orgtbl-aggregate is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
General Public License for more details.

   You should have received a copy of the GNU General Public License
along with this program.  If not, see <http://www.gnu.org/licenses/>.



Tag Table:
Node: Top209
Node: New3207
Node: Examples3548
Node: A very simple example3818
Node: Demonstrate sum and average computing6173
Node: Example without days9511
Node: Example of counting each combination10379
Node: Stop reading here! 80/2013059
Node: Name your input table13539
Node: Create an aggregation block14192
Node: Refresh the aggregation15114
Node: Equivalent in SQL R Datamash el-tblfn Awk C++16754
Node: SQL equivalent17398
Node: R equivalent17744
Node: Datamash equivalent18278
Node: el-tblfn18852
Node: Awk equivalent19584
Node: C++ equivalent20314
Node: Wizards21438
Node: Guiding (traditional) wizard21734
Node: Experimental free form wizard23903
Node: The cols parameter25949
Node: Names of input columns26620
Node: Grouping specifications in cols27695
Node: The hline column28251
Node: The @# column31039
Node: Aggregation formulas in cols33520
Node: Correlation of two columns41061
Node: [Almost) any expression can be specified42800
Node: Column names44274
Node: Input table with or without a header44550
Node: Column names of the input table45943
Node: Multiple lines header47112
Node: Custom column names50029
Node: Formatters52439
Node: Org Mode compatible formatters52687
Node: Debugging formatters54888
Node: Alignment cookies55560
Node: Discarding an output column57479
Node: Sorting60266
Node: Example with one sorting column60472
Node: Several sorting columns61753
Node: hlines in the output table64105
Node: Output hlines depends on sorting columns64503
Node: Example with hline 267399
Node: Cells processing72491
Node: Where Calc interpretation happens?72986
Node: Dates78463
Node: Durations80468
Node: Empty and malformed input cells82302
Node: Symbolic computation83972
Node: Intervals86269
Node: Error or precision forms87065
Node: Wide variety of inputs88236
Node: Standard Org Mode input88826
Node: Virtual input table from Babel89303
Node: An Org ID91904
Node: CSV input92505
Node: JSON input94026
Node: Input slicing96502
Node: Malformed input tables97232
Node: The cond filter98137
Node: Virtual input columns with precompute99662
Node: Post-processing106404
Node: Spreadsheet formulas106945
Node: Algorithm post processing108502
Node: Grand total110465
Node: Chaining115287
Node: Pull & Push116976
Node: Pull mode117210
Node: Push mode118913
Node: Pull or push ?121121
Node: Debugging121855
Node: Seeing the $ forms122378
Node: Seeing Calc formulas before evaluation123588
Node: Seeing Lisp internal form of Calc formulas124722
Node: Example of debugging vsum(nn^2)125931
Node: Summary of debugging formatters127360
Node: Tricks127856
Node: Sorting (1)128178
Node: A few lowest or highest values128993
Node: Span of values130294
Node: No aggregation130853
Node: Look at the first rows of a large table132755
Node: Installation133531
Node: Authors contributors134173
Node: Changes135889
Node: GPL 3 License140600

End Tag Table


Local Variables:
coding: utf-8
End:
