We’ll use this in a table that contains a reference column with repeated values (i.e., an ID) to aggregate values from another column or columns based on that reference. In all instances, we will use the --aggregate flag.

Basic example with a 2-column table, one with the ID and another one with values

In this case, values from the second column will be aggregated based on the values from the first column. To do that, the -i flag references the input table, the --agg_ref_col_index flag is the column we want to use as reference (base 1) and the --agg_col flag is the column we want to aggregate (base 1). Moreover, with the --agg_sep flag, we can choose the separator we want in our aggregated column.

Example with a 3-column table, one being the ID and the other two corresponding to values.

In this case, values from the second and third columns will be aggregated based on the first one.

Having 2 columns as reference.

If we have two columns as reference and we don't want to aggregate them but we also don't want to lose them, we can use the --agg_ref_col_index flag to select more than one column as reference (as well as selecting more than one aggregation column).

Example with a 3-column table, one being the ID and the other two corresponding to value, and another aggregation function.

Besides concatenating values in our column(s) of interest (default mode), we can also use different methods of aggregation using the --agg_mode flag, such as calculating the average ("mean"), median ("median"), maximum ("max") and minimum values ("min"), sum ("sum"), standard deviation ("std"), variance ("var"), interquartile range ("IQR"), 25th percentile ("P25"), 75th percentile ("P75") as well as counts ("count").

Let's see and example with the average of values from columns 2 and 3 based on column 1.

Example of aggregation of counts.

Useful to obtain a table with the ID in one column and the number of times said ID appears in the table when it was desaggregated in the other column.

We'll use it to filter entries from one or more tables based on one or a few keywords in one or more columns.

Basic example

This script enables filtering entries from one or more tables. If the wildcard * is used in the file path with the -i flag, multiple tables can be selected, and everything described in this chapter can be applied to those tables (although the examples below demonstrate each case using a single table). Filtering can be performed based on one or more keywords and applied to one or more columns. Despite its complex appearance, the script is highly versatile and comprehensive. Several examples are provided below, with increasing complexity, to help you better understand how to use it.

The basic arguments that are always required are: the input table (-i flag); the columns to be returned (--extract_cols); the column containing the keywords (--ext_col_match); the keyword(s) to search for (--ext_keywords); and the match mode (--ext_match_mode) , which specifies whether the keyword should be included in the column as a partial match (option "i") or as an exact match (option "c"). Finally, the --ext_search_mode option is used when matching across multiple columns. It allows you to specify whether the match must be satisfied in all columns (option "c") or only in some of them (option "s").

The most essential part is understanding how the keyword search string is built. The pattern is as follows: key1_col1&key2_col1%key1_col2&key2_col2 where each keyword is separated by a "&" (when you want a keyword or another in a specific column) and each column by a "%".

We'll start with a basic example in which we'll keep the entries that contain the keyword MONDO:0017999 (--ext_keywords) in column 1 (--ext_col_match) from the input table long_disease_cluster (-i flag). Moreover, we want the match to be complete (--ext_match_mode c) and we want to get columns 1 and 2 from the result (--extract_cols)

Complete match in ALL specified columns (two keys, one in each column)

As we observed, the previous example returns a few MONDO terms that meet the condition of a complete match in column 1. We'll extend the previous case to only get the MONDO term that has cluster "53_ref" in the second column (--ext_col_match)

Searching two (or more entries) in the same column (complete match)

We'll get the rows that contain either "MONDO:0008995" or "MONDO:0013969".

Partial match in the second column

We'll get the rows that contain the substring "ref" in the second column (changing the --ext_match_mode flag to i (include))

Reverse partial match in the second column

If what we want is not obtaining the entries that match the pattern, but those that do not, we can use the --ext_reverse flag. This situation is valid for both complete and patial matches. In this case, we will use the same example as before adding the reverse condition. This way, instead of obtaining entries that contain "ref", we will get those that don't have it ("rand").

Partial match in ALL specified columns (two columns)

In the previous case where we filtered by MONDO:0017999 in the first column, we found 3 clusters: 36_rand, 53_ref and 53_rand. In this case, to only get the different subsets, we can use the partial match. For example, to only get with MONDO:0017999 from cluster 53.

And to only get MONDO:0017999 from random clusters

Complete match in SOME of the specified columns (two columns)

So far, we've seen how to make matches that happen in all columns. If we want to search for keywords in more than one column but we want the pattern to only be found in SOME of the columns, we can change the --ext_search_mode flag to "s" (some).

For example, if we want to keet the entries that contain MONDO:0008995 or MONDO:0013969 in the first column or 22_rand or 66_ref in the second column, we can do as follows:

Changing a table from long to wide format.

When we work with workflows like autoflow's, we sometimes launch parallelized tasks with different job combinations, like: network (ORPHA and MONDO) and clustering type (CPM and HLC) and we write all the different metrics into a common file, such as:

ORPHA	CPM	N_CLUSTERS	500
ORPHA	CPM	CLUSTER_SIZE	10
ORPHA	HLC	N_CLUSTERS	600
ORPHA	HLC	CLUSTER_SIZE	12
MONDO	CPM	N_CLUSTERS	550
MONDO	CPM	CLUSTER_SIZE	11
MONDO	HLC	N_CLUSTERS	650
MONDO	HLC	CLUSTER_SIZE	13

In the table above, we can see that the first two columns are factors (network and clustering type), the third one is the measured metric and the last one is its value. This format would be called the "long" format (long and wide format examples). However, in order to be able to use some graphing methods from CanvasXpress or to see the metrics table in a simpler way, we need to transform this table onto a "wide" format. To do so, we'll use the create_metric_table method.

Basic example

Here, we'll specify the input table in the -i flag, the output table in the -o flag and the names of the columns that will be factors in the output table (separated by commas) in the --sample_attributes flag. You may also redirect corrupted fields to a file with the --corrupted flag.

Imagining we have an input file with columns that represent "network name, clustering type, network type, metric, value", the command would be as follows:

Defining another table to dump corrupt entries into:

We can optionally add a -c flag to obtain another table with corrupt values that could not be transformed into a "wide" format due to having a missing value in one(some) factor(s).

We’ll use this in a table that contains a reference column with repeated values (i.e., an ID) to disaggregate values from another column or columns based on that reference. In all instances, we will use the --desaggregate flag.

Basic example with a 3-column table where two of them are disaggregated and one is aggregated.

In this case, values from the third column will be disaggregated, adjusting the remaining columns according to the number of values in each row of the third column. We'll used the -i flag to specify the input table, the --desagg_col flag for the column we want to disaggregate (1 based) and the --desagg_sep flag to indicate the separator used in the aggregated column.

Basic example with a 4-column table, where two of them are disaggregated and the other aggregated.

If there is more than one aggregated column, we can specify them with the --desagg_col flag, separating them by commas. It is assumed that all aggregated columns have the same number of elements for each row (i.e. 1 column with pat1,pat2,pat3 and another with male,male,female)

Convert table from excel to plain format (CSV, TSV and similar).

Basic example

This example shows how to convert a table from excel to plain format (CSV, TSV and similar). The two-sheet excel file is as follows:

Let's extract the first three columns from the first sheet of the file special_files/scRNAseq.xlsx and convert it to plain tabular format. We will use the -i flag to specify input file, -c to select columns to extract and -s for the sheet to retrieve data from.

Extracting the first two and last two columns from the second sheet

In this case, we extract the first two and last two columns from the second sheet in the same file

Extract all columns from the first sheet

If we wish to extract all columns without specifying all of them we can just leave the --excel_cols argument empty, which signals for the extraction of every column.

Extracting select rows

In addition to columns, the user can also choose which rows to extract from the table. By default, every row is extracted, but we can extract a selection.

In this case we are extracting rows 1,3 and columns 1,3,4 from the first sheet

Se utiliza para filtrar las filas que nos interesan de una o más tablas en función a una serie de valores que se pasan

Ejemplo básico

Tenemos nuestra tabla con la primera columna teniendo el identificador de una red (la que nos interesa para poder filtrar), y las otras columnas siendo factores y metrica-valor. Tenemos otro archivo en el que tenemos los identificadores de las redes que queremos filtrar (red1 y red3).

Pasamos la tabla de interés tabla como input al flag -i y la columna en la que están los valores que filtrarán las filas con el flag -c. El archivo de una columna con los IDs de redes que queremos filtrar se pasan al flag -t. Pasamos el path a la carpeta (si, a una carpeta, ya que el flag de input -f puede aceptar más de una tabla, separada por comas, para ser filtrada) en el que queremos que se escriba la/s tabla/s filtrada/s con el flag -o y podemos añadir un prefijo para el nombre de la tabla de salida si nos interesa (en este caso usamos "filtered_" en este caso) con el flag --prefix

Usar un match complementario (usar los términos como blacklist en vez de whitelist)

Si en vez de querer quedarnos con todas las filas que tengan los términos de la tabla que pasamos con el flag -t, queremos quedarnos con todas las filas que no tengan esos términos, podemos activar el flag booleano --blacklist.

Repitiendo el mismo ejemplo anterior, pero activando ese flag, tenemos:

Match parcial (vale tanto para whitelist como para blacklist)

Si en vez de querer filtrar los elementos de la tabla por un match exacto, queremos que sea por un match parcial, podemos activar el flag booleano --not_exact_match

En la tabla siguiente, vamos a quedarnos con todos los clústeres de referencia y descartar los random, indicando que los valores que queremos filtrar en nuestra tabla de entrada dada con el flag -i están en la segunda columna (-c 2) y el archivo que pasamos para filtrar con el flag -t solo tiene el valor 'ref', que será usado para hacer un match parcial al haber usado el flag --not_exact_match

Filtrar múltiples tablas

Como se ha comentado antes, el flag -f acepta múltiples tablas separadas por comas. Es por ello que el flag -o para el output indica carpeta de salida y no archivo de salida. Además, si vamos a escribir los archivos en la misma carpeta, podemos utilizar el flag --prefix para que no se escriban con el mismo nombre y nos reemplacen las tablas de entrada.

Por lo tanto, podemos filtrar varias tablas a la vez utilizando el mismo archivo de filtrado. Esto es util si tenemos distintos archivos de métricas con los mismos identificadores y queremos filtrar todos ellos por los mismos términos.

Extract a selection of columns from the original table

Basic example using numeric indices to select columns

Input table is provided with flag -i. Columns to extract are selected with flag -c (comma-separated 1-based numeric indices), using a hyphen (-) so select a column range.

In this case, we will extract columns 1, 3, 4 and 5. We will do so by selecting column 1 and the 3-5 interval.

Using column names if header flag is present

If flag -H is active, it means the table has a header. If that is the case we can select columns by specifying their name, again selecting multiple by separating them by commas. (NOTE: even if the table has a header, columns may be selected by numerical index without activating the -H flag, exactly as in the previous example)

In this case, we will select columns col-1, col-3, col-4 and col-5, the same as in the previous example, but this time selecting them by column name. In this case it is also possible to select a range of columns, but in this case by using the "%-%" sequence of characters (as the hyphen may also be a part of the column name, as was chosen for this example)

A versatile script to merge two tables.

Basic example

The most basic case will be retrieving common entried from two columns of two different tables. In this case (and all the following ones) the mechanism is simple: we'll have the -a and -b flags to specify the two input tables, and the -A and -B flags to indicate the columns from each respective table. We'll use the -s flag to specify the separator (by default: tab). The default mode will be obtaining the IDs that are present in both tables (inner join), which is the default mode of the --keep flag. However, we'll also take a look at some different use cases.

Now we'll see an example with two tables, both of them containing a column with MONDO disease IDs, to find the common entries between them.

Retrieving the rest of the columns for the common entries found

If we don't only want to get the common entries from both tables in that column, but algo get the rest of the columns from both tables, we can use the --full flag

Retrieving specific entries from a table

We've just seen an example of inner join, which is the default mode. With the table_linker binary, we can also use the left join and right join cases.

However, we can keep specific entries from a table, whether it is A or B, using the --keep flag with intersect_columns. For example, if we want to keep specific entries from table A, we can use --keep a. If we also want to keep the rest of the columns from table A for the specific entries, we can also use the --full flag. In the next example, we'll show how to return the specific entries from table A without returning the rest of the columns (we could do it by adding the --full flag).

In the same way, if we want to keep specific entried from table B, we can use the --keep b flag. Finally, if we want the specific entries from tables A and B, we can use the --keep ab flag (in this case, it would be the complementary of --keep c, where we would get common entries to both). We'll show this case, returning the rest of the columns from both tables for those entries.

It is used to merge N tables based on their first column when they share the same IDs.

Basic example

In this script, we'll use the --tables flag to specify all input tables, separated by commas (,). The --columns2add flag will be used to indicate the column indexes (1 based) of each file added to the first one (therefore, we will not specify the first table's columns), separating the columns in each set by commas and each set of columns (pet table) by a semicolon (;). The --fill_character flag will be used to specify which character we want the empty fields to be filled with. Lastly, we'll use the --union flag to also add the rows that do not have a match to the merging table (first table).

In this case, we'll join tables tables/disease_cluster, tables/disease_gene and tables/mondo_to_orpha and we will fill the empty fields with "NA".

It is used to obtain the number of occurrences of all the terms in a column of a table.

Basic example

The input table will be specified with the -i flag and the column we want to count (1 based) will be indicated with the --count-cols flag.

Let's see the number of occurrences of the different terms in the second column of the table 'cluster_genes_dis_agg'.

We can use it to sort the rows of the input table by the given column/s

Generally speaking, the logic is the following:

--sort_rows_by [0-based column index OR column name],["d" if descending OR "a" if ascending OR custom_sort];[other_column],[mode];...

Basic example

We have a table with 3 columns, pat (patient), name and score. We want to sort the rows of the table by the score column, in ascending order

We'll use the -i flag to specify the input table, -H if the table have header and --sort_rows_by to give the sorting behaviour we expect (sort ascending by the score column)

Note that we can also use a numeric column index (0-based) to set the column we want to apply the sorting, even if the table has header (What we can't do is the opposite—try to sort by specifying a column name in a table that doesn't have a header; doing so would cause an error.)

Sorting by more than one column

Using the same table, we want to sort by score descending, but when the score is equal, we then want to sort by the name ascending (A before B and so)

Sorting by specific order in a given column (string case)

We can also sort the rows of a table in a completely arbitrary order based on a given column. In that case, we specify the column name or index and a string (or file; see the next section) that indicates the desired sort order. If using a string, the elements are separated by “|”

Sorting by specific order in a given column (file case)

We can also specify a filepath to sort the rows by the specific column. In that case it will be a one column file with the desired order.

We'll use this when we have a table that contains a column with IDs (i.e., ensemblIDs and associated GO terms) and another table that we want to use as a dictionary to translate certain values for different ones (i.e., a table with ensemblIDs and their corresponding gene symbol).

Basic example

We have a table with MONDO IDs and the cluster(s) where that MONDO disease has been found. We also have a file that correlates each MONDO ID with its corresponding ORPHA ID. We'll use the latter to translate all MONDO IDs to ORPHA IDs in the first table.

We'll use the -i flag to specify the input table (the first table in our case) and the -I flag for the dictionary (second table). We will then use the -c flag to select the column we want to translate, the --from flag to indicate which column from the dictionary table we want to use as the search key and the --to flag to select the column in the dictionary that contains the terms we want to use for replacing in the first table.

Removing untranslated entries

As we can observe in the previous example, if an ID term from the input file is not present in the dictionary, that ID remains untranslated. If we want to remove any untranslated entries, we can add the -u flag to the command.

This binary is used to get a subset from the input table, starting from row X and taking Y rows from there.

Basic example

La tabla de entrada se indica con el flag -i. La fila por la que se empieza con el flag -s o --start_line (indice basado en 1) y la cantidad de filas que se extraen desde ese punto con el flag -l o --lines_to_subset. Además, si la tabla original tiene un header y se quiere mantener en la tabla subseteada, se puede utilizar el flag -H o --header

The input table is specified with the -i flag. The row were we'll start subsetting our table and the number of rows we want to extract from that point will be specified with the --offset flag (1 based), separating both numbers by a comma. We can also add the -H or --header flag.

In this case, we'll get 4 rows from the metrics table, starting from row 3 and keeping the header.

Subsetting the whole table in different files with K lines per file.

If we want to subset the table in different files, each one containing K number of lines, we can use the --sp_chunk_size flag, indicating the number of lines we want per file, keeping in mind that we will also need to add the --split_out flag. The -o flag is used to state the ouput directory where the files will be stored.

In this case, we'll separate the disease clusters table in different files, each one of them containing 4 lines (we'll see that the last one only has 2 due to the input table having 10 entries).

Subsetting the whole table in N different files with the same number of entries in each one

Si se quiere partir la tabla en una cantidad fija de N archivos , cada uno de ellos con la misma cantidad de registros, se puede utilizar el flag -n o --number_of_files en vez de los flags -s y -l o --chunk_size. En este caso el flag de output -o también indica la carpeta donde se guardarán los archivos.

If we want to subset the table in a fixed number of N files, each one of them containing the same number of entries, we can use the --sp_file_number flag, indicating the number of files you want. Just like before, we will also have to add the --split_out flag. The -o output flag will also indicated where we will store the files.

In this case, we will separate the disease clusters table in 2 files.

Se utiliza para realizar un merge de dos tablas en base a una columna con identificadores compartidos.

Ejemplo básico

Se utiliza como tabla base la que se da con el flag -i, y la que se va a mergear con el flag -l, la co. Esto sería equivalente a un left join. Para obtener un right join, tan solo tendríamos que alternar los flags que damos a cada tabla (hacer un left join pero con la linker como input)

Por defecto, se espera que el identificador en la tabla input esté en la primera columna, y todo el resto de columnas de esta tabla se mantendrán. Con respecto a la tabla linker, podemos indicar la columna que contiene el identificador con el flag --id_linker (por defecto a 0), y las columnas que queremos mantener en el merge, separadas por comas, con el flag --columns2linker (por defecto a 1)

Tambien tenemos el flag -s para indicar que separador queremos para la tabla resultante (por defecto tabulación)

Ejemplo utilizando los flags --id_linker y --columns2linker

Utilizamos el flag --columns2linker para indicar que queremos las dos columnas de la tabla linker, no solo la primera. Usamos también el flag --id_linker para indicar qué columna contiene los identificadores para el merge en la tabla linker, aunque en este caso coincida con el valor por defecto

Manteniendo solo registros comunes

Para mantener solo los registros comunes a ambas tablas podemos usar el flag --drop. Esto sería equivalente a un inner join

This binary adds one or more tags (i.e. additional factors of a variable and their value) to all rows in a table.

Basic example using a string with comma separated tags

We'll specify the table we want the tags added to with the -i flag and a string with comma separated tags with the --tags flag

Example giving the name of a file to the -t (--tags) flag

If a file name is specified with the --tags flag instead of a comma separated string, cmdtabs can recognize it a load the file to use it as tags. In this case, the -x flag will be used to specify said file separator.

Specifying more that one file separated by commas to the --tags flag

If we indicate a few files separated by commas to the --tags flag, cmdtabs will read and use each one of them to add the tags.

It is used to convert a table in TSV, CSV, etc. format into a table with the required syntax to be added to LaTeX.

Basic example

We'll specify the table to convert with the -i flag and add the --latex flag for this binary to be used.

Adding the backbone

If we want to get a more detailed backbone to fill the remaining fields, we can used the --whole or -w flag.

Used to transpose a table.

Basic example

We'll transpose the specified table with the -i flag and add the --transposed flag for this binary to be used.