Modifying and editing a data table

Liz Kiernan
  • Updated

How to modify table entries or delete table rows or columns 

This video shows how to change a handful of cells for data already in a table

You can do all of these in the Data page of your Terra workspace. For example, if you wanted to change the sample or participant IDs, delete particular samples , or delete the r1_fastq column altogether (we're not recommending this one, just using it as an example) in the table below.Modifying-data-tables_Screen_shot.png

Select from the options below for step-by-step instructions of how to do each action in Terra.

  • If your workspace already includes a data table with at least the number of rows you need, you can edit individual cells only by clicking on the pencil icon in the cell you want to change and working directly in the popup that appears.S11a_Edit_sample-table_Screen_Shot.png

    Note that you can specify the kind of attribute (string, reference, number or Boolean) by clicking on the right option in the Attribute type (left screenshot below). You can also generate an array by selecting Attribute is a list and adding items to the array (right screenshot below).

    Modify-attribute_Screen_shot.png Modify-attribute_Attribute-is-a-list_Screen_shot.png
  • 1. Check the box to the left of the row(s) you want to delete. To delete the entire table, select all rows from the dropdown.
    Data-tables-deleting-rows-or-tables_Screen_shot.png

    2. Click the three vertical dots at the top and select Delete Data.

  • 1. Check the three vertical dots at the top right of the column you want to delete. 

    2. Select Delete/hide Columns from the dropdown.
    Delete-table-columns_Screen_shot.png

    You can also change and save the visible columns (without deleting) If you just want to hide the columns of metadata, you can do that by clicking on the gear icon at the top right of the table. This will reveal a popup where you can choose what columns of metadata to show. You can even choose to save this view, especially useful for collaborators working in the same workspace.
    Adjust-and-save-table-columns-view_Screen_shot.png

  • If you want to update the values in a column for several (or all) rows in a table - if the study name or dates have changed, for example - follow the instructions below.

    1. First select the row(s) to edit. 

    2. Click the three dot icon at the top of the data table.

    3. Select Edit Attribute.
    Modify-table_Edit-attribute_Screen_shot.png

    4. Select the attribute (column to edit)
    Modify-table_Add-metadata-column_Screen_shot.png
    5. Enter the new value and click Save changes 
    Modify-table-Edit-metadata_Screen_shot.png
  • 1. Starting in the workspace you want to copy from, select the rows you want to copy (or all rows, if you want the entire table).
    Data-tables-deleting-rows-or-tables_Screen_shot.png

    2. Click the three vertical dots at the top and select Export to Workspace to select the workspace to copy to.

    There is no cost to copying data in a table to a workspaceExporting a data table to another workspace or downloading the TSV metadata does not incur any egress costs because you are not moving/copying the files that are referenced by the table, merely copying the metadata and references to the new workspace. Note that readers of your workspace will need read access to the bucket or workspace which actually owns the data to access files linked in the table.

Add data (rows or columns) to an existing table

If you have an existing table and need to add rows of data (for example, add additional specimens to a specimen table), you will work in a spreadsheet editor outside of Terra following the step-by-step instructions below.

Modifying-tables_Add-rows_Screen_shot.png

TSV uploads are additive

Uploading tsvs with missing columns or rows does not remove that data.  This allows you to upload new TSVs that only contain the rows or columns you want to be added (but must include the entity ID column which is used as the key). 

Step 1. Download the existing table (optional)

You can download the table that's already in the workspace as a template. Alternatively, you can make a table from scratch. Note, however, that the first (ID) column header must match the existing TSV. 

  • 1.1. Select the Download all Rows button at the top of the table.
    modifying-tables_Download-all-rows_Screen_shot.png

    1.2. Click the three vertical dots to the right of the "Copy Page to Clipboard" button and select Download as TSV.
    Delete-data-table-columns_Delete-data_Screen_shot.png

  • 1.1. Select the rows to download.
    img src="/hc/article_attachments/360073550091/Modifying-tables_Download-as-tsv_Screen_shot.png" alt="Modifying-tables_Download-as-tsv_Screen_shot.png" width="677" height="178">

    1.2. Click the three vertical dots to the right of the "Copy Page to Clipboard" button and select Download as TSV.
    Delete-data-table-columns_Delete-data_Screen_shot.png

Step 2. Edit and save the TSV file using your favorite spreadsheet editor

2.1. Open in your favorite spreadsheet editor and edit like any file.

Beware of unallowed characters! Note that ID cells (i.e. the first column of your table) can only include alphanumeric characters, "-" and "_" and no spaces. Single quotes are not allowed (use double quotes instead). 

All formatting will be lost when you save the file in TSV format.

When adding columns of metadataNote that if you are adding columns of metadata, column headers will be the attribute name of the new column in the updated data table.

2.2. Save the file as "tab-separated values" or "tab-delimited text".
Your editor may give you a warning, but we assure you, it's fine! Also, Terra will completely ignore the name you give the file. It's the "root entity" in the first column header that determines the table name in the workspace.
Data-QuickStart_Part2_Save-as-Tab-delimited-text.png

A note about  .tsv  versus  .txt  file extensions Depending on what spreadsheet editor you use, when you save in the proper format your spreadsheet may have either a ".tsv" or a ".txt" extension. Terra will accept either one.

Step 3. Upload the TSV file to your workspace

Click the blue "+" icon at the top right of the TABLE column in the workspace Data page and follow the directions.
Data-QuickStart-Part2_Upload-tsv.png

A note about overwriting table rows When your TSV load file has the same entity (name) as a table already in the workspace, you may get an error message when you try to upload about overwriting data (see screenshot below).

Exercise2-tsv-warning_Screen_Shot.png

Terra will only overwrite data rows with the same ID (in the first column). If the TSV (load) file includes different IDs, these rows will be added to the existing table.

Making tables programmatically (i.e. scripting)

You can automate the process of making and modifying tables using a special API called the FIreCloud Service Selector (FISS). Learn more in Managing data and automating workflows with the FISS API.

Next steps

Once you have your samples organized in a data table, you might want to add reference data to the workspace so that you can perform data processing like alignment or calculate quality control metrics. Learn how to add workspace-level metadata to a table in Creating Workspace Data tables.

Additional resources

 

Was this article helpful?

0 out of 0 found this helpful

Have more questions? Submit a request

Comments

0 comments

Please sign in to leave a comment.