Import and Edit of Observed Data

A generic tool for handling of observed data within the Open Systems Pharmacology Suite is the formerly known PKExcelImporter. It is used in both applications (PK-Sim® and MoBi®) for importing observed data from e.g. Microsoft Excel® files with following prerequisites:

  1. A file contains one or several sheets with data tables.

  2. Column headers are in the first non-empty row.

  3. Units are given in the second header row or as part of the column header (at the end) enclosed in brackets (For example Time [h] would be interpreted as column name Time [h] and unit h).

  4. LLOQ values (= values below Lower Limit Of Quantification) must be preceded by a "<", e.g. "<0.2", where 0.2 is the LLOQ value. In case of different LLOQ values in one Observed Data vector the largest of those LLOQ values is used as LLOQ value.

The LLOQ value is stored at the data column and is not editable. All LLOQ values are stored as LLOQ/2 (= 0.1 in the example) to display them in the middle of the unknown range 0, LLOQ. In charts for such data a dotted line y=LLOQ is shown.

File Selection Dialog

In the Parameter Identification those LLOQ values can be handled differently (see Handling of LLOQ values).

To import data you should do the following:

  1. Select the input file (see File Selection).

  2. Specify the column mapping (see Column Mapping in Import of Observed Data).

  3. You can continue importing data sheets/data files by adding or changing the column mapping or selecting another input file.

  4. Enter all required meta data and set unit information.

  5. Complete the transfer of the imported data sheets to the calling application by confirming your settings.

File Selection‌

Click on the Observed Data button to start the import component and specify the the excel file to be imported.

Both excel file formats (xls and xlsx) are supported and it is not required to have Microsoft Excel® installed on your computer.

Preview of imported and original data‌

After selection of the file to be imported, a split window appears (see screenshot below). The left hand side shows a preview of the imported data file using the current mapping, each data table can be found in a separate tab. The right hand side window displays the mapping of imported column identifiers with the predefined data types. This mapping is performed automatically upon import but can be overridden by adjusting the controls. The preview of the imported data displays the first one hundred lines of each imported sheet.

An estimate of the number of data tables upon import using the current mapping is given in brackets in the Import button. This helps the user to judge whether the specified mapping produces the desired number of data tables. The Import All button is used to import multiple sheets at the same time.

Clicking on the Preview Original Data button allows the user to quickly review the original data. This might be useful in case explanatory data that is needed to perform mapping gets trimmed out during the import process. Also, in the preview of the original data, specific subsets of data can be selected for import.

Column Mapping in Import of Observed Data‌

The mapping table on the right in the Import Observed Data window shows the automatically generated mapping of the columns of the source sheet to the targets columns. Automatic mapping of source columns onto the target columns takes the following criteria into account:

  1. Equality of names.

    The target column has the same name as the source column.

  2. The target column supports the unit of the source column.

  3. If several target columns match the above criteria, the ones that have not been used in mapping are preferred to avoid multiple mapping.

  4. If no matching target column can be found, proceed as for meta data information on table level.

The mapping of source and target columns can be changed manually by using the buttons on the right hand side of each target column cell.

A source column can only be mapped to a target column if the data types are compatible. This means, for example, that you cannot map a source column of data type 'date' to a target column of data type 'number'. Source columns of data type text can be mapped to all target column data types.

The icons to the left of each target entry in the mapping dialog have the following meaning:

It might be more effective to enter meta data information for a column during the mapping process, especially if you are using the multiple mapping feature.

The meta data will then be used for all columns which will be created out of this mapping.

Using Group By in the mapping By mapping a source column on a target column using, the data sheet will be split into every distinct value of that source column. This results in multiple import tables. The label of each resulting import table contains the source column name and the respective value of the source column in that group. If used for grouping, a target column will appear as meta data in the following.

Meta Data Mapping If meta data are requested for importing tables you can also map source columns onto such meta data fields. Then the source data gets split in the same way as for a group by mapping and the meta data fields are filled out with the distinct values of the source column.

In both, PK-Sim® and MoBi®, observed data can be organized in folders in the Building Block explorer. Observed data can be grouped into subfolders and shifted among folders by drag/drop or by using meta data to automatically group observed data during the import into a specific subfolder.

Importing a file with several data sheets‌

To import a single data sheet you have to click on the Import button. If you want to import several data sheets in one file, click on the Import All button (see Import All) button. The number of currently imported tables is shown in brackets in the imports tab page caption.

You can go through each source sheet, map the columns and import the sheet as new import table. That way you would collect several import tables which can be transferred to the calling application later on.

Each required target column must be mapped onto at least one source column to enable the import buttons.

Entering Meta Data‌

Meta data are additional information that the calling application might request of the user. There can be meta data requested for an imported table or for each imported column (see below for an example).

Setting Units‌

For a column there can be multiple dimensions defined. Each dimension can have multiple units.

If no unit information is found in the source column, the default unit is automatically set but must be explicitly confirmed.

Completing the Import of Observed Data‌

A new tab page is created for each imported data file and you can enter meta data for tables or columns, set unit information or just view the imported data (see Imported Table Tab Page Screenshot). Changes to the error type or to units can be made in this view and are directly reflected in the chart.

To complete the import of data tables to the calling application press the OK button.

Editing Observed Data‌

The new editing window can be accessed through double clicking the observed data in the building block view or through the context menu.

Last updated