DataViz Makeover 1

DataViz Makeover 1

The original Visualisation

Figure 1 below shows the original visualisation for the top six trading countries with Singapore in 2019 to 2020 using the data provided by Department of Statistics, Singapore (DOS).

Figure 1: Original Visualisation

1. Critiques for the original visualisation

Before looking at the visualisation aesthetics and clarity, it is important to have a clear understanding of the context and objective of the visualisation.

What does merchandise trade constitutes?

• Merchandise Trade is the combination of imports and exports.
  • Imports refers to goods brought into Singapore.
  • Exports refers to good brought out of Singapore.
• How are the top six trading countries with Singapore selected?
  • They are selected based on their total summation of imports and exports for year 2020.
• How do we measure merchandise trade?
  • Merchandise trade is measured in Singapore Dollars (S$).

1.1 Clarity of Visualisation

Clarity is the visualisation “Fit”: How the visualisation matches and tell reality.

1.1.1 Good points in the visualisation to be kept

1. Plotting the graph over time provides temporal information.
2. Graph was plotted with “Export” and “Import” data.

1.1.2 Areas to be improved

1. Title of visualisation did not mention who the six countries are trading with.
2. Subtitle can be added to provide more context and information for the visualisation, such as how the top six countries are selected, which year is used for the calculation and the unit of measurements.
3. Lack of aggregated data that determined the ranking and selection of the top six countries.
4. Ranking of the countries could present valuable information for the visualisation.
5. No caption is included to reference the source of data.
6. Different axis intervals and range giving the wrong visual perception:
   • Inter-plot x-axis intervals and range are different. Japan plot has a different time period as compared to the other countries.
   • Inter-plot y-axis intervals and range for import and exports are different, making it difficult to compare between the six countries.
   • Y-axis label is confusing. The “M” was not explained.
7. Merchandise trade can be seasonal and it fluctuates heavily by month. A condensed time frame might be more useful for the visualiation.

1.2 Aesthetics of Visualisation

Aesthetics is the visualisation “Form”: How the visualisation looks.

1.2.1 Good points in the visualisation to be kept

1. Individual graph for each country allows comparison between the countries.

1.2.2 Areas to be improved

1. Title alignment are inconsistent.
   • Main title is skewed to the right.
   • Individual titles are skewed to the left.
   • Main title font size is smaller than individual plots title.
2. Color fill under the curve does not bring any value. Furthermore, the overlapping colors are confusing for readers to read and interpret.
3. Annotation can be added on the graphs to tell the story more effectively.

2. Alternative Data Visualisation

From Ben’s (Jones 2012) Data Visualisation chart of Clarity vs. Aesthetics shown in Figure 2, the current visualisation would fall under Quadrant III and the proposed visualisation would aim to be in Quadrant I.

Figure 2: Aesthetics against Clarity chart

To improve the visualisation, the time series could be changed to yearly instead of monthly. Merchandise trade of each country fluctuates heavily and certain goods are seasonal. Utilising a monthly interval for the x-axis clutters the graph with the line plot peak and trough that might not provide useful information. Aggregating the data into years might be clearer for interpretation for the change over year.

An additional plot can be added to summarize and rank the top 6 countries. This plot displayed will include background information that the top six country are selected based on the total trade in 2020. The bar plot can also be arranged in descending order to showcase the ranking of the six countries.

Figure 3: Sketch of alternative visualisation

Figure 3 would be an alternative presentation of the visualisation.

• The title will be renamed to provide more context to understand the visualisation.
• Subtitle will be added to provide background information on the context.
• The line plot concept will be retained on the left of the visualisation.
  • The x-axis will be changed to year instead of months to aggregate the data.
  • The y-axis title to include the units of measurement (S$).
  • Line plot to be stacked horizontally with same y-axis marks for comparison of trade value between the 6 countries.
  • Additional line of total trade (sum of import and export) to be included in the graph.
• Additional horizontal bar plot to be added to the right of the visualisation.
  • The bar plot will indicate the total trade in 2020.
  • Include the title and context for the bar plot.
  • Other than the 6 country, include an additional variable Others to group the remaining countries.
  • Change the color for the bar plot to green to align to the line plot.
• Include caption to indicate the dataset source and date.
• Include footer if necessary to indicate any special information.

3. Proposed Data Visualisation using Tableau

The dataset used was retrieved from Deparment of Statistics Singapore under the sub-section of Merchandise Trade by Region/Market.

Tableau Prep Builder 2021.1 software is used for data preparation and Tableau 2021.1 is used for data visualisation.

3.1 Data Preparation

Upload the dataset into Tableau Prep Builder.

Figure 4: Load the Import worksheet

Refer to Figure 4 to import the dataset by following the steps below.
1. Tick the box circled in red on the left. This option allows the Tableau Prep Builder to perform preliminary cleaning of the data.
2. Drag the T1 worksheet in orange into the middle box.
3. Change the name in green to Import.
4. Click the + button and create a Clean Step node in purple.

Figure 5: Exclude the rows for regions

Refer to Figure 5 to exclude the rows for regions and total imports by following the steps below.
1. Select the first 7 rows (Total Merchandise Imports, America, Asia, Europe, Oceania, Africa, European Union) in red from the Variables column and click the Exclude button in orange.

Figure 6: Pivot Date into row

Refer to Figure 6 to pivot the Months from the columns into rows.
1. Select the + button and create a Pivot node in red.
2. Drag all the months column header and drop to the Pivoted Fields section as per the orange arrow.
3. Rename the column headers to Date, Import and Country accordingly in blue box.
4. Change the data type of Date column to Date in green.

Figure 7: Clean column header and label data type

Refer to Figure 7 to aggregrate the import by year
1. Select the + button and create an Aggregate node in red.
2. Drag column Country and Date and drop them in Grouped Fields as per the orange arrow.
3. Drag column Import and drop it in Aggregated Fields as per the green arrow to aggregate the sum of import by year.
3. Group the column Date by Year following the steps in the blue boxes.

Repeat the steps from Figure 4 to Figure 7 for Export using the T2 worksheet.

Figure 8: Join both tables

Refer to Figure 8 to Join both table
1. Drag the Aggregate 3 node from T2 flow to the + button on the right of T1 flow as shown in the red arrow in Figure 8.
2. Add two Applied Join Clauses to match variables Date and Country shown in orange box.
3. Select the Join Type to full in the green box to include all countries into the final list.
4. Select the columns Country(Country-1) and Date(Date-1) and Remove Fields in the blue box. These are duplicate or repeated columns after performing the Join.

Figure 9: Create new column for total sum of export and import

Refer to Figure 9 to create new column for total sum of export and import
1. Select the Create Calculated Field button in red.
2. Change the Field Name to Total in orange.
3. Input the equation of [Export] + [Import] in green and save the new column.

Figure 10: Pivot the Export, Import and Total columns

Refer to Figure 10 to Pivot the columns
1. Create a new Pivot node in the red box.
2. Drag and drop the columns Export, Import and Total into the Pivoted Fields in orange.

Figure 11: Output into csv

Refer to Figure 11 to Ouput file into csv
1. Create a new Ouput node in the red box.
2. Select the Browse button to change the directory and filename and change the Output type to Comma Separated Values (.csv) in orange.

3.2 Data Visualisation

The data prepared earlier is uploaded into Tableau for Visualisation.

3.2.1 Create line plot for the 6 countries

The data contains all countries that trades with Singapore from 1976 to 2021. Hence, the columns Year and Country is added to the Filter section on the worksheet to align with the original visualisation parameters. Refer to step 1 and 2 together with Figure 12 to filter the 6 countries for 2019 and 2020.

Figure 12: Filter the Country and Year

1. Filter Year to select 2019 and 2020.
   
2. Filter Country to select the 6 countries, Mainland China, Malaysia, United States, Taiwan, Hong Kong and Japan.
   

Figure 13: Dual axis and sychronize of dual y-axis

3. Create line plot by adding Country and Date to the Columns section and two Trade value node to Rows section.
4. Set one Trade value to a Line graph and the other Trade value to a Shape type. Right click the second node and select Dual Axis and Synchronize the secondary y-axis as in Figure 13. This allows the graph aesthetics to be more customisable with the size and shape.
5. Add the Trade type to the Color tab to separate into 3 lines: Export, Import and Total.
6. Change the Total line color to green to signify the total summation of import and export.
7. Right click the the secondary dual y-axis on the right and uncheck the Show Header option to remove the y-axis. As the secondary y-axis is of the same interval as the primary y-axis on the left, there is no value is displaying it.
8. Rename the y-axis to Trade Value (S$).
9. Change the Title of the graph to Merchandise Trade of Top Six Trading Countries with Singapore from 2019 to 2020, (S$).

Figure 14: Creating dummy variables and annotate YoY

10. Annotate the Year-on-Year change from 2019 to 2020 on the Total line to show the change in percentage. The numbers is set to red to indicate negative growth and green to indicate positive growth. To allow repeatability in annotating the number and colors for the dashboard, five dummy variables are created as shown in Figure 14.
    • percent+ and percent- to indicate the positive and negative growth year-on-year in percentage. The logic sequence is in subsection 1 in Figure 14.
    • arrow+ and arrow+ to show an upward pointing triangle for positive growth and downward pointing triangle for negative growth respectively. The logic sequence is in subsection 2 in Figure 14.
    • Verb to output the word grew or fell depending if the year-on-year change is positive or negative respectively. The logic sequence is in subsection 3 in Figure 14.
    • Right click the green circle for Total sales in 2020 and add Annotate -> Mark.
    • Include the annotation description in Figure 14 subsection 4. Change the color for positive change to green and negative change to red.

Figure 15: Annotate unique insights

11. Annotate unique insights such as strong increase or decrease of the import or export on the graph. The annotation description is shown in Figure 15 and change the color similar to step 10 for Total change.

The final line plot is shown in Figure 16.

Figure 16: Final line plot

3.2.2 Create Bar Chart for the 6 countries in 2020

1. Create a Bar chart in a new worksheet

Figure 17: Create new Variable for other countries

2. Right click Country and Create a new Set as per the top left example in Figure 17.
3. Name the set top6country and select the 6 countries from the list as per the top right example in Figure 17.
4. In the Analysis toolbar, select Create Calculated Field.
5. Enter the logic sequence shown at the bottom of Figure 17 and name the variable Others. This creates a new variable where the 6 countries are named as usual and the other countries will be combined into the dummy variable called Others.
6. Filter Date variable to show only 2020.
7. Filter Trade type to show only Total.
8. Insert variable Others to the Rows and Trade Value to the Columns on the bar chart worksheet.
9. Insert Others to the Color and Trade Value to Label and change the calculation to Percent of Total. This shows the proportion of total trade value for each country over Singapore’s total trade value in 2020.
10. Edit the bar colors to green to align to the Line plot Total color. Set the Others to grey color to better differentiate the countries apart.
11. Change the Title to Total Merchandise Trade of Singapore in 2020, (S$).
12. Change the x-axis title to Trade Value (S$).

Figure 18: Final Bar Chart (2020)

3.2.3 Create Dashboard

1. Create a new Dashboard.

Figure 19: Dashboard wireframe

2. Setup the wireframe for the dashboard by referring to Figure 19 and the following steps.
   • Change the dashboard Size to Automatic boxed in red in Figure 19.
   • Check the option for Show dashboard title boxed in red to display the Title in Figure 19 subsection 1.
   • Insert Horizontal and Vertical objects as shown in Figure 19 for section 2 to 6.
3. Drag and drop the line plot worksheet Trade to sub section 3 in Figure 19.
4. Change line plot Legend to floating and re-position it on top of the line plot.
5. Drag and drop the bar chart worksheet BarChart2020 to sub section 4 in Figure 19.
6. Remove the Legend container as the country name is represented on the y-axis.
7. Change the Title of the dashboard
8. Add subtitle to provide context for visualisation.
9. Add caption to include source and date of data.
10. Add footer to include the metadata definition and exclusion of data.

Figure 20: Final Dashboard

The final visualisation is available on my Tableau Public page.

4.0 Insights from visualisation

There is a saying: “Statistics are like bikinis. What they reveal is suggestive, but what they conceal is vital.” Visualisation should be designed to be factual and clear for interpretation.

A positive trade balance is termed when Singapore export value is greater than import value with a country. This signifies an inflow of cash to Singapore from the country. Conversely, an negative trade balance is termed when Singapore import value is greater than export value with a country. This signifies an outflow of cash from Singapore to the country.

COVID-19 was an unprecedented event in 2020 which led to countries locking down their cities or closing their borders globally and Singapore is no exception. Trade is integral to Singapore’s success and the decrease in trade activities in 2020 was a major impact on it’s growth. However, the makeover from the original visualisation showed meaningful insights for interpretation.

1. The total trade value with Malaysia fell 8.4% in 2020, the largest decrease among the six countries. The Import value with Malaysia was similar for 2020 whereas the Export value took a major hit and fell by 18.2% from 2019. This resulted in a negative trade balance with Malaysia in 2020.

2. The total trade with the United States marginally fell by 2.5%. However, there was a large change in Export (grew 19.2%) and Import (fell 18.9%) in 2020. In 2019, Singapore had a negative trade balance with the United States and it was the reversed in 2020 where there was a positive trade balance.

3. Trade with Taiwan performed the best among the six countries where the total trade value grew by 13.0% in 2020. Both export and import grew significantly in 2020.

4. Hong Kong is the fifth largest trade country with Singapore among the six country. Interestingly, export value is much larger than import value where the exports value is around 90% of the total trade value.

5. Mainland China and Japan total trade value had marginal change. Imports and exports are also consistent and equal in 2020.

6. The top six countries total trade stands at around 57% of Singapore’s total trade in 2020.

In light of COVID-19 pandemic affecting countries economy, the changes in trade might be unique in 2020. A better representation would be to use a longer time frame to show the general trend of the trade value.

Jones, Ben. 2012. “Data Visualization: Clarity or Aesthetics?” 2012. https://dataremixed.com/2012/05/data-visualization-clarity-or-aesthetics/.