How to organize and arrange data tables

This article outlines ways to sort Terra data tables both manually - using the Terra UI - and programmatically - using the terminal. 

Sorting a column in ascending or descending order

A quick way to sort data is by ascending or descending order of individual columns.

1.1. Click the three vertical dots in the top right corner to open the column menu

1.2. Choose to sort columns by ascending or descending order. 

Note: You can also quickly sort or re-sort a column by clicking on the column title.

Reorganizing columns using drag and drop 

You can change the columns' order within the table through a drag-and-drop column reorder feature.

2.1. Click the settings button near the top left page of the data tab in a workspace. 

2.2. Click on the dotted lines to the left of a column name and drag it to display in a different order.  

2.3. Use the check-boxes to the left of the column names to display only the columns you're interested in.

2.4. You can optionally save this column order and selection: click save this column selection. This selection will be available in copies of this workspace and when sharing with other users.

If you have multiple saved column selections you can toggle through the different selections using the settings button. 

2.5. Click Done.

Reordering columns using a TSV file

By default, data columns are displayed in alphabetical order. To display data columns in the same order as the TSV you use to upload the data to a table, follow the directions below before you upload your data.

3.1. Click on workspace data on the bottom left side of the data page of a workspace. This will open a list of key-value pairs. 

3.2. Click on the blue plus icon on the bottom right hand side. A new row will appear in the workspace data table, with blank fields for a key, value, and description.

3.3. Add the name of your key into your Terra workspace in the new row's Key column.

Example key: workspace-column-defaults

3.4. Run the Python code below in a local terminal instance on your local machine to print out the value associated with the key.

Customizing this code for your file (required)

• The name of your key - replace workspace_column_defaults in the last 2 lines with this name.
• your .tsv file name - replace "things.tsv" with your file name (in quotes) in the 3rd line.

import csv
import json
files = ["things.tsv"]
tables = {}
for path in files:
    with open(path) as f:
        reader = csv.reader(f, delimiter="\t")
        header_row = next(reader)
        entity = header_row[0][len("entity:"):-len("_id")]
        tables[entity] = {
          "shown": [header_row[0][len("entity:"):]] + header_row[1:],
          "hidden": []
        }
workspace_column_defaults = json.dumps(tables)
print(workspace_column_defaults)

3.5. Copy the value printed by the code into the Value column of the new row in the Workspace Data table and click the blue check mark to save the key-value pair. 

3.6. Upload the data as a .TSV. It will appear in the order specified in the Workspace Data row, instead of in alphabetical order. 

Example

The following is an example where the key value has been set as 'workspace-column-order' and the dataset has been saved on the local machine as 'things.tsv'

In this example our value is:  {"sample": {"shown": ["sample_id", "height", "ancestry", "sex", "age", "fg", "bmi", "fi", "hdl", "ldl", "population", "tc", "tg", "whr"], "hidden": }}

Troubleshooting key-value pairs

You must define key-value pairs prior to uploading data. When they are added after the data has already been uploaded, you may need to clear your local storage. 

1. Click the three dot menu on the upper right hand corner of the Google Chrome browser to access the developer tools on Google Chrome.

2. Click more tools and developer tools.

The hot keys to get to developer tools Mac OS Command + Option + I
Windows/Linux F12 or Control + Shift + I

3. On the top of the developer tab there is a menu with two arrows at the top, use the arrows to get to the application tab. 

4. On the application tab, go to local storage on the left-hand dashboard

5. From the local storage, delete all storage below apc_user_id. 

Deleting the local storage will allow you to "reset" the order of data on Terra without needing to re-upload the dataset.