How to Transform Tables with Views

A table is the result of importing data. Depending on the source of the data, the value types of the variables may not be correct (e.g. text instead of integer) and the data dictionary may be missing or incomplete (e.g. CSV provides only variable names).

Views are logical tables that can be used both for transforming data (e.g. recoding) and enhancing the data dictionary. The advantage of using a view instead of modifying the imported table’s variables is that the table can be dropped, data can be updated/imported again without affecting the view’s data dictionary.

From a Table

This is a manual operation that can be very straightforward.

Procedure

Note

  1. Go to the table’s page
  2. Select variables (either all or only the ones of interest)
  3. Select Add to view

⇒ The view is created (or updated if it already exists) with the selected variables.

From a Variable

This operation allows to fine tune the transformation script, useful when setting categories from an originally continuous variable.

Procedure

Note

  1. Go to the variable’s page
  2. Select from the Derive menu the appropriate derivation operation
  3. Follow derivation wizard instructions

⇒ The view is created (or updated if it already exists) with the selected variable.

Using Excel

It can be convenient for batch processing and for Excel lovers, to prepare the view using an Excel file. This file will both contains the variable descriptions and the data transformation scripts.

  • Either follow the instructions in the Excel template to make your data dictionary from scratch,
  • Or download the table’s dictionary to start from a prefilled Excel file.

The derivation script is to be defined in the script column (see Magma JS Introduction).

Procedure

Note

  1. Go to the project’s tables page
  2. Select Add Table > Add view…
  3. Upload the Excel file and select the table on which the view is based

⇒ The view is created with the provided variables.

Using R

The opalr R package is very powerful for interacting with a Opal server.

A view can be created with the opal.table_create() function and its data dictionary updated with the opal.table_dictionary_update() function.

See also data dictionary management examples from the Opal Projects vignette.

Procedure

Note

  1. Preliminary: install opalr R package
  2. Connect to Opal server using opal.login()
  3. Make the view’s data dictionary in R
  4. Create the view with opal.table_create() or update the view with opal.table_dictionary_update()

⇒ The view is created or updated with the provided variables.