How to Edit, Filter, and Join Data Tables

Once you have a table of data, as an output of a search or a listing, XNAT provides you with several tools to either expand or narrow down your search results.

To expand your search and cross-reference against another data type, you can Join your data table with another data type. See below.

Sorting Data Tables

I have created a stored search on XNAT Central that joins APARC data with Clinical evaluations, just to have something to work with. Let's say, in this example, that I want to sort my data by MMSE scores, descending from the highest. A simple left-click on the column header brings up a contextual menu that allows me to do just that.

However, the resulting data table doesn't look right - because I have a set of NULL data floating to the top of my list. To fix this, I will need to filter this data table.

Filtering Data by Column

To exclude NULL entries in the MMSE scores from this data table, I need to create a filter. Clicking on the column header brings my contextual menu back, and I select "Filter".

In the Filter dialog, I can select from a number of mathematical operators, including IS NOT NULL. Creating this filter immediately narrows down my search, and brings the data I want to the top of the table.

In the table header, you will see the filter noted: Filter(s): MMSE IS NOT NULL

Removing Data Filters

Removing an established filter is easy. Simply click on the column header that has a filter applied to bring the filter dialog back up. Clicking on the Trashcan icon will remove that filter from that data set.

Filtering Tip: "AND" vs "OR" filters

If you place a filter on multiple columns of data, you can quickly narrow down your search results even further. For example, this table has three different column filters applied, which has narrowed an original data set of nearly 500 rows down to a 33-row data set.

You can see the filters specified in the table header:

Filter(s): (MMSE IS NOT NULL) AND (NumVert > 130000) AND (rating = 0.0)

These filters work in combination as "AND" filters, where the only data sets that are displayed are those that meet all three conditions. However, placing multiple filters on a single column of data does not work like this. For example, if I wanted to show only MMSE scores between 14 and 20, I would like to apply two filters like the following:

MMSE <= 20
MMSE >= 14

However, these filters combine as "OR" filters, which gives me a data set where rows are either less than 20 OR greater than 14 ... which returns my entire set of non-NULL data. This is a known issue that may be addressed in future versions of XNAT.

Joining Data Tables

An Advanced Search starts with a root, or "pivot," data type and joins it to other data types.

For consistency with naming in XNAT, we will call this a "pivot" data type, although this is a loaded term in data mining.

You can produce the same effect from any given data table, such as a data type report, by selecting Options > Join. This will allow you to join your current data table with a new data type.

Suppose you select MR Session as your pivot data type. Your report format begins with a single row of data for every MR session that you have permission to see.

This means: If a subject does not have an MR session, you will not see any data for that subject in this report. For some searches (i.e. "Create a clinical comparison for all subjects that have scan data") that is ideal. For others ("Show me the project protocol and highlight subjects that are missing MR Sessions") this is not going to work.

Partial data set: Pivot type "MR Session" joined to "Subject" and filtered by project. Note that subjects with multiple scans are listed multiple times in the "Subject" column.

Now suppose you select Subject as your pivot data type. Your report format begins with a single row of data for every subject that you have permission to see. This means: If you join to MR Session, you will see one and only one result for MR session as relates to each Subject. In any longitudinal study, or any study that involves gathering multiple sessions of data, this would obviously be a confusing and non-ideal result.

Partial data set: Pivot type "Subject" joined to "MR Session" with the "Primary Project for Session" added and filtered by project. Note that subjects do not have linked MR Sessions; instead, a count of MR Sessions is provided.

Pivoting on visits

You can also pivot on the Visit data type, if it is being used in your experiments. For example, select MR session as the pivot type. Your report format begins with a single row of data for every MR session you have permission to see. If you join to the Visit data type, you will see each MR session that is associated with a Visit. Then you can filter by visit_name and visit_type.