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.

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_fidschoolyr7enryearlatlon
3451A B Paterson College232014-27.927672153.360364
1750A B Paterson College1182013-27.927672153.360364
5132A B Paterson College562015-27.927672153.360364
2217Abercorn State School12014-25.136508151.127706
511Abercorn State School52013-25.136508151.127706
3912Abercorn State School782015-25.136508151.127706
798Abergowrie State School132013-18.474804145.884302
2495Abergowrie State School202014-18.474804145.884302
4189Abergowrie State School112015-18.474804145.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.

Inputs

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.

Outputs

Once the tool has run, a pop-up box will appear asking you to display your results (shown below). Select Display to open the output table. You will see that there has been an entirely new table created, which now combines the schools in our imported dataset into its own row, pivoting the data based on their year.

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: