PORTAL USER GUIDE

Reshape Table Long-to-Wide

The Reshape Table Long-to-Wide tool allows you to transform spatial data tables which are of long-format into wide-format. Wide-format tables represent one subject of data per row with many columns representing, for example one variable over many columns. Long-format tables contain multiple rows of the same subject with varying information.

If you require the preservation of spatial information please use the Reshape Table Long-to-Wide (Spatial) tool instead.

SET UP

For this worked example, we will transform a wide-format data table into a long format data table. A theoretical table with limited dimensions has been used to allow for ease of visualisation, this tool would work for any similarly formatted table of much larger dimensions.

 

The table we will be working on:

ogc_fidtimestampstation_namevalue
12015-01-04T13:00:00.000+0000Angaston Power Station85.259999
22015-01-05T13:00:00.000+0000Angaston Power Station565.340002
32015-01-04T13:00:00.000+0000Bogong / Mckay Power Station21.675
42015-01-05T13:00:00.000+0000Bogong / Mckay Power Station7016.8399

You can download a CSV copy of this table here.

First, we begin by importing the CSV into the AURIN Portal – To do this:

  • Click Import in the Data sidebar.
  • Browse to the location of your local copy of the CSV file and input the following variables:
    • Title: Name of your dataset.
    • Abstract: Description of your dataset.
    • Aggregation Level: The aggregation level of the geometry of your dataset, in this case, this should be set to Non Spatial.
    • Key: The primary key of the dataset, this can be any column you view as containing the values which identify each row, select ogc_fid.
  • Click Add & Display.

Once you have added the datasets, you are ready to use the Reshape Table Long-to-Wide tool. Follow on to learn about the input options.

Inputs

To perform the dataset transformation, open the Reshape Table Long-to-Wide tool (Tools → Data Processing → Reshape Table Long-to-Wide) and enter your parameters as shown in the image below then click the Run Tool button.

 

  • Dataset Input: This is the dataset that contains the columns you would like to include in the calculation. Select the one we imported.
  • Identifier Column: This represents the variable that the dataset should be grouped by. In this instance. Select station_name.
  • Subgroup Column: This represents the variable that provides new data. In this instance. Select timestamp.

Outputs

Once the tool has run, a pop-up box will appear asking you to display your results (shown below). Click Display to open the output table. You will see that there has been an entirely new table created (also shown below), which now combines the power stations in our import dataset into its own row, and combines the timestamped variable data into their respective columns.

 

Looking for Spatial Data?

You can browse the AURIN Data Discovery:

How can you Create Impact?

Learn more about AURIN Researcher's outcomes & real-world impact: