The mining industry in Australia plays a pivotal role in contributing to the country’s social and economic development. It reflects the evolving needs, demands, and population size of human society throughout history. Aiming at analysing the impact of mining industry to Australia society, the first thing that needed to do is the visualization of Australian mining data, sourced from the RMIT dataset.

The original dataset presented certain challenges in terms of data structure, which were incongruent with the formats expected by analytical tools like Tableau or Power BI. As a preliminary step, the data required some Alteryx restructuring work to conform to these tool-specific standards. This blog elucidates the data preparation process.

Step 1: Selection of Fields for Restructuring

The dataset comprises several types of mining products, each with a varying number of detailed columns. To maintain clarity and address the inherent differences in data structures, it was imperative to perform data restructuring for each mining product type separately. Therefore, the first step involved the selection of specific fields for reconfiguration.

Step 2: Extraction of Field Names from the First Data Row

To achieve this, a dynamic rename tool was employed to extract region and price data from the first row of the dataset, which originally contained state information. In this process, the original field names for mining product types were temporarily lost but were subsequently restored in the following step.

Step 3: Retrieval of Lost Field Names

By applying a formula function in the dynamic rename tool, the regional fields were given a prefix corresponding to the respective mining product type. Subsequently, the table was transposed, with “Year” and “Price” serving as key columns. This transformation resulted in a vertical table structure with columns for year, price, mining type_region, and value.

Step 4: Table Adjustment

The data was further refined by utilizing the text-to-column tool to separate the mining type as an independent column. The necessary fields were selected and the field order was adjusted accordingly.

Through this process of data restructuring, the dataset was successfully transformed into a format conducive to in-depth analysis using Tableau. This effort sets the stage for a rigorous examination of the Australian mining industry, offering valuable insights into its historical evolution and contemporary dynamics.

The Data School
Author: The Data School