Table Operations

Introduction

Table Operations is a software tool that enables users to modify tabular data in many ways e.g. by removing rows and columns by order number or specific content, by replacing values, by different kinds of rearrangements etc. Table Operations is not a complete spreadsheet software like e.g. Microsoft Excel, but it complements spreadsheet programs with automatic or facilitated operations to accomplish task that would be too difficult or tedious to perform manually in the spreadsheet (without programming). Table Operations also feature tabular data processing with the power of regular expressions (character patterns).

Throughout the following text, the word "value" is used in a general sense meaning any text present in a table cell, no matter if it is a number or not. Any operation performed by the program can be reversed by clicking the Undo button located in the top part of the ACTIONS panel. After that, the operation can be repeated by clicking the Redo button located just next to the Undo button. The maximum history available is three steps back. To erase everything and start all over, click the Clear All button located in the top right corner of the ACTIONS panel.

Reading in data

To input data, either paste delimiter-separated values in the "Input Data" field or open a plain text file (e.g. csv or tsv) containing such values. You can directly copy-paste data from Microsoft Excel or other spreadsheet program (they are tab-separated when copied to clipboard). Select proper delimiter in the "Reading data" section and press the Read Data button. Check the table in the "Table Preview" field. The values should be properly distributed in table cells. If you see all values in the first cell, a mistaken delimiter has been probably set. Correct the delimiter choice and click "Read Data" again. Visual inspection of the input data should provide a clue about the delimiter.

Data output

The program outputs processed tables into the "Ouput Data" field as tab-separated values. Such text can be directly copy-pasted into a spreadsheet program like MS Excel and should get properly distributed into table cells. Alternatively, the output data can be saved into a text file as delimiter-separated values. Just select the delimiter and click the Save to File button. If you are unsure whether the delimiter character is present in the data, check the "Quoted values" option. The purpose of the "Table Preview" panel is visual control of input and output data, but no editing or data export functions are available there. Table Operations is not designed to support manual editing of values. However, although not recommended, it is possible to make some editing in the "Output Data" field and then update the program data with the Update from Output button located in the top part of the ACTIONS panel. It is critical not to corrupt the tab-separated order of values by the editing.

Removing rows by number

To remove rows by numbers, specify the number(s) and click the adjacent Apply button. You may enter a single number, numeric range (e.g. 5-12), comma-separated numbers, or a combination thereof (e.g. 1,3-5,7,12,15). Alternatively, you can remove rows by order patterns, e.g. every other row. To accomplish this, fill the row order number that is to be periodically applied in the row removal operation and click the adjacent Apply button. You can optionally limit the operation to a certain range of rows by filling the "Start with" and "Stop before" fields.

Removing columns by number

This function removes the columns in the table whose numbers match either those filled in the field labeled "Remove column number:" or a numeric pattern defined with the other three fields. In the first case, you can provide single number, numeric range (e.g. 5-12) or a comma-separated set of numbers, or a combination thereof (e.g. 1,3,5-12,15). In the second case, a numeric value from the range 1 to the last column number is expected in the field labeled "Remove every". In case number 1 is filled, the field "Start from" becomes mandatory and a number larger than 1 must be entered in it (otherwise the entire table would be erased, which is regarded an invalid operation). With such settings the function will remove all columns from the entered number on. In other cases i.e. when a number larger than 1 is filled in the field "Remove every", the function will remove columns matching the numeric pattern, e.g. every third column when number 3 has been entered. If all odd columns are to be removed, set "Remove every" to 2 and "Start from" to 1.

Removing rows by content

To remove the table rows by specific content, you have the options to remove rows containing particular text, to remove rows that do not contain particular text, or, in case of numeric values, you can remove all rows in which the number(s) fulfill an (in)equality that you specify as a parameter (see below for details). For the first two option, enter any text as the parameter and check the settings in the bottom part of the section. For the third option, enter an expression composed of comparison characters (=, >, <) and a number, for example "=1.25", ">50", ">=0", etc. Then choose if the removal criterion should be checked against all values in each row or against one column only (specify the number). Lastly, click the Apply button adjacent to the relevant input field.

Removing columns by content

To remove the table columns by specific content, you have the options to remove columns containing particular text, to remove columns that do not contain particular text, or, in case of numeric values, you can remove all columns in which the number(s) fulfill an (in)equality that you specify as a parameter (see below for details). For the first two option, enter any text as the parameter and check the settings in the bottom part of the section. For the third option, enter an expression composed of comparison characters (=, >, <) and a number, for example "=1.50", "<25", ">=0", etc. Then choose if the removal criterion should be checked against all values in each columns or against one row only (specify the number). Lastly, click the Apply button adjacent to the relevant input field.

Removing rows by duplicate values

Rows containing identical values can be removed with the Remove Duplicate Rows button. You can choose in which column to search for the identical values or alternatively choose to search for entirely identical rows. Change the other settings to fit your intentions, namely if the search should be case-sensitive and which of the duplicate rows should be preserved (first one, last one, or none).

Merging columns

This function puts the values from the specified columns into a single column and separates them with the specified separator. Enter a single number, numeric range (e.g. 9-12), comma-separated numbers, or a combination thereof (e.g. 7,4-6,2). The columns don't have to be adjacent to each other and will be merged in the order specified. The concatenation separator is an empty string by default, so the values will get directly concatenated to each other, if no separator has been entered. You can specify the position of the merged column with the number of the column after which the merged column should be placed. Use zero for the merged column to become the first column. By default, the header of the merged column is the same as that of the first column in the merging order.

Removing, replacing, prefixing and appending values

This section is primarily designed to replace particular character strings in cells, but is extended with miscellaneous options to accomplish more complex tasks as well. First of all, you can limit the search & replace operations to a specific row or column. If not specified, the whole table will be scanned for replacements. Next choose if you need to replace certain number of characters in the beginning of each value, replace certain number of characters in the end of each value, or replace a specific text. In the first two options enter a numeric value specifying how many characters should be replaced with the text provided in the "Replace with" field. No or zero value will make the program prefix and append, respectively the text provided in the "Replace with" field to each value. The third option, i.e. replacement of specific text values is accompanied by a set of additional options that allows you to narrow down or broaden the search & replace logic. The first option specifies if the search should be case-sensitive. The second option, if checked, will make the program search for whole words (any set of alphanumeric characters separated from other "words" with spaces, commas, etc. or positioned at the beginning/end of the cell) matching the target text value while the third option will require the matches to span whole cells. The fourth option allows you to define the target text as a regular expression (character pattern). The last option will make the program replace whole cell values containing any match instead of just replacing the matching text. Fill the "Replace with" field and click the Replace button to start the operation. If left empty, the program will remove all matches according to the settings chosen.

Sorting table

To sort the table, enter the number of the column of interest, choose between alphabetical sort and sorting by numerical values and click the Sort Table button. The table will get sorted in ascending order. If you need descending order instead, click the Reverse Row Order button. To change the order of rows randomly, click the Shuffle Rows button.

Rearranging table by moving columns under each other

This operation is designed to rearrange the table in a way that the values originally distributed in columns become all listed in one column while the original column labels are placed next to each corresponding value. The purpose of such rearrangement is to prepare the data for analysis in a statistical environment like R, where column-wise (measured values and factor levels) organized data is expected. Before starting the operation, choose the rearrangement pattern (how many adjacent columns to keep and where to start) and check the additional options (what information should be copied alongside the data column(s)). Click the Apply button to execute the operation.

Sorting values into columns by categories

This is the inverse operation with respect to the previous operation. Values from only two columns specified as "values" and "categories" will be used, values in other columns, if present, will be discarded. The operation will rearrange the table so that columns will be labeled with the category names and all values will be sorted into the columns according to the original assignment in rows.

Adding a column with repeated values

This operation allows you to create a new column filled with a repetition of a set of values provided in the field "Comma-separated values". The primary purpose of this function is to assign data in a column to categories (categorical variables or factors) for further statistical processing. For example, you have a set of 12 measured values and you need to create a column with repeating values "male, male, male, female, female, female, male, male, male, female, female, female" and another column with values "experiment1, experiment1, experiment1, experiment1, experiment1, experiment1, experiment2, experiment2, experiment2, experiment2, experiment2, experiment2". To accomplish this, you first need to enter the text "male, female" (without the quotes) into the field "Comma-separated values", set "Repeat each value" to 3 and "Repeat the whole set" to 2. Then choose where to place the new column, fill the "Column header" value, e.g. "sex", and click the Apply button. To create the second column, repeat the previous steps with the values "experiment1, experiment2", 6 and 1, respectively.

Additional operations

There are a few supplemental functions grouped in the section labeled "Additional operations". The function Transpose Table will rearrange the table so that rows become columns and columns become rows. The function "Fill all empty cells" will insert the provided text into all blank cells. The function "Count all cells containing" will search for the provided text and count the positive cells. The search can be further narrowed down or broadened with additional options which are the same as described above e.g. in the "Removing, replacing, prefixing and appending values" section. The function "Count all non-empty cells" will simply count all non-blank cells.