How to organize and arrange data tables

Yashasvika Duggal
  • Updated

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. 

Screenshot showing an example Terra data table being sorted by a specific column. A user has clicked the three vertical dots to the right of the column's name and opened a menu with options to sort the table by the column's values in ascending or descending order; and to rename, delete, or clear the column. This menu is highlighted with an orange rectangle.

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.  

Screen capture showing how to change the order of the columns in a Terra data table. The user clicks on the 'Settings' button at the top of the table, chooses which columns to view, and re-arranges the columns by dragging and dropping the column names into the preferred 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.

Screenshot showing the Workspace Data section of the Data tab in an example Terra workspace. The workspace data is organized into a table with 3 columns: Key, Value, and Description. An orange rectangle highlights the Key and Value columns. An orange arrow highlights a blue circle with a white plus sign at the bottom right of the page.

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. 

Screenshot showing an example key-value pair used to display a data table's columns in the same order as in the data table's .tsv file. The last row in the Workspace Data table is being filled in with a user-defined name (the 'Key' column) and a string printed out by the Python code referenced in this article (the 'Value' column).

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'

Screenshot showing the Python code used to print out a string specifying the display order for an example Terra data table. The code has been customized for this example: the 'files' variable is set to the file path to the .tsv data file. An orange arrow highlights the code's output, which is a string that can be copied into the 'Value' column of the Workspace Data table.

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. 

Screenshot showing the menu used to clear the browser's data using the Chrome browser's developer tools. An orange arrow highlights the double-arrow button next to the 'Network' tab of the Developer Tools menu, and an orange rectangle highlights the 'Application' option in the drop-down menu.

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. 

Screenshot showing the Application menu in the Chrome developer tools. An orange arrow highlights the Local Storage section and an orange rectangle highlights the section of data that includes '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. 

Was this article helpful?

0 out of 1 found this helpful

Comments

0 comments

Please sign in to leave a comment.