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.

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_fid	timestamp	station_name	value
1	2015-01-04T13:00:00.000+0000	Angaston Power Station	85.259999
2	2015-01-05T13:00:00.000+0000	Angaston Power Station	565.340002
3	2015-01-04T13:00:00.000+0000	Bogong / Mckay Power Station	21.675
4	2015-01-05T13:00:00.000+0000	Bogong / Mckay Power Station	7016.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. Enter ReshapeWideInput.
  • Abstract: Description of your dataset. Enter Demonstration dataset for Reshape Wide.
  • Aggregation Level: The aggregation level of the geometry of your dataset, in this case, select 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.

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.
• Key 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.

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.