<commit_before>.. include:: references.txt
.. |join| replace:: :func:`~astropy.table.operations.join`

.. _table_operations:

Table operations
-----------------

In this section we describe higher-level operations that can be used to generate a new
table from one or more input tables.  This includes:

=======================

.. list-table::
   :header-rows: 1
   :widths: 28 52 20

   * - Documentation
     - Description
     - Function
   * - `Grouped operations`_
     - Group tables and columns by keys
     - `~astropy.table.table.Table.group_by`
   * - `Stack vertically`_
     - Concatenate input tables along rows
     - `~astropy.table.operations.vstack`
   * - `Stack horizontally`_
     - Concatenate input tables along columns
     - `~astropy.table.operations.hstack`
   * - `Join`_
     - Database-style join of two tables
     - `~astropy.table.operations.join`


.. _grouped-operations:

Grouped operations
^^^^^^^^^^^^^^^^^^

Sometimes in a table or table column there are natural groups within the dataset for which
it makes sense to compute some derived values.  A simple example is a list of objects with
photometry from various observing runs::

  >>> from astropy.table import Table
  >>> obs = Table.read("""name    obs_date    mag_b  mag_v
  ...                     M31     2012-01-02  17.0   17.5
  ...                     M31     2012-01-02  17.1   17.4
  ...                     M101    2012-01-02  15.1   13.5
  ...                     M82     2012-02-14  16.2   14.5
  ...                     M31     2012-02-14  16.9   17.3
  ...                     M82     2012-02-14  15.2   15.5
  ...                     M101    2012-02-14  15.0   13.6
  ...                     M82     2012-03-26  15.7   16.5
  ...                     M101    2012-03-26  15.1   13.5
  ...                     M101    2012-03-26  14.8   14.3
  ...                     """, format='ascii')

Table groups
~~~~~~~~~~~~~~

Now suppose we want the mean magnitudes for each object.  We first group the data by the
``name`` column with the :func:`~astropy.table.table.Table.group_by` method.  This returns
a new table sorted by ``name`` which has a ``groups`` property specifying the unique
values of ``name`` and the corresponding table rows::

  >>> obs_by_name = obs.group_by('name')
  >>> print obs_by_name  # doctest: +SKIP
  name  obs_date  mag_b mag_v
  ---- ---------- ----- -----
  M101 2012-01-02  15.1  13.5  << First group (index=0, key='M101')
  M101 2012-02-14  15.0  13.6
  M101 2012-03-26  15.1  13.5
  M101 2012-03-26  14.8  14.3
   M31 2012-01-02  17.0  17.5  << Second group (index=4, key='M31')
   M31 2012-01-02  17.1  17.4
   M31 2012-02-14  16.9  17.3
   M82 2012-02-14  16.2  14.5  << Third group (index=7, key='M83')
   M82 2012-02-14  15.2  15.5
   M82 2012-03-26  15.7  16.5
                               << End of groups (index=10)
  >>> print obs_by_name.groups.keys
  name
  ----
  M101
   M31
   M82
  >>> print obs_by_name.groups.indices
  [ 0  4  7 10]

The ``groups`` property is the portal to all grouped operations with tables and columns.
It defines how the table is grouped via an array of the unique row key values and the
indices of the group boundaries for those key values.  The groups here correspond to the
row slices ``0:4``, ``4:7``, and ``7:10`` in the ``obs_by_name`` table.

The initial argument (``keys``) for the `~astropy.table.table.Table.group_by` function
can take a number of input data types:

- Single string value with a table column name (as shown above)
- List of string values with table column names
- Another `Table` or `Table` column with same length as table
- Numpy structured array with same length as table
- Numpy homogeneous array with same length as table

In all cases the corresponding row elements are considered as a tuple of values which
form a key value that is used to sort the original table and generate
the required groups.

As an example, to get the average magnitudes for each object on each observing
night, we would first group the table on both ``name`` and ``obs_date`` as follows::

  >>> print obs.group_by(['name', 'obs_date']).groups.keys
  name  obs_date
  ---- ----------
  M101 2012-01-02
  M101 2012-02-14
  M101 2012-03-26
   M31 2012-01-02
   M31 2012-02-14
   M82 2012-02-14
   M82 2012-03-26


Manipulating groups
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Once you have applied grouping to a table then you can easily access the individual
groups or subsets of groups.  In all cases this returns a new grouped table.
For instance to get the sub-table which corresponds to the second group (index=1)
do::

  >>> print obs_by_name.groups[1]
  name  obs_date  mag_b mag_v
  ---- ---------- ----- -----
   M31 2012-01-02  17.0  17.5
   M31 2012-01-02  17.1  17.4
   M31 2012-02-14  16.9  17.3

To get the first and second groups together use a slice::

  >>> groups01 = obs_by_name.groups[0:2]
  >>> print groups01
  name  obs_date  mag_b mag_v
  ---- ---------- ----- -----
  M101 2012-01-02  15.1  13.5
  M101 2012-02-14  15.0  13.6
  M101 2012-03-26  15.1  13.5
  M101 2012-03-26  14.8  14.3
   M31 2012-01-02  17.0  17.5
   M31 2012-01-02  17.1  17.4
   M31 2012-02-14  16.9  17.3
  >>> print groups01.groups.keys
  name
  ----
  M101
   M31

You can also supply a numpy array of indices or a boolean mask to select particular
groups, e.g.::

  >>> mask = obs_by_name.groups.keys == 'M101'
  >>> print obs_by_name.groups[mask]
  name  obs_date  mag_b mag_v
  ---- ---------- ----- -----
  M101 2012-01-02  15.1  13.5
  M101 2012-02-14  15.0  13.6
  M101 2012-03-26  15.1  13.5<commit_msg>Fix creation of column mask in example.<commit_after>.. include:: references.txt
.. |join| replace:: :func:`~astropy.table.operations.join`

.. _table_operations:

Table operations
-----------------

In this section we describe higher-level operations that can be used to generate a new
table from one or more input tables.  This includes:

=======================

.. list-table::
   :header-rows: 1
   :widths: 28 52 20

   * - Documentation
     - Description
     - Function
   * - `Grouped operations`_
     - Group tables and columns by keys
     - `~astropy.table.table.Table.group_by`
   * - `Stack vertically`_
     - Concatenate input tables along rows
     - `~astropy.table.operations.vstack`
   * - `Stack horizontally`_
     - Concatenate input tables along columns
     - `~astropy.table.operations.hstack`
   * - `Join`_
     - Database-style join of two tables
     - `~astropy.table.operations.join`


.. _grouped-operations:

Grouped operations
^^^^^^^^^^^^^^^^^^

Sometimes in a table or table column there are natural groups within the dataset for which
it makes sense to compute some derived values.  A simple example is a list of objects with
photometry from various observing runs::

  >>> from astropy.table import Table
  >>> obs = Table.read("""name    obs_date    mag_b  mag_v
  ...                     M31     2012-01-02  17.0   17.5
  ...                     M31     2012-01-02  17.1   17.4
  ...                     M101    2012-01-02  15.1   13.5
  ...                     M82     2012-02-14  16.2   14.5
  ...                     M31     2012-02-14  16.9   17.3
  ...                     M82     2012-02-14  15.2   15.5
  ...                     M101    2012-02-14  15.0   13.6
  ...                     M82     2012-03-26  15.7   16.5
  ...                     M101    2012-03-26  15.1   13.5
  ...                     M101    2012-03-26  14.8   14.3
  ...                     """, format='ascii')

Table groups
~~~~~~~~~~~~~~

Now suppose we want the mean magnitudes for each object.  We first group the data by the
``name`` column with the :func:`~astropy.table.table.Table.group_by` method.  This returns
a new table sorted by ``name`` which has a ``groups`` property specifying the unique
values of ``name`` and the corresponding table rows::

  >>> obs_by_name = obs.group_by('name')
  >>> print obs_by_name  # doctest: +SKIP
  name  obs_date  mag_b mag_v
  ---- ---------- ----- -----
  M101 2012-01-02  15.1  13.5  << First group (index=0, key='M101')
  M101 2012-02-14  15.0  13.6
  M101 2012-03-26  15.1  13.5
  M101 2012-03-26  14.8  14.3
   M31 2012-01-02  17.0  17.5  << Second group (index=4, key='M31')
   M31 2012-01-02  17.1  17.4
   M31 2012-02-14  16.9  17.3
   M82 2012-02-14  16.2  14.5  << Third group (index=7, key='M83')
   M82 2012-02-14  15.2  15.5
   M82 2012-03-26  15.7  16.5
                               << End of groups (index=10)
  >>> print obs_by_name.groups.keys
  name
  ----
  M101
   M31
   M82
  >>> print obs_by_name.groups.indices
  [ 0  4  7 10]

The ``groups`` property is the portal to all grouped operations with tables and columns.
It defines how the table is grouped via an array of the unique row key values and the
indices of the group boundaries for those key values.  The groups here correspond to the
row slices ``0:4``, ``4:7``, and ``7:10`` in the ``obs_by_name`` table.

The initial argument (``keys``) for the `~astropy.table.table.Table.group_by` function
can take a number of input data types:

- Single string value with a table column name (as shown above)
- List of string values with table column names
- Another `Table` or `Table` column with same length as table
- Numpy structured array with same length as table
- Numpy homogeneous array with same length as table

In all cases the corresponding row elements are considered as a tuple of values which
form a key value that is used to sort the original table and generate
the required groups.

As an example, to get the average magnitudes for each object on each observing
night, we would first group the table on both ``name`` and ``obs_date`` as follows::

  >>> print obs.group_by(['name', 'obs_date']).groups.keys
  name  obs_date
  ---- ----------
  M101 2012-01-02
  M101 2012-02-14
  M101 2012-03-26
   M31 2012-01-02
   M31 2012-02-14
   M82 2012-02-14
   M82 2012-03-26


Manipulating groups
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Once you have applied grouping to a table then you can easily access the individual
groups or subsets of groups.  In all cases this returns a new grouped table.
For instance to get the sub-table which corresponds to the second group (index=1)
do::

  >>> print obs_by_name.groups[1]
  name  obs_date  mag_b mag_v
  ---- ---------- ----- -----
   M31 2012-01-02  17.0  17.5
   M31 2012-01-02  17.1  17.4
   M31 2012-02-14  16.9  17.3

To get the first and second groups together use a slice::

  >>> groups01 = obs_by_name.groups[0:2]
  >>> print groups01
  name  obs_date  mag_b mag_v
  ---- ---------- ----- -----
  M101 2012-01-02  15.1  13.5
  M101 2012-02-14  15.0  13.6
  M101 2012-03-26  15.1  13.5
  M101 2012-03-26  14.8  14.3
   M31 2012-01-02  17.0  17.5
   M31 2012-01-02  17.1  17.4
   M31 2012-02-14  16.9  17.3
  >>> print groups01.groups.keys
  name
  ----
  M101
   M31

You can also supply a numpy array of indices or a boolean mask to select particular
groups, e.g.::

  >>> mask = obs_by_name.groups.keys['name'] == 'M101'
  >>> print obs_by_name.groups[mask]
  name  obs_date  mag_b mag_v
  ---- ---------- ----- -----
  M101 2012-01-02  15.1  13.5
  M101 2012-02-14  15.0  13.6
  M101 2012-03-26  15.1  13.5
{'fraction_non_alphanumeric': 0.13260014306151646, 'fraction_numerical': 0.12017167381974249, 'mean_word_length': 2.1104004449388207, 'pattern_counts': {'":': 0, '<': 19, '<?xml version=': 0, '>': 81, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 14, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7216111', 'n_tokens_mistral': 4530, 'n_tokens_neox': 3681, 'n_words': 1293}