PORTAL USER GUIDE
Reshape Table Long-to-Wide (Spatial)
The Reshape Table Long-to-Wide (Spatial) tool allows you to transform data tables which are in a long format – a format where each row of data does not have a unique geometry, into a wide format where each row should have a unique geometry. A common example of long format data tables are unfiltered temporal dataset, where for example a row may be a representation of the same location at a different time. For example, if there were multiple years of a variable to a dataset, a wide-format table would list each year of the variable as a new column and a long-format table would repeat the dataset in a new row whilst containing new information for the year variable.
For example, if there were multiple years of a variable to a dataset, a wide-format table would list each year of the variable as a new column and a long-format table would repeat the dataset in a new row whilst containing new information for the year variable.
The table we will be working on:
|3451||A B Paterson College||23||2014||-27.927672||153.360364|
|1750||A B Paterson College||118||2013||-27.927672||153.360364|
|5132||A B Paterson College||56||2015||-27.927672||153.360364|
|2217||Abercorn State School||1||2014||-25.136508||151.127706|
|511||Abercorn State School||5||2013||-25.136508||151.127706|
|3912||Abercorn State School||78||2015||-25.136508||151.127706|
|798||Abergowrie State School||13||2013||-18.474804||145.884302|
|2495||Abergowrie State School||20||2014||-18.474804||145.884302|
|4189||Abergowrie State School||11||2015||-18.474804||145.884302|
You can download a CSV copy of this table here.
Please Note: The Reshape Table Long-to-Wide (Spatial) tool relies on rows which are representing the same entity to have consistent identification fields and geometries. For example, where one specific school name has been defined to have a specific latitude and longitude, that school name must have the same latitude and longitude in every row it appears in.
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.
Next, to make full use of the capabilities of Reshape Table Long-to-Wide (Spatial) we need to spatialise the imported CSV, in this case we can do this by using the Spatialise Coordinates tool.
Once you have added and spatialised the datasets, you are ready to use the Reshape Table Long-to-Wide (Spatial) tool. Follow on to learn about the input options.
To perform the dataset transformation, open the Reshape Table Long-to-Wide (Spatial) tool (Tools → Data Processing → Reshape Table Long-to-Wide (Spatial)) 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 and spatialised.
- Identifier Column: This represents the variable that the dataset should be grouped by. In this instance. Select school.
- Subgroup Column: This represents the variable that provides new data. In this instance. Select year.