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


INFO-DIR-SECTION Emacs
START-INFO-DIR-ENTRY
* Orgtbl Join: (orgtbl-join).     Join columns from other Org Mode tables
END-INFO-DIR-ENTRY

INFO-DIR-SECTION Misc
START-INFO-DIR-ENTRY
* (orgtbl-join).        Join several Org Mode Tables.
END-INFO-DIR-ENTRY


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

Join several Org Mode Tables
****************************

One table (the master table) is grown by selectively appending columns
of other tables (the reference tables).

* Menu:

* New::
* Example::
* In-place, Push, Pull: In-place Push Pull.
* Matching rows::
* Wizard::
* Wide variety of input tables::
* About reference tables::
* Selecting the output columns::
* Post processing::
* Installation::
* Author, contributors: Author contributors.
* Changes::
* GPL 3 License::

— The Detailed Node Listing —

Example

* A cooking recipe::
* SQL equivalent::

In-place, Push, Pull

* _In-place_ mode::
* _Push_ mode::
* _Pull_ mode::
* As a rule of thumb::

Matching rows

* Duplicate rows::
* How to handle missing rows?::

Wizard

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

Wide variety of input tables

* Standard Org Mode input::
* Virtual input table from Babel::
* An Org ID::
* CSV input::
* JSON input::
* Input slicing::
* Malformed input tables::
* Headers::

About reference tables

* Multiple reference tables::
* Zero reference table::
* Joining 2 similar tables::

Post processing

* Computing spreadsheet formulas afterward::
* Algorithm post processing::
* Chaining::



File: orgtbl-join.info,  Node: New,  Next: Example,  Prev: Top,  Up: Top

1 New
*****

The wizard now offers the basic and expert modes.  See *note Guiding
(traditional) wizard::.


File: orgtbl-join.info,  Node: Example,  Next: In-place Push Pull,  Prev: New,  Up: Top

2 Example
*********

* Menu:

* A cooking recipe::
* SQL equivalent::


File: orgtbl-join.info,  Node: A cooking recipe,  Next: SQL equivalent,  Up: Example

2.1 A cooking recipe
====================

Here is a list of products for a cooking recipe.

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

   We want to complete it with nutritional facts: quantities of fiber,
sugar, proteins, and carbohydrates.  For this purpose, we have a long
reference table of standard products.  (This table has been freely
borrowed from Nut-Nutrition, <http://nut.sourceforge.net/>, by Jim
Jozwiak).

     #+tblname: nut
     | type     | Fiber | Sugar | Protein | 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 |
     | bread    |   0.7 |   0.7 |     3.3 | 16.0 |
     | orange   |   3.1 |  11.9 |     1.3 | 17.6 |
     | banana   |   2.1 |   9.9 |     0.9 | 18.5 |
     | tofu     |   0.7 |   0.5 |     6.6 |  1.4 |
     | nut      |   2.6 |   1.3 |     4.9 |  7.2 |
     | corn     |   4.7 |   1.8 |     2.8 | 21.3 |

   Let us put the cursor on the ‘type’ column of the recipe table, and
type ‘M-x orgtbl-join’.

   A few questions are asked.  Then the recipe gets new columns appended
with the needed nutrition facts:

     | type     | quty | Fiber | Sugar | Protein | Carb |
     |----------+------+-------+-------+---------+------|
     | onion    |   70 |   1.3 |   4.4 |     1.3 |  9.0 |
     | tomato   |  120 |   0.6 |   2.1 |     0.8 |  3.4 |
     | eggplant |  300 |   2.5 |   3.2 |     0.8 |  8.6 |
     | tofu     |  100 |   0.7 |   0.5 |     6.6 |  1.4 |


File: orgtbl-join.info,  Node: SQL equivalent,  Prev: A cooking recipe,  Up: Example

2.2 SQL equivalent
==================

If you are familiar with SQL, you would get a similar result with a
_join_ (actually a _left outer join_ by default, but that can be
configured with the ‘:full’ parameter).

     select *
     from recipe, nut
     where recipe.type = nut.type;

     select *
     from recipe, nut
     left outer join nut on recipe.type = nut.type;


File: orgtbl-join.info,  Node: In-place Push Pull,  Next: Matching rows,  Prev: Example,  Up: Top

3 In-place, Push, Pull
**********************

Three modes are available: _in-place_, _push_, _pull_.

* Menu:

* _In-place_ mode::
* _Push_ mode::
* _Pull_ mode::
* As a rule of thumb::


File: orgtbl-join.info,  Node: _In-place_ mode,  Next: _Push_ mode,  Up: In-place Push Pull

3.1 _In-place_ mode
===================

The master table is changed (in-place) by appending columns from
reference tables.

   Invoke it with the ‘M-x orgtbl-join’ command.  The cursor must be
positioned on the column used to perform the join.


File: orgtbl-join.info,  Node: _Push_ mode,  Next: _Pull_ mode,  Prev: _In-place_ mode,  Up: In-place Push Pull

3.2 _Push_ mode
===============

The master table drives the creation of derived tables.  Specify the
wanted result in ‘#+ORGTBL: SEND’ directives (as many as desired):

     #+ORGTBL: SEND enriched orgtbl-to-joined-table :ref-table nut :mas-column type :ref-column type
     | type     | quty |
     |----------+------|
     | onion    |   70 |
     | tomato   |  120 |
     | eggplant |  300 |
     | tofu     |  100 |

   The receiving blocks must be created somewhere else in the same file:

     #+BEGIN RECEIVE ORGTBL enriched
     #+END RECEIVE ORGTBL enriched

   Typing ‘C-c C-c’ with the cursor on the first pipe of the master
table refreshes all derived tables.


File: orgtbl-join.info,  Node: _Pull_ mode,  Next: As a rule of thumb,  Prev: _Push_ mode,  Up: In-place Push Pull

3.3 _Pull_ mode
===============

So-called "dynamic blocks" may also be used.  The resulting table knows
how to build itself.  Example:

   A master table is unaware that it will be enriched in a joined table:

     #+TBLNAME: recipe
     | type     | quty |
     |----------+------|
     | onion    |   70 |
     | tomato   |  120 |
     | eggplant |  300 |
     | tofu     |  100 |

   Create somewhere else a _dynamic block_ which carries the
specification of the join:

     #+BEGIN: join :mas-table recipe :mas-column type :ref-table nut :ref-column type
     | type     | quty | Fiber | Sugar | Protein | Carb |
     |----------+------+-------+-------+---------+------|
     | onion    |   70 |   1.3 |   4.4 |     1.3 |  9.0 |
     | tomato   |  120 |   0.6 |   2.1 |     0.8 |  3.4 |
     | eggplant |  300 |   2.5 |   3.2 |     0.8 |  8.6 |
     | tofu     |  100 |   0.7 |   0.5 |     6.6 |  1.4 |
     #+END:

   Typing ‘C-c C-c’ with the cursor on the ‘#+BEGIN:’ line refreshes the
table.


File: orgtbl-join.info,  Node: As a rule of thumb,  Prev: _Pull_ mode,  Up: In-place Push Pull

3.4 As a rule of thumb
======================

For quick and once-only processing, use _in-place_ mode.

   Use _pull_ or _push_ modes for reproducible work.  The _pull_ mode
might be easier to use than the _push_, because there is a *note
wizard:: bound to ‘C-c C-x x’ (see below).  Other than that, the two
modes use the same underlying engine, so using one or the other is just
a matter or convenience.


File: orgtbl-join.info,  Node: Matching rows,  Next: Wizard,  Prev: In-place Push Pull,  Up: Top

4 Matching rows
***************

The purpose of OrgtblJoin is to match rows of the master table with rows
of each of the reference tables.

   What happens if the match is not exact?  There might be duplicate
rows.  Rows may be missing.  Those cases are normal and perfectly
handled by OrgtblJoin.

   Missing rows handling may be customized with the ‘:full’ parameter.

* Menu:

* Duplicate rows::
* How to handle missing rows?::


File: orgtbl-join.info,  Node: Duplicate rows,  Next: How to handle missing rows?,  Up: Matching rows

4.1 Duplicate rows
==================

The reference tables may contain several matching rows for the same
value in the master table.  In this case, as many rows are created in
the joined table.  Therefore, the resulting table may be longer than the
master table.  Example, if a reference table contains three rows for
"eggplants":

     #+tblname: nut
     | type     | Cooking | Fiber | Sugar | Protein | Carb |
     |----------+---------+-------+-------+---------+------|
     | ...      | ...     |   ... |   ... |     ... |  ... |
     | eggplant | boiled  |   2.5 |   3.2 |     0.8 |  8.6 |
     | eggplant | pickled |   3.4 |   6.5 |     1.2 | 13.3 |
     | eggplant | raw     |   2.8 |   1.9 |     0.8 |  4.7 |
     | ...      | ...     |   ... |   ... |     ... |  ... |

   Then the resulting table will have those three rows appended:

     | type     | quty | type     | Cooking | Fiber | Sugar | Protein | Carb |
     |----------+------+----------+---------+-------+-------+---------+------|
     | ...      |  ... | ...      | ...     | ...   | ...   | ...     | ...  |
     | eggplant |  300 | eggplant | boiled  |   2.5 |   3.2 |     0.8 |  8.6 |
     | eggplant |  300 | eggplant | pickled |   3.4 |   6.5 |     1.2 | 13.3 |
     | eggplant |  300 | eggplant | raw     |   2.8 |   1.9 |     0.8 |  4.7 |

   If you are familiar with SQL, this behavior is reminiscent of the
_left outer join_.

   Duplicate entries may happen both in the master and the reference
tables.  The joined table will have all combinations.  So for instance
if there are 2 ‘eggplant’ rows in the master table, and 3 ‘eggplant’
rows in the reference table, then the joined table will get 6 ‘eggplant’
rows.


File: orgtbl-join.info,  Node: How to handle missing rows?,  Prev: Duplicate rows,  Up: Matching rows

4.2 How to handle missing rows?
===============================

It may happen that no row in the reference table matches a value in the
master table.  By default, in this case, the master row is kept, with
empty cells added to it.  Information from the master table is not lost.
If, for example, a line in the recipe refers to an unknown "amaranth"
product (a cereal known by the ancient Incas), then the resulting table
will still contain the ‘amaranth’ row, with empty nutritional facts.

     | type     | quty | type     | Fiber | Sugar | Protein | Carb |
     |----------+------+----------+-------+-------+---------+------|
     | onion    |   70 | onion    |   1.3 |   4.4 |     1.3 |  9.0 |
     | tomato   |  120 | tomato   |   0.6 |   2.1 |     0.8 |  3.4 |
     | eggplant |  300 | eggplant |   2.5 |   3.2 |     0.8 |  8.6 |
     | tofu     |  100 | tofu     |   0.7 |   0.5 |     6.6 |  1.4 |
     | amaranth |  120 |          |       |       |         |      |

   This behavior is controlled by the ‘:full’ parameter:
   • ‘:full mas’ the joined result contains the full master table (the
     default)
   • ‘:full ref’ the joined result contains the full reference tables
   • ‘:full mas+ref’ the joined result contains all rows from both
     master and all reference tables
   • ‘:full none’ or ‘:full nil’ the joined result contains only rows
     that appear in both tables

   The use cases may be as follow:

   • ‘:full mas’ is useful when the reference table is large, as a
     dictionary or a nutritional facts table.  We just pick the needed
     rows from the reference.

   • ‘:full mas+ref’ is useful when both tables are similar.  For
     instance, one table has been grown by a team, and the other
     independently by another team.  The joined table will contain
     additional rows from both teams.

   • ‘:full none’ is useful to create the intersection of tables.  For
     instance we have a list of items in the main warehouse, and another
     list of damaged items.  We are interested only in damaged items in
     the main warehouse.


File: orgtbl-join.info,  Node: Wizard,  Next: Wide variety of input tables,  Prev: Matching rows,  Up: Top

5 Wizard
********

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

* Menu:

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


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

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

The wizard may be invoked in-place or for the pull-mode.

   Invoke the wizard in-place by typing ‘M-x orgtbl-join’ with the
cursor inside the master table to be enriched.  The cursor should be
anywhere in the column serving the join process.  Use the ‘C-u’ prefix
to invoke the wizard in expert mode.

   The menu entry ‘Table > Column > Join with tables’ is equivalent to
‘M-x orgtbl-join’, if this menu entry has been added (see *note
Installation:: for how to do that).

   For the pull-mode, the same wizard may create a fresh new block
‘#+BEGIN: join...’, or amend an existing one.  Invoke it with
   • either ‘M-x orgtbl-join-insert-dblock-join’
   • or ‘C-c C-x x =join’.
   • prefix any of the above two commands with ‘C-u’ to use expert mode.
   Put the cursor on an empty space in your Org Mode file, or on an
existing ‘#+BEGIN: join...’ block.

   For all questions, completion is available.

   Note: there are many kinds of dynamic blocks that can be inserted
besides ‘join’.

   The basic mode (as opposed to expert mode) only queries for input
tables in the current buffer.  Of course, if the wizard updates an
existing block with exotic parameters, those parameters will be queried
even in basic mode.

   As there might be as many reference tables as wanted, the wizard
continues asking for reference tables (in expert mode).  When done,
answer ‘n’ when the wizard ask if you want an additional reference table
to be joined.

   The wizard does not (yet) take into account the ‘:cond’ and ‘:post’
parameters.  If there where such parameters already specified, the
wizard will leave them untouched.


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

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

Suppose we have a join block like this one:

     #+BEGIN: join :mas-table "my_table" :mas-column "the_column" :ref-table "other_table" :ref-column "another_column"
     #+END:

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

     #+BEGIN: join
     #+join: :mas-file
     #+join: :mas-name my_table
     #+join: :mas-orgid
     #+join: :mas-params
     #+join: :mas-slice
     #+join: :mas-column the_column
     #+join: :ref-file
     #+join: :ref-name other_table
     #+join: :ref-orgid
     #+join: :ref-params
     #+join: :ref-slice
     #+join: :ref-column another_column
     #+join: :full
     #+join: :cols
     #+join: :cond
     #+join: :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.

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

   All fields recognized by OrgtblJoin 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-join.info,  Node: Wide variety of input tables,  Next: About reference tables,  Prev: Wizard,  Up: Top

6 Wide variety of input tables
******************************

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

   Here we discus the ‘:mas-table’ and ‘:ref-table’ parameters.

* Menu:

* Standard Org Mode input::
* Virtual input table from Babel::
* An Org ID::
* CSV input::
* JSON input::
* Input slicing::
* Malformed input tables::
* Headers::


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

6.1 Standard Org Mode input
===========================

   • ‘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-join.info,  Node: Virtual input table from Babel,  Next: An Org ID,  Prev: Standard Org Mode input,  Up: Wide variety of input tables

6.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, 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
     (list
      '(type quty)
      'hline
      (list "tomato" (* 53.1 12))
      (list "tofu" (* 12.5 7)))
     #+end_src

   If executed, the script would output this table:

     #+RESULTS: ascript
     | type   |  quty |
     |--------+-------|
     | tomato | 637.2 |
     | tofu   |  87.5 |

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

     #+BEGIN: join :mas-table "ascript" :ref-table "nut" :mas-column "type" :ref-column "type" :full "mas"
     | type   |  quty | Fiber | Sugar | Protein | Carb |
     |--------+-------+-------+-------+---------+------|
     | tomato | 637.2 |   0.6 |   2.1 |     0.8 |  3.4 |
     | tofu   |  87.5 |   0.7 |   0.5 |     6.6 |  1.4 |
     #+END:

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


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

6.3 An Org ID
=============

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

   Those Org Mode identifiers span all known Org Mode files.  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-join.info,  Node: CSV input,  Next: JSON input,  Prev: An Org ID,  Up: Wide variety of input tables

6.4 CSV input
=============

CVS input is specific to OrgtblJoin.  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’.

   The separators 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 references as ‘$1, $2, $3, …’ as
usual.


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

6.5 JSON input
==============

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

   Currently, the only accepted format is an array of arrays.  There are
currently no parameters.  In the future it may be possible to specify
alternative sub-formats.


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

6.6 Input slicing
=================

The 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-join.info,  Node: Malformed input tables,  Next: Headers,  Prev: Input slicing,  Up: Wide variety of input tables

6.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-join.info,  Node: Headers,  Prev: Malformed input tables,  Up: Wide variety of input tables

6.8 Headers
===========

The master and the reference tables may or may not have a header.  When
there is a header, it may extend over several lines.  A header ends with
an horizontal line.

   OrgtblJoin tries to preserve as much of the master table as possible.
Therefore, if the master table has a header, the joined table will have
it verbatim, over as many lines as needed.

   The reference tables headers (if any), will fill-in the header (if
any) of the resulting table.  But if there is no room in the resulting
table header, the reference tables headers lines will be ignored, partly
of fully.

   A header is useful to refer to columns.  If there is no header, then
columns must be referred with _dollar_ names: ‘$1’ is the name of the
first column, ‘$2’ is the name of the second column, and so on.  This is
pretty much the same as in the Org Mode spreadsheet.


File: orgtbl-join.info,  Node: About reference tables,  Next: Selecting the output columns,  Prev: Wide variety of input tables,  Up: Top

7 About reference tables
************************

Why is there a distinction between the _master table_ and the _reference
tables_?

   • The result of running OrgtblJoin will be an enriched version of the
     *master table*.  The master table contains our data.
   • The *reference tables* are stores of data from which OrgtblJoin
     picks selected rows to enrich the result.  The reference tables
     contains alien data akin to data found in a library.

* Menu:

* Multiple reference tables::
* Zero reference table::
* Joining 2 similar tables::


File: orgtbl-join.info,  Node: Multiple reference tables,  Next: Zero reference table,  Up: About reference tables

7.1 Multiple reference tables
=============================

OrgtblJoin used to handle just one reference table.  Now, as many as
wanted are handled.

   To specify the reference tables, just use several times the
‘:ref-table’ and ‘:ref-column’ parameters.  They must match: for
instance, the third ‘:ref-table’ must match the third ‘:ref-column’.

   For now, the ‘:full’ and ‘:mas-column’ parameters should be mentioned
just once.  This could change in the future with as many such parameters
as reference tables.


File: orgtbl-join.info,  Node: Zero reference table,  Next: Joining 2 similar tables,  Prev: Multiple reference tables,  Up: About reference tables

7.2 Zero reference table
========================

One side effect of going multiple, is that _zero_ reference table is now
accepted.  In this case, the result of the join is just the master
table.  But the result can be changed in several ways:

   • Selection and re-ordering of columns through the ‘:cols’ parameter.
   • Additional computed columns through the ‘:formula’ parameter and
     survival of ‘#+TBLFM:’ lines.
   • Lisp and Babel post-processing through the ‘:post’ parameter.

   This is a use-case where joining is not needed.  Rather, a re-process
of the master table is wanted, and OrgtblJoin happens to be able to
perform those changes.

   For instance, the input table may be a CSV table, and the result an
Org Mode table.  Then OrgtblJoin is able to perform just this conversion
(and it is fast).


File: orgtbl-join.info,  Node: Joining 2 similar tables,  Prev: Zero reference table,  Up: About reference tables

7.3 Joining 2 similar tables
============================

What if we need not to append data from some table/s to a main table,
but to join 2 similar or symmetric tables with different data?

   Let’s assume we have these 2 tables:

     +#TBLNAME: TagsQ1
     | tag  | Q1 |
     |------+----|
     | tagA | 25 |
     | tagB | 18 |
     | tagC | 13 |
     | tagD |  6 |
     | tagE |  2 |
     | tagF |  2 |
     | tagG |  1 |

   and

     +#TBLNAME: TagsQ2
     | tag    | Q2 |
     |--------+----|
     | tagA   |  2 |
     | tagD   |  3 |
     | tagE   |  3 |
     | tagF   |  5 |
     | tagG   |  7 |
     | tagH   | 11 |
     | tagI   | 15 |

   Looking closely at both tables we can observe that some of these tags
appear in both (‘tags A, D, E, F, G’), some only on ‘Q1’ (‘D, C’) and
other only on ‘Q2’ (‘H, I, ...’).

   We want to create a table that includes all the tags, with a column
with their frequency for table ‘TagsQ1’ and another for ‘TagsQ2’.

   So we can create the orgtbl-join block with the *note Wizard::.  Type
‘C-c C-x x’, then answer ‘join’.

   As our tables are somehow symmetric (no one is a primary one), you
will choose arbitrarily ‘TagsQ1’ as the _"master table"_ and ‘TagsQ2’ as
the _"reference table"_.

   So continue answering to the wizard:
  1. Master table: ‘TagsQ1’
  2. Reference table: ‘TagsQ2’
  3. joining column in reference table: ‘tag’
  4. joining column in master table: ‘tag’

   Then there is a question about which table should appear entirely.
In the result you want, there might be missing values in both ‘Q1’ and
‘Q2’ columns.  Therefore the right answer is: ‘mas+ref’

   Eventually you get:
     #+BEGIN: join :mas-table "TagsQ1" :ref-table "TagsQ2" :mas-column "tag" :ref-column "tag" :full "mas+ref"
     | tag    | Q1 | Q2 |
     |--------+----+----|
     | tagA   | 25 |  2 |
     | tagB   | 18 |
     | tagC   | 13 |
     | tagD   |  6 |  3 |
     | tagE   |  2 |  3 |
     | tagF   |  2 |  5 |
     | tagG   |  1 |  7 |
     |--------+----+----|
     | tagH   |    | 11 |
     | tagI   |    | 15 |
     | tag... |    | 19 |
     #+END:

   The ‘tagB’ and ‘tagC’ rows are incomplete on purpose.  To fill in the
table, just type TAB inside it.


File: orgtbl-join.info,  Node: Selecting the output columns,  Next: Post processing,  Prev: About reference tables,  Up: Top

8 Selecting the output columns
******************************

By default, all columns from the master table and all the reference
tables are output (except the joining column, which is output only
once).

   This can be customized with the ‘:cols’ parameter.  Give it the list
of desired columns, in the order they should be output.

   Columns may be specified by their name (if they have one) or by a
dollar form.  Thus, ‘$3’ means the third column (numbering begins with
1).

   By default, the first example give all columns (except ‘type’ which
appears only once):

     #+BEGIN: join :mas-table recipe :mas-column type :ref-table nut :ref-column type
     | type     | quty | Fiber | Sugar | Protein | Carb |
     |----------+------+-------+-------+---------+------|
     | onion    |   70 |   1.3 |   4.4 |     1.3 |  9.0 |
     | tomato   |  120 |   0.6 |   2.1 |     0.8 |  3.4 |
     | eggplant |  300 |   2.5 |   3.2 |     0.8 |  8.6 |
     | tofu     |  100 |   0.7 |   0.5 |     6.6 |  1.4 |
     #+END:

   If we want only ‘quty’ and ‘Protein’, we specify it like that:

     #+BEGIN: join :cols (quty Protein) :mas-table recipe :mas-column type :ref-table nut :ref-column type
     | quty | Protein |
     |------+---------|
     |   70 |     1.3 |
     |  120 |     0.8 |
     |  300 |     0.8 |
     |  100 |     6.6 |
     #+END:

   Or like that:

     #+BEGIN: join :cols "quty Protein" :mas-table recipe :mas-column type :ref-table nut :ref-column type
     | quty | Protein |
     |------+---------|
     |   70 |     1.3 |
     |  120 |     0.8 |
     |  300 |     0.8 |
     |  100 |     6.6 |
     #+END:


File: orgtbl-join.info,  Node: Post processing,  Next: Installation,  Prev: Selecting the output columns,  Up: Top

9 Post processing
*****************

* Menu:

* Computing spreadsheet formulas afterward::
* Algorithm post processing::
* Chaining::


File: orgtbl-join.info,  Node: Computing spreadsheet formulas afterward,  Next: Algorithm post processing,  Up: Post processing

9.1 Computing spreadsheet formulas afterward
============================================

Additional columns can be specified for the resulting table.  With the
previous example, we added a 7th column multiplying columns 2 and 3.
This results in a line beginning with ‘#+TBLFM:’ below the table, as
usual in Org spreadsheet.  This line will survive re-computations.

   Moreover, we added a spreadsheet formula with a ‘:formula’ parameter.
This will fill-in the 7th column header.  It is translated into a usual
‘#+TBLFM:’ spreadsheet line.

     #+BEGIN: join :mas-table recipe :mas-column type :ref-table nut :ref-column type :formula "@1$7=totfiber"
     #+name: richer
     | type     | quty | Fiber | Sugar | Protein | Carb | totfiber |
     |----------+------+-------+-------+---------+------+----------|
     | onion    |   70 |   1.3 |   4.4 |     1.3 |  9.0 |      91. |
     | tomato   |  120 |   0.6 |   2.1 |     0.8 |  3.4 |      72. |
     | eggplant |  300 |   2.5 |   3.2 |     0.8 |  8.6 |     750. |
     | tofu     |  100 |   0.7 |   0.5 |     6.6 |  1.4 |      70. |
     #+TBLFM: $7=$2*$3::@1$7=totfiber
     #+END:


File: orgtbl-join.info,  Node: Algorithm post processing,  Next: Chaining,  Prev: Computing spreadsheet formulas afterward,  Up: Post processing

9.2 Algorithm post processing
=============================

The joined table can be post-processed with the ‘:post’ parameter.  It
accepts a Lisp ‘lambda’, a Lisp function, a Lisp expression, or a Babel
block.

   The processing receives the joined 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
_ginger_.

     #+BEGIN: join ... :post (lambda (table) (append table '(hline (ginger na na na na))))
     | product   |   quty | Carb | Fiber | Sugar | Protein |
     |-----------+--------+------+-------+-------+---------|
     | onion     |     70 |  9.0 |   1.3 |   4.4 |     1.3 |
     | unknown   |    999 |
     | tomato    |    120 |  3.4 |   0.6 |   2.1 |     0.8 |
     |-----------+--------+------+-------+-------+---------|
     | ginger    |     33 |   na |    na |    na |      na |
     #+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 (ginger na na na na))))
     #+end_src

     ... :post my-function

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

     #+BEGIN: join ... :post "my-babel-block(tbl=*this*)"
     ...
     #+END:

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

   The block is passed the table to process in a Lisp variable called
‘*this*’.


File: orgtbl-join.info,  Node: Chaining,  Prev: Algorithm post processing,  Up: Post processing

9.3 Chaining
============

In an above example we gave a name to the resulting joined table:
‘#+name: richer’.  Doing so the joined table may become an input for a
further computation, for example in a Babel block.

   The name will survive re-computations.  This happens only in _pull
mode_.

   Note that the ‘#+name: richer’ line could appear above the ‘#+BEGIN:’
line.  But sometimes this is not taken into account by further Babel
blocks.  In this case, put ‘#+name: richer’ bellow ‘#+BEGIN:’.


File: orgtbl-join.info,  Node: Installation,  Next: Author contributors,  Prev: Post processing,  Up: Top

10 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-join’
     M-x package-list-packages

   Alternatively, you can download the Lisp files, and load them:

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

   You may want to add an entry in the ‘Table’ menu, ‘Column’ sub-menu.
You may also want to call ‘orgtbl-join’ with ‘C-c j’.  One way to do so
is to use ‘use-package’ in your ‘.emacs’ init file:

     (use-package orgtbl-join
       :after (org)
       :bind ("C-c j" . orgtbl-join)
       :init
       (easy-menu-add-item
        org-tbl-menu '("Column")
        ["Join with another table" orgtbl-join (org-at-table-p)]))

   Note: there used to be a ‘orgtbl-join-setup-keybindings’ function to
do just what the above ‘use-package’ does.  In this new way, key and
menu bindings are no longer hard-coded in the package.


File: orgtbl-join.info,  Node: Author contributors,  Next: Changes,  Prev: Installation,  Up: Top

11 Author, contributors
***********************

Comments, enhancements, etc.  welcome.

   Author
   • Thierry Banel, tbanelwebmin at free dot fr
   • bymoz089 (GitHub) found and tracked-down a bug in the in-place
     joining
   • Eduardo Mercovich (GitHub edumerco) wrote the documentation for the
     2 similar tables use case.

   Contributors
   • Dirk Schmitt, surviving ‘#.NAME:’ line
   • wuqui, ‘:cols’ parameter
   • Misohena (<https://misohena.jp/blog/author/misohena>), double width
     Japanese characters (string-width vs.  length)
   • Shankar Rao, ‘:post’ post-processing
   • Piotr Panasiuk, ‘#+CAPTION:’ and any tags survive
   • Luis Miguel Hernanz, multiple reference tables suggestion, fix
     regex bug


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

12 Changes
**********

   • remove duplicate reference column
   • fix keybindings
   • ‘#.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
   • 2 as column name no longer supported, write $2
   • add ‘:full’ parameter
   • remove ‘C-c C-x i’, use standard ‘C-c C-x x’ instead
   • added the ‘:cols’ parameter
   • ‘:post’ post-processing
   • 3x speedup ‘org-table-to-lisp’ and avoid Emacs 27 to 30
     incompatibilities
   • ‘#+CAPTION:’ and any other tag survive inside ‘#+BEGIN:’
   • now there can be several reference tables in a join, instead of
     just one.
   • Documentation is now integrated right into Emacs in the ‘info’
     format.  Type ‘M-: (info "orgtbl-join")’
   • TOC in README.org (thanks org-make-toc)
   • Virtual input table produced by Babel blocks
   • Speedup of resulting table recalculation when there are formulas in
     ‘#+tblfm:’ or in ‘:formula’.  The overall join may be up to x4
     faster and ÷4 less memory hungry.
   • Add the chapter "Joining 2 similar tables" for a common use case.
   • Aggregation of the titles of this README.
   • New free-form wizard.
   • Wizard can now be called in basic or expert mode.


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

13 GPL 3 License
****************

Copyright (C) 2014-2026 Thierry Banel

   orgtbl-join 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-join 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: Top200
Node: New1477
Node: Example1660
Node: A cooking recipe1822
Node: SQL equivalent3684
Node: In-place Push Pull4150
Node: _In-place_ mode4439
Node: _Push_ mode4784
Node: _Pull_ mode5581
Node: As a rule of thumb6709
Node: Matching rows7218
Node: Duplicate rows7754
Node: How to handle missing rows?9571
Node: Wizard11809
Node: Guiding (traditional) wizard12075
Node: Experimental free form wizard13931
Node: Wide variety of input tables15581
Node: Standard Org Mode input16138
Node: Virtual input table from Babel16572
Node: An Org ID18609
Node: CSV input19116
Node: JSON input20069
Node: Input slicing20526
Node: Malformed input tables21257
Node: Headers22153
Node: About reference tables23143
Node: Multiple reference tables23844
Node: Zero reference table24503
Node: Joining 2 similar tables25497
Node: Selecting the output columns27906
Node: Post processing29686
Node: Computing spreadsheet formulas afterward29939
Node: Algorithm post processing31221
Node: Chaining33113
Node: Installation33732
Node: Author contributors35024
Node: Changes35889
Node: GPL 3 License37425

End Tag Table


Local Variables:
coding: utf-8
End:
